Java使用POI导出Excel(二)：多个sheet

         org.apache.poipoi5.2.2compile org.apache.poipoi-ooxml5.2.2compile

 package com.***.excel; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.VerticalAlignment; import org.springframework.http.MediaType; import javax.servlet.http.HttpServletResponse; import java.net.URLEncoder; import java.util.List; /** * @description: excel导出多个sheet工具类 * @author: *** * @date: 2022/9/15 */ public class ExportSheetUtil { /** * 拆解并导出多重Excel */ public static void exportManySheetExcel(String fileName, List mysheets, HttpServletResponse response) { //创建工作薄 HSSFWorkbook wb = new HSSFWorkbook(); //表头样式 HSSFCellStyle style = wb.createCellStyle(); // 垂直 style.setVerticalAlignment(VerticalAlignment.CENTER); // 水平 style.setAlignment(HorizontalAlignment.CENTER); //字体样式 HSSFFont fontStyle = wb.createFont(); fontStyle.setFontName("微软雅黑"); fontStyle.setFontHeightInPoints((short) 12); style.setFont(fontStyle); for (ExcelSheet excel : mysheets) { //新建一个sheet //获取该sheet名称 HSSFSheet sheet = wb.createSheet(excel.getFileName()); //获取sheet的标题名 String[] handers = excel.getHanders(); //第一个sheet的第一行为标题 HSSFRow rowFirst = sheet.createRow(0); //写标题 for (int i = 0; i  dataset = excel.getDataset(); for (int i = 0; i 

 package com.***.excel; import lombok.Data; import java.util.List; /** * @description: 导出多个sheet表 * @author: *** * @date: 2022/9/15 */ @Data public class ExcelSheet { /*** sheet的名称*/ private String fileName; /*** sheet里的标题*/ private String[] handers; /*** sheet里的数据集*/ private List dataset; public ExcelSheet(String fileName, String[] handers, List dataset) { this.fileName = fileName; this.handers = handers; this.dataset = dataset; } }

    /*** 导出开票及运单信息*/ ExportInvoiceAndBillVo exportInvoiceAndBillInfo(InvoiceReviewListDto dto);

 package com.***.vo.invoicereview; import lombok.Data; import java.io.Serializable; import java.util.List; /** * @description: 导出开票和运单信息Vo * @author: *** * @date: 2022/9/19 */ @Data public class ExportInvoiceAndBillVo implements Serializable { /*** 开票信息*/ private List invoiceList; /*** 运单信息*/ private List billList; }      @Override public ExportInvoiceAndBillVo exportInvoiceAndBillInfo(InvoiceReviewListDto dto) { ExportInvoiceAndBillVo invoiceAndBill = new ExportInvoiceAndBillVo(); // 查询需要导出的开票信息 PageInfo pageInfo = queryInvoiceReviewList(dto); List invoiceList = pageInfo.getList(); if (invoiceList.size() > 10000) { throw new ServiceException("开票数据过多,请分批次导出"); } // 查询需要导出的车运运单信息 List invoiceIdList = invoiceList.stream().map(InvoiceReviewListVo::getInvoiceId).collect(Collectors.toList()); List billList = getBillInfo(invoiceIdList); if (billList.size() > 10000) { throw new ServiceException("运单数据过多,请分批次导出"); } // 将表1 表2的数据 放入定义的对象Vo中 invoiceAndBill.setInvoiceList(getInvoiceDataList(invoiceList)); invoiceAndBill.setBillList(getBillDataList(billList)); return invoiceAndBill; }

     /** * 导出开票和运单信息 */ @Log @PostMapping("/exportInvoiceAndBillInfo") public void exportInvoiceAndBillInfo(@RequestBody InvoiceReviewListDto dto, HttpServletResponse response) { ExportInvoiceAndBillVo invoiceAndBillVo = invoiceFacadeService.exportInvoiceAndBillInfo(dto); //设置sheet的表头与表名 String[] invoiceSheetHead = {"开票编号", "票号", "公司名称", "收票方名称", "结算类型", "纳税识别码", "收票联系人", "联系人电话", "运单总金额(元)", "含税总金额(元)", "开票状态", "提交开票时间", "运营审核时间", "运营审核人", "财务审核时间", "财务审核人", "开票完成时间", "冲销操作人", "冲销时间"}; String[] billSheetHead = {"开票编号", "运单号", "发货地", "收货地", "司机", "司机电话", "货物名称", "货物数量", "单位", "货物重量(吨)", "运单状态", "运单金额(元)", "含税金额(元)"}; ExcelSheet invoiceExcel = new ExcelSheet("开票信息", invoiceSheetHead, invoiceAndBillVo.getInvoiceList()); ExcelSheet billExcel = new ExcelSheet("运单信息", billSheetHead, invoiceAndBillVo.getBillList()); List mysheet = new ArrayList<>(); mysheet.add(invoiceExcel); mysheet.add(billExcel); ExportSheetUtil.exportManySheetExcel("开票及运单信息", mysheet, response); }