List of usage examples for org.apache.poi.ss.usermodel FormulaEvaluator evaluateFormulaCell
CellType evaluateFormulaCell(Cell cell);
From source file:de.ingrid.iplug.excel.service.SheetsService.java
License:EUPL
private static Comparable<? extends Object> calculateFormula(final Cell poiCell, final FormulaEvaluator eval) { Comparable<? extends Object> ret = null; final int type = eval.evaluateFormulaCell(poiCell); switch (type) { case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(poiCell)) { ret = getFormattedDateString(poiCell); } else {/*w ww.j a v a 2s .c o m*/ ret = poiCell.getNumericCellValue(); } break; case Cell.CELL_TYPE_BOOLEAN: ret = poiCell.getBooleanCellValue(); break; case Cell.CELL_TYPE_BLANK: case Cell.CELL_TYPE_ERROR: case Cell.CELL_TYPE_FORMULA: case Cell.CELL_TYPE_STRING: default: ret = poiCell.getStringCellValue(); } return ret; }
From source file:eu.impact_project.resultsrepository.report.Report.java
License:Apache License
private void evaluateFormulas(Sheet sheet) { FormulaEvaluator evaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator(); Row row = null;//from www. ja v a 2 s . com Cell cell = null; for (int i = 0; i < 100; i++) { row = sheet.getRow(i); if (row == null) row = sheet.createRow(i); for (int j = 0; j < 50; j++) { cell = row.getCell(j); if (cell == null) cell = row.createCell(j); evaluator.evaluateFormulaCell(cell); } } }
From source file:eu.squadd.timesheets.eolas.TimeTemplate.java
public String prepareTimesheet(String[] args) { String response = null;// w w w. j a va 2 s. c o m try { String[] ym = args[0].split("/"); month = Integer.parseInt(ym[0]); year = Integer.parseInt(ym[1]); Calendar cal = Calendar.getInstance(TimeZone.getDefault()); cal.set(Calendar.YEAR, year); cal.set(Calendar.MONTH, month - 1); int days = cal.getActualMaximum(Calendar.DAY_OF_MONTH); monthName = cal.getDisplayName(Calendar.MONTH, Calendar.SHORT_FORMAT, Locale.ENGLISH); String periodName = monthName + "-" + year; cal.set(Calendar.DATE, 1); String dayOfWeek = new SimpleDateFormat("EE").format(cal.getTime()); System.out.println("Month: " + periodName); System.out.println("Days in month: " + days); System.out.println("Month starts in: " + dayOfWeek); Map<String, String> bankHolidays = year == 2016 ? publicHolidays2016 : publicHolidays2017; Map<String, String> holidays = this.extractHolidays(args); HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(template)); HSSFSheet sheet = wb.getSheet("timesheet"); //getSheetAt(0); HSSFRow currentRow; SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy"); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); sheet.getRow(4).getCell(1).setCellValue(periodName); int row = 7; int startRow = 0; int i = 1; while (i <= days) { currentRow = sheet.getRow(row); if (currentRow.getRowNum() > 47) break; String day = currentRow.getCell(0).getStringCellValue(); if (day.startsWith("Total")) { evaluator.evaluateFormulaCell(currentRow.getCell(2)); evaluator.evaluateFormulaCell(currentRow.getCell(4)); row++; continue; } if (startRow == 0) { if (dayOfWeek.equals(day.substring(0, 3))) { startRow = currentRow.getRowNum(); System.out.println("Starting row found: " + startRow + 1); } else { row++; continue; } } cal.set(Calendar.DATE, i); String date = sdf.format(cal.getTime()); if (!day.equals("Saturday") && !day.equals("Sunday") && bankHolidays.get(date) == null && holidays.get(date) == null) { currentRow.getCell(1).setCellValue(date); currentRow.getCell(2).setCellValue(defaultHours); // regular hours //currentRow.getCell(3).setCellValue(defaultHours); // overtime hours currentRow.getCell(4).setCellValue(defaultHours); // total hours } i++; row++; } currentRow = sheet.getRow(46); evaluator.evaluateFormulaCell(currentRow.getCell(2)); evaluator.evaluateFormulaCell(currentRow.getCell(4)); currentRow = sheet.getRow(47); evaluator.evaluateFormulaCell(currentRow.getCell(2)); evaluator.evaluateFormulaCell(currentRow.getCell(4)); response = outFilePath.replace("#MONTH#", periodName); wb.write(new FileOutputStream(response)); } catch (IOException ex) { Logger.getLogger(Timesheets.class.getName()).log(Level.SEVERE, null, ex); } System.out.println("Timesheet created."); return response; }
From source file:helpers.Excel.ExcelDataFormat.java
public OneExcelSheet marshalAsStructure(Iterator<Row> sheet, FormulaEvaluator evaluator) { logger.info("Evaluating formulas."); evaluator.evaluateAll();/* w w w.j ava 2 s . com*/ logger.info("Done..."); OneExcelSheet onesheet = new OneExcelSheet(); ArrayList<String> headers = null; for (Iterator<Row> rowIterator = sheet; rowIterator.hasNext();) { Row row = rowIterator.next(); if (headers == null) { headers = new ArrayList<String>(); int coln = 0; for (Iterator<Cell> cellIterator = row.cellIterator(); cellIterator.hasNext();) { try { Cell cell = cellIterator.next(); logger.info("Header:" + cell.getStringCellValue()); String headn = cell.getStringCellValue().replace(" ", ""); headers.add(headn); OneExcelColumn col = new OneExcelColumn(headn, coln); onesheet.columns.add(col); } catch (Exception e) { logger.error("Unable to decode cell header. Ex=" + e.getMessage(), e); } coln++; } } else { ArrayList<Object> newrow = new ArrayList<Object>(); onesheet.data.add(newrow); int coln = 0; //for (Iterator<Cell> cellIterator = row.cellIterator(); cellIterator.hasNext();) for (int cn = 0; cn < row.getLastCellNum(); cn++) { Cell cell = row.getCell(cn, Row.CREATE_NULL_AS_BLANK); //Cell cell=cellIterator.next(); //logger.info("Cell type:"+cell.getCellType()); switch (evaluator.evaluateInCell(cell).getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { //logger.info(cell.getCellType()+"="+cell.getDateCellValue()); newrow.add(cell.getDateCellValue()); if (onesheet.columns.size() > coln) onesheet.columns.get(coln).columnTypes[9]++; } else { //logger.info(cell.getCellType()+"="+cell.getNumericCellValue()); newrow.add(cell.getNumericCellValue()); if (onesheet.columns.size() > coln) onesheet.columns.get(coln).columnTypes[cell.getCellType()]++; } break; case HSSFCell.CELL_TYPE_FORMULA: int value = evaluator.evaluateFormulaCell(cell); value = cell.getCachedFormulaResultType(); newrow.add(value); if (onesheet.columns.size() > coln) onesheet.columns.get(coln).columnTypes[0]++; break; default: //logger.info(cell.getCellType()+"="+cell.getStringCellValue()); String cellstr = new String(cell.getStringCellValue().getBytes(), Charset.forName("UTF-8")); newrow.add(cellstr); if (onesheet.columns.size() > coln) onesheet.columns.get(coln).columnTypes[cell.getCellType()]++; break; } coln++; } } } return onesheet; }
From source file:hjow.hgtable.util.XLSXUtil.java
License:Apache License
/** * <p>XLSX ? ? ?? . ? ? ?? ?? , ? ? ?? ? ? ?? ?.</p> * /* w ww .j a va 2s . c o m*/ * @param file : XLSX ? * @return ? ? */ public static List<TableSet> toTableSets(File file) { List<TableSet> tableSets = new Vector<TableSet>(); org.apache.poi.ss.usermodel.Workbook workbook = null; if (file == null) throw new NullPointerException(Manager.applyStringTable("Please select file !!")); if (!file.exists()) throw new NullPointerException(Manager.applyStringTable("File") + " " + file.getAbsolutePath() + " " + Manager.applyStringTable("is not exist")); boolean isHead = true; int rowNum = 0; int cellNum = 0; int cellCount = 0; FileInputStream fileStream = null; try { if (file.getAbsolutePath().endsWith(".xlsx") || file.getAbsolutePath().endsWith(".XLSX")) { workbook = new org.apache.poi.xssf.usermodel.XSSFWorkbook(file); } else if (file.getAbsolutePath().endsWith(".xls") || file.getAbsolutePath().endsWith(".XLS")) { fileStream = new FileInputStream(file); workbook = new org.apache.poi.hssf.usermodel.HSSFWorkbook(fileStream); } org.apache.poi.ss.usermodel.FormulaEvaluator evals = workbook.getCreationHelper() .createFormulaEvaluator(); org.apache.poi.ss.usermodel.Sheet sheet = null; for (int x = 0; x < workbook.getNumberOfSheets(); x++) { TableSet newTableSet = new DefaultTableSet(); newTableSet.setColumns(new Vector<Column>()); sheet = workbook.getSheetAt(x); newTableSet.setName(sheet.getSheetName()); rowNum = 0; isHead = true; String targetData = null; for (org.apache.poi.ss.usermodel.Row row : sheet) { cellNum = 0; for (org.apache.poi.ss.usermodel.Cell cell : row) { try { if (cellNum >= cellCount) { throw new IndexOutOfBoundsException( Manager.applyStringTable("There are some cells not have their heads") + ", " + Manager.applyStringTable("Head count") + " : " + cellCount + ", " + Manager.applyStringTable("Cell Number") + " : " + cellNum); } switch (cell.getCellType()) { case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING: if (isHead) { newTableSet.getColumns().add(new Column( cell.getRichStringCellValue().getString(), Column.TYPE_STRING)); } else { targetData = cell.getRichStringCellValue().getString(); newTableSet.getColumns().get(cellNum).setType(cell.getCellType()); } break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC: if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) { if (isHead) { newTableSet.getColumns().add(new Column( String.valueOf(cell.getStringCellValue()), Column.TYPE_DATE)); } else { targetData = String.valueOf(cell.getDateCellValue()); newTableSet.getColumns().get(cellNum).setType(cell.getCellType()); } } else { if (isHead) { newTableSet.getColumns().add(new Column( String.valueOf(cell.getStringCellValue()), Column.TYPE_NUMERIC)); } else { double values = cell.getNumericCellValue(); double intPart = values - ((double) ((int) values)); if (intPart == 0.0) { targetData = String.valueOf(((int) values)); newTableSet.getColumns().get(cellNum).setType(Column.TYPE_INTEGER); } else { targetData = String.valueOf(values); newTableSet.getColumns().get(cellNum).setType(cell.getCellType()); } } } break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN: if (isHead) { newTableSet.getColumns().add(new Column( String.valueOf(cell.getStringCellValue()), Column.TYPE_BOOLEAN)); } else { targetData = String.valueOf(cell.getBooleanCellValue()); newTableSet.getColumns().get(cellNum).setType(cell.getCellType()); } break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA: if (isHead) { newTableSet.getColumns().add(new Column( String.valueOf(cell.getStringCellValue()), Column.TYPE_NUMERIC)); } else { if (evals.evaluateFormulaCell(cell) == 0) { targetData = String.valueOf(cell.getNumericCellValue()); newTableSet.getColumns().get(cellNum).setType(Column.TYPE_NUMERIC); } else if (evals.evaluateFormulaCell(cell) == 1) { targetData = String.valueOf(cell.getStringCellValue()); newTableSet.getColumns().get(cellNum).setType(Column.TYPE_STRING); } else if (evals.evaluateFormulaCell(cell) == 4) { targetData = String.valueOf(cell.getBooleanCellValue()); newTableSet.getColumns().get(cellNum).setType(Column.TYPE_BOOLEAN); } else { targetData = String.valueOf(cell.getCellFormula()); newTableSet.getColumns().get(cellNum).setType(cell.getCellType()); } } break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK: if (isHead) { newTableSet.getColumns().add(new Column("", Column.TYPE_STRING)); } else { targetData = ""; newTableSet.getColumns().get(cellNum).setType(Column.TYPE_BLANK); } break; default: if (isHead) { newTableSet.getColumns().add(new Column("", Column.TYPE_STRING)); } else { try { targetData = cell.getStringCellValue(); } catch (Exception e1) { e1.printStackTrace(); } newTableSet.getColumns().get(cellNum).setType(cell.getCellType()); } break; } if (isHead) { cellCount++; } else { while (rowNum > 0 && newTableSet.getColumns().get(cellNum).getData().size() < rowNum) { newTableSet.getColumns().get(cellNum).getData().add(""); } if (targetData != null) newTableSet.getColumns().get(cellNum).getData().add(targetData); else { newTableSet.getColumns().get(cellNum).getData().add(""); } } } catch (ArrayIndexOutOfBoundsException e1) { StringBuffer err = new StringBuffer(""); for (StackTraceElement errEl : e1.getStackTrace()) { err = err.append("\t " + errEl + "\n"); } String cellObject = null; try { cellObject = cell.getStringCellValue(); } catch (Exception e2) { } throw new ArrayIndexOutOfBoundsException( Manager.applyStringTable("Array index out of range") + " <- " + Manager.applyStringTable("Reading xlsx file") + " : " + file.getName() + ", " + sheet.getSheetName() + "\n" + Manager.applyStringTable("On") + " " + Manager.applyStringTable("Row") + " " + rowNum + ", " + Manager.applyStringTable("Cell") + " " + cellNum + ", " + Manager.applyStringTable("Value") + " : " + String.valueOf(cellObject) + "\n " + Manager.applyStringTable("<-\n") + err + "\n " + Manager.applyStringTable("Original Message") + "...\n" + e1.getMessage() + "\n" + Manager.applyStringTable("End")); } cellNum++; } isHead = false; rowNum++; } fillTableSet(newTableSet); newTableSet.removeEmptyColumn(true); tableSets.add(newTableSet); } return tableSets; } catch (Throwable e) { if (Main.MODE >= DebuggingUtil.DEBUG) e.printStackTrace(); Main.logError(e, Manager.applyStringTable("On reading xlsx") + " : " + file + "\n" + Manager.applyStringTable("At rownum") + " " + rowNum + ", " + Manager.applyStringTable("cellnum") + " " + cellNum); return null; } finally { try { workbook.close(); } catch (Throwable e) { } try { if (fileStream != null) fileStream.close(); } catch (Throwable e) { } } }
From source file:magicware.scm.redmine.tools.util.ExcelUtils.java
License:Apache License
public static String getCellContent(Cell cell, FormulaEvaluator evaluator) { String result = null;/*from w w w . j a v a 2 s . c o m*/ switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: result = cell.getRichStringCellValue().getString(); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { result = Constants.DATE_FORMAT.format(cell.getDateCellValue()); } else { result = String.valueOf(Double.valueOf(cell.getNumericCellValue()).intValue()); } break; case Cell.CELL_TYPE_BOOLEAN: result = String.valueOf(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: switch (evaluator.evaluateFormulaCell(cell)) { case Cell.CELL_TYPE_BOOLEAN: result = String.valueOf(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { result = Constants.DATE_FORMAT.format(cell.getDateCellValue()); } else { result = String.valueOf(Double.valueOf(cell.getNumericCellValue()).intValue()); } break; case Cell.CELL_TYPE_STRING: result = String.valueOf(cell.getStringCellValue()); break; case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_ERROR: result = String.valueOf(cell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: break; } break; default: break; } return result; }
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 {/*from w w w.j ava 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:org.isource.util.CSVUtils.java
private static HSSFWorkbook evaluateFormulas(HSSFWorkbook wb) { FormulaEvaluator evaluator = null; evaluator = wb.getCreationHelper().createFormulaEvaluator(); for (int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) { Sheet sheet = wb.getSheetAt(sheetNum); for (Row r : sheet) { for (Cell c : r) { if (c.getCellType() == Cell.CELL_TYPE_FORMULA) { evaluator.evaluateFormulaCell(c); if (sheetNum == 0 && c.getColumnIndex() == r.getPhysicalNumberOfCells() - 1) { switch (c.getCachedFormulaResultType()) { case Cell.CELL_TYPE_NUMERIC: break; case Cell.CELL_TYPE_STRING: break; }/*from w w w.j av a 2 s . co m*/ } } } } } return wb; }
From source file:org.nuclos.server.report.export.ExcelExport.java
License:Open Source License
private NuclosFile export(Workbook wb, String sheetname, ResultVO result, List<ReportFieldDefinition> fields, String name) throws NuclosReportException { sheetname = sheetname != null ? sheetname : SpringLocaleDelegate.getInstance().getMessage("XLSExport.2", "Daten aus Nucleus"); Sheet s = wb.getSheet(sheetname);/*from w w w . ja v a 2 s . c o m*/ if (s == null) { s = wb.createSheet(sheetname); } int iRowNum = 0; int iColumnNum = 0; CreationHelper createHelper = wb.getCreationHelper(); Row row = getRow(s, 0); Map<Integer, CellStyle> styles = new HashMap<Integer, CellStyle>(); for (Iterator<ResultColumnVO> i = result.getColumns().iterator(); i.hasNext(); iColumnNum++) { i.next(); Cell cell = getCell(row, iColumnNum); cell.setCellValue(fields.get(iColumnNum).getLabel()); CellStyle style = wb.createCellStyle(); String f = getFormat(fields.get(iColumnNum)); if (f != null) { style.setDataFormat(createHelper.createDataFormat().getFormat(f)); } styles.put(iColumnNum, style); } iRowNum++; // export data for (int i = 0; i < result.getRows().size(); i++, iRowNum++) { iColumnNum = 0; Object[] dataRow = result.getRows().get(i); row = getRow(s, iRowNum); for (int j = 0; j < result.getColumns().size(); j++, iColumnNum++) { Object value = dataRow[j]; Cell c = getCell(row, iColumnNum); ReportFieldDefinition def = fields.get(j); if (value != null) { if (value instanceof List) { final StringBuilder sb = new StringBuilder(); for (Iterator<?> it = ((List<?>) value).iterator(); it.hasNext();) { final Object v = it.next(); sb.append(CollectableFieldFormat.getInstance(def.getJavaClass()) .format(def.getOutputformat(), v)); if (it.hasNext()) { sb.append(", "); } } c.setCellValue(sb.toString()); } else { if (Date.class.isAssignableFrom(def.getJavaClass())) { c.setCellStyle(styles.get(iColumnNum)); c.setCellValue((Date) value); } else if (Integer.class.isAssignableFrom(def.getJavaClass())) { c.setCellStyle(styles.get(iColumnNum)); c.setCellValue((Integer) value); } else if (Double.class.isAssignableFrom(def.getJavaClass())) { c.setCellStyle(styles.get(iColumnNum)); c.setCellValue((Double) value); } else { c.setCellValue(String.valueOf(value)); } } } else { c.setCellValue(""); } } } try { FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); for (int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) { Sheet sheet = wb.getSheetAt(sheetNum); for (Row r : sheet) { for (Cell c : r) { if (c.getCellType() == Cell.CELL_TYPE_FORMULA) { evaluator.evaluateFormulaCell(c); } } } } } catch (Exception e) { } // ignore any Exception ByteArrayOutputStream baos = new ByteArrayOutputStream(1024); try { wb.write(baos); return new NuclosFile(name + format.getExtension(), baos.toByteArray()); } catch (IOException e) { throw new NuclosReportException(e); } finally { try { baos.close(); } catch (IOException e) { } } }
From source file:org.openelis.bean.WorksheetExcelHelperBean.java
License:Open Source License
private Object getCellValue(Cell cell) { Object value;/* w w w . j a v a2 s .c o m*/ FormulaEvaluator eval; value = null; if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_FORMULA: eval = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator(); switch (eval.evaluateFormulaCell(cell)) { case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { value = new Datetime(Datetime.YEAR, Datetime.MINUTE, cell.getDateCellValue()); } else { cell.setCellType(Cell.CELL_TYPE_STRING); value = cell.getStringCellValue(); if (((String) value).trim().length() == 0) value = null; } break; case Cell.CELL_TYPE_STRING: value = cell.getStringCellValue(); if (((String) value).trim().length() == 0) value = null; break; } break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { value = new Datetime(Datetime.YEAR, Datetime.MINUTE, cell.getDateCellValue()); } else { cell.setCellType(Cell.CELL_TYPE_STRING); value = cell.getStringCellValue(); if (((String) value).trim().length() == 0) value = null; } break; case Cell.CELL_TYPE_STRING: value = cell.getStringCellValue(); if (((String) value).trim().length() == 0) value = null; break; } } return value; }