List of usage examples for org.apache.poi.hssf.usermodel HSSFCellStyle setAlignment
@Override public void setAlignment(HorizontalAlignment align)
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/*from w w w.j a va 2s . 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 w ww . j ava 2s. co m 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 {//from ww w .j a v a 2 s . 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:angiotool.SaveToExcel.java
License:Open Source License
private HSSFCellStyle headingCellStyle() { HSSFFont font = wb.createFont(); // defaults to Arial on windows font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // bold HSSFCellStyle style = wb.createCellStyle(); // make style style.setFont(font); // set font style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // set alignment to centered return style; }
From source file:angiotool.SaveToExcel.java
License:Open Source License
private HSSFCellStyle plainCellStyle() { HSSFFont font = wb.createFont(); // defaults to Arial on windows font.setBoldweight(HSSFFont.DEFAULT_CHARSET); // bold HSSFCellStyle style = wb.createCellStyle(); // make style style.setFont(font); // set font style.setAlignment(HSSFCellStyle.ALIGN_LEFT); // set alignment to centered return style; }
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 av a 2 s . c o 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:br.com.nfsconsultoria.azcontrole.bean.VendaBean.java
public void postProcessXLS(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0);//from w w w. j ava 2 s . c om HSSFRow header = sheet.getRow(0); HSSFFont font = wb.createFont(); font.setBold(true); font.setColor(HSSFColor.WHITE.index); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setWrapText(true); cellStyle.setAlignment(CellStyle.ALIGN_JUSTIFY); cellStyle.setFont(font); for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) { HSSFCell cell = header.getCell(i); cell.setCellStyle(cellStyle); } }
From source file:cn.edu.henu.rjxy.lms.controller.Acdemic.java
@RequestMapping("daochuxuesheng") public void daochuxuesheng(HttpServletRequest request, HttpServletResponse response) throws IOException { HttpSession session = request.getSession(); session.setAttribute("state", null); // ???? /*from w w w.jav a2 s. c o m*/ response.setContentType("application/vnd.ms-excel"); String codedFileName; codedFileName = "??"; OutputStream fOut = null; try { // ???? codedFileName = java.net.URLEncoder.encode("", "UTF-8"); response.setHeader("content-disposition", "attachment;filename=" + "student_messsage" + ".xls"); HSSFWorkbook wb = new HSSFWorkbook(); // webbooksheet,Excelsheet HSSFSheet sheet = wb.createSheet(""); // 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); cell = row.createCell((short) 4); cell.setCellValue(""); cell.setCellStyle(style); cell = row.createCell((short) 5); cell.setCellValue(""); cell.setCellStyle(style); cell = row.createCell((short) 6); cell.setCellValue("?"); cell.setCellStyle(style); cell = row.createCell((short) 7); cell.setCellValue("qq"); cell.setCellStyle(style); List allstudent = StudentDao.getAllStudent(); for (int i = 0; i < allstudent.size(); i++) { StudentWithoutPwd tmp = (StudentWithoutPwd) allstudent.get(i); row = sheet.createRow((int) i + 1); row.createCell((short) 0).setCellValue(tmp.getStudentSn()); row.createCell((short) 1).setCellValue(tmp.getStudentName()); row.createCell((short) 2).setCellValue(tmp.getStudentIdcard()); row.createCell((short) 3).setCellValue(tmp.getStudentGrade()); row.createCell((short) 4).setCellValue((tmp.isStudentSex()) ? ("") : ("")); row.createCell((short) 5).setCellValue(tmp.getStudentCollege()); row.createCell((short) 6).setCellValue(tmp.getStudentTel()); row.createCell((short) 7).setCellValue(tmp.getStudentQq()); } fOut = response.getOutputStream(); wb.write(fOut); } catch (UnsupportedEncodingException e1) { } catch (Exception e) { } finally { fOut.flush(); fOut.close(); session.setAttribute("state", "open"); } System.out.println("?..."); }
From source file:cn.edu.henu.rjxy.lms.controller.Acdemic.java
@RequestMapping("daochualltoxls") public void exportExcel(HttpServletRequest request, HttpServletResponse response) throws IOException { HttpSession session = request.getSession(); System.out.println("1..."); session.setAttribute("state", null); // ???? /*from w w w . j a v a 2 s . c o m*/ response.setContentType("application/vnd.ms-excel"); String codedFileName; codedFileName = "??"; OutputStream fOut = null; try { // ???? codedFileName = java.net.URLEncoder.encode("", "UTF-8"); response.setHeader("content-disposition", "attachment;filename=" + "teacher_messsage" + ".xls"); HSSFWorkbook wb = new HSSFWorkbook(); // webbooksheet,Excelsheet HSSFSheet sheet = wb.createSheet(""); // 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); cell = row.createCell((short) 4); cell.setCellValue(""); cell.setCellStyle(style); cell = row.createCell((short) 5); cell.setCellValue("qq"); cell.setCellStyle(style); cell = row.createCell((short) 6); cell.setCellValue("?"); cell.setCellStyle(style); cell = row.createCell((short) 7); cell.setCellValue(""); cell.setCellStyle(style); List allteacher = TeacherDao.getAllTeacher(); for (int i = 0; i < allteacher.size(); i++) { TempTeacherWithoutPwd tmp = (TempTeacherWithoutPwd) allteacher.get(i); row = sheet.createRow((int) i + 1); row.createCell((short) 0).setCellValue(tmp.getTeacherSn()); row.createCell((short) 1).setCellValue(tmp.getTeacherName()); row.createCell((short) 2).setCellValue(tmp.getTeacherIdcard()); row.createCell((short) 3).setCellValue(tmp.getTeacherPosition()); row.createCell((short) 4).setCellValue(tmp.getTeacherCollege()); row.createCell((short) 5).setCellValue(tmp.getTeacherQq()); row.createCell((short) 6).setCellValue(tmp.getTeacherTel()); row.createCell((short) 7).setCellValue((tmp.getTeacherSex()) ? ("") : ("")); } fOut = response.getOutputStream(); wb.write(fOut); } catch (UnsupportedEncodingException e1) { } catch (Exception e) { } finally { fOut.flush(); fOut.close(); session.setAttribute("state", "open"); } System.out.println("?..."); }
From source file:cn.edu.henu.rjxy.lms.controller.DeanController.java
@RequestMapping("dean/daochuxuesheng") public void daochuxuesheng(HttpServletRequest request, HttpServletResponse response) throws IOException { HttpSession session = request.getSession(); session.setAttribute("state", null); // ???? //from ww w .j a va 2 s .co m response.setContentType("application/vnd.ms-excel"); String codedFileName; codedFileName = "??"; OutputStream fOut = null; try { // ???? codedFileName = java.net.URLEncoder.encode("", "UTF-8"); response.setHeader("content-disposition", "attachment;filename=" + "student_messsage" + ".xls"); HSSFWorkbook wb = new HSSFWorkbook(); // webbooksheet,Excelsheet HSSFSheet sheet = wb.createSheet(""); // 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); cell = row.createCell((short) 4); cell.setCellValue(""); cell.setCellStyle(style); cell = row.createCell((short) 5); cell.setCellValue(""); cell.setCellStyle(style); cell = row.createCell((short) 6); cell.setCellValue("?"); cell.setCellStyle(style); cell = row.createCell((short) 7); cell.setCellValue("qq"); cell.setCellStyle(style); List allstudent = StudentDao.getAllStudent(); for (int i = 0; i < allstudent.size(); i++) { StudentWithoutPwd tmp = (StudentWithoutPwd) allstudent.get(i); row = sheet.createRow((int) i + 1); row.createCell((short) 0).setCellValue(tmp.getStudentSn()); row.createCell((short) 1).setCellValue(tmp.getStudentName()); row.createCell((short) 2).setCellValue(tmp.getStudentIdcard()); row.createCell((short) 3).setCellValue(tmp.getStudentGrade()); row.createCell((short) 4).setCellValue((tmp.isStudentSex()) ? ("") : ("")); row.createCell((short) 5).setCellValue(tmp.getStudentCollege()); row.createCell((short) 6).setCellValue(tmp.getStudentTel()); row.createCell((short) 7).setCellValue(tmp.getStudentQq()); } fOut = response.getOutputStream(); wb.write(fOut); } catch (UnsupportedEncodingException e1) { } catch (Exception e) { } finally { fOut.flush(); fOut.close(); session.setAttribute("state", "open"); } System.out.println("?..."); }