apache-poi导出excel数据

excel导出

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

excel导出

1. 添加依赖

 <dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>5.2.2</version></dependency>

2. 编写工具类

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;
}

3. 导出示例

@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);}

运行结果