简单封装POI导出excel

Apache POI是一套根据Office Open XML标准（OOXML）和Microsoft OLE 2复合文档格式（OLE2）来处理各种文件格式的Java API，在应用中经常用来和Microsoft Office系列产品对接，在和excel对接是，需要处理excel繁琐的单元格，下面提供一个在Spring环境下导出excel的简单框架，减少对单元格的繁琐处理，减少重复搬砖，提高开发效率。

1. 依赖

<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>

2. Excel导出配置

1) 导出excel的样式、数据配置文件

导出excel时，主要逻辑无非是将数据填充到excel单元格中，再设置单元格样式，封装POI对excel单元格的操作，只需要定义预先约定的配置文件，封装类再解析配置文件，得到被导出excel每行、每个单元格的数据，操作单元格，最后生成excel文件，返回给用户。

下面是约定的简单配置

<?xml version="1.0" encoding="UTF-8"?><excel name="export.xls"><sheet data-list="data1" desc="" field="examName"><title desc="" field="examName" border="thin" height="40" background-color="41" align="center" v-align="center" wrap="true" bold="bold" font-size="25" font-color="12" font-name="黑体"></title><headers border="thin" height="25" background-color="43" align="center" v-align="center" wrap="true" bold="bold" font-size="20" font-name="宋体" ><column header="名称" field="examName" type="String" border="thin" width="30" height="20" background-color="" align="center" v-align="top" wrap="true" bold="" font-size="18" font-name="宋体"></column><column header="测试1" field="schoolYear" type="String" border="thin" width="20" height="20" background-color="" align="right" v-align="center" wrap="true" bold="" font-size="18" font-name="宋体"></column><column header="测试2" field="term" type="String" border="thin" width="40" height="20" background-color="" align="left" v-align="" wrap="bottom" bold="true" font-size="18" font-name="隶书"></column></headers></sheet><sheet data-list="data1" desc="11111" field="examName"><title desc="but" field="examName" border="thin" height="40" background-color="41" align="center" v-align="center" wrap="true" bold="bold" font-size="25" font-color="12" font-name="黑体"></title><headers border="thin" height="25" background-color="43" align="center" v-align="center" wrap="true" bold="bold" font-size="20" font-name="宋体" ><column header="名称" field="examName" type="String" border="thin" width="30" height="20" background-color="" align="center" v-align="top" wrap="true" bold="" font-size="18" font-name="宋体"></column><column header="测试1" field="schoolYear" type="String" border="thin" width="20" height="20" background-color="" align="right" v-align="center" wrap="true" bold="" font-size="18" font-name="宋体"></column><column header="测试2" field="term" type="String" border="thin" width="40" height="20" background-color="" align="left" v-align="" wrap="bottom" bold="true" font-size="18" font-name="隶书"></column></headers></sheet></excel>

2) 配置解析器

配置解析器ExcelConfigHelper负责解析导出配置，生成程序可读的导出excel配置ExcelConfigVO对象

public class ExcelConfigHelper {public static ExcelConfigVO GetExcelConfig(String configPath) {ExcelConfigVO config = new ExcelConfigVO();SAXReader reader = new SAXReader();try {Document doc = reader.read(new File(configPath));Element root = doc.getRootElement();config.setName(root.attributeValue("name"));List<SheetConfigVO> sheetList = new ArrayList<SheetConfigVO>();config.setSheetList(sheetList);Iterator<Element> si = root.elementIterator();while (si.hasNext()) {Element se = si.next();SheetConfigVO sc = new SheetConfigVO();sheetList.add(sc);sc.setDataList(se.attributeValue("data-list"));sc.setDesc(se.attributeValue("desc"));sc.setField(se.attributeValue("field"));Element te = se.element("title");if (null != te) {sc.setTitleStyle(getStyleConfig(te));}Element he = se.element("headers");if (null != he) {sc.setHeaderStyle(getStyleConfig(he));}List<ColumnConfigVO> columnList = new ArrayList<ColumnConfigVO>();sc.setColumnList(columnList);Iterator<Element> ci = he.elementIterator();while (ci.hasNext()) {Element ce = ci.next();ColumnConfigVO cc = getColumnConfig(ce);columnList.add(cc);}}} catch (DocumentException e) {e.printStackTrace();}return config;}private static StyleConfigVO getStyleConfig(Element e) {StyleConfigVO style = new StyleConfigVO();style.setField(e.attributeValue("field"));style.setDesc(e.attributeValue("desc"));style.setBorder(e.attributeValue("border"));style.setWidth(e.attributeValue("width"));style.setHeight(e.attributeValue("height"));style.setBgcolor(e.attributeValue("background-color"));style.setAlign(e.attributeValue("align"));style.setValign(e.attributeValue("v-align"));style.setWrap(e.attributeValue("wrap"));style.setBold(e.attributeValue("bold"));style.setFontSize(e.attributeValue("font-size"));style.setFontName(e.attributeValue("font-name"));style.setFontColor(e.attributeValue("font-color"));return style;}private static ColumnConfigVO getColumnConfig(Element e) {ColumnConfigVO c = new ColumnConfigVO();c.setField(e.attributeValue("field"));c.setHeader(e.attributeValue("header"));StyleConfigVO style = getStyleConfig(e);c.setStyle(style);return c;}}

