List of usage examples for org.apache.poi.ss.usermodel Sheet getSheetName
String getSheetName();
From source file:CellStyleDetails.java
License:Apache License
public static void main(String[] args) throws Exception { if (args.length == 0) { throw new IllegalArgumentException("Filename must be given"); }/*from w w w.j av a2 s .co m*/ Workbook wb = WorkbookFactory.create(new File(args[0])); DataFormatter formatter = new DataFormatter(); for (int sn = 0; sn < wb.getNumberOfSheets(); sn++) { Sheet sheet = wb.getSheetAt(sn); System.out.println("Sheet #" + sn + " : " + sheet.getSheetName()); for (Row row : sheet) { System.out.println(" Row " + row.getRowNum()); for (Cell cell : row) { CellReference ref = new CellReference(cell); System.out.print(" " + ref.formatAsString()); System.out.print(" (" + cell.getColumnIndex() + ") "); CellStyle style = cell.getCellStyle(); System.out.print("Format=" + style.getDataFormatString() + " "); System.out.print("FG=" + renderColor(style.getFillForegroundColorColor()) + " "); System.out.print("BG=" + renderColor(style.getFillBackgroundColorColor()) + " "); Font font = wb.getFontAt(style.getFontIndex()); System.out.print("Font=" + font.getFontName() + " "); System.out.print("FontColor="); if (font instanceof HSSFFont) { System.out.print(renderColor(((HSSFFont) font).getHSSFColor((HSSFWorkbook) wb))); } if (font instanceof XSSFFont) { System.out.print(renderColor(((XSSFFont) font).getXSSFColor())); } System.out.println(); System.out.println(" " + formatter.formatCellValue(cell)); } } System.out.println(); } }
From source file:adams.data.io.input.ExcelSpreadSheetReader.java
License:Open Source License
/** * Reads the spreadsheet content from the specified file. * * @param in the input stream to read from * @return the spreadsheets or null in case of an error *//*from ww w. ja v a 2 s .c o m*/ @Override protected List<SpreadSheet> doReadRange(InputStream in) { List<SpreadSheet> result; int[] indices; Workbook workbook; Sheet sheet; SpreadSheet spsheet; Row exRow; Cell exCell; adams.data.spreadsheet.Row spRow; int i; int n; int cellType; DateFormat dformat; boolean numeric; int dataRowStart; int firstRow; int lastRow; List<String> header; result = new ArrayList<>(); workbook = null; dformat = DateUtils.getTimestampFormatter(); try { workbook = WorkbookFactory.create(in); m_SheetRange.setMax(workbook.getNumberOfSheets()); indices = m_SheetRange.getIntIndices(); firstRow = m_FirstRow - 1; dataRowStart = getNoHeader() ? firstRow : firstRow + 1; for (int index : indices) { if (m_Stopped) break; spsheet = m_SpreadSheetType.newInstance(); spsheet.setDataRowClass(m_DataRowType.getClass()); result.add(spsheet); if (isLoggingEnabled()) getLogger().info("sheet: " + (index + 1)); sheet = workbook.getSheetAt(index); if (sheet.getLastRowNum() == 0) { getLogger().severe("No rows in sheet #" + index); return null; } spsheet.setName(sheet.getSheetName()); // header if (isLoggingEnabled()) getLogger().info("header row"); exRow = sheet.getRow(firstRow); if (exRow == null) { getLogger().warning("No data in sheet #" + (index + 1) + "?"); } else if (exRow != null) { spRow = spsheet.getHeaderRow(); m_TextColumns.setMax(exRow.getLastCellNum()); if (getNoHeader()) { header = SpreadSheetUtils.createHeader(exRow.getLastCellNum(), m_CustomColumnHeaders); for (i = 0; i < header.size(); i++) spRow.addCell("" + (i + 1)).setContent(header.get(i)); } else { if (!m_CustomColumnHeaders.trim().isEmpty()) { header = SpreadSheetUtils.createHeader(exRow.getLastCellNum(), m_CustomColumnHeaders); for (i = 0; i < header.size(); i++) spRow.addCell("" + (i + 1)).setContent(header.get(i)); } else { for (i = 0; i < exRow.getLastCellNum(); i++) { if (m_Stopped) break; exCell = exRow.getCell(i); if (exCell == null) { spRow.addCell("" + (i + 1)).setMissing(); continue; } numeric = !m_TextColumns.isInRange(i); switch (exCell.getCellType()) { case Cell.CELL_TYPE_BLANK: case Cell.CELL_TYPE_ERROR: spRow.addCell("" + (i + 1)).setContent("column-" + (i + 1)); break; case Cell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(exCell)) spRow.addCell("" + (i + 1)).setContent(new DateTime( HSSFDateUtil.getJavaDate(exCell.getNumericCellValue()))); else if (numeric) spRow.addCell("" + (i + 1)).setContent(exCell.getNumericCellValue()); else spRow.addCell("" + (i + 1)).setContentAsString(numericToString(exCell)); break; default: spRow.addCell("" + (i + 1)).setContentAsString(exCell.getStringCellValue()); } } } } } // data if (spsheet.getColumnCount() > 0) { if (m_NumRows < 1) lastRow = sheet.getLastRowNum(); else lastRow = Math.min(firstRow + m_NumRows - 1, sheet.getLastRowNum()); for (i = dataRowStart; i <= lastRow; i++) { if (m_Stopped) break; if (isLoggingEnabled()) getLogger().info("data row: " + (i + 1)); spRow = spsheet.addRow("" + spsheet.getRowCount()); exRow = sheet.getRow(i); if (exRow == null) continue; for (n = 0; n < exRow.getLastCellNum(); n++) { // too few columns in header? if ((n >= spsheet.getHeaderRow().getCellCount()) && m_AutoExtendHeader) spsheet.insertColumn(spsheet.getColumnCount(), ""); m_TextColumns.setMax(spsheet.getHeaderRow().getCellCount()); exCell = exRow.getCell(n); if (exCell == null) { spRow.addCell(n).setMissing(); continue; } cellType = exCell.getCellType(); if (cellType == Cell.CELL_TYPE_FORMULA) cellType = exCell.getCachedFormulaResultType(); numeric = !m_TextColumns.isInRange(n); switch (cellType) { case Cell.CELL_TYPE_BLANK: case Cell.CELL_TYPE_ERROR: if (m_MissingValue.isEmpty()) spRow.addCell(n).setMissing(); else spRow.addCell(n).setContent(""); break; case Cell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(exCell)) spRow.addCell(n).setContent( dformat.format(HSSFDateUtil.getJavaDate(exCell.getNumericCellValue()))); else if (numeric) spRow.addCell(n).setContent(exCell.getNumericCellValue()); else spRow.addCell(n).setContentAsString(numericToString(exCell)); break; default: if (m_MissingValue.isMatch(exCell.getStringCellValue())) spRow.addCell(n).setMissing(); else spRow.addCell(n).setContentAsString(exCell.getStringCellValue()); } } } } } } catch (Exception ioe) { getLogger().log(Level.SEVERE, "Failed to read range '" + m_SheetRange + "':", ioe); result = null; m_LastError = "Failed to read range '" + m_SheetRange + "' from stream!\n" + Utils.throwableToString(ioe); } return result; }
From source file:athena.Controller.java
License:Open Source License
private void convertExceltoCSV(String inputFile, String outputFilePath) { InputStream inp = null;/*from ww w. j av a 2 s . c o m*/ try { inp = new FileInputStream(inputFile); wb = new XSSFWorkbook(inp); for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet thisSheet = wb.getSheetAt(i); int rowEnd = Math.max(1400, thisSheet.getLastRowNum()); view.setOutput("Writting.. " + thisSheet.getSheetName()); String csvRawString = ""; String outputFileName = outputFilePath + thisSheet.getSheetName() + ".csv"; try { OutputStream os; File testFile = new File(outputFileName); if (testFile.exists() && !testFile.isDirectory()) { os = new FileOutputStream(outputFilePath + thisSheet.getSheetName() + "(1).csv"); } else { os = new FileOutputStream(outputFileName); } PrintWriter w = new PrintWriter(new OutputStreamWriter(os, "UTF-8")); for (int j = 0; j < rowEnd; j++) { Row row = thisSheet.getRow(j); if (row != null) { for (int k = 0; k < row.getLastCellNum(); k++) { if (k == (row.getLastCellNum() - 1)) { if (row.getCell(k) != null) { csvRawString = csvRawString + row.getCell(k); } } else { if (row.getCell(k) == null) { csvRawString = csvRawString + ","; } else { csvRawString = csvRawString + row.getCell(k) + ","; } } } } else { csvRawString = csvRawString + ","; } csvRawString = csvRawString + "\n"; w.print(csvRawString); w.flush(); csvRawString = ""; } w.close(); view.setOutput("Done.. " + thisSheet.getSheetName()); } catch (FileNotFoundException e) { view.setOutput("I'm confused.. File not found!"); } catch (UnsupportedEncodingException e) { view.setOutput("Call 911.. or Jake"); } } } catch (IOException e) { view.setOutput("Uh oh.. Fail to read file!"); } finally { try { inp.close(); view.setOutput("Done conversion.. " + model.getInputFilePath() + "\n"); model.setInputFilePath(null); model.setOutputFilePath(null); view.refreshIntputPath(); view.refreshOutputPath(); } catch (IOException e) { view.setOutput("Damn input stream.."); } } }
From source file:au.com.onegeek.lambda.parser.Excel2SeleniumParser.java
License:Apache License
private void parse(InputStream stream) throws CannotCompileException, NotFoundException, CannotCreateTestClassException, InstantiationException, IllegalAccessException, IllegalArgumentException, InvocationTargetException { logger.debug("Parsing..."); if (this.dataMap != null && this.tests != null) { return;/*from w ww.j a v a2 s.c om*/ } this.dataMap = new ArrayList<Map<String, Object>>(); this.tests = new ArrayList<Class<Test>>(); Workbook workbook = null; try { workbook = new XSSFWorkbook(stream); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } logger.debug("workbook" + workbook.toString()); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { Sheet sheet = workbook.getSheetAt(i); if (sheet.getSheetName().startsWith("data")) { // parse key\value pairs HashMap<String, Object> map = new HashMap<String, Object>(); dataMap.add(map); boolean done = false; Row row = sheet.getRow(sheet.getFirstRowNum()); while (!done && row != null && row.getPhysicalNumberOfCells() > 0) { // TODO: parse numerics correctly (i.e. don't add decimal points if not needed) String key = (String) XslxUtil.objectFrom(workbook, row.getCell(0)); String value = null; try { value = (String) XslxUtil.objectFrom(workbook, row.getCell(1)); logger.debug("Adding variable to map: " + key + ":" + value); map.put(key, value); row = sheet.getRow(row.getRowNum() + 1); if (row == null || (row.getRowNum() == sheet.getLastRowNum() + 1)) { done = true; } } catch (NullPointerException e) { //throw new CannotCreateVariableException("No value found for variable '" + key + "' in dataset: " + sheet.getSheetName()); done = true; } } } } JavassistTestBuilderImpl builder = JavassistTestBuilderImpl.getInstance(); // Parse Test sheets into Test objects for (int s = 0; s < workbook.getNumberOfSheets(); s++) { Sheet sheet = workbook.getSheetAt(s); int i = 0; // Ignore data sheets if (sheet.getSheetName().startsWith("suite")) { int maxRows = sheet.getPhysicalNumberOfRows(); int currentRow = sheet.getFirstRowNum(); logger.debug("Nr rows in sheet: " + maxRows); // Create Test Class String testCaseName = "Test" + Excel2SeleniumParser.toCamelCase(sheet.getSheetName()); logger.debug("Creating Test class with name: " + testCaseName); builder.makeTestClass(testCaseName, this.dataMap); boolean testCaseInProgress = false; boolean dataProviderAdded = false; // Get First row, containing the test name and the data to be injected while (i < maxRows) { logger.debug("i: " + i); logger.debug("currentRow: " + currentRow); Row row = sheet.getRow(currentRow); TestCommand command = null; // Check for empty row if (row != null && row.getPhysicalNumberOfCells() != 0) { i++; // Get Cells Iterator<Cell> iterator = row.cellIterator(); while (iterator.hasNext()) { Cell cell = iterator.next(); String cellValue = (cell == null || cell.toString() == "") ? "" : XslxUtil.objectFrom(workbook, cell).toString(); logger.debug("Cell: " + cellValue); if (cellValue.startsWith("test")) { logger.debug("Test case found: " + cellValue + ". Creating Test Case"); // Create new Test CASE try { builder.addTest(cellValue); testCaseInProgress = true; dataProviderAdded = false; } catch (CannotModifyTestMethodException e) { e.printStackTrace(); throw new CannotCreateTestClassException( "Could not create Test Class as there was a variable not found in test assertion. Embedded exception: " + e.getMessage()); } catch (VariableNotFoundException e) { e.printStackTrace(); throw new CannotCreateTestClassException( "Could not create Test Class as there was a variable not found in test assertion. Embedded exception: " + e.getMessage()); } break; } else { if (command == null & !cellValue.equals("")) { logger.debug("Command found: " + cellValue + ". Creating new TestCommand"); command = new TestCommand(cellValue); } else if (!cellValue.equals("")) { logger.debug("Command argument found: " + cellValue); command.addParameter(cellValue); } } } } else { // Blank row could mean a test case has just been completed // Complete last test case by adding a data provider if (testCaseInProgress && !dataProviderAdded) { try { logger.debug("In Progress Test Case now being closed off and added to class..."); builder.addDataProvider(); dataProviderAdded = true; logger.debug("In Progress Test Case now closed off!"); } catch (CannotCreateDataProviderException e) { throw new CannotCreateTestClassException( "Could not create Test case as a DataProvider for the method could not be created. Embedded exception: " + e.getMessage()); } } } try { if (command != null) { logger.debug("Adding command to method"); builder.appendTestToLastMethod(command); } } catch (CannotModifyTestMethodException e) { throw new CannotCreateTestClassException("Unable to add Test Case '" + command.toString() + "' to Test Class. Embedded exception: " + e.getMessage()); } catch (VariableNotFoundException e) { throw new CannotCreateTestClassException("Unable to add Test Case '" + command.toString() + "' to Test Class as a variable was not found. Embedded exception: " + e.getMessage()); } currentRow++; } // Blank row could mean a test case has just been completed // Complete last test case by adding a data provider logger.debug( "End of rows...Checking if In Progress Test Case now being closed off and added to class..."); if (testCaseInProgress && !dataProviderAdded) { logger.debug(" In Progress Test Case now being closed off and added to class..."); try { builder.addDataProvider(); dataProviderAdded = true; logger.debug("In Progress Test Case now closed off!"); } catch (CannotCreateDataProviderException e) { throw new CannotCreateTestClassException( "Could not create Test case as a DataProvider for the method could not be created. Embedded exception: " + e.getMessage()); } } if (testCaseInProgress) { logger.debug("Generating class file"); try { this.tests.add(builder.getCreatedClass()); } catch (CannotModifyTestMethodException e) { e.printStackTrace(); throw new CannotCreateTestClassException( "Could not create Test case as a DataProvider for the method could not be created. Embedded exception: " + e.getMessage()); } testCaseInProgress = false; } } } try { stream.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } logger.info("Looking at our classes..."); // Look at the Test Objects for (Class<Test> clazz : tests) { logger.info("Class: " + clazz.getName()); for (Method m : clazz.getMethods()) { logger.info("Method: " + m); if (m.getName().equalsIgnoreCase("testRetailDataProvider")) { logger.info("invoking data provider"); Test test = clazz.newInstance(); Object[][] data = (Object[][]) m.invoke(test); for (Object[] obs : data) { for (Object o : obs) { logger.info("data value: " + o); } } } } } }
From source file:bad.robot.excel.matchers.RowNumberMatcher.java
License:Apache License
@Override protected boolean matchesSafely(Sheet actual, Description mismatch) { if (expected.getLastRowNum() != actual.getLastRowNum()) { mismatch.appendText("got ").appendValue(numberOfRowsIn(actual)).appendText(" row(s) in sheet ") .appendValue(actual.getSheetName()).appendText(" expected ") .appendValue(numberOfRowsIn(expected)); return false; }//from w w w . jav a2 s . com return true; }
From source file:bad.robot.excel.matchers.SheetNameMatcher.java
License:Apache License
@Override protected boolean matchesSafely(Workbook actual, Description mismatch) { List<String> missingSheets = new ArrayList<String>(); for (Sheet sheet : sheetsOf(expected)) { if (actual.getSheet(sheet.getSheetName()) == null) missingSheets.add(sheet.getSheetName()); }/*w ww. j a v a 2s. c o m*/ mismatch.appendValueList("sheet(s) ", ", ", notFound(missingSheets), missingSheets); return missingSheets.isEmpty(); }
From source file:bad.robot.excel.matchers.SheetsMatcher.java
License:Apache License
@Override protected boolean matchesSafely(Workbook actual, Description mismatch) { for (Sheet expectedSheet : sheetsOf(expected)) { Sheet actualSheet = actual.getSheet(expectedSheet.getSheetName()); if (!hasSameNumberOfRowAs(expectedSheet).matchesSafely(actualSheet, mismatch)) return false; if (!hasSameRowsAs(expectedSheet).matchesSafely(actualSheet, mismatch)) return false; }/*from w ww . j a v a2 s . c om*/ return true; }
From source file:blanco.commons.calc.parser.AbstractBlancoCalcParser.java
License:Open Source License
/** * ???// ww w. j a v a 2 s.com * * @param sheet * * @throws SAXException * SAX???? */ private final void parseSheet(final Sheet sheet) throws SAXException { // ????? AttributesImpl attrImpl = new AttributesImpl(); attrImpl.addAttribute("", "name", "name", "CDATA", sheet.getSheetName()); getContentHandler().startElement("", (String) getProperty(URI_PROPERTY_NAME_SHEET), (String) getProperty(URI_PROPERTY_NAME_SHEET), attrImpl); startSheet(sheet.getSheetName()); //getLastRowNum()??0???? +1? int maxRows = sheet.getLastRowNum() + 1; for (int row = 0; row < maxRows; row++) { startRow(row + 1); Row line = sheet.getRow(row); if (line != null) { for (int column = 0; column < line.getLastCellNum(); column++) { startColumn(column + 1); Cell cell = line.getCell(column); // ?trim()?????????????? String value = getCellValue(cell); fireCell(column + 1, row + 1, value); endColumn(column + 1); } } endRow(row + 1); } endSheet(sheet); // ????? getContentHandler().endElement("", (String) getProperty(URI_PROPERTY_NAME_SHEET), (String) getProperty(URI_PROPERTY_NAME_SHEET)); }
From source file:br.com.gartech.nfse.integrador.util.ExcelHelper.java
private String workbook2xml(org.apache.poi.ss.usermodel.Workbook workbook) { String result = null;/* w w w .j av a 2 s . c o m*/ StringBuffer sb = null; Sheet sheet = null; if (workbook != null && workbook.getSheetAt(0) != null) { String newLine = System.getProperty("line.separator"); sb = new StringBuffer(); sb.append("<?xml version=\"1.0\" ?>"); sb.append(newLine); sb.append("<!DOCTYPE workbook SYSTEM \"workbook.dtd\">"); sb.append(newLine); sb.append(newLine); sb.append("<workbook>"); sb.append(newLine); for (int i = 0; i < workbook.getNumberOfSheets(); ++i) { sheet = workbook.getSheetAt(i); if (sheet != null && sheet.rowIterator().hasNext()) { sb.append("\t"); sb.append("<sheet>"); sb.append(newLine); sb.append("\t\t"); sb.append("<name><![CDATA[" + sheet.getSheetName() + "]]></name>"); sb.append(newLine); int j = 0; for (Iterator<Row> iterator = sheet.rowIterator(); iterator.hasNext();) { Row row = (Row) iterator.next(); int k = 0; if (row.getCell(0) != null && row.getCell(0).getStringCellValue() != null && row.getCell(0).getStringCellValue().trim().length() > 0) { sb.append("\t\t"); sb.append("<row number=\"" + j + "\">"); sb.append(newLine); for (Cell cell : row) { sb.append("\t\t\t"); sb.append("<col number=\"" + k + "\">"); sb.append("<![CDATA[" + cellToString(cell) + "]]>"); sb.append("</col>"); sb.append(newLine); k++; } sb.append("\t\t"); sb.append("</row>"); sb.append(newLine); } j++; } sb.append("\t"); sb.append("</sheet>"); sb.append(newLine); } } sb.append("</workbook>"); sb.append(newLine); result = sb.toString(); } return result; }
From source file:cherry.goods.excel.ExcelFactoryTest.java
License:Apache License
@Test public void testOpen_XLS() throws Exception { // /*from w w w . j a v a 2s. c om*/ File file = File.createTempFile(getClass().getName(), ".xls", new File(".")); file.deleteOnExit(); try (OutputStream out = new FileOutputStream(file); Workbook workbook = ExcelFactory.createBlankXls("OPEN_XLS")) { workbook.write(out); } // try (InputStream in = new FileInputStream(file); Workbook workbook = ExcelFactory.open(in)) { Sheet sheet = workbook.getSheetAt(0); assertNotNull(sheet); assertEquals("OPEN_XLS", sheet.getSheetName()); } }