导出excel之SXSSFWorkbook多sheet页导出封装
SXSSFWorkbook多sheet页面导出模板配置：
{ "excelName":"导出测试", "sheets":[ { "sheetName":"测试页1", "headers":[ { "key":"itemId", "name":"ID" },{ "key":"itemName", "name":"Name" },{ "key":"itemCode", "name":"Code" } ] },{ "sheetName":"测试页2", "headers":[ { "key":"itemId", "name":"ID" },{ "key":"category", "name":"Category" },{ "key":"uom", "name":"Uom" } ] } ] }
导出代码封装：
import org.apache.commons.collections.MapUtils; import org.apache.commons.collections4.CollectionUtils; import org.apache.commons.io.IOUtils; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.streaming.SXSSFSheet; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.io.OutputStream; import java.io.UnsupportedEncodingException; import java.net.URLEncoder; import java.util.List; import java.util.Map; /** * @Auther: * @Date: 201 * @Description: */ public class ExcelExporter { private static final Logger LOGGER = LoggerFactory.getLogger(ExcelExporter.class); public static final String EXCEL_NAME = "excelName"; public static final String DEFAULT_EXCEL_NAME = "数据导出"; public static final String HEADERS = "headers"; public static final String LINES = "lines"; public static final String HEADER_DATA = "headerData"; public static final String SHEET_DATA = "sheetData"; public static final String LINE_DATA = "lineData"; public static final String SHEETS = "sheets"; public static final String SHEET_NAME = "sheetName"; public static final String KEY = "key"; public static final String NAME = "name"; /** * * @param request * @param response * @param data 导出数据 * @param styleTemplateService 模板服务类 * @param styleTemplateCode 模板编码 * @param sourceFileName 文件名,可以为空 * @throws Exception */ public static void export(HttpServletRequest request, HttpServletResponse response, Map<String, Object> data, IStyleTemplateService styleTemplateService,String styleTemplateCode,String sourceFileName) throws Exception { Map<String, Object> config = styleTemplateService.getExportConfig(styleTemplateCode); String fileName = sourceFileName!=null?sourceFileName:MapUtils.getString(config, ExcelExporter.EXCEL_NAME, ExcelExporter.DEFAULT_EXCEL_NAME); setExcelHeader(response, request, fileName); List<Map<String, Object>> exportSheets = (List<Map<String, Object>>) config.get(SHEETS); // 创建一个工作簿 // 处理07版本，但是适用于大数据量，导出之后数据不会占用内存 SXSSFWorkbook workbook = new SXSSFWorkbook(); String sheetName; String sheetData; SXSSFSheet sheet; List<Map<String, String>> headers; List<Map<String, Object>> lines; if (CollectionUtils.isNotEmpty(exportSheets)) { for (Map<String, Object> exportSheet : exportSheets) { //获取sheet名 sheetName = String.valueOf(exportSheet.get(SHEET_NAME)); // 创建一个工作表sheet sheet = workbook.createSheet(sheetName); //获取列名头 headers = (List<Map<String, String>>) exportSheet.get(HEADERS); //初始化页签头 initHeader(workbook, sheet, headers); // sheetData = String.valueOf(exportSheet.get(SHEET_DATA)); //获取各行数据 lines = (List<Map<String, Object>>) data.get(sheetData); createTableRows(lines, headers, sheet); autoAllSizeColumn(sheet, headers.size()); } } exportExcel(workbook, response.getOutputStream()); } /** * 初始化表头信息 */ private static void initHeader(SXSSFWorkbook sxssfWorkbook, Sheet sheet, List<Map<String, String>> headers) { // 创建第一行 Row row = sheet.createRow(0); Cell cell; if (CollectionUtils.isEmpty(headers)) { return; } for (int i = 0; i < headers.size(); i++) { cell = row.createCell(i); cell.setCellValue(headers.get(i).get(NAME)); setCellStyle(cell, sxssfWorkbook); } } /** * 设置单元格样式 * * @param cell 单元格 */ private static void setCellStyle(Cell cell, SXSSFWorkbook sxssfWorkbook) { // 设置样式 CellStyle cellStyle = sxssfWorkbook.createCellStyle(); //cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 设置字体居中 // 设置字体 Font font = sxssfWorkbook.createFont(); font.setFontName("宋体"); font.setBold(true);// 字体加粗 font.setFontHeightInPoints((short) 13); cellStyle.setFont(font); cell.setCellStyle(cellStyle); } /** * @param datas 数据,每一个map都是一行 * @param header key[i]代表从map中获取keys[i]的值作为第i列的值,如果传的是null默认取表头 */ private static void createTableRows(List<Map<String, Object>> datas, List<Map<String, String>> header, Sheet sheet) { if (CollectionUtils.isEmpty(datas) || CollectionUtils.isEmpty(header)) { return; } for (int i = 0, length_1 = datas.size(); i < length_1; i++) { // 创建行(从第二行开始) Map<String, Object> data = datas.get(i); Row row = sheet.createRow(i + 1); Cell cell; for (int j = 0, length_2 = header.size(); j < length_2; j++) { // 单元格获取map中的key cell = row.createCell(j); cell.setCellValue(MapUtils.getString(data, header.get(j).get(KEY), "")); } } } /** * 根据表头自动调整列宽度 * * @param sheet * @param columnSzie 列数 */ private static void autoAllSizeColumn(SXSSFSheet sheet, int columnSzie) { sheet.trackAllColumnsForAutoSizing(); for (int i = 0; i < columnSzie; i++) { sheet.autoSizeColumn(i); } } /** * 设置http请求报文为下载文件 * * @param response * @param request * @param fileName * @throws UnsupportedEncodingException **/ private static void setExcelHeader(HttpServletResponse response, HttpServletRequest request, String fileName) throws UnsupportedEncodingException { String name = fileName + ".xlsx"; String userAgent = request.getHeader("User-Agent"); if (userAgent.contains("Firefox")) { name = new String(name.getBytes("UTF-8"), "ISO8859-1"); } else { name = URLEncoder.encode(name, "UTF-8"); } response.addHeader("Content-Disposition", "attachment; filename=\"" + name + "\""); response.setContentType("application/octet-stream" + ";charset=" + "UTF-8"); response.setHeader("Accept-Ranges", "bytes"); } /** * 将数据写出到excel中 * * @param outputStream */ private static void exportExcel(SXSSFWorkbook workbook, OutputStream outputStream) { try { workbook.write(outputStream); } catch (IOException e) { LOGGER.error(" exportExcel error", e); } finally { IOUtils.closeQuietly(outputStream); } } }
导出调用
导出结果：
