当前位置： 首页 > news >正文

apache-poi导出excel数据

news 2025/6/24 23:13:47

excel导出

自动设置宽度，设置标题框，设置数据边框。

excel导出

  1. 添加依赖
 <dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>5.2.2</version></dependency>
  1. 编写工具类
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.text.SimpleDateFormat;
import java.util.*;/*** Excel导出**/
@Slf4j
public class MyExcelExportUtils {/*** 列的最大宽度*/private static final int COLUMN_MAX_WIDTH = 10240;/*** 列的最小宽度*/private static final int COLUMN_MIN_WIDTH = (int) (2048);/*** 导出Excel** @param excelExports excel集合* @param fileName     文件名* @param response     响应对象* @throws IOException*/public static void exportExcel(List<ExcelExport> excelExports, String fileName, HttpServletResponse response)throws IOException {
//        ServletOutputStream servletOutputStream = null;try {
//            servletOutputStream = response.getOutputStream();response.setContentType("multipart/form-data");response.setCharacterEncoding("utf-8");if (null == fileName || fileName.trim().equals("")) { //如果不设置文件名，则默认fileName = new String((fileName + new SimpleDateFormat("yyyy-MM-dd").format(new Date())).getBytes(), StandardCharsets.UTF_8);}response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");Workbook workbook = new XSSFWorkbook();// 创建一个单元格样式CellStyle titleCellStyle = workbook.createCellStyle();titleCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//25%灰色titleCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);titleCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());titleCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());titleCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());titleCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());// 设置边框样式为细线titleCellStyle.setBorderBottom(BorderStyle.THIN);titleCellStyle.setBorderTop(BorderStyle.THIN);titleCellStyle.setBorderLeft(BorderStyle.THIN);titleCellStyle.setBorderRight(BorderStyle.THIN);titleCellStyle.setAlignment(HorizontalAlignment.CENTER);titleCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 创建一个单元格样式CellStyle dataCellStyle = workbook.createCellStyle();dataCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());dataCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());// 设置边框样式为细线dataCellStyle.setBorderLeft(BorderStyle.THIN);dataCellStyle.setBorderRight(BorderStyle.THIN);// 创建一个单元格样式CellStyle lastCellStyle = workbook.createCellStyle();lastCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());lastCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());lastCellStyle.setBorderBottom(BorderStyle.THIN);// 设置边框样式为细线lastCellStyle.setBorderLeft(BorderStyle.THIN);lastCellStyle.setBorderRight(BorderStyle.THIN);lastCellStyle.setBorderBottom(BorderStyle.THIN);for (int i = 0; i < excelExports.size(); i++) {ExcelExport excelExport = excelExports.get(i);List<String> headList = excelExport.getTitle();String sheetName = excelExport.getSheetName();List<LinkedHashMap<String, Object>> dataList = excelExport.getDataList();Sheet sheet = workbook.createSheet(sheetName);// 创建数据行Row titleRow = sheet.createRow(0);titleRow.setHeightInPoints(500 / 20);for (int j = 0; j < headList.size(); j++) {Cell cell1 = titleRow.createCell(j);cell1.setCellStyle(titleCellStyle);cell1.setCellValue(headList.get(j));}Map<String, Integer> columnWidthMap = new HashMap<>();for (int k = 0; k < dataList.size(); k++) {Row dataRow = sheet.createRow(k + 1);for (int j = 0; j < headList.size(); j++) {String title = headList.get(j);LinkedHashMap<String, Object> stringObjectLinkedHashMap = dataList.get(k);Object value = stringObjectLinkedHashMap.get(title);Cell cell = dataRow.createCell(j);if (k == (dataList.size() - 1)) {cell.setCellStyle(lastCellStyle);} else {cell.setCellStyle(dataCellStyle);}cell.setCellValue(value == null ? null : value.toString());if (null != value) {// 计算内容的字符长度int charLength = value.toString().length();// 设置一个最大宽度（以字符为单位）int maxWidthInChars = 0;// 根据内容长度和最大宽度计算列宽// 注意：Excel的列宽单位是字符宽度的1/256，因此需要将字符数乘以256// 但由于内容可能包含非单字节字符（如中文），这里简化处理，只考虑ASCII字符宽度// 如果需要更精确的处理，可以考虑使用FontMetrics来计算实际渲染宽度int columnWidth = (int) (Math.max(charLength, maxWidthInChars) * 256);if (columnWidthMap.containsKey(title)) {Integer width = columnWidthMap.get(title);if (width < columnWidth) {columnWidthMap.put(title, columnWidth);}} else {columnWidthMap.put(title, columnWidth);}}}}for (int j = 0; j < headList.size(); j++) {String title = headList.get(j);int width = columnWidthMap.getOrDefault(title,COLUMN_MIN_WIDTH);if (width > COLUMN_MAX_WIDTH) { //防止太长width = COLUMN_MAX_WIDTH;} else if (width < COLUMN_MIN_WIDTH) {width = COLUMN_MIN_WIDTH;}sheet.setColumnWidth(j, width);log.info("列:" + j + ",标题:" + title + ",宽度:" + width);
//                    sheet.autoSizeColumn(j);//自动行宽}}workbook.write(response.getOutputStream());workbook.close();
//            servletOutputStream.flush();} catch (IOException e) {throw new IOException(e.toString());} finally {}}public static void exportExcel2(List<String> headList, List<Object[]> dataList, String sheetName, String fileName, HttpServletResponse response)throws IOException {try {response.setContentType("multipart/form-data");response.setCharacterEncoding("utf-8");if (null == fileName || fileName.trim().equals("")) { //如果不设置文件名，则默认fileName = new String((fileName + new SimpleDateFormat("yyyy-MM-dd").format(new Date())).getBytes(), StandardCharsets.UTF_8);}response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");Workbook workbook = new XSSFWorkbook();// 创建一个单元格样式CellStyle titleCellStyle = workbook.createCellStyle();titleCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//25%灰色titleCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);titleCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());titleCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());titleCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());titleCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());// 设置边框样式为细线titleCellStyle.setBorderBottom(BorderStyle.THIN);titleCellStyle.setBorderTop(BorderStyle.THIN);titleCellStyle.setBorderLeft(BorderStyle.THIN);titleCellStyle.setBorderRight(BorderStyle.THIN);titleCellStyle.setAlignment(HorizontalAlignment.CENTER);titleCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 创建一个单元格样式CellStyle dataCellStyle = workbook.createCellStyle();dataCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());dataCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());// 设置边框样式为细线dataCellStyle.setBorderLeft(BorderStyle.THIN);dataCellStyle.setBorderRight(BorderStyle.THIN);// 创建一个单元格样式CellStyle lastCellStyle = workbook.createCellStyle();lastCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());lastCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());lastCellStyle.setBorderBottom(BorderStyle.THIN);// 设置边框样式为细线lastCellStyle.setBorderLeft(BorderStyle.THIN);lastCellStyle.setBorderRight(BorderStyle.THIN);lastCellStyle.setBorderBottom(BorderStyle.THIN);Sheet sheet = workbook.createSheet(sheetName);// 创建数据行Row titleRow = sheet.createRow(0);titleRow.setHeightInPoints(500 / 20);
//                titleRow.setHeight((short)500);for (int j = 0; j < headList.size(); j++) {Cell cell1 = titleRow.createCell(j);cell1.setCellStyle(titleCellStyle);cell1.setCellValue(headList.get(j));}Map<String, Integer> columnWidthMap = new HashMap<>();for (int k = 0; k < dataList.size(); k++) {Row dataRow = sheet.createRow(k + 1);for (int j = 0; j < headList.size(); j++) {String title = headList.get(j);
//                    LinkedHashMap<String, Object> stringObjectLinkedHashMap = dataList.get(k);
//                    Object value = stringObjectLinkedHashMap.get(title);Object value = dataList.get(k)[j];Cell cell = dataRow.createCell(j);if (k == (dataList.size() - 1)) {cell.setCellStyle(lastCellStyle);} else {cell.setCellStyle(dataCellStyle);}cell.setCellValue(value == null ? null : value.toString());if (null != value) {// 计算内容的字符长度int charLength = value.toString().length();// 设置一个最大宽度（以字符为单位）int maxWidthInChars = 0;// 根据内容长度和最大宽度计算列宽// 注意：Excel的列宽单位是字符宽度的1/256，因此需要将字符数乘以256// 但由于内容可能包含非单字节字符（如中文），这里简化处理，只考虑ASCII字符宽度// 如果需要更精确的处理，可以考虑使用FontMetrics来计算实际渲染宽度int columnWidth = (int) (Math.max(charLength, maxWidthInChars) * 256);if (columnWidthMap.containsKey(title)) {Integer width = columnWidthMap.get(title);if (width < columnWidth) {columnWidthMap.put(title, columnWidth);}} else {columnWidthMap.put(title, columnWidth);}}}}for (int j = 0; j < headList.size(); j++) {String title = headList.get(j);int width = columnWidthMap.getOrDefault(title,COLUMN_MIN_WIDTH);if (width > COLUMN_MAX_WIDTH) { //防止太长width = COLUMN_MAX_WIDTH;} else if (width < COLUMN_MIN_WIDTH) {width = COLUMN_MIN_WIDTH;}sheet.setColumnWidth(j, width);
//                log.info("列:" + j + ",宽度:" + width);log.info("列:" + j + ",标题:" + title + ",宽度:" + width);
//                    sheet.autoSizeColumn(j);//自动行宽}workbook.write(response.getOutputStream());workbook.close();
//            servletOutputStream.flush();} catch (IOException e) {throw new IOException(e.toString());} finally {
//            if (servletOutputStream != null) {
//                servletOutputStream.close();
//            }}}/*** 导出Excel** @param dataList  数据集合* @param sheetName Excel工作表名称* @param fileName  文件名* @param response  响应对象* @throws IOException*/public static void exportExcel3(List<ColumnData> dataList, String sheetName, String fileName, HttpServletResponse response)throws IOException {List<String> headList = new ArrayList<>();int maxColumn = 0;for (ColumnData columnData : dataList) {headList.add(columnData.getTitle());maxColumn = Math.max(maxColumn, columnData.getValues().size());}int titleLength = headList.size();List<Object[]> dataList2 = new ArrayList<>();for (int i = 0; i < maxColumn; i++) {Object[] objects = new Object[titleLength];for (int j = 0; j < titleLength; j++) {ColumnData columnData = dataList.get(j);Object obj = getDataFromColumnData(columnData, i);objects[j] = obj;}dataList2.add(objects);}exportExcel2(headList, dataList2, sheetName, fileName, response);}private static Object getDataFromColumnData(ColumnData columnData, int index) {List values = columnData.getValues();if (index < values.size()) {return values.get(index);}return null;}
}
public class ColumnData {/*** 标题*/private String title;/*** 竖直的数据*/private List values;public String getTitle() {return title;}public void setTitle(String title) {this.title = title;}public List getValues() {return values;}public void setValues(List values) {this.values = values;}
}
@Setter
@Getter
public class ExcelExport {/*** 页面名称*/private String sheetName;/*** 标题*/private List<String> title;/*** 数据内容*/List<LinkedHashMap<String, Object>> dataList;
}
  1. 导出示例
@GetMapping("/exportExcel2")public void exportExcel2(HttpServletRequest request, HttpServletResponse response) throws IOException {ExcelExport excelExport = new ExcelExport();excelExport.setSheetName("测试");excelExport.setTitle(Arrays.asList("用户名", "地址"));List<LinkedHashMap<String, Object>> dataList = new ArrayList();{LinkedHashMap<String, Object> map = new LinkedHashMap();map.put("用户名","test1");map.put("地址","长安");dataList.add(map);}{LinkedHashMap<String, Object> map = new LinkedHashMap();map.put("用户名","test2");map.put("地址","静海");dataList.add(map);}excelExport.setDataList(dataList);MyExcelExportUtils.exportExcel(Arrays.asList(excelExport), "test", response);}

运行结果
在这里插入图片描述

http://www.lryc.cn/news/534702.html

相关文章：

