List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getSheetName
@Override
public String getSheetName()
From source file:FileHelper.ExcelHelper.java
public SheetsOfFile ReadSheetsOfFile(String fileName) { try {/* www. ja v a 2 s .c om*/ SheetsOfFile sheetsOfFile = new SheetsOfFile(); ArrayList<String> sheets = new ArrayList<String>(); File excel = new File(fileName); FileInputStream fis = new FileInputStream(excel); XSSFWorkbook book = new XSSFWorkbook(fis); int size = book.getNumberOfSheets(); if (size > 1) { for (int i = 0; i < size; i++) { XSSFSheet sheet = book.getSheetAt(i); String str = sheet.getSheetName(); sheets.add(str); } } sheetsOfFile.setSheets(sheets); return sheetsOfFile; } catch (Throwable t) { System.out.println("Throwsable: " + t.getMessage()); return new SheetsOfFile(); } }
From source file:FormatConvert.exceloperation.Excel2csv.java
public static void SeperatorExcel2sheet(String excelfile, String targetdir) { try {/*from w w w .j av a2 s. co m*/ FileInputStream is = (new FileInputStream(excelfile)); if (excelfile.endsWith(".xlsx")) { XSSFWorkbook wb = new XSSFWorkbook(is); int sheetnub = wb.getNumberOfSheets(); for (int i = 0; i < sheetnub; i++) { XSSFSheet sheet = wb.getSheetAt(i); if (sheet.toString() != null) { String temfile = targetdir + "\\" + sheet.getSheetName() + ".csv"; Excel2csv.copySheets2CSV(sheet, temfile); } } } else { HSSFWorkbook wb = new HSSFWorkbook(is); int sheetnub = wb.getNumberOfSheets(); for (int i = 0; i < sheetnub; i++) { HSSFSheet sheet = wb.getSheetAt(i); if (sheet.toString() != null) { String temfile = targetdir + "\\" + sheet.getSheetName() + ".csv"; Excel2csv.copySheets2CSV(sheet, temfile); } } } } catch (Exception ioe) { ioe.printStackTrace(); } }
From source file:FormatConvert.exceloperation.ExcelOperation.java
public static void SeperatorExcel2sheet(String excelfile, String targetdir) { try {//from w w w.java 2 s. c o m FileInputStream is = (new FileInputStream(excelfile)); if (excelfile.endsWith(".xlsx")) { XSSFWorkbook wb = new XSSFWorkbook(is); int sheetnub = wb.getNumberOfSheets(); for (int i = 0; i < sheetnub; i++) { XSSFSheet sheet = wb.getSheetAt(i); if (sheet.toString() != null) { Workbook wb2 = new XSSFWorkbook(); XSSFSheet tempsheet = (XSSFSheet) wb2.createSheet(); Util.copySheets(tempsheet, sheet, true); //tempsheet=wb.cloneSheet(i); //tempsheet = sheet; String temfile = targetdir + "\\" + sheet.getSheetName() + ".xlsx"; FileOutputStream fileOut = new FileOutputStream(temfile); wb2.write(fileOut); fileOut.close(); } } } else { HSSFWorkbook wb = new HSSFWorkbook(is); int sheetnub = wb.getNumberOfSheets(); for (int i = 0; i < sheetnub; i++) { HSSFSheet sheet = wb.getSheetAt(i); if (sheet.toString() != null) { Workbook wb2 = new HSSFWorkbook(); HSSFSheet tempsheet = (HSSFSheet) wb2.createSheet(); Util.copySheets(tempsheet, sheet, true); //tempsheet=wb.cloneSheet(i); //tempsheet = sheet; String temfile = targetdir + "\\" + sheet.getSheetName() + ".xlsx"; FileOutputStream fileOut = new FileOutputStream(temfile); wb2.write(fileOut); fileOut.close(); } } } } catch (Exception ioe) { ioe.printStackTrace(); } }
From source file:gov.anl.cue.arcane.engine.matrix.MatrixModel.java
License:Open Source License
/** * Import template scan variables.//from w w w.j a v a 2s . co m * * @param matrixModel the matrix model * @param nodeCounts the node counts * @param nodeBases the node bases * @param nodeRequests the node requests * @param nodeCount the node count * @param sheets the sheets * @throws NotStrictlyPositiveException the not strictly positive exception */ public static void importTemplateScanVariables(MatrixModel matrixModel, HashMap<Integer, Integer> nodeCounts, HashMap<Integer, Integer> nodeBases, int nodeRequests, int nodeCount, Iterator<XSSFSheet> sheets) throws NotStrictlyPositiveException { // Scan the variables. XSSFSheet sheet; while (sheets.hasNext()) { // Move to the next sheet. sheet = sheets.next(); // Allocate a new variable. MatrixVariable matrixVariable = new MatrixVariable(); // Assign the new variables's name and, possibly, units. String newName = sheet.getSheetName(); if (newName.contains("(")) { // Parse the name and units. matrixVariable.name = newName.substring(0, newName.indexOf("(") - 1); String unitsString = newName.substring(newName.indexOf("(") + 1, newName.indexOf(")")); // Attempt to convert the units text to a units value. try { // Convert the units text to a units value. matrixVariable.units = Amount.valueOf("1.0 " + unitsString); // Catch errors. } catch (Exception e) { // Use a default value. matrixVariable.units = Amount.ONE; } } else { // Assign the name. matrixVariable.name = newName; } // Scan the sheet for the next variable. for (int rowIndex = 0; rowIndex < nodeRequests; rowIndex++) { // Get the next equation. String equation = Util.getSpreadsheetString(sheet, rowIndex + 1, 0); // Store the equation. for (int count = 0; count < nodeCounts.get(rowIndex); count++) matrixVariable.equations.add(equation); // Check to make sure that the matrix is allocated. if (matrixVariable.coefficients == null) { // Allocate the coefficient matrix. matrixVariable.coefficients = new Array2DRowRealMatrix(nodeCount, nodeCount); } // Scan the columns for the next coefficient. for (int columnIndex = 0; columnIndex < nodeRequests; columnIndex++) { // Get the next coefficient. double fullValue = Util.getSpreadsheetNumber(sheet, rowIndex + 1, columnIndex + 2); // Store the next coefficients. MatrixModel.setTemplateEntry(nodeCounts, nodeBases, matrixVariable, rowIndex, columnIndex, fullValue); } } // Store the new variable. matrixModel.add(matrixVariable); } }
From source file:gov.anl.cue.arcane.engine.matrix.MatrixModel.java
License:Open Source License
/** * Read scan variables./*from w ww . j ava2 s .co m*/ * * @param matrixModel the matrix model * @param matrixDimensions the matrix dimensions * @param sheets the sheets * @throws NotStrictlyPositiveException the not strictly positive exception * @throws OutOfRangeException the out of range exception */ public static void readScanVariables(MatrixModel matrixModel, MatrixModel.MatrixDimensions matrixDimensions, Iterator<XSSFSheet> sheets) throws NotStrictlyPositiveException, OutOfRangeException { // Scan the sheets. while (sheets.hasNext()) { // Move to the next sheet. XSSFSheet sheet = sheets.next(); // Allocate a new variable. MatrixVariable matrixVariable = new MatrixVariable(); // Assign the new variables's name and, possibly, units. String newName = sheet.getSheetName(); if (newName.contains("(")) { // Parse the name and units. matrixVariable.name = newName.substring(0, newName.indexOf("(") - 1); String unitsString = newName.substring(newName.indexOf("(") + 1, newName.indexOf(")")); // Attempt to convert the units text to a units value. try { // Convert the units text to a units value. matrixVariable.units = Amount.valueOf("1.0 " + unitsString); // Catch errors. } catch (Exception e) { // Use a default value. matrixVariable.units = Amount.ONE; } } else { // Assign the name. matrixVariable.name = newName; } // Scan the sheet for the next variable. for (int rowIndex = 0; rowIndex < matrixDimensions.rows; rowIndex++) { // Get the next equation. String equation = Util.getSpreadsheetString(sheet, rowIndex + 1, 0); // Store the equation. matrixVariable.equations.add(equation); // Check to make sure that the matrix is allocated. if (matrixVariable.coefficients == null) { matrixVariable.coefficients = new Array2DRowRealMatrix(matrixDimensions.rows, matrixDimensions.columns); } // Scan the columns for the next coefficient. for (int columnIndex = 0; columnIndex < matrixDimensions.columns; columnIndex++) { // Get the next coefficient. double value = Util.getSpreadsheetNumber(sheet, rowIndex + 1, columnIndex + 2); // Store the next coefficient. matrixVariable.coefficients.setEntry(rowIndex, columnIndex, value); } } // Store the new variable. matrixModel.add(matrixVariable); } }
From source file:in.expertsoftware.colorcheck.VerifyTokens.java
/** * Verify_tokens function takes two parameters first is number of sheets present in workbook and second is an instance of workbook. * it process each sheet individually and verifying the tokens position as well check tokens are present or not and error is handled by error model. * @param NumberOfSheets Number of Sheet present In this workbook * @param workbook an instance of XSSFWorkbook * @return ArrayList of type Error and Model * /* ww w . j a va2 s . c om*/ */ public ArrayList verify_tokens(int NumberOfSheets, XSSFWorkbook workbook) { ArrayList<ErrorModel> errorModelList = new ArrayList<ErrorModel>(); ArrayList<TokenModel> tokenModelList = new ArrayList<TokenModel>(); ArrayList<String> metadataCount = new ArrayList<String>(); ArrayList errorAndTokenList = new ArrayList(); for (int i = 0; i < NumberOfSheets; i++) { XSSFSheet Sheet = workbook.getSheetAt(i); if (containSheetName(Sheet.getSheetName())) { Iterator<Row> rowIterator = Sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.iterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getColumnIndex() == 0) { String key = cell.getStringCellValue(); switch (Sheet.getSheetName()) { case "BasicInfo": if (!(cell.getCellType() == Cell.CELL_TYPE_BLANK)) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Vulnerable Token present at"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } break; case "User_Financial_Input": switch (key.trim()) { case "SOUFI": TokenModel tknmodel = new TokenModel(); if (metadataCount.contains("SOUFI")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("SOUFI"); } break; case "EOUFI": tknmodel = new TokenModel(); if (metadataCount.contains("EOUFI")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("EOUFI"); } break; case "UFCS1": tknmodel = new TokenModel(); if (metadataCount.contains("UFCS1")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("UFCS1"); } break; case "UFCS2": tknmodel = new TokenModel(); if (metadataCount.contains("UFCS2")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("UFCS2"); } break; } break; case "User_Operation_Input": switch (key.trim()) { case "SOUOI": TokenModel tknmodel = new TokenModel(); if (metadataCount.contains("SOUOI")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("SOUOI"); } break; case "EOUOI": tknmodel = new TokenModel(); if (metadataCount.contains("EOUOI")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("EOUOI"); } break; } break; case "Unit_Map": break; case "Operation_Standard": switch (key.trim()) { case "SOOI": TokenModel tknmodel = new TokenModel(); if (metadataCount.contains("SOOI")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("SOOI"); } break; case "EOOI": tknmodel = new TokenModel(); if (metadataCount.contains("EOOI")) { ErrorModel errmodel = new ErrorModel(); CellReference cellRef = new CellReference(cell); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef1 = new CellReference(cell); errmodel.setCell_ref(cellRef1.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("EOOI"); } break; case "SOOCS": tknmodel = new TokenModel(); if (metadataCount.contains("SOOCS")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("SOOCS"); } break; case "EOOCS": tknmodel = new TokenModel(); if (metadataCount.contains("EOOCS")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("EOOCS"); } break; case "SOOWD": tknmodel = new TokenModel(); if (metadataCount.contains("SOOWD")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("SOOWD"); } break; case "EOOWD": tknmodel = new TokenModel(); if (metadataCount.contains("EOOWD")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("EOOWD"); } break; } break; case "Financial_Standard": switch (key.trim()) { case "SOFI": TokenModel tknmodel = new TokenModel(); if (metadataCount.contains("SOFI")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("SOFI"); } break; case "EOFI": tknmodel = new TokenModel(); if (metadataCount.contains("EOFI")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("EOFI"); } break; case "FCS1": tknmodel = new TokenModel(); if (metadataCount.contains("FCS1")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("FCS1"); } break; case "FCS2": tknmodel = new TokenModel(); if (metadataCount.contains("FCS2")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("FCS2"); } break; case "SOFWD": tknmodel = new TokenModel(); if (metadataCount.contains("SOFWD")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("SOFWD"); } break; case "EOFWD": tknmodel = new TokenModel(); if (metadataCount.contains("EOFWD")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("EOFWD"); } break; case "SOFCP": tknmodel = new TokenModel(); if (metadataCount.contains("SOFCP")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("SOFCP"); } break; case "EOFCP": tknmodel = new TokenModel(); if (metadataCount.contains("EOFCP")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("EOFCP"); } break; } break; case "CrossCheck": switch (key.trim()) { case "SOCCK": TokenModel tknmodel = new TokenModel(); if (metadataCount.contains("SOCCK")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("SOCCK"); } break; case "EOCCK": tknmodel = new TokenModel(); if (metadataCount.contains("EOCCK")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("EOCCK"); } break; } break; case "Reporting_Qtr": switch (key.trim()) { case "SORQ": TokenModel tknmodel = new TokenModel(); if (metadataCount.contains("SORQ")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("SORQ"); } break; case "EORQ": tknmodel = new TokenModel(); if (metadataCount.contains("EORQ")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("EORQ"); } break; } break; case "Reporting_Year": switch (key.trim()) { case "SORY": TokenModel tknmodel = new TokenModel(); if (metadataCount.contains("SORY")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("SORY"); } break; case "EORY": tknmodel = new TokenModel(); if (metadataCount.contains("EORY")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("EORY"); } break; } break; case "Chart_Qtr": switch (key.trim()) { case "SOOCQ": TokenModel tknmodel = new TokenModel(); if (metadataCount.contains("SOOCQ")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("SOOCQ"); } break; case "EOOCQ": tknmodel = new TokenModel(); if (metadataCount.contains("EOOCQ")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("EOOCQ"); } break; case "SOFCQ": tknmodel = new TokenModel(); if (metadataCount.contains("SOFCQ")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("SOFCQ"); } break; case "EOFCQ": tknmodel = new TokenModel(); if (metadataCount.contains("EOFCQ")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("EOFCQ"); } break; } break; case "Chart_Year": switch (key.trim()) { case "SOOCY": TokenModel tknmodel = new TokenModel(); if (metadataCount.contains("SOOCY")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("SOOCY"); } break; case "EOOCY": tknmodel = new TokenModel(); if (metadataCount.contains("EOOCY")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("EOOCY"); } break; case "SOFCY": tknmodel = new TokenModel(); if (metadataCount.contains("SOFCY")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("SOFCY"); } break; case "EOFCY": tknmodel = new TokenModel(); if (metadataCount.contains("EOFCY")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("EOFCY"); } break; } break; case "Summary": switch (key.trim()) { case "SOS": TokenModel tknmodel = new TokenModel(); if (metadataCount.contains("SOS")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("SOS"); } break; case "EOS": tknmodel = new TokenModel(); if (metadataCount.contains("EOS")) { ErrorModel errmodel = new ErrorModel(); errmodel.setSheet_name(Sheet.getSheetName()); CellReference cellRef = new CellReference(cell); errmodel.setCell_ref(cellRef.formatAsString()); errmodel.setRow(cell.getRowIndex() + 1); errmodel.setCol(cell.getColumnIndex() + 1); errmodel.setError_desc("Token Present More Than One Time"); errmodel.setError_level("Error"); errorModelList.add(errmodel); } else { tknmodel.setSheet_name(Sheet.getSheetName()); tknmodel.setToken_name(key.trim()); tknmodel.setRow_no((cell.getRowIndex() + 1)); tokenModelList.add(tknmodel); metadataCount.add("EOS"); } break; } break; case "MetaDataSheet": break; } } } } } else { System.out.println("workbook have another sheet"); ErrorModel errmodel = new ErrorModel(); errmodel.setError_desc("This is an extra sheet in this workbook"); errmodel.setSheet_name(Sheet.getSheetName()); //errmodel.setRow(-2); errmodel.setError_level("Warning"); errorModelList.add(errmodel); } } //for checking every tocken is present or not; errorModelList.addAll(verifyAllTokenPresent(metadataCount)); errorAndTokenList.add(errorModelList); errorAndTokenList.add(tokenModelList); return errorAndTokenList; }
From source file:mil.tatrc.physiology.utilities.Excel2PDF.java
License:Apache License
public static void convert(String from, String to) throws IOException { FileInputStream xlFile = new FileInputStream(new File(from)); // Read workbook into HSSFWorkbook XSSFWorkbook xlWBook = new XSSFWorkbook(xlFile); //We will create output PDF document objects at this point PDDocument pdf = new PDDocument(); //pdf.addTitle(); for (int s = 0; s < xlWBook.getNumberOfSheets(); s++) { XSSFSheet xlSheet = xlWBook.getSheetAt(s); Log.info("Processing Sheet : " + xlSheet.getSheetName()); PDPage page = new PDPage(PDRectangle.A4); page.setRotation(90);// ww w.j a v a 2s.co m pdf.addPage(page); PDRectangle pageSize = page.getMediaBox(); PDPageContentStream contents = new PDPageContentStream(pdf, page); contents.transform(new Matrix(0, 1, -1, 0, pageSize.getWidth(), 0));// including a translation of pageWidth to use the lower left corner as 0,0 reference contents.setFont(PDType1Font.HELVETICA_BOLD, 16); contents.beginText(); contents.newLineAtOffset(50, pageSize.getWidth() - 50); contents.showText(xlSheet.getSheetName()); contents.endText(); contents.close(); int rows = xlSheet.getPhysicalNumberOfRows(); for (int r = 0; r < rows; r++) { XSSFRow row = xlSheet.getRow(r); if (row == null) continue; int cells = row.getPhysicalNumberOfCells(); if (cells == 0) continue;// Add an empty Roe } } /* //We will use the object below to dynamically add new data to the table PdfPCell table_cell; //Loop through rows. while(rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); while(cellIterator.hasNext()) { Cell cell = cellIterator.next(); //Fetch CELL switch(cell.getCellType()) { //Identify CELL type //you need to add more code here based on //your requirement / transformations case Cell.CELL_TYPE_STRING: //Push the data from Excel to PDF Cell table_cell=new PdfPCell(new Phrase(cell.getStringCellValue())); //feel free to move the code below to suit to your needs my_table.addCell(table_cell); break; } //next line } } */ pdf.save(new File(to)); pdf.close(); xlWBook.close(); xlFile.close(); //close xls }
From source file:mil.tatrc.physiology.utilities.testing.validation.ValdiationTool.java
License:Apache License
public void loadData(String revision, String env, String arch, boolean sendEmail) { String directoryName = DEFAULT_DIRECTORY; String fileName = DEFAULT_FILE; String destinationDirectory = DEST_DIRECTORY; try {// ww w . j a va 2 s.c o m File dest = new File(DEST_DIRECTORY); dest.mkdir(); // Delete current dir contents // FileUtils.delete(destinationDirectory); // Ok, let's make them again // FileUtils.createDirectory(destinationDirectory); } catch (Exception ex) { Log.error("Unable to clean directories"); return; } try { File xls = new File(directoryName + "/" + fileName); if (!xls.exists()) { Log.error("Could not find xls file " + directoryName + "/" + fileName); return; } // Read in props file File file = new File("ValidationTables.config"); FileInputStream fileInput = new FileInputStream(file); Properties config = new Properties(); config.load(fileInput); fileInput.close(); // Set up the Email object String hostname = "Unknown"; try { InetAddress addr = InetAddress.getLocalHost(); hostname = addr.getHostName(); } catch (Exception ex) { System.out.println("Hostname can not be resolved"); } EmailUtil email = new EmailUtil(); String subj = env + " " + arch + " " + TABLE_TYPE + " Validation from " + hostname + " Revision " + revision; email.setSubject(subj); email.setSender(config.getProperty("sender")); email.setSMTP(config.getProperty("smtp")); if (hostname.equals(config.get("buildhost"))) { Log.info("Emailling all recipients " + subj); for (String recipient : config.getProperty("recipients").split(",")) email.addRecipient(recipient.trim()); } else {// Running on your own machine, just send it to yourself Log.info("Emailling local runner " + subj); email.addRecipient(System.getProperty("user.name") + "@ara.com"); } html.append("<html>"); html.append("<body>"); // Get a list of all the results files we have to work with File vdir = new File("./Scenarios/Validation/"); String[] vFiles = vdir.list(); // Now read in the spreadsheet FileInputStream xlFile = new FileInputStream(directoryName + "/" + fileName); XSSFWorkbook xlWBook = new XSSFWorkbook(xlFile); FormulaEvaluator evaluator = xlWBook.getCreationHelper().createFormulaEvaluator(); List<ValidationRow> badSheets = new ArrayList<ValidationRow>(); Map<String, List<ValidationRow>> tables = new HashMap<String, List<ValidationRow>>(); Map<String, List<ValidationRow>> tableErrors = new HashMap<String, List<ValidationRow>>(); List<ValidationRow> allRows = new ArrayList<ValidationRow>(); for (int i = 0; i < xlWBook.getNumberOfSheets(); i++) { XSSFSheet xlSheet = xlWBook.getSheetAt(i); Log.info("Processing Sheet : " + xlSheet.getSheetName()); String sheetName = xlSheet.getSheetName().trim().replaceAll(" ", ""); List<String> sheetFiles = new ArrayList<String>(); String rSheetName = sheetName + "ValidationResults.txt"; File rFile = new File(rSheetName); if (!rFile.exists()) { // Search for any file starting with the sheet name for (String f : vFiles) if (f.startsWith(sheetName) && f.endsWith(".txt")) sheetFiles.add(f); } else sheetFiles.add(rSheetName); for (String resultsName : sheetFiles) { Log.info("Processing " + resultsName); try { // Look for a results file CSVContents results = new CSVContents("./Scenarios/Validation/" + resultsName); results.readAll(resultData); // Find any assessments assessments = new HashMap<String, SEPatientAssessment>(); for (String vFile : vFiles) { if (vFile.indexOf(sheetName) > -1 && vFile.indexOf('@') > -1) { Object aData = CDMSerializer.readFile("./Scenarios/Validation/" + vFile); if (aData instanceof PatientAssessmentData) { String aClassName = "SE" + aData.getClass().getSimpleName(); aClassName = aClassName.substring(0, aClassName.indexOf("Data")); try { Class<?> aClass = Class.forName( "mil.tatrc.physiology.datamodel.patient.assessments." + aClassName); SEPatientAssessment a = (SEPatientAssessment) aClass.newInstance(); aClass.getMethod("load", aData.getClass()).invoke(a, aData); assessments.put(vFile, a); } catch (Exception ex) { Log.error("Unable to load assesment xml " + vFile, ex); } } else Log.error(vFile + " is named like a patient assessment, but its not?"); } } } catch (Exception ex) { ValidationRow vRow = new ValidationRow(); vRow.header = sheetName; vRow.error = danger + "No results found for sheet " + endSpan; badSheets.add(vRow); continue; } // Is this patient validation? patient = null; if (TABLE_TYPE.equals("Patient")) { // Patient Name is encoded in the naming convention (or else it needs to be) String patientName = resultsName.substring(resultsName.lastIndexOf("-") + 1, resultsName.indexOf("Results")); patient = new SEPatient(); patient.load((PatientData) CDMSerializer.readFile("./stable/" + patientName + ".xml")); } allRows.clear(); tables.clear(); tableErrors.clear(); // Read the sheet and process all the validation data rows try { int rows = xlSheet.getPhysicalNumberOfRows(); for (int r = 0; r < rows; r++) { XSSFRow row = xlSheet.getRow(r); if (row == null) continue; int cells = 11;//row.getPhysicalNumberOfCells(); XSSFCell cell = row.getCell(0); if (cell == null) continue; // Check to see if this row is a header String cellValue = cell.getStringCellValue(); if (cellValue == null || cellValue.isEmpty()) continue;// No property, skip it cellValue = row.getCell(1).getStringCellValue(); if (cellValue != null && cellValue.equals("Units")) continue;// Header ValidationRow vRow = new ValidationRow(); allRows.add(vRow); for (int c = 0; c <= cells; c++) { cellValue = null; cell = row.getCell(c); if (cell == null) continue; switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: cellValue = Double.toString(cell.getNumericCellValue()); break; case XSSFCell.CELL_TYPE_STRING: cellValue = cell.getStringCellValue(); break; case XSSFCell.CELL_TYPE_FORMULA: switch (evaluator.evaluateFormulaCell(cell)) { case XSSFCell.CELL_TYPE_NUMERIC: cellValue = String.format("%." + 3 + "g", cell.getNumericCellValue()); break; case XSSFCell.CELL_TYPE_STRING: cellValue = cell.getStringCellValue(); break; } } switch (c) { case 0://A Log.info("Processing " + cellValue); vRow.name = cellValue.trim().replaceAll(" ", ""); String prop = vRow.name; if (vRow.name.indexOf('*') != -1) prop = prop.substring(0, prop.length() - 1); vRow.header = vRow.name; break; case 1://B if (cellValue != null && !cellValue.equalsIgnoreCase("none") && !cellValue.equalsIgnoreCase("n\\a") && !cellValue.equalsIgnoreCase("n/a")) { vRow.unit = cellValue; } if (vRow.unit != null && !vRow.unit.isEmpty()) vRow.header += "(" + vRow.unit + ")"; break; case 2://C if (cellValue != null) { String unit = null; int u = cellValue.indexOf("("); if (u > -1) { unit = cellValue.substring(u + 1, cellValue.indexOf(")")); cellValue = cellValue.substring(0, u); } vRow.dType = DataType.valueOf(cellValue); if (vRow.dType == DataType.MeanPerWeight || vRow.dType == DataType.WaveformMinPerWeight || vRow.dType == DataType.WaveformMaxPerWeight) { vRow.weightUnit = unit; } } break; case 3://D // Replace any return characters with empty if (patient != null && vRow.name.indexOf('*') == -1) { try { Method has = SEPatient.class.getMethod("has" + vRow.name); if ((Boolean) has.invoke(patient)) { Method get = SEPatient.class.getMethod("get" + vRow.name); SEScalar s = ((SEScalar) get.invoke(patient)); vRow.refValue = s.getValue(vRow.unit); vRow.refValues = cellValue; break; } else { Log.error("Patient does not have a value for " + vRow.name); } } catch (Exception ex) { // Nothing to do, row is not a patient property } } if (cellValue == null) vRow.refValues = null; else vRow.refValues = cellValue.replace("\n", ""); break; case 4://E // Replace any return characters with empty if (cellValue != null) cellValue = cellValue.replace("\n", ""); vRow.refCites = cellValue; break; case 5://F Reference Page (Internal only) break; case 6://G Notes if (cellValue != null) vRow.notes = cellValue; break;// Skipping for now case 7://H Internal Notes (Internal only) break; case 8://I Reading (Internal only) break; case 9://J Table (Internal only) if (cellValue == null) cellValue = ""; vRow.table = cellValue; if (patient != null) vRow.table = patient.getName() + "Patient" + cellValue; break; case 10://K ResultFile (Internal only) if (cellValue != null) vRow.resultFile = cellValue; break; case 11://L Mantissa Digits if (cellValue != null) vRow.doubleFormat = cellValue; if (patient != null && vRow.dType != DataType.Patient2SystemMean) vRow.refValues = String.format("%." + vRow.doubleFormat, vRow.refValue); break; } } } } catch (Exception ex) { Log.error("Error reading row", ex); ValidationRow vRow = new ValidationRow(); vRow.header = sheetName; vRow.error = danger + "Sheet has errors" + endSpan; badSheets.add(vRow); continue; } // Sort all of our rows, and validate them for (ValidationRow vRow : allRows) { if (vRow.table.isEmpty()) vRow.table = sheetName;//Default table is the sheet name if (!tables.containsKey(vRow.table)) tables.put(vRow.table, new ArrayList<ValidationRow>()); if (!tableErrors.containsKey(vRow.table)) tableErrors.put(vRow.table, new ArrayList<ValidationRow>()); if (buildExpectedHeader(vRow)) { Log.info("Validating " + vRow.header); if (validate(vRow)) { tables.get(vRow.table).add(vRow); } else tableErrors.get(vRow.table).add(vRow); } else tableErrors.get(vRow.table).add(vRow); } for (String name : tables.keySet()) { if (name.contains("All")) continue; List<ValidationRow> t = tables.get(name); WriteHTML(t, name); WriteDoxyTable(t, name, destinationDirectory); if (name.equalsIgnoreCase(sheetName)) { List<String> properties = new ArrayList<String>(); for (ValidationRow vRow : t) properties.add(vRow.name); for (ValidationRow vRow : tableErrors.get(name)) properties.add(vRow.name); CrossCheckValidationWithSchema(properties, tableErrors.get(name), name); } WriteHTML(tableErrors.get(name), name + "Errors"); if (patient != null) CustomMarkdown(patient.getName(), destinationDirectory); } } } xlWBook.close(); WriteHTML(badSheets, fileName + " Errors"); html.append("</body>"); html.append("</html>"); if (sendEmail) email.sendHTML(html.toString()); } catch (Exception ex) { Log.error("Error processing spreadsheet " + fileName, ex); } // Just for fun, I am going to create a single md file with ALL the tables in it try { String line; File vDir = new File(destinationDirectory); PrintWriter writer = new PrintWriter(destinationDirectory + "/AllValidationTables.md", "UTF-8"); for (String fName : vDir.list()) { if (fName.equals("AllValidationTables.md")) continue; if (new File(fName).isDirectory()) continue; FileReader in = new FileReader(destinationDirectory + "/" + fName); BufferedReader inFile = new BufferedReader(in); writer.println(fName); while ((line = inFile.readLine()) != null) writer.println(line); inFile.close(); writer.println("<br>"); } writer.close(); } catch (Exception ex) { Log.error("Unable to create single validation table file.", ex); } }
From source file:mil.tatrc.physiology.utilities.testing.validation.ValidationMatrix.java
License:Apache License
public static void convert(String from, String to) throws IOException { FileInputStream xlFile = new FileInputStream(new File(from)); // Read workbook into HSSFWorkbook XSSFWorkbook xlWBook = new XSSFWorkbook(xlFile); List<SheetSummary> sheetSummaries = new ArrayList<SheetSummary>();// has to be an ordered list as sheet names can only be so long Map<String, String> refs = new HashMap<String, String>(); List<Sheet> Sheets = new ArrayList<Sheet>(); for (int s = 0; s < xlWBook.getNumberOfSheets(); s++) { XSSFSheet xlSheet = xlWBook.getSheetAt(s); Log.info("Processing Sheet : " + xlSheet.getSheetName()); if (xlSheet.getSheetName().equals("Summary")) { int rows = xlSheet.getPhysicalNumberOfRows(); for (int r = 1; r < rows; r++) { XSSFRow row = xlSheet.getRow(r); if (row == null) continue; SheetSummary ss = new SheetSummary(); sheetSummaries.add(ss);// w ww.j a va 2 s. c o m ss.name = row.getCell(0).getStringCellValue(); ss.description = row.getCell(1).getStringCellValue(); ss.validationType = row.getCell(2).getStringCellValue(); } } else if (xlSheet.getSheetName().equals("References")) { int rows = xlSheet.getPhysicalNumberOfRows(); for (int r = 1; r < rows; r++) { XSSFRow row = xlSheet.getRow(r); if (row == null) continue; refs.put("\\[" + r + "\\]", "@cite " + row.getCell(1).getStringCellValue()); } } else { int rows = xlSheet.getPhysicalNumberOfRows(); Sheet sheet = new Sheet(); sheet.summary = sheetSummaries.get(s - 2); Sheets.add(sheet); int cells = xlSheet.getRow(0).getPhysicalNumberOfCells(); for (int r = 0; r < rows; r++) { XSSFRow row = xlSheet.getRow(r); if (row == null) continue; String cellValue = null; for (int c = 0; c < cells; c++) { List<Cell> column; if (r == 0) { column = new ArrayList<Cell>(); sheet.table.add(column); } else { column = sheet.table.get(c); } XSSFCell cell = row.getCell(c); if (cell == null) { column.add(new Cell("", Agreement.NA, refs)); continue; } cellValue = null; switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: cellValue = Double.toString(cell.getNumericCellValue()); break; case XSSFCell.CELL_TYPE_STRING: cellValue = cell.getStringCellValue(); break; } if (cellValue == null || cellValue.isEmpty()) column.add(new Cell("", Agreement.NA, refs)); else { Agreement a = Agreement.NA; XSSFColor color = cell.getCellStyle().getFillForegroundColorColor(); if (color != null) { byte[] rgb = color.getRGB(); if (rgb[0] < -25 && rgb[1] > -25 && rgb[2] < -25) { a = Agreement.Good; sheet.summary.goodAgreement++; } else if (rgb[0] > -25 && rgb[1] > -25 && rgb[2] < -25) { a = Agreement.Ok; sheet.summary.okAgreement++; } else if (rgb[0] > -25 && rgb[1] < -25 && rgb[2] < -25) { a = Agreement.Bad; sheet.summary.badAgreement++; } } column.add(new Cell(cellValue, a, refs)); } } } } } xlWBook.close(); xlFile.close(); //close xls // Generate our Tables for each Sheet PrintWriter writer = null; try { String name = from.substring(from.lastIndexOf('/') + 1, from.lastIndexOf('.')) + "Scenarios"; writer = new PrintWriter(to + name + "Summary.md", "UTF-8"); writer.println( "|Scenario|Description|Validation Type|Good agreement|General agreement with deviations|Some major disagreements|"); writer.println("|--- |--- |:---: |:---: |:---: |:---: |"); for (Sheet sheet : Sheets) { writer.println("|" + sheet.summary.name + "|" + sheet.summary.description + "|" + sheet.summary.validationType + "|" + success + sheet.summary.goodAgreement + endSpan + "|" + warning + sheet.summary.okAgreement + endSpan + "|" + danger + sheet.summary.badAgreement + endSpan + "|"); } writer.close(); // Create file and start the table writer = new PrintWriter(to + name + ".md", "UTF-8"); writer.println(name + " {#" + name + "}"); writer.println("======="); writer.println(); writer.println(); for (Sheet sheet : Sheets) { Log.info("Writing table : " + sheet.summary.name); writer.println("## " + sheet.summary.name); writer.println(sheet.summary.description); writer.println("We used a " + sheet.summary.validationType + " validation method(s)."); writer.println(""); for (int row = 0; row < sheet.table.get(0).size(); row++) { for (int col = 0; col < sheet.table.size(); col++) { writer.print("|" + sheet.table.get(col).get(row).text); } writer.println("|"); if (row == 0) { for (int col = 0; col < sheet.table.size(); col++) { writer.print("|--- "); } writer.println("|"); } } writer.println(); writer.println(); } writer.close(); } catch (Exception ex) { Log.error("Error writing tables for " + from, ex); writer.close(); } }
From source file:mx.edu.um.mateo.activos.dao.impl.ActivoDaoHibernate.java
License:Open Source License
@Override @SuppressWarnings("unchecked") public void sube(byte[] datos, Usuario usuario, OutputStream out, Integer codigoInicial) { Date inicio = new Date(); int idx = 5;/*w w w . j a v a2 s . c o m*/ int i = 0; SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy"); SimpleDateFormat sdf2 = new SimpleDateFormat("dd/MM/yy"); SimpleDateFormat sdf3 = new SimpleDateFormat("dd-MM-yy"); MathContext mc = new MathContext(16, RoundingMode.HALF_UP); NumberFormat nf = NumberFormat.getInstance(); nf.setGroupingUsed(false); nf.setMaximumFractionDigits(0); nf.setMinimumIntegerDigits(5); Transaction tx = null; try { String ejercicioId = "001-2013"; Map<String, CentroCosto> centrosDeCosto = new HashMap<>(); Map<String, TipoActivo> tipos = new HashMap<>(); Query tipoActivoQuery = currentSession() .createQuery("select ta from TipoActivo ta " + "where ta.empresa.id = :empresaId " + "and ta.cuenta.id.ejercicio.id.idEjercicio = :ejercicioId " + "and ta.cuenta.id.ejercicio.id.organizacion.id = :organizacionId"); log.debug("empresaId: {}", usuario.getEmpresa().getId()); log.debug("ejercicioId: {}", ejercicioId); log.debug("organizacionId: {}", usuario.getEmpresa().getOrganizacion().getId()); tipoActivoQuery.setLong("empresaId", usuario.getEmpresa().getId()); tipoActivoQuery.setString("ejercicioId", ejercicioId); tipoActivoQuery.setLong("organizacionId", usuario.getEmpresa().getOrganizacion().getId()); List<TipoActivo> listaTipos = tipoActivoQuery.list(); for (TipoActivo tipoActivo : listaTipos) { tipos.put(tipoActivo.getCuenta().getId().getIdCtaMayor(), tipoActivo); } log.debug("TIPOS: {}", tipos); Query proveedorQuery = currentSession().createQuery( "select p from Proveedor p where p.empresa.id = :empresaId and p.nombre = :nombreEmpresa"); proveedorQuery.setLong("empresaId", usuario.getEmpresa().getId()); proveedorQuery.setString("nombreEmpresa", usuario.getEmpresa().getNombre()); Proveedor proveedor = (Proveedor) proveedorQuery.uniqueResult(); Query codigoDuplicadoQuery = currentSession() .createQuery("select a from Activo a where a.empresa.id = :empresaId and a.codigo = :codigo"); XSSFWorkbook workbook = new XSSFWorkbook(new ByteArrayInputStream(datos)); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet ccostoFantasma = wb.createSheet("CCOSTO-FANTASMAS"); int ccostoFantasmaRow = 0; XSSFSheet sinCCosto = wb.createSheet("SIN-CCOSTO"); int sinCCostoRow = 0; XSSFSheet codigoAsignado = wb.createSheet("CODIGO-ASIGNADO"); int codigoAsignadoRow = 0; XSSFSheet fechaInvalida = wb.createSheet("FECHA-INVALIDA"); int fechaInvalidaRow = 0; XSSFSheet sinCosto = wb.createSheet("SIN-COSTO"); int sinCostoRow = 0; //tx = currentSession().beginTransaction(); for (idx = 5; idx <= 5; idx++) { XSSFSheet sheet = workbook.getSheetAt(idx); int rows = sheet.getPhysicalNumberOfRows(); for (i = 2; i < rows; i++) { log.debug("Leyendo pagina {} renglon {}", idx, i); XSSFRow row = sheet.getRow(i); if (row.getCell(0) == null) { break; } String nombreGrupo = row.getCell(0).getStringCellValue().trim(); switch (row.getCell(0).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: nombreGrupo = row.getCell(0).toString().trim(); break; case XSSFCell.CELL_TYPE_STRING: nombreGrupo = row.getCell(0).getStringCellValue().trim(); break; } TipoActivo tipoActivo = tipos.get(nombreGrupo); if (tipoActivo != null) { String cuentaCCosto = row.getCell(2).toString().trim(); if (StringUtils.isNotBlank(cuentaCCosto)) { CentroCosto centroCosto = centrosDeCosto.get(cuentaCCosto); if (centroCosto == null) { Query ccostoQuery = currentSession().createQuery("select cc from CentroCosto cc " + "where cc.id.ejercicio.id.idEjercicio = :ejercicioId " + "and cc.id.ejercicio.id.organizacion.id = :organizacionId " + "and cc.id.idCosto like :idCosto"); ccostoQuery.setString("ejercicioId", ejercicioId); ccostoQuery.setLong("organizacionId", usuario.getEmpresa().getOrganizacion().getId()); ccostoQuery.setString("idCosto", "1.01." + cuentaCCosto); ccostoQuery.setMaxResults(1); List<CentroCosto> listaCCosto = ccostoQuery.list(); if (listaCCosto != null && listaCCosto.size() > 0) { centroCosto = listaCCosto.get(0); } if (centroCosto == null) { XSSFRow renglon = ccostoFantasma.createRow(ccostoFantasmaRow++); renglon.createCell(0).setCellValue(sheet.getSheetName() + ":" + (i + 1)); renglon.createCell(1).setCellValue(row.getCell(0).toString()); renglon.createCell(2).setCellValue(row.getCell(1).toString()); renglon.createCell(3).setCellValue(row.getCell(2).toString()); renglon.createCell(4).setCellValue(row.getCell(3).toString()); renglon.createCell(5).setCellValue(row.getCell(4).toString()); renglon.createCell(6).setCellValue(row.getCell(5).toString()); renglon.createCell(7).setCellValue(row.getCell(6).toString()); renglon.createCell(8).setCellValue(row.getCell(7).toString()); renglon.createCell(9).setCellValue(row.getCell(8).toString()); renglon.createCell(10).setCellValue(row.getCell(9).toString()); renglon.createCell(11).setCellValue(row.getCell(10).toString()); renglon.createCell(12).setCellValue(row.getCell(11).toString()); renglon.createCell(13).setCellValue(row.getCell(12).toString()); renglon.createCell(14).setCellValue(row.getCell(13).toString()); renglon.createCell(15).setCellValue(row.getCell(14).toString()); renglon.createCell(16).setCellValue(row.getCell(15).toString()); continue; } centrosDeCosto.put(cuentaCCosto, centroCosto); } String poliza = null; switch (row.getCell(4).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: poliza = row.getCell(4).toString(); poliza = StringUtils.removeEnd(poliza, ".0"); log.debug("POLIZA-N: {}", poliza); break; case XSSFCell.CELL_TYPE_STRING: poliza = row.getCell(4).getStringCellValue().trim(); log.debug("POLIZA-S: {}", poliza); break; } Boolean seguro = false; if (row.getCell(5) != null && StringUtils.isNotBlank(row.getCell(5).toString())) { seguro = true; } Boolean garantia = false; if (row.getCell(6) != null && StringUtils.isNotBlank(row.getCell(6).toString())) { garantia = true; } Date fechaCompra = null; if (row.getCell(7) != null) { log.debug("VALIDANDO FECHA"); XSSFCell cell = row.getCell(7); switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: log.debug("ES NUMERIC"); if (DateUtil.isCellDateFormatted(cell)) { log.debug("ES FECHA"); fechaCompra = cell.getDateCellValue(); } else if (DateUtil.isCellInternalDateFormatted(cell)) { log.debug("ES FECHA INTERNAL"); fechaCompra = cell.getDateCellValue(); } else { BigDecimal bd = new BigDecimal(cell.getNumericCellValue()); bd = stripTrailingZeros(bd); log.debug("CONVIRTIENDO DOUBLE {} - {}", DateUtil.isValidExcelDate(bd.doubleValue()), bd); fechaCompra = HSSFDateUtil.getJavaDate(bd.longValue(), true); log.debug("Cal: {}", fechaCompra); } break; case Cell.CELL_TYPE_FORMULA: log.debug("ES FORMULA"); CellValue cellValue = evaluator.evaluate(cell); switch (cellValue.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { fechaCompra = DateUtil.getJavaDate(cellValue.getNumberValue(), true); } } } } if (row.getCell(7) != null && fechaCompra == null) { String fechaCompraString; if (row.getCell(7).getCellType() == Cell.CELL_TYPE_STRING) { fechaCompraString = row.getCell(7).getStringCellValue(); } else { fechaCompraString = row.getCell(7).toString().trim(); } try { fechaCompra = sdf.parse(fechaCompraString); } catch (ParseException e) { try { fechaCompra = sdf2.parse(fechaCompraString); } catch (ParseException e2) { try { fechaCompra = sdf3.parse(fechaCompraString); } catch (ParseException e3) { // no se pudo convertir } } } } if (fechaCompra == null) { XSSFRow renglon = fechaInvalida.createRow(fechaInvalidaRow++); renglon.createCell(0).setCellValue(sheet.getSheetName() + ":" + (i + 1)); renglon.createCell(1).setCellValue(row.getCell(0).toString()); renglon.createCell(2).setCellValue(row.getCell(1).toString()); renglon.createCell(3).setCellValue(row.getCell(2).toString()); renglon.createCell(4).setCellValue(row.getCell(3).toString()); renglon.createCell(5).setCellValue(row.getCell(4).toString()); renglon.createCell(6).setCellValue(row.getCell(5).toString()); renglon.createCell(7).setCellValue(row.getCell(6).toString()); renglon.createCell(8).setCellValue(row.getCell(7).toString()); renglon.createCell(9).setCellValue(row.getCell(8).toString()); renglon.createCell(10).setCellValue(row.getCell(9).toString()); renglon.createCell(11).setCellValue(row.getCell(10).toString()); renglon.createCell(12).setCellValue(row.getCell(11).toString()); renglon.createCell(13).setCellValue(row.getCell(12).toString()); renglon.createCell(14).setCellValue(row.getCell(13).toString()); renglon.createCell(15).setCellValue(row.getCell(14).toString()); renglon.createCell(16).setCellValue(row.getCell(15).toString()); continue; } String codigo = null; switch (row.getCell(8).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: codigo = row.getCell(8).toString(); break; case XSSFCell.CELL_TYPE_STRING: codigo = row.getCell(8).getStringCellValue().trim(); break; } if (StringUtils.isBlank(codigo)) { codigo = "SIN CODIGO" + nf.format(codigoInicial); XSSFRow renglon = codigoAsignado.createRow(codigoAsignadoRow++); renglon.createCell(0).setCellValue(sheet.getSheetName() + ":" + (i + 1)); renglon.createCell(1).setCellValue(row.getCell(0).toString()); renglon.createCell(2).setCellValue(row.getCell(1).toString()); renglon.createCell(3).setCellValue(row.getCell(2).toString()); renglon.createCell(4).setCellValue(row.getCell(3).toString()); renglon.createCell(5).setCellValue(row.getCell(4).toString()); renglon.createCell(6).setCellValue(row.getCell(5).toString()); renglon.createCell(7).setCellValue(row.getCell(6).toString()); renglon.createCell(8).setCellValue(row.getCell(7).toString()); renglon.createCell(9).setCellValue("SIN CODIGO" + codigoInicial); renglon.createCell(10).setCellValue(row.getCell(9).toString()); renglon.createCell(11).setCellValue(row.getCell(10).toString()); renglon.createCell(12).setCellValue(row.getCell(11).toString()); renglon.createCell(13).setCellValue(row.getCell(12).toString()); renglon.createCell(14).setCellValue(row.getCell(13).toString()); renglon.createCell(15).setCellValue(row.getCell(14).toString()); renglon.createCell(16).setCellValue(row.getCell(15).toString()); codigoInicial++; } else { // busca codigo duplicado if (codigo.contains(".")) { codigo = codigo.substring(0, codigo.lastIndexOf(".")); log.debug("CODIGO: {}", codigo); } codigoDuplicadoQuery.setLong("empresaId", usuario.getEmpresa().getId()); codigoDuplicadoQuery.setString("codigo", codigo); Activo activo = (Activo) codigoDuplicadoQuery.uniqueResult(); if (activo != null) { XSSFRow renglon = codigoAsignado.createRow(codigoAsignadoRow++); renglon.createCell(0).setCellValue(sheet.getSheetName() + ":" + (i + 1)); renglon.createCell(1).setCellValue(row.getCell(0).toString()); renglon.createCell(2).setCellValue(row.getCell(1).toString()); renglon.createCell(3).setCellValue(row.getCell(2).toString()); renglon.createCell(4).setCellValue(row.getCell(3).toString()); renglon.createCell(5).setCellValue(row.getCell(4).toString()); renglon.createCell(6).setCellValue(row.getCell(5).toString()); renglon.createCell(7).setCellValue(row.getCell(6).toString()); renglon.createCell(8).setCellValue(row.getCell(7).toString()); renglon.createCell(9) .setCellValue(codigo + "-" + "SIN CODIGO" + nf.format(codigoInicial)); renglon.createCell(10).setCellValue(row.getCell(9).toString()); renglon.createCell(11).setCellValue(row.getCell(10).toString()); renglon.createCell(12).setCellValue(row.getCell(11).toString()); renglon.createCell(13).setCellValue(row.getCell(12).toString()); renglon.createCell(14).setCellValue(row.getCell(13).toString()); renglon.createCell(15).setCellValue(row.getCell(14).toString()); renglon.createCell(16).setCellValue(row.getCell(15).toString()); codigo = "SIN CODIGO" + nf.format(codigoInicial); codigoInicial++; } } String descripcion = null; if (row.getCell(9) != null) { switch (row.getCell(9).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: descripcion = row.getCell(9).toString(); descripcion = StringUtils.removeEnd(descripcion, ".0"); break; case XSSFCell.CELL_TYPE_STRING: descripcion = row.getCell(9).getStringCellValue().trim(); break; default: descripcion = row.getCell(9).toString().trim(); } } String marca = null; if (row.getCell(10) != null) { switch (row.getCell(10).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: marca = row.getCell(10).toString(); marca = StringUtils.removeEnd(marca, ".0"); break; case XSSFCell.CELL_TYPE_STRING: marca = row.getCell(10).getStringCellValue().trim(); break; default: marca = row.getCell(10).toString().trim(); } } String modelo = null; if (row.getCell(11) != null) { switch (row.getCell(11).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: modelo = row.getCell(11).toString(); modelo = StringUtils.removeEnd(modelo, ".0"); break; case XSSFCell.CELL_TYPE_STRING: modelo = row.getCell(11).getStringCellValue().trim(); break; default: modelo = row.getCell(11).toString().trim(); } } String serie = null; if (row.getCell(12) != null) { switch (row.getCell(12).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: serie = row.getCell(12).toString(); serie = StringUtils.removeEnd(serie, ".0"); break; case XSSFCell.CELL_TYPE_STRING: serie = row.getCell(12).getStringCellValue().trim(); break; default: serie = row.getCell(12).toString().trim(); } } String responsable = null; if (row.getCell(13) != null) { switch (row.getCell(13).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: responsable = row.getCell(13).toString(); responsable = StringUtils.removeEnd(responsable, ".0"); break; case XSSFCell.CELL_TYPE_STRING: responsable = row.getCell(13).getStringCellValue().trim(); break; default: responsable = row.getCell(13).toString().trim(); } } String ubicacion = null; if (row.getCell(14) != null) { switch (row.getCell(14).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: ubicacion = row.getCell(14).toString(); ubicacion = StringUtils.removeEnd(ubicacion, ".0"); break; case XSSFCell.CELL_TYPE_STRING: ubicacion = row.getCell(14).getStringCellValue().trim(); break; default: ubicacion = row.getCell(14).toString().trim(); } } BigDecimal costo = null; switch (row.getCell(15).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: costo = new BigDecimal(row.getCell(15).getNumericCellValue(), mc); log.debug("COSTO-N: {} - {}", costo, row.getCell(15).getNumericCellValue()); break; case XSSFCell.CELL_TYPE_STRING: costo = new BigDecimal(row.getCell(15).toString(), mc); log.debug("COSTO-S: {} - {}", costo, row.getCell(15).toString()); break; case XSSFCell.CELL_TYPE_FORMULA: costo = new BigDecimal( evaluator.evaluateInCell(row.getCell(15)).getNumericCellValue(), mc); log.debug("COSTO-F: {}", costo); } if (costo == null) { XSSFRow renglon = sinCosto.createRow(sinCostoRow++); renglon.createCell(0).setCellValue(sheet.getSheetName() + ":" + (i + 1)); renglon.createCell(1).setCellValue(row.getCell(0).toString()); renglon.createCell(2).setCellValue(row.getCell(1).toString()); renglon.createCell(3).setCellValue(row.getCell(2).toString()); renglon.createCell(4).setCellValue(row.getCell(3).toString()); renglon.createCell(5).setCellValue(row.getCell(4).toString()); renglon.createCell(6).setCellValue(row.getCell(5).toString()); renglon.createCell(7).setCellValue(row.getCell(6).toString()); renglon.createCell(8).setCellValue(row.getCell(7).toString()); renglon.createCell(9).setCellValue(row.getCell(8).toString()); renglon.createCell(10).setCellValue(row.getCell(9).toString()); renglon.createCell(11).setCellValue(row.getCell(10).toString()); renglon.createCell(12).setCellValue(row.getCell(11).toString()); renglon.createCell(13).setCellValue(row.getCell(12).toString()); renglon.createCell(14).setCellValue(row.getCell(13).toString()); renglon.createCell(15).setCellValue(row.getCell(14).toString()); renglon.createCell(16).setCellValue(row.getCell(15).toString()); continue; } Activo activo = new Activo(fechaCompra, seguro, garantia, poliza, codigo, descripcion, marca, modelo, serie, responsable, ubicacion, costo, tipoActivo, centroCosto, proveedor, usuario.getEmpresa()); this.crea(activo, usuario); } else { XSSFRow renglon = sinCCosto.createRow(sinCCostoRow++); renglon.createCell(0).setCellValue(sheet.getSheetName() + ":" + (i + 1)); renglon.createCell(1).setCellValue(row.getCell(0).toString()); renglon.createCell(2).setCellValue(row.getCell(1).toString()); renglon.createCell(3).setCellValue(row.getCell(2).toString()); renglon.createCell(4).setCellValue(row.getCell(3).toString()); renglon.createCell(5).setCellValue(row.getCell(4).toString()); renglon.createCell(6).setCellValue(row.getCell(5).toString()); renglon.createCell(7).setCellValue(row.getCell(6).toString()); renglon.createCell(8).setCellValue(row.getCell(7).toString()); renglon.createCell(9).setCellValue(row.getCell(8).toString()); renglon.createCell(10).setCellValue(row.getCell(9).toString()); renglon.createCell(11).setCellValue(row.getCell(10).toString()); renglon.createCell(12).setCellValue(row.getCell(11).toString()); renglon.createCell(13).setCellValue(row.getCell(12).toString()); renglon.createCell(14).setCellValue(row.getCell(13).toString()); renglon.createCell(15).setCellValue(row.getCell(14).toString()); renglon.createCell(16).setCellValue(row.getCell(15).toString()); continue; } } else { throw new RuntimeException( "(" + idx + ":" + i + ") No se encontro el tipo de activo " + nombreGrupo); } } } //tx.commit(); log.debug("################################################"); log.debug("################################################"); log.debug("TERMINO EN {} MINS", (new Date().getTime() - inicio.getTime()) / (1000 * 60)); log.debug("################################################"); log.debug("################################################"); wb.write(out); } catch (IOException | RuntimeException e) { //if (tx != null && tx.isActive()) { //tx.rollback(); //} log.error("Hubo problemas al intentar pasar datos de archivo excel a BD (" + idx + ":" + i + ")", e); throw new RuntimeException( "Hubo problemas al intentar pasar datos de archivo excel a BD (" + idx + ":" + i + ")", e); } }