话不多说直接上代码,如果适合你,麻烦给个赞!!! package com.dajie.notice.util; import java.io.IOException; import java.io.OutputStream; import java.util.ArrayList; import java.util.Arrays; import java.util.List; import javax.servlet.http.HttpServletResponse; import com.dajie.notice.controller.me.MeExportController; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.RichTextString; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFRichTextString; import org.slf4j.Logger; import org.slf4j.LoggerFactory; / * 多功能excel导出 * * @author kangzhuang * @ClassName: PoiExportExcel.java * @Description: */ public class PoiExportComplexExcel { / * 日志 */ private static final Logger logger = LoggerFactory.getLogger(MeExportController.class); //导出表名 private String fileName; //显示的导出表的标题 private String title; //导出表的列名 private String[] rowName; //定义导出数据集合 private List<Object[]> dataList = new ArrayList<Object[]>(); HttpServletResponse response; //构造方法，传入要导出的数据 public PoiExportComplexExcel(String fileName, String title, String[] rowName, List<Object[]> dataList, HttpServletResponse response) { this.fileName = fileName; this.title = title; if (rowName == null) { this.rowName = new String[0]; } else { this.rowName = Arrays.copyOf(rowName, rowName.length); } this.dataList = dataList; this.response = response; } / * 创建excel * * @param workbook */ private void createExcel(SXSSFWorkbook workbook) throws Exception { logger.info("PoiExportComplexExcel createExcel start fileName:{}",fileName); Sheet sheet = workbook.createSheet(title); // 创建工作表 CellStyle columnTopStyle = this.getColumnTopStyle(workbook);//获取列头样式对象 CellStyle style = this.getStyle(workbook); //单元格样式对象 int columnNum = rowName.length; // 定义所需列数 Row rowRowName = sheet.createRow(0); // 在索引0的位置创建行(最顶端的行开始的第0行) // 将列头设置到sheet的单元格中 for (int n = 0; n < columnNum; n++) { Cell cellRowName = rowRowName.createCell(n); //创建列头对应个数的单元格 cellRowName.setCellType(Cell.CELL_TYPE_STRING); //设置列头单元格的数据类型 RichTextString text = new XSSFRichTextString(rowName[n]); cellRowName.setCellValue(text); //设置列头单元格的值 cellRowName.setCellStyle(columnTopStyle); //设置列头单元格样式 } //将查询出的数据设置到sheet对应的单元格中 for (int i = 0; i < dataList.size(); i++) { Object[] obj = dataList.get(i); //遍历每个对象 Row row = sheet.createRow(i + 1); //创建所需的行数 for (int j = 0; j < obj.length; j++) { Cell cell_col = null; //设置单元格的数据类型 cell_col = row.createCell(j, Cell.CELL_TYPE_STRING); //带序号j+1 //防止.toString()时此处报空指针异常 if (obj[j] == null) { cell_col.setCellValue("");//设置单元格的值 } else { cell_col.setCellValue(obj[j].toString());//设置单元格的值 } cell_col.setCellStyle(style); //设置单元格样式 } } //让列宽随着导出的列长自动适应 for (int colNum = 0; colNum < columnNum; colNum++) { int columnWidth = sheet.getColumnWidth(colNum) / 256; for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) { Row currentRow; //当前行未被使用过 if (sheet.getRow(rowNum) == null) { currentRow = sheet.createRow(rowNum); } else { currentRow = sheet.getRow(rowNum); } if (currentRow.getCell(colNum) != null) { Cell currentCell = currentRow.getCell(colNum); if (currentCell.getRichStringCellValue() != null) { columnWidth = currentCell.getStringCellValue().getBytes().length; // 设置每列的宽度 } } } if (colNum == 0) { sheet.setColumnWidth(colNum, (columnWidth - 2) * 256); } else { sheet.setColumnWidth(colNum, (columnWidth + 4) * 256); } } } / * 导出单个xlsx * * @throws Exception */ public void export() { logger.info("PoiExportComplexExcel export start"); OutputStream out = null; // SXSSFWorkbook为POI专门处理大数据量的，并且可以解决使用XSSFWorkbook内存溢出问题。 // 创建工作簿对象（需要初始化导出数据量） SXSSFWorkbook workbook = new SXSSFWorkbook(dataList.size() + 1); try { this.createExcel(workbook); if (workbook != null) { fileName = new String(fileName.getBytes("utf-8"), "ISO8859-1") + ".xlsx"; String headStr = "attachment; filename=\"" + fileName + "\""; response.setContentType("APPLICATION/OCTET-STREAM"); response.setHeader("Content-Disposition", headStr); out = response.getOutputStream(); workbook.write(out); out.flush(); } } catch (IOException e) { logger.error("export IOException:", e); } catch (Exception e) { logger.error("export Exception:", e); } finally { try { if (out != null) { out.close(); } if (workbook != null) { workbook.close(); } } catch (IOException e) { logger.error("export IOException:", e); } } } /* * 列头单元格样式 */ public CellStyle getColumnTopStyle(SXSSFWorkbook workbook) { // 设置字体 Font font = workbook.createFont(); //字体加粗 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //设置字体名字 font.setFontName("Courier New"); //设置样式; CellStyle style = workbook.createCellStyle(); //设置底边框; style.setBorderBottom(CellStyle.BORDER_THIN); //设置底边框颜色; style.setBottomBorderColor(HSSFColor.BLACK.index); //设置左边框; style.setBorderLeft(CellStyle.BORDER_THIN); //设置左边框颜色; style.setLeftBorderColor(HSSFColor.BLACK.index); //设置右边框; style.setBorderRight(CellStyle.BORDER_THIN); //设置右边框颜色; style.setRightBorderColor(HSSFColor.BLACK.index); //设置顶边框; style.setBorderTop(CellStyle.BORDER_THIN); //设置顶边框颜色; style.setTopBorderColor(HSSFColor.BLACK.index); //在样式用应用设置的字体; style.setFont(font); //设置自动换行; style.setWrapText(false); //设置水平对齐的样式为居中对齐; style.setAlignment(CellStyle.ALIGN_CENTER); //设置垂直对齐的样式为居中对齐; style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); return style; } /* * 列数据信息单元格样式 */ public CellStyle getStyle(SXSSFWorkbook workbook) { // 设置字体 Font font = workbook.createFont(); //设置字体名字 font.setFontName("Courier New"); //设置样式; CellStyle style = workbook.createCellStyle(); //设置底边框; style.setBorderBottom(CellStyle.BORDER_THIN); //设置底边框颜色; style.setBottomBorderColor(HSSFColor.BLACK.index); //设置左边框; style.setBorderLeft(CellStyle.BORDER_THIN); //设置左边框颜色; style.setLeftBorderColor(HSSFColor.BLACK.index); //设置右边框; style.setBorderRight(CellStyle.BORDER_THIN); //设置右边框颜色; style.setRightBorderColor(HSSFColor.BLACK.index); //设置顶边框; style.setBorderTop(CellStyle.BORDER_THIN); //设置顶边框颜色; style.setTopBorderColor(HSSFColor.BLACK.index); //在样式用应用设置的字体; style.setFont(font); //设置自动换行; style.setWrapText(false); //设置水平对齐的样式为居中对齐; style.setAlignment(CellStyle.ALIGN_CENTER); //设置垂直对齐的样式为居中对齐; style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); return style; } } 

引入的依赖:

讯享网

注意:版本号一定要一致,不然你会遇到神坑!

讯享网<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.14</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.14</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>3.14</version> </dependency>