POI下载excel通用方法
POI下载excel通用方法
最近遇到一个业务是需要下载excel，使用POI,这里记录一下实现过程
1、导包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
2.公共方法的编写
package com.pingan.esbx.cassandra.util;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.util.CollectionUtils;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Method;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Objects;
public class ExcelDonload {
/**
* 26个英文字母表
**/
public static final String[] letters = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"};
/**
* 创建excel的行数(随描述 标题等的增加而增加)
**/
public static int rowNum = NumberUtils.INTEGER_ZERO;
/**
* 指定时间格式
*/
public static DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
/**
* 下载
* ①tableTitles 与 titlesInfoKey 个数要一致，不能为空
* ②fileName、title、subtitle、desc均可不传或者传null
* ③titlesInfoKey 是与tableTitles对应的字段，需与实体类一致
*
* @param title 标题
* @param resultList 数据
* @param fileName 文件名
* @param tableTitles 表头
* @param titlesInfoKey 表头对应的字段
* @param subtitle 副标题
* @param desc 描述
* @param response
*/
public static <T> void donloadExcel(
String title,
List<T> resultList,
String fileName,
String[] tableTitles,
String[] titlesInfoKey,
String subtitle,
String[] desc,
HttpServletResponse response
) {
//1.参数校验
if (CollectionUtils.isEmpty(resultList)
|| tableTitles.length <= NumberUtils.INTEGER_ZERO) {
throw new RuntimeException("请求参数不正确！");
}
if (resultList.size() > 60000) {
throw new RuntimeException("到处数据超过6w,不能导出！");
}
//2.创建Excel工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
//3.设置excel的 描述、标题、副标题、表头
setExcelTitle(workbook, sheet, title, tableTitles, subtitle, desc);
//4.写入数据
HSSFCellStyle dataStyle = workbook.createCellStyle();
dataStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
dataStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
for (int i = 0; i < resultList.size(); i++) {
T clazz = resultList.get(i);
insertDataToCell(sheet, clazz, titlesInfoKey, dataStyle);
}
//5.返回数据量
File file = returnExcelDataStream(fileName, workbook);
//6.导出
buildResponseExcelFile(file, response);
}
/**
* 下载文件流
*
* @param file
* @param response
*/
private static void buildResponseExcelFile(File file, HttpServletResponse response) {
InputStream in = null;
OutputStream out = null;
try {
in = new FileInputStream(file.getPath());
response.reset();
response.setHeader("Content-disposition", "attachment;filename=" + new String(file.getName().getBytes(), "iso-8859-1"));
response.setContentType("application/octet-stream");
response.addHeader("Context-Length", "" + file.length());
response.setCharacterEncoding("utf-8");
out = response.getOutputStream();
int b;
while ((b = in.read()) != -1) {
out.write(b);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (in != null) {
try {
in.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if (out != null) {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
file.delete();
}
/**
* 返回文件流
*
* @param fileName
* @param workbook
* @return
*/
private static File returnExcelDataStream(String fileName, HSSFWorkbook workbook) {
File file = null;
try {
String tempExcelPath = null;
//创建临时目录
File tempFile = File.createTempFile("tem", null);
//这里采用相对路径,防止某些部署是非root权限。而导致拿不到根目录，从而不能下载(不能用绝对路径)
String temPath = tempFile.getParent() + "/";
if (StringUtils.isNotBlank(fileName)) {
tempExcelPath = temPath + File.separator + fileName + ".xls";
} else {
tempExcelPath = temPath + File.separator + "report.xls";
}
file = new File(tempExcelPath);
file.deleteOnExit();
file.createNewFile();
FileOutputStream fileOutputStream = FileUtils.openOutputStream(file);
workbook.write(fileOutputStream);
fileOutputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
return file;
}
/**
* 通过反射获取字段值
*
* @param sheet
* @param clazz
* @param titlesInfoKey
* @param <T>
*/
private static <T> void insertDataToCell(HSSFSheet sheet, T clazz, String[] titlesInfoKey, HSSFCellStyle dataStyle) {
HSSFRow row = sheet.createRow(rowNum);
row.setHeight((short) (15 * 20));
for (int j = 0; j < titlesInfoKey.length; j++) {
HSSFCell cell = row.createCell(j);
cell.setCellStyle(dataStyle);
//反射获取对象的值
Object fieldValue = getValueByReflect(titlesInfoKey[j], clazz);
String cellResult = null;
//时间类型 Date 转换为字符串
if (fieldValue instanceof Date) {
cellResult = getStringTime((Date) fieldValue);
} else {
cellResult = Objects.isNull(fieldValue) ? "" : String.valueOf(fieldValue);
}
cell.setCellValue(cellResult);
}
rowNum += NumberUtils.INTEGER_ONE;
}
/**
* 设置excel的 描述、标题、副标题、表头
*
* @param workbook
* @param sheet
* @param title
* @param tableTitles
* @param subtitle
* @param desc
*/
private static void setExcelTitle(HSSFWorkbook workbook,
HSSFSheet sheet,
String title,
String[] tableTitles,
String subtitle,
String[] desc) {
//合并单元格的行数 desc描述的数组项数+(beginTime && endTime)+title标题
//$A$1:$I$1" 的含义是 第1行的A列到第1行的I列合并
//计算要合并的行数
int cellMergedRegionNm = NumberUtils.INTEGER_ZERO;
//描述的长度
int descLength = NumberUtils.INTEGER_ZERO;
//当前要创建的excel的行数
rowNum = NumberUtils.INTEGER_ZERO;
if (desc != null) {
descLength = desc.length;
cellMergedRegionNm += descLength;
rowNum += descLength;
}
//统计时间栏
if (StringUtils.isNotBlank(subtitle)) {
cellMergedRegionNm += NumberUtils.INTEGER_ONE;
}
//标题栏
if (StringUtils.isNotBlank(title)) {
cellMergedRegionNm += NumberUtils.INTEGER_ONE;
}
//获取表头的数量(他决定了合并单元格的列数)
int tableTitleLength = tableTitles.length;
//获取表头的长度对应的字母
String letter = getLetterByNum(tableTitleLength);
//设置需合并的行数与列数(循环次数是行数 ,$A$1:$I$1中的A代表第一列，I代表列的最后一列)
for (int i = 0; i < cellMergedRegionNm; i++) {
sheet.addMergedRegion(CellRangeAddress.valueOf("$A$" + (i + 1) + ":$" + letter + "$" + (i + 1)));
}
//设置颜色
HSSFPalette palette = workbook.getCustomPalette();
palette.setColorAtIndex((short) 9, (byte) 240, (byte) 240, (byte) 240);
palette.setColorAtIndex((short) 10, (byte) 255, (byte) 153, (byte) 102);
palette.setColorAtIndex((short) 11, (byte) 100, (byte) 149, (byte) 137);
palette.setColorAtIndex((short) 12, (byte) 176, (byte) 196, (byte) 222);
//字体
HSSFFont workbookFont = workbook.createFont();
workbookFont.setFontName("仿宋");
workbookFont.setFontHeightInPoints((short) 14);
//字体
HSSFFont titleFont = workbook.createFont();
titleFont.setFontName("黑体");
titleFont.setFontHeightInPoints((short) 18);
titleFont.setColor(IndexedColors.WHITE.index);
//单元格的风格1
HSSFCellStyle descStyleLong = workbook.createCellStyle();
descStyleLong.setFont(workbookFont);
descStyleLong.setFillForegroundColor((short) 9);
descStyleLong.setFillPattern(CellStyle.SOLID_FOREGROUND);
//单元格的风格2
HSSFCellStyle descStyleShort = workbook.createCellStyle();
descStyleShort.setFont(workbookFont);
descStyleShort.setFillForegroundColor((short) 10);
descStyleShort.setFillPattern(CellStyle.SOLID_FOREGROUND);
//单元格的风格3
HSSFCellStyle titleStyle = workbook.createCellStyle();
titleStyle.setFont(titleFont);
titleStyle.setFillForegroundColor((short) 11);
titleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//描述:设置描述，没有就不传
HSSFRow row = null;
HSSFCell cell = null;
if (descLength > 0) {
for (int i = 0; i < descLength; i++) {
row = sheet.createRow(i);
row.setHeight((short) (23 * 20));
cell = row.createCell(0);
cell.setCellValue(desc[i]);
if (i >= 4) {
row.setHeight((short) (40 * 20));
cell.setCellStyle(descStyleShort);
} else {
cell.setCellStyle(descStyleLong);
}
}
}
//设置标题：没有就不传
if (StringUtils.isNoneBlank(title)) {
row = sheet.createRow(rowNum);
row.setHeight((short) (60 * 20));
cell = row.createCell(0);
cell.setCellValue(title);
cell.setCellStyle(titleStyle);
rowNum += NumberUtils.INTEGER_ONE;
}
HSSFCellStyle styleTimeRange = workbook.createCellStyle();
styleTimeRange.setFont(titleFont);
styleTimeRange.setFillForegroundColor((short) 11);
styleTimeRange.setFillPattern(CellStyle.FINE_DOTS);
styleTimeRange.setAlignment(HSSFCellStyle.ALIGN_CENTER);
styleTimeRange.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//设置时间范围
if (StringUtils.isNotBlank(subtitle)) {
row = sheet.createRow(rowNum);
row.setHeight((short) (30 * 20));
cell = row.createCell(0);
cell.setCellValue(subtitle);
cell.setCellStyle(styleTimeRange);
rowNum += NumberUtils.INTEGER_ONE;
}
//设置表头字体
HSSFFont tableTitleFont = workbook.createFont();
tableTitleFont.setFontName("仿宋");
tableTitleFont.setFontHeightInPoints((short) 14);
tableTitleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//表头风格
HSSFCellStyle tableTitleStyle = workbook.createCellStyle();
tableTitleStyle.setFont(tableTitleFont);
tableTitleStyle.setFillForegroundColor((short) 12);
tableTitleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
tableTitleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
tableTitleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
tableTitleStyle.setLeftBorderColor(IndexedColors.WHITE.index);
tableTitleStyle.setRightBorderColor(IndexedColors.WHITE.index);
tableTitleStyle.setTopBorderColor(IndexedColors.WHITE.index);
tableTitleStyle.setBottomBorderColor(IndexedColors.WHITE.index);
tableTitleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
tableTitleStyle.setBorderTop(HSSFCellStyle.BORDER_THICK);
tableTitleStyle.setBorderRight(HSSFCellStyle.BORDER_THICK);
tableTitleStyle.setBorderBottom(HSSFCellStyle.BORDER_THICK);
if (tableTitles != null && tableTitles.length > 0) {
row = sheet.createRow(rowNum);
row.setHeight((short) (41.2 * 20));
for (int i = 0; i < tableTitles.length; i++) {
cell = row.createCell(i);
String tableTitle = tableTitles[i];
cell.setCellValue(tableTitle);
cell.setCellStyle(tableTitleStyle);
//根据标题长短设置单元格的宽窄
sheet.setColumnWidth(i, (tableTitle.length() * 5) * 256);
}
rowNum += NumberUtils.INTEGER_ONE;
}
}
private static String getLetterByNum(int tableTitleLength) {
return letters[tableTitleLength - NumberUtils.INTEGER_ONE];
}
/**
* 返回指定时间的指定字符串格式 yyyy-MM-dd HH:mm:ss
* SimpleDateFormat 线程不安全 所以枷锁
*/
public synchronized static String getStringTime(Date date) {
String format = dateFormat.format(date);
return format;
}
/**
* 通过反射获取字段值(通过get方法)
*
* @param fieldName
* @param t
* @param <T>
* @return
*/
public static <T> Object getValueByReflect(String fieldName, T t) {
String methodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
try {
Method method = t.getClass().getMethod(methodName);
Object fieldValue = method.invoke(t);
return fieldValue;
} catch (Exception e) {
return null;
}
}
}
3.调用示例
@RestController
@RequestMapping("/excel")
public class ExcelController {
private static final String[] TITLES_NONEXP_INFO_KEY = {
"time",
"consumerCompany",
"consumerSystem",
"consumerCode",
"providerCompany",
"providerSystem",
"providerCode",
"failCount",
"failRatio",
"totalCount"
};
private static final String[] TITLES_NONEXP_INFO = {
"时 间", "消费方公司", "消费方系统",
"消费方编码", "服务方公司",
"服务方系统", "服务方编码", "失败次数", "失败率", "总数"
};
@GetMapping("/exportExcel")
public void exportExcel(HttpServletRequest request, HttpServletResponse response) {
String title = "INVALID 月报表";
List<ReportDTO> list = new ArrayList<>();
String inputParams = "111111";
ReportDTO reportDTO = new ReportDTO();
reportDTO.setTime(new Date());
reportDTO.setConsumerCompany("集团总部");
reportDTO.setConsumerSystem("文件处理系统");
reportDTO.setConsumerCode("EIP_DPS");
reportDTO.setProviderCompany("中国科技");
reportDTO.setProviderSystem("中国智者统一搜索平台");
reportDTO.setProviderCode("PA011-ZHIZHE-USP-SE_PZHIZHE_ESG");
reportDTO.setFailCount(100);
reportDTO.setFailRatio(0.34);
reportDTO.setTotalCount(340);
list.add(reportDTO);
String fileName = "我的测试";
String[] desc = {
"报表中数据排序方法:",
"1.选中待排序的数据区域",
"2.excel菜单栏：【开始】->【格式】->【设置单元格】->【对齐】->【文本控制】:取消\"合并单元格\"选项\"",
"3.excel菜单栏:【开始】->【排序和筛选】->【自定义排序】",
"查询条件：",
JSON.toJSONString("这里可以写自己的查询条件")
};
String beginTime = "2023-7-6";
String endTime = "2023-7-7";
String subtitle = "统计时间范围【" + beginTime + "," + endTime + "】";
ExcelDonload.donloadExcel(
title,
list,
fileName,
TITLES_NONEXP_INFO,
TITLES_NONEXP_INFO_KEY,
subtitle,
desc,
response);
}
}
4.实体类
@Data
public class ReportDTO implements Serializable {
private Date time;
private String consumerCompany;
private String consumerSystem;
private String consumerCode;
private String providerCompany;
private String providerSystem;
private String providerCode;
private Integer failCount;
private Double failRatio;
private Integer totalCount;
}
5.下载效果
