一般导出excel分为2步,第一步为查询需要导出的数据,这个就不做阐述了,查询出一个list,然后开始导出excel:
首先,导出excel需要依赖一下jar包:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.16</version>
</dependency>
再把要导入的每一个title放到list里:
public List<String> exportTitleMake(XSSFSheet sheet) {
List<String> title = Lists.newArrayList();
title.add("导出测试");
return title;
}
然后开始导出
XSSFWorkbook workbook = new XSSFWorkbook();
OutputStream outputStream = null;
try {
workbook.createSheet();
XSSFSheet sheet = workbook.getSheetAt(0); //主要表单
CellStyle style = workbook.createCellStyle(); // 样式对象
style.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直
style.setAlignment(HorizontalAlignment.CENTER);// 水平
style.setWrapText(true);//自适应
List<String> title = exportTitleMake(sheet);
ExcelTools.createRow(sheet, 0, title);
int i = 1;
for (int j = 0; j < 10; j++) {
title.clear();
title.add("");
ExcelTools.createRow(sheet, i++, title);
}
ResponseHeaderForExcel.setResponseHeader(request, response, "导出测试");
outputStream = response.getOutputStream();
workbook.write(outputStream);
return true;
} catch (Exception e) {
log.error("failed to export, cause: {}", Throwables.getStackTraceAsString(e));
throw new JsonResponseException("export.fail");
}finally {
try {
workbook.close();
if (outputStream != null) {
outputStream.flush();
outputStream.close();
}
} catch (IOException io) {
log.error("failed to close workbook, cause:{}", Throwables.getStackTraceAsString(io));
}
}
for循环筛选出来的数据,然后title.add进去即可,注意每次add之前都要clear一下,最好不要使用clear,最好直接new 一个,就好了,下载excel的代码:
public abstract class ResponseHeaderForExcel {
public static void setResponseHeader(HttpServletRequest request, HttpServletResponse response, String fileName)
throws UnsupportedEncodingException {
fileName = fileName + ".xlsx";
final String userAgent = request.getHeader("USER-AGENT");
String finalFileName;
if(StringUtils.contains(userAgent.toUpperCase(), "MSIE")
|| StringUtils.contains(userAgent.toUpperCase(), "TRIDENT")
|| StringUtils.contains(userAgent.toUpperCase(), "EDGE") ){//IE浏览器
finalFileName = URLEncoder.encode(fileName,"UTF8");
}else if(StringUtils.contains(userAgent.toUpperCase(), "Mozilla".toUpperCase())){//safari,火狐浏览器
finalFileName = new String(fileName.getBytes(), "ISO8859-1");
}else{
finalFileName = URLEncoder.encode(fileName,"UTF8");//其他浏览器, 如chrome等
}
response.setHeader("Content-Disposition", "attachment; filename=\"" + finalFileName + "\"");
response.setContentType("application/octet-stream;charset=utf-8");
}
}
给excel每个单元格赋值的方法:
public static void createRow(Sheet sheet, int rowNum, List<String> contents) {
Workbook workbook = sheet.getWorkbook();
CellStyle textStyle;
if (workbook.getNumCellStyles() > 0) {
textStyle = workbook.getCellStyleAt(0);
} else {
textStyle = workbook.createCellStyle();
}
DataFormat format = workbook.createDataFormat();
textStyle.setDataFormat(format.getFormat("@"));
CellStyle style;
if (workbook.getNumCellStyles() == 0) {
style = workbook.createCellStyle();
} else {
style = workbook.getCellStyleAt(0);
}
style.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直
style.setAlignment(HorizontalAlignment.CENTER);// 水平
style.setWrapText(true);//自适应
Row row = sheet.createRow(rowNum);
row.setRowStyle(style);
for (int i = 0; i < contents.size(); i++) {
Cell cell = row.createCell(i);
cell.setCellStyle(style);
DataFormat df = workbook.createDataFormat();
if (contents.get(i) == null) {
cell.setCellValue("");
} else if (isNumeric(contents.get(i))) {
cell.setCellType(CellType.NUMERIC);
cell.setCellValue(Double.parseDouble(contents.get(i)));
textStyle.setDataFormat(df.getFormat("#,##0.00"));
} else {
cell.setCellType(CellType.STRING);
cell.setCellValue(contents.get(i));
}
}
}
就ok了
ps:会有个问题,就是导出excel报错,但是可以正常导出,报的错是:org.eclipse.jetty.io.EofException: Closed
网上搜了一下说是内存不足,可是实际上我只导出来两三条数据,后来发现是接口设置了返回boolean类型,改成void就行了,原因是返回了一个文件,又返回了一个boolean值,跟http的header有关,具体没研究