POI下载excel通用方法

朱雀 2024-03-17 17:28 60阅读 0赞

### POI下载excel通用方法 ###

> 最近遇到一个业务是需要下载excel，使用POI,这里记录一下实现过程

#### 1、导包 ####

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

#### 2.公共方法的编写 ####

package com.pingan.esbx.cassandra.util;
    
    import org.apache.commons.io.FileUtils;
    import org.apache.commons.lang3.StringUtils;
    import org.apache.commons.lang3.math.NumberUtils;
    import org.apache.poi.hssf.usermodel.*;
    import org.apache.poi.ss.usermodel.CellStyle;
    import org.apache.poi.ss.usermodel.IndexedColors;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.springframework.util.CollectionUtils;
    
    import javax.servlet.http.HttpServletResponse;
    import java.io.*;
    import java.lang.reflect.Method;
    import java.text.DateFormat;
    import java.text.SimpleDateFormat;
    import java.util.Date;
    import java.util.List;
    import java.util.Objects;
    
    public class ExcelDonload {
        
        /**
         * 26个英文字母表
         **/
        public static final String[] letters = {
        "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"};
        /**
         * 创建excel的行数(随描述 标题等的增加而增加)
         **/
        public static int rowNum = NumberUtils.INTEGER_ZERO;
        /**
         * 指定时间格式
         */
        public static DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    
        /**
         * 下载
         * ①tableTitles 与 titlesInfoKey 个数要一致，不能为空
         * ②fileName、title、subtitle、desc均可不传或者传null
         * ③titlesInfoKey 是与tableTitles对应的字段，需与实体类一致
         *
         * @param title         标题
         * @param resultList    数据
         * @param fileName      文件名
         * @param tableTitles   表头
         * @param titlesInfoKey 表头对应的字段
         * @param subtitle      副标题
         * @param desc          描述
         * @param response
         */
        public static <T> void donloadExcel(
                String title,
                List<T> resultList,
                String fileName,
                String[] tableTitles,
                String[] titlesInfoKey,
                String subtitle,
                String[] desc,
                HttpServletResponse response
    
        ) {
        
            //1.参数校验
            if (CollectionUtils.isEmpty(resultList)
                    || tableTitles.length <= NumberUtils.INTEGER_ZERO) {
        
                throw new RuntimeException("请求参数不正确！");
            }
            if (resultList.size() > 60000) {
        
                throw new RuntimeException("到处数据超过6w,不能导出！");
            }
            //2.创建Excel工作簿
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet();
            //3.设置excel的 描述、标题、副标题、表头
            setExcelTitle(workbook, sheet, title, tableTitles, subtitle, desc);
            //4.写入数据
            HSSFCellStyle dataStyle = workbook.createCellStyle();
            dataStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            dataStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            for (int i = 0; i < resultList.size(); i++) {
        
                T clazz = resultList.get(i);
                insertDataToCell(sheet, clazz, titlesInfoKey, dataStyle);
            }
            //5.返回数据量
            File file = returnExcelDataStream(fileName, workbook);
            //6.导出
            buildResponseExcelFile(file, response);
    
        }
    
        /**
         * 下载文件流
         *
         * @param file
         * @param response
         */
        private static void buildResponseExcelFile(File file, HttpServletResponse response) {
        
            InputStream in = null;
            OutputStream out = null;
    
            try {
        
                in = new FileInputStream(file.getPath());
                response.reset();
                response.setHeader("Content-disposition", "attachment;filename=" + new String(file.getName().getBytes(), "iso-8859-1"));
                response.setContentType("application/octet-stream");
                response.addHeader("Context-Length", "" + file.length());
                response.setCharacterEncoding("utf-8");
    
                out = response.getOutputStream();
                int b;
                while ((b = in.read()) != -1) {
        
                    out.write(b);
                }
            } catch (Exception e) {
        
                e.printStackTrace();
            } finally {
        
                if (in != null) {
        
                    try {
        
                        in.close();
                    } catch (IOException e) {
        
                        e.printStackTrace();
                    }
                }
                if (out != null) {
        
                    try {
        
                        out.close();
                    } catch (IOException e) {
        
                        e.printStackTrace();
                    }
                }
            }
    
            file.delete();
        }
    
        /**
         * 返回文件流
         *
         * @param fileName
         * @param workbook
         * @return
         */
        private static File returnExcelDataStream(String fileName, HSSFWorkbook workbook) {
        
    
            File file = null;
            try {
        
    
             
                String tempExcelPath = null;
                //创建临时目录
                File tempFile = File.createTempFile("tem", null);
                //这里采用相对路径,防止某些部署是非root权限。而导致拿不到根目录，从而不能下载(不能用绝对路径)
                String temPath = tempFile.getParent() + "/";
                if (StringUtils.isNotBlank(fileName)) {
        
                    tempExcelPath = temPath + File.separator + fileName + ".xls";
                } else {
        
                    tempExcelPath = temPath + File.separator + "report.xls";
                }
                file = new File(tempExcelPath);
                file.deleteOnExit();
                file.createNewFile();
                FileOutputStream fileOutputStream = FileUtils.openOutputStream(file);
                workbook.write(fileOutputStream);
                fileOutputStream.close();
            } catch (Exception e) {
        
                e.printStackTrace();
            }
            return file;
    
        }
    
    
        /**
         * 通过反射获取字段值
         *
         * @param sheet
         * @param clazz
         * @param titlesInfoKey
         * @param <T>
         */
        private static <T> void insertDataToCell(HSSFSheet sheet, T clazz, String[] titlesInfoKey, HSSFCellStyle dataStyle) {
        
            HSSFRow row = sheet.createRow(rowNum);
            row.setHeight((short) (15 * 20));
            for (int j = 0; j < titlesInfoKey.length; j++) {
        
                HSSFCell cell = row.createCell(j);
                cell.setCellStyle(dataStyle);
                //反射获取对象的值
                Object fieldValue = getValueByReflect(titlesInfoKey[j], clazz);
    
                String cellResult = null;
                //时间类型 Date 转换为字符串
                if (fieldValue instanceof Date) {
        
                    cellResult = getStringTime((Date) fieldValue);
                } else {
        
                    cellResult = Objects.isNull(fieldValue) ? "" : String.valueOf(fieldValue);
                }
    
                cell.setCellValue(cellResult);
            }
            rowNum += NumberUtils.INTEGER_ONE;
        }
    
    
        /**
         * 设置excel的 描述、标题、副标题、表头
         *
         * @param workbook
         * @param sheet
         * @param title
         * @param tableTitles
         * @param subtitle
         * @param desc
         */
        private static void setExcelTitle(HSSFWorkbook workbook,
                                          HSSFSheet sheet,
                                          String title,
                                          String[] tableTitles,
                                          String subtitle,
                                          String[] desc) {
        
            //合并单元格的行数 desc描述的数组项数+(beginTime && endTime)+title标题
            //$A$1:$I$1" 的含义是 第1行的A列到第1行的I列合并
            //计算要合并的行数
            int cellMergedRegionNm = NumberUtils.INTEGER_ZERO;
            //描述的长度
            int descLength = NumberUtils.INTEGER_ZERO;
            //当前要创建的excel的行数
            rowNum = NumberUtils.INTEGER_ZERO;
            if (desc != null) {
        
                descLength = desc.length;
                cellMergedRegionNm += descLength;
                rowNum += descLength;
            }
            //统计时间栏
            if (StringUtils.isNotBlank(subtitle)) {
        
                cellMergedRegionNm += NumberUtils.INTEGER_ONE;
            }
            //标题栏
            if (StringUtils.isNotBlank(title)) {
        
                cellMergedRegionNm += NumberUtils.INTEGER_ONE;
            }
            //获取表头的数量(他决定了合并单元格的列数)
            int tableTitleLength = tableTitles.length;
            //获取表头的长度对应的字母
            String letter = getLetterByNum(tableTitleLength);
            //设置需合并的行数与列数(循环次数是行数 ,$A$1:$I$1中的A代表第一列，I代表列的最后一列)
            for (int i = 0; i < cellMergedRegionNm; i++) {
        
                sheet.addMergedRegion(CellRangeAddress.valueOf("$A$" + (i + 1) + ":$" + letter + "$" + (i + 1)));
            }
            //设置颜色
            HSSFPalette palette = workbook.getCustomPalette();
            palette.setColorAtIndex((short) 9, (byte) 240, (byte) 240, (byte) 240);
            palette.setColorAtIndex((short) 10, (byte) 255, (byte) 153, (byte) 102);
            palette.setColorAtIndex((short) 11, (byte) 100, (byte) 149, (byte) 137);
            palette.setColorAtIndex((short) 12, (byte) 176, (byte) 196, (byte) 222);
            //字体
            HSSFFont workbookFont = workbook.createFont();
            workbookFont.setFontName("仿宋");
            workbookFont.setFontHeightInPoints((short) 14);
            //字体
            HSSFFont titleFont = workbook.createFont();
            titleFont.setFontName("黑体");
            titleFont.setFontHeightInPoints((short) 18);
            titleFont.setColor(IndexedColors.WHITE.index);
            //单元格的风格1
            HSSFCellStyle descStyleLong = workbook.createCellStyle();
            descStyleLong.setFont(workbookFont);
            descStyleLong.setFillForegroundColor((short) 9);
            descStyleLong.setFillPattern(CellStyle.SOLID_FOREGROUND);
            //单元格的风格2
            HSSFCellStyle descStyleShort = workbook.createCellStyle();
            descStyleShort.setFont(workbookFont);
            descStyleShort.setFillForegroundColor((short) 10);
            descStyleShort.setFillPattern(CellStyle.SOLID_FOREGROUND);
            //单元格的风格3
            HSSFCellStyle titleStyle = workbook.createCellStyle();
            titleStyle.setFont(titleFont);
            titleStyle.setFillForegroundColor((short) 11);
            titleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
            titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            //描述:设置描述，没有就不传
            HSSFRow row = null;
            HSSFCell cell = null;
            if (descLength > 0) {
        
                for (int i = 0; i < descLength; i++) {
        
                    row = sheet.createRow(i);
                    row.setHeight((short) (23 * 20));
                    cell = row.createCell(0);
                    cell.setCellValue(desc[i]);
                    if (i >= 4) {
        
                        row.setHeight((short) (40 * 20));
                        cell.setCellStyle(descStyleShort);
                    } else {
        
                        cell.setCellStyle(descStyleLong);
                    }
                }
            }
    
            //设置标题：没有就不传
            if (StringUtils.isNoneBlank(title)) {
        
    
                row = sheet.createRow(rowNum);
                row.setHeight((short) (60 * 20));
                cell = row.createCell(0);
                cell.setCellValue(title);
                cell.setCellStyle(titleStyle);
    
                rowNum += NumberUtils.INTEGER_ONE;
            }
            HSSFCellStyle styleTimeRange = workbook.createCellStyle();
            styleTimeRange.setFont(titleFont);
            styleTimeRange.setFillForegroundColor((short) 11);
            styleTimeRange.setFillPattern(CellStyle.FINE_DOTS);
            styleTimeRange.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            styleTimeRange.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            //设置时间范围
            if (StringUtils.isNotBlank(subtitle)) {
        
    
                row = sheet.createRow(rowNum);
                row.setHeight((short) (30 * 20));
                cell = row.createCell(0);
                cell.setCellValue(subtitle);
                cell.setCellStyle(styleTimeRange);
                rowNum += NumberUtils.INTEGER_ONE;
            }
            //设置表头字体
            HSSFFont tableTitleFont = workbook.createFont();
            tableTitleFont.setFontName("仿宋");
            tableTitleFont.setFontHeightInPoints((short) 14);
            tableTitleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            //表头风格
            HSSFCellStyle tableTitleStyle = workbook.createCellStyle();
            tableTitleStyle.setFont(tableTitleFont);
            tableTitleStyle.setFillForegroundColor((short) 12);
            tableTitleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
            tableTitleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            tableTitleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            tableTitleStyle.setLeftBorderColor(IndexedColors.WHITE.index);
            tableTitleStyle.setRightBorderColor(IndexedColors.WHITE.index);
            tableTitleStyle.setTopBorderColor(IndexedColors.WHITE.index);
            tableTitleStyle.setBottomBorderColor(IndexedColors.WHITE.index);
            tableTitleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            tableTitleStyle.setBorderTop(HSSFCellStyle.BORDER_THICK);
            tableTitleStyle.setBorderRight(HSSFCellStyle.BORDER_THICK);
            tableTitleStyle.setBorderBottom(HSSFCellStyle.BORDER_THICK);
    
            if (tableTitles != null && tableTitles.length > 0) {
        
                row = sheet.createRow(rowNum);
    
                row.setHeight((short) (41.2 * 20));
    
                for (int i = 0; i < tableTitles.length; i++) {
        
                    cell = row.createCell(i);
                    String tableTitle = tableTitles[i];
                    cell.setCellValue(tableTitle);
                    cell.setCellStyle(tableTitleStyle);
                    //根据标题长短设置单元格的宽窄
                    sheet.setColumnWidth(i, (tableTitle.length() * 5) * 256);
    
                }
                rowNum += NumberUtils.INTEGER_ONE;
            }
        }
    
        private static String getLetterByNum(int tableTitleLength) {
        
            return letters[tableTitleLength - NumberUtils.INTEGER_ONE];
        }
    
        /**
         * 返回指定时间的指定字符串格式 yyyy-MM-dd HH:mm:ss
         * SimpleDateFormat 线程不安全 所以枷锁
         */
        public synchronized static String getStringTime(Date date) {
        
            String format = dateFormat.format(date);
            return format;
        }
    
        /**
         * 通过反射获取字段值(通过get方法)
         *
         * @param fieldName
         * @param t
         * @param <T>
         * @return
         */
        public static <T> Object getValueByReflect(String fieldName, T t) {
        
            String methodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
            try {
        
                Method method = t.getClass().getMethod(methodName);
                Object fieldValue = method.invoke(t);
                return fieldValue;
            } catch (Exception e) {
        
                return null;
            }
        }
    }

