List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook createCellStyle
@Override
public HSSFCellStyle createCellStyle()
From source file:HSSFReadWrite.java
License:Apache License
/** * given a filename this outputs a sample sheet with just a set of * rows/cells.//from ww w . j a va 2 s . com */ private static void testCreateSampleSheet(String outputFilename) throws IOException { int rownum; HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet(); HSSFCellStyle cs = wb.createCellStyle(); HSSFCellStyle cs2 = wb.createCellStyle(); HSSFCellStyle cs3 = wb.createCellStyle(); HSSFFont f = wb.createFont(); HSSFFont f2 = wb.createFont(); f.setFontHeightInPoints((short) 12); f.setColor((short) 0xA); f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); f2.setFontHeightInPoints((short) 10); f2.setColor((short) 0xf); f2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cs.setFont(f); cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)")); cs2.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs2.setFillPattern((short) 1); // fill w fg cs2.setFillForegroundColor((short) 0xA); cs2.setFont(f2); wb.setSheetName(0, "HSSF Test"); for (rownum = 0; rownum < 300; rownum++) { HSSFRow r = s.createRow(rownum); if ((rownum % 2) == 0) { r.setHeight((short) 0x249); } for (int cellnum = 0; cellnum < 50; cellnum += 2) { HSSFCell c = r.createCell(cellnum); c.setCellValue(rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000))); if ((rownum % 2) == 0) { c.setCellStyle(cs); } c = r.createCell(cellnum + 1); c.setCellValue(new HSSFRichTextString("TEST")); // 50 characters divided by 1/20th of a point s.setColumnWidth(cellnum + 1, (int) (50 * 8 / 0.05)); if ((rownum % 2) == 0) { c.setCellStyle(cs2); } } } // draw a thick black border on the row at the bottom using BLANKS rownum++; rownum++; HSSFRow r = s.createRow(rownum); cs3.setBorderBottom(HSSFCellStyle.BORDER_THICK); for (int cellnum = 0; cellnum < 50; cellnum++) { HSSFCell c = r.createCell(cellnum); c.setCellStyle(cs3); } s.addMergedRegion(new CellRangeAddress(0, 3, 0, 3)); s.addMergedRegion(new CellRangeAddress(100, 110, 100, 110)); // end draw thick black border // create a sheet, set its title then delete it s = wb.createSheet(); wb.setSheetName(1, "DeletedSheet"); wb.removeSheetAt(1); // end deleted sheet FileOutputStream out = new FileOutputStream(outputFilename); wb.write(out); out.close(); }
From source file:Console.java
static public void exportToExcel(String sheetName, ArrayList headers, ArrayList data, File outputFile) throws HPSFException { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(sheetName); int rowIdx = 0; short cellIdx = 0; // Header/* w w w . j a v a2s . c o m*/ HSSFRow hssfHeader = sheet.createRow(rowIdx); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); for (Iterator cells = headers.iterator(); cells.hasNext();) { HSSFCell hssfCell = hssfHeader.createCell(cellIdx++); hssfCell.setCellStyle(cellStyle); hssfCell.setCellValue((String) cells.next()); } // Data rowIdx = 1; for (Iterator rows = data.iterator(); rows.hasNext();) { ArrayList row = (ArrayList) rows.next(); HSSFRow hssfRow = sheet.createRow(rowIdx++); cellIdx = 0; for (Iterator cells = row.iterator(); cells.hasNext();) { HSSFCell hssfCell = hssfRow.createCell(cellIdx++); Object o = cells.next(); if ("class java.lang.Double".equals(o.getClass().toString())) { hssfCell.setCellValue((Double) o); } else { hssfCell.setCellValue((String) o); } } } wb.setSheetName(0, sheetName); try { FileOutputStream outs = new FileOutputStream(outputFile); wb.write(outs); outs.close(); // System.out.println("Archivo creado correctamente en " + outputFile.getAbsolutePath()); } catch (IOException e) { e.printStackTrace(); throw new HPSFException(e.getMessage()); } }
From source file:Adicionales.Abrir_xml.java
private void ExportarEtiquetasXml(HSSFWorkbook workbook, Document document, String nombre) { System.out.println("Exportando : " + nombre); try {/*from ww w . j a v a2 s .com*/ HSSFSheet sheet = workbook.createSheet(tipo_comprobante + " " + (nombres.indexOf(nombre) + 1)); System.out.println("Primera vez creada"); HSSFCellStyle cellStyle = workbook.createCellStyle(); HSSFRow rowTag = sheet.createRow(0); HSSFRow RowData = sheet.createRow(1); NodeList nodeList = document.getElementsByTagName("*"); for (int i = 0; i < nodeList.getLength(); i++) { Element element = (Element) nodeList.item(i); if (element.getChildNodes().getLength() == 1 && !element.getNodeName().contains(":")) { if (element.getFirstChild().getNodeType() == Node.TEXT_NODE && !element.getNodeName().equals("comprobante")) { HSSFCell cellTag = rowTag.createCell(i); cellTag.setCellValue(element.getNodeName()); HSSFCell cellData = RowData.createCell(i); cellData.setCellValue(element.getFirstChild().getNodeValue()); sheet.autoSizeColumn((short) i); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellTag.setCellStyle(cellStyle); } else { DocumentBuilderFactory sub_factory = DocumentBuilderFactory.newInstance(); DocumentBuilder sub_builder = sub_factory.newDocumentBuilder(); Document sub_document = sub_builder .parse(new InputSource(new StringReader(element.getFirstChild().getNodeValue()))); NodeList sub_nodeList = sub_document.getElementsByTagName("*"); for (int j = 0; j < sub_nodeList.getLength(); j++) { Element sub_element = (Element) sub_nodeList.item(j); if (sub_element.getNodeName().equals("Signature")) break; if (sub_element.getChildNodes().getLength() == 1 && !sub_element.getNodeName().contains(":")) { if (sub_element.getFirstChild().getNodeType() == Node.TEXT_NODE) { HSSFCell cellTag = rowTag.createCell(j); cellTag.setCellValue(sub_element.getNodeName()); HSSFCell cellData = RowData.createCell(j); cellData.setCellValue(sub_element.getFirstChild().getNodeValue()); sheet.autoSizeColumn((short) j); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellTag.setCellStyle(cellStyle); } } } } } } } catch (IOException e) { System.out.println(e.getMessage()); } catch (IllegalArgumentException e) { JOptionPane.showMessageDialog(null, "<html>Error al exportar archivo " + nombre + "<br>Verificar maximo de etiquetas soportadas [255]</html>"); } catch (ParserConfigurationException e) { System.out.println("ParserConfigurationException " + e.getMessage()); } catch (SAXException e) { System.out.println("SAXException " + e.getMessage()); } }
From source file:Almacen.Conciliacion.java
private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed // TODO add your handling code here: try {//ww w . j a v a2s . c o m javax.swing.JFileChooser archivo = new javax.swing.JFileChooser(); archivo.setFileFilter(new ExtensionFileFilter("Excel document (*.xls)", new String[] { "xls" })); String ruta = null; if (archivo.showSaveDialog(null) == archivo.APPROVE_OPTION) { ruta = archivo.getSelectedFile().getAbsolutePath(); if (ruta != null) { File archivoXLS = new File(ruta + ".xls"); Session session = HibernateUtil.getSessionFactory().openSession(); ArrayList datos = new ArrayList(); Query query = session.createSQLQuery( "select compania.nombre, orden.tipo_nombre, orden.modelo, orden.no_serie, clientes.nombre as nombres,orden.id_orden \n" + "from orden inner join compania on compania.id_compania=orden.id_compania inner join clientes on clientes.id_clientes=orden.id_cliente\n" + "where orden.id_orden=" + Integer.parseInt(orden) + ""); query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP); datos = (ArrayList) query.list(); // Path FROM = Paths.get("imagenes/plantillaConciliacion.xls"); Path TO = Paths.get(ruta + ".xls"); //sobreescribir el fichero de destino, si existe, y copiar // los atributos, incluyendo los permisos rwx CopyOption[] options = new CopyOption[] { StandardCopyOption.REPLACE_EXISTING, StandardCopyOption.COPY_ATTRIBUTES }; Files.copy(FROM, TO, options); FileInputStream miPlantilla = new FileInputStream(archivoXLS); POIFSFileSystem fsFileSystem = new POIFSFileSystem(miPlantilla); HSSFWorkbook libro = new HSSFWorkbook(fsFileSystem); libro.getSheet("Conciliacion").getRow(0).getCell(6) .setCellValue("CONCILIACIN PARA FACTURACIN"); for (int i = 0; i < datos.size(); i++) { java.util.HashMap map = (java.util.HashMap) datos.get(i); libro.getSheet("Conciliacion").getRow(1).getCell(2) .setCellValue(map.get("nombre").toString()); libro.getSheet("Conciliacion").getRow(2).getCell(2) .setCellValue(map.get("tipo_nombre").toString()); libro.getSheet("Conciliacion").getRow(3).getCell(2) .setCellValue(map.get("modelo").toString()); libro.getSheet("Conciliacion").getRow(4).getCell(2) .setCellValue(map.get("no_serie").toString()); libro.getSheet("Conciliacion").getRow(5).getCell(2) .setCellValue(map.get("nombres").toString()); libro.getSheet("Conciliacion").getRow(2).getCell(12) .setCellValue(map.get("id_orden").toString()); } HSSFCellStyle borde_d = libro.createCellStyle(); borde_d.setBorderBottom(CellStyle.BORDER_THIN); borde_d.setBorderTop(CellStyle.BORDER_THIN); borde_d.setBorderRight(CellStyle.BORDER_THIN); borde_d.setBorderLeft(CellStyle.BORDER_THIN); borde_d.setAlignment(CellStyle.ALIGN_RIGHT); HSSFCellStyle borde_i = libro.createCellStyle(); borde_i.setBorderBottom(CellStyle.BORDER_THIN); borde_i.setBorderTop(CellStyle.BORDER_THIN); borde_i.setBorderRight(CellStyle.BORDER_THIN); borde_i.setBorderLeft(CellStyle.BORDER_THIN); borde_i.setAlignment(CellStyle.ALIGN_LEFT); HSSFCellStyle borde_c = libro.createCellStyle(); borde_c.setBorderBottom(CellStyle.BORDER_THIN); borde_c.setBorderTop(CellStyle.BORDER_THIN); borde_c.setBorderRight(CellStyle.BORDER_THIN); borde_c.setBorderLeft(CellStyle.BORDER_THIN); borde_c.setAlignment(CellStyle.ALIGN_CENTER); HSSFCellStyle borde_dr = libro.createCellStyle(); borde_dr.setBorderBottom(CellStyle.BORDER_THIN); borde_dr.setBorderTop(CellStyle.BORDER_THIN); borde_dr.setBorderRight(CellStyle.BORDER_THIN); borde_dr.setBorderLeft(CellStyle.BORDER_THIN); borde_dr.setAlignment(CellStyle.ALIGN_RIGHT); borde_dr.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); borde_dr.setFillBackgroundColor(HSSFColor.LIGHT_BLUE.index); borde_dr.setFillForegroundColor(HSSFColor.YELLOW.index); HSSFCellStyle borde_ir = libro.createCellStyle(); borde_ir.setBorderBottom(CellStyle.BORDER_THIN); borde_ir.setBorderTop(CellStyle.BORDER_THIN); borde_ir.setBorderRight(CellStyle.BORDER_THIN); borde_ir.setBorderLeft(CellStyle.BORDER_THIN); borde_ir.setAlignment(CellStyle.ALIGN_LEFT); borde_ir.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); borde_ir.setFillBackgroundColor(HSSFColor.LIGHT_BLUE.index); borde_ir.setFillForegroundColor(HSSFColor.YELLOW.index); HSSFCellStyle borde_cr = libro.createCellStyle(); borde_cr.setBorderBottom(CellStyle.BORDER_THIN); borde_cr.setBorderTop(CellStyle.BORDER_THIN); borde_cr.setBorderRight(CellStyle.BORDER_THIN); borde_cr.setBorderLeft(CellStyle.BORDER_THIN); borde_cr.setAlignment(CellStyle.ALIGN_CENTER); borde_cr.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); borde_cr.setFillBackgroundColor(HSSFColor.LIGHT_BLUE.index); borde_cr.setFillForegroundColor(HSSFColor.YELLOW.index); DecimalFormat formatoPorcentaje = new DecimalFormat("#,##0.00"); int miRenglon = 9; int num_tab = t_datos.getRowCount(); for (int i = 0; i < num_tab; i++) { for (int j = 0; j < 4; j++) { int renglon = 0; switch (j) { case 0: renglon = 8; break; case 1: renglon = 10; break; case 2: renglon = 11; break; case 3: renglon = 12; break; } if ((Double.parseDouble(t_datos.getValueAt(i, renglon).toString()) > 0 && t_datos.getValueAt(i, 9).toString().compareTo("N") == 0) || ((Double.parseDouble(t_datos.getValueAt(i, renglon).toString()) > 0 && renglon >= 10)) || (renglon == 8 && Double.parseDouble(t_datos.getValueAt(i, 10).toString()) <= 0 && Double.parseDouble(t_datos.getValueAt(i, 11).toString()) <= 0 && Double.parseDouble(t_datos.getValueAt(i, 12).toString()) <= 0)) { if ((boolean) t_datos.getValueAt(i, 3) == true || (boolean) t_datos.getValueAt(i, 4) == true) { libro.getSheet("Conciliacion").createRow(miRenglon); //columna0 if (t_datos.getValueAt(i, 5) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(0) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(0) .setCellValue(t_datos.getValueAt(i, 5).toString()); } //columna1 if (t_datos.getValueAt(i, 6) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(1) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(1) .setCellValue(t_datos.getValueAt(i, 6).toString()); } //columna2 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(2) .setCellValue(t_datos.getValueAt(i, renglon).toString()); //columna3 if (t_datos.getValueAt(i, 14) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(3) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(3) .setCellValue(t_datos.getValueAt(i, 14).toString()); } //columna4 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(4) .setCellValue(t_datos.getValueAt(i, 2).toString()); //columna5 if (renglon == 8 && t_datos.getValueAt(i, 9).toString().compareTo("-") == 0) libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue(""); else { switch (renglon) { case 8: libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue("N"); break; case 10: libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue("D"); break; case 11: libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue("R"); break; case 12: libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue("M"); break; } } //columna6 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(6) .setCellValue(formatoPorcentaje.format(t_datos.getValueAt(i, 15))); //columna7 $tot aut. double n; n = BigDecimal .valueOf(Double.parseDouble(t_datos.getValueAt(i, renglon).toString()) * Double.parseDouble(t_datos.getValueAt(i, 15).toString())) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(7) .setCellValue(formatoPorcentaje.format(n)); //columna8 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(8) .setCellValue(formatoPorcentaje.format(t_datos.getValueAt(i, 16))); //columna9 $tot com n = BigDecimal .valueOf(Double.parseDouble(t_datos.getValueAt(i, renglon).toString()) * Double.parseDouble(t_datos.getValueAt(i, 16).toString())) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(9) .setCellValue(formatoPorcentaje.format(n)); //columna10 11 if (renglon == 8 && t_datos.getValueAt(i, 9).toString().compareTo("-") == 0) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(""); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(""); } else { switch (renglon) { case 8: n = BigDecimal.valueOf( Double.parseDouble(t_datos.getValueAt(i, 16).toString()) / 0.9d) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(formatoPorcentaje.format(n * Double.parseDouble( t_datos.getValueAt(i, renglon).toString()))); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(formatoPorcentaje.format(n)); break; case 10: n = BigDecimal .valueOf( Double.parseDouble(t_datos.getValueAt(i, 15).toString()) * 0.72d) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(formatoPorcentaje.format(n * Double.parseDouble( t_datos.getValueAt(i, renglon).toString()))); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(formatoPorcentaje.format(n)); break; case 11: n = BigDecimal .valueOf( Double.parseDouble(t_datos.getValueAt(i, 15).toString()) * 0.65d) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(formatoPorcentaje.format(n * Double.parseDouble( t_datos.getValueAt(i, renglon).toString()))); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(formatoPorcentaje.format(n)); break; case 12: n = BigDecimal .valueOf( Double.parseDouble(t_datos.getValueAt(i, 15).toString()) * 0.65d) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(formatoPorcentaje.format(n * Double.parseDouble( t_datos.getValueAt(i, renglon).toString()))); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(formatoPorcentaje.format(n)); break; } } //columna12 if (t_datos.getValueAt(i, 18) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(12) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(12) .setCellValue(t_datos.getValueAt(i, 18).toString()); } //columna13 if (t_datos.getValueAt(i, 19) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(13) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(13) .setCellValue(t_datos.getValueAt(i, 19).toString()); } //columna14 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(14) .setCellValue("V"); if (renglon == 8 && t_datos.getValueAt(i, 9).toString().compareTo("-") == 0) { libro.getSheet("Conciliacion").getRow(miRenglon).getCell(0) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(1) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(2) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(3) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(4) .setCellStyle(borde_i); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(5) .setCellStyle(borde_c); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(6) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(7) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(8) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(9) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(10) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(11) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(12) .setCellStyle(borde_i); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(13) .setCellStyle(borde_i); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(14) .setCellStyle(borde_d); } else { libro.getSheet("Conciliacion").getRow(miRenglon).getCell(0) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(1) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(2) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(3) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(4) .setCellStyle(borde_ir); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(5) .setCellStyle(borde_cr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(6) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(7) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(8) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(9) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(10) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(11) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(12) .setCellStyle(borde_ir); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(13) .setCellStyle(borde_ir); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(14) .setCellStyle(borde_dr); } miRenglon++; } } } } //font1.setColor(BaseColor.WHITE); libro.getSheet("Conciliacion").createRow(miRenglon); libro.getSheet("Conciliacion") .addMergedRegion(new CellRangeAddress(miRenglon, miRenglon, 0, 14)); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(0) .setCellValue("Faltante en Vales"); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(0).setCellStyle(borde_c); miRenglon++; for (int i = 0; i < num_tab; i++) { for (int j = 0; j < 4; j++) { int renglon = 0; switch (j) { case 0: renglon = 8; break; case 1: renglon = 10; break; case 2: renglon = 11; break; case 3: renglon = 12; break; } if ((Double.parseDouble(t_datos.getValueAt(i, renglon).toString()) > 0 && t_datos.getValueAt(i, 9).toString().compareTo("N") == 0) || ((Double.parseDouble(t_datos.getValueAt(i, renglon).toString()) > 0 && renglon >= 10)) || (renglon == 8 && Double.parseDouble(t_datos.getValueAt(i, 10).toString()) <= 0 && Double.parseDouble(t_datos.getValueAt(i, 11).toString()) <= 0 && Double.parseDouble(t_datos.getValueAt(i, 12).toString()) <= 0)) { if ((boolean) t_datos.getValueAt(i, 3) == false && (boolean) t_datos.getValueAt(i, 4) == false && t_datos.getValueAt(i, 5) != null) { libro.getSheet("Conciliacion").createRow(miRenglon); //columna0 if (t_datos.getValueAt(i, 5) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(0) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(0) .setCellValue(t_datos.getValueAt(i, 5).toString()); } //columna1 if (t_datos.getValueAt(i, 6) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(1) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(1) .setCellValue(t_datos.getValueAt(i, 6).toString()); } //columna2 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(2) .setCellValue(t_datos.getValueAt(i, renglon).toString()); //columna3 if (t_datos.getValueAt(i, 14) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(3) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(3) .setCellValue(t_datos.getValueAt(i, 14).toString()); } //columna4 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(4) .setCellValue(t_datos.getValueAt(i, 2).toString()); //columna5 if (renglon == 8 && t_datos.getValueAt(i, 9).toString().compareTo("-") == 0) libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue(""); else { switch (renglon) { case 8: libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue("N"); break; case 10: libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue("D"); break; case 11: libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue("R"); break; case 12: libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue("M"); break; } } //columna6 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(6) .setCellValue(formatoPorcentaje.format(t_datos.getValueAt(i, 15))); //columna7 $tot aut. double n; n = BigDecimal .valueOf(Double.parseDouble(t_datos.getValueAt(i, renglon).toString()) * Double.parseDouble(t_datos.getValueAt(i, 15).toString())) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(7) .setCellValue(formatoPorcentaje.format(n)); //columna8 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(8) .setCellValue(formatoPorcentaje.format(t_datos.getValueAt(i, 16))); //columna9 $tot com n = BigDecimal .valueOf(Double.parseDouble(t_datos.getValueAt(i, renglon).toString()) * Double.parseDouble(t_datos.getValueAt(i, 16).toString())) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(9) .setCellValue(formatoPorcentaje.format(n)); //columna10 11 if (renglon == 8 && t_datos.getValueAt(i, 9).toString().compareTo("-") == 0) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(""); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(""); } else { switch (renglon) { case 8: n = BigDecimal.valueOf( Double.parseDouble(t_datos.getValueAt(i, 16).toString()) / 0.9d) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(formatoPorcentaje.format(n * Double.parseDouble( t_datos.getValueAt(i, renglon).toString()))); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(formatoPorcentaje.format(n)); break; case 10: n = BigDecimal .valueOf( Double.parseDouble(t_datos.getValueAt(i, 15).toString()) * 0.72d) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(formatoPorcentaje.format(n * Double.parseDouble( t_datos.getValueAt(i, renglon).toString()))); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(formatoPorcentaje.format(n)); break; case 11: n = BigDecimal .valueOf( Double.parseDouble(t_datos.getValueAt(i, 15).toString()) * 0.65d) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(formatoPorcentaje.format(n * Double.parseDouble( t_datos.getValueAt(i, renglon).toString()))); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(formatoPorcentaje.format(n)); break; case 12: n = BigDecimal .valueOf( Double.parseDouble(t_datos.getValueAt(i, 15).toString()) * 0.65d) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(formatoPorcentaje.format(n * Double.parseDouble( t_datos.getValueAt(i, renglon).toString()))); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(formatoPorcentaje.format(n)); break; } } //columna12 if (t_datos.getValueAt(i, 18) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(12) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(12) .setCellValue(t_datos.getValueAt(i, 18).toString()); } //columna13 if (t_datos.getValueAt(i, 19) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(13) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(13) .setCellValue(t_datos.getValueAt(i, 19).toString()); } //columna14 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(14) .setCellValue(""); if (renglon == 8 && t_datos.getValueAt(i, 9).toString().compareTo("-") == 0) { libro.getSheet("Conciliacion").getRow(miRenglon).getCell(0) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(1) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(2) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(3) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(4) .setCellStyle(borde_i); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(5) .setCellStyle(borde_c); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(6) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(7) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(8) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(9) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(10) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(11) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(12) .setCellStyle(borde_i); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(13) .setCellStyle(borde_i); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(14) .setCellStyle(borde_d); } else { libro.getSheet("Conciliacion").getRow(miRenglon).getCell(0) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(1) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(2) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(3) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(4) .setCellStyle(borde_ir); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(5) .setCellStyle(borde_cr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(6) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(7) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(8) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(9) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(10) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(11) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(12) .setCellStyle(borde_ir); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(13) .setCellStyle(borde_ir); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(14) .setCellStyle(borde_dr); } miRenglon++; } } } } FileOutputStream archivo1 = new FileOutputStream(archivoXLS); libro.write(archivo1); archivo1.close(); Desktop.getDesktop().open(archivoXLS); } } } catch (Exception e) { e.printStackTrace(); } }
From source file:at.htlpinkafeld.beans.AlleAbwesenheitenBean.java
/** * xls post processing//w w w . j a va 2 s . co m * * @param document xls document */ public void postProcessXLS(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0); HSSFRow header = sheet.getRow(0); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) { HSSFCell cell = header.getCell(i); cell.setCellStyle(cellStyle); sheet.autoSizeColumn(i); } HSSFRow bottomRow = sheet.createRow(sheet.getLastRowNum() + 2); bottomRow.createCell(0) .setCellValue("Stand: " + LocalDate.now().format(DateTimeFormatter.ofPattern("dd.MM.yyyy"))); }
From source file:at.htlpinkafeld.beans.JahresuebersichtBean.java
/** * post processes the XLS for creating/* w w w . j a va2 s .com*/ * * @param document xls-doc */ public void postProcessXLS(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0); sheet.shiftRows(0, sheet.getLastRowNum(), 2); HSSFRow topRow = sheet.createRow(0); topRow.createCell(0).setCellValue("Jahresbersicht - " + selectedYear.getYear()); topRow.createCell(3).setCellValue("von " + selectedUser.getPersName()); sheet.createRow(1).createCell(0).setCellValue(" "); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2)); HSSFRow header = sheet.getRow(2); HSSFRow footer = sheet.getRow(sheet.getLastRowNum()); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) { HSSFCell cell = header.getCell(i); cell.setCellStyle(cellStyle); cell = footer.getCell(i); cell.setCellStyle(cellStyle); sheet.autoSizeColumn(i); } HSSFRow bottomRow = sheet.createRow(sheet.getLastRowNum() + 2); bottomRow.createCell(0) .setCellValue("Stand: " + LocalDate.now().format(DateTimeFormatter.ofPattern("dd.MM.yyyy"))); }
From source file:at.htlpinkafeld.beans.UserDetailsBean.java
public void postProcessXLS(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0); sheet.shiftRows(0, sheet.getLastRowNum(), 2); HSSFRow topRow = sheet.createRow(0); topRow.createCell(0)/*from w ww . j a v a 2s .c o m*/ .setCellValue("Monatsbersicht - " + selectedDate.format(DateTimeFormatter.ofPattern("MM.yyyy"))); topRow.createCell(7).setCellValue("von " + selectedUser); sheet.createRow(1).createCell(0).setCellValue(" "); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6)); HSSFRow header = sheet.getRow(2); HSSFRow footer = sheet.getRow(sheet.getLastRowNum()); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) { HSSFCell cell = header.getCell(i); cell.setCellStyle(cellStyle); cell = footer.getCell(i); cell.setCellStyle(cellStyle); sheet.autoSizeColumn(i); } HSSFRow bottomRow = sheet.createRow(sheet.getLastRowNum() + 2); bottomRow.createCell(0) .setCellValue("Stand: " + LocalDate.now().format(DateTimeFormatter.ofPattern("dd.MM.yyyy"))); }
From source file:attandance.standalone.manager.AttandanceManager.java
private void outputAttandance(List<LateRecord> lates, List<AbsenceRecord> absences) { // webbookExcel HSSFWorkbook wb = new HSSFWorkbook(); // webbooksheet,Excelsheet HSSFSheet sheet = wb.createSheet(""); int width = ((int) (20 * 1.14388)) * 256; sheet.setColumnWidth(0, width);/*from w w w .j ava 2 s. co m*/ sheet.setColumnWidth(1, width); sheet.setColumnWidth(2, width); sheet.setColumnWidth(3, width); // sheet0,??poiExcel?short HSSFRow row = sheet.createRow((int) 0); // ? HSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // ? HSSFCell cell = row.createCell((short) 0); cell.setCellValue("??"); cell.setCellStyle(style); cell = row.createCell((short) 1); cell.setCellValue(""); cell.setCellStyle(style); cell = row.createCell((short) 2); cell.setCellValue("?"); cell.setCellStyle(style); cell = row.createCell((short) 3); cell.setCellValue("?"); cell.setCellStyle(style); // ? ?? int i = 0; for (; i < lates.size(); i++) { row = sheet.createRow((int) i + 1); LateRecord lateStaff = lates.get(i); // ? row.createCell((short) 0).setCellValue(lateStaff.getStaffName()); row.createCell((short) 1).setCellValue(lateStaff.getCaculateDateString()); cell = row.createCell((short) 2); cell.setCellValue(new SimpleDateFormat(DateHelper.DATE_FORMAT).format(lateStaff.getLateDate())); row.createCell((short) 3).setCellValue(lateStaff.getLateTimeDesc()); } for (int j = 0; j < absences.size(); j++) { row = sheet.createRow((int) i + j + 1); AbsenceRecord absenceStaff = absences.get(j); // ? row.createCell((short) 0).setCellValue(absenceStaff.getStaffName()); row.createCell((short) 1).setCellValue(absenceStaff.getCaculateDateString()); cell = row.createCell((short) 2); cell.setCellValue( new SimpleDateFormat(DateHelper.ONLY_DATE_FORMAT).format(absenceStaff.getAbsenceDate())); row.createCell((short) 3).setCellValue("?"); } // ? try { String fileName = "C:/xhuxing-private/" + new Date(System.currentTimeMillis()).getMonth() + ".xls"; FileOutputStream fout = new FileOutputStream(fileName); wb.write(fout); fout.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:be.vds.jtbdive.client.view.core.dive.profile.DiveProfileExcelParser.java
License:Open Source License
public void export(Date date, DiveProfile diveProfile, File file) throws IOException { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(UIAgent.getInstance().getExportFormatDateHoursFull().format(date)); HSSFCellStyle cellStyle = wb.createCellStyle(); HSSFFont font = wb.createFont();/*from www .j ava 2 s .c om*/ font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyle.setFont(font); int rowIndex = 0; int colIndex = 0; HSSFRow row = sheet.createRow(rowIndex++); HSSFCell cell = row.createCell(colIndex++); cell.setCellValue("Time (s)"); cell.setCellStyle(cellStyle); cell = row.createCell(colIndex++); cell.setCellValue("Depth (m)"); cell.setCellStyle(cellStyle); cell = row.createCell(colIndex++); cell.setCellValue("Ascent Warning"); cell.setCellStyle(cellStyle); cell = row.createCell(colIndex++); cell.setCellValue("Bottom Time Warning"); cell.setCellStyle(cellStyle); cell = row.createCell(colIndex++); cell.setCellValue("Deco Ceiling Warning"); cell.setCellStyle(cellStyle); cell = row.createCell(colIndex++); cell.setCellValue("Deco Entry"); cell.setCellStyle(cellStyle); Map<Double, Double> entries = diveProfile.getDepthEntries(); List<Double> seconds = new ArrayList<Double>(entries.keySet()); Collections.sort(seconds); for (Double time : seconds) { colIndex = 0; row = sheet.createRow(rowIndex++); row.createCell(colIndex++, HSSFCell.CELL_TYPE_NUMERIC).setCellValue(time); row.createCell(colIndex++, HSSFCell.CELL_TYPE_NUMERIC).setCellValue(entries.get(time)); row.createCell(colIndex++, HSSFCell.CELL_TYPE_BOOLEAN) .setCellValue(diveProfile.getAscentWarnings().contains(time)); row.createCell(colIndex++, HSSFCell.CELL_TYPE_BOOLEAN) .setCellValue(diveProfile.getRemainingBottomTimeWarnings().contains(time)); row.createCell(colIndex++, HSSFCell.CELL_TYPE_BOOLEAN) .setCellValue(diveProfile.getDecoCeilingWarnings().contains(time)); row.createCell(colIndex++, HSSFCell.CELL_TYPE_BOOLEAN) .setCellValue(diveProfile.getDecoEntries().contains(time)); } for (int i = 0; i <= colIndex; i++) { sheet.autoSizeColumn(i); } FileOutputStream fileOut = new FileOutputStream(file); wb.write(fileOut); fileOut.close(); }
From source file:bean.BankProfileData.java
public void export0() { Integer columnNo;//from www .j a v a 2 s . c om HSSFWorkbook workbook; HSSFSheet sheet; HSSFRow headerRow, dataRow, totalRow = null; HSSFCell cell; HSSFCellStyle cellStyle, boldStyle; HSSFFont font; ThemeDisplay themeDisplay = LiferayFacesContext.getInstance().getThemeDisplay(); getExportData().createFolder(null, themeDisplay, "Bank SL Report", "DESCRIPTION"); if (getExportData().getFilename() == null || getExportData().getFilename().length() == 0) { getExportData().setFilename("Default(" + new Date() + ")"); } getExportData().setFilename(getExportData().getFilename().replace(":", "")); try { getExportData().setFilename(getExportData().getFilename().concat(".xls")); workbook = new HSSFWorkbook(); cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00")); font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); boldStyle = workbook.createCellStyle(); boldStyle.setFont(font); for (int i = 0; i < getAccountsWithSubsidiaryData().getAccountCodesFiltered().size(); i++) { try { sheet = workbook.createSheet(getDataConvert() .accountCodeConvert(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i))); } catch (Exception e) { sheet = workbook.createSheet(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i)); } headerRow = sheet.createRow((short) 0); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue(getDataConvert() .accountCodeConvert(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i))); cell.setCellStyle(boldStyle); try { headerRow = sheet.createRow(headerRow.getRowNum() + 1); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue("As of " + getCustomDate() .formatDate(getAccountsWithSubsidiaryData().getReportDate(), "MM-dd-YYYY")); cell.setCellStyle(boldStyle); } catch (Exception e) { getAccountsWithSubsidiaryData().setReportDate(getCustomDate().getCurrentDate()); cell.setCellValue("As of " + getCustomDate() .formatDate(getAccountsWithSubsidiaryData().getReportDate(), "MM-dd-YYYY")); cell.setCellStyle(boldStyle); } if (getAccountsWithSubsidiaryData().getAcctCreateDateFrom() != null || getAccountsWithSubsidiaryData().getAcctCreateDateTo() != null) { headerRow = sheet.createRow(headerRow.getRowNum() + 1); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue(getAccountsWithSubsidiaryData().getAcctCreateDateTo() != null ? "Account Created Date: " + getCustomDate() .formatDate( getAccountsWithSubsidiaryData().getAcctCreateDateFrom(), "MM-dd-YYYY") .concat(" - ") .concat(getCustomDate().formatDate( getAccountsWithSubsidiaryData().getAcctCreateDateTo(), "MM-dd-YYYY")) : "Account Created Date: " + getCustomDate().formatDate( getAccountsWithSubsidiaryData().getAcctCreateDateFrom(), "MM-dd-YYYY")); cell.setCellStyle(boldStyle); } if ((getAccountsWithSubsidiaryData().getAmountFilter() != null && getAccountsWithSubsidiaryData().getAmountFilter().compareTo(BigDecimal.ZERO) == 1)) { headerRow = sheet.createRow(headerRow.getRowNum() + 1); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue("Amount Range: " + getDataConvert() .numericConvert(getAccountsWithSubsidiaryData().getAmountFilter().doubleValue())); cell.setCellStyle(boldStyle); } headerRow = sheet.createRow(headerRow.getRowNum() + 1); headerRow = sheet.createRow(headerRow.getRowNum() + 1); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue("Account No."); cell.setCellStyle(boldStyle); cell = headerRow.createCell(columnNo++); cell.setCellValue("Name"); cell.setCellStyle(boldStyle); cell = headerRow.createCell(columnNo++); cell.setCellValue("Account Status"); cell.setCellStyle(boldStyle); cell = headerRow.createCell(columnNo++); cell.setCellValue("Balance"); cell.setCellStyle(boldStyle); for (int ii = 0; ii < getBankProfileSummary().get(i).size(); ii++) { columnNo = 0; dataRow = sheet.createRow(headerRow.getRowNum() + ii + 1); dataRow.createCell(columnNo++) .setCellValue(getBankProfileSummary().get(i).get(ii)[2].toString()); dataRow.createCell(columnNo++) .setCellValue(getBankProfileSummary().get(i).get(ii)[4].toString()); dataRow.createCell(columnNo++).setCellValue(getDataConvert() .acctStatusConvert(getBankProfileSummary().get(i).get(ii)[6].toString().charAt(0))); cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((BigDecimal) getBankProfileSummary().get(i).get(ii)[5]).doubleValue()); cell.setCellStyle(cellStyle); totalRow = sheet.createRow((short) dataRow.getRowNum() + 2); } if (getBankProfileSummary().get(i).size() > 0) { cell = totalRow.createCell(1); cell.setCellValue("TOTAL"); cell.setCellStyle(boldStyle); cell = totalRow.createCell(2); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(getAccountsWithSubsidiaryData().getSubtotal().get(i).doubleValue()); cell.setCellStyle(cellStyle); } } FileOutputStream fileOutputStream = new FileOutputStream(getExportData().getFilename()); workbook.write(fileOutputStream); fileOutputStream.close(); getExportData().fileUploadByDL(getExportData().getFilename(), "Bank SL Report", themeDisplay, null); File file = new File(getExportData().getFilename()); if (file.exists()) { file.delete(); } } catch (Exception e) { System.out.print("bankProfileData().export0() " + e); FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error", "An error occurred while generating excel file."); FacesContext.getCurrentInstance().addMessage(null, message); } }