poi 4.0.0 导出excel

水深无声 2023-06-29 05:47 7阅读 0赞

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

以下代码

package com.dajie.mplan.audit.utils;
    
    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 javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import java.net.URLEncoder;
    import java.util.Iterator;
    import java.util.List;
    import java.util.Map;
    
    /**
     * 导出工具
     *
     * @author kangzhuang
     * @date 2020/01/10
     */
    public class ExcelExportUtils {
        public static final String W2003 = "2003";
    
        public static final String W2007 = "2007";
    
        public static final String XLS = "xls";
    
        public static final String XLSX = "xlsx";
    
        /**
         * 创建 Workbook
         *
         * @param version 2003 2007 xls xlsx
         * @return Workbook
         */
        public static Workbook createWorkbook(String version) {
            if (W2003.equalsIgnoreCase(version) || XLS.equalsIgnoreCase(version)) {
                return new HSSFWorkbook();
            } else if (W2007.equalsIgnoreCase(version) || XLSX.equalsIgnoreCase(version)) {
                return new XSSFWorkbook();
            } else {
                return new XSSFWorkbook();
            }
        }
    
        /**
         * 导出简单Excel
         *
         * @param titleList title list 文件第一行标题
         * @param dataList  data list  数据
         * @param version   2003 2007 xls xlsx
         * @return Workbook
         */
        public static Workbook exportSimpleExcelByMap(String[] titleList, List<Map<String, Object>> dataList, String version) {
            Row row;
            int rowIndex = 0;
            Workbook workbook = ExcelExportUtils.createWorkbook(version);
            Sheet sheet = workbook.createSheet();
    
            if (titleList != null && titleList.length > 0) {
                int cellIndex = 0;
                row = sheet.createRow(rowIndex++);
                for (String title : titleList) {
                    row.createCell(cellIndex++).setCellValue(title == null ? "" : title);
                }
            }
            if (dataList != null && !dataList.isEmpty()) {
                for (Map<String, Object> map : dataList) {
                    if (map == null || map.isEmpty()) {
                        continue;
                    }
                    int cellIndex = 0;
                    row = sheet.createRow(rowIndex++);
                    Iterator iterator = map.values().iterator();
                    while (iterator.hasNext()) {
                        Object value = iterator.next();
                        row.createCell(cellIndex++).setCellValue(value == null ? "" : value.toString());
                    }
                }
            }
    
            return workbook;
        }
    
        /**
         * 导出简单Excel
         *
         * @param titleList title list
         * @param dataList  data list
         * @param version   2003 2007 xls xlsx
         * @return Workbook
         */
        public static Workbook exportSimpleExcelByObject(String[] titleList, List<List<Object>> dataList, String version) {
            Row row;
            int rowIndex = 0;
            Workbook workbook = ExcelExportUtils.createWorkbook(version);
            Sheet sheet = workbook.createSheet();
    
            if (titleList != null && titleList.length > 0) {
                int cellIndex = 0;
                row = sheet.createRow(rowIndex++);
                for (String title : titleList) {
                    row.createCell(cellIndex++).setCellValue(title == null ? "" : title);
                }
            }
            if (dataList != null && !dataList.isEmpty()) {
                for (List<Object> objList : dataList) {
                    if (objList == null || objList.isEmpty()) {
                        continue;
                    }
                    int cellIndex = 0;
                    row = sheet.createRow(rowIndex++);
                    for (Object value : objList) {
                        row.createCell(cellIndex++).setCellValue(value == null ? "" : value.toString());
                    }
                }
            }
    
            return workbook;
        }
    
        /**
         * 填充模板sheet数据
         *
         * @param sheet    Sheet
         * @param dataList data list
         * @param rowIndex 从第几行开始 最小值0
         */
        public static void fillTemplateSheetByMap(Sheet sheet, List<Map<String, Object>> dataList, int rowIndex) {
            if (dataList == null || dataList.isEmpty() || rowIndex < 0) {
                return;
            }
            for (Map<String, Object> map : dataList) {
                if (map == null || map.isEmpty()) {
                    continue;
                }
                int cellIndex = 0;
                Row row = sheet.getRow(rowIndex);
                if (row == null) {
                    row = sheet.createRow(rowIndex);
                }
                Iterator iterator = map.values().iterator();
                while (iterator.hasNext()) {
                    Object value = iterator.next();
                    Cell cell = row.getCell(cellIndex);
                    if (cell == null) {
                        cell = row.createCell(cellIndex);
                    }
                    cell.setCellValue(value == null ? "" : value.toString());
                    cellIndex++;
                }
                rowIndex++;
            }
        }
    
        /**
         * 填充模板sheet数据
         *
         * @param sheet    Sheet
         * @param dataList data list
         * @param rowIndex 从第几行开始 最小值0
         */
        public static void fillTemplateSheetByObject(Sheet sheet, List<List<Object>> dataList, int rowIndex) {
            if (dataList == null || dataList.isEmpty() || rowIndex < 0) {
                return;
            }
            for (List<Object> objList : dataList) {
                if (objList == null || objList.isEmpty()) {
                    continue;
                }
                int cellIndex = 0;
                Row row = sheet.getRow(rowIndex);
                if (row == null) {
                    row = sheet.createRow(rowIndex);
                }
                for (Object value : objList) {
                    Cell cell = row.getCell(cellIndex);
                    if (cell == null) {
                        cell = row.createCell(cellIndex);
                    }
                    cell.setCellValue(value == null ? "" : value.toString());
                    cellIndex++;
                }
                rowIndex++;
            }
        }
    
        /**
         * 设置下载响应
         */
        public static void setDownLoadResponse(HttpServletRequest request, HttpServletResponse response, String fileName) throws Exception {
            String msie = "msie";
            String chrome = "chrome";
            String windows = "windows";
            String firefox = "firefox";
            String browserType = request.getHeader("User-Agent").toLowerCase();
            if (browserType.contains(firefox) || browserType.contains(chrome)) {
                fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");
            } else if (browserType.contains(msie) || browserType.contains(windows)) {
                fileName = URLEncoder.encode(fileName, "UTF-8");
            }
            // 重置
            response.reset();
            // 告知浏览器不缓存
            response.setHeader("pragma", "no-cache");
            response.setHeader("cache-control", "no-cache");
            response.setHeader("expires", "0");
            // 响应编码
            response.setCharacterEncoding("UTF-8");
            // 用给定的名称和值添加一个响应头
            response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
        }
    }
    
    
    
    @RequestMapping("downloadExcel")
        public void downloadExcel(HttpServletResponse response, HttpServletRequest request, int taskId) {
            LOGGER.info("BusinessSettlementDownController downloadExcel taskId{}", taskId);
    
            // 模板所需数据
            List<List<Object>> dataList = new ArrayList<>();
            //准备所需数据
    
            // 执行导出动作
            Workbook workbook = ExcelExportUtils.exportSimpleExcelByObject(TEMPLATE_COULMN, dataList, ExcelExportUtils.W2007);
            try {
                ExcelExportUtils.setDownLoadResponse(request, response, "学生.xlsx");
                workbook.write(response.getOutputStream());
            } catch (Exception e) {
                LOGGER.info("BusinessSettlementDownController downloadExcel error e:{}", e);
            }
        }

感觉不错，可留赞，谢谢