package pwc.taxtech.atms.common;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.formula.eval.ValueEval;
import org.apache.poi.ss.formula.functions.FreeRefFunction;
import org.apache.poi.ss.formula.udf.AggregatingUDFFinder;
import org.apache.poi.ss.formula.udf.DefaultUDFFinder;
import org.apache.poi.ss.formula.udf.UDFFinder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellReference;

import java.io.*;

public class POITest {
    public static void main(String[] args) {

        File workbookFile = new File("C:\\source\\test - Copy.xlsx");

        try {
            FileInputStream fis = new FileInputStream(workbookFile);
            Workbook workbook = WorkbookFactory.create(fis);

            String[] functionNames = {"TmpFunction"};
            FreeRefFunction[] functionImpls = {new TmpFunction()};

            UDFFinder udfs = new DefaultUDFFinder(functionNames, functionImpls);
            UDFFinder udfToolpack = new AggregatingUDFFinder(udfs);

            workbook.addToolPack(udfToolpack);

            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();


            int sheetNum = workbook.getNumberOfSheets();
            Sheet st1 = workbook.getSheetAt(0);
            st1.getRow(1).getCell(0).setCellType(CellType.NUMERIC);
            st1.getRow(1).getCell(0).setCellValue(15);
            evaluator.evaluateAll();
            for (int i = 0; i < sheetNum; i++) {
                Sheet tmpSheet = workbook.getSheetAt(i);
                for (int r = tmpSheet.getFirstRowNum(); r <= tmpSheet.getLastRowNum(); r++) {
                    Row row = tmpSheet.getRow(r);
                    if (null == row) {
                        continue;
                    }
                    for (int c = row.getFirstCellNum(); c <= row.getLastCellNum(); c++) {
                        System.out.println("row :" + r + "  cell: " + c);
                        Cell tmp = row.getCell(c);
                        if (null != tmp && tmp.getCellTypeEnum().equals(CellType.FORMULA)) {
//                            CellValue v = evaluator.evaluate(tmp);
                            tmp.setCellType(CellType.NUMERIC);
                            tmp.setCellValue(tmp.getNumericCellValue());
                        }

                    }
                }

            }

            FileOutputStream excelFileOutPutStream = new FileOutputStream("C:\\source\\test - Copy.xlsx");

            workbook.write(excelFileOutPutStream);

            excelFileOutPutStream.flush();

            excelFileOutPutStream.close();


        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


}