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 com.qihang.winter.poi.excel.export.template; import java.lang.reflect.Field; import java.util.ArrayList; import java.util.Arrays; import java.util.Collection; import java.util.HashMap; import java.util.HashSet; import java.util.Iterator; import java.util.List; import java.util.Map; import java.util.Set; import com.qihang.winter.poi.excel.entity.TemplateExportParams; import com.qihang.winter.poi.excel.export.styler.IExcelExportStyler; import com.qihang.winter.poi.exception.excel.enums.ExcelExportEnum; import com.qihang.winter.poi.util.PoiElUtil; import org.apache.commons.lang3.StringUtils; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Drawing; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import com.qihang.winter.poi.cache.ExcelCache; import com.qihang.winter.poi.exception.excel.ExcelExportException; import org.slf4j.Logger; import org.slf4j.LoggerFactory; /** * Excel ?? * * @author Zerrion * @date 2013-10-17 * @version 1.0 */ public final class ExcelExportOfTemplateUtil extends com.qihang.winter.poi.excel.export.base.ExcelExportBase { private static final Logger LOGGER = LoggerFactory.getLogger(ExcelExportOfTemplateUtil.class); /** * TEMP for eachcell ,cell?,?? */ private Set<String> tempCreateCellSet = new HashSet<String>(); /** * ??, */ private com.qihang.winter.poi.excel.entity.TemplateExportParams teplateParams; /** * Sheet ?,?? ,?? * * @param teplateParams * @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 = com.qihang.winter.poi.excel.entity.enmus.ExcelType.XSSF; } // ?? Map<String, Integer> titlemap = getTitleMap(sheet); Drawing patriarch = sheet.createDrawingPatriarch(); // Field[] fileds = com.qihang.winter.poi.util.PoiPublicUtil.getClassFields(pojoClass); com.qihang.winter.poi.excel.annotation.ExcelTarget etarget = pojoClass .getAnnotation(com.qihang.winter.poi.excel.annotation.ExcelTarget.class); String targetId = null; if (etarget != null) { targetId = etarget.value(); } // ?? List<com.qihang.winter.poi.excel.entity.params.ExcelExportEntity> excelParams = new ArrayList<com.qihang.winter.poi.excel.entity.params.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 its * @param excelParams * @return */ private int getShiftRows(Collection<?> dataSet, List<com.qihang.winter.poi.excel.entity.params.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 styles * @param rowHeight * @throws Exception */ public int getOneObjectSize(Object t, List<com.qihang.winter.poi.excel.entity.params.ExcelExportEntity> excelParams) throws Exception { com.qihang.winter.poi.excel.entity.params.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); } 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) { LOGGER.error(e.getMessage(), e); return null; } return wb; } /** * excel?,workbook,?excel * * @param teplateParams * @throws Exception * @author Zerrion * @date 2013-11-11 */ private Workbook getCloneWorkBook() throws Exception { return ExcelCache.getWorkbook(teplateParams.getTemplateUrl(), teplateParams.getSheetNum(), teplateParams.isScanAllsheet()); } /** * ??,?? * * @param teplateParams * @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) throws Exception { deleteCell(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); } } } } /** * ,?? * @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(PoiElUtil.IF_DELETE)) { if (Boolean.valueOf(PoiElUtil.eval(text .substring(text.indexOf(PoiElUtil.START_STR) + 2, text.indexOf(PoiElUtil.END_STR)) .trim(), map).toString())) { com.qihang.winter.poi.util.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(PoiElUtil.START_STR) != -1 && !oldString.contains(PoiElUtil.FOREACH)) { // step 2. ??? String params = null; boolean isNumber = false; if (isNumber(oldString)) { isNumber = true; oldString = oldString.replace(PoiElUtil.NUMBER_SYMBOL, ""); } while (oldString.indexOf(PoiElUtil.START_STR) != -1) { params = oldString.substring(oldString.indexOf(PoiElUtil.START_STR) + 2, oldString.indexOf(PoiElUtil.END_STR)); oldString = oldString.replace(PoiElUtil.START_STR + params + PoiElUtil.END_STR, PoiElUtil.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(PoiElUtil.FOREACH)) { addListDataToExcel(cell, map, oldString.trim()); } } private boolean isNumber(String text) { return text.startsWith(PoiElUtil.NUMBER_SYMBOL) || text.contains("{" + PoiElUtil.NUMBER_SYMBOL) || text.contains(" " + PoiElUtil.NUMBER_SYMBOL); } /** * foreach? * @param cell * @param map * @param oldString * @throws Exception */ private void addListDataToExcel(Cell cell, Map<String, Object> map, String name) throws Exception { boolean isCreate = !name.contains(PoiElUtil.FOREACH_NOT_CREATE); boolean isShift = name.contains(PoiElUtil.FOREACH_AND_SHIFT); name = name.replace(PoiElUtil.FOREACH_NOT_CREATE, PoiElUtil.EMPTY) .replace(PoiElUtil.FOREACH_AND_SHIFT, PoiElUtil.EMPTY).replace(PoiElUtil.FOREACH, PoiElUtil.EMPTY) .replace(PoiElUtil.START_STR, PoiElUtil.EMPTY); String[] keys = name.replaceAll("\\s{1,}", " ").trim().split(" "); Collection<?> datas = (Collection<?>) com.qihang.winter.poi.util.PoiPublicUtil.getParamsValue(keys[0], map); List<com.qihang.winter.poi.excel.entity.params.ExcelTemplateParams> columns = getAllDataColumns(cell, name.replace(keys[0], PoiElUtil.EMPTY)); if (datas == null) { return; } Iterator<?> its = datas.iterator(); Row row; int rowIndex = cell.getRow().getRowNum() + 1; //?? if (its.hasNext()) { Object t = its.next(); cell.getRow().setHeight(columns.get(0).getHeight()); setForEeachCellValue(isCreate, cell.getRow(), cell.getColumnIndex(), t, columns, map); } if (isShift) { cell.getRow().getSheet().shiftRows(cell.getRowIndex() + 1, cell.getRow().getSheet().getLastRowNum(), datas.size() - 1, true, true); } while (its.hasNext()) { Object t = its.next(); if (isCreate) { row = cell.getRow().getSheet().createRow(rowIndex++); } else { row = cell.getRow().getSheet().getRow(rowIndex++); if (row == null) { row = cell.getRow().getSheet().createRow(rowIndex - 1); } } row.setHeight(columns.get(0).getHeight()); setForEeachCellValue(isCreate, row, cell.getColumnIndex(), t, columns, map); } } private void setForEeachCellValue(boolean isCreate, Row row, int columnIndex, Object t, List<com.qihang.winter.poi.excel.entity.params.ExcelTemplateParams> columns, Map<String, Object> map) throws Exception { for (int i = 0, max = columnIndex + columns.size(); i < max; i++) { if (row.getCell(i) == null) row.createCell(i); } for (int i = 0, max = columns.size(); i < max; i++) { boolean isNumber = false; String tempStr = new String(columns.get(i).getName()); if (isNumber(tempStr)) { isNumber = true; tempStr = tempStr.replace(PoiElUtil.NUMBER_SYMBOL, ""); } map.put(teplateParams.getTempParams(), t); String val = PoiElUtil.eval(tempStr, map).toString(); if (isNumber && StringUtils.isNotEmpty(val)) { row.getCell(i + columnIndex).setCellValue(Double.parseDouble(val)); row.getCell(i + columnIndex).setCellType(Cell.CELL_TYPE_NUMERIC); } else { row.getCell(i + columnIndex).setCellValue(val); } row.getCell(i + columnIndex).setCellStyle(columns.get(i).getCellStyle()); tempCreateCellSet.add(row.getRowNum() + "_" + (i + columnIndex)); } } /** * ?? * @param cell * @param name * @return */ private List<com.qihang.winter.poi.excel.entity.params.ExcelTemplateParams> getAllDataColumns(Cell cell, String name) { List<com.qihang.winter.poi.excel.entity.params.ExcelTemplateParams> columns = new ArrayList<com.qihang.winter.poi.excel.entity.params.ExcelTemplateParams>(); cell.setCellValue(""); if (name.contains(PoiElUtil.END_STR)) { columns.add(new com.qihang.winter.poi.excel.entity.params.ExcelTemplateParams( name.replace(PoiElUtil.END_STR, PoiElUtil.EMPTY).trim(), cell.getCellStyle(), cell.getRow().getHeight())); return columns; } columns.add(new com.qihang.winter.poi.excel.entity.params.ExcelTemplateParams(name.trim(), cell.getCellStyle(), cell.getRow().getHeight())); int index = cell.getColumnIndex(); Cell tempCell; while (true) { tempCell = cell.getRow().getCell(++index); if (tempCell == null) { break; } String cellStringString; try {//?,??,? cellStringString = tempCell.getStringCellValue(); if (StringUtils.isBlank(cellStringString)) { break; } } catch (Exception e) { throw new ExcelExportException("for each ,?"); } //?cell tempCell.setCellValue(""); if (cellStringString.contains(PoiElUtil.END_STR)) { columns.add(new com.qihang.winter.poi.excel.entity.params.ExcelTemplateParams( cellStringString.trim().replace(PoiElUtil.END_STR, ""), tempCell.getCellStyle(), tempCell.getRow().getHeight())); break; } else { if (cellStringString.trim().contains(teplateParams.getTempParams())) { columns.add(new com.qihang.winter.poi.excel.entity.params.ExcelTemplateParams( cellStringString.trim(), tempCell.getCellStyle(), tempCell.getRow().getHeight())); } else { //? break; } } } return columns; } /** * ?? * * @param excelParams * @param titlemap * @return */ private void sortAndFilterExportField( List<com.qihang.winter.poi.excel.entity.params.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); } }