注意:由于公司需要大量导出数据成excel表格，因此在网上找了方法，亲测有效.
声明:该博客参考于https://blog.csdn.net/long530439142/article/details/79002792，谢谢哥们提供方法。
1、在pom.xml中添加poi-ooxml组件
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.9</version> </dependency>
2、excel自定义数据格式
package com.cn.commodity.dto; import java.io.Serializable; import java.util.List; public class ExcelData implements Serializable { private static final long serialVersionUID = 4444017239100620999L; // 表头 private List<String> titles; // 数据 private List<List<Object>> rows; // 页签名称 private String name; public List<String> getTitles() { return titles; } public void setTitles(List<String> titles) { this.titles = titles; } public List<List<Object>> getRows() { return rows; } public void setRows(List<List<Object>> rows) { this.rows = rows; } public String getName() { return name; } public void setName(String name) { this.name = name; } }
3、ExcelUtils 工具类
package com.cn.commodity.utils;
import com.cn.commodity.dto.ExcelData; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.*; import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder; import javax.servlet.http.HttpServletResponse; import java.io.OutputStream; import java.util.List; import java.awt.Color; import java.net.URLEncoder; public class ExcelUtils { public static void exportExcel(HttpServletResponse response, String fileName, ExcelData data) throws Exception { // 告诉浏览器用什么软件可以打开此文件 response.setHeader("content-Type", "application/vnd.ms-excel"); // 下载文件的默认名称 response.setHeader("Content-Disposition", "attachment;filename="+URLEncoder.encode(fileName, "utf-8")); exportExcel(data, response.getOutputStream()); } public static void exportExcel(ExcelData data, OutputStream out) throws Exception { XSSFWorkbook wb = new XSSFWorkbook(); try { String sheetName = data.getName(); if (null == sheetName) { sheetName = "Sheet1"; } XSSFSheet sheet = wb.createSheet(sheetName); writeExcel(wb, sheet, data); wb.write(out); } catch(Exception e){ e.printStackTrace(); }finally{ //此处需要关闭 wb 变量 out.close(); } } private static void writeExcel(XSSFWorkbook wb, Sheet sheet, ExcelData data) { int rowIndex = 0; rowIndex = writeTitlesToExcel(wb, sheet, data.getTitles()); writeRowsToExcel(wb, sheet, data.getRows(), rowIndex); autoSizeColumns(sheet, data.getTitles().size() + 1); } private static int writeTitlesToExcel(XSSFWorkbook wb, Sheet sheet, List<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 (String field : titles) { Cell cell = titleRow.createCell(colIndex); cell.setCellValue(field); cell.setCellStyle(titleStyle); colIndex++; } rowIndex++; return rowIndex; } private static int writeRowsToExcel(XSSFWorkbook wb, Sheet sheet, List<List<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 (List<Object> rowData : rows) { Row dataRow = sheet.createRow(rowIndex); colIndex = 0; for (Object cellData : rowData) { Cell cell = dataRow.createCell(colIndex); if (cellData != null) { cell.setCellValue(cellData.toString()); } 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); } }
4、写一个Controller,在本地生成文件
package com.cn.commodity.controller; import java.io.File; import java.io.FileOutputStream; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; import javax.servlet.http.HttpServletResponse; import com.cn.commodity.dto.ExcelData; import com.cn.commodity.utils.ExcelUtils; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import org.springframework.web.bind.annotation.RestController; @RestController @RequestMapping("/excel") public class ExcelController { @RequestMapping(value = "/export", method = RequestMethod.GET) public void excel(HttpServletResponse response) throws Exception { ExcelData data = new ExcelData(); data.setName("hello"); List<String> titles = new ArrayList(); titles.add("a1"); titles.add("a2"); titles.add("a3"); data.setTitles(titles); List<List<Object>> rows = new ArrayList(); List<Object> row = new ArrayList(); row.add("11111111111"); row.add("22222222222"); row.add("3333333333"); rows.add(row); data.setRows(rows); //生成本地 File f = new File("F:\\test.xlsx"); FileOutputStream out = new FileOutputStream(f); ExcelUtils.exportExcel(data, out); out.close(); /* SimpleDateFormat fdate=new SimpleDateFormat("yyyy-MM-dd-HH-mm-ss"); String fileName=fdate.format(new Date())+".xls"; ExcelUtils.exportExcel(response,fileName,data);*/ } }
本人实测有效，如有问题，欢迎留言！
