EasyExcelUtils导出复杂Excel

效果如下：

（1）查询数据list

@RequestMapping(value = "/exportContractProgress", method = RequestMethod.POST)
@ApiOperation(value = "导出[合同执行进度报表]", notes = "导出[合同执行进度报表]")
public JsonResult<OrderKhdPageRespDTO> exportContractProgress(@RequestBody @Validated OrderKhdPageParamsDTO dto,HttpServletRequest request,HttpServletResponse response){SysUser sysUser = getPcLoginUser(request);List<OrderKhdDetailRespDTO> items=this.orderService.findKhdPage(DEFAULT_PAGE_NUM, DEFAULT_PAGE_SIZE, dto, sysUser);String excelName ="contractProgress"+ DateUtil.dateToString(new Date(), DateUtil.FORMAT_DATE_NUB);String sheetName="合同执行进度报表";List<List<String>> headTitles =this.headerContractService.comFixedHeader();List<List<Object>> listData=this.headerContractService.cellList(items);int mergeRowIndex = 0;//从第几行开始合并(数据合并,非表头合并)int[] mergeColumnIndex ={0,1};//从第几列开始合并(数据合并,非表头合并)EasyExcelUtils.writeExcel(response,this.findDownLoadPath(), excelName, sheetName,headTitles, listData,mergeRowIndex,mergeColumnIndex);return JsonResult.success();
}

package com.zk.tobacco.service.excel;import com.zk.tobacco.dto.response.orderKhd.OrderKhdDetailRespDTO;
import java.util.List;/*** Created: pjj* Desc:* Date: 2022/5/29 15:21*/
public interface HeaderContractService {List<List<Object>> cellList(List<OrderKhdDetailRespDTO> datas);List<List<String>> comFixedHeader();
}

package com.zk.tobacco.service.excel;import com.google.common.collect.Lists;
import com.zk.tobacco.dto.response.orderKhd.OrderKhdDetailRespDTO;
import com.zk.tobacco.util.decimal.AmountCommonUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;/*** Copyright (C), 2022/5/29* FileName:* Author: PJJ* Date: 2022/5/29 15:14* Description: 1*/
@Slf4j
@Component
public class HeaderContractServiceImpl implements HeaderContractService {//填充单元格数据@Overridepublic  List<List<Object>> cellList(List<OrderKhdDetailRespDTO> datas){List<List<Object>> listData = new ArrayList<>();if (datas.isEmpty()) {return listData;}for (OrderKhdDetailRespDTO entity : datas) {List<Object> cell = new ArrayList<>();cell.add(entity.getContractNo());cell.add(entity.getContractName());cell.add(entity.getOrderSn());cell.add(entity.getSupplierName());cell.add(entity.getCreateTime());cell.add(entity.getTotalPrice());cell.add(entity.getHavedInvoiceAmount());//已开票金额cell.add(entity.getBuyCount());cell.add(entity.getReceiveQuantity());BigDecimal subQuantity= AmountCommonUtils.subtract(entity.getBuyCount(),entity.getReceiveQuantity());cell.add(subQuantity);cell.add(entity.getOrderStatusName());listData.add(cell);}return listData;}//列名@Overridepublic  List<List<String>> comFixedHeader(){List<List<String>> headTitles = Lists.newArrayList();String subTitle="合同执行进度报表";headTitles.add(this.findOneColumn(subTitle,"合同编号"));headTitles.add(this.findOneColumn(subTitle,"采购合同名称"));headTitles.add(this.findOneColumn(subTitle,"订单号"));headTitles.add(this.findOneColumn(subTitle,"供应商名称"));headTitles.add(this.findOneColumn(subTitle,"下单日期"));headTitles.add(this.findOneColumn(subTitle,"价税合计"));headTitles.add(this.findOneColumn(subTitle,"已开票金额"));headTitles.add(this.findOneColumn(subTitle,"到货情况","订购数量"));headTitles.add(this.findOneColumn(subTitle,"到货情况","已收货数量"));headTitles.add(this.findOneColumn(subTitle,"到货情况","差异数量（订购数量-已收货数量）"));headTitles.add(this.findOneColumn(subTitle,"订单状态"));return headTitles;}//定义标题有4行public List<String>  findOneColumn(String subTitle,String name){//String title= QualityConstants.BAOBIAO_COMPANY_NAME;List<String> column1 = new ArrayList<>();//column1.add(title);     //第一行column1.add(subTitle);  //第二行column1.add(name);      //第三行column1.add(name);      //第四行return column1;}//定义标题有5行public List<String>  findOneColumn(String subTitle,String name1,String name2){//String title= QualityConstants.BAOBIAO_COMPANY_NAME;List<String> column1 = new ArrayList<>();//column1.add(title);     //第一行column1.add(subTitle);  //第二行column1.add(name1);     //第三行column1.add(name2);      //第四行return column1;}
}

