如何使用Java 实现excel模板导出---多sheet导出？

实现多个sheet的excel导出功能

效果展示：

maven依赖

<dependency><groupId>org.apache.poigroupId><artifactId>poiartifactId><version>3.17version>dependency><dependency><groupId>org.apache.poigroupId><artifactId>poi-ooxmlartifactId><version>3.17version>dependency>

相关工具类
**此处省略异常处理类

ExcelReportUtil 类

import org.apache.commons.logging.Log;import org.apache.commons.logging.LogFactory;import org.apache.poi.ss.usermodel.*;import org.apache.poi.ss.util.CellRangeAddress;import java.math.BigDecimal;import java.util.*;public class ExcelReportUtil {private static Log logger = LogFactory.getLog(ExcelReportUtil.class);public static void cloneRow(Sheet sheet, Row srcRow, Row destRow) {if (sheet == null || srcRow == null || destRow == null)return;Set mergedRegions = new HashSet();destRow.setHeight(srcRow.getHeight());if (srcRow.getFirstCellNum() >= 0 && srcRow.getLastCellNum() >= 0) {            for (int i = srcRow.getFirstCellNum(), j = srcRow.getLastCellNum(); i <= j; i++) {Cell srcCell = srcRow.getCell(i);Cell destCell = destRow.getCell(i);                if (srcCell != null) {                logger.debug("cell is found at col[" + i +                 "], will be cloned to the destRow");                    if (destCell == null) {                        destCell = destRow.createCell(i);                    }                    cloneCell(srcCell, destCell);                    CellRangeAddress mergedRegion = getMergedRegion(sheet, srcRow.getRowNum(),                    srcCell.getColumnIndex());                    if (mergedRegion != null) {                    CellRangeAddress newMergedRegion = new CellRangeAddress(                    destRow.getRowNum(),                     destRow.getRowNum() + mergedRegion.getLastRow() - mergedRegion.getFirstRow(),                     mergedRegion.getFirstColumn(),                     mergedRegion.getLastColumn());                        if (isNewMergedRegion(newMergedRegion, mergedRegions)) {                        logger.debug("CellRangeAddress is found at col[" + i +                         "], will be cloned to the destRow");mergedRegions.add(newMergedRegion);sheet.addMergedRegion(newMergedRegion);                        }                    }                }            }}}public static CellRangeAddress getMergedRegion(Sheet sheet, int rowNum, int colNum) {        for (int i = 0, c = sheet.getNumMergedRegions(); i < c; i++) {            CellRangeAddress merged = sheet.getMergedRegion(i);            if (isRangeContainsCell(merged, rowNum, colNum)) {                return merged;            }        }        return null;    }    public static boolean isRangeContainsCell(CellRangeAddress range, int row, int col) {        if ((range.getFirstRow() <= row) && (range.getLastRow() >= row)                && (range.getFirstColumn() <= col)                && (range.getLastColumn() >= col)) {            return true;        }        return false;    }            private static boolean isNewMergedRegion(CellRangeAddress region,            Collection mergedRegions) {        for (Iterator iterator = mergedRegions.iterator(); iterator.hasNext();) {            CellRangeAddress cellRangeAddress = (CellRangeAddress) iterator.next();            if (areRegionsEqual(cellRangeAddress, region)) {                return false;            }        }        return true;    }            public static boolean areRegionsEqual(CellRangeAddress region1,            CellRangeAddress region2) {        if ((region1 == null && region2 != null)                || (region1 != null && region2 == null)) {            return false;        }        if (region1 == null) {            return true;        }        return (region1.getFirstColumn() == region2.getFirstColumn()                && region1.getLastColumn() == region2.getLastColumn()                && region1.getFirstRow() == region2.getFirstRow()                 && region2.getLastRow() == region2.getLastRow());    }            public static void cloneCell(Cell srcCell, Cell destCell) {if (srcCell == null || destCell == null)return;destCell.setCellStyle(srcCell.getCellStyle());switch (srcCell.getCellTypeEnum()) {case NUMERIC :destCell.setCellValue(srcCell.getNumericCellValue());break;case STRING :destCell.setCellValue(srcCell.getRichStrinGCellValue());break;case FORMULA :destCell.setCellFormula(srcCell.getCellFormula());break;case ERROR:destCell.setCellErrorValue(srcCell.getErrorCellValue());break;case BOOLEAN:destCell.setCellValue(srcCell.getBooleanCellValue());break;default :destCell.setCellType(CellType.BLANK);break;}}            public static void setCellValue(Cell cell, Object value) {    if (cell == null)    return;    if (value == null) {    cell.setCellType(CellType.BLANK);    } else if (value instanceof Calendar) {    cell.setCellValue((Calendar) value);    //Date格式化日期输出} else if (value instanceof Date) {//cell.setCellValue((Date) value);//cell.setCellValue(BaseUtils.Date2String((Date)value, CommonConstants.FORMAT_Date));cell.setCellValue(BaseUtils.Date2String((Date)value, "yyyy-MM-dd HH:mm:ss"));} else if (value instanceof Number) {setCellValue(cell, (Number) value);} else if (value instanceof String) {cell.setCellValue((String) value);} else {logger.warn("value type [" + value.getClass().getName() + "] is not directly supported, will be processed as String");cell.setCellValue((String) value.toString());}    }    private static void setCellValue(Cell cell, Number value) {double doubleContent = 0.0;if (value instanceof Byte) {doubleContent = (Byte) value;} else if (value instanceof Double) {doubleContent = (Double) value;} else if (value instanceof Float) {doubleContent = (Float) value;//BigDecimal转换为Double}else if (value instanceof BigDecimal) {doubleContent = TypeCaseHelper.convert2Double(value);} else if (value instanceof Integer) {float tmp = (Integer) value;doubleContent = tmp;} else if (value instanceof Long) {float tmp = (Long) value;doubleContent = tmp;} else if (value instanceof Short) {short tmp = (Short) value;doubleContent = tmp;} else {throw new XLSReportException("value type [" + value.getClass().getName() + "] can not be processed as double");}cell.setCellValue(doubleContent);}public static String getCellStringValue(Cell cell) {String cellStringValue = null;switch (cell.getCellTypeEnum()) {case NUMERIC:cellStringValue = cell.getNumericCellValue() + "";break;case STRING:cellStringValue = cell.getStringCellValue();break;case BOOLEAN:cellStringValue = cell.getBooleanCellValue() + "";break;default :logger.warn("can not get the string value of a cell whose type is " + cell.getCellTypeEnum());cellStringValue = "";break;}return cellStringValue;}public static void removeComment(Sheet sheet, Comment comment) {if (sheet != null && comment != null) {sheet.getRow(comment.getRow()).getCell(comment.getColumn()).removeCellComment();}}}

excel 接口

import org.apache.poi.ss.usermodel.Sheet;import java.util.Map;public interface ExcelProcessor{void init();boolean isInited();void process(Sheet sheet, Map dataSource);}

实现类

import com.alibaba.fastJSON.jsON;import com.alibaba.fastjson.JSONException;import org.apache.commons.logging.Log;import org.apache.commons.logging.LogFactory;import org.apache.ibatis.ognl.Ognl;import org.apache.ibatis.ognl.OgnlException;import org.apache.poi.ss.usermodel.*;import java.util.Arrays;import java.util.Collections;import java.util.HashMap;import java.util.Map;public class ExcelIteratorNoLastRowProcessor implements ExcelProcessor {//填充类型public static String TYPE_ID = "I";private static Log logger = LogFactory.getLog(ExcelIteratorNoLastRowProcessor.class);private boolean inited;//批注private final Comment comment;//填充数据的名称private String expression;//从第几行开始填充private int firstIndex;//填充的数据private Map<Integer, String> dataMapping;public ExcelIteratorNoLastRowProcessor(Comment comment) {this.comment = comment;}public String getExpression() {return expression;}public void setExpression(String expression) {this.expression = expression;}public int getFirstIndex() {return firstIndex;}public void setFirstIndex(int firstIndex) {this.firstIndex = firstIndex;}public Map<Integer, String> getDataMapping() {return dataMapping;}public void setDataMapping(Map<Integer, String> dataMapping) {this.dataMapping = dataMapping;}@Overridepublic void init() {try {Map<String, Object> claSSMap = new HashMap<String, Object>();classMap.put("dataMapping", Map.class);Map<String, Object> cfg = JSON.parseObject(this.comment.getString().toString(),Map.class);Object expresionCfg = cfg.get("expression");Object firstIndexCfg = cfg.get("firstIndex");Object dataMappingCfg = cfg.get("dataMapping");if (expresionCfg == null || !(expresionCfg instanceof String)) {throw new XLSReportCfgException("expresion must be configured and its type must be String");}this.expression = (String) expresionCfg;if (firstIndexCfg == null || !(firstIndexCfg instanceof Integer)) {throw new XLSReportCfgException("firstIndex must be configured and its type must be Integer");}this.firstIndex = (Integer) firstIndexCfg;if (dataMappingCfg == null || !(dataMappingCfg instanceof Map)) {throw new XLSReportCfgException("dataMapping must be configured and its type must be Map");}this.dataMapping = (Map<Integer, String>) dataMappingCfg;this.inited = true;} catch (JSONException e) {throw new XLSReportCfgException("the comment configuration at [" +comment.getRow() + "," + comment.getColumn() + "] " + comment.getString().toString() +" is error", e);}}@Overridepublic boolean isInited() {return this.inited;}@Overridepublic void process(Sheet sheet, Map dataSource) {try {if (!isInited())throw new XLSReportException("the CommentProcessor has not inited yet");//从dataSource中找到填充目标名称Object content = Ognl.getValue(this.expression, dataSource);if (content == null) {content = Collections.EMPTY_LIST;}if (!(content instanceof Iterable)) {content = Arrays.asList(content);}int index = 0;boolean isAddRow = false;for (Object element : (Iterable) content) {//clone rowlogger.debug("clone the row at index[" + (this.firstIndex + index + 1) + "] to the new row at index[" + (this.firstIndex + index) + "]");ExcelReportUtil.cloneRow(sheet, sheet.getRow(this.firstIndex + index + 1), sheet.createRow(this.firstIndex + index));//获取填充行Row aimedRow = sheet.getRow(this.firstIndex + index);//fill datafor (Integer key : this.dataMapping.keySet()) {int cellIndex = key;//获取第一行的数据Map rowDS = new ExcelDataSource(element).getData();rowDS.put("index", index);Cell aimedCell = aimedRow.getCell(cellIndex);if (aimedCell == null)aimedCell = aimedRow.createCell(cellIndex);//找到列对应的数据Object value = Ognl.getValue(this.dataMapping.get(key), rowDS);//样式if(Boolean.parseBoolean(rowDS.get("isBolded") == null ? "":rowDS.get("isBolded").toString())){Workbook workbook = sheet.getWorkbook();CellStyle cellStyle = workbook.createCellStyle();Font font = workbook.createFont();//font.setBoldweight(Font.BOLDWEIGHT_BOLD);font.setBold(true);cellStyle.setFont(font);aimedCell.setCellStyle(cellStyle);}logger.debug("set the value of cell[" + (this.firstIndex + index) + ", " + cellIndex + "] to " + value);//给列填值ExcelReportUtil.setCellValue(aimedCell, value);}index++;}//remove commentExcelReportUtil.removeComment(sheet, this.comment);} catch (OgnlException e) {throw new XLSReportException("extracting data error while using OGNL expression[" +this.expression + "] with root object : " + dataSource);}}}

excel填充数据处理类

import org.apache.commons.logging.Log;import org.apache.commons.logging.LogFactory;import java.beans.BeanInfo;import java.beans.Introspector;import java.beans.PropertyDescriptor;import java.util.Collections;import java.util.HashMap;import java.util.Map;public class ExcelDataSource  {private static Log logger = LogFactory.getLog(ExcelDataSource.class);private Object dataSource;public ExcelDataSource(Object dataSource) {this.dataSource = dataSource;}public Map getData() {Map ds = null;if (this.dataSource == null) {ds = Collections.emptyMap();} else if (this.dataSource instanceof Map) {ds = (Map) this.dataSource;} else {logger.debug("the type of dataSource is [" + dataSource.getClass() + "], will be transformed to Map");ds = transformPropertiesToMap(this.dataSource);}return ds;}private Map transformPropertiesToMap(Object bean) {Map properties = new HashMap();BeanInfo beanInfo;try {beanInfo = Introspector.getBeanInfo(bean.getClass());PropertyDescriptor[] pds = beanInfo.getPropertyDescriptors();for (PropertyDescriptor pd : pds) {if (!"class".equals(pd.getName())) {properties.put(pd.getName(), pd.getReadMethod().invoke(bean));}}} catch (Exception e) {throw new XLSReportException(e);}return properties;}}

excel填充处理类

import com.alibaba.fastjson.JSON;import org.apache.commons.logging.Log;import org.apache.commons.logging.LogFactory;import org.apache.poi.ss.usermodel.*;import java.io.InputStream;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;public class ExcelReportFiller {private static final Log logger = LogFactory.getLog(ExcelReportFiller.class);public void fill(Workbook template, Map dataSource) {int sheetCount = template.getNumberOfSheets();for (int i = 0; i < sheetCount; i++) {logger.debug("scan the sheet at index[" + i + "]");fillSheet(template.getSheetAt(i), dataSource);}}public void fillSheetName(String sheetName, Workbook template,Map dataSource) {logger.debug("scan the sheet at [" + sheetName + "]");fillSheet(template.getSheet(sheetName), dataSource);}private void fillSheet(Sheet sheet, Map dataSource) {int rowCount = sheet.getLastRowNum();for (int i = 0; i <= rowCount; i++) {Row row = sheet.getRow(i);if (row != null) {int cellCount = row.getLastCellNum();for (int j = 0; j <= cellCount; j++) {Cell cell = row.getCell(j);if (cell != null ) {Comment comment = cell.getCellComment();if (comment != null) {logger.debug("comment is found at [" + i + ", " + j + "]");ExcelProcessor processor = ExcelProcessorFactory.getCommentProcessor(comment);processor.process(sheet, dataSource);//refresh rowCountrowCount = sheet.getLastRowNum();}}}}}}}

excel模板处理

import org.apache.commons.logging.Log;import org.apache.commons.logging.LogFactory;import org.apache.poi.openxml4j.exceptions.InvalidFormatException;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.ss.usermodel.WorkbookFactory;import javax.servlet.Http.httpservletRequest;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.io.InputStream;public class ExcelReportTemplate  {private static final Log logger = LogFactory.getLog(ExcelReportTemplate.class);private final String path;private ClassLoader classLoader;public ExcelReportTemplate(String path) {this(path, (ClassLoader) null);}public ExcelReportTemplate(String path, ClassLoader classLoader) {if (path == null) {throw new IllegalArgumentException("Path must not be null");}this.path = path;if (classLoader == null) {try {classLoader = Thread.currentThread().getContextClassLoader();} catch (Throwable ex) {logger.debug("Cannot access thread context ClassLoader - falling back to system class loader", ex);classLoader = ExcelReportTemplate.class.getClassLoader();}}this.classLoader = classLoader;}public Workbook getTemplate() throws IOException, InvalidFormatException {InputStream is =new FileInputStream(this.path);if (is == null) {throw new FileNotFoundException("class path resource [" + this.path + "] cannot be opened because it does not exist");}//return new HSSFWorkbook(is);Workbook workbook = WorkbookFactory.create(is);if(is != null){is.close();}return workbook;}public Workbook getTemplate(HttpServletRequest resquest) throws IOException, InvalidFormatException {InputStream is = this.classLoader.getResourceAsStream(this.path);if (is == null) {throw new FileNotFoundException("class path resource [" + this.path + "] cannot be opened because it does not exist");}Workbook workbook = WorkbookFactory.create(is);if(is != null){is.close();}return workbook;}}

实现关键代码展示
通过模板实现导出功能

 try {            os = new FileOutputStream(excelPth);            ExcelReportCreator.createXLS(new ExcelReportTemplate(excelTempletPath),                    new ExcelDataSource(jsonMap),                    new ExcelReportFiller(),                    os);            excelFilePathName = ftpPath + fileModel.getRealFileName();        } catch (Exception e) {            log.error("导出excel文件出错" + " excel文件路径=" + excelPth + " 模板路径=" + excelTempletPath, e);            log.error("excel内容 " + jsonMap);            throw e;        } finally {            if (os != null) {                os.close();            }        }

ExcelReportCreator 中的代码

public static void createXLS(ExcelReportTemplate template,ExcelDataSource dataSource, ExcelReportFiller filler,OutputStream os) throws IOException, InvalidFormatException {Workbook workbook = template.getTemplate();filler.fill(workbook, dataSource.getData());workbook.write(os);}

导入数据案例展示

excel模板批注案例
每个sheet页都需要写批注，通过批注中的expression对应的值来判断是哪个sheet页的数据，从而进行填充。dataMapping中的key值指的是列序号，value值指的是填充的字段名称，通过名称找对应的数据。

来源地址：https://blog.csdn.net/Do_LaLi/article/details/131873298