package pwc.taxtech.atms.common.util;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
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.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import pwc.taxtech.atms.constant.VATCommon;
import pwc.taxtech.atms.vat.dao.DataImportModel;

import java.io.FileInputStream;
import java.io.IOException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import static org.apache.poi.ss.usermodel.CellType.NUMERIC;
import static pwc.taxtech.atms.constant.Constant.DEFAULT_RESULT;

public class NPOIHelper {
    public static DataImportModel renderTBDataTableFromExcel(FileInputStream fis, Integer selectedSheetIndex,
                                                             int headerRowIndex, boolean haveHeader, Integer topRowNumber) {

        DataImportModel dataImportModel = new DataImportModel();
        Workbook workbook = null;
        try {
            workbook = WorkbookFactory.create(fis);
            Sheet sheet = workbook.getSheetAt(selectedSheetIndex);
            dataImportModel.setDataList(new ArrayList<>());
            dataImportModel.setSheetNameList(new ArrayList<>());
            dataImportModel.setResult(DEFAULT_RESULT);

            try {
                //TODO:should be replace by biz exception (neo)
                if (!(workbook instanceof XSSFWorkbook)) throw new RuntimeException("not support workbook type");
                XSSFFormulaEvaluator eva = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
                int numberOfSheets = workbook.getNumberOfSheets();
                for (int index = 0; index < numberOfSheets; index++) {
                    dataImportModel.getSheetNameList().add(workbook.getSheetAt(index).getSheetName());
                }
                //判断文件是否为空
                if (sheet.getPhysicalNumberOfRows() == 0) {
                    dataImportModel.setResult(false);
                    dataImportModel.setResultMsg(VATCommon.Const_ImportFileError);
                } else {
                    //var headerRow = sheet.GetRow(HeaderRowIndex);
                    int cellCount = 0;

                    for (int i = 0; i <= sheet.getLastRowNum(); i++) {
                        Row row = sheet.getRow(i);
                        if (row != null && row.getPhysicalNumberOfCells() > cellCount) {
                            cellCount = row.getPhysicalNumberOfCells();
                        }
                    }

                    List<String> rowList = new ArrayList<>();


                    //dataImportModel.dataList.Add(rowList);
                    dataImportModel.setLastRowIndex(sheet.getLastRowNum());
                    int RowStart = sheet.getFirstRowNum();
                    for (int i = RowStart; i <= sheet.getLastRowNum(); i++) {
                        Row row = sheet.getRow(i);
                        if (row != null) {
                            rowList = getOutputInvoiceRowValueList(eva, row, cellCount);
                            dataImportModel.getDataList().add(rowList);
                        }
                    }
                }
            } catch (Exception ex) {
                dataImportModel.setResult(false);
                dataImportModel.setResultMsg(VATCommon.Const_ImportFileError);
            }

            if (topRowNumber > 0) {
                //第一行为列头的话就多输出一行
                int topNum = haveHeader ? topRowNumber + 1 : topRowNumber;

                if (topNum < dataImportModel.getDataList().size())
                    dataImportModel.setDataList(dataImportModel.getDataList().subList(0, topNum));
//                dataImportModel.setDataList(dataImportModel.getDataList().subList(0,topRowNumber));
            }
            return dataImportModel;

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

        return null;
    }

    private static List<String> getOutputInvoiceRowValueList(XSSFFormulaEvaluator eva, Row row, int cellCount) {
        List<String> rowList = new ArrayList<>();
        for (int j = 0; j < cellCount; j++) {
            if (row.getCell(j) != null) {
                rowList.add(getStringCellValue(eva, row.getCell(j)));
            } else {
                rowList.add(null);
            }
        }
        return rowList;
    }

    private static String getStringCellValue(XSSFFormulaEvaluator eva, Cell cell) {
        // 获取单元格数据内容为字符串类型的数据
        DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String strCell = "";
        switch (cell.getCellTypeEnum()) {
            case NUMERIC:
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    strCell = sdf.format(cell.getDateCellValue());
                } else {
                    long longVal = Math.round(cell.getNumericCellValue());
                    if (Double.parseDouble(longVal + ".0") == cell.getNumericCellValue())
                        strCell = longVal + "";
                    else
                        strCell = cell.getNumericCellValue() + "";
                }
                break;
            case FORMULA:
                try {
                    if (eva.evaluate(cell).getCellTypeEnum() == NUMERIC) {
                        strCell = eva.evaluate(cell).getNumberValue() + "";
                    } else {
                        strCell = eva.evaluate(cell).getStringValue();
                    }
                } catch (Exception ex) {
                    strCell = "#N/A";
                }
                break;
            default:
                strCell = cell.toString();
                break;
        }
        return strCell;
    }
}