excel导出
自动设置宽度，设置标题框，设置数据边框。
excel导出
- 添加依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.2</version>
</dependency>
- 编写工具类
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;
}
- 导出示例
@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);
}
运行结果
