EasyPoi Excel和 Word简易工具类

/** * Copyright 2013-2015 JueYue (qrb.jueyue@gmail.com) * <p> * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * <p> * http://www.apache.org/licenses/LICENSE-2.0 * <p> * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package cn.afterturn.easypoi.excel.export.template; import cn.afterturn.easypoi.cache.ExcelCache; import cn.afterturn.easypoi.entity.ImageEntity; import cn.afterturn.easypoi.excel.annotation.ExcelTarget; import cn.afterturn.easypoi.excel.entity.TemplateExportParams; import cn.afterturn.easypoi.excel.entity.TemplateSumEntity; import cn.afterturn.easypoi.excel.entity.enmus.ExcelType; import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity; import cn.afterturn.easypoi.excel.entity.params.ExcelForEachParams; import cn.afterturn.easypoi.excel.export.base.BaseExportService; import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler; import cn.afterturn.easypoi.excel.html.helper.MergedRegionHelper; import cn.afterturn.easypoi.exception.excel.ExcelExportException; import cn.afterturn.easypoi.exception.excel.enums.ExcelExportEnum; import cn.afterturn.easypoi.util.*; import org.apache.commons.lang3.StringUtils; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.lang.reflect.Field; import java.util.*; import static cn.afterturn.easypoi.excel.ExcelExportUtil.SHEET_NAME; import static cn.afterturn.easypoi.util.PoiElUtil.*; /** * Excel 导出根据模板导出 * * @author JueYue * 2013-10-17 * @version 1.0 */ public final class ExcelExportOfTemplateUtil extends BaseExportService { private static final Logger LOGGER = LoggerFactory .getLogger(ExcelExportOfTemplateUtil.class); /** * 缓存TEMP 的for each创建的cell ,跳过这个cell的模板语法查找,提高效率 */ private Set<String> tempCreateCellSet = new HashSet<String>(); /** * 模板参数,全局都用到 */ private TemplateExportParams templateParams; /** * 单元格合并信息 */ private MergedRegionHelper mergedRegionHelper; private TemplateSumHandler templateSumHandler; /** * 往Sheet 填充正常数据,根据表头信息 使用导入的部分逻辑,坐对象映射 * * @param sheet * @param pojoClass * @param dataSet * @param workbook */ private void addDataToSheet(Class<?> pojoClass, Collection<?> dataSet, Sheet sheet, Workbook workbook) throws Exception { // 获取表头数据 Map<String, Integer> titlemap = getTitleMap(sheet); Drawing patriarch = PoiExcelGraphDataUtil.getDrawingPatriarch(sheet); // 得到所有字段 Field[] fileds = PoiPublicUtil.getClassFields(pojoClass); ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class); String targetId = null; if (etarget != null) { targetId = etarget.value(); } // 获取实体对象的导出数据 List<ExcelExportEntity> excelParams = new ArrayList<ExcelExportEntity>(); getAllExcelField(null, targetId, fileds, excelParams, pojoClass, null, null); // 根据表头进行筛选排序 sortAndFilterExportField(excelParams, titlemap); short rowHeight = getRowHeight(excelParams); int index = templateParams.getHeadingRows() + templateParams.getHeadingStartRow(), titleHeight = index; int shiftRows = getShiftRows(dataSet, excelParams); //下移数据,模拟插入 sheet.shiftRows(templateParams.getHeadingRows() + templateParams.getHeadingStartRow(), sheet.getLastRowNum(), shiftRows, true, true); mergedRegionHelper.shiftRows(sheet, templateParams.getHeadingRows() + templateParams.getHeadingStartRow(), shiftRows, sheet.getLastRowNum() - templateParams.getHeadingRows() - templateParams.getHeadingStartRow()); templateSumHandler.shiftRows(templateParams.getHeadingRows() + templateParams.getHeadingStartRow(), shiftRows); PoiExcelTempUtil.reset(sheet, templateParams.getHeadingRows() + templateParams.getHeadingStartRow(), sheet.getLastRowNum()); if (excelParams.size() == 0) { return; } Iterator<?> its = dataSet.iterator(); while (its.hasNext()) { Object t = its.next(); index += createCells(patriarch, index, t, excelParams, sheet, workbook, rowHeight, 0)[0]; } // 合并同类项 mergeCells(sheet, excelParams, titleHeight); } /** * 利用foreach循环输出数据 * * @param cell * @param map * @param name * @throws Exception */ private void addListDataToExcel(Cell cell, Map<String, Object> map, String name) throws Exception { boolean isCreate = !name.contains(FOREACH_NOT_CREATE); boolean isShift = name.contains(FOREACH_AND_SHIFT); name = name.replace(FOREACH_NOT_CREATE, EMPTY).replace(FOREACH_AND_SHIFT, EMPTY) .replace(FOREACH, EMPTY).replace(START_STR, EMPTY); String[] keys = name.replaceAll("\\s{1,}", " ").trim().split(" "); Collection<?> datas = (Collection<?>) PoiPublicUtil.getParamsValue(keys[0], map); if (datas == null) { return; } Object[] columnsInfo = getAllDataColumns(cell, name.replace(keys[0], EMPTY), mergedRegionHelper); Iterator<?> its = datas.iterator(); int rowspan = (Integer) columnsInfo[0], colspan = (Integer) columnsInfo[1]; @SuppressWarnings("unchecked") List<ExcelForEachParams> columns = (List<ExcelForEachParams>) columnsInfo[2]; Row row = null; int rowIndex = cell.getRow().getRowNum() + 1; ExcelForEachParams indexColumn = getIndexColumn(columns); //处理当前行 int loopSize = 0; if (its.hasNext()) { Object t = its.next(); loopSize = setForeachRowCellValue(isCreate, cell.getRow(), cell.getColumnIndex(), t, columns, map, rowspan, colspan, mergedRegionHelper)[0]; rowIndex += rowspan - 1 + loopSize - 1; } //修复不论后面有没有数据,都应该执行的是插入操作 if (isShift && datas.size() > 1 && datas.size() * rowspan > 1 && cell.getRowIndex() + rowspan <= cell.getRow().getSheet().getLastRowNum()) { int lastRowNum = cell.getRow().getSheet().getLastRowNum(); int shiftRows = lastRowNum - cell.getRowIndex() - rowspan; cell.getRow().getSheet().shiftRows(cell.getRowIndex() + rowspan, lastRowNum, (datas.size() - 1) * rowspan, true, true); mergedRegionHelper.shiftRows(cell.getSheet(), cell.getRowIndex() + rowspan, (datas.size() - 1) * rowspan, shiftRows); templateSumHandler.shiftRows(cell.getRowIndex() + rowspan, (datas.size() - 1) * rowspan); PoiExcelTempUtil.reset(cell.getSheet(), cell.getRowIndex() + rowspan + (datas.size() - 1) * rowspan, cell.getRow().getSheet().getLastRowNum()); } while (its.hasNext()) { Object t = its.next(); row = createRow(rowInd