demo-comment-excel-processor/src/main/java/com/tptj/demo/hg/comment/excel/processor/Demo.java

package com.tptj.demo.hg.comment.excel.processor;

import com.fanruan.api.log.LogKit;
import com.fr.form.ui.ComboBox;
import com.fr.form.ui.DictContainedCustomWriteAbleEditor;
import com.fr.form.ui.Widget;
import com.fr.intelli.record.Focus;
import com.fr.invoke.Reflect;
import com.fr.io.core.PageExcelExporterReport;
import com.fr.log.FineLoggerFactory;
import com.fr.main.workbook.ResultWorkBook;
import com.fr.record.analyzer.EnableMetrics;
import com.fr.report.cell.*;
import com.fr.report.cell.cellattr.PageExportCellElement;
import com.fr.report.core.PackedReport;
import com.fr.report.elementcase.ElementCase;
import com.fr.report.fun.impl.AbstractCommentExcelProcessor;
import com.fr.report.report.ResultReport;
import com.fr.report.worksheet.CalculatableResWorkSheet;
import com.fr.stable.ColumnRow;
import com.fr.third.v2.org.apache.poi.ss.usermodel.*;
import com.fr.third.v2.org.apache.poi.ss.usermodel.Cell;
import com.fr.third.v2.org.apache.poi.xssf.streaming.SXSSFCell;
import com.fr.third.v2.org.apache.poi.xssf.streaming.SXSSFRow;
import com.fr.third.v2.org.apache.poi.xssf.streaming.SXSSFSheet;
import com.fr.third.v2.org.apache.poi.xssf.streaming.SXSSFWorkbook;
import com.fr.third.v2.org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
import com.fr.write.main.WriteRWorkBook;

import java.util.Iterator;
import java.util.List;
import java.util.UUID;

/**
 * @author 秃破天际
 * @version 10.0
 * Created by 秃破天际 on 2021-04-13
 **/
@EnableMetrics
public class Demo extends AbstractCommentExcelProcessor {
    @Override
    @Focus(id="com.tptj.demo.hg.comment.excel.processor.v10",text = "comment excel processor")
    public void addCellComment( Sheet sheet, ElementCase report, Drawing drawing ) {
        //先获取结果报表，只有填报允许导出下拉框数据字典选项
        Workbook workbook = sheet.getWorkbook();
        ExcelDictionaryBuilder builder = new ExcelDictionaryBuilder();
        ResultReport result = null;
        try{
            //匹配不预览导出的场景
            if( report instanceof PageExcelExporterReport ){
                result = Reflect.on(report).get("sourceReport");
            }else{
                result = ((PackedReport) report).getPackee();
            }
            ResultWorkBook resultWorkBook = result.getResultWorkBook();
            if( !(resultWorkBook instanceof WriteRWorkBook) ){
                return;
            }
            //遍历单元格，对所有下拉框数据字典进行相同合并操作
            Iterator iterator = report.cellIterator();

            while(iterator.hasNext()){
                Object cell = iterator.next();
                if( cell instanceof DefaultPageCellElement ){
                    continue;
                }
                CellElement oldCell = ((PageExportCellElement) cell).getOldCell();
                Widget wgt = ((AbstractWidgetCellElement)oldCell).getWidget();
                if( null == wgt ){
                    //适应混淆的代码
                    TemplateCellElement tcell = Reflect.on(oldCell).call("getBeFrom").call("get_ce_from").get();
                    wgt = tcell.getWidget();
                }
                if( null == wgt ){
                    continue;
                }
                if( !(wgt instanceof ComboBox) ){
                    continue;
                }
                builder.add(new ExcelDictionary((CalculatableResWorkSheet)result,
                        (DictContainedCustomWriteAbleEditor)wgt),(ResultCellElement)cell);
            }
        }catch(Exception e){
            LogKit.error("不支持分页分sheet导出！");
            return;
        }
        //在excel中生成具体的选项数据
        try {
            List<ExcelDictionary> build = builder.build();
            createBox((SXSSFWorkbook)workbook,sheet,build);
        } catch (Exception e) {
            LogKit.error(e.getMessage(),e);
        }
    }

    @Override
    public void convertCommentToTooltip( CellElement cellElement, Cell cell ) {
        FineLoggerFactory.getLogger().info("asd");
    }

    /**
     * 创建一个隐藏的sheet来保存数据字典的数据
     * @param wb
     * @param sheet
     * @param list
     */
    private void createBox(SXSSFWorkbook wb, Sheet sheet, List<ExcelDictionary> list) {
        int sheetCount = wb.getNumberOfSheets();
        String sheetName = "hidden1949"+sheetCount;
        SXSSFSheet nsheet = wb.createSheet(sheetName);
        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(null);
        int maxRow = 0;
        for( ExcelDictionary item : list ){
            if(item.getData().length > maxRow ){
                maxRow = item.getData().length;
            }
        }
        //把所有的选项填充到sheet中
        for( int ridx =0;ridx<maxRow; ridx++ ){
            SXSSFRow row = nsheet.createRow(ridx);
            for( int col=0;col <list.size();col++ ){
                ExcelDictionary item = list.get(col);
                String[] data = item.getData();
                if( data.length > ridx ){
                    SXSSFCell cell = row.createCell(col);
                    cell.setCellValue(data[ridx]);
                }
            }
        }
        for( int col=0;col <list.size();col++ ){
            ExcelDictionary item = list.get(col);
            String[] data = item.getData();
            if(0 == data.length){
                continue;
            }
            Name namedCell = wb.createName();
            String formulaId = "s"+ UUID.randomUUID().toString().replace("-", "");
            namedCell.setNameName(formulaId);
            String cTag = ColumnRow.valueOf(col,0).toString().replaceAll("1","");
            namedCell.setRefersToFormula(sheetName+"!$"+cTag+"$1:$"+cTag+"$" + data.length);
            DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint(formulaId);
            DataValidation dataValidation = dvHelper.createValidation(dvConstraint,item.getCells());
            sheet.addValidationData(dataValidation);
        }
        wb.setSheetHidden(sheetCount, true);
    }
}