Java使用poi组件导出excel报表,能导出excel报表的还可以使用jxl组件,但jxl想对于poi功能有限,jxl应该不能载excel插入浮动层图片,poi能很好的实现输出excel各种功能,介绍poi导出excel功能实现案例,算比较常用的功能实现以及导出excel需要注意的地方。
环境配置
这里使用的为3.17版本的poi
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.17</version>
</dependency>
一、导出模板
这里我们就不多BB了,直接上代码
所需util
package com.ecb.userCenter.common.util;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
/**
* @author Zuo Ning
* @date 2019/7/24 16:24
* @des 导出excel模板
*/
@Slf4j
public class ExcelUtils {
public static void exportExcelUsers(HttpServletResponse response, String[] handers, String excleName) throws Exception {
try {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("sheet1");
HSSFCellStyle style = disFont(wb);
HSSFRow rowFirst = sheet.createRow(0);
rowFirst.setHeight((short) 300);
// 设置列宽
for (int i = 0; i < handers.length; i++) {
sheet.setColumnWidth((short) i, (short) 8300);
}
for (int i = 0; i < handers.length; i++) {
HSSFCell cell = rowFirst.createCell(i);
cell.setCellStyle(style);
cell.setCellValue(handers[i]);
}
//写出文件(path为文件路径含文件名)
OutputStream output = response.getOutputStream();
response.reset();
response.setHeader("Content-disposition", "attachment; filename=" + excleName + ".xls");
response.setContentType("application/msexcel");
wb.write(output);
output.close();
} catch (Exception e) {
log.error(e.getMessage());
throw e;
}
}
/**
* 设置excel样式
*
* @param wb
* @return
*/
public static HSSFCellStyle disFont(HSSFWorkbook wb) {
HSSFCellStyle style = wb.createCellStyle();
style.setBorderBottom(BorderStyle.THIN);//下边框
style.setBorderLeft(BorderStyle.THIN);//左边框
style.setBorderRight(BorderStyle.THIN);//右边框
style.setBorderTop(BorderStyle.THIN); //上边框
style.setAlignment(HorizontalAlignment.CENTER);//水平居中
style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setFillForegroundColor(IndexedColors.WHITE.index);
HSSFFont font = wb.createFont();
// font.setFontName("华文行楷");//设置字体名称
font.setFontHeightInPoints((short) 10);//设置字号
font.setItalic(false);//设置是否为斜体
font.setBold(true);//设置是否加粗
style.setFont(font);
return style;
}
}
servie层
//service实现层
public void exportWhitelistTemplate(HttpServletResponse response) {
try {
String time = DateUtils.getDate4yyyymmddhhmmss();
String excelName = java.net.URLEncoder.encode("用户白名单模板-" + time, "UTF-8");
String[] handers = {"姓名", "手机号"};
// 1导入硬盘
ExcelUtils.exportExcelUsers(response, handers, excelName);
} catch (Exception e) {
log.error(e.getMessage());
Result.fail(ResultCode.EXCEPTION_OTHER.getCode(), "导出失败");
}
}
controller层
//controller层
@GetMapping("/exportWhitelistTemplate")
@ApiOperation("导出用户白名单模板")
@IgnoreToken
public Result exportWhitelistTemplate(HttpServletResponse response) {
userWhitelistService.exportWhitelistTemplate(response);
return null;
}
导出后样式
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-93DXCwwN-1578555718786)(http://39.105.146.77:5927/upload/2020/1/4-d8a0297ad0c349158d0f63119a12d3f7.png)]
这里可以根据自己需求更改handers从而完成自己的需求
二、导出
这里是导出带数据的,并且可以根据需求做一些处理
@Override
public void redemptionPackageDownLoad(HttpServletResponse response, OilCardLedgerQueryParameters params) {