Excel配置类

ExcelConfigVO

public class ExcelConfigVO implements Serializable {/** *  */private static final long serialVersionUID = -6669292508345659358L;private String name;private List<SheetConfigVO> sheetList;

SheetConfigVO

public class SheetConfigVO implements Serializable {/** *  */private static final long serialVersionUID = -7809672890306591286L;private String dataList;private String desc;private String field;private StyleConfigVO titleStyle;private StyleConfigVO headerStyle;private List<ColumnConfigVO> columnList;

StyleConfigVO 

public class StyleConfigVO implements Serializable {/** *  */private static final long serialVersionUID = 2747357536186545711L;private String desc;private String field;private String border;private String width;private String height;private String bgcolor;private String align;private String valign;private String wrap;private String bold;private String fontSize;private String fontName;private String fontColor;

ColumnConfigVO 

public class ColumnConfigVO implements Serializable {/** *  */private static final long serialVersionUID = 5303919948113915294L;private String header;private String field;private StyleConfigVO style;

3. 根据配置和数据生成excel

Excel生成器ExcelExportHelper根据配置和数据生成excel

public class ExcelExportHelper {private static final Logger log = LoggerFactory.getLogger(ExcelExportHelper.class);public static HSSFWorkbook export(Map<String, Object> map, ExcelConfigVO config) {try {HSSFWorkbook hw = new HSSFWorkbook();int sheetIndex = 1;for (SheetConfigVO sc : config.getSheetList()) {// dataList数据集合，desc是sheet名称，field是sheet名称在数据集合中的字段，后面二者取一个String dataList = sc.getDataList();String desc = sc.getDesc();String field = sc.getField();List<Object> datas = (List<Object>) map.get(dataList);if (StringUtils.isEmpty(desc) && !CollectionUtils.isEmpty(datas) && !StringUtils.isEmpty(field)) {Object obj = datas.get(0);Field f = obj.getClass().getDeclaredField(field);f.setAccessible(true);desc = (String) f.get(obj);}if (StringUtils.isEmpty(desc)) {desc = "sheet" + sheetIndex;}HSSFSheet sheet = hw.createSheet(desc);if (null != sc.getTitleStyle()) {StyleConfigVO ts = sc.getTitleStyle();String tdesc = ts.getDesc();String tfield = ts.getField();if (StringUtils.isEmpty(tdesc) && !CollectionUtils.isEmpty(datas) && !StringUtils.isEmpty(tfield)) {Object obj = datas.get(0);Field f = obj.getClass().getDeclaredField(tfield);f.setAccessible(true);tdesc = (String) f.get(obj);}if (StringUtils.isEmpty(tdesc)) {tdesc = "title" + sheetIndex;}HSSFCellStyle titleStyle = getCellStyle(hw, ts);int rindex = 0;HSSFRow row = sheet.createRow(rindex++);if (!StringUtils.isEmpty(ts.getHeight())) {row.setHeightInPoints(Short.valueOf(ts.getHeight()));}HSSFCell cell = row.createCell(0);cell.setCellStyle(titleStyle);cell.setCellType(HSSFCell.CELL_TYPE_STRING);cell.setCellValue(desc);if (!CollectionUtils.isEmpty(sc.getColumnList()) && sc.getColumnList().size() > 1) {for (int i = 1; i < sc.getColumnList().size(); i++) {cell = row.createCell(i);cell.setCellStyle(titleStyle);cell.setCellType(HSSFCell.CELL_TYPE_STRING);cell.setCellValue("");}CellRangeAddress merge = new CellRangeAddress(rindex - 1, rindex - 1, 0,sc.getColumnList().size() - 1);sheet.addMergedRegion(merge);}StyleConfigVO hs = sc.getHeaderStyle();HSSFCellStyle headerstyle = getCellStyle(hw, hs);row = sheet.createRow(rindex++);if (!StringUtils.isEmpty(hs.getHeight())) {row.setHeightInPoints(Short.valueOf(hs.getHeight()));}Map<Integer, HSSFCellStyle> styleMap = new HashMap<Integer, HSSFCellStyle>();if (!CollectionUtils.isEmpty(sc.getColumnList())) {for (int i = 0; i < sc.getColumnList().size(); i++) {ColumnConfigVO column = sc.getColumnList().get(i);styleMap.put(i, getCellStyle(hw, column.getStyle()));cell = row.createCell(i);cell.setCellStyle(headerstyle);cell.setCellType(HSSFCell.CELL_TYPE_STRING);cell.setCellValue(column.getHeader());if (null != column.getStyle() && !StringUtils.isEmpty(column.getStyle().getWidth())) {sheet.setColumnWidth(i, Short.valueOf(column.getStyle().getWidth()) * 256);}}}for (Object obj : datas) {row = sheet.createRow(rindex++);for (int i = 0; i < sc.getColumnList().size(); i++) {ColumnConfigVO column = sc.getColumnList().get(i);if (i == 0 && !StringUtils.isEmpty(column.getStyle().getHeight())) {row.setHeightInPoints(Short.valueOf(column.getStyle().getHeight()));}styleMap.put(i, getCellStyle(hw, column.getStyle()));cell = row.createCell(i);cell.setCellStyle(styleMap.get(i));cell.setCellType(HSSFCell.CELL_TYPE_STRING);String val = "";Field f = obj.getClass().getDeclaredField(column.getField());f.setAccessible(true);val = (String) f.get(obj);cell.setCellValue(null == val ? "" : val);}}}sheetIndex++;}return hw;} catch (NoSuchFieldException e) {log.error("", e);} catch (SecurityException e) {log.error("", e);} catch (IllegalArgumentException e) {log.error("", e);} catch (IllegalAccessException e) {log.error("", e);}return null;}private static HSSFCellStyle getCellStyle(HSSFWorkbook hw, StyleConfigVO ts) {HSSFCellStyle titlestyle = hw.createCellStyle();HSSFFont titlefont = hw.createFont();if (!StringUtils.isEmpty(ts.getBold()) && "BOLD".equalsIgnoreCase(ts.getBold().trim())) {titlefont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);}if (!StringUtils.isEmpty(ts.getFontName())) {titlefont.setFontName(ts.getFontName().trim());}if (!StringUtils.isEmpty(ts.getFontSize())) {titlefont.setFontHeightInPoints(Short.valueOf(ts.getFontSize()));}if (!StringUtils.isEmpty(ts.getFontColor())) {titlefont.setColor(Short.valueOf(ts.getFontColor()));}titlestyle.setFont(titlefont);if (!StringUtils.isEmpty(ts.getBorder())) {if ("THIN".equalsIgnoreCase(ts.getBorder().trim())) {titlestyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);titlestyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);titlestyle.setBorderRight(HSSFCellStyle.BORDER_THIN);titlestyle.setBorderTop(HSSFCellStyle.BORDER_THIN);} else if ("NONE".equalsIgnoreCase(ts.getBorder().trim())) {titlestyle.setBorderBottom(HSSFCellStyle.BORDER_NONE);titlestyle.setBorderLeft(HSSFCellStyle.BORDER_NONE);titlestyle.setBorderRight(HSSFCellStyle.BORDER_NONE);titlestyle.setBorderTop(HSSFCellStyle.BORDER_NONE);} else if ("DOUBLE".equalsIgnoreCase(ts.getBorder().trim())) {titlestyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);titlestyle.setBorderLeft(HSSFCellStyle.BORDER_DOUBLE);titlestyle.setBorderRight(HSSFCellStyle.BORDER_DOUBLE);titlestyle.setBorderTop(HSSFCellStyle.BORDER_DOUBLE);} else if ("DOTTED".equalsIgnoreCase(ts.getBorder().trim())) {titlestyle.setBorderBottom(HSSFCellStyle.BORDER_DOTTED);titlestyle.setBorderLeft(HSSFCellStyle.BORDER_DOTTED);titlestyle.setBorderRight(HSSFCellStyle.BORDER_DOTTED);titlestyle.setBorderTop(HSSFCellStyle.BORDER_DOTTED);} else if ("DASHED".equalsIgnoreCase(ts.getBorder().trim())) {titlestyle.setBorderBottom(HSSFCellStyle.BORDER_DASHED);titlestyle.setBorderLeft(HSSFCellStyle.BORDER_DASHED);titlestyle.setBorderRight(HSSFCellStyle.BORDER_DASHED);titlestyle.setBorderTop(HSSFCellStyle.BORDER_DASHED);} else if ("MEDIUM".equalsIgnoreCase(ts.getBorder().trim())) {titlestyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);titlestyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);titlestyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);titlestyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);}}if (!StringUtils.isEmpty(ts.getAlign())) {String align = ts.getAlign().toUpperCase().trim();switch (align) {case "CENTER":titlestyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);case "LEFT":titlestyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);case "RIGHT":titlestyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);case "FILL":titlestyle.setAlignment(HSSFCellStyle.ALIGN_FILL);case "GENERAL":titlestyle.setAlignment(HSSFCellStyle.ALIGN_GENERAL);default:titlestyle.setAlignment(HSSFCellStyle.ALIGN_JUSTIFY);}}if (!StringUtils.isEmpty(ts.getValign())) {String valign = ts.getValign().toUpperCase().trim();switch (valign) {case "CENTER":titlestyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);case "BOTTOM":titlestyle.setAlignment(HSSFCellStyle.VERTICAL_BOTTOM);case "TOP":titlestyle.setAlignment(HSSFCellStyle.VERTICAL_TOP);default:titlestyle.setAlignment(HSSFCellStyle.VERTICAL_JUSTIFY);}}if (!StringUtils.isEmpty(ts.getBgcolor())) {titlestyle.setFillForegroundColor(Short.valueOf(ts.getBgcolor()));titlestyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);}if (!StringUtils.isEmpty(ts.getWrap())) {titlestyle.setWrapText(Boolean.valueOf(ts.getWrap()));}return titlestyle;}public static String getFileName(String fileName, ExcelConfigVO config) {if (StringUtils.isEmpty(fileName)) {fileName = config.getName();}if (StringUtils.isEmpty(fileName)) {fileName = "export.xls";}return fileName;}}

4. 导出逻辑

1) 获取导出任务

导出任务接口，导出特定数据时需要实现接口，给配置解析器提供特定的配置，给excel生成器提供特定的数据。

 

public interface IExcelExportProcessor {/** * 配置文件路径 *  * @return */public String getConfigPath();/** * 导出文件名 *  * @return */public String getFilename();/** * 查询数据 *  * @param queryMap *            查询参数 * @return 导出数据 */public Map<String, Object> getDatas(Map<String, Object> queryMap);}

导出任务实现样例

 

@Component("excelExport.seat")public class ExportSeatProcessor implements IExcelExportProcessor {private static final Logger log = LoggerFactory.getLogger(ExportSeatProcessor.class);@Overridepublic String getConfigPath() {return "";}@Overridepublic String getFilename() {return null;}@Overridepublic Map<String, Object> getDatas(Map<String, Object> queryMap) {return null;}}

2) 导出接口

导出接口根据请求参数得到导出任务Bean和数据请求参数，通过导出任务Bean获取导出配置，数据，导出文件名，再调用配置解析器，excel生成器，生成excel并返回。

RequestMapping(value = "/export", method = RequestMethod.GET)public void test(HttpServletRequest req, HttpServletResponse res) throws IOException {String processorName = req.getParameter("processor");if (StringUtils.isEmpty(processorName)) {res.getWriter().println("parameter error!");return;}IExcelExportProcessor processor = (IExcelExportProcessor) ExcelExportContext.getApplicationContext().getBean(processorName);if (null == processor) {res.getWriter().println("parameter error!");return;}Map<String, Object> queryMap = getParameterMap(req);String fileName = processor.getFilename();String configPath = processor.getConfigPath();Map<String, Object> map = processor.getDatas(queryMap);ExcelConfigVO config = ExcelConfigHelper.GetExcelConfig(configPath);fileName = ExcelExportHelper.getFileName(fileName, config);HSSFWorkbook hw = ExcelExportHelper.export(map, config);res.setContentType("application/octet-stream");res.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes(), "ISO-8859-1"));OutputStream out = null;try {out = res.getOutputStream();hw.write(out);} catch (IOException e) {log.error("export excel error", e);} finally {try {if (null != out) {out.close();}} catch (IOException e) {log.error("export excel error", e);}}}

http GET请求 url?processorName=xxx&name=xxx$yyy=xxx，增加导出数据查询参数，即可导出excel。

（完）