ExcelUtil-读取Excel工具类

在日常开发中，经常遇到读取Excel的情况。往往在处理Excel时，我们一般需要解析Excel内容的列名以及列名对应下面的内容，即列名为一个集合，列名对应的内容为key-value形式，key为列名，value为值。因此自定义一个ExcelUtil来方便处理Excel内容

---

代码：

引入POI依赖：

<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi-ooxml</artifactId>
   <version>5.1.0</version>
</dependency>

public class ExcelUtil {

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

    /**
     * 响应给httpResponse
     * @param response
     * @param fileName
     * @param headList
     * @param rows
     */
    public static void write2Response(HttpServletResponse response, String fileName, List<String> headList, List<ArrayList<String>> rows) {
        response.setHeader("Content-Disposition", "attachment;filename="+fileName+".xlsx");
        response.setContentType("application/octet-stream;charset=UTF-8");
        try (OutputStream outputStream = response.getOutputStream(); Workbook workbook = write(headList, rows);){
            workbook.write(outputStream);
        } catch (Exception e) {
            logger.error("生成excel失败", e);
        }
    }

    /**
     * 读取excel
     * @param inputStream
     * @return
     */
    public static ExcelReaderBuilder read(InputStream inputStream) {
        return new ExcelReaderBuilder(inputStream);
    }

    private static Workbook write(List<String> headList, List<ArrayList<String>> rows) {
        ExcelWriterBuilder excelWriterBuilder = new ExcelWriterBuilder();
        return excelWriterBuilder.sheetName("数据").head(new ArrayList<>(headList)).data(new ArrayList<>(rows)).build();
    }

    private static class ExcelWriterBuilder {

        private SXSSFWorkbook workbook;

        private SXSSFSheet sheet;

        private CellStyle headStyle;

        public ExcelWriterBuilder() {
            workbook = new SXSSFWorkbook();
            sheet = workbook.createSheet();
            headStyle = workbook.createCellStyle();
            Font font = workbook.createFont();
            font.setBold(true);
            headStyle.setFont(font);
        }

        /**
         * sheet页的名称
         * @param sheetName
         * @return
         */
        public ExcelWriterBuilder sheetName(String sheetName) {
            workbook.setSheetName(0, sheetName);
            return this;
        }

        /**
         * 设置excel头，强制使用ArrayList，在使用get方法时，时间复杂度为O（1）
         * @param headList
         * @return
         */
        public ExcelWriterBuilder head(ArrayList<String> headList) {
            SXSSFRow headRow = sheet.createRow(0);
            for (int i = 0, length = headList.size(); i < length; i++) {
                sheet.setColumnWidth(i, 6000);
                SXSSFCell cell = headRow.createCell(i);
                cell.setCellType(CellType.STRING);
                cell.setCellStyle(headStyle);
                cell.setCellValue(headList.get(i));
            }
            return this;
        }

        public ExcelWriterBuilder data(ArrayList<ArrayList<String>> dataList) {
            for (int i = 0, sizei = dataList.size(); i < sizei; i++) {
                SXSSFRow sheetRow = sheet.createRow(1 + i);
                ArrayList<String> rowList = dataList.get(i);
                for (int j = 0, sizej = rowList.size(); j < sizej; j++) {
                    SXSSFCell sheetRowCell = sheetRow.createCell(j);
                    sheetRowCell.setCellType(CellType.STRING);
                    sheetRowCell.setCellValue(rowList.get(j));
                }
            }
            return this;
        }

        public Workbook build() {
            return workbook;
        }

    }

    public static class ExcelReaderBuilder {

        private XSSFWorkbook workbook;

        private XSSFSheet sheet;

        public ExcelReaderBuilder(InputStream inputStream) {
            try {
                // workbook = WorkbookFactory.create(inputStream);
                workbook = new XSSFWorkbook(inputStream);
                sheet = workbook.getSheetAt(0);
            } catch (Exception e) {
                // logger.error(e.getMessage(), e);
            }
        }

        public List<String> head() {
            Row row = sheet.getRow(0);
            return getRowData(row);
        }

        private List<String> getRowData(Row row) {
            if (row == null) {
                return Collections.emptyList();
            }
            Cell cell;
            List<String> dataList = new ArrayList<>();
            int cellNum = row.getLastCellNum();
            for (int i=0; i<cellNum; i++) {
                cell = row.getCell(i);
                if (cell != null && CellType.STRING != cell.getCellType()) {
                    cell.setCellType(CellType.STRING);
                }
                dataList.add(cell==null?null:cell.getStringCellValue());
            }
            return dataList;
        }

        public List<List<String>> data() {
            Row row;
            List<List<String>> list = new ArrayList<>();
            int rowNum = sheet.getPhysicalNumberOfRows();
            for (int i=1; i<rowNum;i++) {
                row = sheet.getRow(i);
                List<String> rowData = getRowData(row);
                list.add(rowData);
            }
            return list;
        }

        public List<Map<String, String>> dataMap() {
            List<String> head = head();
            List<List<String>> data = data();
            List<Map<String, String>> dataMap = new LinkedList<>();
            for (List<String> row : data) {
                Map<String, String> rowMap = new HashMap<>(row.size());
                for (int i = 0, rowSize = row.size(); i < rowSize; i++) {
                    rowMap.put(head.get(i), row.get(i));
                }
                dataMap.add(rowMap);
            }
            return dataMap;
        }

    }

}