• Springboot+POI实现excel生成下载进阶版(单元格合并,多Sheet,各种样式处理)


     上周五来了新的需求,基本上我写的还款那一系列流程不要了(我好悲伤,当时写了很久的,逻辑复杂的写的我很骄傲),新的变成如上所示(仅仅一部分),勾选几笔后生成一个excel表格,不同的融资编号所引发的那堆数据生成的表格放在不同的sheet页。模板如下:

       哎呦,这个表格不太规则哟~之前写的比较简单,中规中矩,一行字段标题对应几行数据,连表头都没有,但这个是双重表格,而且有16个以上的合并单元格,表格带边框,左对齐,右对齐,居中的数据都有,还有加粗的,大小不一的数据,一看写出代码就少不了。于是最开始的想法是偷个懒,坐等同事的在上一步业务中对每笔单号生成各自的excel,我在这一步拿着所有的融资编号去找到他们在上一步保存的excel,然后写套代码做一个excel的数据合并,将不同的excel合到一个excel中,放入不同sheet页(来夸夸我,我是不是很机智),结果我成功了,但我又失败了。。。我在网上找了套代码进行合并的,然后无脑的修改后还是运行,生成本地成功了,但是放在swagger上测试下载时,一直说文件打不开。。整到下班也一直说格式问题文件打不开,网上各种方式都试了,,果然,别人的代码不要随意套用,还是自己写一套比较实用。

      周末的日子比较无聊,望着空荡荡的房子,安静的过分,无聊的我决定找点事情做,打开电脑打开IDEA,将周五决定偷懒的工作整一下,于是各种通过代码调整合并单元格,样式诸类。。。基本上把整个思路全写完了,大面积代码,剩下的就是那些数据在哪取我不清楚,只能先造点假数据了。。周一来了后,问了下同事数据取的表在哪,然后把代码补充下,再根据生成的excel样式对比模板表,调了调,大功告成。因为第一次处理生成这种样式的excel,所以纪念一下,有需要的朋友可以当个参考,注释我都写的很明确了,因为我在main方法里面加个测试数据,所以代码粘下来可以直接运行看效果。

    代码如下:

    package cn.exrick.xboot.common.utils;
    
    import org.apache.poi.hssf.usermodel.*;
    import org.apache.poi.ss.util.CellRangeAddress;
    import javax.servlet.http.HttpServletResponse;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.OutputStream;
    import java.io.UnsupportedEncodingException;
    import java.text.SimpleDateFormat;
    import java.util.*;
    
    /**
     * @author zae
     */
    public class DownLoadPayMsgUtil {
    
        public static void main(String[] args) {
            //示例数据:
            //申请信息数据
            String[] apllyTitlesKey = new String[]{"txn_no","deliver_contact","order_id","order_date","deliver_address","account_period","commodity_name","trade_amount"};
            String[] applyTitle = new String[]{"序号","产融平台融资编号","发货人","订单编号","订单日期","工厂地址","收货地址","商品名称","运费金额"};
            Map<String, Object> randomMapOne = getRandomMap(apllyTitlesKey);
            Map<String, Object> randomMapTwo = getRandomMap(apllyTitlesKey);
            Map<String, Object> randomMapThree = getRandomMap(apllyTitlesKey);
            Map<String, Object> randomMapFour = getRandomMap(apllyTitlesKey);
            List<Map<String,Object>> applyContantList = Arrays.asList(randomMapOne,randomMapTwo,randomMapThree,randomMapFour);//实际工作中在数据库中获取
            String[][] applyContent = DownLoadPayMsgUtil.convertListToArray(apllyTitlesKey, applyContantList);//list转换二维数组
    
            //支付信息数据
            String[] payTitlesKey = new String[]{"carry_date","carry_no","carry_contact_user","carry_plate","carry_amount","account_bank","account_no"};
            String[] payTitle = new String[]{"承运时间","提单号","承运人","承运车号","运费金额","开户行","账号"};
            Map<String, Object> mapOne = getRandomMap(payTitlesKey);
            Map<String, Object> mapTwo = getRandomMap(payTitlesKey);
            List<Map<String,Object>> payContantList = Arrays.asList(mapOne,mapTwo);//实际工作中在数据库中获取
            String[][] payContent = DownLoadPayMsgUtil.convertListToArray(payTitlesKey, payContantList);//list转换二维数组
    
            //前置内容数据
            List<String> beforeTitle = Arrays.asList("申请人:","开户行:","名称:","账号:");
            List<String> beforeContent = Arrays.asList("zae","工商银行","zae的账户","274630575");//实际工作在数据库中取得
    
            Map<String,Object> dataMap = new HashMap<>();
            dataMap.put("applyTitle",applyTitle);//申请单表字段组
            dataMap.put("applyContent",applyContent);//申请单表内容数据
            dataMap.put("payTitle",payTitle);//支付信息表字段组
            dataMap.put("payContent",payContent);//支付信息表内容数据
            dataMap.put("beforeTitle",beforeTitle);//前置内容字段组
            dataMap.put("beforeContent",beforeContent);//前置内容数据
            dataMap.put("applyAmount","2000");//申请金额总和
            dataMap.put("payAmount","3000");//运费金额总和
    
            List<Map<String,Object>> dataList = new ArrayList<>();
            Map<String,Object> dataMap2 = new HashMap<>(dataMap);
            dataList.add(dataMap);//多个map情况下会生成多个sheet页
            dataList.add(dataMap2);
    
            //根据传入的数据,生成一个excel对象
            HSSFWorkbook wb = DownLoadPayMsgUtil.getHSSFWorkbook(dataList);
            //导出下载
            //DownLoadPayMsgUtil.exportExcel(wb,"提款申请.xls",null);
            //保存在本地
            exportExcelToLocally(wb,"D:\提款申请.xls");
    
        }
    
    
        /**
         * 用传入的sheet名称,标题,内容生成HSSFWorkbook对象。
         * @param dataList
         * @return HSSFWorkbook
         */
        public static HSSFWorkbook getHSSFWorkbook(List<Map<String,Object>> dataList){
            // 第一步,创建一个HSSFWorkbook,对应一个Excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
            //第二步,创建确定位置的的合并单元格对象
            CellRangeAddress callRangeAddress1 = new CellRangeAddress(2,2,0,8);//起始行,结束行,起始列,结束列
            CellRangeAddress callRangeAddress2 = new CellRangeAddress(3,3,0,1);//起始行,结束行,起始列,结束列
            CellRangeAddress callRangeAddress3 = new CellRangeAddress(3,3,2,5);//起始行,结束行,起始列,结束列
            CellRangeAddress callRangeAddress4 = new CellRangeAddress(3,3,7,8);//起始行,结束行,起始列,结束列
            CellRangeAddress callRangeAddress5 = new CellRangeAddress(4,4,0,1);//起始行,结束行,起始列,结束列
            CellRangeAddress callRangeAddress6 = new CellRangeAddress(4,4,2,7);//起始行,结束行,起始列,结束列
            CellRangeAddress callRangeAddress7 = new CellRangeAddress(5,5,0,1);//起始行,结束行,起始列,结束列
            CellRangeAddress callRangeAddress8 = new CellRangeAddress(5,5,2,7);//起始行,结束行,起始列,结束列
            CellRangeAddress callRangeAddress9 = new CellRangeAddress(6,6,0,1);//起始行,结束行,起始列,结束列
            CellRangeAddress callRangeAddress10 = new CellRangeAddress(6,6,2,7);//起始行,结束行,起始列,结束列
            CellRangeAddress callRangeAddress11 = new CellRangeAddress(8,8,0,8);//起始行,结束行,起始列,结束列
    
            // 第三步,创建单元格样式(字体大小,是否加粗,是否加边框,对齐方式)
            HSSFCellStyle styleTitle = createCellStyle(wb,(short)11,true,false,"C");//标题头
            HSSFCellStyle styleBeforeTitle = createCellStyle(wb,(short)9,false,false,"R");//前置内容字段
            HSSFCellStyle styleBeforeData = createCellStyle(wb,(short)9,false,false,"F");//前置内容数据
            HSSFCellStyle styleTableTitle = createCellStyle(wb,(short)9,true,true,"C");//表头
            HSSFCellStyle styleTableContent = createCellStyle(wb,(short)9,false,true,"C");//表内容
    
    
            for(int k=1;k<=dataList.size();k++){
                Map<String,Object> dataMap = dataList.get(k-1);
                String []applyTitle = (String[]) dataMap.get("applyTitle");//申请信息表的字段组
                String [][]applyContent = (String[][]) dataMap.get("applyContent");//申请信息表的内容组
                String []payTitle = (String[]) dataMap.get("payTitle");//支付信息表的字段组
                String [][]payContent = (String[][]) dataMap.get("payContent");//支付信息表的内容组
                //第四步:创建未确定位置的合并单元格对象
                CellRangeAddress callRangeAddress12 = new CellRangeAddress(10+applyContent.length,10+applyContent.length,0,4);//起始行,结束行,起始列,结束列
                CellRangeAddress callRangeAddress13 = new CellRangeAddress(10+applyContent.length,10+applyContent.length,6,7);//起始行,结束行,起始列,结束列
                CellRangeAddress callRangeAddress14 = new CellRangeAddress(11+applyContent.length,11+applyContent.length,0,8);//起始行,结束行,起始列,结束列
                CellRangeAddress callRangeAddress15 = new CellRangeAddress(12+applyContent.length,12+applyContent.length,6,8);//起始行,结束行,起始列,结束列
                // 第五步,在workbook中添加一个sheet,对应Excel文件中的sheet
                HSSFSheet sheet = wb.createSheet("Sheet"+k);
                //设置列宽
                sheet.setColumnWidth(0, 256*15);
                sheet.setColumnWidth(1, 256*20);
                sheet.setColumnWidth(2, 256*25);
                sheet.setColumnWidth(5, 256*30);
                //第六步,加载合并单元格对象
                sheet.addMergedRegion(callRangeAddress1);
                sheet.addMergedRegion(callRangeAddress2);
                sheet.addMergedRegion(callRangeAddress3);
                sheet.addMergedRegion(callRangeAddress4);
                sheet.addMergedRegion(callRangeAddress5);
                sheet.addMergedRegion(callRangeAddress6);
                sheet.addMergedRegion(callRangeAddress7);
                sheet.addMergedRegion(callRangeAddress8);
                sheet.addMergedRegion(callRangeAddress9);
                sheet.addMergedRegion(callRangeAddress10);
                sheet.addMergedRegion(callRangeAddress11);
                sheet.addMergedRegion(callRangeAddress12);
                sheet.addMergedRegion(callRangeAddress13);
                sheet.addMergedRegion(callRangeAddress14);
                sheet.addMergedRegion(callRangeAddress15);
                for(int i = 1;i<=payContent.length+1;i++){//支付信息账号部分单元格合并的创建以及加载
                    CellRangeAddress callRangeAddress16 = new CellRangeAddress(12+applyContent.length+i,12+applyContent.length+i,6,8);//起始行,结束行,起始列,结束列
                    sheet.addMergedRegion(callRangeAddress16);
                }
    
                // 第七步,根据模板往sheet中添加数据
                //7.1 设置标题头和前置内容
                List<String> beforeTitle = (List<String>) dataMap.get("beforeTitle");
                List<String> beforeContent = (List<String>) dataMap.get("beforeContent");
                //设置总标题
                HSSFRow rowTitle = sheet.createRow(2);
                HSSFCell cellTitle = rowTitle.createCell(0);
                cellTitle.setCellStyle(styleTitle);
                cellTitle.setCellValue("提款信息表");
                //设置前置内容数据
                for(int i = 3;i<=6;i++){
                    HSSFRow rowBefore = sheet.createRow(i);
                    for(int j = 0;j<=2;j++){//循环赋值前置信息数据(排除申请日期)
                        if(j == 1){
                            continue;
                        }
                        HSSFCell cellBefore = rowBefore.createCell(j);
                        if(j == 0){
                            cellBefore.setCellStyle(styleBeforeTitle);
                            cellBefore.setCellValue(beforeTitle.get(i-3));
                        }else{
                            cellBefore.setCellStyle(styleBeforeData);
                            cellBefore.setCellValue(beforeContent.get(i-3));
                        }
                    }
                    if(i == 3){//申请日期
                        HSSFCell cellBeforeDate = rowBefore.createCell(7);
                        cellBeforeDate.setCellStyle(styleBeforeData);
                        cellBeforeDate.setCellValue("申请日期:"+new SimpleDateFormat("yyyy-MM-dd").format(new Date()));
                    }
    
                }
    
                //7.2申请信息
                //设置申请信息表的表头
                HSSFRow rowApplyTitle = sheet.createRow(8);
                for(int i = 0;i<applyTitle.length;i++){
                    HSSFCell cellApplyTitle = rowApplyTitle.createCell(i);
                    cellApplyTitle.setCellStyle(styleTableTitle);
                    cellApplyTitle.setCellValue("申请信息");
                }
    
                //设置申请信息表的内容
                for(int row = 9;row <= 9+applyContent.length;row++){
                    HSSFRow rowApplyContent = sheet.createRow(row);
                    for(int cell = 0;cell<applyTitle.length;cell++){
                        HSSFCell cellApplyContent = rowApplyContent.createCell(cell);
                        cellApplyContent.setCellStyle(styleTableContent);
                        if(row == 9){//表的字段名
                            cellApplyContent.setCellValue(applyTitle[cell]);
                        }else if(row != 9 && cell == 0){//表的序号列
                            cellApplyContent.setCellValue(row-9);
                        }else{//表的内容
                            cellApplyContent.setCellValue(applyContent[row-10][cell-1]);
                        }
                    }
                }
                //设置申请信息表的底部
                HSSFRow rowApplyBottom = sheet.createRow(9+applyContent.length+1);
                for(int i = 0;i<=8;i++){
                    if(i==0 || i==5 || i==6 || i==8){
                        HSSFCell cellApplyBottom = rowApplyBottom.createCell(i);
                        cellApplyBottom.setCellStyle(styleTableContent);
                        if(i == 8){
                            cellApplyBottom.setCellValue(dataMap.get("applyAmount").toString());
                        }
                    }
                }
    
                //7.3支付信息
                //设置支付信息表的表头
                HSSFRow rowPayTitle = sheet.createRow(11+applyContent.length);
                for(int i = 0;i<applyTitle.length;i++){
                    HSSFCell cellPayTitle = rowPayTitle.createCell(i);
                    cellPayTitle.setCellStyle(styleTableTitle);
                    cellPayTitle.setCellValue("支付信息");
                }
                //设置支付信息表的内容
                for(int row = 12+applyContent.length;row<=12+applyContent.length+payContent.length+1;row++){
                    HSSFRow rowPayContent = sheet.createRow(row);
                    for(int cell = 0;cell<payTitle.length-1;cell++){
                        HSSFCell cellPayContent = rowPayContent.createCell(cell);
                        cellPayContent.setCellStyle(styleTableContent);
                        if(row == 12+applyContent.length){//支付信息表的字段名
                            cellPayContent.setCellValue(payTitle[cell]);
                        }else if(row == 12+applyContent.length+payContent.length+1){//支付信息表的最后一行
                            if(cell == 4){//运费金额总和
                                cellPayContent.setCellValue(dataMap.get("payAmount").toString());
                            }else{//空白
                                cellPayContent.setCellValue("");
                            }
                        }else{//支付信息表的内容
                            cellPayContent.setCellValue(payContent[row-13-applyContent.length][cell]);
                        }
                    }
                    for(int cell = payTitle.length-1;cell<9;cell++){//关于该表中账号字段的特殊处理
                        HSSFCell cellPayContent = rowPayContent.createCell(cell);
                        cellPayContent.setCellStyle(styleTableContent);
                        if(row == 12+applyContent.length){
                            cellPayContent.setCellValue(payTitle[payTitle.length-1]);
                        }else if(row == 12+applyContent.length+payContent.length+1){
                            cellPayContent.setCellValue("");
                        }else{
                            cellPayContent.setCellValue(payContent[row-13-applyContent.length][payTitle.length-1]);
                        }
                    }
                }
            }
            return wb;
        }
    
        //把list转换为String[][]
        public static String[][] convertListToArray(String[] titles , List<Map<String,Object>> list){
            //excel的主体内容
            String[][] content = new String[list.size()][];
    
            for (int i = 0; i < list.size(); i++) {
                Map<String,Object> map = list.get(i);
                content[i] = new String[titles.length];
                for (int j = 0; j < titles.length; j++) {
                    Object obj = map.get(titles[j]);
                    if(obj == null){
                        obj = "";
                    }
                    content[i][j] =  obj.toString();
                }
            }
            return content;
        }
    
        /**
         *字体大小,是否加粗,是否水平居中,是否加边框,左对齐"L",右对齐"R"
         * @param workbook
         * @param fontsize
         * @return 单元格样式
         */
        private static HSSFCellStyle createCellStyle(HSSFWorkbook workbook, short fontsize,boolean flag,boolean flag2,String alignType) {
            HSSFCellStyle style = workbook.createCellStyle();
            //左右对齐
            if(alignType!=null){
                if("L".equalsIgnoreCase(alignType)){
                    style.setAlignment(HSSFCellStyle.ALIGN_LEFT);//左对齐
                }else if("R".equalsIgnoreCase(alignType)){
                    style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);//右对齐
                }else if("C".equalsIgnoreCase(alignType)){
                    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
                }
            }
            //是否加边框
            if(flag2){
                style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
                style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
                style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
                style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
            }
    
            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
            //创建字体
            HSSFFont font = workbook.createFont();
            //是否加粗字体
            if(flag){
                font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            }
            font.setFontHeightInPoints(fontsize);
            //加载字体
            style.setFont(font);
            return style;
        }
    
        /*
           把HSSFWorkbook对象写入到浏览器输出流,完成下载功能
        */
        public static void exportExcel(HSSFWorkbook hssfWorkbook, String fileName, HttpServletResponse response){
    
            try {
                //设置响应头
                DownLoadPayMsgUtil.setResponseHeader(response, fileName);
                //获取输出流
                OutputStream os = response.getOutputStream();
                //写入
                hssfWorkbook.write(os);
                os.flush();
                os.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    
        /*
        设置浏览器下载响应头
         */
        private static void setResponseHeader(HttpServletResponse response, String fileName) {
            try {
                try {
                    fileName = new String(fileName.getBytes(),"ISO8859-1");
                } catch (UnsupportedEncodingException e) {
                    e.printStackTrace();
                }
                response.setContentType("application/octet-stream;charset=UTF-8");
                response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
                response.addHeader("Pargam", "no-cache");
                response.addHeader("Cache-Control", "no-cache");
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
    
        /**
         * 将生成的excel生成在本地
         * @param hssfWorkbook
         * @param path
         */
        private static void exportExcelToLocally(HSSFWorkbook hssfWorkbook,String path){
            try {
                FileOutputStream fileOut = new FileOutputStream(path);
                hssfWorkbook.write(fileOut);
                fileOut.flush();
                fileOut.close();
                System.out.println("生成成功");
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    
        /**
         * 随机生成map数据,测试专用
         * @param titleArr
         * @return
         */
        private static Map<String,Object> getRandomMap(String [] titleArr){
            Map<String,Object> rtnMap = new HashMap<>();
            for(int i = 0;i<titleArr.length;i++){
                rtnMap.put(titleArr[i],"测试数据"+i);
            }
            return rtnMap;
        }
    }

    生成的效果如下:

     生成了两个sheet页,当然如果勾选更多笔融资编号,就会生成更多的sheet页。

    注释很清楚,不多解释了,嘻嘻。

  • 相关阅读:
    Pandas to_sql将DataFrame保存的数据库中
    Pandas 的groupby操作
    Hibernate(一)
    EasyUI
    Java面试题
    Solr
    Lucene
    SpringMVC(二)
    MyBatis(二)
    MyBatis(一)
  • 原文地址:https://www.cnblogs.com/zaevn00001/p/13879468.html
Copyright © 2020-2023  润新知