5、poi usermodel读取数据

需求：读excel中的数据，去掉空行，入库
常用的读取数据方法当然是用poi了，循环sheet页，每一行row，每一个单元格cell。获得的数据再通过反射封装到对象中。
遇到的坑有：
（1）空行问题：具体情况具体处理吧，如果某一个的每个单元格如果都为空就认为是空行，也有某个单元格为空，就忽略该行的情况。
（2）性能问题：100条左右的数据，从读取数据到封装入库，总共花了20多秒，结果当然是poi速度太慢，虽然是小系统，但是这种速度也是不可忍受的。于是找到eventUserModel方式读取，见下一节

import java.io.File;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import org.apache.commons.io.FileUtils;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.picc.ermss.controller.UploadController;
import com.picc.ermss.model.Require;
import com.picc.ermss.model.UserSheet;

public class ExcelUtil {
    private final static Logger logger = LoggerFactory.getLogger(UploadController.class);

    public static Map<String, List<String>> readSheetTitle(File file) {
        Map<String, List<String>> sheetTitleMap = new HashMap<String, List<String>>();
        Workbook workbook = null;
        try {
            // 获取 workbook
            workbook = WorkbookFactory.create(FileUtils.openInputStream(file));
            // 遍历 workbook
            for (int i=0; i<workbook.getNumberOfSheets(); i++) {
                // 获取某个sheet
                Sheet sheet = workbook.getSheetAt(i);
                List<String> titleList = new ArrayList<String>(); 
                // 获取 sheet的第一行 表头
                Row firstRow = sheet.getRow(0);
                if(firstRow == null) {
                    continue;
                }
                // 遍历第一行的每个cell
                for (int j=0; j<firstRow.getLastCellNum(); j++) {
                    Cell cell = firstRow.getCell(j);
                    String title = cell.getStringCellValue().trim();
                    if (!"".equals(title)) {
                        // 标题不为空，才添加到 titleList
                        titleList.add(title);
                    }
                }
                sheetTitleMap.put(sheet.getSheetName(), titleList);
            }
        } catch (IOException | EncryptedDocumentException | InvalidFormatException e) {
            logger.info(e.getMessage());
        } finally {
            try {
                if(workbook != null) {
                    workbook.close();
                }
            } catch (IOException e) {
                logger.error(e.getMessage());
            }
        }
        return sheetTitleMap;
    }
    
    private static Sheet sheet = null;
    private static Row rowFirst = null;
    private static Row row = null;
    private static Map<String, String> objectMap = new HashMap<String, String>(); 
    private static String title = "";
    private static String value = "";
    
