1.调用方式
ExcelUtils.exportExcel(resultOrderList, fieldLinkedMap, response.getOutputStream(), fileName, suffix);
2.exportExcel(List<Map<String, Object>> resultOrderList, Map<String, Object> headOrderMap, OutputStream os, String sheetTitle, String version)方法参数介绍
resultOrderList：从数据库获取到的Map类型的数据集合。集合示例：[{"file_name":"hadoop规划表"，"dept_name":"数据部"},{"file_name":"财年数据运营统计表"，"dept_name":"运营部"}]
headOrderMap：标题有序集合，可以使用LinkedHashMap。集合示例：{"file_name":"文件名称"，"dept_name":"所属部门"}
sheetTitle：sheet页的名称。
version：excel的版本，可以是xls或者xlsx。
3.工具类代码（放到java文件中可以直接使用）
package com.zkdj.huishu.crm.utils; import java.io.IOException; import java.io.OutputStream; import java.util.Iterator; import java.util.List; import java.util.Map; import java.util.Map.Entry; import org.apache.commons.lang.StringUtils; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFRichTextString; 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.BorderStyle; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.FillPatternType; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.VerticalAlignment; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.util.IOUtils; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFFont; import org.apache.poi.xssf.usermodel.XSSFRichTextString; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; /** * Excel工具类 * Author zhangjifu * Date 2020年11月12日-下午6:00:50 */ public class ExcelUtils { /** * 导出Excel表格 * @param resultOrderList 结果集列表 * @param headOrderMap 头部有序集合 * @param sheetTitle sheet页标题 * @param os 输出流 * @param version Excel版本：xls或者xlsx */ public static void exportExcel(List<Map<String, Object>> resultOrderList, Map<String, Object> headOrderMap, OutputStream os, String sheetTitle, String version) { if (StringUtils.isBlank(version) || "xls".equals(version.trim())) { exportHSExcel(resultOrderList, headOrderMap, sheetTitle, os); } else { exportXSExcel(resultOrderList, headOrderMap, sheetTitle, os); } } /** * 2003版Excel表格导出，后缀为xls * @param resultOrderList 结果集列表 * @param headOrderMap 头部有序集合 * @param os 输出流 */ private static void exportHSExcel(List<Map<String, Object>> resultOrderList, Map<String, Object> headOrderMap, String sheetTitle, OutputStream os) { // 声明一个工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); // 生成一个表格 HSSFSheet sheet = workbook.createSheet(sheetTitle); // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth(20); sheet.setDefaultRowHeightInPoints(24); // 指定宽度 // setColumnWidth(sheet, excelComplex.getColumnWidths()); // 生成头部样式 HSSFCellStyle headStyle = workbook.createCellStyle(); // 设置头部样式 setHeadCellStyle(headStyle); // 生成头部字体 HSSFFont headFont = workbook.createFont(); // 设置头部字体 headFont.setFontName("宋体"); headFont.setColor(IndexedColors.WHITE.getIndex()); headFont.setFontHeightInPoints((short) 12); // 把头部字体应用到头部样式 headStyle.setFont(headFont); // 生成数据行样式 HSSFCellStyle resultStyle = workbook.createCellStyle(); setResultCellStyle(resultStyle); // 生成数据行字体 HSSFFont resultFont = workbook.createFont(); resultFont.setBold(true); // 把数据行字体应用到数据行样式 resultStyle.setFont(resultFont); // 遍历头部集合，产生标题行 int headRowIndex = 0; int headCellIndex = 0; HSSFRow row = sheet.createRow(headRowIndex); HSSFCell cellHeader; Iterator<Entry<String, Object>> headIterator = headOrderMap.entrySet().iterator(); while(headIterator.hasNext()){ Entry<String, Object> nextEntry = headIterator.next(); cellHeader = row.createCell(headCellIndex); cellHeader.setCellStyle(headStyle); cellHeader.setCellValue(new HSSFRichTextString(StringUtils.isBlank((String) nextEntry.getValue())?"":String.valueOf(nextEntry.getValue()))); headCellIndex++; } // 遍历集合数据，产生数据行 int resultRowIndex = headRowIndex+1; int resultCellIndex = 0; HSSFCell cell; for (Map<String, Object> resultMap : resultOrderList) { row = sheet.createRow(resultRowIndex); headIterator = headOrderMap.entrySet().iterator(); while(headIterator.hasNext()){ String cellValue = String.valueOf(resultMap.get(headIterator.next().getKey())); cell = row.createCell(resultCellIndex); cell.setCellStyle(resultStyle); if(StringUtils.isNotBlank(cellValue) && "null"!=cellValue){ cell.setCellValue(new HSSFRichTextString(cellValue)); } resultCellIndex++; } resultCellIndex=0; resultRowIndex++; } // 将Excel发送到客户端 workBookWrite(os, workbook); } /** * 2007版Excel导出，后缀为xlsx * @param resultOrderList 结果集列表 * @param headOrderMap 头部有序集合 * @param sheetTitle sheet页标题 * @param os 输出流 */ private static void exportXSExcel(List<Map<String, Object>> resultOrderList, Map<String, Object> headOrderMap, String sheetTitle, OutputStream os) { // 声明一个工作薄 XSSFWorkbook workbook = new XSSFWorkbook(); // 生成一个表格 XSSFSheet sheet = workbook.createSheet(sheetTitle); // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth(20); sheet.setDefaultRowHeightInPoints(24); // 指定宽度 // setColumnWidth(sheet, excelComplex.getColumnWidths()); // 生成头部样式 XSSFCellStyle headStyle = workbook.createCellStyle(); // 设置头部样式 setHeadCellStyle(headStyle); // 生成头部字体 XSSFFont headFont = workbook.createFont(); headFont.setColor(IndexedColors.WHITE.getIndex()); headFont.setFontHeightInPoints((short) 12); headFont.setBold(true); // 把头部字体应用到头部样式 headStyle.setFont(headFont); // 生成数据行样式 XSSFCellStyle resultStyle = workbook.createCellStyle(); // 设置数据行央视 setResultCellStyle(resultStyle); // 生成数据行字体 XSSFFont resultFont = workbook.createFont(); // 设置数据行字体 resultFont.setBold(true); // 把数据行字体应用数据行样式 resultStyle.setFont(resultFont); // 遍历头部集合，产生标题行 int headRowIndex = 0; int headCellIndex = 0; XSSFRow row = sheet.createRow(headRowIndex); XSSFCell cellHeader; Iterator<Entry<String, Object>> headIterator = headOrderMap.entrySet().iterator(); while(headIterator.hasNext()){ Entry<String, Object> nextEntry = headIterator.next(); cellHeader = row.createCell(headCellIndex); cellHeader.setCellStyle(headStyle); cellHeader.setCellValue(new XSSFRichTextString(StringUtils.isBlank((String) nextEntry.getValue())?"":String.valueOf(nextEntry.getValue()))); headCellIndex++; } // 遍历集合数据，产生数据行 int resultRowIndex = headRowIndex+1; int resultCellIndex = 0; XSSFCell cell; for (Map<String, Object> resultMap : resultOrderList) { row = sheet.createRow(resultRowIndex); headIterator = headOrderMap.entrySet().iterator(); while(headIterator.hasNext()){ String cellValue = String.valueOf(resultMap.get(headIterator.next().getKey())); cell = row.createCell(resultCellIndex); cell.setCellStyle(resultStyle); if(StringUtils.isNotBlank(cellValue) && "null"!=cellValue){ cell.setCellValue(new XSSFRichTextString(cellValue)); } resultCellIndex++; } resultCellIndex=0; resultRowIndex++; } // 将Excel发送到客户端 workBookWrite(os, workbook); } private static void setColumnWidth(Sheet sheet, Integer[] columnWidths) { for (int i = 0; i < columnWidths.length; i++) { sheet.setColumnWidth(i, 256 * columnWidths[i] + 184); } } /** * 设置头部样式 * @param headStyle 头部样式 */ private static void setHeadCellStyle(CellStyle headStyle) { headStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex()); headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); headStyle.setBorderBottom(BorderStyle.THIN); headStyle.setBorderLeft(BorderStyle.THIN); headStyle.setBorderRight(BorderStyle.THIN); headStyle.setBorderTop(BorderStyle.THIN); headStyle.setAlignment(HorizontalAlignment.CENTER); headStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直 } /** * 设置数据行样式 * @param resultStyle 数据行样式 */ private static void setResultCellStyle(CellStyle resultStyle) { resultStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); resultStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); resultStyle.setBorderBottom(BorderStyle.THIN); resultStyle.setBorderLeft(BorderStyle.THIN); resultStyle.setBorderRight(BorderStyle.THIN); resultStyle.setBorderTop(BorderStyle.THIN); resultStyle.setAlignment(HorizontalAlignment.CENTER); resultStyle.setVerticalAlignment(VerticalAlignment.CENTER); } /** * 将Excel输出到客户端 * @param os 输出流 * @param workbook 工作簿 */ private static void workBookWrite(OutputStream os, Workbook workbook) { try { workbook.write(os); } catch (IOException e) { e.printStackTrace(); } finally { IOUtils.closeQuietly(workbook); IOUtils.closeQuietly(os); } } }
原文：https://www.cnblogs.com/Imbadguy/p/13965370.html