使用easyexcel实现复杂excel表格导出

1、问题描述

最近在做一个自动化开发票的需求，就是把网页预览的发票导出成一个excel文件。其实这个很好实现，就是使用blob就可以实现把网页的html内容导出成一个.xls的文件就行了。

Blob把html导出为excel文件_blob导入导出excel_金斗潼关的博客-CSDN博客

这种方式其实就是利用了.xls文件可以解析html文本的功能。本质上还是一个html文件只不过是将.html另存为.xls而已。因为我的logo图片是放在服务器上的，所以导出.xls的时候需访问一次服务器上logo图片的路径，才能把logo图片加载到.xls文件里面。但是海外分公司他们导出的.xls文件里面就是加载不出logo图片，起初怀疑是因为国内与海外的网络问题，再他们尝试了vpn连接后导出的.xls也是无法加载logo图片的。我排查下来，可能是与这个证书有关系。因为要访问服务器路径肯定要过一个认证的，他们的office365打开.xls文件时没有过这个证书认证，所以他们的.xls文件自然就访问不了logo图片了。

因为解决不了这个证书问题，所以只能使用easyexcel写一个后端的服务，连带图片内容合成.xlsx文件。

2、解决方法

使用Springboot整合easyExcel写一个后端接口服务

官方文档：https://easyexcel./docs/current/quickstart/write

GitHub：alibaba/easyexcel: 快速、简洁、解决大文件内存溢出的java处理Excel工具 ()

不过，我看文档以及github上给的例子，貌似都是简单表格导出的，类似于这种：

但我想导出的是这种样子的：其中表头是可以动态改变的

经过我的研究，貌似easyExcel不支持这种复杂的表格样式导出。

不过我受到了这篇文章的启发：EasyExcel实现追加写入文件_easyexcel追加写入_Lincain的博客-CSDN博客

我们可以通过模板的形式不断的追加，即一个复杂的excel表格样式可以由若干个简单样式的模板拼接起来。

3、我的代码

logo.png

pom.xml

<?xml version="1.0" encoding="UTF-8"?><project xmlns="/POM/4.0.0"xmlns:xsi="/2001/XMLSchema-instance"xsi:schemaLocation="/POM/4.0.0 /xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><groupId>org.example</groupId><artifactId>easyexcelTest</artifactId><version>1.0-SNAPSHOT</version><parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>2.0.8.RELEASE</version><relativePath/></parent><properties><piler.source>8</piler.source><piler.target>8</piler.target><project.build.sourceEncoding>UTF-8</project.build.sourceEncoding></properties><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.1.3</version></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><version>2.7.11</version></dependency><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.12</version><scope>test</scope></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.20</version><scope>compile</scope></dependency></dependencies></project>

bo类

把复杂的表格样式拆为若干个模板，分别用不同的实体类对应。