  • 唯一值校验的实现思路(续)
  • ffmpeg基本用法
  • MYSQL第四次
  • 联德胜w801开发板（六）手机蓝牙设置wifi名称和密码
  • Linux：库
  • 向量数据库简单对比
  • 大模型基本原理（四）——如何武装ChatGPT
  • 从零开始：使用Jenkins实现高效自动化部署
  • Spring Cloud工程完善
  • SSM仓库物品管理系统 附带详细运行指导视频
  • UI自动化测试中如何处理验证码？
  • 华为交换机堆叠配置
  • Vue 和 dhtmlx-gantt 实现图表构建动态多级甘特图效果 ，横坐标为动态刻度不是日期
  • collabora online+nextcloud+mariadb在线文档协助
  • “可通过HTTP获取远端WWW服务信息”漏洞修复
  • 【AI时代】-开发环境准备 之 Conda 创建 Python 环境 (含pip常用命令、jupyter 安装及汉化、自定义文档位置等配置)
  • [LeetCode] day19 454. 四数相加 II
  • LeetCodehot 力扣热题100 验证二叉搜索树
  • 四次挥手详解
  • Deepseek-v3 / Dify api接入飞书机器人go程序
  • 2025.2.9 每日学习记录2：技术报告写了一半+一点点读后感
  • qml ToolBar详解
  • 机器学习在癌症分子亚型分类中的应用
  • Ansible自动化部署K8s集群一 Ansible的基础使用实战
  • ZooKeeper Watcher 机制详解：从注册到回调的全过程
  • flutter_tools/gradle Unsupported class file major version 65 问题解决
  • C++设计模式 - 模板模式
  • mysql查缺补漏
  • 跨越边界，大模型如何助推科技与社会的完美结合？
  • 哪吒闹海！SCI算法+分解组合+四模型原创对比首发！SGMD-FATA-Transformer-LSTM多变量时序预测