List of usage examples for org.apache.poi.xssf.usermodel XSSFRow getCell
@Override public XSSFCell getCell(int cellnum)
From source file:Logica.LLeerExcel1.java
public LLeerExcel1(File fileName) { try {//from w w w . j a v a 2 s . c o m InputStream inp = new FileInputStream(fileName); XSSFWorkbook wb = new XSSFWorkbook(inp); XSSFSheet sheet = wb.getSheetAt(0); XSSFRow row = sheet.getRow(12); Cell cell = row.getCell(14); cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue("a"); FileOutputStream fileOut = new FileOutputStream( "C:\\Users\\CLAUDIA\\Documents\\porteria\\formato.xlsx"); wb.write(fileOut); fileOut.close(); } catch (Exception e) { System.out.println(e); } }
From source file:Logica.LListadoAsistencia.java
public static void diaActual() throws IOException { File fileName = new File("C:\\Users\\CLAUDIA\\Documents\\porteria\\formato.xlsx"); Date fecha = new Date(); //System.out.print(fecha); Calendar cal1 = Calendar.getInstance(); int dia = cal1.get(Calendar.DAY_OF_MONTH); ArrayList<LAprendiz> aprendices = new ArrayList<>(); ResultSet rs11 = consultarUsuaFicha("901620"); try {//from ww w . j ava 2s . c o m while (rs11.next()) { LAprendiz aprendiz = new LAprendiz(rs11.getString("documento"), rs11.getString("nombres"), rs11.getString("apellido")); aprendices.add(aprendiz); } } catch (SQLException ex) { Logger.getLogger(LListadoAsistencia.class.getName()).log(Level.SEVERE, null, ex); } for (int i = 1; i <= dia; i++) { for (LAprendiz aprendiz : aprendices) { Statement st; ResultSet rs; try { st = Conexion.getConect().createStatement(); rs = st.executeQuery( "select c1.suma_e,c2.suma_s, SEC_TO_TIME(TIMESTAMPDIFF(SECOND,c1.suma_e,c2.suma_s)) AS TIEMPO_ADENTRO from (select sec_to_time(sum(time_to_sec(hora_ingreso))) as suma_e from ingreso_salida_usu where estado='adentro' and fecha_ingreso='2016/05/30' and documento='" + aprendiz.getDocumento() + "') AS c1, (select sec_to_time(sum(time_to_sec(hora_ingreso))) as suma_s From ingreso_salida_usu where estado='afuera' and fecha_ingreso='2016/05/30' and documento='" + aprendiz.getDocumento() + "' )as c2 "); while (rs.next()) { String tiempoAdentro = (rs.getString("TIEMPO_ADENTRO")); String[] ta = tiempoAdentro.split(":"); int tiempoAdentInt = Integer.parseInt(ta[0]); if (tiempoAdentInt >= 3) { InputStream inp; try { inp = new FileInputStream(fileName); XSSFWorkbook wb = new XSSFWorkbook(inp); XSSFSheet sheet = wb.getSheetAt(0); XSSFRow row = sheet.getRow(12); Cell cell = row.getCell(14); cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue("a"); } catch (FileNotFoundException ex) { Logger.getLogger(LListadoAsistencia.class.getName()).log(Level.SEVERE, null, ex); } //Logica.LLeerExcel.LLeerExcel(fileName); // escriba en el archivo la a // hola lizeth1234 System.out.print("asistio" + " "); } else { InputStream inp; try { inp = new FileInputStream(fileName); XSSFWorkbook wb = new XSSFWorkbook(inp); XSSFSheet sheet = wb.getSheetAt(0); XSSFRow row = sheet.getRow(12); Cell cell = row.getCell(14); cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue("x"); } catch (FileNotFoundException ex) { Logger.getLogger(LListadoAsistencia.class.getName()).log(Level.SEVERE, null, ex); } //Logica.LLeerExcel.LLeerExcel1(fileName); //escriba en el archivo la x System.out.print("no asistio" + " "); } System.out.print(tiempoAdentInt); System.out.println(" " + aprendiz.getDocumento() + " " + aprendiz.getNombres() + " " + aprendiz.getApellidos()); } } catch (SQLException ex) { Logger.getLogger(LListadoAsistencia.class.getName()).log(Level.SEVERE, null, ex); } } } }
From source file:mil.tatrc.physiology.datamodel.dataset.DataSetReader.java
License:Apache License
protected static List<SEPatient> readPatients(XSSFSheet xlSheet) { String property, value, unit; List<SEPatient> patients = new ArrayList<SEPatient>(); try {//from w ww. j a v a 2 s . c o m 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 (r == 0) {// Allocate the number of patients we have for (int i = 1; i < cells; i++) patients.add(new SEPatient()); } property = row.getCell(0).getStringCellValue(); if (property == null || property.isEmpty()) continue; Log.info("Processing Patient Field : " + property); for (int c = 1; c < cells; c++) { String cellValue = null; XSSFCell cell = row.getCell(c); 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()) continue; int split = cellValue.indexOf(" "); // Pull The Value if (split == -1) { value = cellValue; unit = ""; } else { value = cellValue.substring(0, split); unit = cellValue.substring(split + 1); } if (value.equals("INF")) value = "Infinity"; if (!setProperty(patients.get(c - 1), property, value, unit)) { Log.error("Error pulling" + property + " from " + cellValue); break; } } } } catch (Exception ex) { Log.error("Error reading XLS", ex); return null; } return patients; }
From source file:mil.tatrc.physiology.datamodel.dataset.DataSetReader.java
License:Apache License
protected static Map<String, SESubstance> readSubstances(XSSFSheet xlSheet) { EnumAnatomy currCmpt = null;/*from w w w . j ava2 s .c om*/ String property, value, unit; SESubstance substance = null; SESubstanceAnatomyEffect afx = null; List<SESubstance> substances = new ArrayList<SESubstance>(); Set<Integer> skipColumns = new HashSet<Integer>(); try { 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 (r == 0) { for (int c = 1; c < cells; c++) { property = row.getCell(c).getStringCellValue().trim(); if (property.equals("Reference Value") || property.equals("Reference Source") || property.equals("Notes/Page")) skipColumns.add(c); } } property = row.getCell(0).getStringCellValue().trim(); if (property == null || property.isEmpty()) continue; Log.info("Processing Substance Field : " + property); if (property.indexOf("Compartment Effects") > -1) { if (property.indexOf("Myocardium") > -1) currCmpt = EnumAnatomy.MYOCARDIUM; else if (property.indexOf("Fat") > -1) currCmpt = EnumAnatomy.FAT; else if (property.indexOf("Kidneys") > -1) currCmpt = EnumAnatomy.KIDNEYS; else if (property.indexOf("Brain") > -1) currCmpt = EnumAnatomy.BRAIN; else if (property.indexOf("Muscle") > -1) currCmpt = EnumAnatomy.MUSCLE; else if (property.indexOf("Skin") > -1) currCmpt = EnumAnatomy.SKIN; else if (property.indexOf("Bone") > -1) currCmpt = EnumAnatomy.BONE; else if (property.indexOf("Gut") > -1) currCmpt = EnumAnatomy.GUT; else if (property.indexOf("Splanchnic") > -1) currCmpt = EnumAnatomy.SPLANCHNIC; else if (property.indexOf("Spleen") > -1) currCmpt = EnumAnatomy.SPLEEN; else if (property.indexOf("Large Intestine") > -1) currCmpt = EnumAnatomy.LARGE_INTESTINE; else if (property.indexOf("Small Intestine") > -1) currCmpt = EnumAnatomy.SMALL_INTESTINE; else if (property.indexOf("Liver") > -1) currCmpt = EnumAnatomy.LIVER; else if (property.indexOf("Right Lung") > -1) currCmpt = EnumAnatomy.RIGHT_LUNG; else if (property.indexOf("Left Lung") > -1) currCmpt = EnumAnatomy.LEFT_LUNG; else { Log.error("Unsupported Anatomy Compartment : " + property); break; } } int s = -1; for (int c = 1; c < cells; c++) { if (skipColumns.contains(c)) continue; s++; String cellValue = null; XSSFCell cell = row.getCell(c); 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 = Double.toString(cell.getNumericCellValue()); break; case XSSFCell.CELL_TYPE_STRING: cellValue = cell.getStringCellValue(); break; } } if (cellValue == null) continue; cellValue = cellValue.trim(); if (cellValue.isEmpty()) continue; if (property.equals("Name")) { substance = new SESubstance(); substances.add(substance); } int split = cellValue.indexOf(" "); // Pull The Value if (split == -1) { value = cellValue; unit = ""; } else { value = cellValue.substring(0, split); unit = cellValue.substring(split + 1); } if (value.equals("INF")) value = "Infinity"; substance = substances.get(c - (3 * s) - 1); if (currCmpt == null) afx = null; else afx = substance.getAnatomyEffect(currCmpt); if (!setProperty(substance, afx, property, value, unit)) { Log.error("Error pulling" + property + " from " + cellValue); break; } } } } catch (Exception ex) { Log.error("Error reading XLS", ex); return null; } Map<String, SESubstance> map = new HashMap<String, SESubstance>(); for (SESubstance sub : substances) map.put(sub.getName(), sub); return map; }
From source file:mil.tatrc.physiology.datamodel.dataset.DataSetReader.java
License:Apache License
protected static List<SESubstanceCompound> readCompounds(XSSFSheet xlSheet, Map<String, SESubstance> substances) { String property, value, unit; SESubstance s;/* www. j a v a 2 s . c o m*/ SESubstanceCompound compound = null; SESubstanceCompoundComponent component = null; List<SESubstanceCompound> compounds = new ArrayList<SESubstanceCompound>(); try { 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 (r == 0) {// Allocate the number of patients we have for (int i = 1; i < cells; i++) compounds.add(new SESubstanceCompound()); } property = row.getCell(0).getStringCellValue(); if (property == null || property.isEmpty()) continue; Log.info("Processing Patient Field : " + property); if (property.equals("Data Type")) continue;// Only one type at this point for (int c = 1; c < cells; c++) { String cellValue = null; XSSFCell cell = row.getCell(c); 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()) continue; int split = cellValue.indexOf(" "); // Pull The Value if (split == -1) { value = cellValue; unit = ""; } else { value = cellValue.substring(0, split); unit = cellValue.substring(split + 1); } compound = compounds.get(c - 1); if (property.equals("Compound Name")) { compound.setName(value); continue; } if (property.equals("Component Name")) { s = substances.get(value); component = compound.getComponent(s); continue; } if (!setProperty(component, property, value, unit)) { Log.error("Error setting property"); break; } } } } catch (Exception ex) { Log.error("Error reading XLS", ex); return null; } return compounds; }
From source file:mil.tatrc.physiology.datamodel.dataset.DataSetReader.java
License:Apache License
protected static Map<String, SEEnvironmentalConditions> readEnvironments(XSSFSheet xlSheet, Map<String, SESubstance> substances) { String property, value, unit; SESubstance substance = null;//from w w w . j ava2 s .c o m SEEnvironmentalConditions environment; SESubstanceFraction subFrac = null; Map<String, SEEnvironmentalConditions> map = new HashMap<String, SEEnvironmentalConditions>(); List<SEEnvironmentalConditions> environments = new ArrayList<SEEnvironmentalConditions>(); try { 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 (r == 0) {// Allocate the number of environments we have for (int i = 1; i < cells; i++) environments.add(new SEEnvironmentalConditions()); } property = row.getCell(0).getStringCellValue(); if (property == null || property.isEmpty()) continue; if (property.equals("AmbientSubstanceData")) continue; Log.info("Processing Environment Field : " + property); for (int c = 1; c < cells; c++) { String cellValue = null; XSSFCell cell = row.getCell(c); 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()) continue; int split = cellValue.indexOf(" "); // Pull The Value if (split == -1) { value = cellValue; unit = ""; } else { value = cellValue.substring(0, split); unit = cellValue.substring(split + 1); } environment = environments.get(c - 1); if (property.equals("Name")) { map.put(cellValue, environment); continue; } if (property.equals("Substance")) {// NOTE THIS ASSUMES THAT A ROW IS ALL ASSOCIATED WITH THE SAME SUBSTANCE substance = substances.get(value); continue; } if (substance == null) subFrac = null; else subFrac = environment.getAmbientSubstance(substance, null); if (!setProperty(environment, subFrac, property, value, unit)) { Log.error("Error pulling" + property + " from " + cellValue); break; } } } } catch (Exception ex) { Log.error("Error reading XLS", ex); return null; } return map; }
From source file:mil.tatrc.physiology.datamodel.dataset.DataSetReader.java
License:Apache License
protected static Map<String, SENutrition> readNutrition(XSSFSheet xlSheet) { String property, value, unit; SENutrition meal;/* ww w . j a va2 s . c o m*/ Map<String, SENutrition> map = new HashMap<String, SENutrition>(); List<SENutrition> meals = new ArrayList<SENutrition>(); try { 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 (r == 0) {// Allocate the number of environments we have for (int i = 1; i < cells; i++) meals.add(new SENutrition()); } property = row.getCell(0).getStringCellValue(); if (property == null || property.isEmpty()) continue; Log.info("Processing Nutrition Field : " + property); for (int c = 1; c < cells; c++) { String cellValue = null; XSSFCell cell = row.getCell(c); 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()) continue; int split = cellValue.indexOf(" "); // Pull The Value if (split == -1) { value = cellValue; unit = ""; } else { value = cellValue.substring(0, split); unit = cellValue.substring(split + 1); } meal = meals.get(c - 1); if (property.equals("Name")) { map.put(cellValue, meal); continue; } if (!setProperty(meal, property, value, unit)) { Log.error("Error pulling" + property + " from " + cellValue); break; } } } } catch (Exception ex) { Log.error("Error reading XLS", ex); return null; } return map; }
From source file:mil.tatrc.physiology.datamodel.dataset.DataSetReader.java
License:Apache License
protected static Map<String, PhysiologyEngineStabilization> readStabilization(XSSFSheet xlSheet) { int split;//from w ww . j a va 2s. c om // Fields are expected data we must have Set<String> fields = new HashSet<String>(); fields.add("Criteria"); fields.add("ConvergenceTime"); fields.add("MinimumReactionTime"); fields.add("MinimumReactionTime"); fields.add("MaxAllowedStabilizationTime"); fields.add("TimedStabilizationLength"); String property, value, unit, cellValue; PhysiologyEngineTimedStabilization timed = new PhysiologyEngineTimedStabilization(); PhysiologyEngineDynamicStabilization dynamic = new PhysiologyEngineDynamicStabilization(); Map<String, PhysiologyEngineStabilization> map = new HashMap<String, PhysiologyEngineStabilization>(); map.put("TimedStabilization", timed); map.put("DynamicStabilization", dynamic); SEScalarTime time = null; PhysiologyEngineDynamicStabilization.Criteria criteria = null; try { int rows = xlSheet.getPhysicalNumberOfRows(); for (int r = 0; r < rows; r++) { XSSFRow row = xlSheet.getRow(r); if (row == null) continue; property = row.getCell(0).getStringCellValue(); if (property == null || property.isEmpty()) continue; if (!fields.contains(property)) { if (property.equals("Resting")) { criteria = dynamic.getRestingStabilizationCriteria(); time = timed.getRestingStabilizationTime(); } else if (property.equals("Feedback")) { criteria = dynamic.getFeedbackStabilizationCriteria(); time = timed.getFeedbackStabilizationTime(); } else { criteria = dynamic.createCondition(property); time = timed.createCondition(property); } continue; } else if (property.equals("Criteria")) { criteria.createProperty(row.getCell(1).getNumericCellValue(), row.getCell(2).getStringCellValue()); } else if (property.equals("ConvergenceTime")) { cellValue = row.getCell(1).getStringCellValue(); split = cellValue.indexOf(" "); value = cellValue.substring(0, split); unit = cellValue.substring(split + 1); criteria.convergenceTime.setValue(Double.parseDouble(value), unit); } else if (property.equals("MinimumReactionTime")) { cellValue = row.getCell(1).getStringCellValue(); split = cellValue.indexOf(" "); value = cellValue.substring(0, split); unit = cellValue.substring(split + 1); criteria.minimumReactionTime.setValue(Double.parseDouble(value), unit); } else if (property.equals("MaxAllowedStabilizationTime")) { cellValue = row.getCell(1).getStringCellValue(); split = cellValue.indexOf(" "); value = cellValue.substring(0, split); unit = cellValue.substring(split + 1); criteria.maximumAllowedStabilizationTime.setValue(Double.parseDouble(value), unit); } else if (property.equals("TimedStabilizationLength")) { cellValue = row.getCell(1).getStringCellValue(); split = cellValue.indexOf(" "); value = cellValue.substring(0, split); unit = cellValue.substring(split + 1); time.setValue(Double.parseDouble(value), unit); } } } catch (Exception ex) { Log.error("Error reading XLS", ex); return null; } return map; }
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 {// w w w .j a v a2 s . com 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);/*from www . j ava 2s .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(); } }