POI下载excel通用方法

news/2024/12/29 18:47:30/

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");/*** 下载** @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.写入数据for (int i = 0; i < resultList.size(); i++) {T clazz = resultList.get(i);insertDataToCell(workbook, sheet, rowNum, clazz, titlesInfoKey);}//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;if (StringUtils.isNotBlank(fileName)) {tempExcelPath = File.separator + fileName + ".xls";} else {tempExcelPath = 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 rowNum* @param clazz* @param titlesInfoKey* @param <T>*/private static <T> void insertDataToCell(HSSFWorkbook workbook, HSSFSheet sheet, int rowNum, T clazz, String[] titlesInfoKey) {HSSFRow row = sheet.createRow(rowNum);row.setHeight((short) (15 * 20));for (int j = 0; j < titlesInfoKey.length; j++) {HSSFCell cell = row.createCell(j);HSSFCellStyle style5 = workbook.createCellStyle();style5.setAlignment(HSSFCellStyle.ALIGN_CENTER);style5.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);cell.setCellStyle(style5);//反射获取对象的值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);}}/*** 设置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 = 0;//描述的长度int descLength = 0;//当前要创建的excel的行数rowNum = 0;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.下载效果

在这里插入图片描述


http://www.ppmy.cn/news/797092.html

相关文章

惊曝！从没有公布的液晶电视天生缺陷！（液晶电视很容易烧毁？）

惊曝！从没有公布的液晶电视天生缺陷！（液晶电视很容易烧毁？）

阅读更多...
【洛谷】P5707 【深基2.例12】上学迟到

【洛谷】P5707 【深基2.例12】上学迟到

题目链接&#xff1a;https://www.luogu.com.cn/problem/P5707 【深基2.例12】上学迟到 题目描述 学校和 yyy 的家之间的距离为 s s s 米&#xff0c;而 yyy 以 v v v 米每分钟的速度匀速走向学校。 在上学的路上&#xff0c;yyy 还要额外花费 10 10 10 分钟的时间进行垃…
阅读更多...
解决每次新建word都有页眉和页脚

解决每次新建word都有页眉和页脚

直接删除Normal模板来恢复到初始状态&#xff1a; 1、关闭所有打开的Word文档&#xff1b; 2、复制这条命令&#xff1a; %appdata%\microsoft\templates 3、开始 → 运行 → 粘贴上面复制的命令 → 确定 4、在打开的目录下&#xff0c;找到并删除Normal.dot&#xff0c;然后重…
阅读更多...
Word2010版 ，每页上下都有一根粗黑线，但是不是页眉或页脚的下框线，怎么删除?

Word2010版 ，每页上下都有一根粗黑线，但是不是页眉或页脚的下框线，怎么删除?

word2007以上 都是同样的解决方法。 在页面布局菜单 页面背景 标签的右边 有一个 页面边框点开来 将上下横线设置为无 或者 左边的第一个框 无
阅读更多...
Windows下使用Word写论文时，删除页眉下多余的横线

Windows下使用Word写论文时，删除页眉下多余的横线

我使用的是word2016版本&#xff0c;在写毕业论文的时候&#xff0c;页眉上方总是出现这个横线导致格式不对&#xff0c;从网上查了很多资料也没有能用的&#xff0c;这个也是自己慢慢摸索出来滴~ 正文&#xff1a; 应该有很多同学用word写文档添加空页眉以后都会出现这个横线…
阅读更多...
word 2010下，如何批量删除Work的页眉和页脚，然后存为PDF文档

word 2010下，如何批量删除Work的页眉和页脚，然后存为PDF文档

今天有这个需要&#xff0c;就尝试写了一段宏。 目的是把一个目录下的所有的word文件的页眉页脚删除&#xff0c;然后存成PDF。 这样的好处是&#xff0c;在电纸书上看时&#xff0c;能清爽一些。 再加上&#xff0c;办公室里&#xff0c;闷得我喘不上气来&#xff0c;也只能…
阅读更多...
itext Pdf页眉/页脚/水印

itext Pdf页眉/页脚/水印

package com.company.common.core.utils.itext;import com.itextpdf.text.*; import com.itextpdf.text.pdf.*; import lombok.SneakyThrows;import java.io.IOException;/*** pdf的页眉页脚设置** author kally* date 2022/8/26*/ public class PdfHeaderFooter extends PdfPa…
阅读更多...
电脑重装系统word从第二页开始有页眉页脚如何设置

电脑重装系统word从第二页开始有页眉页脚如何设置

word第一页不要页眉页脚&#xff0c;从第二页开始有&#xff0c;如何设置&#xff1f;许多用户在使用Word文档的时候经常会用到这个功能&#xff0c;那么具体要怎么设置呢&#xff1f;其实方法也非常的简单&#xff0c;下面就讲讲电脑重装系统word从第二页开始有页眉页脚如何设…
阅读更多...
最新文章

Copyright @ 2022~2023