POI复杂导出记录

　　

?
// 以下代码去除其他信息  仅供参考<br>List<Obj> a= demo.getOutboundReportDetail(null);
 
        Map<String, List<Obj>> map =
 
                a.stream().collect(Collectors.groupingBy(Obj::getSupplierName));
 
        HSSFWorkbook workbook = new HSSFWorkbook();
 
        // 设置第一个sheet的名称
 
        HSSFSheet sheet = workbook.createSheet("sheet1");
 
        sheet.setDefaultRowHeight((short) (2*256));
 
        sheet.setDefaultColumnWidth(20);
 
 
 
        String fileName = "";//设置文件名
 
        Cell cell = null;
 
        CellStyle style = excelUtil.getRow1CellStyle(workbook);//标题
 
        CellStyle row1CellStyle0 = excelUtil.getRow1CellStyle0(workbook);
 
        CellStyle rowCellStyle = excelUtil.getRow1CellStyle1(workbook);
        CellStyle row1CellStyle2 = excelUtil.getRow1CellStyle2(workbook);
 
        CellStyle row1CellStyle3 = excelUtil.getRow1CellStyle3(workbook);
 
        // 开始添加excel第一行表头（excel中下标是0）
 
        HSSFRow row = sheet.createRow(0);
 
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));//合并表头
 
        cell = row.createCell(0);
 
        style.setAlignment(HorizontalAlignment.CENTER);
 
        cell.setCellValue("*************");//标题
 
        cell.setCellStyle(style);
 
 
        BigDecimal count = list.stream().map(getCount).reduce(add);
 
        BigDecimal Money = list.stream().map(getTotalPrice).reduce(add);
 
 
 
        int rowNum = 1;
 
        for (String string : map.keySet()) {
 
            List<OutboundProductVO> excelList =
 
                    JSON.parseArray(JSON.toJSONString(map.get(string)), OutboundProductVO.class);
 
 
 
            BigDecimal sumCount = excelList.stream().map(OutboundProductVO::getCount).reduce(BigDecimal.ZERO,BigDecimal::add);
 
            BigDecimal sumTotal= excelList.stream().map(OutboundProductVO::getTotalPrice).reduce(BigDecimal.ZERO,BigDecimal::add);
 
 
 
            row = sheet.createRow(rowNum);
 
            cell = row.createCell(0);
 
            sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,0,6));
 
            cell.setCellValue("****"+string);
 
            cell.setCellStyle(row1CellStyle0);
 
            rowNum++;
 
            //表头
 
            row=sheet.createRow(rowNum);
 
            cell = row.createCell(0);
 
            cell.setCellStyle(rowCellStyle);
 
            cell.setCellValue("****");
 
            cell = row.createCell(1);
 
            cell.setCellStyle(rowCellStyle);
 
            cell.setCellValue("****");
 
            cell = row.createCell(2);
 
            cell.setCellStyle(rowCellStyle);
 
            cell.setCellValue("****");
 
            cell = row.createCell(3);
 
            cell.setCellStyle(rowCellStyle);
 
            cell.setCellValue("**");
 
            cell = row.createCell(4);
 
            cell.setCellStyle(rowCellStyle);
 
            cell.setCellValue("**");
 
            cell = row.createCell(5);
 
            cell.setCellStyle(rowCellStyle);
 
            cell.setCellValue("**");
 
            cell = row.createCell(6);
 
            cell.setCellStyle(rowCellStyle);
 
            cell.setCellValue("***");
 
            rowNum++;
 
            //表体数据
 
            for (int i = 0; i <= excelList.size(); i++) {
 
                row=sheet.createRow(rowNum);
 
                if (i == excelList.size()){
 
                    cell = row.createCell(0);
 
                    cell.setCellStyle(row1CellStyle2);
 
                    sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,0,4));//合并前面的表头
 
                    cell.setCellValue("**");
 
                    cell = row.createCell(1);
 
                    cell.setCellStyle(row1CellStyle2);
 
                    cell = row.createCell(2);
 
                    cell.setCellStyle(row1CellStyle2);
 
                    cell = row.createCell(3);
 
                    cell.setCellStyle(row1CellStyle2);
 
                    cell = row.createCell(4);
 
                    cell.setCellStyle(row1CellStyle2);
 
                    cell = row.createCell(5);
 
                    cell.setCellStyle(row1CellStyle2);
 
                    cell.setCellValue(Count);
 
                    cell = row.createCell(6);
 
                    cell.setCellStyle(row1CellStyle2);
 
                    cell.setCellValue(Total);
 
                }else {
 
                    cell = row.createCell(0);
 
                    cell.setCellValue(Code);
 
                    cell = row.createCell(1);
 
                    cell.setCellValue(Name);
 
                    cell = row.createCell(2);
 
                    cell.setCellValue(Specification);
 
                    cell = row.createCell(3);
 
                    cell.setCellValue(Price);
 
                    cell = row.createCell(4);
 
                    cell.setCellValue(Unit);
 
                    cell = row.createCell(5);
 
                    cell.setCellValue(Count);
 
                    cell = row.createCell(6);
 
                    cell.setCellValue(Price);
 
                }
 
                rowNum++;
 
            }
 
            rowNum++;
 
        }
 
 
        row=sheet.createRow(rowNum);
 
        cell = row.createCell(0);
 
        cell.setCellValue("**");
 
        cell.setCellStyle(row1CellStyle3);
 
        cell = row.createCell(1);
 
        cell.setCellStyle(row1CellStyle3);
 
        cell = row.createCell(2);
 
        cell.setCellStyle(row1CellStyle3);
 
        cell = row.createCell(3);
 
        cell.setCellStyle(row1CellStyle3);
 
        cell = row.createCell(4);
 
        cell.setCellStyle(row1CellStyle3);
 
        cell = row.createCell(5);
 
        cell.setCellStyle(row1CellStyle3);
 
        cell.setCellValue(count);
 
        cell = row.createCell(6);
 
        cell.setCellStyle(row1CellStyle3);
 
        cell.setCellValue(Money);
 
 
 
 
 
 
 
        response.setCharacterEncoding("UTF-8");
 
        response.setHeader("content-Type", "application/vnd.ms-excel;charset=UTF-8");
 
        response.setHeader("Content-Disposition",
 
                    "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
 
        workbook.write(response.getOutputStream());
<br>public class ExcelUtil {
    public static void downLoadExcel(String fileName, HttpServletResponse response, HttpServletRequest request, Workbook workbook) throws IOException {
        ServletOutputStream outputStream = null;
        try {
 
            try {
                // 设置文件名的编码格式
                fileName = new String(fileName.getBytes(), "ISO8859-1");
            } catch (UnsupportedEncodingException e) {
 
            }
            // 设置文件的编码格式
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            // 设置响应头添加附件
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            // 设置不需要缓存
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
 
            response.addHeader("fileName",fileName);
 
            outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
        } catch (IOException e) {
 
        } finally {
            workbook.close();
            if (outputStream != null) {
                outputStream.flush();
                outputStream.close();
            }
        }
    }
 
    
    public static CellStyle getRow1CellStyle(Workbook workbook) {
 
        CellStyle cellStyle = workbook.createCellStyle();
        //设置水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置字体
        Font font = workbook.createFont();
        //设置字号
        font.setFontHeightInPoints((short) 15);
        //设置是否为斜体
        font.setItalic(false);
        //设置是否加粗
        font.setBold(true);
        //设置字体颜色
        font.setColor(IndexedColors.BLACK.index);
        cellStyle.setFont(font);
        return cellStyle;
    }
 
    public static CellStyle getRow1CellStyle0(Workbook workbook) {
 
        CellStyle cellStyle = workbook.createCellStyle();
 
        //设置字体
        Font font = workbook.createFont();
        //设置字号
        font.setFontHeightInPoints((short) 12);
        //设置是否为斜体
        font.setItalic(false);
        //设置是否加粗
        font.setBold(true);
        //设置字体颜色
        font.setColor(IndexedColors.BLACK.index);
        cellStyle.setFont(font);
 
        return cellStyle;
    }
 
    public static CellStyle getRow1CellStyle1(Workbook workbook) {
 
        CellStyle cellStyle = workbook.createCellStyle();
        //设置下边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        //设置上边框
        cellStyle.setBorderTop(BorderStyle.THIN);
        //设置走边框
        cellStyle.setBorderLeft(BorderStyle.THIN);
        //设置右边框
        cellStyle.setBorderRight(BorderStyle.THIN);
 
        //设置字体
        Font font = workbook.createFont();
        //设置字号
        font.setFontHeightInPoints((short) 12);
        //设置是否为斜体
        font.setItalic(false);
        //设置是否加粗
        font.setBold(true);
        //设置字体颜色
        font.setColor(IndexedColors.BLACK.index);
        cellStyle.setFont(font);
        //设置背景
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.index);;
        return cellStyle;
    }
 
    public static CellStyle getRow1CellStyle2(Workbook workbook) {
 
        CellStyle cellStyle = workbook.createCellStyle();
        //设置下边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        //设置上边框
        cellStyle.setBorderTop(BorderStyle.THIN);
        //设置走边框
        cellStyle.setBorderLeft(BorderStyle.THIN);
        //设置右边框
        cellStyle.setBorderRight(BorderStyle.THIN);
 
        //设置字体
        Font font = workbook.createFont();
        //设置字号
        font.setFontHeightInPoints((short) 11);
        //设置是否为斜体
        font.setItalic(false);
        //设置是否加粗
        font.setBold(true);
        //设置字体颜色
        font.setColor(IndexedColors.BLACK.index);
        cellStyle.setFont(font);
 
 
        return cellStyle;
    }
 
    public static CellStyle getRow1CellStyle3(Workbook workbook) {
 
        CellStyle cellStyle = workbook.createCellStyle();
 
        //设置下边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        //设置上边框
        cellStyle.setBorderTop(BorderStyle.THIN);
        //设置走边框
        cellStyle.setBorderLeft(BorderStyle.THIN);
        //设置右边框
        cellStyle.setBorderRight(BorderStyle.THIN);
 
        //设置字体
        Font font = workbook.createFont();
        //设置字号
        font.setFontHeightInPoints((short) 12);
        //设置是否为斜体
        font.setItalic(false);
        //设置是否加粗
        font.setBold(true);
        //设置字体颜色
        font.setColor(IndexedColors.BLACK.index);
        cellStyle.setFont(font);
        //设置背景
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.index);;
        return cellStyle;
    }
 
 
 
}<br>大致效果

 

 

　　

posted @   DarkerbeS  阅读(45)  评论(0)  编辑  收藏  举报
相关博文：
·  elementui 动态表格
·  stream流根据一个字段分组同时提取另一字段
·  java自定义导出Excel格式
·  poi报表导出 复杂导出 指定合并列和对比重复列合并行动态导出
·  poi异步导出、excel异步导出-通用-仅限单组数据导出
阅读排行：
· 10年+ .NET Coder 心语 ── 封装的思维：从隐藏、稳定开始理解其本质意义
· 【设计模式】告别冗长if-else语句：使用策略模式优化代码结构
· 提示词工程——AI应用必不可少的技术
· 字符编码：从基础到乱码解决
· 地球OL攻略 —— 某应届生求职总结
点击右上角即可分享
微信分享提示
AI IDE Trae