#### 3.调用示例 ####

@RestController
    @RequestMapping("/excel")
    public class ExcelController {
        
        private static final String[] TITLES_NONEXP_INFO_KEY = {
        
                "time",
                "consumerCompany",
                "consumerSystem",
                "consumerCode",
                "providerCompany",
                "providerSystem",
                "providerCode",
                "failCount",
                "failRatio",
                "totalCount"
        };
        private static final String[] TITLES_NONEXP_INFO = {
        
                "时  间", "消费方公司", "消费方系统",
                "消费方编码", "服务方公司",
                "服务方系统", "服务方编码", "失败次数", "失败率", "总数"
        };
    
    
        @GetMapping("/exportExcel")
        public void exportExcel(HttpServletRequest request, HttpServletResponse response) {
        
            String title = "INVALID 月报表";
            List<ReportDTO> list = new ArrayList<>();
            String inputParams = "111111";
    
            ReportDTO reportDTO = new ReportDTO();
            reportDTO.setTime(new Date());
            reportDTO.setConsumerCompany("集团总部");
            reportDTO.setConsumerSystem("文件处理系统");
            reportDTO.setConsumerCode("EIP_DPS");
            reportDTO.setProviderCompany("中国科技");
            reportDTO.setProviderSystem("中国智者统一搜索平台");
            reportDTO.setProviderCode("PA011-ZHIZHE-USP-SE_PZHIZHE_ESG");
            reportDTO.setFailCount(100);
            reportDTO.setFailRatio(0.34);
            reportDTO.setTotalCount(340);
    
            list.add(reportDTO);
            String fileName = "我的测试";
            String[] desc = {
        
                    "报表中数据排序方法:",
                    "1.选中待排序的数据区域",
                    "2.excel菜单栏：【开始】->【格式】->【设置单元格】->【对齐】->【文本控制】:取消\"合并单元格\"选项\"",
                    "3.excel菜单栏:【开始】->【排序和筛选】->【自定义排序】",
                    "查询条件：",
                    JSON.toJSONString("这里可以写自己的查询条件")
            };
            String beginTime = "2023-7-6";
            String endTime = "2023-7-7";
            String subtitle = "统计时间范围【" + beginTime + "," + endTime + "】";
            ExcelDonload.donloadExcel(
                    title,
                    list,
                    fileName,
                    TITLES_NONEXP_INFO,
                    TITLES_NONEXP_INFO_KEY,
                    subtitle,
                    desc,
                    response);
    
        }
    }

#### 4.实体类 ####

@Data
    public class ReportDTO implements Serializable {
        
        private Date time;
        private String consumerCompany;
        private String consumerSystem;
        private String consumerCode;
        private String providerCompany;
        private String providerSystem;
        private String providerCode;
        private Integer failCount;
        private Double failRatio;
        private Integer totalCount;
    }

#### 5.下载效果 ####

![在这里插入图片描述][cdb8123aacbf4ef19f1ad53132955136.png]

[cdb8123aacbf4ef19f1ad53132955136.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/557dedcb68bb437296ebe789990348f6.png