1.添加poi依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
2.工具类
package com.example.importks.util;
import java.io.IOException;
import com.example.importks.entity.ExcelData;
import jakarta.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.awt.Color;
import java.net.URLEncoder;
import java.util.Map;
/**
* @author yx
* @date 2023/11/10 0010 16:18
* 导出工具类
*/
public class ExcelExportUtil {
public static void exportExcel(HttpServletResponse response, String fileName, String sheetName, String[][] titles, List<Map<String,Object>> rows) throws Exception {
// 告诉浏览器用什么软件可以打开此文件
response.setHeader("content-Type", "application/vnd.ms-excel");
// 下载文件的默认名称
response.setHeader("Content-Disposition", "attachment;filename="+URLEncoder.encode(fileName, "utf-8"));
exportExcel(sheetName, titles, rows, response.getOutputStream());
}
public static void exportExcel(String sheetName, String[][] titles, List<Map<String,Object>> rows, OutputStream out) throws Exception {
XSSFWorkbook wb = new XSSFWorkbook();
try {
// String sheetName = data.getName();
if (null == sheetName || "".equals(sheetName)) {
sheetName = "Sheet1";
}
XSSFSheet sheet = wb.createSheet(sheetName);
writeExcel(wb, sheet, titles, rows);
wb.write(out);
} catch(Exception e){
e.printStackTrace();
}finally{
//此处需要关闭 wb 变量
out.close();
}
}
private static void writeExcel(XSSFWorkbook wb, Sheet sheet, String[][] titles,List<Map<String,Object>> rows) {
int rowIndex = 0;
rowIndex = writeTitlesToExcel(wb, sheet, titles);
writeRowsToExcel(wb, sheet, titles, rows, rowIndex);
autoSizeColumns(sheet, titles.length + 1);
}
private static int writeTitlesToExcel(XSSFWorkbook wb, Sheet sheet, String[][] titles) {
int rowIndex = 0;
int colIndex = 0;
XSSFFont titleFont = wb.createFont();
titleFont.setFontName("simsun");
titleFont.setColor(IndexedColors.BLACK.index);
XSSFCellStyle titleStyle = wb.createCellStyle();
// titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
// titleStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
//titleStyle.setFillForegroundColor(new XSSFColor(new Color(182, 184, 192)));
// titleStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
titleStyle.setFont(titleFont);
//setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));
Row titleRow = sheet.createRow(rowIndex);
colIndex = 0;
//要保证导出表头和字段一一对应上
for(int i=0;i<titles.length;i++){
String field = titles[i][1];
// System.out.println(colIndex+":"+field);
Cell cell = titleRow.createCell(colIndex);
cell.setCellValue(field);
cell.setCellStyle(titleStyle);
colIndex++;
}
rowIndex++;
return rowIndex;
}
private static int writeRowsToExcel(XSSFWorkbook wb, Sheet sheet, String[][] titles, List<Map<String,Object>> rows, int rowIndex) {
int colIndex = 0;
XSSFFont dataFont = wb.createFont();
dataFont.setFontName("simsun");
dataFont.setColor(IndexedColors.BLACK.index);
XSSFCellStyle dataStyle = wb.createCellStyle();
//dataStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
//dataStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
dataStyle.setFont(dataFont);
// setBorder(dataStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));
for (Map<String,Object> rowData : rows) {
Row dataRow = sheet.createRow(rowIndex);
colIndex = 0;
// 表头titles(二元数组)每一行的第一个数作为Map的key,查出列表每一行的字段对应的值,塞进excel
for (int i=0;i<titles.length;i++) {
// System.out.println("titles[i][0]:"+titles[i][0]);
String cellData = rowData.get(titles[i][0]).toString();
Cell cell = dataRow.createCell(colIndex);
if (cellData != null) {
cell.setCellValue(cellData);
} else {
cell.setCellValue("");
}
cell.setCellStyle(dataStyle);
colIndex++;
}
rowIndex++;
}
return rowIndex;
}
private static void autoSizeColumns(Sheet sheet, int columnNumber) {
for (int i = 0; i < columnNumber; i++) {
int orgWidth = sheet.getColumnWidth(i);
sheet.autoSizeColumn(i, true);
int newWidth = (int) (sheet.getColumnWidth(i) + 100);
if (newWidth > orgWidth) {
sheet.setColumnWidth(i, newWidth);
} else {
sheet.setColumnWidth(i, orgWidth);
}
}
}
private static void setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color) {
style.setBorderTop(border);
style.setBorderLeft(border);
style.setBorderRight(border);
style.setBorderBottom(border);
style.setBorderColor(XSSFCellBorder.BorderSide.TOP, color);
style.setBorderColor(XSSFCellBorder.BorderSide.LEFT, color);
style.setBorderColor(XSSFCellBorder.BorderSide.RIGHT, color);
style.setBorderColor(XSSFCellBorder.BorderSide.BOTTOM, color);
}
}
可以service直接调用,有表头,sheet名字,注意查询列表类型。