List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt
@Override public XSSFSheet getSheetAt(int index)
From source file:log_compressor.write_disk_space.java
public static void write_disk_space(HashMap<String, ArrayList<String>> map, List<String> server_list) throws FileNotFoundException, IOException { File myFile = new File("D:\\log\\log_output.xlsx"); FileInputStream fis = new FileInputStream(myFile); XSSFWorkbook myWorkBook = new XSSFWorkbook(fis); XSSFSheet mySheet = myWorkBook.getSheetAt(0); HashMap<String, String> res = new HashMap<String, String>(); ArrayList<String> key_set = new ArrayList<String>(); ArrayList<String> free_space = new ArrayList<String>(); BusinessOrInfra boi = new BusinessOrInfra(); int rownum = mySheet.getLastRowNum() + 1; Row row1 = mySheet.createRow(rownum++); Cell cell2 = row1.createCell(0);/*from ww w. java 2s .c o m*/ Date date = new Date(); cell2.setCellValue(date.toString()); int i = 0; boolean isInfra = boi.isInfrastructure(); for (String key : server_list) { free_space.clear(); ArrayList<String> disk_free = map.get(key); for (String df : disk_free) { if (!df.equals("need manual check")) { int free_position1 = df.lastIndexOf("GB"); int free_position2 = df.lastIndexOf("free"); String disk = df.substring(0, 1); String key_disk = key + ":" + disk; String free_space_percent = df.substring(free_position1 + 3, free_position2 - 1); free_space.add(free_space_percent); res.put(key_disk, free_space_percent); key_set.add(key_disk); } else { free_space.add("need manual check"); } } Row row = null; if (isInfra) { if (i != 22) { row = mySheet.createRow(rownum++); i++; } else { rownum = rownum + 2; row = mySheet.createRow(rownum++); i++; } } else { row = mySheet.createRow(rownum++); i++; } int cellnum = 0; Cell cell = row.createCell(cellnum++); cell.setCellValue(key); for (String val : free_space) { cellnum = cellnum + 1; Cell cell1 = row.createCell(cellnum); cell1.setCellValue(val); } } FileOutputStream os = new FileOutputStream(myFile); myWorkBook.write(os); }
From source file:log_compressor.write_xlsx.java
public static void write_xlsx(HashMap<String, List<String>> map, ArrayList<String> server_list) throws FileNotFoundException, IOException { File myFile = new File("D:\\log\\log_output.xlsx"); FileInputStream fis = new FileInputStream(myFile); XSSFWorkbook myWorkBook = new XSSFWorkbook(fis); XSSFSheet mySheet = myWorkBook.getSheetAt(0); BusinessOrInfra boi = new BusinessOrInfra(); boolean isInfra = boi.isInfrastructure(); int rownum = mySheet.getLastRowNum() + 1; Row row1 = mySheet.createRow(rownum++); Cell cell2 = row1.createCell(0);//from w w w . j a va 2 s.co m Date date = new Date(); cell2.setCellValue(date.toString()); int i = 0; for (String key : server_list) { Row row = null; if (isInfra) { if (i != 22) { row = mySheet.createRow(rownum++); i++; } else { rownum = rownum + 2; row = mySheet.createRow(rownum++); i++; } } else { row = mySheet.createRow(rownum++); i++; } int cellnum = 0; List<String> event = map.get(key); Cell cell = row.createCell(cellnum); cell.setCellValue(key); Cell cell1 = row.createCell(cellnum + 2); cell1.setCellValue(event.toString().substring(1, event.toString().length() - 1)); } FileOutputStream os = new FileOutputStream(myFile); myWorkBook.write(os); }
From source file:lp.XLSXhandler.java
public Object[] opener(File uploaded, String name) { double[][] data = new double[0][0]; String[] dmuNames = new String[0]; String[] variable_names = new String[0]; Object[] obj = new Object[5]; try {/*from ww w . j a va 2 s. co m*/ OPCPackage pkg = OPCPackage.open(uploaded); XSSFWorkbook wb = new XSSFWorkbook(pkg); XSSFSheet sheet1 = wb.getSheetAt(0); //I find the number of the rows in the file! (0-based) int rows = sheet1.getLastRowNum(); System.out.println("Total Rows of DATA in the file: " + rows); //I find the number of columns! (1-based) int columns = sheet1.getRow(0).getLastCellNum(); System.out.println("Total Columns of DATA in the file: " + columns); data = new double[rows][columns - 1]; dmuNames = new String[rows]; variable_names = new String[columns]; Row row_trav; Cell cell_trav; // Retrieve data from file to array for (int i = 0; i <= rows; i++) { row_trav = sheet1.getRow(i); for (int k = 0; k < columns; k++) { cell_trav = row_trav.getCell(k); if (i == 0) { //we are at line 0 of the uploaded file variable_names[k] = cell_trav.getStringCellValue(); } if (k == 0 && i < rows) { //we are at column 0 of the uploaded file Row row_name = sheet1.getRow(i + 1); cell_trav = row_name.getCell(0); dmuNames[i] = cell_trav.getStringCellValue(); } if (i > 0 && k > 0) { data[i - 1][k - 1] = cell_trav.getNumericCellValue(); } } } obj[0] = data; obj[1] = rows; obj[2] = columns; obj[3] = variable_names; obj[4] = dmuNames; } catch (InvalidFormatException e) { } catch (IOException e) { } return obj; }
From source file:lp.XLSXhandler.java
public boolean fileformat(File uploaded) { boolean f = true; try {//w w w . j a va 2 s. c om OPCPackage pkg = OPCPackage.open(uploaded); XSSFWorkbook wb = new XSSFWorkbook(pkg); XSSFSheet sheet1 = wb.getSheetAt(0); //I find the number of the rows in the file! (0-based) int rows = sheet1.getLastRowNum() + 1; //I find the number of columns! (1-based) int columns = sheet1.getRow(0).getLastCellNum(); /* * I will check only the data part! not the names of the DMUs */ Row row_check; Cell cell_check; for (int i = 1; i < rows; i++) { row_check = sheet1.getRow(i); for (int k = 1; k < columns; k++) { cell_check = row_check.getCell(k); /*If there is something else exept a number (0) * or excel function (2) */ int current = cell_check.getCellType(); if (current == 0 || current == 2) { } else { f = false; } } } } catch (InvalidFormatException e) { e.getMessage(); new Lp_first().cleanDir(); } catch (IOException e) { } return f; }
From source file:machinetoolstore.core.util.ExcelParser.java
public ThreeRollMill doExcelParse(String fileName) { String filePath = UPLOAD_LOCATION + fileName; File importFile = new File(filePath); ThreeRollMill entity = new ThreeRollMill(); try (FileInputStream fis = new FileInputStream(importFile)) { int counter = 0; XSSFWorkbook xssf = new XSSFWorkbook(fis); XSSFSheet xssfSheet = xssf.getSheetAt(0); entity.setCommonId((int) xssfSheet.getRow(counter++).getCell(1).getNumericCellValue()); entity.setType(xssfSheet.getRow(counter++).getCell(1).getStringCellValue()); entity.setModel(xssfSheet.getRow(counter++).getCell(1).getStringCellValue()); entity.setBrand(xssfSheet.getRow(counter++).getCell(1).getStringCellValue()); entity.setManufacturer(xssfSheet.getRow(counter++).getCell(1).getStringCellValue()); entity.setMachineState(xssfSheet.getRow(counter++).getCell(1).getStringCellValue()); entity.setYearOfIssue((int) xssfSheet.getRow(counter++).getCell(1).getNumericCellValue()); entity.setLocation(xssfSheet.getRow(counter++).getCell(1).getStringCellValue()); entity.setSales(xssfSheet.getRow(counter++).getCell(1).getStringCellValue()); entity.setMaterialThickness((int) xssfSheet.getRow(counter++).getCell(1).getNumericCellValue()); entity.setMaterialWidth((int) xssfSheet.getRow(counter++).getCell(1).getNumericCellValue()); entity.setBendingSpeed(xssfSheet.getRow(counter++).getCell(1).getNumericCellValue()); entity.setMinDiameterMaxBend((int) xssfSheet.getRow(counter++).getCell(1).getNumericCellValue()); entity.setTopRollDiameter((int) xssfSheet.getRow(counter++).getCell(1).getNumericCellValue()); entity.setMiddleRollDiameter((int) xssfSheet.getRow(counter++).getCell(1).getNumericCellValue()); entity.setDistanceOfBottomTwoRolls((int) xssfSheet.getRow(counter++).getCell(1).getNumericCellValue()); entity.setMaterialProofStress((int) xssfSheet.getRow(counter++).getCell(1).getNumericCellValue()); entity.setMainEnginePower(xssfSheet.getRow(counter++).getCell(1).getNumericCellValue()); entity.setMachineDimensions(xssfSheet.getRow(counter++).getCell(1).getStringCellValue()); entity.setMachineWeight((int) xssfSheet.getRow(counter++).getCell(1).getNumericCellValue()); entity.setMachinePrice((int) xssfSheet.getRow(counter++).getCell(1).getNumericCellValue()); entity.setDescription(xssfSheet.getRow(counter++).getCell(1).getStringCellValue()); entity.setMainPhoto(xssfSheet.getRow(counter).getCell(1).getStringCellValue()); ArrayList<ThreeRollMillPhoto> photoList = new ArrayList<>(); for (int i = 0; i < 5; i++) { if (!(xssfSheet.getRow(counter).getCell(1).getStringCellValue()).equals("")) { ThreeRollMillPhoto photo = new ThreeRollMillPhoto(); photo.setImageName(xssfSheet.getRow(counter).getCell(1).getStringCellValue()); photo.setThreeRollMill(entity); photoList.add(photo);//from ww w. j av a 2 s .c o m } counter++; } ArrayList<ThreeRollMillVideo> videoList = new ArrayList<>(); for (int i = 0; i < 5; i++) { if (!(xssfSheet.getRow(counter).getCell(1).getStringCellValue()).equals("")) { ThreeRollMillVideo video = new ThreeRollMillVideo(); video.setVideoUrl(xssfSheet.getRow(counter).getCell(1).getStringCellValue()); video.setThreeRollMill(entity); videoList.add(video); } counter++; } entity.setPhotoList(photoList); entity.setVideoList(videoList); } catch (Exception e) { e.printStackTrace(); } return entity; }
From source file:main.KeywordList.java
private List<Keyword> readFromExcel(String fileName) { List<Keyword> keywordList = new ArrayList(); try {/*ww w . j ava 2 s .c om*/ ForcastUi.consoleLog("Opening filename: " + fileName); FileInputStream fIP = openExcelFileOrCreate(fileName); //Get the workbook instance for XLSX file XSSFWorkbook workbook = new XSSFWorkbook(fIP); XSSFSheet spreadsheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = spreadsheet.iterator(); XSSFRow row; boolean firstLine = true; while (rowIterator.hasNext()) { String group = ""; //0 String packageName = ""; //1 String subpackageName = ""; //2 String positionType = ""; //3 String keyword = ""; //4 String date = "01/01/2000"; //5 String priority = "1"; //6 row = (XSSFRow) rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); // ? Cell cell = cellIterator.next(); group = cell.getStringCellValue(); if (firstLine) { firstLine = false; continue; } if (cellIterator.hasNext()) { cell = cellIterator.next(); packageName = getCellValue(cell); } if (cellIterator.hasNext()) { cell = cellIterator.next(); subpackageName = getCellValue(cell); } if (cellIterator.hasNext()) { cell = cellIterator.next(); positionType = getCellValue(cell); } if (cellIterator.hasNext()) { cell = cellIterator.next(); keyword = getCellValue(cell); } if (cellIterator.hasNext()) { cell = cellIterator.next(); date = getCellDate(cell); } if (cellIterator.hasNext()) { cell = cellIterator.next(); priority = getCellValue(cell); } //System.out.println("Keyword "+ keyword+"Date: "+date); keywordList.add( new Keyword(group, packageName, subpackageName, positionType, keyword, date, priority));//.add(new TopStockDescription(shortName.trim(),longName.trim(),explanation.trim(),false)); } fIP.close(); } catch (FileNotFoundException e) { ForcastUi.consoleLog(e.getMessage()); e.printStackTrace(); } catch (IOException e) { ForcastUi.consoleLog(e.getMessage()); e.printStackTrace(); } catch (Exception ex) { ForcastUi.consoleLog(ex.getMessage()); Logger.getLogger(TopStockDescriptionList.class.getName()).log(Level.SEVERE, null, ex); } return keywordList; }
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);/*from w ww . j a va 2 s . c o 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 {//w w w .j a v a 2 s . c om 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 w w w . jav a 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:mindbodymerge.Parser.java
/** * Parses the Sales data excel sheet that was passed in, and creates Array List populated with the member's names (memberList), the items the members * purchase (myzouItemList) and the price of the items that they paid (itemPriceList) * All array lists are indexed to the same person (i.e. memberList.get(0) bought myzouItemList.get(0) and paid itemPriceList.get(0)) * Parsing of the excel files are hard coded to the columns that held the correct data fields, and will need to be changed if the format of the excel * file changes/*from w ww. j a v a 2 s . c om*/ * * @param memberList: list of members who purchased items * @param myzouItemList: list of what the members purchased * @param itemPriceList: list of how much the member paid * * @return void * */ private void parseSales(ArrayList<String> memberList, ArrayList<String> myzouItemList, ArrayList<String> itemPriceList) { try { //Variable to be used in determining which columns of the excel file should be read from used in the while loop int iterationCount = 0; //Used for parsing of the item price into an item string Double itemNumber; String itemString; //Holds mocode String mocode; //Uses Apache POI .XLSX file parser to get the data, as treating it like a normal .CSV will give weird data //sales is the sales data.xlsx file FileInputStream fis = new FileInputStream(sales); Database db = new Database(); //Open WorkBook (.XLSX file) XSSFWorkbook myWorkBook = new XSSFWorkbook(fis); //Open first sheet of workbook, shouldn't have to change unless the formatting of the sales data.xlsx file changes to a different sheet XSSFSheet mySheet = myWorkBook.getSheetAt(0); //Iterator for moving through each row of the sheet Iterator<Row> rowIterator = mySheet.iterator(); //This first call of the iterator is to move the iterator past the labels at the top of the columns in the excel sheet rowIterator.next(); //Move through each row of the excel file while (rowIterator.hasNext()) { //Move to next row Row row = rowIterator.next(); //Iterator for the cells in the row Iterator<Cell> cellIterator = row.cellIterator(); //Reset the iterationCount to 0 so the while loop below knows what column we are in iterationCount = 0; while (cellIterator.hasNext()) { //Move to the next cell Cell cell = cellIterator.next(); //The second column (column B) holds the list of member names, so we read from that column to the memberList if (iterationCount == 1) { //Get rid of all the spaces so matching is easier memberList.add(cell.getStringCellValue().replaceAll("\\s", "")); } //The fourth column (column D) holds the list of purchased items, so we read from the column to the memberList else if (iterationCount == 3) { mocode = db.getMocode(cell.getStringCellValue()); myzouItemList.add(mocode); } //The 17th column (column Q) holds the list of amount paid for the items with tax //Make sure that you choose the column that holds the actual amount paid (e.g. the row with negative numbers showing credit charges and tax) //number is taken in as a double, and formatted as a string to be added else if (iterationCount == 16) { itemNumber = cell.getNumericCellValue(); itemString = String.format("%.2f", itemNumber); itemPriceList.add(itemString); } //Move counter to next cell iterationCount++; } } //Test block for ensuring the lists are correct, the sizes should all be equal // System.out.println(memberList.size()); // System.out.println(myzouItemList.size()); // System.out.println(itemPriceList.size()); // // System.out.println(memberList); // System.out.println(myzouItemList); // System.out.println(itemPriceList); } catch (IOException e) { } }