List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook close
@Override
public void close() throws IOException
From source file:org.xframium.device.data.ExcelDataProvider.java
License:Open Source License
/** * Read data./*from w ww.j a v a2 s. c o m*/ * * @param inputStream the input stream */ private void readData(InputStream inputStream) { BufferedReader fileReader = null; XSSFWorkbook workbook = null; try { workbook = new XSSFWorkbook(inputStream); XSSFSheet sheet = workbook.getSheet(tabName); for (int i = 1; i <= sheet.getLastRowNum(); i++) { XSSFRow currentRow = sheet.getRow(i); if (getCellValue(currentRow.getCell(0)) == null || getCellValue(currentRow.getCell(0)).isEmpty()) break; String driverName = ""; switch (driverType) { case APPIUM: if (getCellValue(currentRow.getCell(3)).toUpperCase().equals("IOS")) driverName = "IOS"; else if (getCellValue(currentRow.getCell(3)).toUpperCase().equals("ANDROID")) driverName = "ANDROID"; else throw new IllegalArgumentException("Appium is not supported on the following OS " + getCellValue(currentRow.getCell(3)).toUpperCase()); break; case PERFECTO: driverName = "PERFECTO"; break; case WEB: driverName = "WEB"; break; } Device currentDevice = new SimpleDevice(getCellValue(currentRow.getCell(0)), getCellValue(currentRow.getCell(1)), getCellValue(currentRow.getCell(2)), getCellValue(currentRow.getCell(3)), getCellValue(currentRow.getCell(4)), getCellValue(currentRow.getCell(5)), getCellValue(currentRow.getCell(6)), Integer.parseInt(getCellValue(currentRow.getCell(7))), driverName, Boolean.parseBoolean(getCellValue(currentRow.getCell(8))), null); if (currentDevice.isActive()) { if (log.isDebugEnabled()) log.debug("Extracted: " + currentDevice); DeviceManager.instance().registerDevice(currentDevice); } } } catch (Exception e) { log.fatal("Error reading Excel Element File", e); } finally { try { workbook.close(); } catch (Exception e) { } } }
From source file:org.xframium.page.data.provider.ExcelPageDataProvider.java
License:Open Source License
/** * Read elements.//from www .j a v a 2s . com * * @param inputStream the input stream */ private void readElements(InputStream inputStream) { XSSFWorkbook workbook = null; try { workbook = new XSSFWorkbook(inputStream); String[] tabs = tabNames.split(","); for (String tabName : tabs) { XSSFSheet sheet = workbook.getSheet(tabName); if (sheet == null) continue; addRecordType(tabName, false); XSSFRow firstRow = sheet.getRow(0); for (int i = 1; i <= sheet.getLastRowNum(); i++) { XSSFRow currentRow = sheet.getRow(i); if (getCellValue(currentRow.getCell(0)) == null || getCellValue(currentRow.getCell(0)).isEmpty()) break; DefaultPageData currentRecord = new DefaultPageData(tabName, tabName + "-" + i, true); for (int x = 0; x < firstRow.getLastCellNum(); x++) { String currentName = getCellValue(firstRow.getCell(x)); String currentValue = getCellValue(currentRow.getCell(x)); if (currentValue == null) currentValue = ""; if (currentValue.startsWith(PageData.TREE_MARKER) && currentValue.endsWith(PageData.TREE_MARKER)) { // // This is a reference to another page data table // currentRecord.addPageData(currentName); currentRecord.addValue(currentName + PageData.DEF, currentValue); currentRecord.setContainsChildren(true); } else currentRecord.addValue(currentName, currentValue); } addRecord(currentRecord); } } } catch (Exception e) { log.fatal("Error reading Excel Element File", e); } finally { try { workbook.close(); } catch (Exception e) { } } }
From source file:org.xframium.page.element.provider.ExcelElementProvider.java
License:Open Source License
/** * Read elements./*w w w . jav a2 s. c o m*/ * * @param inputStream the input stream */ private void readElements(InputStream inputStream) { XSSFWorkbook workbook = null; try { workbook = new XSSFWorkbook(inputStream); boolean elementsRead = true; String[] tabs = tabNames.split(","); for (String tabName : tabs) { XSSFSheet sheet = workbook.getSheet(tabName); if (sheet == null) continue; for (int i = 1; i <= sheet.getLastRowNum(); i++) { System.out.println(i); XSSFRow currentRow = sheet.getRow(i); if (getCellValue(currentRow.getCell(0)) == null || getCellValue(currentRow.getCell(0)).isEmpty()) break; ElementDescriptor elementDescriptor = new ElementDescriptor(tabName, getCellValue(currentRow.getCell(0)), getCellValue(currentRow.getCell(1))); String contextName = null; if (getCellValue(currentRow.getCell(4)) != null && !getCellValue(currentRow.getCell(4)).isEmpty()) { contextName = getCellValue(currentRow.getCell(4)); } Element currentElement = ElementFactory.instance().createElement( BY.valueOf(getCellValue(currentRow.getCell(2))), getCellValue(currentRow.getCell(3)).replace("$$", ","), getCellValue(currentRow.getCell(1)), getCellValue(currentRow.getCell(0)), contextName); if (log.isDebugEnabled()) log.debug("Adding Excel Element using [" + elementDescriptor.toString() + "] as [" + currentElement); elementsRead = elementsRead & validateElement(elementDescriptor, currentElement); elementMap.put(elementDescriptor.toString(), currentElement); } } setInitialized(elementsRead); } catch (Exception e) { log.fatal("Error reading Excel Element File", e); } finally { try { workbook.close(); } catch (Exception e) { } } }
From source file:org.xframium.page.keyWord.provider.ExcelKeyWordProvider.java
License:Open Source License
private void readElements(InputStream inputStream) { List<MatrixTest> testList = new ArrayList<MatrixTest>(10); XSSFWorkbook workbook = null; try {// w w w . j a v a 2 s . c o m workbook = new XSSFWorkbook(inputStream); XSSFSheet sheet = workbook.getSheet("Model"); // // Extract the Tests // for (int i = 1; i <= sheet.getLastRowNum(); i++) { XSSFRow currentRow = sheet.getRow(i); String pageName = getCellValue(currentRow.getCell(0)); if (pageName.toLowerCase().equals("name")) continue; String className = getCellValue(currentRow.getCell(1)); try { Class useClass = KeyWordPage.class; if (className != null && !className.isEmpty()) useClass = (Class<Page>) Class.forName(className); if (log.isDebugEnabled()) log.debug("Creating page as " + useClass.getSimpleName() + " for " + pageName); KeyWordDriver.instance().addPage(pageName, useClass); } catch (Exception e) { log.error("Error creating instance of [" + className + "]"); } } sheet = workbook.getSheet("Tests"); // // Extract the Tests // for (int i = 1; i <= sheet.getLastRowNum(); i++) { XSSFRow currentRow = sheet.getRow(i); List<String> testDefinition = new ArrayList<String>(10); for (int j = 0; j < currentRow.getLastCellNum(); j++) testDefinition.add(getCellValue(currentRow.getCell(j))); MatrixTest currentTest = new MatrixTest(testDefinition.toArray(new String[0])); if (currentTest.getName() != null && !currentTest.getName().isEmpty() && currentTest.isActive()) testList.add(currentTest); } for (MatrixTest currentTest : testList) { List<String[]> stepList = new ArrayList<String[]>(20); sheet = workbook.getSheet(currentTest.getName()); if (sheet != null) { for (int i = 1; i <= sheet.getLastRowNum(); i++) { XSSFRow currentRow = sheet.getRow(i); List<String> stepDefinition = new ArrayList<String>(10); for (int j = 0; j < currentRow.getLastCellNum(); j++) stepDefinition.add(getCellValue(currentRow.getCell(j))); stepList.add(stepDefinition.toArray(new String[0])); } } currentTest.setStepDefinition((String[][]) stepList.toArray(new String[0][0])); } for (MatrixTest currentTest : testList) { if (currentTest.getType().equals("function")) KeyWordDriver.instance().addFunction(currentTest.createTest()); else KeyWordDriver.instance().addTest(currentTest.createTest()); } } catch (Exception e) { log.fatal("Error reading Excel Element File", e); } finally { try { workbook.close(); } catch (Exception e) { } } }
From source file:packtest.AligningCells.java
License:Apache License
public static void main(String[] args) throws IOException { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet();// w w w. ja v a2 s . c om XSSFRow row = sheet.createRow((short) 2); row.setHeightInPoints(30); for (int i = 0; i < 8; i++) { //column width is set in units of 1/256th of a character width sheet.setColumnWidth(i, 256 * 15); } createCell(wb, row, (short) 0, XSSFCellStyle.ALIGN_CENTER, XSSFCellStyle.VERTICAL_BOTTOM); createCell(wb, row, (short) 1, XSSFCellStyle.ALIGN_CENTER_SELECTION, XSSFCellStyle.VERTICAL_BOTTOM); createCell(wb, row, (short) 2, XSSFCellStyle.ALIGN_FILL, XSSFCellStyle.VERTICAL_CENTER); createCell(wb, row, (short) 3, XSSFCellStyle.ALIGN_GENERAL, XSSFCellStyle.VERTICAL_CENTER); createCell(wb, row, (short) 4, XSSFCellStyle.ALIGN_JUSTIFY, XSSFCellStyle.VERTICAL_JUSTIFY); createCell(wb, row, (short) 5, XSSFCellStyle.ALIGN_LEFT, XSSFCellStyle.VERTICAL_TOP); createCell(wb, row, (short) 6, XSSFCellStyle.ALIGN_RIGHT, XSSFCellStyle.VERTICAL_TOP); //center text over B4, C4, D4 row = sheet.createRow((short) 3); centerAcrossSelection(wb, row, (short) 1, (short) 3, XSSFCellStyle.VERTICAL_CENTER); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("xssf-align.xlsx"); wb.write(fileOut); fileOut.close(); wb.close(); }
From source file:packtest.BigGridDemo.java
License:Apache License
public static void main(String[] args) throws Exception { // Step 1. Create a template file. Setup sheets and workbook-level objects such as // cell styles, number formats, etc. XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet("Big Grid"); Map<String, XSSFCellStyle> styles = createStyles(wb); //name of the zip entry holding sheet data, e.g. /xl/worksheets/sheet1.xml String sheetRef = sheet.getPackagePart().getPartName().getName(); //save the template FileOutputStream os = new FileOutputStream("template.xlsx"); wb.write(os);/*ww w . jav a2s. co m*/ os.close(); //Step 2. Generate XML file. File tmp = File.createTempFile("sheet", ".xml"); Writer fw = new OutputStreamWriter(new FileOutputStream(tmp), XML_ENCODING); generate(fw, styles); fw.close(); //Step 3. Substitute the template entry with the generated data FileOutputStream out = new FileOutputStream(Utils.getPath("big-grid.xlsx")); substitute(new File(Utils.getPath("template.xlsx")), tmp, sheetRef.substring(1), out); out.close(); wb.close(); }
From source file:packtest.CalendarDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Calendar calendar = Calendar.getInstance(); if (args.length > 0) calendar.set(Calendar.YEAR, Integer.parseInt(args[0])); int year = calendar.get(Calendar.YEAR); XSSFWorkbook wb = new XSSFWorkbook(); Map<String, XSSFCellStyle> styles = createStyles(wb); for (int month = 0; month < 12; month++) { calendar.set(Calendar.MONTH, month); calendar.set(Calendar.DAY_OF_MONTH, 1); //create a sheet for each month XSSFSheet sheet = wb.createSheet(months[month]); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false);/*w w w .j a v a 2s . c o m*/ XSSFPrintSetup printSetup = sheet.getPrintSetup(); printSetup.setOrientation(PrintOrientation.LANDSCAPE); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); //the header row: centered text in 48pt font XSSFRow headerRow = sheet.createRow(0); headerRow.setHeightInPoints(80); XSSFCell titleCell = headerRow.createCell(0); titleCell.setCellValue(months[month] + " " + year); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1")); //header with month titles XSSFRow monthRow = sheet.createRow(1); for (int i = 0; i < days.length; i++) { //for compatibility with HSSF we have to set column width in units of 1/256th of a character width sheet.setColumnWidth(i * 2, 5 * 256); //the column is 5 characters wide sheet.setColumnWidth(i * 2 + 1, 13 * 256); //the column is 13 characters wide sheet.addMergedRegion(new CellRangeAddress(1, 1, i * 2, i * 2 + 1)); XSSFCell monthCell = monthRow.createCell(i * 2); monthCell.setCellValue(days[i]); monthCell.setCellStyle(styles.get("month")); } int cnt = 1, day = 1; int rownum = 2; for (int j = 0; j < 6; j++) { XSSFRow row = sheet.createRow(rownum++); row.setHeightInPoints(100); for (int i = 0; i < days.length; i++) { XSSFCell dayCell_1 = row.createCell(i * 2); XSSFCell dayCell_2 = row.createCell(i * 2 + 1); int day_of_week = calendar.get(Calendar.DAY_OF_WEEK); if (cnt >= day_of_week && calendar.get(Calendar.MONTH) == month) { dayCell_1.setCellValue(day); calendar.set(Calendar.DAY_OF_MONTH, ++day); if (i == 0 || i == days.length - 1) { dayCell_1.setCellStyle(styles.get("weekend_left")); dayCell_2.setCellStyle(styles.get("weekend_right")); } else { dayCell_1.setCellStyle(styles.get("workday_left")); dayCell_2.setCellStyle(styles.get("workday_right")); } } else { dayCell_1.setCellStyle(styles.get("grey_left")); dayCell_2.setCellStyle(styles.get("grey_right")); } cnt++; } if (calendar.get(Calendar.MONTH) > month) break; } } // Write the output to a file FileOutputStream out = new FileOutputStream("calendar-" + year + ".xlsx"); wb.write(out); out.close(); wb.close(); }
From source file:packtest.CreatePivotTable.java
License:Apache License
public static void main(String[] args) throws FileNotFoundException, IOException, InvalidFormatException { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet();/*from w w w . ja v a 2 s. c o m*/ //Create some data to build the pivot table on setCellData(sheet); XSSFPivotTable pivotTable = sheet.createPivotTable(new AreaReference("A1:D4"), new CellReference("H5")); //Configure the pivot table //Use first column as row label pivotTable.addRowLabel(0); //Sum up the second column pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 1); //Set the third column as filter pivotTable.addColumnLabel(DataConsolidateFunction.AVERAGE, 2); //Add filter on forth column pivotTable.addReportFilter(3); FileOutputStream fileOut = new FileOutputStream(Utils.getPath("ooxml-pivottable.xlsx")); wb.write(fileOut); fileOut.close(); wb.close(); }
From source file:packtest.WorkingWithRichText.java
License:Apache License
public static void main(String[] args) throws Exception { XSSFWorkbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); try {/*w w w . j av a 2 s . c o m*/ XSSFSheet sheet = wb.createSheet(); XSSFRow row = sheet.createRow((short) 2); XSSFCell cell = row.createCell(1); XSSFRichTextString rt = new XSSFRichTextString("The quick brown fox"); XSSFFont font1 = wb.createFont(); font1.setBold(true); font1.setColor(new XSSFColor(new java.awt.Color(255, 0, 0))); rt.applyFont(0, 10, font1); XSSFFont font2 = wb.createFont(); font2.setItalic(true); font2.setUnderline(XSSFFont.U_DOUBLE); font2.setColor(new XSSFColor(new java.awt.Color(0, 255, 0))); rt.applyFont(10, 19, font2); XSSFFont font3 = wb.createFont(); font3.setColor(new XSSFColor(new java.awt.Color(0, 0, 255))); rt.append(" Jumped over the lazy dog", font3); cell.setCellValue(rt); // Write the output to a file OutputStream fileOut = new FileOutputStream(Utils.getPath("xssf-richtext.xlsx")); try { wb.write(fileOut); } finally { fileOut.close(); } } finally { wb.close(); } }
From source file:ReadExcel.HSSFReadWrite.java
License:Apache License
private static void startReadXlsxFile(String fileName) { try {//from ww w.j ava2 s . c o m XSSFWorkbook wb = HSSFReadWrite.readxlsxFile(fileName); System.out.println("Data dump:\n"); for (int k = 0; k < wb.getNumberOfSheets(); k++) { XSSFSheet sheet = wb.getSheetAt(k); int rows = sheet.getPhysicalNumberOfRows(); System.out.println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows + " row(s)."); for (int r = 0; r < rows; r++) { XSSFRow row = sheet.getRow(r); if (row == null) { continue; } int cells = row.getPhysicalNumberOfCells(); System.out.println("\nROW " + row.getRowNum() + " has " + cells + " cell(s)."); for (int c = 0; c < cells; c++) { XSSFCell cell = row.getCell(c); String value = null; switch (cell.getCellTypeEnum()) { case FORMULA: value = "FORMULA value=" + cell.getCellFormula(); break; case NUMERIC: value = "NUMERIC value=" + cell.getNumericCellValue(); break; case STRING: value = "STRING value=" + cell.getStringCellValue(); break; default: } System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value); } } } wb.close(); } catch (Exception e) { e.printStackTrace(); } }