    // 解析excel，返回List<Map<String, String>>格式的对象
    public static List<Map<String, String>> parseExcelData(List<UserSheet> userSheetListChanged, File file) {
        logger.info("开始方法-----解析excel成List<Map<String, String>>对象");
        logger.info("获取workbook");
        List<Map<String, String>> resultList = new LinkedList<Map<String, String>>();  
        Workbook workbook = null;
        // 获取 workbook
        try {
            workbook = WorkbookFactory.create(FileUtils.openInputStream(file));
        } catch (EncryptedDocumentException | InvalidFormatException | IOException e) {
            e.printStackTrace();
        }
        logger.info("开始解析数据，并添加到list中");
        for (UserSheet userSheet : userSheetListChanged) {
            // 根据名字获取sheet
            logger.info("要解析的sheetname：" + userSheet.getSheetname());
            sheet = workbook.getSheet(userSheet.getSheetname());
            // 获取第一行
            rowFirst = sheet.getRow(0);
            // 遍历每一行sheet.getPhysicalNumberOfRows()
            for(int rowIndex = 1; rowIndex < sheet.getPhysicalNumberOfRows(); rowIndex++) {
                logger.info("遍历第" + rowIndex + "行");
                row = sheet.getRow(rowIndex);
                // 每一行 对应一个 ObjectMap
                objectMap.clear();
                // 遍历数据行里的每一个单元格，遍历长度以第一行为准
                for(int cellIndex=0;cellIndex < rowFirst.getPhysicalNumberOfCells();cellIndex++){ //cell  
                    title = rowFirst.getCell(cellIndex).getStringCellValue();
                    // 给sheetid赋值
                    if("sheetid".equals(title)) {
                        value = userSheet.getSheetid();
                    }
                    // 给serialno赋值
                    if("serialno".equals(title)) {
                        value = String.valueOf(rowIndex);
                    }
                    Cell cell = row.getCell(cellIndex);  
                    if(cell != null) {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        value = cell.getStringCellValue();
                    }
                    // 把每个单元格里的值都放到map里
                    objectMap.put(title, value);
                } 
                // 过滤空行
                Set<Entry<String,String>> entrySet = objectMap.entrySet();
                int sum = 0;
                int actualSum = entrySet.size();
                for(Entry<String,String> entry : entrySet) {
                    String value = entry.getValue();
                    if("".equals(value) || value == null) {
                        sum += 1;
                    }
                }
                if(actualSum - sum == 2) {
                    //resultList.add(objectMap);  
                    break;
                } else {
                    resultList.add(objectMap);  
                }
            }
        }
        logger.info("结束方法-----解析excel成List<Map<String, String>>对象");
        return resultList;
    }

    
    
    /** 
     * 利用反射将    List<Map<String,String>>结构 生成相应的List<T>数据 
     *  
     * */  
    public static <T> List<T> toObjectList(List<Map<String, String>> list, Class<T> clazz) {  
        List<T> returnList = new LinkedList<T>();  
        for(int i=0; i<list.size(); i++){  
            Set<Map.Entry<String, String>> set =  list.get(i).entrySet();  
            Iterator<Entry<String, String>> iterator = set.iterator();  
            // 生成一个对象实例
            T obj = null;
            try {
                obj = clazz.newInstance();
            } catch (InstantiationException | IllegalAccessException e) {
                e.printStackTrace();
            }  
            Method[] methods = clazz.getDeclaredMethods();  
            while (iterator.hasNext()) {      
                Map.Entry<String, String> entry = (Map.Entry<String, String>) iterator.next();  
                for(Method m : methods){  
                    if(m.getName().startsWith("set")) {     
                        String methodName = entry.getKey().toString();  
                        StringBuffer sb = new StringBuffer(methodName);    
                        sb.replace(0, 1, (methodName.charAt(0)+"").toUpperCase());    
                        // 获取set方法
                        methodName = "set" +  sb.toString();  
                        if(methodName.equals(m.getName())) {  
                            try {
                                // 调用set方法，给对象赋值
                                m.invoke(obj, entry.getValue());
                            } catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
                                e.printStackTrace();
                            }  
                            break;  
                        }  
                    }  
                }  
            }  
            returnList.add(obj);  
        }  
        System.out.println("size=" + returnList.size());  
        return returnList;  
    }  
    
    
    
    public static void main(String[] args) {
//      System.out.println(ExcelUtil.class.getResource("/").getPath().substring(1));
//      File file = new File("C:/Users/Administrator/Desktop/说明文档2/xxxx.xlsx");
//      System.out.println(readSheetTitle_HSSF(file));
        
        
        List<UserSheet> usersheetList = new ArrayList<UserSheet>();
        UserSheet sheet = new UserSheet();
        sheet.setSheetid("024e1efd-0c2b-4484-b807-e12ce9f0263a");
        sheet.setSheetname("需求提验升级时间");
        usersheetList.add(sheet);
        File file = new File("D:/profiles/sts-bundle/workspace20170628/ermss/target/classes/aaa.xlsx");
        
        List<Map<String, String>> list = parseExcelData(usersheetList, file);
        System.out.println(list);
        try {
            System.out.println(toObjectList(list, Require.class));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}