excel读取工具(apache-poi)

1.封装工具

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Workbook;
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;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public final class ExcelReader {
    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelReader.class);
    private static final DecimalFormat DECIMAL_FORMAT = new DecimalFormat("#.##");

    private ExcelReader() {
    }

    public static ExcelReader.ExcelType getExcelType(String fileName) {
        if (StringUtils.isBlank(fileName)) {
            return null;
        } else {
            return fileName.toLowerCase(Locale.CHINESE).endsWith("xlsx") ? ExcelReader.ExcelType.XLSX : ExcelReader.ExcelType.XLS;
        }
    }

    public static List<Map<String, Object>> readToMapList(File file) {
        return readToMapList((File)file, (String[])null);
    }

    public static List<Map<String, Object>> readToMapList(InputStream in, ExcelReader.ExcelType excelType) {
        return readToMapList((InputStream)in, (ExcelReader.ExcelType)excelType, (String[])null);
    }

    public static List<Map<String, Object>> readToMapList(InputStream in, ExcelReader.ExcelType excelType, String[] fields) {
        return readToMapList(in, excelType, fields, 0);
    }

    public static List<Map<String, Object>> readToMapList(File file, String[] fields) {
        return readToMapList(file, fields, 0);
    }

    public static List<Map<String, Object>> readToMapList(File file, String[] fields, Integer sheetIndex) {
        ArrayList dataList = new ArrayList();

        try {
            ExcelReader.ExcelType type = ExcelReader.ExcelType.XLS;
            if (file.getName().toLowerCase(Locale.CHINESE).endsWith("xlsx")) {
                type = ExcelReader.ExcelType.XLSX;
            }

            return readToMapList(new FileInputStream(file), type, fields, sheetIndex);
        } catch (FileNotFoundException var5) {
            LOGGER.error("file not found", var5);
            return dataList;
        }
    }

    public static List<Map<String, Object>> readToMapList(InputStream inputStream, ExcelReader.ExcelType excelType, String[] fields, Integer sheetIndex) {
        ArrayList dataList = new ArrayList();

        try {
            Object wb = ExcelReader.ExcelType.XLSX.equals(excelType) ? new XSSFWorkbook(inputStream) : new HSSFWorkbook(inputStream);

            try {
                int rows;
                HashMap fieldIndex;
                short headerCellCount;
                int i;
                String cellValue;
                int c;
                HashMap dataMap;
                if (ExcelReader.ExcelType.XLSX.equals(excelType) && wb instanceof XSSFWorkbook) {
                    XSSFSheet sheet = ((XSSFWorkbook)wb).getSheetAt(sheetIndex);
                    rows = sheet.getLastRowNum();
                    XSSFRow headerRow = sheet.getRow(0);
                    fieldIndex = new HashMap();
                    headerCellCount = headerRow.getLastCellNum();
                    i = 0;

                    label119:
                    while(true) {
                        if (i >= headerCellCount) {
                            i = 1;

                            while(true) {
                                if (i > rows) {
                                    break label119;
                                }

                                XSSFRow row = sheet.getRow(i);
                                if (row != null) {
                                    dataMap = new HashMap();

                                    for(c = 0; c < headerCellCount; ++c) {
                                        if (fieldIndex.get(c) != null) {
                                            XSSFCell cell = row.getCell(c);
                                            if (cell != null) {
                                                dataMap.put((String)fieldIndex.get(c), getValue(cell));
                                            }
                                        }
                                    }

                                    dataList.add(dataMap);
                                }

                                ++i;
                            }
                        }

                        XSSFCell cell = headerRow.getCell(i);
                        if (cell != null && CellType.STRING.equals(cell.getCellType())) {
                            cellValue = cell.getStringCellValue();
                            if (fields == null || fields.length == 0 || ArrayUtils.contains(fields, cellValue)) {
                                fieldIndex.put(i, cellValue);
                            }
                        }

                        ++i;
                    }
                } else if (wb instanceof HSSFWorkbook) {
                    HSSFSheet sheet = ((HSSFWorkbook)wb).getSheetAt(sheetIndex);
                    rows = sheet.getLastRowNum();
                    HSSFRow headerRow = sheet.getRow(0);
                    fieldIndex = new HashMap();
                    headerCellCount = headerRow.getLastCellNum();
                    i = 0;

                    label149:
                    while(true) {
                        if (i >= headerCellCount) {
                            i = 1;

                            while(true) {
                                if (i > rows) {
                                    break label149;
                                }

                                HSSFRow row = sheet.getRow(i);
                                if (row != null) {
                                    dataMap = new HashMap();

                                    for(c = 0; c < headerCellCount; ++c) {
                                        if (fieldIndex.get(c) != null) {
                                            HSSFCell cell = row.getCell(c);
                                            if (cell != null) {
                                                dataMap.put((String)fieldIndex.get(c), getValue(cell));
                                            }
                                        }
                                    }

                                    dataList.add(dataMap);
                                }

                                ++i;
                            }
                        }

                        HSSFCell cell = headerRow.getCell(i);
                        if (cell != null && CellType.STRING.equals(cell.getCellType())) {
                            cellValue = cell.getStringCellValue();
                            if (fields == null || fields.length == 0 || ArrayUtils.contains(fields, cellValue)) {
                                fieldIndex.put(i, cellValue);
                            }
                        }

                        ++i;
                    }
                }
            } catch (Throwable var17) {
                if (wb != null) {
                    try {
                        ((Workbook)wb).close();
                    } catch (Throwable var16) {
                        var17.addSuppressed(var16);
                    }
                }

                throw var17;
            }

            if (wb != null) {
                ((Workbook)wb).close();
            }
        } catch (IOException var18) {
            LOGGER.error(var18.getLocalizedMessage(), var18);
        }

        return dataList;
    }

    private static Object getValue(Cell cell) {
        CellType cellType = cell.getCellType();
        if (CellType.FORMULA.equals(cellType)) {
            return cell.getCellFormula();
        } else if (CellType.NUMERIC.equals(cellType) && DateUtil.isCellDateFormatted(cell)) {
            return cell.getDateCellValue();
        } else if (CellType.NUMERIC.equals(cellType)) {
            return DECIMAL_FORMAT.format(cell.getNumericCellValue());
        } else {
            return CellType.STRING.equals(cellType) ? cell.getStringCellValue() : null;
        }
    }

    public static String cutZero(String v) {
        if (v.contains(".")) {
            while(true) {
                if (v.lastIndexOf(48) != v.length() - 1) {
                    if (v.lastIndexOf(46) == v.length() - 1) {
                        v = v.substring(0, v.lastIndexOf(46));
                    }
                    break;
                }

                v = v.substring(0, v.lastIndexOf(48));
            }
        }

        return v;
    }

    public static enum ExcelType {
        XLS,
        XLSX;

        private ExcelType() {
        }
    }
}

