List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getNumberOfSheets
@Override public int getNumberOfSheets()
From source file:uk.co.spudsoft.birt.emitters.excel.tests.Issue87PrintSettings.java
License:Open Source License
@Test public void testPrintPagesXls() throws Exception { scale = false;//from w ww . jav a2s .c o m InputStream inputStream = runAndRenderReport("BigCrosstab.rptdesign", "xls"); assertNotNull(inputStream); try { HSSFWorkbook workbook = new HSSFWorkbook(inputStream); assertNotNull(workbook); assertEquals(1, workbook.getNumberOfSheets()); assertEquals(33, workbook.getNumCellStyles()); assertEquals("Big Crosstab Report 1", workbook.getSheetAt(0).getSheetName()); assertEquals(2, workbook.getSheetAt(0).getPrintSetup().getFitWidth()); assertEquals(3, workbook.getSheetAt(0).getPrintSetup().getFitHeight()); assertEquals(true, workbook.getSheetAt(0).getAutobreaks()); assertEquals(60, workbook.getSheetAt(0).getRow(1).getCell(2).getCellStyle().getRotation()); assertEquals(60, workbook.getSheetAt(0).getRow(2).getCell(2).getCellStyle().getRotation()); assertEquals(60, workbook.getSheetAt(0).getRow(2).getCell(3).getCellStyle().getRotation()); assertEquals(0, workbook.getSheetAt(0).getRow(3).getCell(2).getCellStyle().getRotation()); assertTrue(runTime - startTime < 4000L); assertTrue(renderTime - runTime < 4000L); Sheet sheet = workbook.getSheetAt(0); assertEquals(236, firstNullRow(sheet)); assertEquals(28, greatestNumColumns(sheet)); } finally { inputStream.close(); } }
From source file:uk.co.spudsoft.birt.emitters.excel.tests.Issue87PrintSettings.java
License:Open Source License
@Test public void testPrintScaleXls() throws Exception { scale = true;// ww w. ja va2 s.c om InputStream inputStream = runAndRenderReport("BigCrosstab.rptdesign", "xls"); assertNotNull(inputStream); try { HSSFWorkbook workbook = new HSSFWorkbook(inputStream); assertNotNull(workbook); assertEquals(1, workbook.getNumberOfSheets()); assertEquals(33, workbook.getNumCellStyles()); assertEquals("Big Crosstab Report 1", workbook.getSheetAt(0).getSheetName()); assertEquals(27, workbook.getSheetAt(0).getPrintSetup().getScale()); assertEquals(false, workbook.getSheetAt(0).getAutobreaks()); assertEquals(60, workbook.getSheetAt(0).getRow(1).getCell(2).getCellStyle().getRotation()); assertEquals(60, workbook.getSheetAt(0).getRow(2).getCell(2).getCellStyle().getRotation()); assertEquals(60, workbook.getSheetAt(0).getRow(2).getCell(3).getCellStyle().getRotation()); assertEquals(0, workbook.getSheetAt(0).getRow(3).getCell(2).getCellStyle().getRotation()); assertTrue(runTime - startTime < 4000L); assertTrue(renderTime - runTime < 4000L); Sheet sheet = workbook.getSheetAt(0); assertEquals(236, firstNullRow(sheet)); assertEquals(28, greatestNumColumns(sheet)); } finally { inputStream.close(); } }
From source file:uk.co.spudsoft.birt.emitters.excel.tests.Issue95ClickThroughHyperlinksTest.java
License:Open Source License
@Test public void testHyperlinksXls() throws BirtException, IOException { debug = false;/*from w ww . j a v a2 s. co m*/ InputStream inputStream = runAndRenderReport("Issue95ClickThroughHyperlinks.rptdesign", "xls"); assertNotNull(inputStream); try { HSSFWorkbook workbook = new HSSFWorkbook(inputStream); assertNotNull(workbook); assertEquals(1, workbook.getNumberOfSheets()); Sheet sheet = workbook.getSheetAt(0); assertEquals(2002, this.firstNullRow(sheet)); for (int i = 1; i < 2000; ++i) { assertEquals("http://www.spudsoft.co.uk/?p=" + i, sheet.getRow(i).getCell(0).getHyperlink().getAddress()); assertEquals( "run?__report=Issue95ClickThroughHyperlinks.rptdesign&Pointless=" + i + "&__overwrite=true", sheet.getRow(i).getCell(1).getHyperlink().getAddress()); } } finally { inputStream.close(); } }
From source file:uk.co.spudsoft.birt.emitters.excel.tests.NestedTables2ReportTest.java
License:Open Source License
@Test public void testRunReportXls() throws BirtException, IOException { InputStream inputStream = runAndRenderReport("NestedTables2.rptdesign", "xls"); assertNotNull(inputStream);/* www . j av a 2s . c om*/ try { HSSFWorkbook workbook = new HSSFWorkbook(inputStream); assertNotNull(workbook); assertEquals(1, workbook.getNumberOfSheets()); assertEquals("Nested Tables Test Report", workbook.getSheetAt(0).getSheetName()); Sheet sheet = workbook.getSheetAt(0); assertEquals(12, firstNullRow(sheet)); assertEquals(1, sheet.getRow(0).getCell(0).getNumericCellValue(), 0.0); assertEquals(1, sheet.getRow(0).getCell(1).getNumericCellValue(), 0.0); assertEquals(2, sheet.getRow(1).getCell(0).getNumericCellValue(), 0.0); assertEquals(2, sheet.getRow(1).getCell(1).getNumericCellValue(), 0.0); assertEquals(3, sheet.getRow(2).getCell(0).getNumericCellValue(), 0.0); assertEquals(3, sheet.getRow(2).getCell(1).getNumericCellValue(), 0.0); assertEquals(1, sheet.getRow(3).getCell(0).getNumericCellValue(), 0.0); assertEquals(2, sheet.getRow(3).getCell(1).getNumericCellValue(), 0.0); assertEquals(3, sheet.getRow(3).getCell(2).getNumericCellValue(), 0.0); assertEquals(2, sheet.getRow(4).getCell(0).getNumericCellValue(), 0.0); assertEquals(2, sheet.getRow(4).getCell(1).getNumericCellValue(), 0.0); assertEquals(4, sheet.getRow(5).getCell(0).getNumericCellValue(), 0.0); assertEquals(4, sheet.getRow(5).getCell(1).getNumericCellValue(), 0.0); assertEquals(6, sheet.getRow(6).getCell(0).getNumericCellValue(), 0.0); assertEquals(6, sheet.getRow(6).getCell(1).getNumericCellValue(), 0.0); assertEquals(2, sheet.getRow(7).getCell(0).getNumericCellValue(), 0.0); assertEquals(4, sheet.getRow(7).getCell(1).getNumericCellValue(), 0.0); assertEquals(6, sheet.getRow(7).getCell(2).getNumericCellValue(), 0.0); assertEquals(3, sheet.getRow(8).getCell(0).getNumericCellValue(), 0.0); assertEquals(3, sheet.getRow(8).getCell(1).getNumericCellValue(), 0.0); assertEquals(6, sheet.getRow(9).getCell(0).getNumericCellValue(), 0.0); assertEquals(6, sheet.getRow(9).getCell(1).getNumericCellValue(), 0.0); assertEquals(9, sheet.getRow(10).getCell(0).getNumericCellValue(), 0.0); assertEquals(9, sheet.getRow(10).getCell(1).getNumericCellValue(), 0.0); assertEquals(3, sheet.getRow(11).getCell(0).getNumericCellValue(), 0.0); assertEquals(6, sheet.getRow(11).getCell(1).getNumericCellValue(), 0.0); assertEquals(9, sheet.getRow(11).getCell(2).getNumericCellValue(), 0.0); short bgColour = ((HSSFCell) sheet.getRow(0).getCell(0)).getCellStyle().getFillBackgroundColor(); assertEquals("0:0:0", workbook.getCustomPalette().getColor(bgColour).getHexString()); short baseColour = workbook .getFontAt(((HSSFCell) sheet.getRow(0).getCell(0)).getCellStyle().getFontIndex()).getColor(); assertEquals("FFFF:FFFF:FFFF", workbook.getCustomPalette().getColor(baseColour).getHexString()); } finally { inputStream.close(); } }
From source file:uk.co.spudsoft.birt.emitters.excel.tests.NestedTablesSingleColumnGridReportTest.java
License:Open Source License
@Test public void testRunReportXls() throws BirtException, IOException { InputStream inputStream = runAndRenderReport("NestedTablesSingleColumnGrid.rptdesign", "xls"); assertNotNull(inputStream);//from ww w. ja v a2s . c om try { HSSFWorkbook workbook = new HSSFWorkbook(inputStream); assertNotNull(workbook); assertEquals(1, workbook.getNumberOfSheets()); assertEquals("Nested Tables Test Report", workbook.getSheetAt(0).getSheetName()); Sheet sheet = workbook.getSheetAt(0); assertEquals(10, firstNullRow(sheet)); } finally { inputStream.close(); } }
From source file:uk.co.spudsoft.birt.emitters.excel.tests.PageLayoutTest.java
License:Open Source License
@Test public void testRunReportXls() throws BirtException, IOException { InputStream inputStream = runAndRenderReport("PageLayout.rptdesign", "xls"); assertNotNull(inputStream);//w w w . j a v a 2 s.c om try { HSSFWorkbook workbook = new HSSFWorkbook(inputStream); assertNotNull(workbook); assertEquals(1, workbook.getNumberOfSheets()); assertEquals("Page Layout Test", workbook.getSheetAt(0).getSheetName()); HSSFSheet sheet0 = workbook.getSheetAt(0); HSSFPrintSetup printSetup = sheet0.getPrintSetup(); assertEquals(HSSFPrintSetup.A4_PAPERSIZE, printSetup.getPaperSize()); assertEquals(true, printSetup.getLandscape()); assertEquals(1.0 / 2.54, printSetup.getHeaderMargin(), 0.01); assertEquals(1.0 / 2.54, printSetup.getFooterMargin(), 0.01); assertEquals(0.7 / 2.54, sheet0.getMargin(Sheet.LeftMargin), 0.01); assertEquals(0.7 / 2.54, sheet0.getMargin(Sheet.RightMargin), 0.01); assertEquals(1.7 / 2.54, sheet0.getMargin(Sheet.TopMargin), 0.01); assertEquals(1.7 / 2.54, sheet0.getMargin(Sheet.BottomMargin), 0.01); } finally { inputStream.close(); } }
From source file:uk.co.spudsoft.birt.emitters.excel.tests.PageLayoutTest.java
License:Open Source License
@Test public void testRunReportPixelsXls() throws BirtException, IOException { InputStream inputStream = runAndRenderReport("PageLayoutPixels.rptdesign", "xls"); assertNotNull(inputStream);/*from ww w . j a v a 2s . c om*/ try { HSSFWorkbook workbook = new HSSFWorkbook(inputStream); assertNotNull(workbook); assertEquals(1, workbook.getNumberOfSheets()); assertEquals("Page Layout Test", workbook.getSheetAt(0).getSheetName()); HSSFSheet sheet0 = workbook.getSheetAt(0); HSSFPrintSetup printSetup = sheet0.getPrintSetup(); assertEquals(HSSFPrintSetup.A4_PAPERSIZE, printSetup.getPaperSize()); assertEquals(true, printSetup.getLandscape()); assertEquals(0.5, printSetup.getHeaderMargin(), 0.01); assertEquals(0.5, printSetup.getFooterMargin(), 0.01); assertEquals(0.75, sheet0.getMargin(Sheet.LeftMargin), 0.01); assertEquals(0.75, sheet0.getMargin(Sheet.RightMargin), 0.01); assertEquals(1.0, sheet0.getMargin(Sheet.TopMargin), 0.01); assertEquals(1.0, sheet0.getMargin(Sheet.BottomMargin), 0.01); } finally { inputStream.close(); } }
From source file:util.DocumentFunction.java
public static String readXlsFile(String filename) { StringBuilder text = new StringBuilder(); try {//from ww w.j av a2 s. com FileInputStream file = new FileInputStream(new File(filename)); //Create Workbook instance holding reference to .xlsx file HSSFWorkbook workbook = new HSSFWorkbook(file); //Get first/desired sheet from the workbook for (int i = 0; i < workbook.getNumberOfSheets(); i++) { HSSFSheet sheet = workbook.getSheetAt(i); //Iterate through each rows one by one Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); //For each row, iterate through all the columns Iterator<Cell> cellIterator = row.cellIterator(); boolean breakPoint = true; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); //Check the cell type and format accordingly switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: text.append(cell.getNumericCellValue() + " "); break; case Cell.CELL_TYPE_STRING: text.append(cell.getStringCellValue() + " "); break; case Cell.CELL_TYPE_BLANK: breakPoint = false; break; } } if (breakPoint) { text.append("\n"); } } } file.close(); } catch (Exception e) { e.printStackTrace(); } return text.toString(); }
From source file:velo.importer.AccountsList.java
License:Open Source License
public void importFromXls(InputStream is, String spreadSheetName) throws Exception { HSSFWorkbook workbook = new HSSFWorkbook(is); //Count the number of sheets if (workbook.getNumberOfSheets() < 1) { throw new Exception("Number of sheets in excel is less than 1!"); }/*from w w w . j ava2 s . co m*/ HSSFSheet sheet = workbook.getSheet(spreadSheetName); if (sheet == null) { throw new Exception("Could not find sheet named '" + spreadSheetName + "'"); } //todo 3 cells at least! //Make sure the headers correspond to the expected values HSSFRow header = sheet.getRow(0); HSSFCell accountNameTitle = header.getCell((short) 0); HSSFCell targetNameTitle = header.getCell((short) 1); if (!accountNameTitle.toString().equalsIgnoreCase("ACCOUNT")) { throw new Exception( "Column one in first row must equal to 'ACCOUNT' and represents the Account to associate"); } if (!targetNameTitle.toString().equalsIgnoreCase("RESOURCE_UNIQUE_NAME")) { throw new Exception( "Column one in first row must equal to 'RESOURCE_UNIQUE_NAME' and represents the resource unique name the account is related to!"); } for (int i = 1; i <= sheet.getLastRowNum(); i++) { HSSFRow row = sheet.getRow(i); HSSFCell accountName = row.getCell((short) 0); HSSFCell targetName = row.getCell((short) 1); String targetNameErrMsg = "Target Name at row # '" + i + "' is empty!"; if (targetName == null) { throw new Exception(targetNameErrMsg); } else if (targetName.toString().length() < 1) { throw new Exception(targetNameErrMsg); } String accountErrMsg = "Account Name at row # '" + i + "' is empty!"; if (accountName == null) { throw new Exception(accountErrMsg); } else if (accountName.toString().length() < 1) { throw new Exception(accountErrMsg); } System.out.println("Row(" + i + ") - Account: '" + accountName.toString() + "', On Target: '" + targetName.toString() + "'"); velo.importer.ImportAccount ia = new velo.importer.ImportAccount(); ia.setResourceName(targetName.toString()); ia.setAccountName(accountName.toString()); this.add(ia); } }
From source file:velo.importer.AccountsToUsersList.java
License:Open Source License
public void importFromXls(InputStream is, String spreadSheetName) throws Exception { HSSFWorkbook workbook = new HSSFWorkbook(is); //Count the number of shits if (workbook.getNumberOfSheets() < 1) { throw new Exception("Number of sheets in excel is less than 1!"); }//from www. jav a2 s . c om HSSFSheet sheet = workbook.getSheet(spreadSheetName); if (sheet == null) { throw new Exception("Could not find sheet named '" + spreadSheetName + "'"); } //todo 3 cells at least! //Make sure the headers correspond to the expected values HSSFRow header = sheet.getRow(0); HSSFCell userNameTitle = header.getCell((short) 0); HSSFCell accountNameTitle = header.getCell((short) 1); HSSFCell targetNameTitle = header.getCell((short) 2); if (!userNameTitle.toString().equalsIgnoreCase("USER")) { throw new Exception( "Column one in first row must equal to 'USER' and represents the User that owns the account!"); } if (!accountNameTitle.toString().equalsIgnoreCase("ACCOUNT")) { throw new Exception( "Column one in first row must equal to 'ACCOUNT' and represents the Account to associate"); } if (!targetNameTitle.toString().equalsIgnoreCase("RESOURCE_UNIQUE_NAME")) { throw new Exception( "Column one in first row must equal to 'TARGET-SYSTEM' and represents the resource unique name the account is related to!"); } for (int i = 1; i <= sheet.getLastRowNum(); i++) { HSSFRow row = sheet.getRow(i); HSSFCell userName = row.getCell((short) 0); HSSFCell accountName = row.getCell((short) 1); HSSFCell targetName = row.getCell((short) 2); String targetNameErrMsg = "Target Name at row # '" + i + "' is empty!"; if (targetName == null) { throw new Exception(targetNameErrMsg); } else if (targetName.toString().length() < 1) { throw new Exception(targetNameErrMsg); } String userNameErrMsg = "User Name at row # '" + i + "' is empty!"; if (userName == null) { throw new Exception(userNameErrMsg); } else if (userName.toString().length() < 1) { throw new Exception(userNameErrMsg); } String accountErrMsg = "Account Name at row # '" + i + "' is empty!"; if (accountName == null) { throw new Exception(accountErrMsg); } else if (accountName.toString().length() < 1) { throw new Exception(accountErrMsg); } //System.out.println("Row("+i+") - User: '" + userName.toString() + "', Account: '" + accountName.toString() + "', On Target: '" + targetName.toString() + "'"); velo.importer.ImportAccountToUser iatu = new velo.importer.ImportAccountToUser(); iatu.setUserName(userName.toString()); iatu.setResourceName(targetName.toString()); iatu.setAccountName(accountName.toString()); this.add(iatu); } }