com.esd.cs.common.XExcelSheetParser.java Source code

Introduction

Here is the source code for com.esd.cs.common.XExcelSheetParser.java

Source

/*
 * Copyright (c) 2013 ????www.hrbesd.com. All rights reserved.
 * 
 * HRBESD PROPRIETARY/CONFIDENTIAL. Use is subject to license terms.
 */
package com.esd.cs.common;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * ?excel 2003-2010
 * 
 * @author zhangjianzong
 * 
 */
public class XExcelSheetParser {
    // log4j
    private Logger logger = Logger.getLogger(XExcelSheetParser.class);

    private XSSFWorkbook workbook;// 

    public XExcelSheetParser(File file) {
        try {
            // ?workbook

            workbook = new XSSFWorkbook(new FileInputStream(file));
            // workbook = new HSSFWorkbook(new FileInputStream(file));
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public List<List<Object>> getDatasInSheet(int sheetNumber) {
        List<List<Object>> result = new ArrayList<List<Object>>();
        // sheet
        XSSFSheet sheet = workbook.getSheetAt(sheetNumber);
        // sheet
        int rowCount = sheet.getLastRowNum();
        logger.info("found excel rows count:" + rowCount);
        if (rowCount < 1) {
            return result;
        }
        // ??row
        for (int rowIndex = 0; rowIndex < rowCount; rowIndex++) {
            // 
            XSSFRow row = sheet.getRow(rowIndex);
            if (null != row) {
                List<Object> rowData = new ArrayList<Object>();
                // ?
                int cellCount = row.getLastCellNum();
                // ??cell
                for (short cellIndex = 0; cellIndex < cellCount; cellIndex++) {
                    XSSFCell cell = row.getCell(cellIndex);
                    // ??
                    Object cellStr = this.getCellString(cell);

                    rowData.add(cellStr);
                }
                result.add(rowData);
            }
        }

        return result;
    }

    private Object getCellString(XSSFCell cell) {
        // TODO Auto-generated method stub
        Object result = null;
        if (cell != null) {
            // ?Numeric:0,String:1,Formula:2,Blank:3,Boolean:4,Error:5
            int cellType = cell.getCellType();
            switch (cellType) {
            case HSSFCell.CELL_TYPE_STRING:
                result = cell.getRichStringCellValue().getString();
                break;
            case HSSFCell.CELL_TYPE_NUMERIC:
                result = cell.getNumericCellValue();
                break;
            case HSSFCell.CELL_TYPE_FORMULA:
                result = cell.getNumericCellValue();
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                result = cell.getBooleanCellValue();
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                result = null;
                break;
            case HSSFCell.CELL_TYPE_ERROR:
                result = null;
                break;
            default:
                break;
            }
        }
        return result;
    }
}