前端时间由于对导出数据的需求比较多,excel或者pdf的都有,因此整理了下常用的动能,完成了对apache.poi的简单封装,以此来达到快速开发的目的,本文主要介绍EXCEL的导出实现,下面会提供API文档以及源码,欢迎交流指正(vx:lv630512957)。
1. API文档
2. 代码
2.1 参数配置类
public class ExcelConfig { public static final String XLSX = "xlsx"; public static final String XLS = "xls"; public int startLine = 0; public String dateFormate = "yyyy-MM-dd"; public String suffix = "xlsx"; public int[] columnWidth; public String sheetName = "sheet1"; public int sheetIndex = 0; public ExcelConfig() { } public ExcelConfig(int startLine, String dateFormate, String suffix, int[] columnWidth, String sheetName, int sheetIndex) { this.startLine = startLine; this.dateFormate = dateFormate; this.suffix = suffix; this.columnWidth = columnWidth; this.sheetName = sheetName; this.sheetIndex = sheetIndex; } public int getStartLine() { return this.startLine; } public void setStartLine(int startLine) { this.startLine = startLine; } public String getDateFormate() { return this.dateFormate; } public void setDateFormate(String dateFormate) { this.dateFormate = dateFormate; } public String getSuffix() { return this.suffix; } public void setSuffix(String suffix) { this.suffix = suffix; } public int[] getColumnWidth() { return this.columnWidth; } public void setColumnWidth(int[] columnWidth) { this.columnWidth = columnWidth; } public String getSheetName() { return this.sheetName; } public void setSheetName(String sheetName) { this.sheetName = sheetName; } public int getSheetIndex() { return this.sheetIndex; } public void setSheetIndex(int sheetIndex) { this.sheetIndex = sheetIndex; } }
2.2 无模板导出类
import java.util.List; import java.util.Map; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; 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.xssf.usermodel.XSSFWorkbook; public class ExcelExport { private static final int LEN_LIMIT = 500000; public ExcelExport() { } public static Workbook export(ExcelTitle[] arrFields, List<Object> lstModel, ExcelConfig config) throws Exception { if (arrFields != null && arrFields.length != 0) { if (lstModel == null) { throw new Exception("列表数据为空!"); } else if (arrFields.length * lstModel.size() > 500000) { throw new Exception("excel数据量过大,请分批次重新生成!确保cell数量不大于500000"); } else { if (config == null) { config = new ExcelConfig(); } Workbook workbook = new XSSFWorkbook(); Sheet sheet = workbook.createSheet(config.getSheetName()); CellStyle headStyle = ExcelStyle.headerCellStyle(workbook); CellStyle cellStyle = ExcelStyle.cellStyle(workbook); Row row_field = sheet.createRow(config.getStartLine()); int[] columeWidth = config.getColumnWidth(); int i; int len; if (columeWidth != null && columeWidth.length == arrFields.length) { i = 0; for(len = arrFields.length; i < len; ++i) { sheet.setColumnWidth(i, config.getColumnWidth()[i] * 256 + 184); } } i = 0; for(len = arrFields.length; i < len; ++i) { Cell cell = row_field.createCell(i); cell.setCellStyle(headStyle); cell.setCellValue(arrFields[i].getValue()); } i = 0; for(len = lstModel.size(); i < len; ++i) { Row row = sheet.createRow(i + config.getStartLine() + 1); Object rDto = lstModel.get(i); Map map; if (rDto instanceof Map) { map = (Map)rDto; } else { map = ExcelUtils.objectToMap(rDto); } for(int j = 0; j < arrFields.length; ++j) { Cell cell = row.createCell(j); cell.setCellStyle(cellStyle); cell.setCellValue(ExcelUtils.cellvalueFormat(map.get(arrFields[j].getKey()), config)); } } return workbook; } } else { throw new Exception("表头数据为空!"); } } public static Workbook export(Workbook workbook, ExcelTitle[] arrFields, List<Object> lstModel, ExcelConfig config) throws Exception { if (arrFields != null && arrFields.length != 0) { if (lstModel == null) { throw new Exception("列表数据为空!"); } else if (arrFields.length * lstModel.size() > 500000) { throw new Exception("excel数据量过大,请分批次重新生成!确保cell数量不大于500000"); } else { if (config == null) { config = new ExcelConfig(); } Sheet sheet = workbook.createSheet(config.getSheetName()); CellStyle headStyle = ExcelStyle.headerCellStyle(workbook); CellStyle cellStyle = ExcelStyle.cellStyle(workbook); Row row_field = sheet.createRow(config.getStartLine()); int[] columeWidth = config.getColumnWidth(); int i; int len; if (columeWidth != null && columeWidth.length == arrFields.length) { i = 0; for(len = arrFields.length; i < len; ++i) { sheet.setColumnWidth(i, config.getColumnWidth()[i] * 256 + 184); } } i = 0; for(len = arrFields.length; i < len; ++i) { Cell cell = row_field.createCell(i); cell.setCellStyle(headStyle); cell.setCellValue(arrFields[i].getValue()); } i = 0; for(len = lstModel.size(); i < len; ++i) { Row row = sheet.createRow(i + config.getStartLine() + 1); Object rDto = lstModel.get(i); Map map; if (rDto instanceof Map) { map = (Map)rDto; } else { map = ExcelUtils.objectToMap(rDto); } for(int j = 0; j < arrFields.length; ++j) { Cell cell = row.createCell(j); cell.setCellStyle(cellStyle); cell.setCellValue(ExcelUtils.cellvalueFormat(map.get(arrFields[j].getKey()), config)); } } return workbook; } } else { throw new Exception("表头数据为空!"); } } }
2.3 有模板导出类
import java.io.InputStream; import java.util.List; import java.util.Map; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; public class ExcelExportTemplate { private static final int LEN_LIMIT = 500000; public ExcelExportTemplate() { } public static Workbook exportByTemplate(InputStream in, String[] fields, List<Object> lstModel, ExcelConfig config) throws Exception { if (in != null && fields != null && fields.length != 0) { if (lstModel == null) { throw new Exception("列表数据为空!"); } else if (fields.length * lstModel.size() > 500000) { throw new Exception("excel数据量过大,请分批次重新生成!确保cell数量不大于500000"); } else { if (config == null) { config = new ExcelConfig(); } Workbook workbook = ExcelUtils.getWorkBookTemplate(config.getSuffix(), in); return fillData(workbook, fields, lstModel, config); } } else { throw new Exception("模板文件为空或表头数据为空!"); } } public static Workbook exportByTemplate(Workbook workbook, String[] fields, List<Object> lstModel, ExcelConfig config) throws Exception { if (workbook != null && fields != null && fields.length != 0) { if (lstModel == null) { throw new Exception("列表数据为空!"); } else if (fields.length * lstModel.size() > 500000) { throw new Exception("excel数据量过大,请分批次重新生成!确保cell数量不大于500000"); } else { if (config == null) { config = new ExcelConfig(); } return fillData(workbook, fields, lstModel, config); } } else { throw new Exception("模板文件为空或表头数据为空!"); } } public static Workbook exportByTemplateDynamicTitle(InputStream in, ExcelTitle[] arrFields, List<Object> lstModel, ExcelConfig config) throws Exception { if (in != null && arrFields != null && arrFields.length != 0) { if (lstModel == null) { throw new Exception("列表数据为空!"); } else if (arrFields.length * lstModel.size() > 500000) { throw new Exception("excel数据量过大,请分批次重新生成!确保cell数量不大于500000"); } else { if (config == null) { config = new ExcelConfig(); } Workbook workbook = ExcelUtils.getWorkBookTemplate(config.getSuffix(), in); return fillDataDynamicTitle(workbook, arrFields, lstModel, config); } } else { throw new Exception("模板文件为空或表头数据为空!"); } } public static Workbook exportByTemplateDynamicTitle(Workbook workbook, ExcelTitle[] arrFields, List<Object> lstModel, ExcelConfig config) throws Exception { if (workbook != null && arrFields != null && arrFields.length != 0) { if (lstModel == null) { throw new Exception("列表数据为空!"); } else if (arrFields.length * lstModel.size() > 500000) { throw new Exception("excel数据量过大,请分批次重新生成!确保cell数量不大于500000"); } else { if (config == null) { config = new ExcelConfig(); } return fillDataDynamicTitle(workbook, arrFields, lstModel, config); } } else { throw new Exception("模板文件为空或表头数据为空!"); } } private static Workbook fillData(Workbook workbook, String[] fields, List<Object> lstModel, ExcelConfig config) throws Exception { Sheet sheet = workbook.getSheetAt(config.getSheetIndex()); CellStyle cellStyle = ExcelStyle.cellStyle(workbook); if (lstModel != null && lstModel.size() != 0) { if (sheet == null) { throw new Exception("文件异常!"); } else { int[] columeWidth = config.getColumnWidth(); int i; int len; if (columeWidth != null && columeWidth.length == fields.length) { i = 0; for(len = fields.length; i < len; ++i) { sheet.setColumnWidth(i, config.getColumnWidth()[i] * 256 + 184); } } i = 0; for(len = lstModel.size(); i < len; ++i) { Row row = sheet.createRow(i + config.getStartLine()); Object rDto = lstModel.get(i); Map map; if (rDto instanceof Map) { map = (Map)rDto; } else { map = ExcelUtils.objectToMap(rDto); } for(int j = 0; j < fields.length; ++j) { Cell cell = row.createCell(j); cell.setCellStyle(cellStyle); cell.setCellValue(ExcelUtils.cellvalueFormat(map.get(fields[j]), config)); } } return workbook; } } else { return workbook; } } private static Workbook fillDataDynamicTitle(Workbook workbook, ExcelTitle[] arrFields, List<Object> lstModel, ExcelConfig config) throws Exception { Sheet sheet = workbook.getSheetAt(config.getSheetIndex()); CellStyle headStyle = ExcelStyle.headerCellStyle(workbook); CellStyle cellStyle = ExcelStyle.cellStyle(workbook); if (lstModel != null && lstModel.size() != 0) { if (sheet == null) { throw new Exception("文件异常!"); } else { int[] columeWidth = config.getColumnWidth(); int i; if (columeWidth != null && columeWidth.length == arrFields.length) { int i = 0; for(i = arrFields.length; i < i; ++i) { sheet.setColumnWidth(i, config.getColumnWidth()[i] * 256 + 184); } } Row row_field = sheet.createRow(config.getStartLine()); i = 0; int len; for(len = arrFields.length; i < len; ++i) { Cell cell = row_field.createCell(i); cell.setCellStyle(headStyle); cell.setCellValue(arrFields[i].getValue()); } i = 0; for(len = lstModel.size(); i < len; ++i) { Row row = sheet.createRow(i + config.getStartLine() + 1); Object rDto = lstModel.get(i); Map map; if (rDto instanceof Map) { map = (Map)rDto; } else { map = ExcelUtils.objectToMap(rDto); } for(int j = 0; j < arrFields.length; ++j) { Cell cell = row.createCell(j); cell.setCellStyle(cellStyle); cell.setCellValue(ExcelUtils.cellvalueFormat(map.get(arrFields[j].getKey()), config)); } } return workbook; } } else { return workbook; } } }
2.4 基本样式类
import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.Workbook; public class ExcelStyle { public ExcelStyle() { } public static CellStyle headerCellStyle(Workbook workbook) { CellStyle headerStyle = workbook.createCellStyle(); headerStyle.setBorderBottom((short)1); headerStyle.setBorderLeft((short)1); headerStyle.setBorderRight((short)1); headerStyle.setBorderTop((short)1); headerStyle.setAlignment((short)2); Font headerFont = workbook.createFont(); headerFont.setFontHeightInPoints((short)12); headerFont.setBoldweight((short)700); headerStyle.setFont(headerFont); return headerStyle; } public static CellStyle cellStyle(Workbook workbook) { CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setBorderBottom((short)1); cellStyle.setBorderLeft((short)1); cellStyle.setBorderRight((short)1); cellStyle.setBorderTop((short)1); cellStyle.setAlignment((short)2); cellStyle.setVerticalAlignment((short)1); Font cellFont = workbook.createFont(); cellFont.setBoldweight((short)400); cellStyle.setFont(cellFont); return cellStyle; } }
2.5 表头model类
public class ExcelTitle { private String key; private String value; public ExcelTitle() { } public String getKey() { return this.key; } public void setKey(String key) { this.key = key; } public String getValue() { return this.value; } public void setValue(String value) { this.value = value; } }
2.6 工具类
import java.io.InputStream; import java.text.SimpleDateFormat; import java.util.Date; import java.util.Map; import org.apache.commons.beanutils.BeanMap; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ExcelUtils { public ExcelUtils() { } public static Workbook getWorkBook(String suffix) throws Exception { return getWorkBookTemplate(suffix, (InputStream)null); } public static Workbook getWorkBookTemplate(String suffix, InputStream in) throws Exception { Object workbook = null; try { if ("xls".equalsIgnoreCase(suffix)) { workbook = new HSSFWorkbook(in); } else if ("xlsx".equalsIgnoreCase(suffix)) { workbook = new XSSFWorkbook(in); } return (Workbook)workbook; } catch (Throwable var4) { throw new Exception("创建excel工作簿workBook失败"); } } public static Map<?, ?> objectToMap(Object obj) { return obj == null ? null : new BeanMap(obj); } public static String cellvalueFormat(Object obj, ExcelConfig config) throws Exception { try { if (obj == null) { return ""; } else if (!(obj instanceof Date) && !(obj instanceof java.sql.Date)) { return String.valueOf(obj); } else { SimpleDateFormat sdf = new SimpleDateFormat(config.getDateFormate()); return sdf.format(obj); } } catch (Throwable var3) { throw new Exception("日期格式不符合规范!", var3); } } }