List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getNumberOfSheets
@Override public int getNumberOfSheets()
From source file:FileHelper.ExcelHelper.java
public SheetsOfFile ReadSheetsOfFile(String fileName) { try {//from ww w .j a va 2s . c om SheetsOfFile sheetsOfFile = new SheetsOfFile(); ArrayList<String> sheets = new ArrayList<String>(); File excel = new File(fileName); FileInputStream fis = new FileInputStream(excel); XSSFWorkbook book = new XSSFWorkbook(fis); int size = book.getNumberOfSheets(); if (size > 1) { for (int i = 0; i < size; i++) { XSSFSheet sheet = book.getSheetAt(i); String str = sheet.getSheetName(); sheets.add(str); } } sheetsOfFile.setSheets(sheets); return sheetsOfFile; } catch (Throwable t) { System.out.println("Throwsable: " + t.getMessage()); return new SheetsOfFile(); } }
From source file:FormatConvert.exceloperation.Excel2csv.java
public static void SeperatorExcel2sheet(String excelfile, String targetdir) { try {/* w w w . ja v a2s. c o m*/ FileInputStream is = (new FileInputStream(excelfile)); if (excelfile.endsWith(".xlsx")) { XSSFWorkbook wb = new XSSFWorkbook(is); int sheetnub = wb.getNumberOfSheets(); for (int i = 0; i < sheetnub; i++) { XSSFSheet sheet = wb.getSheetAt(i); if (sheet.toString() != null) { String temfile = targetdir + "\\" + sheet.getSheetName() + ".csv"; Excel2csv.copySheets2CSV(sheet, temfile); } } } else { HSSFWorkbook wb = new HSSFWorkbook(is); int sheetnub = wb.getNumberOfSheets(); for (int i = 0; i < sheetnub; i++) { HSSFSheet sheet = wb.getSheetAt(i); if (sheet.toString() != null) { String temfile = targetdir + "\\" + sheet.getSheetName() + ".csv"; Excel2csv.copySheets2CSV(sheet, temfile); } } } } catch (Exception ioe) { ioe.printStackTrace(); } }
From source file:FormatConvert.exceloperation.ExcelOperation.java
public static void SeperatorExcel2sheet(String excelfile, String targetdir) { try {/* www . ja v a 2 s . c o m*/ FileInputStream is = (new FileInputStream(excelfile)); if (excelfile.endsWith(".xlsx")) { XSSFWorkbook wb = new XSSFWorkbook(is); int sheetnub = wb.getNumberOfSheets(); for (int i = 0; i < sheetnub; i++) { XSSFSheet sheet = wb.getSheetAt(i); if (sheet.toString() != null) { Workbook wb2 = new XSSFWorkbook(); XSSFSheet tempsheet = (XSSFSheet) wb2.createSheet(); Util.copySheets(tempsheet, sheet, true); //tempsheet=wb.cloneSheet(i); //tempsheet = sheet; String temfile = targetdir + "\\" + sheet.getSheetName() + ".xlsx"; FileOutputStream fileOut = new FileOutputStream(temfile); wb2.write(fileOut); fileOut.close(); } } } else { HSSFWorkbook wb = new HSSFWorkbook(is); int sheetnub = wb.getNumberOfSheets(); for (int i = 0; i < sheetnub; i++) { HSSFSheet sheet = wb.getSheetAt(i); if (sheet.toString() != null) { Workbook wb2 = new HSSFWorkbook(); HSSFSheet tempsheet = (HSSFSheet) wb2.createSheet(); Util.copySheets(tempsheet, sheet, true); //tempsheet=wb.cloneSheet(i); //tempsheet = sheet; String temfile = targetdir + "\\" + sheet.getSheetName() + ".xlsx"; FileOutputStream fileOut = new FileOutputStream(temfile); wb2.write(fileOut); fileOut.close(); } } } } catch (Exception ioe) { ioe.printStackTrace(); } }
From source file:graphene.hts.file.ExcelXSSFToJSONConverter.java
License:Apache License
public Map<String, List> convert(final File f) throws Exception, IOException { FileInputStream fis = null;/*from w w w .j a v a 2s . com*/ final Map<String, List> workbookConversion = new TreeMap<String, List>(); try { final String ext = FileUtils.getFileExtension(f.getName()); boolean success = false; logger.debug("Ext " + ext); /** * For modern excel files */ if (".xls".equalsIgnoreCase(ext) || ".xlsx".equalsIgnoreCase(ext)) { fis = new FileInputStream(f); try { logger.debug("Trying to open file " + f.getAbsolutePath() + " as an XSSF document."); final XSSFWorkbook workbook = new XSSFWorkbook(fis); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { workbookConversion.put(workbook.getSheetAt(i).getSheetName(), internalConvert(workbook.getSheetAt(i).iterator())); } success = true; } catch (final Exception e) { logger.error("Could not open file " + f.getAbsolutePath() + " as an XSSF document.", e); } finally { if (ValidationUtils.isValid(fis)) { fis.close(); } } } /** * For older excel files */ if (!success && ".xls".equalsIgnoreCase(FileUtils.getFileExtension(f.getName()))) { fis = new FileInputStream(f); try { logger.debug("Trying to open file " + f.getAbsolutePath() + " as an HSSF document."); final HSSFWorkbook workbook = new HSSFWorkbook(fis); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { workbookConversion.put(workbook.getSheetAt(i).getSheetName(), internalConvert(workbook.getSheetAt(i).iterator())); } } catch (final Exception e) { logger.error("Could not open file " + f.getAbsolutePath() + " as an HSSF document.", e); } finally { if (ValidationUtils.isValid(fis)) { fis.close(); } } } } finally { if (ValidationUtils.isValid(fis)) { fis.close(); } } return workbookConversion; }
From source file:helpers.Excel.ExcelDataFormat.java
private Object unmarshalXLSX(Exchange exchng, InputStream in) throws Exception { XSSFWorkbook workbook = new XSSFWorkbook(in); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); //Get first sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); if (importType != ImportType.FORMATTED) { return marshalAsArray(sheet.iterator()); } else {//from w ww . j ava2 s.co m OneExcel excel = new OneExcel(); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { OneExcelSheet onesheet = marshalAsStructure(workbook.getSheetAt(i).iterator(), evaluator); logger.info("Loading sheet:" + i); logger.info("Data:" + onesheet.data.size()); if (onesheet.data.size() > 0) excel.sheets.add(onesheet); } logger.info("Total sheets:" + excel.sheets.size()); ArrayList<HashMap<String, Object>> resu = excel.GenerateResult(); HashMap<String, Object> mappings = excel.GenerateMappings(); exchng.getOut().setHeader("mappings", mappings); exchng.getOut().setHeader("xlsdata", resu); return resu; } }
From source file:in.expertsoftware.colorcheck.VerifyTokens.java
/** * //from w w w.j a va2 s . c o m * Start is an initiate function of VerifyTokens * @param workbook an instance of XSSFWorkbook * @return ArrayList of type Error and Token class */ public ArrayList start(XSSFWorkbook workbook) { int NumberOfSheets = workbook.getNumberOfSheets(); String[] SheetName = new String[NumberOfSheets]; System.out.println("Get Sheets from workwook"); /*for(int i=0;i<NumberOfSheets;i++) { SheetName[i]=workbook.getSheetName(i); } */ System.out.println("Tokens"); System.out.println(Arrays.deepToString(Tokens)); System.out.println("Check Tokens from Workbook"); ArrayList errorAndTokenList = verify_tokens(NumberOfSheets, workbook); return errorAndTokenList; }
From source file:iscas.tca.ake.demoapp.mvc.module.tools.fileoperator.ExcelBasic.java
License:Open Source License
/** * TODO:<>/*from w ww. j a v a 2 s . c o m*/ * @param xwb * @param sheetName * @return */ public static boolean isSheetExists(XSSFWorkbook xwb, String sheetName) { int num = xwb.getNumberOfSheets(); for (int i = 0; i < num; i++) { if (xwb.getSheetName(i).equals(sheetName)) return true; } return false; }
From source file:javaapp.ReadExcelFile.java
public static void main(String[] args) { FileInputStream fileInputStream = null; try {//from w w w . j a v a2 s . c o m fileInputStream = new FileInputStream("GBRCNCOR.xlsx"); XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream); // for each sheet in the workbook for (int i = 0; i < workbook.getNumberOfSheets(); i++) { System.out.println("Sheet name: " + workbook.getSheetName(i)); } } catch (IOException e) { e.printStackTrace(); } finally { if (fileInputStream != null) { try { fileInputStream.close(); } catch (IOException e) { e.printStackTrace(); } } } }
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 ww w .j av a 2s. 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 {//from w ww . j av a 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); } }