Excel导出，一对多数据结构
数据结构一对多，类似一条订单对应着多条订单明细，然后订单信息实行跨行
效果图
导入依赖
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.4.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>4.4.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.4.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.1</version>
</dependency>
业务逻辑处理
@Override public void getPurchaseExport(PurchaseDetailsDto param, HttpServletResponse response) { List<PurchaseExportVo> outExportVoList = new ArrayList<>();
//一对多查询 List<PurchaseDownLoadDto> purchaseList = this.baseMapper.getPurchaseList(param); int i = 0; for (PurchaseDownLoadDto purchaseDownLoadDto : purchaseList) {
//序号 purchaseDownLoadDto.setSerialNumber(i += 1); PurchaseExportVo purchaseExportVo = new PurchaseExportVo(); BeanUtils.copyProperties(purchaseDownLoadDto, purchaseExportVo); purchaseExportVo.setDetailsList(purchaseDownLoadDto.getPurchaseDetailsList()); outExportVoList.add(purchaseExportVo); } response.setCharacterEncoding("UTF-8"); response.setHeader("content-Type", "application/vnd.ms-excel"); String fileName = "申请记录_" + System.currentTimeMillis(); ExcelHandlerUtils.exportExcel(outExportVoList, "申请记录_", PurchaseExportVo.class, fileName + ".xls", response); }
返回实体类
@Excel(name = "序号", orderNum = "1", width = 10, needMerge = true) private Integer serialNumber; @Excel(name = "姓名", orderNum = "2", width = 10, needMerge = true) private String applicantName; @Excel(name = "归属部门", orderNum = "3", width = 20, needMerge = true) private String deptName; @Excel(name = "电话号码", orderNum = "4", width = 20, needMerge = true) private String tel; @Excel(name = "申请事由", orderNum = "5", width = 30, needMerge = true) private String cause; /** * 审批状态(1已通过、2审批拒绝、3审批中、4已作废、 5已撤销) */ @Excel(name = "审批状态", orderNum = "6", width = 20, replace = {"已通过_1", "审批拒绝_2", "审批中_3", "已作废_4", "已撤销_5"}, needMerge = true) private Integer approvalState; @ExcelCollection(name = "采购明细", orderNum = "7") private List<PurchaseDetailsAddDto> detailsList;
说明：
needMerge = true 合并单元格
replace 替换属性值
desensitizationRule 隐藏属性值(比如：号码隐藏) ；用法：desensitizationRule = "3_4"；这里的3_4表示除了前三后四不隐藏，其它都隐藏
@Excel注解中还有很多属性用法，大家可以尝试下
工具类方法
public static <T> void exportExcel(List<T> dataList, String sheetName, Class<?> clz, String fileName, HttpServletResponse response) { defaultExport(dataList, clz, fileName, response, new ExportParams(null, sheetName)); }
private static <T> void defaultExport(List<T> dataList, Class<?> clz, String fileName, HttpServletResponse response, ExportParams exportParams) { Workbook workbook = ExcelExportUtil.exportExcel(exportParams, clz, dataList); if (workbook != null) { if (Objects.nonNull(response)) { downLoadExcel(fileName, response, workbook); } else { downLoadExcelLocal(fileName, workbook,exportParams.getSheetName()); } } }
private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) { try { response.setCharacterEncoding("UTF-8"); response.setHeader("content-Type", "application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8")); workbook.write(response.getOutputStream()); } catch (IOException e) { throw new RuntimeException(e); } }
private static void downLoadExcelLocal(String fileName, Workbook workbook,String title) { File file=null; File files=null; FileOutputStream fos = null; try { Environment env = ApplicationContextHolder.getContext().getBean(Environment.class); String path=env.getProperty("excel.download.path")+fileName; file=new File(path); if(!file.exists()) { boolean mkOK=file.mkdirs(); log.info("******"+mkOK); } fos = new FileOutputStream(path+"/"+title+".xls"); workbook.write(fos); fos.close(); } catch (Exception e) { throw new RuntimeException(e); } finally { if (Objects.nonNull(fos)) { try { fos.close(); } catch (Exception e) { log.error("关闭文件输出流异常...", e); } } } }
ApplicationContextHolder
import lombok.Getter; import lombok.extern.slf4j.Slf4j; import org.springframework.beans.BeansException; import org.springframework.beans.factory.BeanInitializationException; import org.springframework.beans.factory.InitializingBean; import org.springframework.context.ApplicationContext; import org.springframework.context.ApplicationContextAware; import org.springframework.stereotype.Component; /** * @author zhangpingping * @version V1.2 * @Description (get applicationContext) * @date 2019/5/27 8:49 */ @Component @Slf4j public class ApplicationContextHolder implements ApplicationContextAware { @Getter private static ApplicationContext context; /** * Set the ApplicationContext that this object runs in. * Normally this call will be used to initialize the object. * <p>Invoked after population of normal bean properties but before an init callback such * as {@link InitializingBean#afterPropertiesSet()} * or a custom init-method. Invoked after {@link ResourceLoaderAware#setResourceLoader}, * {@link ApplicationEventPublisherAware#setApplicationEventPublisher} and * {@link MessageSourceAware}, if applicable. * * @param applicationContext the ApplicationContext object to be used by this object * @throws ApplicationContextException in case of context initialization errors * @throws BeansException if thrown by application context methods * @see BeanInitializationException */ @Override public void setApplicationContext(ApplicationContext context) throws BeansException { ApplicationContextHolder.context = context; } /** * 获取bean * * @param beanName * @return */ public final static Object getBean(Class<IdGeneratorOnline> beanName) { return context.getBean(beanName); } /** * 获取bean * * @param beanName * @param requiredType * @return */ public final static Object getBean(String beanName, Class<?> requiredType) { return context.getBean(beanName, requiredType); } }
IdGeneratorOnline
public interface IdGeneratorOnline { long nextId(); }
