excel复杂表头导出

工作中，我们经常会遇到一些excel导入，导出的需求。我们通常可以使用poi等提供的api来处理。但是处理起来，代码十分臃肿很不优雅。
不经意的一次，查看很久之前的代码，看到以前一位同事前辈，针对此问题就已经对excel导出做出了一次比较好的封装，使用到的技术AbstractView+Jxls,对于jxls,网上有许多资料，不了解的同学可以先找些资料了解下。
http://jxls.sourceforge.net/getting_started.html
废话不多说直接上代码：
1.controller接收web请求

@RequestMapping("/xls")
public ModelAndView xls() {
List<PostDiagnoseInfoResponseBean> infoList = homeAppService.getPostDiagnoseInfo("W9432503");
ModelMap modelMap = new ModelMap();
String fileName = "DiagnoseInfo文件.xls";
modelMap.put("list", infoList);
modelMap.put("ExcelExportFileName", fileName);
modelMap.put("ExcelTemplateFileName", "diagnose-template.xls");
modelMap.put("fileName", fileName);
return new ModelAndView(new JXLSExcelView(), modelMap);
}
1
2
3
4
5
6
7
8
9
10
11
2.JXLSExcelView集成AbstractView,代码如下：

public class JXLSExcelView extends AbstractView {
public static final String EXCEL_EXPORT_FILE_NAME = "ExcelExportFileName";
public static final String EXCEL_TEMPLATE_FILE_NAME = "ExcelTemplateFileName";
public static final String EXCEL_SHEET_NAMES = "ExcelSheetNames";
/** The content type for an Excel response */
private static final String CONTENT_TYPE = "application/vnd.ms-excel";
private XLSTransformerExt transformer;

/**
* Default Constructor.
* Sets the content type of the view to "application/vnd.ms-excel".
*/
public JXLSExcelView() {
transformer = new XLSTransformerExt();
setContentType(CONTENT_TYPE);
}

@Override
protected boolean generatesDownloadContent() {
return true;
}

@SuppressWarnings("rawtypes")
@Override
protected void renderMergedOutputModel(Map<String, Object> model,
HttpServletRequest request, HttpServletResponse response)
throws Exception {
String fileName = (String)model.get(EXCEL_EXPORT_FILE_NAME);
String templateName = (String)model.get(EXCEL_TEMPLATE_FILE_NAME);
List newSheetNames = (List)model.get(EXCEL_SHEET_NAMES);

response.setHeader("content-disposition","attachment; filename="+new String(fileName.getBytes("gb2312"),"ISO8859-1"));
String srcFilePath = "/resources/excel/" + templateName;
ServletOutputStream out = response.getOutputStream();
transformer.transformXLS(request.getSession().getServletContext(), srcFilePath, model, out);
out.flush();
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
3.XLSTransformerExt继承XLSTransformer,代码如下：

public class XLSTransformerExt extends XLSTransformer {
@SuppressWarnings("rawtypes")
public void transformXLS(ServletContext servletContext, String srcFilePath, Map beanParams, OutputStream os) {
try {
ServletContextResource resource = new ServletContextResource(servletContext, srcFilePath);
Workbook workbook = transformXLS(resource.getInputStream(), beanParams);
workbook.write(os);
} catch (Exception e) {
e.printStackTrace();
}
}
}
1
2
3
4
5
6
7
8
9
10
11
12
4.excel模板

 

5.maven依赖

<properties>
<jxls.version>1.0</jxls.version>
</properties>
<!-- excel 导出或读取 -->
<dependency>
<groupId>net.sf.jxls</groupId>
<artifactId>jxls-core</artifactId>
<version>${jxls.version}</version>
</dependency>
<dependency>
<groupId>net.sf.jxls</groupId>
<artifactId>jxls-reader</artifactId>
<version>${jxls.version}</version>
</dependency>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
对于上述的需求，相信应该能满足大多数的excel导出需求了。但是相信还是有不少人遇到过这种需求，导出的excel列是动态变化的，即使是同一张报表导出，针对不同的人需要导出不同的列。这种场景下，上述的解决方法就无法满足需求了。
如何解决这种需求呢，大致的解决思路应该都差不多，无非是接收一个表头header[]数组，取值key[]数组，以及数据list即可，正当磨拳擦掌准备大干一场的时候，仔细翻阅了jxls的最新版本，发现已经支持博主的类似需求,请查阅：http://jxls.sourceforge.net/samples/dynamic_grid.html，有了这个指导性文件，实现起来就很简单了:
1.controller接收web请求

@RequestMapping("/xls2")
public ModelAndView xls2() {
ModelMap modelMap = new ModelMap();
modelMap.put(JXLSExcelViewDynamicColumns.EXCEL_EXPORT_FILE_NAME, "dynamic-columns文件-20170622.xls");
modelMap.addAttribute(JXLSExcelViewDynamicColumns.EXCEL_TEMPLATE_DATA_NAME, homeAppService.getPostDiagnoseInfo("W9432503"));
modelMap.put(JXLSExcelViewDynamicColumns.EXCEL_TEMPLATE_HEADERS_NAME, new String[]{"模型id", "模型名称", "因子类型"});
modelMap.put(JXLSExcelViewDynamicColumns.EXCEL_TEMPLATE_INDEXS_NAME, new String[]{"modelId", "modelName", "modelType"});

return new ModelAndView(new JXLSExcelViewDynamicColumns(), modelMap);
}
1
2
3
4
5
6
7
8
9
10
2.JXLSExcelViewDynamicColumns继承AbstractView

public class JXLSExcelViewDynamicColumns extends AbstractView {
public static final String EXCEL_EXPORT_FILE_NAME = "ExcelExportFileName";
public static final String EXCEL_TEMPLATE_FILE_NAME = "ExcelTemplateFileName";
public static final String EXCEL_DEFAULT_TEMPLATE_FILE = "dynamic-columns.xls";

/** The content type for an Excel response */
private static final String CONTENT_TYPE = "application/vnd.ms-excel";

public static final String EXCEL_TEMPLATE_HEADERS_NAME = "headers";
public static final String EXCEL_TEMPLATE_INDEXS_NAME = "indexs";
public static final String EXCEL_TEMPLATE_DATA_NAME = "data";

/**
* Default Constructor.
* Sets the content type of the view to "application/vnd.ms-excel".
*/
public JXLSExcelViewDynamicColumns() {
setContentType(CONTENT_TYPE);
}

@Override
protected boolean generatesDownloadContent() {
return true;
}

@SuppressWarnings("rawtypes")
@Override
protected void renderMergedOutputModel(Map<String, Object> model,
HttpServletRequest request, HttpServletResponse response)
throws Exception {
String fileName = (String)model.get(EXCEL_EXPORT_FILE_NAME);
response.setHeader("content-disposition","attachment; filename="+new String(fileName.getBytes("gb2312"),"ISO8859-1"));

String templateName = model.get(EXCEL_TEMPLATE_FILE_NAME) == null ? EXCEL_DEFAULT_TEMPLATE_FILE : model.get(EXCEL_TEMPLATE_FILE_NAME).toString() ;
String srcFilePath = "/resources/excel/" + templateName;

ServletOutputStream out = response.getOutputStream();
ServletContextResource resource = new ServletContextResource(request.getSession().getServletContext(), srcFilePath);

List headers = Arrays.asList((String[])model.get(EXCEL_TEMPLATE_HEADERS_NAME));
List indexs = Arrays.asList((String[])model.get(EXCEL_TEMPLATE_INDEXS_NAME));
String objectProps = StringUtils.join(indexs,",");

Context context = new Context();
context.putVar(EXCEL_TEMPLATE_HEADERS_NAME, headers);//headers是list
context.putVar(EXCEL_TEMPLATE_DATA_NAME, model.get(EXCEL_TEMPLATE_DATA_NAME));
JxlsHelper.getInstance().processGridTemplate(resource.getInputStream(), out, context,objectProps );//objectProps “a,b,c”字符串
out.flush();
}

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
3.excel-template模板

差点忘了，最新版本jxls的maven依赖：

<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls</artifactId>
<version>2.4.0</version>
</dependency>

<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-poi</artifactId>
<version>1.0.9</version>
</dependency>
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-jexcel</artifactId>
<version>1.0.6</version>
</dependency>
————————————————
版权声明：本文为CSDN博主「伊布拉西莫」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/it_freshman/article/details/73611452