最近学习了点新知识，关于excel的模板，向模板中插入数据然后在导出，就此记录一下。原理和freemarker有点类似，基于POI的基础上进行实现的excel模板导出。实现的功能是前端通过导出按钮调用后台的模板，然后把模板中的数据进行替换。

首先是工具类

AutoExcel

package com.jiuyisoft.utils.excelUtils;import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.*;public class AutoExcel {protected XSSFWorkbook workbook = null;public AutoExcel(String patternPath) throws IOException {loadWorkbook(patternPath);}protected void loadWorkbook(String path) throws IOException {FileInputStream fis = new FileInputStream(path);workbook = new XSSFWorkbook(fis);fis.close();OutputStream output = new FileOutputStream(path);workbook.write(output);output.flush();output.close();}public ExcelSheetGenerator getSheetGenerator(int patternSheetIndex, Object entity) {ExcelSheetGenerator result = null;XSSFSheet newSheet = workbook.cloneSheet(patternSheetIndex);result = new ExcelSheetGenerator(newSheet, entity);return result;}public void removeSheetRange(int startIndex, int count) {for (int i = 0; i < count; i++) {workbook.removeSheetAt(startIndex);}}public void save(String path) throws IOException {FileOutputStream fos = null;fos = new FileOutputStream(path);workbook.write(fos);fos.close();workbook.close();}public XSSFWorkbook getWorkbook() {return workbook;}
}

ExcelSheetGenerator

package com.jiuyisoft.utils.excelUtils;import java.util.List;import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.CellCopyPolicy;
import org.openxmlformats.schemas.drawingml.x2006.main.CTNonVisualDrawingProps;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTTwoCellAnchor;@SuppressWarnings(value = "all")
public class ExcelSheetGenerator {public static class PatternAction {public enum ActionEnum {Replace, ForEach, Unknown}public ActionEnum action;public String field;public PatternAction(ActionEnum action, String field) {this.action = action;this.field = field;}public static boolean isAction(String text) {return (text.indexOf("{{") != -1) && (text.indexOf("}}") != -1);}public static PatternAction generateAction(String text) {if (!isAction(text)) {return null;}ActionEnum actionEnum = ActionEnum.Unknown;String[] items = text.replace("{{", "").replace("}}", "").split("#");if (items.length < 2) {return null;}if (items[0].equals("replace")) {actionEnum = ActionEnum.Replace;} else if (items[0].equals("foreach")) {actionEnum = ActionEnum.ForEach;}
//            else if (items[0].equals("SpecialStyle")) actionEnum = ActionEnum.SpecialStyle;return new PatternAction(actionEnum, items[1].trim());}}protected CellCopyPolicy cellCopyPolicy;protected XSSFSheet patternSheet = null;protected Object entity = null;protected int increaseRows = 0;public ExcelSheetGenerator(XSSFSheet sheet, Object entity) {patternSheet = sheet;this.entity = entity;initConfig();}protected void initConfig() {cellCopyPolicy = (new CellCopyPolicy.Builder()).copyHyperlink(true).cellFormula(true).cellStyle(true).cellValue(true).condenseRows(true).mergedRegions(true).mergeHyperlink(true).rowHeight(true).build();}public static void setCellValue(XSSFCell cell, Object obj) {if (obj instanceof Integer) {cell.setCellValue((Integer) obj);} else if (obj instanceof Double) {cell.setCellValue((Double) obj);} else {cell.setCellValue(String.valueOf(obj));}}public int processPattern(XSSFCell cell, PatternAction action) {try {if (action.action == PatternAction.ActionEnum.Replace) {//cell.setCellValue(entity.getStringByName(action.field));setCellValue(cell, ReflectUtils.getObjectByName(entity, action.field));return 0;
//            } else if(action.action == PatternAction.ActionEnum.SpecialStyle) {
//            	setCellValue(cell, ReflectUtils.getObjectByName(entity, action.field));
//            	XSSFCellStyle xssfCellStyle = new XSSFCellStyle(null);
//            	xssfCellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
//            	cell.setCellStyle(xssfCellStyle);
//                return 0;} else if (action.action == PatternAction.ActionEnum.ForEach) {int startIndex = cell.getRowIndex();XSSFSheet sheet = cell.getSheet();XSSFRow patternRow = sheet.getRow(startIndex + 1);/*int startColIndex = -1, endColIndex = -1;for (int i = cell.getColumnIndex(); i < patternRow.getLastCellNum(); i++) {if (startColIndex == -1) {if (PatternAction.isAction(patternRow.getCell(i).toString())) startColIndex = i;} else {if (PatternAction.isAction(patternRow.getCell(i).toString())) endColIndex = i;else}}String[] fieldPattern = getFieldPattern(patternRow);*/List