cn.meteor.module.util.office.ExcelUtils Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of meteor-util Show documentation
A Java Framework for JavaEE application reference architecture.
The newest version!
package cn.meteor.module.util.office;

import java.io.InputStream;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.xssf.usermodel.XSSFWorkbook;

import cn.meteor.module.util.lang.StringExtUtils;

public class ExcelUtils {
	
	private final static String excel2003L =".xls";    //2003- 版本的excel  
    private final static String excel2007U =".xlsx";   //2007+ 版本的excel
    

    public  static Workbook getWorkbook(InputStream inputStream,String fileName) throws Exception{  
		Workbook workbook = null;  
        String fileType = fileName.substring(fileName.lastIndexOf("."));  
        if(excel2003L.equals(fileType)){  
            workbook = new HSSFWorkbook(inputStream);  //2003-  
        }else if(excel2007U.equals(fileType)){  
            workbook = new XSSFWorkbook(inputStream);  //2007+  
        }else{  
            throw new Exception("解析的文件格式有误！");  
        }  
        return workbook;  
    }
    
    public static List> getRowListFromWorkbook(Workbook workbook, Map headerMapper) throws Exception {
    	return getRowListFromWorkbook(workbook, headerMapper, null);
    }

	
	public static List> getRowListFromWorkbook(Workbook workbook, Map headerMapper, Integer fetchRowSize) throws Exception {
		List> rowList = new ArrayList>();
		if (null == workbook) {
			throw new Exception("创建Excel工作薄为空！");
		}
		// 遍历Excel中所有的sheet
		for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
			Sheet sheet = workbook.getSheetAt(sheetIndex);
			if (sheet == null) {
				continue;
			}

			int fetchRowMaxIndex = sheet.getLastRowNum();
			if(fetchRowSize!=null) {
				fetchRowMaxIndex = fetchRowSize-1;
			}
			// 遍历当前sheet中的所有行
			for (int rowNum = sheet.getFirstRowNum(); rowNum <=sheet.getLastRowNum() && rowNum<=fetchRowMaxIndex; rowNum++) {//下标从0开始
				Row row = sheet.getRow(rowNum);
				if (row == null) {// 如果是null行，则跳过
					continue;
				}
				
				if (sheetIndex == 0 && rowNum == row.getFirstCellNum()) {// 如果是第一个sheet的第一行，添加到map中
					// 遍历所有的列
					for (short colIndex = row.getFirstCellNum(); colIndex  oneRow = new ArrayList