excelpoiparser.java

一个关于tlms的一个小程序 看看能否帮助到别人

package com.szmx.component.excel;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import java.util.List;
import java.util.ArrayList;
import java.io.*;
import java.lang.reflect.Method;

/**
 * ================================================================
 * Copyright 2006 szmx
 * <p/>
 * Change Revision
 * ---------------------------------------------------------------
 * Date              Author            Remarks
 * Mar 20, 2006     BZhang      Create class com.szmx.component.excel.ExcelPoiParser
 * ================================================================
 */
public class ExcelPoiParser {
    private static Log logger = LogFactory.getLog(ExcelPoiParser.class);

    public static List parseExcelFile(String excelFileFullPath,
                                      String rowObjectClassName) throws ExcelParserException {
        File excelFile = new File(excelFileFullPath);
        if (!excelFile.exists()) {
            String err_msg = "Invalid Excel File Path :" + excelFileFullPath;
            logger.error(err_msg);
            throw new ExcelParserException(err_msg);
        }
        return parseExcelFile(excelFile, rowObjectClassName);
    }

    public static List parseExcelFile(File excelFile,
                                      String rowObjectClassName) throws ExcelParserException {
        InputStream excelFileStream;
        try {
            excelFileStream = new FileInputStream(excelFile);
        } catch(FileNotFoundException e) {
            String err_msg = "File Not Found :" + excelFile.getAbsolutePath();
            logger.error(err_msg);
            throw new ExcelParserException(err_msg);
        }
        return parseExcelFile(excelFileStream, rowObjectClassName);
    }

    public static List parseExcelFile(InputStream excelFileStream,
                                      String rowObjectClassName) throws ExcelParserException {
        List rowObjectList = new ArrayList();
        Class rowObjectClass;
        try {
            rowObjectClass = Class.forName(rowObjectClassName);
        } catch (ClassNotFoundException e) {
            String err_msg = "Class Not Found :" + rowObjectClassName;
            logger.error(err_msg);
            throw new ExcelParserException(err_msg);
        }

        HSSFWorkbook workbook;
        try {
            workbook = new HSSFWorkbook(excelFileStream);
        } catch (IOException e) {
            String err_msg = "IO Exception occured when read stream :" + excelFileStream;
            logger.error(err_msg);
            throw new ExcelParserException(err_msg);
        }

        HSSFSheet sheet0 = workbook.getSheetAt(0);     // Sheet0
        int fromRowNum = sheet0.getFirstRowNum() + 1;  // ingore the first row
        int toRowNum = sheet0.getLastRowNum();         // to last row
        HSSFRow headrow = sheet0.getRow(sheet0.getFirstRowNum());
        for (int i = fromRowNum; i <= toRowNum; i++) {
            HSSFRow row = sheet0.getRow(i);
            BaseRowObject rowObj = getRowObject(headrow, row, rowObjectClass);   // convert to obj
            rowObjectList.add(rowObj);
        }

        return rowObjectList;
    }

    private static BaseRowObject getRowObject(HSSFRow headrow, HSSFRow row,
                                              Class rowObjectClass) throws ExcelParserException {
        // get the KBObject Class Name and instance
        BaseRowObject rowObject;
        try {
            rowObject = (BaseRowObject) rowObjectClass.newInstance();
        } catch (Exception e) {
            String err_msg = "Error occured when creating row object :" + rowObjectClass.getName();
            logger.error(err_msg);
            throw new ExcelParserException(err_msg);
        }

        // set the KBObject properties
        for (int i = headrow.getFirstCellNum(); i < headrow.getLastCellNum(); i++) {
            HSSFCell headCell = headrow.getCell((short) i);
            HSSFCell cell = row.getCell((short) i);
            if (headCell != null) {
                if (headCell.getCellStyle().getHidden()) {
                    i--;
                }else{
                    setRowObjectField(rowObject, cell, i);
                }
            }
        }

        return rowObject;
    }

    private static void setRowObjectField(BaseRowObject rowObject,
                                          HSSFCell cell,
                                          int cellIndex) throws ExcelParserException {
        boolean useStringParamFirst = true;
        Class [] methodStringParamClasses = new Class[]{String.class};
        Object[] methodStringParams = new String[]{""};
        Class [] methodParamClasses = null;
        Object[] methodParams = null;

        // prepare the setter method's param info
        if (rowObject.getPropertyName(cellIndex) != null) {
            if (cell != null) {
                switch (cell.getCellType()) {
                    case HSSFCell.CELL_TYPE_STRING :
                        methodStringParams[0] = cell.getStringCellValue();
                        break;
                    case HSSFCell.CELL_TYPE_NUMERIC :
                        methodStringParams[0] = String.valueOf(new Double(cell.getNumericCellValue()).longValue());
                        useStringParamFirst = false;
                        methodParamClasses = new Class[]{Long.class};
                        methodParams = new Long[]{new Long(new Double(cell.getNumericCellValue()).longValue())};
                        break;
                    case HSSFCell.CELL_TYPE_BOOLEAN :
                        methodStringParams[0] = String.valueOf(Boolean.toString(cell.getBooleanCellValue()));
                        useStringParamFirst = false;
                        methodParamClasses = new Class[]{Boolean.class};
                        methodParams = new Boolean[]{Boolean.valueOf(cell.getBooleanCellValue())};
                        break;
                }

                // get the setter method
                String fieldName = rowObject.getPropertyName(cellIndex);
                String methodName = "set" + String.valueOf(fieldName.charAt(0)).toUpperCase() + fieldName.substring(1, fieldName.length());

                try {
                    Method setterMethod;
                    if (useStringParamFirst) {
                        setterMethod = rowObject.getClass().getMethod(methodName, methodStringParamClasses);
                    } else {
                        try {
                            // try using Non-String param
                            setterMethod = rowObject.getClass().getMethod(methodName, methodParamClasses);
                        } catch (Exception e) {
                            // then try using String param
                            setterMethod = rowObject.getClass().getMethod(methodName, methodStringParamClasses);
                            useStringParamFirst = true;
                        }
                    }

                    // invoke the setter method
                    if (useStringParamFirst) {
                        setterMethod.invoke(rowObject, methodStringParams);
                    } else {
                        setterMethod.invoke(rowObject, methodParams);
                    }
                } catch (Exception e) {
                    String err_msg = "Class Method Access Error :" + methodName;
                    logger.error(err_msg);
                    throw new ExcelParserException(err_msg);
                }
            }
        }
    }

}