在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/
文章评论