背景:
在导出Excel数据时遇到非常复杂的表头,怎么处理更加简单优雅呢?网上主要流传着两种解决方式,一种是用代码合并拼接表头,一种是在Excel中画好模板,导入时加载模板然后填充数据。相比之下第二种解决方式就显得非常优雅而简约,因为第一种用代码合并拼接表头最头疼的是计算行列、合并行列,然后再手动往不规则的表头中填入数据。而使用第二种相当于把合并表头部分在Excel中可视化处理了,在写数据时只需要加载模板再写入数据就OK了。
代码:
工具类代码:
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.Objects;
/**
* 指定模板导出Excel工具类
* 主要适用于【复杂的固定表头】导出数据,手动在Excel中把表模板做好后使用该方法填充数据。
*
* @author hongzhou.wei
* @date 2020/6/3
*/
public class ExcelSpecifyTemplateExportUtils {
private static final long serialVersionUID = 1L;
/**
* 主要适用于复杂的固定表头,先手动在Excel中把表头做好后,使用该方法填充数据。
* 向指定模板填充数据,生成excel并下载
*
* @param dataRows 数据集
* @param fieldsName 填充的字段名,用英文逗号分隔
* @param fillStartRow 填充起始行
* @param fileName 文件名
* @param templatePath 模板名
*/
public static void exportExcel(List<Map<String, Object>> dataRows, String fieldsName, int fillStartRow, String fileName, String templatePath) {
File newFile = createNewFile(fileName, templatePath);
// 新文件写入数据,并下载
InputStream is = null;
XSSFWorkbook workbook = null;
XSSFSheet sheet = null;
try {
// 将excel文件转为输入流
is = new FileInputStream(newFile);
// 创建workbook
workbook = new XSSFWorkbook(is);
// 获取第一个sheet
sheet = workbook.getSheetAt(0);
} catch (Exception e1) {
e1.printStackTrace();
}
if (sheet != null) {
try {
// 写数据
FileOutputStream fos = new FileOutputStream(newFile);
// 设定单元格样式-字体10大小、居中、加边框
XSSFCellStyle cellStyle = createCellStyle(workbook, (short) 10, false, true, true);
// 写入业务数据
XSSFRow row = sheet.createRow(fillStartRow);
XSSFCell cell = row.createCell(0);
String[] fields = fieldsName.split(",");
for (int r = 0; r < dataRows.size(); r++) {
Map<String, Object> curRowMap = dataRows.get(r);
row = sheet.createRow(r + fillStartRow);
for (int c = 0; c < fields.length; c++) {
String curCellVal = !Objects.isNull(curRowMap.get(fields[c])) ? curRowMap.get(fields[c]).toString() : "";
cell = row.createCell(c);
cell.setCellStyle(cellStyle);
cell.setCellValue(curCellVal);
}
}
workbook.write(fos);
fos.flush();
fos.close();
// 下载
InputStream fis = new BufferedInputStream(new FileInputStream(
newFile));
// 能够凭空获取 response (spring-web下的类)
HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.currentRequestAttributes()).getResponse();
byte[] buffer = new byte[fis.available()];
fis.read(buffer);
fis.close();
response.reset();
response.setContentType("text/html;charset=UTF-8");
OutputStream toClient = new BufferedOutputStream(
response.getOutputStream());
response.setContentType("application/x-msdownload");
String newName = URLEncoder.encode(
fileName + System.currentTimeMillis() + ".xlsx",
"UTF-8");
response.addHeader("Content-Disposition",
"attachment;filename=\"" + newName + "\"");
response.addHeader("Content-Length", "" + newFile.length());
toClient.write(buffer);
toClient.flush();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (null != is) {
is.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
// 删除创建的新文件
deleteFile(newFile);
}
/**
* 复制文件
*
* @param s 源文件
* @param t 复制到的新文件
*/
private static void fileChannelCopy(File s, File t) {
try {
InputStream in = null;
OutputStream out = null;
try {
in = new BufferedInputStream(new FileInputStream(s), 1024);
out = new BufferedOutputStream(new FileOutputStream(t), 1024);
byte[] buffer = new byte[1024];
int len;
while ((len = in.read(buffer)) != -1) {
out.write(buffer, 0, len);
}
} finally {
if (null != in) {
in.close();
}
if (null != out) {
out.close();
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 读取excel模板,并复制到新文件中供写入和下载
*
* @return
*/
private static File createNewFile(String fileName, String templatePath) {
// 读取模板,并赋值到新文件
// 文件模板路径
String path = Objects.requireNonNull(Thread.currentThread().getContextClassLoader().getResource(templatePath)).getPath();
File file = new File(path);
// 保存文件的路径(默认在该项目所在盘符下的这个目录下)
String realPath = "/tempDir";
// 新的文件名
String newFileName = fileName + System.currentTimeMillis() + ".xlsx";
// 判断路径是否存在
File dir = new File(realPath);
if (!dir.exists()) {
dir.mkdirs();
}
// 写入到新的excel
File newFile = new File(realPath, newFileName);
try {
newFile.createNewFile();
// 复制模板到新文件
fileChannelCopy(file, newFile);
} catch (Exception e) {
e.printStackTrace();
}
return newFile;
}
/**
* 下载成功后删除
*
* @param files
*/
private static void deleteFile(File... files) {
for (File file : files) {
if (file.exists()) {
file.delete();
}
}
}
/**
* 设置表格样式
*
* @param workbook 工作表
* @param fontSize 字体大小
* @param isBold 是否加粗
* @param isAlignment 是否水平居中
* @param isBorder 是否加外边框
* @return
*/
private static XSSFCellStyle createCellStyle(XSSFWorkbook workbook, short fontSize, boolean isBold, boolean isAlignment, boolean isBorder) {
XSSFCellStyle style = workbook.createCellStyle();
// 是否水平居中
if (isAlignment) {
//水平居中
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
}
// 垂直居中
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
// 是否加边框
if (isBorder) {
style.setBorderTop(XSSFCellStyle.BORDER_THIN);
style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
style.setBorderRight(XSSFCellStyle.BORDER_THIN);
style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
}
// 创建字体
XSSFFont font = workbook.createFont();
// 是否加粗字体
if (isBold) {
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
}
font.setFontHeightInPoints(fontSize);
// 加载字体
style.setFont(font);
return style;
}
}
使用代码:
public void xxxExport() {
// 查询需要导出的数据
List<Map<String, Object>> dataRows = xxxDao.xxxExport();
// 导出的数据字段名-与上面map中的键对应
String fieldsName = "majorName,xxx,yyy,zzz";
// 填充起始行
int fillStartRow = 4;
// 文件名
String fileName = "XXX统计表";
// 模板路径,resources的路径相对路径
String templatePath = "/templateFile/xxxExportTemplate.xlsx";
// 处理导出Excel
ExcelSpecifyTemplateExportUtils.exportExcel(dataRows, fieldsName, fillStartRow, fileName, templatePath);
}
注意:
使用过程如果报 java.util.zip.ZipException: invalid stored block lengths错。则在maven中增加插件:
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<version>2.6</version>
<artifactId>maven-resources-plugin</artifactId>
<configuration>
<encoding>UTF-8</encoding>
<nonFilteredFileExtensions>
<nonFilteredFileExtension>xlsx</nonFilteredFileExtension>
</nonFilteredFileExtensions>
</configuration>
</plugin>