POI下载excel通用方法

方法"/>

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.apachemons.io.FileUtils;
import org.apachemons.lang3.StringUtils;
import org.apachemons.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);//单元格的风格1HSSFCellStyle descStyleLong = workbook.createCellStyle();descStyleLong.setFont(workbookFont);descStyleLong.setFillForegroundColor((short) 9);descStyleLong.setFillPattern(CellStyle.SOLID_FOREGROUND);//单元格的风格2HSSFCellStyle descStyleShort = workbook.createCellStyle();descStyleShort.setFont(workbookFont);descStyleShort.setFillForegroundColor((short) 10);descStyleShort.setFillPattern(CellStyle.SOLID_FOREGROUND);//单元格的风格3HSSFCellStyle 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.下载效果