List of usage examples for org.apache.poi.ss.usermodel Workbook close
@Override void close() throws IOException;
From source file:packtest.SelectedSheet.java
License:Apache License
public static void main(String[] args) throws IOException { Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); wb.createSheet("row sheet"); wb.createSheet("another sheet"); Sheet sheet3 = wb.createSheet(" sheet 3 "); sheet3.setSelected(true);//from w w w .ja v a 2 s .c o m wb.setActiveSheet(2); // Create various cells and rows for spreadsheet. FileOutputStream fileOut = new FileOutputStream("selectedSheet.xlsx"); wb.write(fileOut); fileOut.close(); wb.close(); }
From source file:packtest.WorkingWithPageSetup.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); /**/*from ww w. j a v a2s. co m*/ * It's possible to set up repeating rows and columns in your printouts by using the setRepeatingRowsAndColumns() function in the Workbook object. * * This function Contains 5 parameters: * The first parameter is the index to the sheet (0 = first sheet). * The second and third parameters specify the range for the columns to repreat. * To stop the columns from repeating pass in -1 as the start and end column. * The fourth and fifth parameters specify the range for the rows to repeat. * To stop the columns from repeating pass in -1 as the start and end rows. */ Sheet sheet1 = wb.createSheet("new sheet"); Sheet sheet2 = wb.createSheet("second sheet"); // Set the columns to repeat from column 0 to 2 on the first sheet Row row1 = sheet1.createRow(0); row1.createCell(0).setCellValue(1); row1.createCell(1).setCellValue(2); row1.createCell(2).setCellValue(3); Row row2 = sheet1.createRow(1); row2.createCell(1).setCellValue(4); row2.createCell(2).setCellValue(5); Row row3 = sheet2.createRow(1); row3.createCell(0).setCellValue(2.1); row3.createCell(4).setCellValue(2.2); row3.createCell(5).setCellValue(2.3); Row row4 = sheet2.createRow(2); row4.createCell(4).setCellValue(2.4); row4.createCell(5).setCellValue(2.5); // Set the columns to repeat from column 0 to 2 on the first sheet sheet1.setRepeatingColumns(CellRangeAddress.valueOf("A:C")); // Set the the repeating rows and columns on the second sheet. CellRangeAddress cra = CellRangeAddress.valueOf("E2:F3"); sheet2.setRepeatingColumns(cra); sheet2.setRepeatingRows(cra); //set the print area for the first sheet wb.setPrintArea(0, 1, 2, 0, 3); FileOutputStream fileOut = new FileOutputStream("xssf-printsetup.xlsx"); wb.write(fileOut); fileOut.close(); wb.close(); }
From source file:packtest.WorkingWithPictures.java
License:Apache License
public static void main(String[] args) throws IOException { //create a new workbook Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); try {/*from w w w. j av a2 s . c o m*/ CreationHelper helper = wb.getCreationHelper(); //add a picture in this workbook. InputStream is = new FileInputStream(args[0]); byte[] bytes = IOUtils.toByteArray(is); is.close(); int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG); //create sheet Sheet sheet = wb.createSheet(); //create drawing Drawing drawing = sheet.createDrawingPatriarch(); //add a picture shape ClientAnchor anchor = helper.createClientAnchor(); anchor.setCol1(1); anchor.setRow1(1); Picture pict = drawing.createPicture(anchor, pictureIdx); //auto-size picture pict.resize(2); //save workbook String file = "picture.xls"; if (wb instanceof XSSFWorkbook) file += "x"; // NOSONAR OutputStream fileOut = new FileOutputStream(file); try { wb.write(fileOut); } finally { fileOut.close(); } } finally { wb.close(); } }
From source file:pl.softech.knf.ofe.opf.members.xls.export.XlsMembersWritter.java
License:Apache License
public static void main(final String[] args) throws Exception { final List<OpenPensionFund> funds = new LinkedList<>(); funds.addAll(createMocks("AIG OFE")); funds.addAll(createMocks("OFE Allianz Polska")); funds.addAll(createMocks("Bankowy OFE")); funds.addAll(createMocks("Commercial Union OFE BPH CU WBK")); try (FileOutputStream out = new FileOutputStream( new File("/home/ssledz/knf-ofe-work-dir/work", "workbook.xls"))) { final Workbook wb = new HSSFWorkbook(); final XlsMembersWritter output = new XlsMembersWritter(); output.write(funds, wb);//from ww w .j a v a2 s . c o m wb.write(out); wb.close(); } }
From source file:ro.dabuno.office.integration.BusinessPlan.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb; if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); else/* w ww . j av a2 s . c om*/ wb = new XSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Business Plan"); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); //the following three statements are required only for HSSF sheet.setAutobreaks(true); printSetup.setFitHeight((short) 1); printSetup.setFitWidth((short) 1); //the header row: centered text in 48pt font Row headerRow = sheet.createRow(0); headerRow.setHeightInPoints(12.75f); for (int i = 0; i < titles.length; i++) { Cell cell = headerRow.createCell(i); cell.setCellValue(titles[i]); cell.setCellStyle(styles.get("header")); } //columns for 11 weeks starting from 9-Jul Calendar calendar = Calendar.getInstance(); int year = calendar.get(Calendar.YEAR); calendar.setTime(fmt.parse("9-Jul")); calendar.set(Calendar.YEAR, year); for (int i = 0; i < 11; i++) { Cell cell = headerRow.createCell(titles.length + i); cell.setCellValue(calendar); cell.setCellStyle(styles.get("header_date")); calendar.roll(Calendar.WEEK_OF_YEAR, true); } //freeze the first row sheet.createFreezePane(0, 1); Row row; Cell cell; int rownum = 1; for (int i = 0; i < data.length; i++, rownum++) { row = sheet.createRow(rownum); if (data[i] == null) continue; for (int j = 0; j < data[i].length; j++) { cell = row.createCell(j); String styleName; boolean isHeader = i == 0 || data[i - 1] == null; switch (j) { case 0: if (isHeader) { styleName = "cell_b"; cell.setCellValue(Double.parseDouble(data[i][j])); } else { styleName = "cell_normal"; cell.setCellValue(data[i][j]); } break; case 1: if (isHeader) { styleName = i == 0 ? "cell_h" : "cell_bb"; } else { styleName = "cell_indented"; } cell.setCellValue(data[i][j]); break; case 2: styleName = isHeader ? "cell_b" : "cell_normal"; cell.setCellValue(data[i][j]); break; case 3: styleName = isHeader ? "cell_b_centered" : "cell_normal_centered"; cell.setCellValue(Integer.parseInt(data[i][j])); break; case 4: { calendar.setTime(fmt.parse(data[i][j])); calendar.set(Calendar.YEAR, year); cell.setCellValue(calendar); styleName = isHeader ? "cell_b_date" : "cell_normal_date"; break; } case 5: { int r = rownum + 1; String fmla = "IF(AND(D" + r + ",E" + r + "),E" + r + "+D" + r + ",\"\")"; cell.setCellFormula(fmla); styleName = isHeader ? "cell_bg" : "cell_g"; break; } default: styleName = data[i][j] != null ? "cell_blue" : "cell_normal"; } cell.setCellStyle(styles.get(styleName)); } } //group rows for each phase, row numbers are 0-based sheet.groupRow(4, 6); sheet.groupRow(9, 13); sheet.groupRow(16, 18); //set column widths, the width is measured in units of 1/256th of a character width sheet.setColumnWidth(0, 256 * 6); sheet.setColumnWidth(1, 256 * 33); sheet.setColumnWidth(2, 256 * 20); sheet.setZoom(75); //75% scale // Write the output to a file String file = "businessplan.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); wb.close(); }
From source file:ru.wmbdiff.ExportIntoExcel.java
License:Apache License
public void export(File file, WMBDiffNoRootTreeTableModel model) { logger.info("export begin"); Workbook workbook = new HSSFWorkbook(); Sheet sheet = workbook.createSheet("WMBDiff"); int rowNum = 0; //Create Header CellStyle style;/*from w w w.j a va 2 s . co m*/ Font headerFont = workbook.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setColor(IndexedColors.WHITE.getIndex()); headerFont.setFontHeightInPoints((short) 10); style = workbook.createCellStyle(); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.WHITE.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.WHITE.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.WHITE.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.WHITE.getIndex()); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFillForegroundColor(IndexedColors.AQUA.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(headerFont); Row row = sheet.createRow(rowNum++); Cell cell; cell = row.createCell(0); cell.setCellValue("Result"); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue("Broker"); cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue("Execution Group"); cell.setCellStyle(style); cell = row.createCell(3); cell.setCellValue("Name"); cell.setCellStyle(style); cell = row.createCell(4); cell.setCellValue("Type"); cell.setCellStyle(style); cell = row.createCell(5); cell.setCellValue("Last Modification"); cell.setCellStyle(style); cell = row.createCell(6); cell.setCellValue("Deployment Date"); cell.setCellStyle(style); cell = row.createCell(7); cell.setCellValue("Bar File"); cell.setCellStyle(style); cell = row.createCell(8); cell.setCellValue("Result Description"); cell.setCellStyle(style); sheet.createFreezePane(0, 1); List<DiffExecutionGroup> dEG = model.getDiffExecutionGroupList(); ListIterator<DiffExecutionGroup> litr = dEG.listIterator(); while (litr.hasNext()) { DiffExecutionGroup element = litr.next(); element.getDiffResultList(); ListIterator<DiffDeployedObjectResult> litr2 = element.getDiffResultList().listIterator(); while (litr2.hasNext()) { DiffDeployedObjectResult res = litr2.next(); switch (res.getResult()) { case ONLY_IN_A: createRow(rowNum++, sheet, res.getAObject(), "A", res.getResultDesc()); break; case ONLY_IN_B: createRow(rowNum++, sheet, res.getBObject(), "B", res.getResultDesc()); break; case EQUAL: createRow(rowNum++, sheet, res.getAObject(), "=", res.getResultDesc()); createRow(rowNum++, sheet, res.getBObject(), "=", res.getResultDesc()); sheet.groupRow(rowNum - 2, rowNum - 2); break; case DIFF: createRow(rowNum++, sheet, res.getAObject(), "!=", res.getResultDesc()); createRow(rowNum++, sheet, res.getBObject(), "!=", res.getResultDesc()); sheet.groupRow(rowNum - 2, rowNum - 2); break; } ; } ; } ; //Adjust column width to fit the contents for (int i = 0; i < 9; i++) sheet.autoSizeColumn(i); //set Filter sheet.setAutoFilter(new CellRangeAddress(0, rowNum - 1, 0, 8)); try { FileOutputStream out = new FileOutputStream(file); workbook.write(out); workbook.close(); out.close(); } catch (Exception e) { logger.error("export", e); } logger.info("export end"); }
From source file:Sales.MainMenu.java
private void exportResultsToExcelButtonActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_exportResultsToExcelButtonActionPerformed /**//from w ww.ja v a 2s. c o m * Exports search results to .xlsx excel document to the users desktop */ // Get the query name and assign it to the exported excel document String queryName = queryNameTextField.getText(); // Get current date and time of the export Calendar calendar = Calendar.getInstance(); String date = new SimpleDateFormat("YYYY.MM.dd HH.mm.ss").format(calendar.getTime()); // Sets the title of the excel document to concat the query name and current datetime String queryTitle = queryName + " " + date; // Saves the file to the user's desktop directory. String file = USER_HOME_FOLDER + "\\Desktop\\" + queryTitle + ".xlsx"; try { //Generate the .xlsx workbook Workbook wb = new XSSFWorkbook(); //Generates the new sheet Sheet sheet = wb.createSheet("RQS RAW DATA"); //Header row created at sheet line 1 Row headerRow = sheet.createRow(0); //First data row created at line two Row row = sheet.createRow(1); //Initialize Cell Cell cell; //Get the table model from RQS TableModel model = searchResultsTable.getModel(); // Get the header values from RQS table and assign to headerRow for (int headings = 0; headings < model.getColumnCount(); headings++) { headerRow.createCell(headings).setCellValue(model.getColumnName(headings)); } //Get the data from RQS and fill in excel sheet starting at row 2 on excel sheet for (int rows = 0; rows < model.getRowCount(); rows++) {//For each row in RQS for (int cols = 0; cols < model.getColumnCount(); cols++) {//For each column in each row //Assign cell value to corresponding cells in excel sheet //If the cell is not blank or null if (model.getValueAt(rows, cols) != null) { row.createCell(cols).setCellValue(model.getValueAt(rows, cols).toString()); } else { row.createCell(cols).setCellValue(""); } } row = sheet.createRow(rows + 2); } wb.write(new FileOutputStream(file)); wb.close(); JOptionPane.showMessageDialog(this, "The results have been saved to your desktop", "Successful Export", JOptionPane.INFORMATION_MESSAGE); } catch (IOException | HeadlessException e) { System.out.println(e.getMessage()); System.out.println(e.getCause()); } }
From source file:techgarden.Controller.java
public Object[][] getData(String excelFilePath) throws IOException, InvalidFormatException { FileInputStream fis = new FileInputStream(new File(excelFilePath)); org.apache.poi.ss.usermodel.Workbook workbook = WorkbookFactory.create(fis); org.apache.poi.ss.usermodel.Sheet firstSheet = workbook.getSheetAt(0); int rownum = firstSheet.getLastRowNum(); int colnum = firstSheet.getRow(0).getLastCellNum(); Object[][] data = new Object[rownum][colnum]; //String[][] stringData = new String[rownum][colnum]; for (int i = 0; i < rownum; i++) { Row row = firstSheet.getRow(i);// w ww. j a v a 2 s . co m if (row != null) { for (int j = 0; j < colnum; j++) { Cell cell = row.getCell(j); if (cell != null) { try { if (cell.getColumnIndex() == 0) { cell.setCellType(Cell.CELL_TYPE_STRING); data[i][j] = cell.getStringCellValue(); // System.out.println(cell.getStringCellValue()); } else if (cell.getColumnIndex() == 1) { data[i][j] = cell.getDateCellValue(); // System.out.println(cell.getDateCellValue()); } else { data[i][j] = cell.getNumericCellValue(); } } catch (IllegalStateException e) { e.printStackTrace(); // } } } } } workbook.close(); fis.close(); return data; }
From source file:techGardenMap.Controller.java
public Double[][] getData(String excelFilePath) throws IOException, InvalidFormatException { FileInputStream fis = new FileInputStream(new File(excelFilePath)); org.apache.poi.ss.usermodel.Workbook workbook = WorkbookFactory.create(fis); org.apache.poi.ss.usermodel.Sheet firstSheet = workbook.getSheetAt(0); int rownum = firstSheet.getLastRowNum(); int colnum = firstSheet.getRow(0).getLastCellNum(); Double[][] data = new Double[rownum][colnum]; //String[][] stringData = new String[rownum][colnum]; for (int i = 0; i < rownum; i++) { Row row = firstSheet.getRow(i);/*w w w . j a va 2 s . c o m*/ if (row != null) { for (int j = 0; j < colnum; j++) { Cell cell = row.getCell(j); if (cell != null) { try { //cell.setCellType(Cell.CELL_TYPE_STRING); data[i][j] = cell.getNumericCellValue(); System.out.println(cell.getDateCellValue()); } catch (IllegalStateException e) { e.printStackTrace(); // } } } } } workbook.close(); fis.close(); return data; }
From source file:tools.xor.service.AggregateManager.java
License:Apache License
@Override public void exportAggregate(OutputStream os, Object inputObject, Settings settings) throws IOException { validateImportExport();/*ww w .ja v a2s . co m*/ BusinessObject to = readBO(inputObject, settings); Set<BusinessObject> dataObject = to.getObjectCreator().getDataObjects(); // Get the container and the containment property and create a sheet of such objects Map<String, List<BusinessObject>> sheetBO = new HashMap<String, List<BusinessObject>>(); for (BusinessObject bo : dataObject) { if (bo.getContainer() != null && bo.getContainmentProperty() != null) { String key = Constants.XOR.getExcelSheetFullName(bo.getContainer().getType(), bo.getContainmentProperty()); if (!sheetBO.containsKey(key)) { sheetBO.put(key, new LinkedList<BusinessObject>()); } List<BusinessObject> boList = sheetBO.get(key); boList.add(bo); } } Workbook wb = processSheetBO(to, sheetBO); wb.write(os); os.close(); wb.close(); }