List of usage examples for org.apache.poi.ss.usermodel Sheet createRow
Row createRow(int rownum);
From source file:com.inet.web.service.spi.download.ExportEmailWriterSpiService.java
License:Open Source License
/** * write group header// ww w . j a v a 2 s .co m * @param sheet * @param group */ private void writeGroupHeader(Sheet sheet, LdapGroup group, CellStyle style) { Row headerRow = sheet.createRow(index); headerRow.setHeightInPoints(12); Cell cell = headerRow.createCell(STT); cell.setCellValue(group.getName() + " - " + group.getDescription()); cell.setCellStyle(style); sheet.addMergedRegion(new CellRangeAddress(index, index++, STT, MOBILE)); }
From source file:com.inet.web.service.spi.download.ExportEmailWriterSpiService.java
License:Open Source License
/** * /*w w w. j av a 2 s. c o m*/ * @param ws * @param cf * @param contact * @param index * @throws WriteException */ private void writeRecord(Sheet sheet, AccountExportInfo contact, String domain, int seq) { if (contact == null) { return; } Row headerRow = sheet.createRow(index++); headerRow.setHeightInPoints(12); headerRow.createCell(STT).setCellValue(seq); headerRow.createCell(FULL_NAME).setCellValue(contact.getFullName()); headerRow.createCell(EMAIL).setCellValue(contact.getAccount()); headerRow.createCell(QUOTA).setCellValue(contact.getQuota()); headerRow.createCell(LAST_NAME).setCellValue(contact.getLastName()); headerRow.createCell(MIDDLE_NAME).setCellValue(contact.getMiddleName()); headerRow.createCell(FIRST_NAME).setCellValue(contact.getFirstName()); headerRow.createCell(TITLE).setCellValue(contact.getPosition()); headerRow.createCell(TELEPHONE).setCellValue(contact.getTelephone()); headerRow.createCell(MOBILE).setCellValue(contact.getMobile()); }
From source file:com.ipcglobal.fredimport.xls.DistinctCategoriesSpreadsheet.java
License:Apache License
/** * Creates the sheet./*from ww w . j a v a 2 s. c om*/ * * @param distinctCategoryItems the distinct category items * @throws Exception the exception */ public void createSheet(Collection<DistinctCategoryItem> distinctCategoryItems) throws Exception { List<XlsDefItem> xlsDefItems = initHhdrWidthItems(); String sheetName = "DistinctCategoryItems"; Sheet sheet = wb.createSheet(sheetName); processColumnWidths(sheet, xlsDefItems); sheet.createFreezePane(0, 1, 0, 1); // freeze top row sheet.setAutoFilter(CellRangeAddress.valueOf("A1:AB1")); // hack - i know the number of columns sheet.getPrintSetup().setLandscape(true); sheet.setAutobreaks(true); sheet.getPrintSetup().setFitWidth((short) 1); sheet.getPrintSetup().setFitHeight((short) 1); int rowCnt = 0; // Header int colCnt = 0; Row rowHdr = sheet.createRow(rowCnt); for (XlsDefItem xlsDefItem : xlsDefItems) { Cell cellHdr = rowHdr.createCell(colCnt, Cell.CELL_TYPE_STRING); CellStyle style = findCellStyle("Arial", HSSFColor.WHITE.index, (short) 11, XSSFFont.BOLDWEIGHT_BOLD, cellStyleFromHdrAlign(HdrAlign.Left), XSSFCellStyle.VERTICAL_TOP, HSSFColor.LIGHT_BLUE.index, CellBorder.All_Thin, formatGeneral); style.setWrapText(true); cellHdr.setCellStyle(style); cellHdr.setCellValue(xlsDefItem.getName()); colCnt++; } rowCnt++; // Data for (DistinctCategoryItem distinctCategoryItem : distinctCategoryItems) { Row rowData = sheet.createRow(rowCnt); int colNum = 0; for (XlsDefItem xlsDefItem : xlsDefItems) populateCell(rowData, colNum++, xlsDefItem.getDataType(), getByNameAsString(distinctCategoryItem, xlsDefItem.getName())); rowCnt++; } }
From source file:com.ipn.mx.vistas.VistaDatosReporte.java
private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed // TODO add your handling code here: String nombrearchivo = recibenombre.getText(); periodo = recibeperiodo.getText();// w w w .ja va2s.c o m Validaciones val = new Validaciones(); boolean n = val.sololetras(nombrearchivo); boolean p = val.periodo(periodo); if (n && p) { try { //C:\Users\Clemente\Desktop\ESCOM\lavadodinero\nuevacarpetatt2\pldtt2cab String rutaArchivo = "C:\\Users\\bdfe_\\Desktop\\pldtt2cab" + "\\" + nombrearchivo + ".xls"; System.out.println(rutaArchivo); OperacionDAO d = new OperacionDAO(); for (int z = 0; z < listareportes.size(); z++) { System.out.println("el valor de z es" + z); Operacion ope = listareportes.get(z); //puse esta en vez de la primera linea comentada String a = Integer.toString(ope.getIdOperacion()); System.out.println("el valor de a es" + a); rs = d.DatosReportes(a); while (rs.next()) { alarmas = rs.getInt("numalarmas"); idCliente = rs.getInt("id_Cliente"); clave = rs.getString("clave"); CPINM = rs.getString("codigoPostal"); tipoOp = rs.getString("clavetipoOp"); localidad = rs.getString("EntidadFede"); Instrumento = rs.getString("monetarioclave"); numCuenta = daes.decrypt(rs.getString("numeroContrato")); monto = rs.getString("monto"); moneda = rs.getString("claveMoneda"); fechaop = rs.getString("fechaOperacion"); nacionalidad = rs.getString("paisOrigen"); tipopersona = rs.getString("id_tipo"); Razonsocial = rs.getString("nombre"); nombre = daes.decrypt(rs.getString("nombre")); ApPat = daes.decrypt(rs.getString("apellido_Pat")); ApMat = daes.decrypt(rs.getString("apellido_Mat")); RFC = daes.decrypt(rs.getString("RFC")); fechanac = rs.getString("fecha_nac"); domicilio = rs.getString("calle"); ciudad = rs.getString("clave"); telefono = daes.decrypt(rs.getString("numero_Telefono")); actividad = rs.getString("folio"); descripcion = daes.decrypt(rs.getString("detalleop")); } if (alarmas == 1) { tipoReporte = "1"; t = nrep.obtencantidadContratos(idCliente); while (t.next()) { numContrato = t.getInt("cantidadContratos"); } String[] ContratosCliente = new String[numContrato]; t = nrep.obtenContratos(idCliente); while (t.next()) { ContratosCliente[i] = t.getString("numeroContrato"); i++; } for (int j = 0; j < ContratosCliente.length; j++) { t = nrep.AlarmasporContrato(ContratosCliente[j]); while (t.next()) { auxalarmas = t.getInt("numeroalarmas"); } if (auxalarmas == 1) { consecutivo = consecutivo + ContratosCliente[j]; } } } else { tipoReporte = "2"; } if (nacionalidad.equals("1")) { } else { nacionalidad = "2"; } if (tipopersona.equals("1")) { Razonsocial = ""; } else { nombre = ""; } nrep.insertaReporte(ope.getIdOperacion(), rutaArchivo); //cambiar por rutaArchivo String b = Integer.toString(ope.getIdOperacion()); aux2 = nrep.VerReportes(b); while (aux2.next()) { folio = aux2.getString("folio"); } ResultSet t = nrep.VerAlarmas(ope.getIdOperacion()); //modifique el parametro while (t.next()) { razones = razones + t.getString("Descripcion"); } String[] datos = { tipoReporte, periodo, folio, organosup, clave, localidad, CPINM, tipoOp, Instrumento, numCuenta, monto, moneda, fechaop, fechadet, nacionalidad, tipopersona, Razonsocial, nombre, ApPat, ApMat, RFC, CURP, fechanac, domicilio, colonia, ciudad, telefono, actividad, consecutivo, numcuenta2, clave2, nombre2, appat2, apmat2, descripcion, razones }; list2.add(datos); } //}cierra el try try { File archivoXLS = new File(rutaArchivo); /*String []datos= {tipoReporte,periodo,folio,organosup,clave,localidad,CPINM,tipoOp,Instrumento,numCuenta,monto,moneda,fechaop,fechadet, nacionalidad,tipopersona,Razonsocial,nombre,ApPat,ApMat,RFC,CURP,fechanac,domicilio,colonia,ciudad,telefono,actividad, consecutivo,numcuenta2,clave2,nombre2,appat2,apmat2,descripcion,razones};*/ if (archivoXLS.exists()) archivoXLS.delete(); archivoXLS.createNewFile(); Workbook libro = new HSSFWorkbook(); FileOutputStream archivo = new FileOutputStream(archivoXLS); Sheet hoja = libro.createSheet("Reporte"); for (int f = 0; f < list2.size() + 1; f++) { /* crear las filas*/ Row fila = hoja.createRow(f); if (auxfor <= list2.size() - 1) { auxfor++; } for (int c = 0; c < encabezados.length; c++) { /*Creamos la celda a partir de la fila actual*/ Cell celda = fila.createCell(c); /*Si la fila es la nmero 0, estableceremos los encabezados*/ if (f == 0) { celda.setCellValue(encabezados[c]); } else { /*Si no es la primera fila establecemos un valor*/ String[] datos2 = list2.get(f - 1); celda.setCellValue(datos2[c]); System.out .println("tiene en :" + encabezados[c] + "el dato de:" + datos2[c] + "\n"); } } } /*Escribimos en el libro*/ libro.write(archivo); /*Cerramos el flujo de datos*/ archivo.close(); JOptionPane.showMessageDialog(null, "Archivo creado"); this.dispose(); } catch (IOException ex) { Logger.getLogger(VistaDatosReporte.class.getName()).log(Level.SEVERE, null, ex); JOptionPane.showMessageDialog(null, "Ocurrio un error intentelo nuevamente"); } } catch (SQLException ex) { Logger.getLogger(VistaDatosReporte.class.getName()).log(Level.SEVERE, null, ex); JOptionPane.showMessageDialog(null, "Ocurrio un error intentelo nuevamente"); } } else { JOptionPane.showMessageDialog(null, "Valores en los campos invalidos" + "\n" + "El nombre solo acpeta letras y una longitud de 15\n" + "El periodo debe tener el formato de AAAAMM "); } }
From source file:com.iqtb.validacion.managedbean.MbCfdisEmitidos.java
private boolean generarReporte(List<Cfdis> lista) { logger.info(usuario.getUserid() + ". Inicia Generar Reporte Excel"); boolean reporte = false; try {// www . j ava 2 s .co m String ruta = "/work/iqtb/validacionfiles/reportes/" + empresa.getRfc() + "/" + usuario.getUserid() + "/"; DateFormat df = new SimpleDateFormat("ddMMyyyy"); Date today = new Date(); String reportDate = df.format(today); String strNombreReporte = "EmitidosReporte_" + reportDate + ".xls"; File dir = new File(ruta); if (!dir.exists()) { dir.mkdirs(); } Workbook libro = new HSSFWorkbook(); FileOutputStream archivo = new FileOutputStream(ruta + strNombreReporte); logger.info(usuario.getUserid() + ". Archivo creado " + ruta + strNombreReporte); Sheet hoja = libro.createSheet(); int aux = 1; for (Cfdis var : lista) { if (aux == 1) { logger.info("Inicia escritura de cabecera"); Row fila = hoja.createRow((int) 0); Cell a = fila.createCell((int) 0); a.setCellValue("Sucursal"); Cell b = fila.createCell((int) 1); b.setCellValue("RFC Socio Comercial"); Cell c = fila.createCell((int) 2); c.setCellValue("Serie"); Cell d = fila.createCell((int) 3); d.setCellValue("Folio"); Cell e = fila.createCell((int) 4); e.setCellValue("UUID"); Cell f = fila.createCell((int) 5); f.setCellValue("Fecha"); Cell g = fila.createCell((int) 6); g.setCellValue("Numero Aprobacin"); Cell h = fila.createCell((int) 7); h.setCellValue("Ao Aprobacin"); Cell i = fila.createCell((int) 8); i.setCellValue("SubTotal"); Cell j = fila.createCell((int) 9); j.setCellValue("Descuento"); Cell k = fila.createCell((int) 10); k.setCellValue("Total"); Cell l = fila.createCell((int) 11); l.setCellValue("Tipo Moneda"); Cell m = fila.createCell((int) 12); m.setCellValue("Tipo Cambio"); Cell n = fila.createCell((int) 13); n.setCellValue("IVA"); Cell nn = fila.createCell((int) 14); nn.setCellValue("Estado Fiscal"); Cell o = fila.createCell((int) 15); o.setCellValue("Estado"); Cell p = fila.createCell((int) 16); p.setCellValue("Fecha Cancelacin"); Cell q = fila.createCell((int) 17); q.setCellValue("Fecha Modificacin"); Cell r = fila.createCell((int) 18); r.setCellValue("Estado Impresin"); Cell s = fila.createCell((int) 19); s.setCellValue("Pedimento"); Cell t = fila.createCell((int) 20); t.setCellValue("Fecha Pedimento"); Cell u = fila.createCell((int) 21); u.setCellValue("Aduana"); Cell v = fila.createCell((int) 22); v.setCellValue("Fecha Generacin"); Cell w = fila.createCell((int) 23); w.setCellValue("Estado Contable"); Cell x = fila.createCell((int) 24); x.setCellValue("Fecha Vencimineto"); Cell y = fila.createCell((int) 25); y.setCellValue("RFC Emisor"); Cell z = fila.createCell((int) 26); z.setCellValue("RFC Receptor"); Cell a1 = fila.createCell((int) 27); a1.setCellValue("Cadena Original"); logger.info("Termina escritura de cabecera"); } Row fila1 = hoja.createRow((int) aux); sucursal = daoSucursales.getSucursalByHql( "select new Sucursales(idSucursal, nombre) from Sucursales where idSucursal = " + var.getIdSucursal()); Cell a = fila1.createCell((int) 0); if (sucursal != null) { a.setCellValue(sucursal.getNombre()); } else { a.setCellValue(var.getIdSucursal()); } socioComercial = null; socioComercial = daoSociosComerciales.getSocioByHql( "select new SociosComerciales(idSocioComercial, rfc) from SociosComerciales where idSocioComercial = " + var.getIdSocioComercial()); Cell b = fila1.createCell((int) 1); if (socioComercial != null) { b.setCellValue(socioComercial.getRfc()); } else { b.setCellValue(var.getIdSocioComercial()); } Cell c = fila1.createCell((int) 2); c.setCellValue(var.getSerie()); Cell d = fila1.createCell((int) 3); if (var.getFolio() != null) { d.setCellValue(var.getFolio()); } else { d.setCellValue(""); } Cell e = fila1.createCell((int) 4); e.setCellValue(var.getUuid()); Cell f = fila1.createCell((int) 5); if (var.getFecha() != null) { f.setCellValue(var.getFecha().toString()); } else { f.setCellValue(""); } Cell g = fila1.createCell((int) 6); if (var.getNumeroAprobacion() != null) { g.setCellValue(var.getNumeroAprobacion()); } else { g.setCellValue(""); } Cell h = fila1.createCell((int) 7); if (var.getNumeroAprobacion() != null) { h.setCellValue(var.getAnioAprobacion()); } else { h.setCellValue(""); } Cell i = fila1.createCell((int) 8); if (var.getSubtotal() != null) { i.setCellValue(var.getSubtotal().toString()); } else { i.setCellValue(""); } Cell j = fila1.createCell((int) 9); if (var.getDescuento() != null) { j.setCellValue(var.getDescuento().toString()); } else { j.setCellValue(""); } Cell k = fila1.createCell((int) 10); if (var.getTotal() != null) { k.setCellValue(var.getTotal().toString()); } else { k.setCellValue(""); } Cell l = fila1.createCell((int) 11); l.setCellValue(var.getTipoMoneda()); Cell m = fila1.createCell((int) 12); if (var.getTipoCambio() != null) { m.setCellValue(var.getTipoCambio().toString()); } else { m.setCellValue(""); } Cell n = fila1.createCell((int) 13); if (var.getIva() != null) { n.setCellValue(var.getIva().toString()); } else { n.setCellValue(""); } Cell nn = fila1.createCell((int) 14); nn.setCellValue(var.getEstadoFiscal()); Cell o = fila1.createCell((int) 15); o.setCellValue(var.getEstado()); Cell p = fila1.createCell((int) 16); if (var.getFechaVencimiento() != null) { p.setCellValue(var.getFechaCancelacion().toString()); } else { p.setCellValue(""); } Cell q = fila1.createCell((int) 17); if (var.getFechaVencimiento() != null) { q.setCellValue(var.getFechaModificacion().toString()); } else { q.setCellValue(""); } q.setCellValue(var.getFechaModificacion().toString()); Cell r = fila1.createCell((int) 18); r.setCellValue(var.getEstadoImpresion()); Cell s = fila1.createCell((int) 19); s.setCellValue(var.getPedimento()); Cell t = fila1.createCell((int) 20); t.setCellValue(var.getFechaPedimento()); Cell u = fila1.createCell((int) 21); u.setCellValue(var.getAduana()); Cell v = fila1.createCell((int) 22); if (var.getGenerationDate() != null) { v.setCellValue(var.getGenerationDate().toString()); } else { v.setCellValue(""); } Cell w = fila1.createCell((int) 23); w.setCellValue(var.getEstadoContable()); Cell x = fila1.createCell((int) 24); if (var.getFechaVencimiento() != null) { x.setCellValue(var.getFechaVencimiento().toString()); } else { x.setCellValue(""); } Cell y = fila1.createCell((int) 25); y.setCellValue(var.getRfcEmisor()); Cell z = fila1.createCell((int) 26); z.setCellValue(var.getRfcReceptor()); Cell a1 = fila1.createCell((int) 27); a1.setCellValue(var.getCadenaOriginal()); if (aux == 50000) { aux = 1; hoja = libro.createSheet(); logger.debug("se ha creado una nueva hoja"); } else { aux++; } } logger.info(usuario.getUserid() + ". Termina escritura de las filas"); byte[] bytes = new byte[1024]; int read = 0; libro.write(archivo); logger.info(usuario.getUserid() + ". Termina de escribir Reporte de excel"); archivo.close(); logger.info(usuario.getUserid() + ". Reporte Generado en la ruta " + ruta + strNombreReporte); File ficheroXLS = new File(ruta + strNombreReporte); FileInputStream fis = new FileInputStream(ficheroXLS); FacesContext ctx = FacesContext.getCurrentInstance(); HttpServletResponse response = (HttpServletResponse) ctx.getExternalContext().getResponse(); response.setContentType("application/vnd.ms-excel"); String disposition = "attachment; fileName=" + strNombreReporte; response.setHeader("Content-Disposition", disposition); ServletOutputStream out = response.getOutputStream(); while ((read = fis.read(bytes)) != -1) { out.write(bytes, 0, read); } out.flush(); out.close(); ctx.responseComplete(); reporte = true; logger.info(usuario.getUserid() + ". Termina Generar Reporte Excel"); new ManejoArchivos().eliminar(ruta); } catch (IOException e) { descBitacora = "[CFDI_EMITIDO - generarReporte] " + usuario.getUserid() + " IOException ERROR " + e.getMessage(); registrarBitacora(usuario.getIdUsuario(), null, empresa.getIdEmpresa(), descBitacora, BitacoraTipo.ERROR.name()); logger.error(descBitacora); } catch (Exception ex) { descBitacora = "[CFDI_EMITIDO - generarReporte] " + usuario.getUserid() + " ERROR " + ex.getMessage(); registrarBitacora(usuario.getIdUsuario(), null, empresa.getIdEmpresa(), descBitacora, BitacoraTipo.ERROR.name()); logger.error(descBitacora); } return reporte; }
From source file:com.iqtb.validacion.managedbean.MbCfdisRecibidos.java
private boolean generarReporte(List<CfdisRecibidos> lista) { logger.info("[CFDI_RECIBIDO - generarReporte] " + usuario.getUserid() + " Inicia Generar Reporte Excel"); boolean reporte = false; try {/*from w ww .j a v a 2 s . c o m*/ String ruta = "/work/iqtb/validacionfiles/reportes/" + empresa.getRfc() + "/" + usuario.getUserid() + "/"; DateFormat df = new SimpleDateFormat("ddMMyyyy"); Date today = new Date(); String reportDate = df.format(today); String strNombreReporte = "Reporte_" + reportDate + ".xls"; File dir = new File(ruta); if (!dir.exists()) { dir.mkdirs(); } Workbook libro = new HSSFWorkbook(); FileOutputStream archivo = new FileOutputStream(ruta + strNombreReporte); logger.info("[CFDI_RECIBIDO - generarReporte] " + usuario.getUserid() + " Archivo creado " + ruta + strNombreReporte); Sheet hoja = libro.createSheet(); int y = 1; for (CfdisRecibidos var : lista) { if (y == 1) { logger.info("Inicia escritura de cabecera"); Row fila = hoja.createRow((int) 0); Cell a = fila.createCell((int) 0); a.setCellValue("Fecha Emisin"); Cell b = fila.createCell((int) 1); b.setCellValue("Serie"); Cell c = fila.createCell((int) 2); c.setCellValue("Folio"); Cell d = fila.createCell((int) 3); d.setCellValue("Fecha Recepcin"); Cell e = fila.createCell((int) 4); e.setCellValue("RFC Socio Comercial"); Cell f = fila.createCell((int) 5); f.setCellValue("Socio Comercial"); Cell g = fila.createCell((int) 6); g.setCellValue("UUID"); Cell h = fila.createCell((int) 7); h.setCellValue("Total"); Cell i = fila.createCell((int) 8); i.setCellValue("Estado"); Cell j = fila.createCell((int) 9); j.setCellValue("Warning"); Cell k = fila.createCell((int) 10); k.setCellValue("Error"); Cell l = fila.createCell((int) 11); l.setCellValue("Nmero Orden de Compra"); Cell m = fila.createCell((int) 12); m.setCellValue("Total Orden de Compra"); Cell n = fila.createCell((int) 13); n.setCellValue("Tipo de Moneda"); Cell o = fila.createCell((int) 14); o.setCellValue("Entrada Almacn"); Cell p = fila.createCell((int) 15); p.setCellValue("Control de Calidad"); Cell q = fila.createCell((int) 16); q.setCellValue("Autorizacin"); logger.info("Termina escritura de cabecera"); } Row fila1 = hoja.createRow((int) y); Cell a = fila1.createCell((int) 0); a.setCellValue(var.getFecha().toString()); Cell b = fila1.createCell((int) 1); b.setCellValue(var.getSerie()); Cell c = fila1.createCell((int) 2); if (var.getFolio() == null) { c.setCellValue(""); } else { c.setCellValue(var.getFolio()); } Cell d = fila1.createCell((int) 3); d.setCellValue(var.getFechaRecepcion().toString()); Cell e = fila1.createCell((int) 4); e.setCellValue(daoSociosComerciales.getRFCSociobyIdSocio(var.getIdSocioComercial())); Cell f = fila1.createCell((int) 5); f.setCellValue(daoSociosComerciales.getNombreSociobyIdSocio(var.getIdSocioComercial())); Cell g = fila1.createCell((int) 6); g.setCellValue(var.getUuid()); Cell h = fila1.createCell((int) 7); h.setCellValue(var.getTotal().toString()); Cell i = fila1.createCell((int) 8); i.setCellValue(var.getEstado()); Cell j = fila1.createCell((int) 9); j.setCellValue(var.getTiposWarn()); Cell k = fila1.createCell((int) 10); k.setCellValue(var.getError()); Cell l = fila1.createCell((int) 11); if (var.getIdOrdenCompra() != null) { l.setCellValue(daoOrdenCompra.numOrdenCompraById(var.getIdOrdenCompra())); } else { l.setCellValue(""); } Cell m = fila1.createCell((int) 12); if (var.getTotalOc() == null) { m.setCellValue(""); } else { m.setCellValue(var.getTotalOc().toString()); } Cell n = fila1.createCell((int) 13); n.setCellValue(var.getTipoMoneda()); Cell o = fila1.createCell((int) 14); o.setCellValue(var.getEntradaAlmacen()); Cell p = fila1.createCell((int) 15); p.setCellValue(var.getControlCalidad()); Cell q = fila1.createCell((int) 16); q.setCellValue(var.getAutorizacion()); if (y == 50000) { y = 1; hoja = libro.createSheet(); logger.info("se ha creado una nueva hoja"); } else { y++; } } logger.info(usuario.getUserid() + ". Termina escritura de las filas"); byte[] bytes = new byte[1024]; int read = 0; libro.write(archivo); logger.info(usuario.getUserid() + ". Termina de escribir Reporte de excel"); archivo.close(); logger.info(usuario.getUserid() + ". Reporte Generado en la ruta " + ruta + strNombreReporte); File ficheroXLS = new File(ruta + strNombreReporte); FileInputStream fis = new FileInputStream(ficheroXLS); FacesContext ctx = FacesContext.getCurrentInstance(); HttpServletResponse response = (HttpServletResponse) ctx.getExternalContext().getResponse(); response.setContentType("application/vnd.ms-excel"); String disposition = "attachment; fileName=" + strNombreReporte; response.setHeader("Content-Disposition", disposition); ServletOutputStream out = response.getOutputStream(); while ((read = fis.read(bytes)) != -1) { out.write(bytes, 0, read); } out.flush(); out.close(); ctx.responseComplete(); reporte = true; descBitacora = "[CFDI_RECIBIDO - generarReporte] " + usuario.getUserid() + " Genero/Descargo Reporte Excel."; registrarBitacora(usuario.getIdUsuario(), null, empresa.getIdEmpresa(), descBitacora, BitacoraTipo.INFO.name()); logger.info(descBitacora); manejorArchivos.eliminar(ruta); } catch (IOException e) { descBitacora = "[CFDI_RECIBIDO - generarReporte] " + usuario.getUserid() + " IOException ERROR " + e.getMessage(); registrarBitacora(usuario.getIdUsuario(), null, empresa.getIdEmpresa(), descBitacora, BitacoraTipo.ERROR.name()); logger.error(descBitacora); } catch (Exception ex) { descBitacora = "[CFDI_RECIBIDO - generarReporte] " + usuario.getUserid() + " ERROR " + ex.getMessage(); registrarBitacora(usuario.getIdUsuario(), null, empresa.getIdEmpresa(), descBitacora, BitacoraTipo.ERROR.name()); logger.error(descBitacora); } return reporte; }
From source file:com.jeans.iservlet.action.asset.AssetExportAction.java
private void appendRow(Sheet sheet, Asset asset, int rowNumber) { // //w w w. j a va 2 s . c o m DataFormat df = sheet.getWorkbook().createDataFormat(); // ?10? Font font = sheet.getWorkbook().createFont(); font.setFontName(""); font.setFontHeightInPoints((short) 10); // ?1??????? CellStyle cellStyleString = sheet.getWorkbook().createCellStyle(); cellStyleString.setAlignment(CellStyle.ALIGN_CENTER); cellStyleString.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyleString.setFont(font); cellStyleString.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); cellStyleString.setWrapText(false); // ?2????(yyyyMM)??? CellStyle cellStyleDate = sheet.getWorkbook().createCellStyle(); cellStyleDate.setAlignment(CellStyle.ALIGN_CENTER); cellStyleDate.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyleDate.setFont(font); cellStyleDate.setDataFormat(df.getFormat("yyyyMM")); cellStyleDate.setWrapText(false); // ?3??????(#)??? CellStyle cellStyleQuantity = sheet.getWorkbook().createCellStyle(); cellStyleQuantity.setAlignment(CellStyle.ALIGN_RIGHT); cellStyleQuantity.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyleQuantity.setFont(font); cellStyleQuantity.setDataFormat(df.getFormat("#")); cellStyleQuantity.setWrapText(false); // ?4?????(#,##0.00_ )??? CellStyle cellStyleCost = sheet.getWorkbook().createCellStyle(); cellStyleCost.setAlignment(CellStyle.ALIGN_RIGHT); cellStyleCost.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyleCost.setFont(font); cellStyleCost.setDataFormat(df.getFormat("#,##0.00_ ")); cellStyleCost.setWrapText(false); // 20 Row row = sheet.createRow(rowNumber); row.setHeightInPoints(20); Cell cell = null; if (asset instanceof Hardware) { cell = row.createCell(0, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(((Hardware) asset).getCode()); cell = row.createCell(1, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(((Hardware) asset).getFinancialCode()); cell = row.createCell(2, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); Company company = asset.getCompany(); if (null != company) { cell.setCellValue(company.getAlias()); } cell = row.createCell(3, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(AssetConstants.getAssetCatalogName(asset.getCatalog())); cell = row.createCell(4, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getName()); cell = row.createCell(5, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getVendor()); cell = row.createCell(6, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getModelOrVersion()); cell = row.createCell(7, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getAssetUsage()); cell = row.createCell(8, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(((Hardware) asset).getSn()); cell = row.createCell(9, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(((Hardware) asset).getConfiguration()); cell = row.createCell(10, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleDate); Date pt = asset.getPurchaseTime(); if (null != pt) { cell.setCellValue(pt); } cell = row.createCell(11, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleQuantity); cell.setCellValue(asset.getQuantity()); cell = row.createCell(12, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleCost); cell.setCellValue(asset.getCost().doubleValue()); cell = row.createCell(13, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(AssetConstants.getAssetStateName(asset.getState())); cell = row.createCell(14, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(AssetConstants.getHardwareWarrantyName(((Hardware) asset).getWarranty())); cell = row.createCell(15, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(((Hardware) asset).getLocation()); cell = row.createCell(16, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(((Hardware) asset).getIp()); cell = row.createCell(17, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(AssetConstants.getHardwareImportanceName(((Hardware) asset).getImportance())); cell = row.createCell(18, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); Employee owner = ((Hardware) asset).getOwner(); if (null != owner) { cell.setCellValue(owner.getName()); } cell = row.createCell(19, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getComment()); } else if (asset instanceof Software) { cell = row.createCell(0, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); Company company = asset.getCompany(); if (null != company) { cell.setCellValue(company.getAlias()); } cell = row.createCell(1, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(AssetConstants.getAssetCatalogName(asset.getCatalog())); cell = row.createCell(2, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getName()); cell = row.createCell(3, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getVendor()); cell = row.createCell(4, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getModelOrVersion()); cell = row.createCell(5, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getAssetUsage()); cell = row.createCell(6, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleDate); Date pt = asset.getPurchaseTime(); if (null != pt) { cell.setCellValue(pt); } cell = row.createCell(7, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleQuantity); cell.setCellValue(asset.getQuantity()); cell = row.createCell(8, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleCost); cell.setCellValue(asset.getCost().doubleValue()); cell = row.createCell(9, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(AssetConstants.getAssetStateName(asset.getState())); cell = row.createCell(10, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(AssetConstants.getSoftwareTypeName(((Software) asset).getSoftwareType())); cell = row.createCell(11, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(((Software) asset).getLicense()); cell = row.createCell(12, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleDate); Date et = ((Software) asset).getExpiredTime(); if (null != et) { cell.setCellValue(et); } cell = row.createCell(13, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getComment()); } }
From source file:com.jeans.iservlet.action.asset.AssetExportAction.java
private void generateSheetHeader(Sheet sheet, boolean hardware) { // /*from w w w .j a va 2 s . c om*/ // ?10?? Font font = sheet.getWorkbook().createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setFontName(""); font.setFontHeightInPoints((short) 10); // ????????? CellStyle cellStyle = sheet.getWorkbook().createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyle.setFont(font); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); cellStyle.setWrapText(false); // 20 Row row = sheet.createRow(0); row.setHeightInPoints(20); Cell cell = null; if (hardware) { for (int i = 0; i < 20; i++) { cell = row.createCell(i, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyle); cell.setCellValue(HARDWARE_HEADERS[i]); sheet.setColumnWidth(i, HARDWARE_HEADERS_WIDTH[i] * 256); } } else { for (int i = 0; i < 14; i++) { cell = row.createCell(i, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyle); cell.setCellValue(SOFTWARE_HEADERS[i]); sheet.setColumnWidth(i, SOFTWARE_HEADERS_WIDTH[i] * 256); } } }
From source file:com.jeans.iservlet.controller.impl.ExportController.java
private void appendRow(Sheet sheet, Asset asset, int rowNumber, CellStyle cellStyleString, CellStyle cellStyleDate, CellStyle cellStyleQuantity, CellStyle cellStyleCost) { // /*from www. j a v a 2 s . c o m*/ // 20 Row row = sheet.createRow(rowNumber); row.setHeightInPoints(20); Cell cell = null; if (asset instanceof Hardware) { cell = row.createCell(0, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(((Hardware) asset).getCode()); cell = row.createCell(1, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(((Hardware) asset).getFinancialCode()); cell = row.createCell(2, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); Company company = asset.getCompany(); if (null != company) { cell.setCellValue(company.getAlias()); } cell = row.createCell(3, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(AssetConstants.getAssetCatalogName(asset.getCatalog())); cell = row.createCell(4, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getName()); cell = row.createCell(5, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getVendor()); cell = row.createCell(6, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getModelOrVersion()); cell = row.createCell(7, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getAssetUsage()); cell = row.createCell(8, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(((Hardware) asset).getSn()); cell = row.createCell(9, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(((Hardware) asset).getConfiguration()); cell = row.createCell(10, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleDate); Date pt = asset.getPurchaseTime(); if (null != pt) { cell.setCellValue(pt); } cell = row.createCell(11, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleQuantity); cell.setCellValue(asset.getQuantity()); cell = row.createCell(12, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleCost); cell.setCellValue(asset.getCost().doubleValue()); cell = row.createCell(13, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(AssetConstants.getAssetStateName(asset.getState())); cell = row.createCell(14, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(AssetConstants.getHardwareWarrantyName(((Hardware) asset).getWarranty())); cell = row.createCell(15, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(((Hardware) asset).getLocation()); cell = row.createCell(16, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(((Hardware) asset).getIp()); cell = row.createCell(17, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(AssetConstants.getHardwareImportanceName(((Hardware) asset).getImportance())); cell = row.createCell(18, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); Employee owner = ((Hardware) asset).getOwner(); if (null != owner) { cell.setCellValue(owner.getName()); } cell = row.createCell(19, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getComment()); } else if (asset instanceof Software) { cell = row.createCell(0, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); Company company = asset.getCompany(); if (null != company) { cell.setCellValue(company.getAlias()); } cell = row.createCell(1, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(AssetConstants.getAssetCatalogName(asset.getCatalog())); cell = row.createCell(2, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getName()); cell = row.createCell(3, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getVendor()); cell = row.createCell(4, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getModelOrVersion()); cell = row.createCell(5, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getAssetUsage()); cell = row.createCell(6, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleDate); Date pt = asset.getPurchaseTime(); if (null != pt) { cell.setCellValue(pt); } cell = row.createCell(7, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleQuantity); cell.setCellValue(asset.getQuantity()); cell = row.createCell(8, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleCost); cell.setCellValue(asset.getCost().doubleValue()); cell = row.createCell(9, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(AssetConstants.getAssetStateName(asset.getState())); cell = row.createCell(10, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(AssetConstants.getSoftwareTypeName(((Software) asset).getSoftwareType())); cell = row.createCell(11, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(((Software) asset).getLicense()); cell = row.createCell(12, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleDate); Date et = ((Software) asset).getExpiredTime(); if (null != et) { cell.setCellValue(et); } cell = row.createCell(13, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getComment()); } }
From source file:com.jeans.iservlet.controller.impl.ExportController.java
/** * ?????//from w ww. j av a2 s . c om * * @param storedOnly * ??? * @return * @throws IOException */ @RequestMapping(method = RequestMethod.POST, value = "/accessories") public ResponseEntity<byte[]> exportAccessories(@RequestParam boolean storedOnly) throws IOException { StringBuilder fn = new StringBuilder(getCurrentCompany().getName()); Date n = new Date(); String today = (new SimpleDateFormat("yyyyMMdd")).format(n); String now = (new SimpleDateFormat("yyyy-MM-dd HHmmss")).format(n); Workbook wb = new XSSFWorkbook(); fn.append(" - ???(").append(today).append(").xlsx"); Sheet sheet = wb.createSheet(now); // // ?10?? Font font = wb.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setFontName(""); font.setFontHeightInPoints((short) 10); // ????????? CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyle.setFont(font); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); cellStyle.setWrapText(false); // 20 Row row = sheet.createRow(0); row.setHeightInPoints(20); Cell cell = null; for (int i = 0; i < 7; i++) { cell = row.createCell(i, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyle); cell.setCellValue(ACS_HEADERS[i]); sheet.setColumnWidth(i, ACS_HEADERS_WIDTH[i] * 256); } List<Accessory> acs = acsService.listAccessories(getCurrentCompany(), storedOnly); Collections.sort(acs, new Comparator<Accessory>() { @Override public int compare(Accessory o1, Accessory o2) { int ret = o1.getType().compareTo(o2.getType()); if (ret == 0) { ret = Collator.getInstance(java.util.Locale.CHINA).compare(o1.getName(), o2.getName()); if (ret == 0) { ret = Collator.getInstance(java.util.Locale.CHINA).compare(o1.getBrand(), o2.getBrand()); if (ret == 0) { ret = Collator.getInstance(java.util.Locale.CHINA).compare(o1.getModel(), o2.getModel()); } } } return ret; } }); // DataFormat df = wb.createDataFormat(); // ?10? Font dFont = wb.createFont(); dFont.setFontName(""); dFont.setFontHeightInPoints((short) 10); // ?1??????? CellStyle cellStyleString = wb.createCellStyle(); cellStyleString.setAlignment(CellStyle.ALIGN_CENTER); cellStyleString.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyleString.setFont(dFont); cellStyleString.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); cellStyleString.setWrapText(false); // ?2??????(#)??? CellStyle cellStyleQuantity = sheet.getWorkbook().createCellStyle(); cellStyleQuantity.setAlignment(CellStyle.ALIGN_RIGHT); cellStyleQuantity.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyleQuantity.setFont(dFont); cellStyleQuantity.setDataFormat(df.getFormat("#")); cellStyleQuantity.setWrapText(false); int rowNumber = 1; for (Accessory ac : acs) { // 20 Row dRow = sheet.createRow(rowNumber); dRow.setHeightInPoints(20); Cell dCell = null; dCell = dRow.createCell(0, Cell.CELL_TYPE_STRING); dCell.setCellStyle(cellStyleString); dCell.setCellValue(ac.getType().getTitle()); dCell = dRow.createCell(1, Cell.CELL_TYPE_STRING); dCell.setCellStyle(cellStyleString); dCell.setCellValue(ac.getName()); dCell = dRow.createCell(2, Cell.CELL_TYPE_STRING); dCell.setCellStyle(cellStyleString); dCell.setCellValue(ac.getBrand()); dCell = dRow.createCell(3, Cell.CELL_TYPE_STRING); dCell.setCellStyle(cellStyleString); dCell.setCellValue(ac.getModel()); dCell = dRow.createCell(4, Cell.CELL_TYPE_NUMERIC); dCell.setCellStyle(cellStyleQuantity); dCell.setCellValue(null == ac.getStorage() ? 0 : ac.getStorage().getQuantity()); dCell = dRow.createCell(5, Cell.CELL_TYPE_STRING); dCell.setCellStyle(cellStyleString); dCell.setCellValue(ac.getUnit()); dCell = dRow.createCell(6, Cell.CELL_TYPE_STRING); dCell.setCellStyle(cellStyleString); dCell.setCellValue(ac.getDescription()); rowNumber++; } String filename = null; if (isIE()) { filename = URLEncoder.encode(fn.toString(), "UTF-8").replaceAll("\\+", "%20"); } else { filename = new String(fn.toString().getBytes("UTF-8"), "iso8859-1"); } response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setHeader("Content-disposition", "attachment; filename=" + filename); BufferedOutputStream out = new BufferedOutputStream(response.getOutputStream(), 4096); wb.write(out); wb.close(); out.close(); return null; }