meidu2021/daju/target/classes/cn/com/lzt/common/util/OrdersExcelExportOfTemplateUtil.java

/**
 * 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.com.lzt.common.util;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jeecgframework.poi.cache.ExcelCache;
import org.jeecgframework.poi.excel.annotation.ExcelTarget;
import org.jeecgframework.poi.excel.entity.TemplateExportParams;
import org.jeecgframework.poi.excel.entity.enmus.ExcelType;
import org.jeecgframework.poi.excel.entity.params.ExcelExportEntity;
import org.jeecgframework.poi.excel.entity.params.ExcelTemplateParams;
import org.jeecgframework.poi.excel.export.base.ExcelExportBase;
import org.jeecgframework.poi.excel.export.styler.IExcelExportStyler;
import org.jeecgframework.poi.exception.excel.ExcelExportException;
import org.jeecgframework.poi.exception.excel.enums.ExcelExportEnum;
import org.jeecgframework.poi.util.PoiPublicUtil;
import org.jeecgframework.poi.util.PoiSheetUtility;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.lang.reflect.Field;
import java.util.*;

import static org.jeecgframework.poi.util.PoiElUtil.*;

/**
 * Excel 瀵煎嚭鏍规嵁妯℃澘瀵煎嚭
 *
 * @author JueYue
 * @date 2013-10-17
 * @version 1.0
 */
public final class OrdersExcelExportOfTemplateUtil extends ExcelExportBase {

    private static final Logger LOGGER = LoggerFactory.getLogger(OrdersExcelExportOfTemplateUtil.class);

    /**
     * 缂撳瓨TEMP 鐨刦or each鍒涘缓鐨刢ell ,璺宠繃杩欎釜cell鐨勬ā鏉胯娉曟煡鎵�,鎻愰珮鏁堢巼
     */
    private Set<String> tempCreateCellSet = new HashSet<String>();
    /**
     * 妯℃澘鍙傛暟,鍏ㄥ眬閮界敤鍒�
     */
    private TemplateExportParams teplateParams;

    /**
     * 寰�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 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. 鍒ゆ柇妯℃澘鐨凟xcel绫诲瀷,瑙ｆ瀽妯℃澘
        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鏃犳硶鍏嬮殕,鍙兘瀵筫xcel杩涜鍏嬮殕
     *
     * @throws Exception
     * @Author JueYue
     * @date 2013-11-11
     */
    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) 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() == CellType.STRING || cell.getCellType() == CellType.NUMERIC)) {
                    cell.setCellType(CellType.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("");
                    }
                }
            }
        }
    }

    /**
     * 缁欐瘡涓狢ell閫氳繃瑙ｆ瀽鏂瑰紡set鍊�
     *
     * @param cell
     * @param map
     */
    private void setValueForCellByMap(Cell cell, Map<String, Object> map) throws Exception {
        CellType cellType = cell.getCellType();
        if (cellType != CellType.STRING && cellType != CellType.NUMERIC) {
            return;
        }
        String oldString;
        cell.setCellType(CellType.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.replace(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(CellType.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
     * @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);
        List<ExcelTemplateParams> columns = getAllDataColumns(cell, name.replace(keys[0], 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<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(NUMBER_SYMBOL, "");
            }
            map.put(teplateParams.getTempParams(), t);
            String val = eval(tempStr, map).toString();
            if (isNumber && StringUtils.isNotEmpty(val)) {
                row.getCell(i + columnIndex).setCellValue(Double.parseDouble(val));
                row.getCell(i + columnIndex).setCellType(CellType.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<ExcelTemplateParams> getAllDataColumns(Cell cell, String name) {
        List<ExcelTemplateParams> columns = new ArrayList<ExcelTemplateParams>();
        cell.setCellValue("");
        if (name.contains(END_STR)) {
            columns.add(new ExcelTemplateParams(name.replace(END_STR, EMPTY).trim(), cell.getCellStyle(), cell.getRow().getHeight()));
            return columns;
        }
        columns.add(new 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 褰撲腑瀛樺湪绌哄瓧绗︿覆,璇锋鏌ユā鏉�");
            }
            // 鎶婅鍙栬繃鐨刢ell 缃负绌�
            tempCell.setCellValue("");
            if (cellStringString.contains(END_STR)) {
                columns.add(new ExcelTemplateParams(cellStringString.trim().replace(END_STR, ""), tempCell.getCellStyle(), tempCell.getRow().getHeight()));
                break;
            } else {
                if (cellStringString.trim().contains(teplateParams.getTempParams())) {
                    columns.add(new ExcelTemplateParams(cellStringString.trim(), tempCell.getCellStyle(), tempCell.getRow().getHeight()));
                } else {
                    // 鏈�鍚庝竴琛岃鍒犻櫎浜�
                    break;
                }
            }

        }
        return columns;
    }

    /**
     * 瀵瑰鍑哄簭鍒楄繘琛屾帓搴忓拰濉為��
     *
     * @param excelParams
     * @param titlemap
     * @return
     */
    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);
    }

}