Java tutorial
/** * Copyright 2013-2015 JueYue (qrb.jueyue@gmail.com) * * 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 * * http://www.apache.org/licenses/LICENSE-2.0 * * 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.bzvs.excel.export.template; import cn.bzvs.cache.ExcelCache; import cn.bzvs.excel.annotation.ExcelTarget; import cn.bzvs.excel.entity.*; import cn.bzvs.excel.entity.enmus.*; import cn.bzvs.excel.entity.params.ExcelExportEntity; import cn.bzvs.excel.entity.params.ExcelForEachParams; import cn.bzvs.excel.export.base.*; import cn.bzvs.excel.export.styler.IExcelExportStyler; import cn.bzvs.excel.html.helper.*; import cn.bzvs.exception.ExcelExportException; import cn.bzvs.exception.enums.ExcelExportEnum; import cn.bzvs.util.PoiPublicUtil; import cn.bzvs.util.PoiSheetUtility; 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 java.lang.reflect.Field; import java.util.*; import static cn.bzvs.util.PoiElUtil.*; /** * Excel ?? * */ public final class ExcelExportOfTemplateUtil extends ExcelExportBase { /** * TEMP for eachcell ,cell?,?? */ private Set<String> tempCreateCellSet = new HashSet<String>(); /** * ??, */ private TemplateExportParams teplateParams; /** * ??? */ private MergedRegionHelper mergedRegionHelper; private TemplateSumHanlder templateSumHanlder; /** * Sheet ?,?? ,?? * * @param sheet * @param pojoClass * @param dataSet * @param workbook */ private void addDataToSheet(Class<?> pojoClass, Collection<?> dataSet, Sheet sheet, Workbook workbook) throws Exception { if (workbook instanceof XSSFWorkbook) { super.type = ExcelType.XSSF; } // ?? Map<String, Integer> titlemap = getTitleMap(sheet); Drawing patriarch = sheet.createDrawingPatriarch(); // 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); // ?? sortAndFilterExportField(excelParams, titlemap); short rowHeight = getRowHeight(excelParams); int index = teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(), titleHeight = index; //?,? sheet.shiftRows(teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(), sheet.getLastRowNum(), getShiftRows(dataSet, excelParams), true, true); 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); } // ?? mergeCells(sheet, excelParams, titleHeight); } /** * ? * @param dataSet * @param excelParams * @return */ private int getShiftRows(Collection<?> dataSet, List<ExcelExportEntity> excelParams) throws Exception { int size = 0; Iterator<?> its = dataSet.iterator(); while (its.hasNext()) { Object t = its.next(); size += getOneObjectSize(t, excelParams); } return size; } /** * ??,?? * * @param t * @param excelParams * @throws Exception */ public int getOneObjectSize(Object t, List<ExcelExportEntity> excelParams) throws Exception { ExcelExportEntity entity; int maxHeight = 1; for (int k = 0, paramSize = excelParams.size(); k < paramSize; k++) { entity = excelParams.get(k); if (entity.getList() != null) { Collection<?> list = (Collection<?>) entity.getMethod().invoke(t, new Object[] {}); if (list != null && list.size() > maxHeight) { maxHeight = list.size(); } } } return maxHeight; } public Workbook createExcleByTemplate(TemplateExportParams params, Class<?> pojoClass, Collection<?> dataSet, Map<String, Object> map) { // step 1. ?? if (params == null || map == null || StringUtils.isEmpty(params.getTemplateUrl())) { throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR); } Workbook wb = null; // step 2. ?Excel,?? try { this.teplateParams = params; wb = getCloneWorkBook(); // ? setExcelExportStyler( (IExcelExportStyler) teplateParams.getStyle().getConstructor(Workbook.class).newInstance(wb)); // step 3. ?? for (int i = 0, le = params.isScanAllsheet() ? wb.getNumberOfSheets() : params.getSheetNum().length; i < le; i++) { if (params.getSheetName() != null && params.getSheetName().length > i && StringUtils.isNotEmpty(params.getSheetName()[i])) { wb.setSheetName(i, params.getSheetName()[i]); } tempCreateCellSet.clear(); parseTemplate(wb.getSheetAt(i), map, params.isColForEach()); } if (dataSet != null) { // step 4. ? dataHanlder = params.getDataHanlder(); if (dataHanlder != null) { needHanlderList = Arrays.asList(dataHanlder.getNeedHandlerFields()); } addDataToSheet(pojoClass, dataSet, wb.getSheetAt(params.getDataSheetNum()), wb); } } catch (Exception e) { e.getMessage(); return null; } return wb; } /** * excel?,workbook,?excel * * @throws Exception */ private Workbook getCloneWorkBook() throws Exception { return ExcelCache.getWorkbook(teplateParams.getTemplateUrl(), teplateParams.getSheetNum(), teplateParams.isScanAllsheet()); } /** * ??,?? * * @param sheet * @return */ private Map<String, Integer> getTitleMap(Sheet sheet) { Row row = null; Iterator<Cell> cellTitle; Map<String, Integer> titlemap = new HashMap<String, Integer>(); for (int j = 0; j < teplateParams.getHeadingRows(); j++) { row = sheet.getRow(j + teplateParams.getHeadingStartRow()); cellTitle = row.cellIterator(); int i = row.getFirstCellNum(); while (cellTitle.hasNext()) { Cell cell = cellTitle.next(); String value = cell.getStringCellValue(); if (!StringUtils.isEmpty(value)) { titlemap.put(value, i); } i = i + 1; } } return titlemap; } private void parseTemplate(Sheet sheet, Map<String, Object> map, boolean colForeach) throws Exception { deleteCell(sheet, map); mergedRegionHelper = new MergedRegionHelper(sheet); templateSumHanlder = new TemplateSumHanlder(sheet); if (colForeach) { colForeach(sheet, map); } Row row = null; int index = 0; while (index <= sheet.getLastRowNum()) { row = sheet.getRow(index++); if (row == null) { continue; } for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) { if (row.getCell(i) != null && !tempCreateCellSet.contains(row.getRowNum() + "_" + row.getCell(i).getColumnIndex())) { setValueForCellByMap(row.getCell(i), map); } } } //?? hanlderSumCell(sheet); } private void hanlderSumCell(Sheet sheet) { for (TemplateSumHanlder.TemplateSumEntity sumEntity : templateSumHanlder.getDataList()) { Cell cell = sheet.getRow(sumEntity.getRow()).getCell(sumEntity.getCol()); cell.setCellValue(cell.getStringCellValue().replace("sum:(" + sumEntity.getSumKey() + ")", sumEntity.getValue() + "")); } } /** * ,?? * @param sheet * @param map */ private void colForeach(Sheet sheet, Map<String, Object> map) throws Exception { Row row = null; Cell cell = null; int index = 0; while (index <= sheet.getLastRowNum()) { row = sheet.getRow(index++); if (row == null) { continue; } for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) { cell = row.getCell(i); if (row.getCell(i) != null && (cell.getCellType() == Cell.CELL_TYPE_STRING || cell.getCellType() == Cell.CELL_TYPE_NUMERIC)) { cell.setCellType(Cell.CELL_TYPE_STRING); String text = cell.getStringCellValue(); if (text.contains(FOREACH_COL) || text.contains(FOREACH_COL_VALUE)) { foreachCol(cell, map, text); } } } } } /** * * @param cell * @param map * @param name * @throws Exception */ private void foreachCol(Cell cell, Map<String, Object> map, String name) throws Exception { boolean isCreate = name.contains(FOREACH_COL_VALUE); name = name.replace(FOREACH_COL_VALUE, EMPTY).replace(FOREACH_COL, EMPTY).replace(START_STR, EMPTY); String[] keys = name.replaceAll("\\s{1,}", " ").trim().split(" "); Collection<?> datas = (Collection<?>) PoiPublicUtil.getParamsValue(keys[0], map); Object[] columnsInfo = getAllDataColumns(cell, name.replace(keys[0], EMPTY), mergedRegionHelper); if (datas == null) { return; } Iterator<?> its = datas.iterator(); int rowspan = (Integer) columnsInfo[0], colspan = (Integer) columnsInfo[1]; @SuppressWarnings("unchecked") List<ExcelForEachParams> columns = (List<ExcelForEachParams>) columnsInfo[2]; while (its.hasNext()) { Object t = its.next(); setForEeachRowCellValue(true, cell.getRow(), cell.getColumnIndex(), t, columns, map, rowspan, colspan, mergedRegionHelper); cell = cell.getRow().getCell(cell.getColumnIndex() + colspan); } if (isCreate) { cell = cell.getRow().getCell(cell.getColumnIndex() - 1); cell.setCellValue(cell.getStringCellValue() + END_STR); } } /** * ,?? * @param sheet * @param map * @throws Exception */ private void deleteCell(Sheet sheet, Map<String, Object> map) throws Exception { Row row = null; Cell cell = null; int index = 0; while (index <= sheet.getLastRowNum()) { row = sheet.getRow(index++); if (row == null) { continue; } for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) { cell = row.getCell(i); if (row.getCell(i) != null && (cell.getCellType() == Cell.CELL_TYPE_STRING || cell.getCellType() == Cell.CELL_TYPE_NUMERIC)) { cell.setCellType(Cell.CELL_TYPE_STRING); String text = cell.getStringCellValue(); if (text.contains(IF_DELETE)) { if (Boolean.valueOf( eval(text.substring(text.indexOf(START_STR) + 2, text.indexOf(END_STR)).trim(), map) .toString())) { PoiSheetUtility.deleteColumn(sheet, i); } cell.setCellValue(""); } } } } } /** * ?Cell??set * * @param cell * @param map */ private void setValueForCellByMap(Cell cell, Map<String, Object> map) throws Exception { int cellType = cell.getCellType(); if (cellType != Cell.CELL_TYPE_STRING && cellType != Cell.CELL_TYPE_NUMERIC) { return; } String oldString; cell.setCellType(Cell.CELL_TYPE_STRING); oldString = cell.getStringCellValue(); if (oldString != null && oldString.indexOf(START_STR) != -1 && !oldString.contains(FOREACH)) { // step 2. ??? String params = null; boolean isNumber = false; if (isNumber(oldString)) { isNumber = true; oldString = oldString.replaceFirst(NUMBER_SYMBOL, ""); } while (oldString.indexOf(START_STR) != -1) { params = oldString.substring(oldString.indexOf(START_STR) + 2, oldString.indexOf(END_STR)); oldString = oldString.replace(START_STR + params + END_STR, eval(params, map).toString()); } // , if (isNumber && StringUtils.isNotBlank(oldString)) { cell.setCellValue(Double.parseDouble(oldString)); cell.setCellType(Cell.CELL_TYPE_NUMERIC); } else { cell.setCellValue(oldString); } } //foreach ? if (oldString != null && oldString.contains(FOREACH)) { addListDataToExcel(cell, map, oldString.trim()); } } private boolean isNumber(String text) { return text.startsWith(NUMBER_SYMBOL) || text.contains("{" + NUMBER_SYMBOL) || text.contains(" " + NUMBER_SYMBOL); } /** * 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); Object[] columnsInfo = getAllDataColumns(cell, name.replace(keys[0], EMPTY), mergedRegionHelper); if (datas == null) { return; } 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; //?? if (its.hasNext()) { Object t = its.next(); setForEeachRowCellValue(isCreate, cell.getRow(), cell.getColumnIndex(), t, columns, map, rowspan, colspan, mergedRegionHelper); rowIndex += rowspan - 1; } if (isShift && datas.size() * rowspan > 1) { cell.getRow().getSheet().shiftRows(cell.getRowIndex() + rowspan, cell.getRow().getSheet().getLastRowNum(), (datas.size() - 1) * rowspan, true, true); /* cell.getRow().getSheet().shiftRows(cell.getRowIndex() + 1, cell.getRow().getSheet().getLastRowNum(), datas.size() * rowspan - 1, true, true);*/ templateSumHanlder.shiftRows(cell.getRowIndex(), (datas.size() - 1) * rowspan); } while (its.hasNext()) { Object t = its.next(); row = createRow(rowIndex, cell.getSheet(), isCreate, rowspan); setForEeachRowCellValue(isCreate, row, cell.getColumnIndex(), t, columns, map, rowspan, colspan, mergedRegionHelper); rowIndex += rowspan; } } /** * Row * @param sheet * @param rowIndex * @param isCreate * @param rows * @return */ private Row createRow(int rowIndex, Sheet sheet, boolean isCreate, int rows) { for (int i = 0; i < rows; i++) { if (isCreate) { sheet.createRow(rowIndex++); } else if (sheet.getRow(rowIndex++) == null) { sheet.createRow(rowIndex - 1); } } return sheet.getRow(rowIndex - rows); } private void setForEeachRowCellValue(boolean isCreate, Row row, int columnIndex, Object t, List<ExcelForEachParams> columns, Map<String, Object> map, int rowspan, int colspan, MergedRegionHelper mergedRegionHelper) throws Exception { //cell?? for (int i = 0; i < rowspan; i++) { int size = columns.size();//? for (int j = columnIndex, max = columnIndex + colspan; j < max; j++) { if (row.getCell(j) == null) { row.createCell(j); CellStyle style = row.getRowNum() % 2 == 0 ? getStyles(false, size >= j - columnIndex ? null : columns.get(j - columnIndex)) : getStyles(true, size >= j - columnIndex ? null : columns.get(j - columnIndex)); //styler??,?Excel,??Excel? if (style != null) row.getCell(j).setCellStyle(style); } } if (i < rowspan - 1) { row = row.getSheet().getRow(row.getRowNum() + 1); } } //? ExcelForEachParams params; row = row.getSheet().getRow(row.getRowNum() - rowspan + 1); for (int k = 0; k < rowspan; k++) { int ci = columnIndex;//cell?? row.setHeight(columns.get(0 * colspan).getHeight()); for (int i = 0; i < colspan && i < columns.size(); i++) { boolean isNumber = false; params = columns.get(colspan * k + i); tempCreateCellSet.add(row.getRowNum() + "_" + (ci)); if (params == null) { continue; } if (StringUtils.isEmpty(params.getName()) && StringUtils.isEmpty(params.getConstValue())) { row.getCell(ci).setCellStyle(columns.get(i).getCellStyle()); ci = ci + columns.get(i).getColspan(); continue; } String val = null; //?? if (StringUtils.isEmpty(params.getName())) { val = params.getConstValue(); } else { String tempStr = new String(params.getName()); if (isNumber(tempStr)) { isNumber = true; tempStr = tempStr.replaceFirst(NUMBER_SYMBOL, ""); } map.put(teplateParams.getTempParams(), t); val = eval(tempStr, map).toString(); } if (isNumber && StringUtils.isNotEmpty(val)) { row.getCell(ci).setCellValue(Double.parseDouble(val)); row.getCell(ci).setCellType(Cell.CELL_TYPE_NUMERIC); } else { try { row.getCell(ci).setCellValue(val); } catch (Exception e) { e.getMessage(); } } row.getCell(ci).setCellStyle(columns.get(i).getCellStyle()); //?? if (params.isNeedSum()) { templateSumHanlder.addValueOfKey(params.getName(), val); } //??,????? setMergedRegionStyle(row, ci, columns.get(i)); //?? if ((params.getRowspan() != 1 || params.getColspan() != 1) && !mergedRegionHelper.isMergedRegion(row.getRowNum() + 1, ci)) { row.getSheet().addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum() + params.getRowspan() - 1, ci, ci + params.getColspan() - 1)); } ci = ci + params.getColspan(); } row = row.getSheet().getRow(row.getRowNum() + 1); } } private CellStyle getStyles(boolean isSingle, ExcelForEachParams excelForEachParams) { return excelExportStyler.getTemplateStyles(isSingle, excelForEachParams); } /** * ??? * @param row * @param ci * @param params */ private void setMergedRegionStyle(Row row, int ci, ExcelForEachParams params) { //? for (int i = 1; i < params.getColspan(); i++) { row.getCell(ci + i).setCellStyle(params.getCellStyle()); } for (int i = 1; i < params.getRowspan(); i++) { for (int j = 0; j < params.getColspan(); j++) { row.getCell(ci + j).setCellStyle(params.getCellStyle()); } } } /** * ?? * @param cell * @param name * @param mergedRegionHelper * @return */ private Object[] getAllDataColumns(Cell cell, String name, MergedRegionHelper mergedRegionHelper) { List<ExcelForEachParams> columns = new ArrayList<ExcelForEachParams>(); cell.setCellValue(""); columns.add(getExcelTemplateParams(name.replace(END_STR, EMPTY), cell, mergedRegionHelper)); int rowspan = 1, colspan = 1; if (!name.contains(END_STR)) { int index = cell.getColumnIndex(); //?col int startIndex = cell.getColumnIndex(); Row row = cell.getRow(); while (true) { int colSpan = columns.get(columns.size() - 1) != null ? columns.get(columns.size() - 1).getColspan() : 1; index += colSpan; for (int i = 1; i < colSpan; i++) { //??,???,,? columns.add(null); continue; } cell = row.getCell(index); //??? if (cell == null) { //?, columns.add(null); continue; } String cellStringString; try {//?? ? cellStringString = cell.getStringCellValue(); if (StringUtils.isBlank(cellStringString) && colspan + startIndex <= index) { throw new ExcelExportException("for each ,?"); } else if (StringUtils.isBlank(cellStringString) && colspan + startIndex > index) { //?,,?,?,? columns.add(new ExcelForEachParams(null, cell.getCellStyle(), (short) 0)); continue; } } catch (Exception e) { throw new ExcelExportException(ExcelExportEnum.TEMPLATE_ERROR, e); } //?cell cell.setCellValue(""); if (cellStringString.contains(END_STR)) { columns.add(getExcelTemplateParams(cellStringString.replace(END_STR, EMPTY), cell, mergedRegionHelper)); break; } else if (cellStringString.contains(WRAP)) { columns.add(getExcelTemplateParams(cellStringString.replace(WRAP, EMPTY), cell, mergedRegionHelper)); //??,?? colspan = index - startIndex + 1; index = startIndex - 1; row = row.getSheet().getRow(row.getRowNum() + 1); rowspan++; } else { columns.add(getExcelTemplateParams(cellStringString.replace(WRAP, EMPTY), cell, mergedRegionHelper)); } } } colspan = 0; for (int i = 0; i < columns.size(); i++) { colspan += columns.get(i) != null ? columns.get(i).getColspan() : 0; } colspan = colspan / rowspan; return new Object[] { rowspan, colspan, columns }; } /** * ??? * @param name * @param cell * @param mergedRegionHelper * @return */ private ExcelForEachParams getExcelTemplateParams(String name, Cell cell, MergedRegionHelper mergedRegionHelper) { name = name.trim(); ExcelForEachParams params = new ExcelForEachParams(name, cell.getCellStyle(), cell.getRow().getHeight()); //?? if (name.startsWith(CONST) && name.endsWith(CONST)) { params.setName(null); params.setConstValue(name.substring(1, name.length() - 1)); } //? if (NULL.equals(name)) { params.setName(null); params.setConstValue(EMPTY); } //???? if (mergedRegionHelper.isMergedRegion(cell.getRowIndex() + 1, cell.getColumnIndex())) { Integer[] colAndrow = mergedRegionHelper.getRowAndColSpan(cell.getRowIndex() + 1, cell.getColumnIndex()); params.setRowspan(colAndrow[0]); params.setColspan(colAndrow[1]); } params.setNeedSum(templateSumHanlder.isSumKey(params.getName())); return params; } /** * ?? * * @param excelParams * @param titlemap */ private void sortAndFilterExportField(List<ExcelExportEntity> excelParams, Map<String, Integer> titlemap) { for (int i = excelParams.size() - 1; i >= 0; i--) { if (excelParams.get(i).getList() != null && excelParams.get(i).getList().size() > 0) { sortAndFilterExportField(excelParams.get(i).getList(), titlemap); if (excelParams.get(i).getList().size() == 0) { excelParams.remove(i); } else { excelParams.get(i).setOrderNum(i); } } else { if (titlemap.containsKey(excelParams.get(i).getName())) { excelParams.get(i).setOrderNum(i); } else { excelParams.remove(i); } } } sortAllParams(excelParams); } public Workbook createExcleByTemplate(TemplateExportParams params, Map<Integer, Map<String, Object>> map) { // step 1. ?? if (params == null || map == null || StringUtils.isEmpty(params.getTemplateUrl())) { throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR); } Workbook wb = null; // step 2. ?Excel,?? try { this.teplateParams = params; wb = getCloneWorkBook(); // step 3. ?? for (int i = 0, le = params.isScanAllsheet() ? wb.getNumberOfSheets() : params.getSheetNum().length; i < le; i++) { if (params.getSheetName() != null && params.getSheetName().length > i && StringUtils.isNotEmpty(params.getSheetName()[i])) { wb.setSheetName(i, params.getSheetName()[i]); } tempCreateCellSet.clear(); parseTemplate(wb.getSheetAt(i), map.get(i), params.isColForEach()); } } catch (Exception e) { e.getMessage(); return null; } return wb; } }