工具类：EasyExcelUtils```java
package com.zk.tobacco.util.excel;import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.zk.tobacco.util.DateUtil;
import org.apache.commons.io.IOUtils;import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.Date;
import java.util.List;/*** Copyright (C), 2022/5/29* FileName:* Author: PJJ* Date: 2022/5/29 17:28* Description: 1*/
public class EasyExcelUtils {private static final String YMD= DateUtil.dateToString(new Date(), DateUtil.FORMAT_DATE_NUB);/*** 导出复杂excel* @param path excel导出存放路径* @param excelName excel名称* @param sheetName sheet名称* @param headTitles 表头* @param datas 数据* @param mergeRowIndex  int mergeRowIndex = 3;* @param mergeColumnIndex int[] mergeColumnIndex ={0};//{0, 1, 2, 3, 8, 9};* @throws IOException 异常信息*/public static void writeExcel(HttpServletResponse response, String path, String excelName, String sheetName,List<List<String>> headTitles, List<List<Object>> datas,int mergeRowIndex, int[] mergeColumnIndex) throws IOException {String fulPath = path.concat(excelName).concat(ExcelTypeEnum.XLSX.getValue());EasyExcel.write(fulPath).head(headTitles)// 第一个参数表示从哪一行开始进行合并（data数据非表头），由于表头占了两行，因此从第2行开始（索引从0开始）// 第二个参数是指定哪些列要进行合并【列合并】【竖着】// 参数1-第几行，参数2-第几列，index都是从0开始.registerWriteHandler(new MergeSameRowsStrategy(mergeRowIndex,mergeColumnIndex))
//                .registerWriteHandler(new CellWidthHandler())//表头宽度自适应.registerWriteHandler(new CellWidthHandler())//表头宽度自适应// 注意：需要先调用registerWriteHandler()再调用sheet()方法才能使合并策略生效！！！.sheet(sheetName).doWrite(datas);export2Web4File(response, path,excelName);}public static String export2Web4File(HttpServletResponse response, String path, String excelName) throws UnsupportedEncodingException {File file = new File(path.concat(excelName).concat(ExcelTypeEnum.XLSX.getValue()));if (!file.exists()) {return "文件不存在！";}response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");// 这里URLEncoder.encode可以防止中文乱码excelName = URLEncoder.encode(excelName, "UTF-8");response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue());try (FileInputStream in = new FileInputStream(file);ServletOutputStream out = response.getOutputStream();) {IOUtils.copy(in, out);return "导出成功！";} catch (Exception e) {System.out.println("导出失败");}return "导出失败！";}// 合并策略：指定要合并的行列范围/*public void test02() {int[][] toMergeRows = {{2, 3}, {4, 6}};int[] toMergeColumns = {0, 1, 2, 3, 8, 9};List list = new ArrayList<>();for (int[] toMergeRow : toMergeRows) {for (int toMergeColumn : toMergeColumns) {list.add(new CellRangeAddress(toMergeRow[0], toMergeRow[1], toMergeColumn, toMergeColumn));}}EasyExcel.write(fileName).head(header()).registerWriteHandler((WriteHandler) new AssignRowsAndColumnsToMergeStrategy(list)).sheet("模板").doWrite(findDataList());}*/}

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！