在Spring或者SpringBoot程序里，生成Excel文件比较方便，Spring已经通过org.springframework.web.servlet.view.document.AbstractXlsxView提供了对Excel的支持，我们只需要实现它就可以。
首先引入apache poi包，修改pom.xml：
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.0</version>
</dependency>
新建ExcelView extends AbstractXlsxView
package com.terrynow.util;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.util.StringUtils;
import org.springframework.web.servlet.view.document.AbstractXlsxView;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
/**
* @date 2021-2-2 11:10
* @description
*/
public class ExcelView extends AbstractXlsxView {
//这里的Key用在HashMap里的key，携带value数据，在Controller里作为Map<String, Object> model传给ModelAndView，
//然后在下面的方法buildExcelDocument里取出来，组装成excel
//所以可以根据自己的需要，自定义一些key，来自定义你的Excel
public static final String KEY_EXCEL_FILE_NAME = "KEY_EXCEL_FILE_NAME";
public static final String KEY_EXCEL_SHEET_NAME = "KEY_EXCEL_SHEET_NAME";
public static final String KEY_EXCEL_TITLE = "KEY_EXCEL_FILE_TITLE";
//表头，数据是一个String数组
public static final String KEY_EXCEL_HEADERS = "KEY_EXCEL_HEADERS";
//Excel的主体数据，value是一个List<String[]>
public static final String KEY_EXCEL_DATA = "KEY_EXCEL_DATA";
public static final String KEY_EXCEL_BORDER = "KEY_EXCEL_DATA_BORDER";
@Override
protected void buildExcelDocument(Map<String, Object> model, Workbook workbook,
HttpServletRequest httpServletRequest, HttpServletResponse response) {
String fileName = (String) model.get(KEY_EXCEL_FILE_NAME);
String sheetName = (String) model.get(KEY_EXCEL_SHEET_NAME);
if (StringUtils.isEmpty(sheetName)) {
sheetName = fileName;
}
response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + ".xlsx\"; charset=utf-8");
Font font = workbook.createFont();
font.setBold(true);
font.setFontName("黑体");//默认好像宋体，粗体不太明显？
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setFont(font);
boolean border = model.get(KEY_EXCEL_BORDER) != null;
if (border) {
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
}
CellStyle borderStyle = workbook.createCellStyle();
if (border) {
borderStyle.setBorderBottom(BorderStyle.THIN);
borderStyle.setBorderLeft(BorderStyle.THIN);
borderStyle.setBorderRight(BorderStyle.THIN);
borderStyle.setBorderTop(BorderStyle.THIN);
}
String[] header = (String[]) model.get(KEY_EXCEL_HEADERS);
List<String[]> data = (List<String[]>) model.get(KEY_EXCEL_DATA);
// create excel xls sheet
Sheet sheet = workbook.createSheet(sheetName);
int rowNumber = 0;
String title = (String) model.get(KEY_EXCEL_TITLE);
if (!StringUtils.isEmpty(title)) {
Font fontForTitle = workbook.createFont();
// fontForTitle.setBold(true);
fontForTitle.setFontHeightInPoints((short) 24);
CellStyle cellStyleForTitle = workbook.createCellStyle();
cellStyleForTitle.setAlignment(HorizontalAlignment.LEFT);
cellStyleForTitle.setFont(fontForTitle);
cellStyleForTitle.setAlignment(HorizontalAlignment.CENTER);
Row titleRow = sheet.createRow(rowNumber++);
Cell cell = titleRow.createCell(0);
cell.setCellStyle(cellStyleForTitle);
cell.setCellValue(title);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, header.length - 1));
}
// create header row
Row headerRow = sheet.createRow(rowNumber++);
for (int i = 0; i < header.length; i++) {
Cell cell = headerRow.createCell(i);
cell.setCellStyle(cellStyle);
cell.setCellValue(header[i]);
}
// Create data cells
for (String[] datum : data) {
Row courseRow = sheet.createRow(rowNumber++);
for (int i = 0; i < datum.length; i++) {
Cell cell = courseRow.createCell(i);
cell.setCellStyle(borderStyle);
cell.setCellValue(datum[i]);
}
}
// 如果有title的，用row0做调整，否则row1做调整
autoSizeColumns(workbook, 0, StringUtils.isEmpty(title) ? 0 : 1);
// 每一列都是文本格式
// see https://stackoverflow.com/questions/6510012/apache-poi-for-excel-setting-the-cell-type-to-text-for-an-entire-column
DataFormat fmt = workbook.createDataFormat();
CellStyle textStyle = workbook.createCellStyle();
textStyle.setDataFormat(fmt.getFormat("@"));
for (int i = 0; i < header.length; i++) {
sheet.setDefaultColumnStyle(i, textStyle);
}
}
//用这个实现每一列宽度自动调整
private void autoSizeColumns(Workbook workbook, int targetSheetNumber, int targetRow) {
// int numberOfSheets = workbook.getNumberOfSheets();
// for (int i = 0; i < numberOfSheets; i++) {
// Sheet sheet = workbook.getSheetAt(i);
Sheet sheet = workbook.getSheetAt(targetSheetNumber);
if (sheet.getPhysicalNumberOfRows() > targetRow) {
Row row = sheet.getRow(targetRow);
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
int columnIndex = cell.getColumnIndex();
sheet.autoSizeColumn(columnIndex);
}
}
// }
}
}
最后在Controller里
@Controller
public class BomController {
@RequestMapping("/xxx.xlsx")
public ModelAndView listPipesExcel() {
HashMap<String, Object> model = new HashMap<>();
map.put(ExcelView.KEY_EXCEL_FILE_NAME, "文件名称");
map.put(ExcelView.KEY_EXCEL_SHEET_NAME, "工作表名称");
String[] header = new String[]{"编号", "名称", "备注"};
map.put(ExcelView.KEY_EXCEL_HEADERS, header);
List<String[]> data = new ArrayList();
data2.add(new String[]{"1","名称1","备注1"});
data2.add(new String[]{"2","名称2","备注2"});
map.put(ExcelView.KEY_EXCEL_DATA, data);
ExcelView viewExcel = new ExcelView();
return new ModelAndView(viewExcel, model);
}
}
如何生成普通 Excel 文件，请参考：https://blog.terrynow.com/2021/02/03/generate-excel-file-using-apache-poi/
如何合并单元格，详见 https://blog.terrynow.com/2021/02/11/apache-poi-merge-cells/
文章评论