List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook write
private void write(POIFSFileSystem fs) throws IOException
From source file:com.zhu.action.CarAction.java
public void exportpeople(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) {/*from www.j a va 2s. c o m*/ CarForm carForm = (CarForm) form; // webbookExcel 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(0); cell.setCellValue("??"); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue("???"); cell.setCellStyle(style); // ? ?? OrderService orderService = new OrderService(); List<Orderinfo> list = orderService.getOrderCarDetail(carForm.getId()); System.out.println(list.size()); for (int i = 0; i < list.size(); i++) { row = sheet.createRow(i + 1); Orderinfo order = (Orderinfo) list.get(i); // ? row.createCell(0).setCellValue(order.getN1()); row.createCell(1).setCellValue(order.getI1()); row = sheet.createRow(i + list.size() + 1); row.createCell(0).setCellValue(order.getN2()); row.createCell(1).setCellValue(order.getI2()); row = sheet.createRow(i + list.size() + list.size() + 1); row.createCell(0).setCellValue(order.getN3()); row.createCell(1).setCellValue(order.getI3()); row = sheet.createRow(i + list.size() + list.size() + list.size() + 1); row.createCell(0).setCellValue(order.getN4()); row.createCell(1).setCellValue(order.getI4()); row = sheet.createRow(i + list.size() + list.size() + list.size() + list.size() + 1); row.createCell(0).setCellValue(order.getN5()); row.createCell(1).setCellValue(order.getI5()); row = sheet.createRow(i + list.size() + list.size() + list.size() + list.size() + list.size() + 1); row.createCell(0).setCellValue(order.getN6()); row.createCell(1).setCellValue(order.getI6()); row = sheet.createRow( i + list.size() + list.size() + list.size() + list.size() + list.size() + list.size() + 1); row.createCell(0).setCellValue(order.getN7()); row.createCell(1).setCellValue(order.getI7()); row = sheet.createRow(i + list.size() + list.size() + list.size() + list.size() + list.size() + list.size() + list.size() + 1); row.createCell(0).setCellValue(order.getN8()); row.createCell(1).setCellValue(order.getI8()); row = sheet.createRow(i + list.size() + list.size() + list.size() + list.size() + list.size() + list.size() + list.size() + list.size() + 1); row.createCell(0).setCellValue(order.getN9()); row.createCell(1).setCellValue(order.getI9()); row = sheet.createRow(i + list.size() + list.size() + list.size() + list.size() + list.size() + list.size() + list.size() + list.size() + list.size() + 1); row.createCell(0).setCellValue(order.getN10()); row.createCell(1).setCellValue(order.getI10()); } // ? String filePath = ""; Date dt = new Date(); DateFormat df = new SimpleDateFormat("yyyyMMddHHmmss"); String date = df.format(dt).toString(); filePath = "/Users/Nemo/Documents/carpeople" + date + ".xls"; File file = new File(filePath); try { // FileOutputStream fout = new FileOutputStream( // "/Users/Nemo/Documents/carpeople.xls"); // wb.write(fout); // fout.close(); OutputStream out = new FileOutputStream(file); wb.write(out); out.close(); } catch (Exception e) { e.printStackTrace(); } int key = 0; int MaxRowNum = 0, MaxCellNum = 0; try { FileInputStream in = new FileInputStream(filePath); POIFSFileSystem fs = new POIFSFileSystem(in); HSSFWorkbook workbook = new HSSFWorkbook(fs); FileOutputStream out = new FileOutputStream("/Users/Nemo/Documents/carpeople" + date + ".xls"); int number = workbook.getNumberOfSheets(); for (int i = 0; i < number; i++) { // ?sheet sheet = workbook.getSheetAt(i); // 14 System.out.println("" + sheet.getSheetName() + " ? " + (sheet.getLastRowNum() + 1)); MaxRowNum = 0; for (int k = 0; k <= sheet.getLastRowNum(); k++) { HSSFRow hRow = sheet.getRow(k); // System.out.println((k + 1) + ""); if (isBlankRow(hRow)) // { int m = 0; for (m = k + 1; m <= sheet.getLastRowNum(); m++) { HSSFRow nhRow = sheet.getRow(m); if (!isBlankRow(nhRow)) { // System.out.println("?" + (m + 1)); sheet.shiftRows(m, sheet.getLastRowNum(), k - m); break; } } if (m > sheet.getLastRowNum()) break; // ? } else { // ? MaxRowNum++; if (MaxCellNum < hRow.getLastCellNum()) MaxCellNum = hRow.getLastCellNum(); } } workbook.setPrintArea(i, 0, MaxCellNum, 0, MaxRowNum); System.out .println("?? " + sheet.getSheetName() + " " + MaxRowNum); } workbook.write(out); in.close(); out.close(); } catch (IOException e) { System.out.println(key + " " + e.getMessage() + " "); e.printStackTrace(); } System.out.println("??"); }
From source file:com.zhu.action.CarAction.java
public void export(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) {/* w ww .ja v a2s . co m*/ // webbookExcel 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(0); cell.setCellValue("id"); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue("?"); cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue(""); cell.setCellStyle(style); cell = row.createCell(3); cell.setCellValue(""); cell.setCellStyle(style); cell = row.createCell(4); cell.setCellValue(""); cell.setCellStyle(style); cell = row.createCell(5); cell.setCellValue(""); cell.setCellStyle(style); cell = row.createCell(6); cell.setCellValue(""); cell.setCellStyle(style); cell = row.createCell(7); cell.setCellValue("?"); cell.setCellStyle(style); cell = row.createCell(8); cell.setCellValue("?"); cell.setCellStyle(style); // ? ?? CarService carService = new CarService(); List<Car> list = carService.getCar(); for (int i = 0; i < list.size(); i++) { row = sheet.createRow((int) i + 1); Car car = (Car) list.get(i); // ? row.createCell(0).setCellValue(car.getId()); row.createCell(1).setCellValue(car.getCarid()); row.createCell(2).setCellValue(car.getLicense()); row.createCell(3).setCellValue(car.getNumber()); row.createCell(4).setCellValue(car.getPeople()); row.createCell(5).setCellValue(car.getRoute()); row.createCell(6).setCellValue(car.getStartdate()); row.createCell(7).setCellValue(car.getEnddate()); if (car.getStatus() == 0) { row.createCell(8).setCellValue(""); } else if (car.getStatus() == 1) { row.createCell(8).setCellValue(""); } else { row.createCell(8).setCellValue(""); } // cell = row.createCell((short) 3); } // ? String filePath = ""; Date dt = new Date(); DateFormat df = new SimpleDateFormat("yyyyMMddHHmmss"); String date = df.format(dt).toString(); filePath = "/Users/Nemo/Documents/car" + date + ".xls"; File file = new File(filePath); try { FileOutputStream fout = new FileOutputStream(file); wb.write(fout); fout.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:common.PoiExcelHelper.java
/** * * @param exportMapDatas ?/*w ww .j a v a 2s . c o m*/ * @param columnsExplain ? * @param sheetName * @param outFile ? * @return * @throws FileNotFoundException * @throws IOException */ public static boolean productExcelFile(List<Map<String, String>> exportMapDatas, Map<String, String> columnsExplain, String sheetName, File outFile) throws FileNotFoundException, IOException { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(sheetName); //set columnTitle int rownum = 1, cellnum = 0; Set<String> keysSet = columnsExplain.keySet(); Row columnRowTitle = sheet.createRow(0); for (String key : keysSet) { Cell columnCellTitle = columnRowTitle.createCell(cellnum++); columnCellTitle.setCellValue(columnsExplain.get(key)); } for (Map<String, String> exportMapData : exportMapDatas) { Row sheetRow = sheet.createRow(rownum++); cellnum = 0; for (String key : keysSet) { Cell cell = sheetRow.createCell(cellnum++); cell.setCellValue(exportMapData.get(key)); } } FileOutputStream out = new FileOutputStream(outFile); workbook.write(out); out.close(); return true; }
From source file:Compras.Conciliacion.java
private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed // TODO add your handling code here: try {//from w w w. j av a 2s .com 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"); File plantilla = new File("imagenes/plantillaConciliacion.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 formatoDecimal = new DecimalFormat("####0.0"); DecimalFormat formatoPorcentaje = new DecimalFormat("#,##0.00"); int miRenglon = 9; for (int i = 0; i < t_datos.getRowCount(); 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 < t_datos.getRowCount(); 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:control.listaEquipos.java
private boolean generaExcel(String path) { // Se crea el libro HSSFWorkbook libro = new HSSFWorkbook(); // Se crea una hoja dentro del libro HSSFSheet hoja = libro.createSheet("Equipos"); // Se crea una fila dentro de la hoja HSSFRow fila = hoja.createRow(0);// w ww.j a va2 s .c o m // Se crea una celda dentro de la fila HSSFCell celda = fila.createCell((short) 0); HSSFRichTextString texto = new HSSFRichTextString("Id "); celda.setCellValue(texto); celda = fila.createCell((short) 1); texto = new HSSFRichTextString("Nombre"); celda.setCellValue(texto); celda = fila.createCell((short) 2); texto = new HSSFRichTextString("Categoria"); celda.setCellValue(texto); celda = fila.createCell((short) 3); texto = new HSSFRichTextString("Accesorios"); celda.setCellValue(texto); celda = fila.createCell((short) 4); texto = new HSSFRichTextString("Descripcion"); celda.setCellValue(texto); celda = fila.createCell((short) 5); texto = new HSSFRichTextString("Marca"); celda.setCellValue(texto); celda = fila.createCell((short) 6); texto = new HSSFRichTextString("Estado"); celda.setCellValue(texto); int nr = 1; Object[][] tabla = this.getTableData(tabEquipo); for (Object[] o : tabla) { fila = hoja.createRow(nr); int i = 0; for (Object e : o) { celda = fila.createCell((short) i); if (i <= 6) { if (e != null) { texto = new HSSFRichTextString((String) e.toString()); } else { texto = new HSSFRichTextString(""); } celda.setCellValue(texto); } i++; } nr++; } // Se salva el libro. try { FileOutputStream elFichero = new FileOutputStream(path); libro.write(elFichero); elFichero.close(); } catch (Exception e) { e.printStackTrace(); } return true; }
From source file:Controlador.ControladorCargueMasivo.java
public boolean exportarExcel() { dataModelReporte();//from w w w. ja v a2 s . com HSSFWorkbook hssfWorkbook = obtenerExcel(dataModelContenido, dataModelCabecera, "log cargue masivo"); try { FileOutputStream fileOutputStream = new FileOutputStream("log_cargue.xls"); hssfWorkbook.write(fileOutputStream); fileOutputStream.close(); File x = new File("log_cargue.xls"); String path = x.getAbsolutePath(); path = path.replace("\\", "\\" + "\\"); System.out.println("path " + path); Runtime.getRuntime().exec("cmd /c start " + path); return true; } catch (Exception e) { e.printStackTrace(); return false; } }
From source file:Controlador.HSSFCreate.java
/** Processes requests for both HTTP GET and POST methods. * @param request servlet request/*from ww w . jav a2 s. co m*/ * @param response servlet response */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("application/vnd.ms-excel"); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("new sheet"); Map<String, Object[]> data = new HashMap<String, Object[]>(); data.put("1", new Object[] { "Emp No.", "Name", "Salary" }); data.put("2", new Object[] { 1d, "John", 1500000d }); data.put("3", new Object[] { 2d, "Sam", 800000d }); data.put("4", new Object[] { 3d, "Dean", 700000d }); Set<String> keyset = data.keySet(); int rownum = 0; for (String key : keyset) { Row row = sheet.createRow(rownum++); Object[] objArr = data.get(key); int cellnum = 0; for (Object obj : objArr) { Cell cell = row.createCell(cellnum++); if (obj instanceof Date) cell.setCellValue((Date) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Double) cell.setCellValue((Double) obj); } } // Write the output OutputStream out = response.getOutputStream(); wb.write(out); out.close(); }
From source file:Controlador.HSSFCreateAeronave.java
/** * Processes requests for both HTTP GET and POST methods. * * @param request servlet request//from w ww .ja va2s. c o m * @param response servlet response */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, URISyntaxException { response.setContentType("application/vnd.ms-excel"); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("new sheet"); Aeronaves a = new Aeronaves(); ArrayList<Aeronave> aeronaves = (ArrayList<Aeronave>) a.findAll(); Map<String, Object[]> data = new HashMap<String, Object[]>(); data.put("0", new Object[] { "id Aeronave", "Nombre" }); for (int i = 0; i < aeronaves.size(); i++) { String j = "" + (i + 1); data.put(j, new Object[] { "" + aeronaves.get(i).getIdAeronave(), String.copyValueOf(aeronaves.get(i).getNombre()) }); } Set<String> keyset = data.keySet(); int rownum = 0; for (String key : keyset) { Row row = sheet.createRow(rownum++); Object[] objArr = data.get(key); int cellnum = 0; for (Object obj : objArr) { Cell cell = row.createCell(cellnum++); if (obj instanceof Date) { cell.setCellValue((Date) obj); } else if (obj instanceof Boolean) { cell.setCellValue((Boolean) obj); } else if (obj instanceof String) { cell.setCellValue((String) obj); } else if (obj instanceof Double) { cell.setCellValue((Double) obj); } } } // Write the output OutputStream out = response.getOutputStream(); wb.write(out); out.close(); }
From source file:Controlador.jControlador.java
public boolean imprimirExcel(File archivo, JTable table) { try {/*from w ww . jav a 2 s. c o m*/ HSSFWorkbook libro = new HSSFWorkbook(); HSSFSheet hoja = libro.createSheet(); for (int i = 0; i <= table.getRowCount(); i++) { HSSFRow fila = hoja.createRow(i); if (i == 0) { for (int j = 0; j < table.getColumnCount(); j++) { HSSFCell celda = fila.createCell(j); celda.setCellValue(new HSSFRichTextString( table.getColumnModel().getColumn(j).getHeaderValue().toString())); } } if (i != 0) { for (int j = 0; j < table.getColumnCount(); j++) { HSSFCell celda = fila.createCell(j); if (table.getValueAt(i - 1, j) != null) celda.setCellValue(new HSSFRichTextString(table.getValueAt(i - 1, j).toString())); } } } FileOutputStream elFichero = new FileOutputStream(archivo.getAbsolutePath() + ".xls"); libro.write(elFichero); elFichero.close(); return true; } catch (Exception e) { e.printStackTrace(); } return false; }
From source file:controladores.controladorPrincipal.java
public boolean generarReporte() { try {//from w w w .j a va 2s . co m modeloCobranzas cobranza = new modeloCobranzas(); Object[][] facturas = cobranza.listarFacturadasGestion(); String file = "Reporte_cobranza_" + formatDate.format(new Date()) + ".xls"; HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("FirstSheet"); HSSFRow rowhead = sheet.createRow((short) 0); rowhead.createCell(0).setCellValue("Folio"); rowhead.createCell(1).setCellValue("Rut"); rowhead.createCell(2).setCellValue("Razn social"); rowhead.createCell(3).setCellValue("Fecha emisin"); rowhead.createCell(4).setCellValue("Das emisin"); rowhead.createCell(5).setCellValue("Neto"); rowhead.createCell(6).setCellValue("Iva"); rowhead.createCell(7).setCellValue("Total"); rowhead.createCell(8).setCellValue("Forma de pago"); rowhead.createCell(9).setCellValue("Medio de pago"); rowhead.createCell(10).setCellValue("Abono"); rowhead.createCell(11).setCellValue("Monto abono"); rowhead.createCell(12).setCellValue("Saldo"); rowhead.createCell(13).setCellValue("Contacto"); rowhead.createCell(14).setCellValue("Telfono"); rowhead.createCell(15).setCellValue("N de gestiones"); rowhead.createCell(16).setCellValue("Tipo gestin"); rowhead.createCell(17).setCellValue("Resultado"); rowhead.createCell(18).setCellValue("Fecha gestin"); rowhead.createCell(19).setCellValue("Observaciones"); int i = 1; for (Object[] fac : facturas) { rowhead = sheet.createRow(i); i++; rowhead.createCell(0).setCellValue(fac[0].toString()); rowhead.createCell(1).setCellValue(fac[1].toString()); rowhead.createCell(2).setCellValue(fac[2].toString()); rowhead.createCell(3).setCellValue(fac[3].toString()); rowhead.createCell(4).setCellValue(fac[4].toString()); rowhead.createCell(5).setCellValue(fac[5].toString()); rowhead.createCell(6).setCellValue(fac[6].toString()); rowhead.createCell(7).setCellValue(fac[7].toString()); rowhead.createCell(8).setCellValue(fac[8].toString()); rowhead.createCell(9).setCellValue(fac[9].toString()); rowhead.createCell(10).setCellValue(fac[10].toString()); rowhead.createCell(11).setCellValue(fac[11].toString()); rowhead.createCell(12).setCellValue(fac[12].toString()); rowhead.createCell(13).setCellValue(fac[13].toString()); rowhead.createCell(14).setCellValue(fac[14].toString()); rowhead.createCell(15).setCellValue(fac[15].toString()); rowhead.createCell(16).setCellValue(fac[16].toString()); rowhead.createCell(17).setCellValue(fac[17].toString()); rowhead.createCell(18).setCellValue(fac[18].toString()); rowhead.createCell(19).setCellValue(fac[19].toString()); FileOutputStream fileOut; fileOut = new FileOutputStream(file); workbook.write(fileOut); fileOut.close(); } } catch (IOException ex) { Logger.getLogger(controladorPrincipal.class.getName()).log(Level.SEVERE, null, ex); JOptionPane.showMessageDialog(null, ex.getMessage(), "ERROR", JOptionPane.INFORMATION_MESSAGE); System.out.println(ex.getMessage()); return false; } catch (Exception e) { JOptionPane.showMessageDialog(null, e.getMessage(), "ERROR", JOptionPane.INFORMATION_MESSAGE); e.printStackTrace(); return false; } JOptionPane.showMessageDialog(null, "Reporte generado con xito", "Operacin exitosa", JOptionPane.INFORMATION_MESSAGE); return true; }