package com.easyexcel.bo;import com.alibaba.excel.annotation.write.style.ColumnWidth;import com.alibaba.excel.annotation.write.style.ContentRowHeight;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;import java.io.InputStream;/*** @author Wulc* @date /7/25 16:47* @description logo*/@Data@AllArgsConstructor@NoArgsConstructor@ContentRowHeight(80)@ColumnWidth(40)public class LogoBO {//logo图片private InputStream logoImage;}package com.easyexcel.bo;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;/*** @author Wulc* @date /7/25 16:53* @description*/@Data@AllArgsConstructor@NoArgsConstructorpublic class CompanyInfoBO {private String param;}package com.easyexcel.bo;import com.alibaba.excel.annotation.write.style.ContentLoopMerge;import com.alibaba.excel.annotation.write.style.ContentStyle;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;import static com.alibaba.excel.enums.poi.HorizontalAlignmentEnum.CENTER;/*** @author Wulc* @date /7/25 16:54* @description*/@Data@AllArgsConstructor@NoArgsConstructor@ContentStyle(horizontalAlignment = CENTER)//内容样式public class InvoiceTitleBO {@ContentLoopMerge(columnExtend = 6)private String invoiceTitle;}package com.easyexcel.bo;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;/*** @author Wulc* @date /7/27 17:29* @description*/@Data@AllArgsConstructor@NoArgsConstructorpublic class InvoiceInfoBO {private String param1;private String param2;private String param3;private String param4;}package com.easyexcel.bo;import com.alibaba.excel.annotation.ExcelProperty;import com.alibaba.excel.annotation.write.style.ColumnWidth;import com.alibaba.excel.annotation.write.style.ContentStyle;import com.alibaba.excel.enums.BooleanEnum;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;import static com.alibaba.excel.enums.poi.HorizontalAlignmentEnum.CENTER;/*** @author Wulc* @date /7/28 10:02* @description*/@Data@AllArgsConstructor@NoArgsConstructor@ContentStyle(horizontalAlignment = CENTER, wrapped = BooleanEnum.FALSE)//内容样式@ColumnWidth(30)public class ProductListBO {@ExcelProperty("编号")private String item;@ExcelProperty("表头1")private String product;@ExcelProperty("表头2")private String description;@ExcelProperty("表头3")private String quantity;@ExcelProperty("表头4") //表头4会动态改变private String unitPrice;@ExcelProperty("表头5") //表头5会动态改变private String amount;}

MyExcelUtils.java

package com.easyexcel.util;import com.alibaba.excel.EasyExcel;import com.alibaba.excel.annotation.ExcelProperty;import com.easyexcel.bo.*;import org.springframework.core.io.ClassPathResource;import org.springframework.core.io.Resource;import org.ponent;import java.io.File;import java.io.IOException;import java.lang.reflect.Field;import java.lang.reflect.InvocationHandler;import java.lang.reflect.Proxy;import java.util.ArrayList;import java.util.List;import java.util.Map;/*** @author Wulc* @date /7/25 17:07* @description*/@Componentpublic class MyExcelUtils {/*** @author Wulc* @date /7/25 17:11* @description 根据excel模板追加内容*/public void appendExcelContent() throws IOException, NoSuchFieldException, IllegalAccessException {Resource resource = new ClassPathResource("/");String path = resource.getFile().getPath();//插入LogoString templatePath1 = path + "\\template1.xlsx";LogoBO logoBO = new LogoBO(this.getClass().getClassLoader().getResource("logo.png").openStream());List<LogoBO> logoBOList = new ArrayList<>();logoBOList.add(logoBO);EasyExcel.write(templatePath1, LogoBO.class).sheet().useDefaultStyle(false).needHead(false).doWrite(logoBOList);//插入公司信息String templatePath2 = path + "\\template2.xlsx";List<CompanyInfoBO> companyInfoBOList = new ArrayList<>();companyInfoBOList.add(new CompanyInfoBO("HelloWorld"));companyInfoBOList.add(new CompanyInfoBO("HelloSea"));companyInfoBOList.add(new CompanyInfoBO("HelloLand"));EasyExcel.write(templatePath2, CompanyInfoBO.class).withTemplate(new File(templatePath1)).sheet().useDefaultStyle(false).needHead(false).doWrite(companyInfoBOList);//插入标题String templatePath3 = path + "\\template3.xlsx";List<InvoiceTitleBO> invoiceTitleBOList = new ArrayList<>();invoiceTitleBOList.add(new InvoiceTitleBO("标题"));EasyExcel.write(templatePath3, InvoiceTitleBO.class).withTemplate(new File(templatePath2)).sheet().useDefaultStyle(false).needHead(false).doWrite(invoiceTitleBOList);//插入Invoice内容String templatePath4 = path + "\\template4.xlsx";List<InvoiceInfoBO> invoiceInfoBOList = new ArrayList<>();invoiceInfoBOList.add(new InvoiceInfoBO("param1", "信息1", "param6", "信息6"));invoiceInfoBOList.add(new InvoiceInfoBO("param2", "信息2", "param7", "信息7"));invoiceInfoBOList.add(new InvoiceInfoBO("param3", "信息3", "param8", "信息8"));invoiceInfoBOList.add(new InvoiceInfoBO("param4", "信息4", "param9", "信息9"));invoiceInfoBOList.add(new InvoiceInfoBO("param5", "信息5", "", ""));EasyExcel.write(templatePath4, InvoiceInfoBO.class).withTemplate(new File(templatePath3)).sheet().useDefaultStyle(false).needHead(false).doWrite(invoiceInfoBOList);//插入产品清单String templatePath5 = path + "\\template5.xlsx";//动态改变表头，我是用了反射，通过改变@ExcelProperty注解的value的值实现改变表头ProductListBO productListBO = new ProductListBO();Field field1 = productListBO.getClass().getDeclaredField("unitPrice");Field field2 = productListBO.getClass().getDeclaredField("amount");ExcelProperty excelProperty1 = field1.getAnnotation(ExcelProperty.class);ExcelProperty excelProperty2 = field2.getAnnotation(ExcelProperty.class);InvocationHandler invocationHandler1 = Proxy.getInvocationHandler(excelProperty1);InvocationHandler invocationHandler2 = Proxy.getInvocationHandler(excelProperty2);Field declaredField1 = invocationHandler1.getClass().getDeclaredField("memberValues");Field declaredField2 = invocationHandler2.getClass().getDeclaredField("memberValues");declaredField1.setAccessible(true);declaredField2.setAccessible(true);Map memberValues1 = (Map) declaredField1.get(invocationHandler1);Map memberValues2 = (Map) declaredField1.get(invocationHandler2);String[] a1 = new String[1];String[] a2 = new String[1];a1[0] = "表头44"; //设置表头的值a2[0] = "表头55"; //设置表头的值memberValues1.put("value", a1);memberValues2.put("value", a2);List<ProductListBO> productListBOList = new ArrayList<>();productListBOList.add(new ProductListBO("1", "产品1", "描述1", "10", "10", "200"));productListBOList.add(new ProductListBO("2", "产品2", "描述2", "20", "10", "300"));productListBOList.add(new ProductListBO("3", "产品3", "描述3", "20", "10", "300"));EasyExcel.write(templatePath5, productListBO.getClass()).withTemplate(new File(templatePath4)).sheet().useDefaultStyle(true).needHead(true).doWrite(productListBOList);}}

ExcelTest .java

package com.easyexcel;import com.easyexcel.util.MyExcelUtils;import com.easyexcel.util.MyExcelUtils2;import org.junit.Test;import org.junit.runner.RunWith;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.boot.test.context.SpringBootTest;import org.springframework.test.context.junit4.SpringRunner;import java.io.IOException;/*** @author Wulc* @date /7/28 20:08* @description*/@SpringBootTest(classes = SpringbootApplication.class)@RunWith(SpringRunner.class)public class ExcelTest {@Autowiredprivate MyExcelUtils myExcelUtils;@Testpublic void test1() throws IOException, NoSuchFieldException, IllegalAccessException {myExcelUtils.appendExcelContent();}}

测试一下。

你会发现在target目录下的test-classes（因为是@test单元测试启动的，所以构建编译文件都放在target/test-classes下面）有5个.xlsx文件，分别对应MyExcelUtils.java中的template1~5。其中template1~4为过程文件，就是你每个模板依次拼接得到的中间文件。template5为你最终想要的。在实际开发中，可以在生成了template5后就把template1~4文件给删掉，只保留一个template5.xlsx供下载，下载好template5后再把template5.xlsx也给删掉就行了。

修改一下appendExcelContent()方法，返回值改为File类型，并且合成了template5后再把template1~4删掉

MyExcelUtils.java

package com.easyexcel.util;import com.alibaba.excel.EasyExcel;import com.alibaba.excel.annotation.ExcelProperty;import com.easyexcel.bo.*;import org.springframework.core.io.ClassPathResource;import org.springframework.core.io.Resource;import org.ponent;import java.io.File;import java.io.IOException;import java.lang.reflect.Field;import java.lang.reflect.InvocationHandler;import java.lang.reflect.Proxy;import java.util.ArrayList;import java.util.List;import java.util.Map;/*** @author Wulc* @date /7/25 17:07* @description*/@Componentpublic class MyExcelUtils {/*** @author Wulc* @date /7/25 17:11* @description 根据excel模板追加内容*/public File appendExcelContent() throws IOException, NoSuchFieldException, IllegalAccessException {Resource resource = new ClassPathResource("/");String path = resource.getFile().getPath();//插入LogoString templatePath1 = path + "\\template1.xlsx";LogoBO logoBO = new LogoBO(this.getClass().getClassLoader().getResource("logo.png").openStream());List<LogoBO> logoBOList = new ArrayList<>();logoBOList.add(logoBO);EasyExcel.write(templatePath1, LogoBO.class).sheet().useDefaultStyle(false).needHead(false).doWrite(logoBOList);//插入公司信息String templatePath2 = path + "\\template2.xlsx";List<CompanyInfoBO> companyInfoBOList = new ArrayList<>();companyInfoBOList.add(new CompanyInfoBO("HelloWorld"));companyInfoBOList.add(new CompanyInfoBO("HelloSea"));companyInfoBOList.add(new CompanyInfoBO("HelloLand"));EasyExcel.write(templatePath2, CompanyInfoBO.class).withTemplate(new File(templatePath1)).sheet().useDefaultStyle(false).needHead(false).doWrite(companyInfoBOList);//插入标题String templatePath3 = path + "\\template3.xlsx";List<InvoiceTitleBO> invoiceTitleBOList = new ArrayList<>();invoiceTitleBOList.add(new InvoiceTitleBO("标题"));EasyExcel.write(templatePath3, InvoiceTitleBO.class).withTemplate(new File(templatePath2)).sheet().useDefaultStyle(false).needHead(false).doWrite(invoiceTitleBOList);//插入Invoice内容String templatePath4 = path + "\\template4.xlsx";List<InvoiceInfoBO> invoiceInfoBOList = new ArrayList<>();invoiceInfoBOList.add(new InvoiceInfoBO("param1", "信息1", "param6", "信息6"));invoiceInfoBOList.add(new InvoiceInfoBO("param2", "信息2", "param7", "信息7"));invoiceInfoBOList.add(new InvoiceInfoBO("param3", "信息3", "param8", "信息8"));invoiceInfoBOList.add(new InvoiceInfoBO("param4", "信息4", "param9", "信息9"));invoiceInfoBOList.add(new InvoiceInfoBO("param5", "信息5", "", ""));EasyExcel.write(templatePath4, InvoiceInfoBO.class).withTemplate(new File(templatePath3)).sheet().useDefaultStyle(false).needHead(false).doWrite(invoiceInfoBOList);//插入产品清单String templatePath5 = path + "\\template5.xlsx";//动态改变表头，我是用了反射，通过改变@ExcelProperty注解的value的值实现改变表头ProductListBO productListBO = new ProductListBO();Field field1 = productListBO.getClass().getDeclaredField("unitPrice");Field field2 = productListBO.getClass().getDeclaredField("amount");ExcelProperty excelProperty1 = field1.getAnnotation(ExcelProperty.class);ExcelProperty excelProperty2 = field2.getAnnotation(ExcelProperty.class);InvocationHandler invocationHandler1 = Proxy.getInvocationHandler(excelProperty1);InvocationHandler invocationHandler2 = Proxy.getInvocationHandler(excelProperty2);Field declaredField1 = invocationHandler1.getClass().getDeclaredField("memberValues");Field declaredField2 = invocationHandler2.getClass().getDeclaredField("memberValues");declaredField1.setAccessible(true);declaredField2.setAccessible(true);Map memberValues1 = (Map) declaredField1.get(invocationHandler1);Map memberValues2 = (Map) declaredField1.get(invocationHandler2);String[] a1 = new String[1];String[] a2 = new String[1];a1[0] = "表头44"; //设置表头的值a2[0] = "表头55"; //设置表头的值memberValues1.put("value", a1);memberValues2.put("value", a2);List<ProductListBO> productListBOList = new ArrayList<>();productListBOList.add(new ProductListBO("1", "产品1", "描述1", "10", "10", "200"));productListBOList.add(new ProductListBO("2", "产品2", "描述2", "20", "10", "300"));productListBOList.add(new ProductListBO("3", "产品3", "描述3", "20", "10", "300"));EasyExcel.write(templatePath5, productListBO.getClass()).withTemplate(new File(templatePath4)).sheet().useDefaultStyle(true).needHead(true).doWrite(productListBOList);new File(templatePath1).delete();new File(templatePath2).delete();new File(templatePath3).delete();new File(templatePath4).delete();return new File(templatePath5);}}

ExcelController.java

package com.easyexcel.controller;import com.alibaba.excel.EasyExcel;import com.alibaba.excel.util.ListUtils;import com.easyexcel.entity.DemoData;import com.easyexcel.util.MyExcelUtils;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.GetMapping;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RestController;import javax.servlet.http.HttpServletResponse;import java.io.*;import .URLEncoder;import java.util.Date;import java.util.List;/*** @author Wulc* @date /7/20 16:02* @description*/@RestController@RequestMapping("/excel")public class ExcelController {@Autowiredprivate MyExcelUtils myExcelUtils;@GetMapping("/exceldownload")public void exceldownload(HttpServletResponse response) throws IOException, NoSuchFieldException, IllegalAccessException {File file = myExcelUtils.appendExcelContent();try {// 获取文件名String filename = file.getName();// 获取文件后缀名String ext = filename.substring(filename.lastIndexOf(".") + 1).toLowerCase();// 将文件写入输入流FileInputStream fileInputStream = new FileInputStream(file);InputStream fis = new BufferedInputStream(fileInputStream);byte[] buffer = new byte[fis.available()];fis.read(buffer);fis.close();// 清空responseresponse.reset();// 设置response的Headerresponse.setCharacterEncoding("UTF-8");//Content-Disposition的作用：告知浏览器以何种方式显示响应返回的文件，用浏览器打开还是以附件的形式下载到本地保存//attachment表示以附件方式下载 inline表示在线打开 "Content-Disposition: inline; filename=文件名.mp3"// filename表示文件的默认名称，因为网络传输只支持URL编码的相关支付，因此需要将文件名URL编码后进行传输,前端收到后需要反编码才能获取到真正的名称response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8"));// 告知浏览器文件的大小response.addHeader("Content-Length", "" + file.length());OutputStream outputStream = new BufferedOutputStream(response.getOutputStream());response.setContentType("application/octet-stream");outputStream.write(buffer);outputStream.flush();} catch (IOException ex) {ex.printStackTrace();}file.delete(); //下载完后，把文件删除}}