2.读取excel 例子：

3.解析代码

ExcelReader.ExcelType type = ExcelReader.ExcelType.XLS;
            if (serviceFile.getName().toLowerCase().endsWith("xlsx")) {
                type = ExcelReader.ExcelType.XLSX;
            }
            try (InputStream inputStream = serviceFile.openInputStream()) {
                List<Map<String, Object>> maps = ExcelReader.readToMapList(inputStream, type, null, 0);
                task.setTotals(maps.size());
                int index = 0;
                for (Map<String, Object> item : maps) {
                    index++;
                    task.setProgress(index);
                    Entity entity = new Entity(ReportDetails.ID);
                    String report = (String) item.get("所属报表");
                    if (StringUtils.isBlank(report)) {
                        task.println("第" + index + "行数据【所属报表】为空");
                        continue;
                    }
                    entity.setProperty("report", report);
                    String columnName = (String) item.get("列名");
                    if (StringUtils.isBlank(columnName)) {
                        task.println("第" + index + "行数据【列名】为空");
                        continue;
                    }
                    entity.setProperty("columnName", columnName);

                    String numberOfLines = (String) item.get("行数");
                    if (StringUtils.isBlank(numberOfLines)) {
                        task.println("第" + index + "行数据【行数】为空");
                        continue;
                    }
                    entity.setProperty("numberOfLines", Convert.toInt(numberOfLines));

                    String sequenceNumber = (String) item.get("顺序号");
                    if (StringUtils.isBlank(sequenceNumber)) {
                        task.println("第" + index + "行数据【顺序号】为空");
                        continue;
                    }
                    entity.setProperty("sequenceNumber", Convert.toInt(sequenceNumber));

                    String rowSpan = (String) item.get("跨行");
                    if (StringUtils.isNotBlank(rowSpan)) {
                        entity.setProperty("rowSpan", Convert.toInt(rowSpan));
                    }

                    String colSpan = (String) item.get("跨列");
                    if (StringUtils.isNotBlank(colSpan)) {
                        entity.setProperty("colSpan", Convert.toInt(colSpan));
                    }

                    String columnKey = (String) item.get("取值key");
                    if (StringUtils.isNotBlank(columnKey)) {
                        entity.setProperty("columnKey", columnKey);
                    }
                    this.entityService.create(entity);
                }
                task.println("读取excels数据条数 ( " + maps.size() + " )");
            } catch (Exception e) {
                log.error(e.getMessage(), e);
            }