spring boot 根据模板生成新的文件(exec),复杂一点的,新的导出

weixin_45198965

于 2023-04-25 17:25:23 发布

阅读量214

点赞数

文章标签： spring boot excel java

本文链接：https://blog.csdn.net/weixin_45198965/article/details/130364500

版权

在这里插入图片描述

<!--jar包-->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.6.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
   本地引用文件地址
https://repo.e-iceblue.cn/service/rest/repository/browse/maven-public/e-iceblue/spire.xls.free/3.9.1/
      <!--生成pdf工具-->
       

<dependency>
            <groupId>com.spire</groupId>
            <artifactId>spire.xls.free</artifactId>
            <version>3.9.1</version>
            <scope>system</scope>
            <systemPath>${project.basedir}/lib/spire.xls.free-3.9.1.jar</systemPath>
        </dependency>

 /**
     * 根据模板生成新的文件(exec)
     * http://127.0.0.1:8080/public/xls
     * 首先根基模板生成exec文件,在线下载
     */
    @GetMapping("/xls")
    @SneakyThrows
    public void xls(HttpServletResponse response) {

        //获取Excel模板文件绝对磁盘路径
        String path = "F:\\spring xm\\fuwus\\spring-boot_jwt_qh\\src\\main\\resources\\static\\222.xlsx";
        //基于POI在内存中创建一个Excel文件.
        XSSFWorkbook excel = new XSSFWorkbook(new FileInputStream(new File(path)));
        //获取第一个工作表
        XSSFSheet sheet = excel.getSheetAt(0);

        //第二行
        XSSFRow xssfRow = sheet.createRow(1);
        cell(xssfRow,"销售方：",excel,0);
        setRowBorderStyle(sheet,new CellRangeAddress(1, 1, 0, 11));



        XSSFRow xssfRows2 = sheet.createRow(2);
        cell(xssfRows2,"收货人：",excel,0);
        cell(xssfRows2,"客户订单号：",excel,6);
        setRowBorderStyle(sheet,new CellRangeAddress(2, 2, 0, 5));
        setRowBorderStyle(sheet,new CellRangeAddress(2,2, 6, 11));


        XSSFRow xssfRows3 = sheet.createRow(3);
        cell(xssfRows3,"整车到站：",excel,0);
        cell(xssfRows3,"水运到岗：",excel,6);
        setRowBorderStyle(sheet,new CellRangeAddress(3,3, 0, 5));
        setRowBorderStyle(sheet,new CellRangeAddress(3,3,6, 11));



        XSSFRow xssfRows4 = sheet.createRow(4);
        cell(xssfRows4,"专用线：",excel,0);
        cell(xssfRows4,"运输方式：",excel,6);
        setRowBorderStyle(sheet,new CellRangeAddress(4,4, 0, 5));
        setRowBorderStyle(sheet,new CellRangeAddress(4,4,6, 11));



        XSSFRow xssfRows5 = sheet.createRow(5);
        cell(xssfRows5,"订货意向卡号：",excel,0);
        cell(xssfRows5,"创建日期：",excel,6);
       setRowBorderStyle(sheet,new CellRangeAddress(5,5 ,0, 5));
       setRowBorderStyle(sheet,new CellRangeAddress(5,5,6, 11));



        XSSFRow xssfRows6 = sheet.createRow(6);
        cell(xssfRows6,"序号：",excel,0);
        cell(xssfRows6,"订货意向卡子项号",excel,1);
        cell(xssfRows6,"品种",excel,2);
        cell(xssfRows6,"牌号",excel,3);
        cell(xssfRows6,"产地",excel,4);
        cell(xssfRows6,"规格",excel,5);
        cell(xssfRows6,"重量",excel,6);
        cell(xssfRows6,"钢卷内径",excel,7);
        cell(xssfRows6,"包装",excel,8);
        cell(xssfRows6,"计重方式",excel,9);
        cell(xssfRows6,"件重范围",excel,10);
        cell(xssfRows6,"特殊要求",excel,11);

        int number = 6;


//        数组集合2条数据
//        6+1 = 7
        6+2 = 8

        List<Log> logs = new ArrayList<>();
        logs.add(new Log(1,"123"));
        logs.add(new Log(2,"123"));
        logs.add(new Log(3,"123"));
        logs.add(new Log(4,"123"));
        for (int j = 0; j < logs.size(); j++) {
            XSSFRow xssfRows7 = sheet.createRow(number+j+1);
            cell(xssfRows7,String.valueOf(j+1),excel,0);
            cell(xssfRows7,"订货意向卡子项号",excel,1);
            cell(xssfRows7,"品种",excel,2);
            cell(xssfRows7,"牌号",excel,3);
            cell(xssfRows7,"产地",excel,4);
            cell(xssfRows7,"规格",excel,5);
            cell(xssfRows7,"重量",excel,6);
            cell(xssfRows7,"钢卷内径",excel,7);
            cell(xssfRows7,"包装",excel,8);
            cell(xssfRows7,"计重方式",excel,9);
            cell(xssfRows7,"件重范围",excel,10);
            cell(xssfRows7,"特殊要求",excel,11);
        }



        //集合长度  1=2条数据
        int count = logs.size()+1;

        int numbers = number + count;
        XSSFRow rows01 = sheet.createRow(numbers+1);
        cell(rows01,"合计：",excel,0);
        cell(rows01,"",excel,1);
        cell(rows01,"",excel,2);
        cell(rows01,"",excel,3);
        cell(rows01,"",excel,4);
        cell(rows01,"",excel,5);
        cell(rows01,"重量的合计",excel,6);
        cell(rows01,"",excel,7);
        cell(rows01,"",excel,8);
        cell(rows01,"",excel,9);
        cell(rows01,"",excel,10);
        cell(rows01,"",excel,11);



        XSSFRow rows02 = sheet.createRow(numbers+2);
        cell(rows02,"合同条款",excel,0);
        setRowBorderStyle(sheet,new CellRangeAddress(numbers+2,numbers+2 ,0, 11));


        List<String> list = new ArrayList<>();
        list.add("一、 本合同由销售方和采购方在平等互利的前提下就合同事宜协商一致，严格按照中华人民共和国法律规定签署的。销售方和采购方均是受中华人民共和国法律管辖的法人，均保证具有签署和履行合同的权利和义务的能力。非经另一方当事人局面同意，任何一方不得将本合同项下的义务转让予他方或委托他方代为履行。");
        list.add("二、供货时间及交（提）货地点、方式：采购方自提，各项费用采购方自理。");
        list.add("三、合理损耗及计算方法：按仓库入仓抄码为准，误差±3‰。");
        list.add("四、包装标准：出厂包装或加工后包装。");
        list.add("五、产品质量：符合原生产厂家质量标准要求。");
        list.add("六、销售方对质量负责的条件和期限：如有质量异议货到七日内以书面形式向销售方提出，销售方协助采购方向钢厂提出质量异议申请，采购方负责保留货物原样。采购方逾期提出的质量异议，销售方不予受理。");
        list.add("七、结算方式及期限：款到发货， 销售方在采购方收到货物后，向采购方及时开具全额增值税专用发票。");
        list.add("八、违约责任：按《合同法》有关规定处理。");
        list.add("九、合同争议的解决方式：本合同在执行过程中发生的争议，由双方当事人协商解决；协商不成的，提交销售方所在地人民法院判决。");
        list.add("十、其它约定事项：");
        list.add("1、销售方价格说明：为固定单价，不随钢厂的价格变动。");
        list.add("2、销售方因不可抗力因素发生而不能履行合同时，可免除责任。");
        list.add("3、合同一式三份，销售方两份，采购方一份，双方盖章签字后生效，涂改无效。");
        list.add("4、传真件有效（根据实际情况是否保留）。");

        for (int i = 1; i <= list.size(); i++) {
            XSSFRow lists = sheet.createRow(numbers+2+i);
            cell(lists,list.get(i-1),excel,0);
            setRowBorderStyle(sheet,new CellRangeAddress(numbers+2+i,numbers+2+i ,0, 11));
        }

        XSSFRow rows03 = sheet.createRow(numbers+list.size()+3);
        cell(rows03,"销售方",excel,0);
        cell(rows03,"采购方",excel,6);

        setRowBorderStyle(sheet,new CellRangeAddress(numbers+list.size()+3,numbers+list.size()+3,0, 5));
        setRowBorderStyle(sheet,new CellRangeAddress(numbers+list.size()+3,numbers+list.size()+3,6, 11));


        XSSFRow rows04 = sheet.createRow(numbers+list.size()+4);
        cell(rows04,"单 位 名 称：",excel,0);
        cell(rows04,"单 位 名 称：",excel,6);


        setRowBorderStyle(sheet,new CellRangeAddress(numbers+list.size()+4,numbers+list.size()+4, 0, 5));
        setRowBorderStyle(sheet,new CellRangeAddress(numbers+list.size()+4,numbers+list.size()+4,6, 11));

        XSSFRow rows05 = sheet.createRow(numbers+list.size()+5);
        cell(rows05,"单 位 地 址：",excel,0);
        cell(rows05,"单 位 地 址：",excel,6);

        setRowBorderStyle(sheet,new CellRangeAddress(numbers+list.size()+5,numbers+list.size()+5, 0, 5));
        setRowBorderStyle(sheet,new CellRangeAddress(numbers+list.size()+5,numbers+list.size()+5,6, 11));

        XSSFRow rows06 = sheet.createRow(numbers+list.size()+6);
        cell(rows06,"法定代表人：",excel,0);
        cell(rows06,"法定代表人：",excel,6);

        setRowBorderStyle(sheet,new CellRangeAddress(numbers+list.size()+6,numbers+list.size()+6, 0, 5));
        setRowBorderStyle(sheet,new CellRangeAddress(numbers+list.size()+6,numbers+list.size()+6,6, 11));


        XSSFRow rows07 = sheet.createRow(numbers+list.size()+7);
        cell(rows07,"委托代理人：",excel,0);
        cell(rows07,"委托代理人：",excel,6);

        setRowBorderStyle(sheet,new CellRangeAddress(numbers+list.size()+7,numbers+list.size()+7, 0, 5));
        setRowBorderStyle(sheet,new CellRangeAddress(numbers+list.size()+7,numbers+list.size()+7,6, 11));

        XSSFRow rows08 = sheet.createRow(numbers+list.size()+8);
        cell(rows08,"电 话：",excel,0);
        cell(rows08,"电 话：",excel,6);

        setRowBorderStyle(sheet,new CellRangeAddress(numbers+list.size()+8,numbers+list.size()+8, 0, 5));
        setRowBorderStyle(sheet,new CellRangeAddress(numbers+list.size()+8,numbers+list.size()+8,6, 11));

        XSSFRow rows09 = sheet.createRow(numbers+list.size()+9);
        cell(rows09,"传 真：",excel,0);
        cell(rows09,"传 真：",excel,6);

        setRowBorderStyle(sheet,new CellRangeAddress(numbers+list.size()+9,numbers+list.size()+9, 0, 5));
        setRowBorderStyle(sheet,new CellRangeAddress(numbers+list.size()+9,numbers+list.size()+9,6, 11));

        XSSFRow rows10 = sheet.createRow(numbers+list.size()+10);
        cell(rows10,"开 户 银 行：",excel,0);
        cell(rows10,"开 户 银 行：",excel,6);

        setRowBorderStyle(sheet,new CellRangeAddress(numbers+list.size()+10,numbers+list.size()+10, 0, 5));
        setRowBorderStyle(sheet,new CellRangeAddress(numbers+list.size()+10,numbers+list.size()+10,6, 11));

        XSSFRow rows11 = sheet.createRow(numbers+list.size()+11);
        cell(rows11,"账 号：",excel,0);
        cell(rows11,"账 号：",excel,6);
         setRowBorderStyle(sheet,new CellRangeAddress(numbers+list.size()+11,numbers+list.size()+11, 0, 5));
         setRowBorderStyle(sheet,new CellRangeAddress(numbers+list.size()+11,numbers+list.size()+11,6, 11));




        //在线下载
//            //创建输出流，用于从服务器写数据到客户端浏览器
        ServletOutputStream out = response.getOutputStream();
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("content-Disposition", "attachment;filename=user.xlsx");
        excel.write(out);
        //关闭流
        out.flush();
        out.close();
        excel.close();

    }




    /**
     * 设置合并单元格
     *
     * @param sheet            sheet页对象
     * @param startRowIndex    开始行号
     * @param endRowIndex      结束行号
     * @param startColumnIndex 开始列号
     * @param endColumnIndex   结束列号
     */
    public void setMergeCell(Sheet sheet, int startRowIndex, int endRowIndex, int startColumnIndex, int endColumnIndex) {
        //合并单元格区域只有一个单元格时，不合并
        if (endRowIndex == startRowIndex && endColumnIndex == startColumnIndex) {
            return;
        }
        //添加合并单元格区域
        CellRangeAddress cellRangeAddress = new CellRangeAddress(startRowIndex, endRowIndex, startColumnIndex, endColumnIndex);
        sheet.addMergedRegionUnsafe(cellRangeAddress);
    }


    //设置列值
    private Cell cell(Row row, String name, XSSFWorkbook xssfWorkbook, Integer index) {
        Cell cell1 = row.createCell(index);
        cell1.setCellValue(name);
        cell1.setCellStyle(setBorderStyle(xssfWorkbook));
        return cell1;
    }

    //设计样式
    private XSSFCellStyle setBorderStyle(XSSFWorkbook wb) {
        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setWrapText(true);
        return cellStyle;
    }



    //设计样式--给合并后的单元格添加边框
    private void setRowBorderStyle(XSSFSheet sheet,CellRangeAddress cellRange) {
        sheet.addMergedRegion(cellRange);
        RegionUtil.setBorderTop(BorderStyle.THIN, cellRange, sheet);
        RegionUtil.setBorderBottom(BorderStyle.THIN, cellRange, sheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, cellRange, sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, cellRange, sheet);
    }