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; } }