导出excel【动态表头，多个sheet】
@Service
public class ExportService {
@Autowired
private BidExportMapper bidExportMapper;
@Autowired
private ExcelMapper excelMapper;
/**
* 导出excel - 单个sheet
*
* @param packageId
* @param request HttpServletRequest
* @param response HttpServletResponse
* @throws Exception
*/
public void exportSupplierQuotationDetailsAboutPackage(String packageId, HttpServletRequest request, HttpServletResponse response) throws Exception {
//获取表头列表
Map<String, String> headerTitle = getHeaderTitle(packageId);
//获取数据内容
List<Map<String,Object>> resultList = getResultList(packageId);
//通过工具类创建writer
ExcelWriter writer = ExcelUtil.getWriter(true);
//重命名当前sheet
writer.renameSheet("导出excel的sheet名称");
//合并单元格后的标题行，取消默认标题样式
writer.merge(headerTitle.size()-1, "表格的标题",false);
//设置列名
writer.setHeaderAlias(headerTitle);
//如果想只写出加了别名的字段，可以调用此方法排除之
writer.setOnlyAlias(true);
//如果没有内容，则写出标题【表头列】
if (CollectionUtil.isEmpty(resultList)){
writer.writeHeadRow(headerTitle.values());
}
// 一次性写出内容，使用默认样式，强制输出标题
writer.write(resultList,true);
//设置样式
setStyle(writer, headerTitle.size());
//导出下载
exportDownload(response,writer,"导出excel标题");
}
/**
* 导出excel - 多个sheet
*
*
* @param tenderProjectId
* @param request HttpServletRequest
* @param response HttpServletResponse
* @throws Exception
*/
public void exportSupplierQuotationDetailsTotal(String tenderProjectId, HttpServletRequest request, HttpServletResponse response) throws Exception {
//根据采购方案ID，获取包件列表
List<PurTenderProjectPackagesVo> packageList = bidExportMapper.packageListByTenderProjectId(tenderProjectId);
//通过工具类创建writer
ExcelWriter writer = ExcelUtil.getWriter(true);
packageList.stream().forEach(item->{
//获取表头列表
Map<String, String> headerTitle = getHeaderTitle(item.getId());
//获取数据内容
List<Map<String,Object>> resultList = getResultList(item.getId());
if (CollectionUtil.isNotEmpty(resultList)){
//自定义需要读取或写出的Sheet，如果给定的sheet不存在，创建之。
writer.setSheet(item.getCode());
//合并单元格后的标题行，使用默认标题样式
writer.merge(headerTitle.size()-1, "表格的标题",false);
//设置列名
writer.setHeaderAlias(headerTitle);
//如果想只写出加了别名的字段，可以调用此方法排除之
writer.setOnlyAlias(true);
// 一次性写出内容，使用默认样式，强制输出标题
writer.write(resultList,true);
//设置样式
setStyle(writer, headerTitle.size());
}
});
//删除sheet1，因为sheet1为空
writer.getWorkbook().removeSheetAt(0);
//导出下载
exportDownload(response,writer,"导出excel标题");
}
/**
* 获取标题列表
* 这里的标题列表，指的是表头列表，我的业务需求是在表中获取标题列表信息
* 注意：根据自己业务需求进行标题变更
* @param packageId 包件ID
* @return 标题
*/
public Map<String,String> getHeaderTitle(String packageId){
//根据包件ID，获取报价模板
List<PurTenderProjectPriceTemplateItemVo> templateList = bidExportMapper.offerTemplateByPackageId(packageId);
//1.标题信息
Map<String, String> headerTitle= new LinkedHashMap<>();
headerTitle.put("vendorName","供应商名称");
templateList.stream().forEach(item->{
headerTitle.put(item.getColumnName(),item.getShowName());
});
return headerTitle;
}
/**
* 获取数据内容
* 1. 即导出的数据，根据自己业务需求进行更改
* @param packageId 包件ID
* @return 导出的数据
*/
public List<Map<String,Object>> getResultList(String packageId) {
//1.根据包件ID，获取供应商报价明细
PackageInfo packageInfo = excelMapper.selectPackageInfos(packageId);
List<PurTenderBidInfoItemsVo> detailedList = bidExportMapper.offerDetailedByPackageId(packageId);
List<PurTenderProjectPriceTemplateItemVo> templateList = bidExportMapper.offerTemplateByPackageId(packageId);
Map<String, PurTenderProjectPriceTemplateItemVo> itemVoMap = templateList.stream().collect(Collectors.toMap(PurTenderProjectPriceTemplateItemVo::getColumnName, Function.identity()));
//2.导出的供应商报价明细
List<Map<String,Object>> resultList = new ArrayList<>();
if(CollectionUtil.isNotEmpty(detailedList)){
detailedList.forEach(element->{
if (StringUtils.isNotBlank(element.getRegionName())){
element.setDeliveryAddressAll(element.getRegionName() + element.getProvinceName() + element.getCityName() + element.getOfregionName() + element.getDeliveryAddress());
}else {
element.setDeliveryAddressAll(element.getDeliveryAddress());
}
Map<String, Object> map = BeanUtil.beanToMap(element);
resultList.add(map);
});
}
//3.判断未公布报价或者未解密价格展示为 *
if (CollectionUtil.isNotEmpty(resultList)){
resultList.forEach(map->{
if (StringUtils.equals(packageInfo.getPublicState(),"0") || StringUtils.equals(map.get("isEncrypt").toString(),"0")){
Field[] declaredFields = PurTenderBidInfoItemsVo.class.getDeclaredFields();
for (Field declaredField : declaredFields) {
String name = declaredField.getName();
if (itemVoMap.get(name) != null
&& StringUtils.equals(itemVoMap.get(name).getValueType(),"2")
&& declaredField.getType() == BigDecimal.class
&& !StringUtils.equals(name,"purCount")
){
map.put(name,"*");
}
}
}
});
}
return resultList;
}
/**
* 写出到客户端下载
*
* @param response HttpServletResponse
* @param writer ExcelWriter
* @param fileName 文件名称
*/
public void exportDownload(HttpServletResponse response,ExcelWriter writer,String fileName) throws Exception {
//response为HttpServletResponse对象
response.setContentType("application/;charset=utf-8");
//是弹出下载对话框的文件名，不能为中文，中文请自行编码
String codedFileName = java.net.URLEncoder.encode(fileName, "UTF8");
response.setHeader("Content-Disposition","attachment;filename="+ codedFileName + ".xlsx");
//out为OutputStream，需要写出到的目标流
ServletOutputStream outputStream = response.getOutputStream();
writer.flush(outputStream, true);
// 关闭writer，释放内存
writer.close();
//此处记得关闭输出Servlet流
IoUtil.close(outputStream);
}
/**
* 设置样式
*
* @param writer ExcelWriter
* @param headerSize 标题长度
*/
public void setStyle(ExcelWriter writer, int headerSize){
//设置筛选
HutoolExcelUtil.setFilter(writer, "A2:"+HutoolExcelUtil.cellName(2,headerSize));
//全局样式设置
StyleSet styleSet = HutoolExcelUtil.setBaseGlobalStyle(writer,HutoolExcelUtil.createFont(writer,false,false,"宋体",12),HorizontalAlignment.RIGHT,VerticalAlignment.CENTER);
//数字保留小数
CellStyle cellStyleForNumber = styleSet.getCellStyleForNumber();
cellStyleForNumber.setDataFormat((short)2);
//合并单元格样式
CellStyle cellStyle = HutoolExcelUtil.createCellStyle(writer,HutoolExcelUtil.createFont(writer,true,false,"宋体",16),true,VerticalAlignment.CENTER,HorizontalAlignment.CENTER);
writer.getSheet().getRow(0).getCell(0).setCellStyle(cellStyle);
//标题样式
CellStyle cellStyleTitle = HutoolExcelUtil.createCellStyle(writer,HutoolExcelUtil.createFont(writer,true,false,"宋体",12),true,VerticalAlignment.CENTER,HorizontalAlignment.CENTER);;
//设置背景
cellStyleTitle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
//如果不设置，则背景无效
cellStyleTitle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
HutoolExcelUtil.setBorderStyle(cellStyleTitle, BorderStyle.THIN, BorderStyle.THIN, BorderStyle.THIN, BorderStyle.THIN);
Row row = writer.getSheet().getRow(1);
for (int i = 0; i < headerSize; i++) {
if (null!= row && null!=row.getCell(i)){
row.getCell(i).setCellStyle(cellStyleTitle);
}
}
//自适应列宽
HutoolExcelUtil.setSizeAutoColumn(writer.getSheet(), headerSize-1);
}
}