java复杂excel导出--easyexcel

使用java导出复杂excel

基本思路：通过下载oss的模板，然后进行填充，写到一个临时文件，最终将临时文件删除，进行导出
最终效果如下

首先是模板上传到oss上，具体模板如下，上传方法可以用阿里云或者oss提供得方法
注意{.xxx}前面有点表示list的参数，无点表示map的参数

开始进行导出

//导出明细表excel并上传到阿里云
	public String exportVchPrepareCashData(List<VchPrepareCashVo> list, VchVoucherCashCondition condition) throws Exception {
		//originDataList 为需要导出的数据
		List<Object> originDataList = (List<Object>) (List) list;
		//获取模板文件对象
		S3Object s3Object = ossTemplate.getObject(bucketName, OssPathCst.PREPAREVERCHUR_CASH_LINE_EXPORT_TEMP_PATH);


		//设置请求头
		DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMMddHHmmss");
		LocalDateTime localDateTime = LocalDateTime.now();
		String dateStr = localDateTime.format(formatter);
		DateTimeFormatter formatter2 = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
		LocalDateTime localDateTime2 = LocalDateTime.now();
		String dateStr2 = localDateTime2.format(formatter2);


		//设置文件名
		String fileName = OssPathCst.PREPAREVERCHUR_CASH_LINE_EXPORT_TEMP_FILENAME.replaceAll("YYYYMMDDhhmmss", dateStr);


		Map<String, Object> paramsMap = new HashMap();
		paramsMap.put("dateStr2", "提交时间：" + dateStr2);
		paramsMap.put("VoucherPeriod2", "期间：" + condition.getVoucherPeriod());
		paramsMap.put("compCode2", "公司段：" + condition.getCompCode());
		paramsMap.put("unit2", "单位：                 元");


		File file = vchEasyExcelService.fillWithVchTemplateTable(originDataList, paramsMap, s3Object.getObjectContent(), fileName);
		log.info("生成临时文件{}", file.getName());


		MultipartFile multipartFile = new MockMultipartFile(fileName, file.getName(), null, new FileInputStream(file));
		//明细表目录
		String filePath = "vc/";
		//将文件上传到OSS
		HttpResult<HashMap<String, Object>> upload = vchVoucherHandoverService.upload(multipartFile, filePath);
		//关闭oss流
		IOUtils.closeQuietly(s3Object);
		file.delete();
		//将文件路径返回
		return (String) upload.getData().get("fullPath");
	}

以下是具体导出方法fillWithVchTemplateTable

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.cpit.ycyt.vch.rule.excel.export.ExcelUtils;
import com.cpit.ycyt.vch.service.VchEasyExcelService;
import com.cpit.ycyt.vch.utils.TimestampStringConverter;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileItemFactory;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.commons.CommonsMultipartFile;


import java.io.*;
import java.util.List;
import java.util.Map;


@Slf4j
@Service
public class VchEasyExcelServiceImpl implements VchEasyExcelService {

    //文件地址，需要在服务器或者本地存在的路径
    @Value("${export.file.tmpfilepath:}")
    private String tmpfilepath;


    @Value("${export.file.sizeThreshold:}")
    private Integer sizeThreshold;


    public File fillWithVchTemplateTable(List<Object> data, Map<String, Object> map, InputStream inputStream, String fileName) throws Exception {
        //目标文件
        File targetFile = new File(tmpfilepath + fileName);
        //临时文件
        File templateFile = new File(tmpfilepath  + "M_" + fileName);
        FileItemFactory factory = new DiskFileItemFactory(sizeThreshold, null);
        FileItem fileItem = factory.createItem("textField", "text/plain", true, fileName);
        //获取模板文件
        try (Workbook workbook = ExcelUtils.getWorkBookTemplate("xlsx", inputStream);
             OutputStream os = fileItem.getOutputStream();) {
            workbook.write(os);
            MultipartFile multipartFile = new CommonsMultipartFile(fileItem);
            multipartFile.transferTo(templateFile);
        } catch (Exception e) {
            log.error("读取模板文件失败{}", fileName, e);
            throw e;
        }
        try (ExcelWriter excelWriter = EasyExcel.write(targetFile).registerConverter(new TimestampStringConverter()).withTemplate(templateFile).build();
             ExcelReader excelReader = EasyExcel.read(templateFile).build();) {
            List<ReadSheet> readSheetList = excelReader.excelExecutor().sheetList();
            if(CollectionUtils.isNotEmpty(readSheetList)){
                readSheetList.forEach(readSheet -> {
                    log.info("开始填充sheet {}， {}", readSheet.getSheetNo(), readSheet.getSheetName());
                    WriteSheet writeSheet = EasyExcel.writerSheet(readSheet.getSheetNo()).build();
                    excelWriter.fill(map, writeSheet);
                    excelWriter.fill(data, writeSheet);
                });
            }
        } catch (Exception e) {
            log.error("填充模板文件失败{}", fileName, e);
            throw e;
        } finally {
            if(templateFile.exists()){
                templateFile.delete();
            }
        }
        return targetFile;
    }
}

public static Workbook getWorkBookTemplate(String suffix, InputStream in) throws Exception {
        //创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;
        try {
            //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
            if("xls".equalsIgnoreCase(suffix)){
                //2003
                workbook = new HSSFWorkbook(in);
            }else if("xlsx".equalsIgnoreCase(suffix)){
                //2007
//                workbook = new XSSFWorkbook(in);
                workbook = new SXSSFWorkbook(new XSSFWorkbook(in), 200);
            }
        } catch (Throwable t) {
            throw new Exception("创建excel工作簿workBook失败");
        }
        return workbook;
    }```