poi excel 下载

墨蓝 2022-12-27 14:15 210阅读 0赞

package com.leinovo.qes.portal.modules.report.controller;
    
    import io.swagger.annotations.Api;
    import io.swagger.annotations.ApiOperation;
    import org.apache.poi.hssf.util.HSSFColor;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.CellStyle;
    import org.apache.poi.ss.usermodel.Font;
    import org.apache.poi.ss.usermodel.HorizontalAlignment;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.VerticalAlignment;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import javax.servlet.http.HttpServletResponse;
    import java.io.FileNotFoundException;
    import java.io.IOException;
    import java.io.OutputStream;
    import java.util.Arrays;
    import java.util.List;
    
    
    /**
     * @description: ExcelController
     * @author: leiming5
     * @date: 2020-10-28 17:35
     */
    @Api(tags = "Excel Download 接口")
    @RestController
    public class ExcelController { 
        
        /**
         * EXCEL 生成，然后，下载
         *
         * @param response 响应对象
         * @author: leiming5
         */
        @ApiOperation("report/download")
        @GetMapping("report/download")
        public void test22(HttpServletResponse response) { 
    
            String fileName = "template.XLSX";
            response.setHeader("content-disposition", "attachment;filename=" + fileName);
    
            try (OutputStream out = response.getOutputStream()) { 
    
                Workbook workbook = new XSSFWorkbook();
    
                //生成一个表格
                Sheet sheet = workbook.createSheet("模板Excel");
    
                // 获取表格数据
                List<List<String>> excelHead = generateHead();
                List<List<Object>> excelData = generateData();
    
                createExcel(sheet, excelHead, excelData, workbook);
    
                workbook.write(out);
    
            } catch (FileNotFoundException e) { 
                e.printStackTrace();
            } catch (IOException e) { 
                e.printStackTrace();
            }
        }
    
        /**
         * 将表头和数据一起写入excel,并设置样式
         *
         * @param sheet     表格
         * @param excelHead 表头数据
         * @param excelData 表个数据
         * @param workbook  当前工作簿
         * @author: leiming5
         */
        private void createExcel(Sheet sheet, List<List<String>> excelHead, List<List<Object>> excelData, Workbook workbook) { 
    
            // 放入表头字段
            for (int i = 0; i < excelHead.size(); i++) { 
    
                Row row = sheet.createRow(i);
                List<String> list = excelHead.get(i);
    
                for (int j = 0; j < list.size(); j++) { 
    
                    Cell cell = row.createCell(j);
    
                    cell.setCellValue(list.get(j));
                    cell.setCellStyle(getCellStyle(workbook, "head"));
    
                    if (i == 1) { 
    //                    sheet.autoSizeColumn(j); // 设置自适应列宽
                        sheet.setColumnWidth(j, 18 * 256);
                    }
                }
            }
    
            // 合并标题行
            CellRangeAddress range = new CellRangeAddress(0, 0, 0, excelHead.get(1).size() - 1);
            sheet.addMergedRegion(range);
            // 设置列宽
    //        sheet.setDefaultColumnWidth(18); // 设置默认列宽
            sheet.setColumnWidth(0, 26 * 256);
            sheet.setColumnWidth(1, 26 * 256);
    
            // 放入数据
            for (int i = 0; i < excelData.size(); i++) { 
    
                Row row = sheet.createRow(i + excelHead.size());
                List<Object> list = excelData.get(i);
    
                for (int j = 0; j < list.size(); j++) { 
    
                    Cell cell = row.createCell(j);
                    Object o = list.get(j);
    
                    String str = String.valueOf(o);
                    if (isNumeric(str)) { 
                        cell.setCellValue(Double.parseDouble(str));
                    } else { 
                        cell.setCellValue(str);
                    }
                    cell.setCellStyle(getCellStyle(workbook, "body"));
                }
            }
    
        }
    
        /**
         * 判断表格数据类型
         *
         * @param str 原始数据
         * @return true:数字类型，false:字符类型
         * @author: leiming5
         */
        public static boolean isNumeric(String str) { 
    
            try { 
                Double.valueOf(str);
            } catch (NumberFormatException e) { 
                return false;
            }
    
            return true;
        }
    
        /**
         * 设置单元个样式
         *
         * @param workbook 工作簿
         * @param type     自定义单元格类型，head:表头，body:表格主体
         * @return 单元格样式
         * @author: leiming5
         */
        private CellStyle getCellStyle(Workbook workbook, String type) { 
    
            // Sheet样式
            CellStyle columnHeadStyle = workbook.createCellStyle();
            // 背景色的设定 自动换行
            columnHeadStyle.setWrapText(true);
    
            //设置字体颜色
            Font font = workbook.createFont();
            short colorIndex;
            switch (type) { 
                case "head":
                    colorIndex = HSSFColor.HSSFColorPredefined.RED.getIndex();
                    break;
                case "body":
                    colorIndex = HSSFColor.HSSFColorPredefined.BLACK.getIndex();
                    break;
                default:
                    colorIndex = HSSFColor.HSSFColorPredefined.BLACK.getIndex();
            }
            font.setColor(colorIndex);
    
            //将字体设置应用到当前样式
            columnHeadStyle.setFont(font);
    
            //水平居中,垂直居中
            columnHeadStyle.setAlignment(HorizontalAlignment.CENTER);
            columnHeadStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            return columnHeadStyle;
        }
    
        /**
         * 模拟表格数据
         *
         * @return 表格数据
         * @author: leiming5
         */
        private List<List<Object>> generateData() { 
    
            List<Object> rowOne = Arrays.asList(1, "张三", "男", 18);
            List<Object> rowTwo = Arrays.asList(2, "李四", "女", 19);
            List<Object> rowTree = Arrays.asList(3, "李mao", "女", 21);
            return Arrays.asList(rowOne, rowTwo, rowTree);
    
        }
    
        /**
         * 模拟表头字段
         *
         * @return 表头字段
         * @author: leiming5
         */
        private List<List<String>> generateHead() { 
    
            List<String> headerOne = Arrays.asList("这是标题", "", "", "");
            List<String> headerTwo = Arrays.asList("序号", "姓名", "性别", "年龄");
            return Arrays.asList(headerOne, headerTwo);
        }
    }