List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook write
@SuppressWarnings("resource") public final void write(OutputStream stream) throws IOException
From source file:Business.ExcelReportCreator.java
public static int create(EcoSystem system) { // Steps:- // Create a Workbook. // Create a Sheet. // Repeat the following steps until all data is processed: // Create a Row. // Create Cells in a Row. Apply formatting using CellStyle. // Write to an OutputStream. // Close the output stream. XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Customer Details"); //Custom font style for header CellStyle cellStyle = sheet.getWorkbook().createCellStyle(); Font font = sheet.getWorkbook().createFont(); font.setBold(true);// ww w. j a v a2 s . c o m cellStyle.setFont(font); int rowCount = 0; int columnCount1 = 0; //Creating header row String s[] = { "CUSTOMER ID", "CUSTOMER NAME", "CONTACT NO.", "EMAIL ID", "USAGE(Gallons)", "BILLING DATE", "TOTAL BILL($)" }; Row row1 = sheet.createRow(++rowCount); for (String s1 : s) { Cell header = row1.createCell(++columnCount1); header.setCellValue(s1); header.setCellStyle(cellStyle); } for (Network network : system.getNetworkList()) { for (Enterprise enterprise : network.getEnterpriseDirectory().getEnterpriseList()) { if (enterprise instanceof WaterEnterprise) { for (Organization organization : enterprise.getOrganizationDirectory().getOrganizationList()) { if (organization instanceof CustomerOrganization) { for (Employee employee : organization.getEmployeeDirectory().getEmployeeList()) { Customer customer = (Customer) employee; Row row = sheet.createRow(++rowCount); int columnCount = 0; for (int i = 0; i < 7; i++) { Cell cell = row.createCell(++columnCount); if (i == 0) { cell.setCellValue(customer.getId()); } else if (i == 1) { cell.setCellValue(customer.getName()); } else if (i == 2) { cell.setCellValue(customer.getContactNo()); } else if (i == 3) { cell.setCellValue(customer.getEmailId()); } else if (i == 4) { cell.setCellValue(customer.getTotalUsageVolume()); } else if (i == 5) { if (customer.getBillingDate() != null) cell.setCellValue(String.valueOf(customer.getBillingDate())); else cell.setCellValue("Bill Not yet available"); } else if (i == 6) { if (customer.getTotalBill() != 0) cell.setCellValue(customer.getTotalBill()); else cell.setCellValue("Bill Not yet available"); } } } } } } } } try (FileOutputStream outputStream = new FileOutputStream("Customer_details.xlsx")) { workbook.write(outputStream); } catch (FileNotFoundException ex) { JOptionPane.showMessageDialog(null, "File not found"); return 0; } catch (IOException ex) { JOptionPane.showMessageDialog(null, "IOException"); return 0; } return 1; }
From source file:CE.CyberedgeInterface.java
public void toExcel() { try {//from w w w. jav a2 s.c o m con = DbConnection.getConnection(); String query = "Select * from Candidates_record"; ps = con.prepareStatement(query); rs = ps.executeQuery(); XSSFWorkbook w = new XSSFWorkbook(); XSSFSheet ws = w.createSheet("Candidates Record"); XSSFRow header = ws.createRow(0); header.createCell(0).setCellValue("ID"); header.createCell(1).setCellValue("Name"); header.createCell(2).setCellValue("Position"); header.createCell(3).setCellValue("Client"); header.createCell(4).setCellValue("Location"); header.createCell(5).setCellValue("Contact"); header.createCell(6).setCellValue("Email"); header.createCell(7).setCellValue("Experience"); header.createCell(8).setCellValue("Remark"); ws.setColumnWidth(1, 256 * 25); ws.setColumnWidth(2, 256 * 25); ws.setColumnWidth(3, 256 * 25); ws.setColumnWidth(4, 256 * 25); ws.setColumnWidth(5, 256 * 25); ws.setColumnWidth(6, 256 * 25); ws.setColumnWidth(7, 256 * 25); ws.setColumnWidth(8, 256 * 25); ws.setColumnWidth(9, 256 * 25); int index = 1; while (rs.next()) { XSSFRow row = ws.createRow(index); row.createCell(0).setCellValue(rs.getInt("Candidate_id")); row.createCell(1).setCellValue(rs.getString("Name")); row.createCell(2).setCellValue(rs.getString("Position")); row.createCell(3).setCellValue(rs.getString("Client")); row.createCell(4).setCellValue(rs.getString("Location")); row.createCell(5).setCellValue(rs.getString("Contact")); row.createCell(6).setCellValue(rs.getString("Email")); row.createCell(7).setCellValue(rs.getInt("Experience")); row.createCell(8).setCellValue(rs.getString("Remark")); index++; } String file = "C:\\..\\..\\..\\..\\..\\Cyberedge\\CandidateDetails.xlsx"; FileOutputStream fileout = new FileOutputStream(file); w.write(fileout); fileout.close(); JOptionPane.showMessageDialog(null, "File Saved"); ps.close(); rs.close(); } catch (Exception e) { JOptionPane.showMessageDialog(null, e); } }
From source file:ch.admin.isb.hermes5.business.userszenario.projektstrukturplan.ProjektstrukturplanGeneratorExcel.java
License:Apache License
public void addProjektstrukturPlan(String root, AnwenderloesungRenderingContainer container, ZipOutputBuilder zipBuilder, LocalizationEngine localizationEngine) { Szenario szenario = container.getSzenario(); SzenarioItem szenarioTree = container.getSzenarioUserData().getSzenarioTree(); try {//from ww w. j a va 2 s. c o m XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet("Workbreakdownstructure_" + localizationEngine.getLanguage()); addHeader(localizationEngine, wb, sheet); XSSFCellStyle ergebnisStyle = getErgebnisStyle(wb); XSSFCellStyle modulStyle = getModulStyle(wb); XSSFCellStyle defaultStyle = getDefaultStyle(wb); int currentRow = 1; List<Phase> phasen = szenario.getPhasen(); for (Phase phase : phasen) { currentRow = addPhase(sheet, currentRow, phase, localizationEngine, ergebnisStyle, modulStyle, defaultStyle, szenarioTree) + 1; } autosize(sheet); ByteArrayOutputStream out = new ByteArrayOutputStream(); wb.write(out); zipBuilder.addFile(root + "/" + localizationEngine.getLanguage() + "/" + "Workbreakdownstructure_" + localizationEngine.getLanguage() + ".xlsx", out.toByteArray()); } catch (IOException e) { throw new IllegalStateException(e); } }
From source file:ch.bfh.lca._15h.library.export.ExportToExcel.java
/*** * Prototype function. Not yet functional. * Allow to generate an age pyramid graphic in Excel by using an existing Excel Template. * @param language Language of the label in the Excel file * @param rows Arrays of rows to include in the listing * @param excelFilePath Path of the outputed file * @throws FileNotFoundException//from ww w . j a v a 2s . c om * @throws IOException * @throws InvalidFormatException */ public static void exportToAgePyramid(Translation.TRANSLATION_LANGUAGE language, GenericResultRow[] rows, String excelFilePath) throws FileNotFoundException, IOException, InvalidFormatException { //open template URL url = Translation.class.getClassLoader() .getResource("ch/bfh/lca/_15h/library/export/template/alter-pyramide-v2.xlsx"); //Workbook wb = WorkbookFactory.create(new File(url.getPath())); XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(new File(url.getPath()))); Sheet sheet = wb.getSheetAt(0); //http://www.programming-free.com/2012/12/create-charts-in-excel-using-java.html //https://poi.apache.org/spreadsheet/quick-guide.html#NewWorkbook Row row; Cell cell; for (int i = 0; i < 20; i++) { row = sheet.getRow(i + 1); if (row == null) { row = sheet.createRow(i + 1); } for (int j = 0; j < 3; j++) { cell = row.getCell(j); if (cell == null) { cell = row.createCell(j); } switch (j) { case 0: cell.setCellValue(i); break; case 1: cell.setCellValue(i * j * -1); break; case 2: cell.setCellValue(i * j); break; } } } //redefine data range //http://thinktibits.blogspot.ch/2014/09/Excel-Insert-Format-Table-Apache-POI-Example.html XSSFSheet sheet1 = wb.getSheetAt(0); XSSFTable table = sheet1.getTables().get(0); CTTable cttable = table.getCTTable(); AreaReference my_data_range = new AreaReference(new CellReference(0, 0), new CellReference(20, 2)); /* Set Range to the Table */ cttable.setRef(my_data_range.formatAsString()); // cttable.setDisplayName("DATEN"); /* this is the display name of the table */ //cttable.setName("test"); /* This maps to "displayName" attribute in <table>, OOXML */ //cttable.setId(1L); //id attribute against table as long value /* //redefine data range Name rangeCell = wb.getName("DATEN"); //Set new range for named range //String reference = sheetName + "!$C$" + (deface + 1) + ":$C$" + (rowNum + deface); String reference = sheet.getSheetName() + "!$A$2:$C$20"; //Assigns range value to named range rangeCell.setRefersToFormula(reference); */ //write to the file FileOutputStream fileOut = new FileOutputStream(excelFilePath); wb.write(fileOut); fileOut.close(); wb.close(); }
From source file:chocanproject.MemberReportGUI.java
private void writeToExcel() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet ws = wb.createSheet();//from ww w . jav a 2 s . c o m //Load data to Treemap TreeMap<String, Object[]> data = new TreeMap<>(); //Add column headers data.put("-1", new Object[] { dm.getColumnName(0), dm.getColumnName(1), dm.getColumnName(2), dm.getColumnName(3), dm.getColumnName(4), dm.getColumnName(5), dm.getColumnName(6), dm.getColumnName(7), dm.getColumnName(8), dm.getColumnName(9), dm.getColumnName(10) }); //Add rows and cells for (int i = 0; i < dm.getRowCount(); i++) { data.put(Integer.toString(i), new Object[] { getCellValue(i, 0), getCellValue(i, 1), getCellValue(i, 2), getCellValue(i, 3), getCellValue(i, 4), getCellValue(i, 5), getCellValue(i, 6), getCellValue(i, 7), getCellValue(i, 8), getCellValue(i, 9), getCellValue(i, 10) }); } //write to excel file Set<String> ids = data.keySet(); XSSFRow row; int rowID = 0; for (String key : ids) { row = ws.createRow(rowID++); //get data as per key Object[] values = data.get(key); int cellID = 0; for (Object o : values) { Cell cell = row.createCell(cellID++); cell.setCellValue(o.toString()); } } //write to filesystem try { String MName, fName; DateFormat dateFormat = new SimpleDateFormat("MM-dd-YYYY"); Date date = new Date(); String Cdate; MName = fn.toString(); Cdate = (dateFormat.format(date)); fName = MName + "_" + Cdate; FileOutputStream fos = new FileOutputStream(new File("D:/Excel/" + fName + ".xlsx")); wb.write(fos); fos.close(); } catch (FileNotFoundException ex) { ex.printStackTrace(); // Logger.getLogger(WorkBookNSheet.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { ex.printStackTrace(); //Logger.getLogger(MemberReportGUI.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:chocanproject.ProviderReportGUI.java
private void writeToExcel() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet ws = wb.createSheet();// w w w. java2 s .c om //Load data to Treemap TreeMap<String, Object[]> data = new TreeMap<>(); //Add column headers data.put("-1", new Object[] { dp.getColumnName(0), dp.getColumnName(1), dp.getColumnName(2), dp.getColumnName(3), dp.getColumnName(4), dp.getColumnName(5), dp.getColumnName(6), dp.getColumnName(7), dp.getColumnName(8), dp.getColumnName(9), dp.getColumnName(10), dp.getColumnName(11), dp.getColumnName(12), dp.getColumnName(13) }); //Add rows and cells for (int i = 0; i < dp.getRowCount(); i++) { data.put(Integer.toString(i), new Object[] { getCellValue(i, 0), getCellValue(i, 1), getCellValue(i, 2), getCellValue(i, 3), getCellValue(i, 4), getCellValue(i, 5), getCellValue(i, 6), getCellValue(i, 7), getCellValue(i, 8), getCellValue(i, 9), getCellValue(i, 10), getCellValue(i, 11), getCellValue(i, 12), getCellValue(i, 13) }); } //write to excel file Set<String> ids = data.keySet(); XSSFRow row; int rowID = 0; for (String key : ids) { row = ws.createRow(rowID++); //get data as per key Object[] values = data.get(key); int cellID = 0; for (Object o : values) { Cell cell = row.createCell(cellID++); cell.setCellValue(o.toString()); } } //write to filesystem try { String fName; DateFormat dateFormat = new SimpleDateFormat("MM-dd-YYYY"); Date date = new Date(); String Cdate; Cdate = (dateFormat.format(date)); fName = pfn + "_" + Cdate; FileOutputStream fos = new FileOutputStream(new File("D:/Excel/" + fName + ".xlsx")); wb.write(fos); fos.close(); } catch (FileNotFoundException ex) { ex.printStackTrace(); // Logger.getLogger(WorkBookNSheet.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { ex.printStackTrace(); //Logger.getLogger(MemberReportGUI.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:chocanproject.SummaryReports.java
private void writeToExcel() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet ws = wb.createSheet();// w w w . j a v a2 s . c o m //Load data to Treemap TreeMap<String, Object[]> data = new TreeMap<>(); //Add column headers data.put("-1", new Object[] { dm.getColumnName(0), dm.getColumnName(1), dm.getColumnName(2) }); //Add rows and cells for (int i = 0; i < dm.getRowCount(); i++) { data.put(Integer.toString(i), new Object[] { getCellValue(i, 0), getCellValue(i, 1), getCellValue(i, 2) }); } //write to excel file Set<String> ids = data.keySet(); XSSFRow row; int rowID = 0; for (String key : ids) { row = ws.createRow(rowID++); //get data as per key Object[] values = data.get(key); int cellID = 0; for (Object o : values) { Cell cell = row.createCell(cellID++); cell.setCellValue(o.toString()); } } //write to filesystem try { DateFormat dateFormat = new SimpleDateFormat("MM-dd-YYYY"); Date date = new Date(); String Cdate; Cdate = (dateFormat.format(date)); FileOutputStream fos = new FileOutputStream( new File("D:/Excel/SummaryReport-ToBePaid" + Cdate + ".xlsx")); wb.write(fos); fos.close(); } catch (FileNotFoundException ex) { ex.printStackTrace(); // Logger.getLogger(WorkBookNSheet.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { ex.printStackTrace(); //Logger.getLogger(MemberReportGUI.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:chocanproject.TotalSummaryReport.java
private void writeToExcel() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet ws = wb.createSheet();/*from w w w . j a v a2 s . c o m*/ //Load data to Treemap TreeMap<String, Object[]> data = new TreeMap<>(); //Add column headers data.put("-1", new Object[] { dm.getColumnName(0), dm.getColumnName(1), dm.getColumnName(2) }); //Add rows and cells for (int i = 0; i < dm.getRowCount(); i++) { data.put(Integer.toString(i), new Object[] { getCellValue(i, 0), getCellValue(i, 1), getCellValue(i, 2) }); } //write to excel file Set<String> ids = data.keySet(); XSSFRow row; int rowID = 0; for (String key : ids) { row = ws.createRow(rowID++); //get data as per key Object[] values = data.get(key); int cellID = 0; for (Object o : values) { Cell cell = row.createCell(cellID++); cell.setCellValue(o.toString()); } } //write to filesystem try { DateFormat dateFormat = new SimpleDateFormat("MM-dd-YYYY"); Date date = new Date(); String Cdate; Cdate = (dateFormat.format(date)); FileOutputStream fos = new FileOutputStream(new File("D:/Excel/TotalSummaryReport" + Cdate + ".xlsx")); wb.write(fos); fos.close(); } catch (FileNotFoundException ex) { ex.printStackTrace(); // Logger.getLogger(WorkBookNSheet.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { ex.printStackTrace(); //Logger.getLogger(MemberReportGUI.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:cn.comgroup.tzmedia.server.report.CustomerOrderReport.java
public CustomerOrderReportResult runOrderReport(String deployPath, List<CustomerOrder> orders) throws ParseException, FileNotFoundException, IOException { String reportTemplate = deployPath + File.separator + "template" + File.separator + "CustomerOrderReport.xlsx"; final XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(reportTemplate)); DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); XSSFSheet sheet = workbook.getSheetAt(0); int startRow = 1; for (CustomerOrder co : orders) { XSSFRow row = sheet.getRow(startRow); if (row == null) { row = sheet.createRow(startRow); }/*from w w w . j a v a 2s . c o m*/ XSSFCell cellOrderNumber = row.getCell(0); if (cellOrderNumber == null) { cellOrderNumber = row.createCell(0); } XSSFCell cellOrderDate = row.getCell(1); if (cellOrderDate == null) { cellOrderDate = row.createCell(1); } XSSFCell cellOrderType = row.getCell(2); if (cellOrderType == null) { cellOrderType = row.createCell(2); } XSSFCell cellOrderStatus = row.getCell(3); if (cellOrderStatus == null) { cellOrderStatus = row.createCell(3); } XSSFCell cellETN = row.getCell(4); if (cellETN == null) { cellETN = row.createCell(4); } XSSFCell cellShop = row.getCell(5); if (cellShop == null) { cellShop = row.createCell(5); } XSSFCell cellUserId = row.getCell(6); if (cellUserId == null) { cellUserId = row.createCell(6); } XSSFCell cellUserName = row.getCell(7); if (cellUserName == null) { cellUserName = row.createCell(7); } XSSFCell cellPaymentTerm = row.getCell(8); if (cellPaymentTerm == null) { cellPaymentTerm = row.createCell(8); } XSSFCell cellOrderAmount = row.getCell(9); if (cellOrderAmount == null) { cellOrderAmount = row.createCell(9); } XSSFCell cellCouponAmount = row.getCell(10); if (cellCouponAmount == null) { cellCouponAmount = row.createCell(10); } cellOrderNumber.setCellValue(co.getOrderNumber()); if (co.getOrderDate() != null) { cellOrderDate.setCellValue(dateFormat.format(co.getOrderDate().getTime())); } else { cellOrderDate.setCellValue(dateFormat.format(co.getOrderTime().getTime())); } cellOrderType.setCellValue(co.getOrderType().toString()); cellOrderStatus.setCellValue(co.getOrderStatus().toString()); if (co.getExternalTransactionNumber() != null) { cellETN.setCellValue(co.getExternalTransactionNumber()); } cellShop.setCellValue(co.getShopName()); cellUserId.setCellValue(co.getUserId()); cellUserName.setCellValue(co.getUserName()); cellPaymentTerm.setCellValue(co.getPaymentTerm().toString()); cellOrderAmount.setCellValue(co.getOrderAmount()); cellCouponAmount.setCellValue(co.getCouponAmount()); startRow++; System.out.println(startRow + " orders in the report"); } DateFormat dateTimeFormat = new SimpleDateFormat("yyyy-MM-dd-HHMMSS"); String reportName = "CustomerOrderReport" + dateTimeFormat.format(new Date()) + ".xlsx"; String reportPath = deployPath + File.separator + reportName; FileOutputStream fos = new FileOutputStream(reportPath); try (BufferedOutputStream bout = new BufferedOutputStream(fos)) { workbook.write(bout); bout.flush(); } return new CustomerOrderReportResult(reportName); }
From source file:co.com.runt.runistac.logica.ReporteLogica.java
public static byte[] generar(InputStream plantilla, Map<String, String> parametros, List<Object[]> datos) throws Exception { XSSFWorkbook wb = new XSSFWorkbook(plantilla); XSSFSheet mySheet = wb.getSheetAt(0); for (int i = 0; i < mySheet.getLastRowNum(); i++) { Row row = mySheet.getRow(i);/*from w ww . j ava 2 s . c o m*/ if (row != null && row.getCell(0) != null && row.getCell(0).getCellTypeEnum() == CellType.STRING) { for (String key : parametros.keySet()) { String valor = row.getCell(0).getStringCellValue(); valor = valor.replaceAll("\\{" + key + "\\}", parametros.get(key)); row.getCell(0).setCellValue(valor); } } } int rows = mySheet.getLastRowNum(); int i = 0; Row base = mySheet.getRow(rows); CellStyle[] cs = null; if (!datos.isEmpty()) { int cant = datos.get(0).length; cs = new CellStyle[cant]; for (int j = 0; j < cant; j++) { cs[j] = base.getCell(j).getCellStyle(); } } for (Object[] o : datos) { Row row = mySheet.createRow(rows + i); for (int j = 0; j < o.length; j++) { Cell c = row.createCell(j); String value = ""; if (o[j] != null) { if (o[j] instanceof String) { value = (String) o[j]; c.setCellValue(value); } else if (o[j] instanceof Integer) {//integer c.setCellValue((Integer) o[j]); } else if (o[j] instanceof Double) { c.setCellValue((Double) o[j]); } else if (o[j] instanceof Float) { c.setCellValue((Float) o[j]); } else if (o[j] instanceof BigDecimal) { c.setCellValue(((BigDecimal) o[j]).doubleValue()); } else if (o[j] instanceof Date) { c.setCellValue(((Date) o[j])); } else if (o[j] instanceof BigInteger) { c.setCellValue(((BigInteger) o[j]).intValue()); } else { c.setCellValue(o[j].toString()); System.out.println("No se encontro tipo: " + j + "-" + o[j].getClass()); } } c.setCellStyle(cs[j]); } i++; } ByteArrayOutputStream baos = new ByteArrayOutputStream(); wb.write(baos); return baos.toByteArray(); }