List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook createFont
@Override
public HSSFFont createFont()
From source file:send.sms.az.util.SqlExcel.java
License:Open Source License
/** * This method generates an excel sheet containing data from the given Map. * The name of the excel sheet will be the String passed as a parameter. *///from w w w . j ava 2 s .c om public void generateExcel(Map<String, LinkedHashMap<String, String>> resultMap, String name) { FileOutputStream fileOut = null; try { HSSFWorkbook wb = new HSSFWorkbook(); HSSFCellStyle headerStyle = wb.createCellStyle(); HSSFSheet sheet3 = wb.createSheet(name); HSSFFont headerFont = wb.createFont(); // headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headerStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index); headerStyle.setFillBackgroundColor(HSSFColor.WHITE.index); headerStyle.setFont(headerFont); try { fileOut = new FileOutputStream("C:\\" + name + ".xls"); } catch (FileNotFoundException e) { e.printStackTrace(); } HSSFRow sessionname = sheet3.createRow(2); HSSFCell title = sessionname.createCell(3); title.setCellStyle(headerStyle); title.setCellValue(name); HSSFRow row = sheet3.createRow(5); Map<String, LinkedHashMap<String, String>> rMap = resultMap; Map<String, String> columnDetails = rMap.get("1"); Set<String> s = columnDetails.keySet(); int cellNo = 0; for (String s1 : s) { HSSFCell cell0 = row.createCell(cellNo); cell0.setCellStyle(headerStyle); cell0.setCellValue(s1); cellNo++; } for (int i = 1; i <= rMap.size(); i++) { columnDetails = rMap.get(new Integer(i).toString()); System.out.println(i); HSSFRow nextrow = sheet3.createRow(5 + i); Set<String> set = columnDetails.keySet(); int cellNum = 0; for (String s2 : set) { nextrow.createCell(cellNum).setCellValue(columnDetails.get(s2)); cellNum++; } } sheet3.autoSizeColumn(0); sheet3.autoSizeColumn(1); sheet3.autoSizeColumn(2); sheet3.autoSizeColumn(3); wb.write(fileOut); fileOut.flush(); fileOut.close(); } catch (FileNotFoundException fe) { fe.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } finally { try { fileOut.flush(); fileOut.close(); } catch (IOException e) { e.printStackTrace(); } } }
From source file:servlets.ReportesCSV.java
@Override public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { Connection con = null;//from www . jav a2s . c o m PreparedStatement ps = null; ResultSet rs = null; String fechaInicial = request.getParameter("fechaInicial"); String fechaFinal = request.getParameter("fechaFinal"); String url = getServletContext().getRealPath("/"); String identificacion = request.getParameter("identificacion"); String producto = request.getParameter("producto"); String cotizacion = request.getParameter("cotizacion"); int sede = Integer.parseInt(request.getParameter("sede")); String tipo = request.getParameter("tipo"); String query = null; try { con = DBConnector.getInstance().getConnection(); switch (tipo) { case "rotacion": query = "SELECT p.codigoInterno,p.nomProducto,sum(cantidad),mim.nombreMovimiento,p.costo " + "FROM kalamarypos.inv_movimiento m " + "inner join inv_movimiento_detalle md on md.inv_movimiento_numDoc = m.numDoc " + "inner join cfg_mov_inventario_detalle mi on mi.idMovInventarioDetalle = m.cfg_mov_inventario_detalle_idMovInventarioDetalle " + "inner join cfg_mov_inventario_maestro mim on mim.idMovInventarioMaestro = mi.cfg_mov_inventario_maestro_idMovInventarioMaestro " + "inner join cfg_producto p on p.idProducto = md.cfg_producto_IdProducto " + "where fecha between ? and ADDDATE(?, INTERVAL 13 DAY) " + "and cfg_empresasede_idSede = ? " + "group by p.codProducto,p.nomProducto,mim.nombreMovimiento " + "order by 3 desc"; break; case "vc": query = "SELECT f.fecCrea,d.prefijoDoc, fc.fac_documentosmaster_numDocumento,ifnull(di.valorImpuesto,0) iva,f.subtotal-f.descuento as subtotal,f.total, " + "concat(ti.abreviatura,' ',c.numDoc,' ',c.nom1Cliente,' ',c.nom2Cliente,' ',c.apellido1,' ',c.apellido2) as nombre " + "FROM kalamarypos.fac_cartera_cliente fc " + "inner join fac_documentosmaster f on fc.fac_documentosmaster_numDocumento = f.numDocumento " + "inner join cfg_cliente c on c.idCliente = fc.cfg_cliente_idCliente " + "inner join cfg_documento d on d.cfg_empresasede_idSede = f.cfg_empresasede_idSede " + "inner join cfg_tipoidentificacion ti on ti.id = c.cfg_tipoidentificacion_id " + "left join fac_documentoimpuesto di on di.fac_documentosmaster_numDocumento = fc.fac_documentosmaster_numDocumento and di.porcentajeImpuesto=16 and di.fac_documentosmaster_cfg_documento_idDoc=f.cfg_documento_idDoc " + "where f.estado!='CANCELADA' and f.estado!='ANULADA' " + "and f.cfg_empresasede_idSede=? " + "and d.codDocumento !=6 " + "and f.cfg_documento_idDoc =d.idDoc "; if (!identificacion.equals("0")) { query = query + " and c.numDoc = ? "; } query = query + " order by 1 desc"; break; case "productosVendidos": query = "SELECT c.nombreCategoria, " + " r.nombreReferencia, " + " m.nombreMarca, " + " p.codigoInterno, " + " p.nomProducto, " + " sum(fd.cantidad) as total, " + " max(f.fecCrea) as maxFeCrea " + "FROM kalamarypos.fac_cartera_cliente fc " + "inner join fac_documentosmaster f on fc.fac_documentosmaster_numDocumento = f.numDocumento " + "inner join cfg_documento d on d.cfg_empresasede_idSede = f.cfg_empresasede_idSede " + "inner join fac_documentodetalle fd on fd.fac_documentosmaster_cfg_documento_idDoc=f.cfg_documento_idDoc " + "inner join cfg_producto p on p.idProducto = fd.cfg_producto_idProducto " + "inner join cfg_categoriaproducto c on c.idCategoria = p.cfg_categoriaproducto_idCategoria " + "inner join cfg_referenciaproducto r on r.idReferencia = p.cfg_referenciaproducto_idReferencia " + "inner join cfg_marcaproducto m on m.idMarca = p.cfg_marcaproducto_idMarca " + "where f.estado!='CANCELADA' and f.estado!='ANULADA' " + "and f.cfg_empresasede_idSede=? " + "and fd.fac_documentosmaster_numDocumento=f.numDocumento " + "and f.cfg_documento_idDoc =d.idDoc " + "and f.fecCrea between ? and ADDDATE(?, INTERVAL 1 DAY) " + "and fd.fac_documentosmaster_numDocumento = f.numDocumento " + "group by fd.cfg_producto_idProducto " + "order by 6 desc "; break;//productosVendidos case "ventasVendedores": query = "SELECT f.fecCrea,d.prefijoDoc, fc.fac_documentosmaster_numDocumento,ifnull(di.valorImpuesto,0) iva,f.subtotal-f.descuento as subtotal,f.total, " + "concat('CC ',s.numDoc,' ',s.nom1Usuario,' ',s.nom2Usuario,' ',s.apellido1,' ',s.apellido2) as nombre " + "FROM kalamarypos.fac_cartera_cliente fc " + "inner join fac_documentosmaster f on fc.fac_documentosmaster_numDocumento = f.numDocumento " + "inner join cfg_documento d on d.cfg_empresasede_idSede = f.cfg_empresasede_idSede " + "inner join seg_usuario s on s.idUsuario = f.seg_usuario_idUsuario1 " + " left join fac_documentoimpuesto di on di.fac_documentosmaster_numDocumento = fc.fac_documentosmaster_numDocumento and di.porcentajeImpuesto=16 and di.fac_documentosmaster_cfg_documento_idDoc=f.cfg_documento_idDoc " + "where f.estado!='CANCELADA' and f.estado!='ANULADA' " + "and f.cfg_empresasede_idSede=? " + "and f.fecCrea between ? and ADDDATE(?, INTERVAL 13 DAY) " + "and f.cfg_documento_idDoc =d.idDoc " + "and d.codDocumento !=6 " + "and s.cfg_rol_idrol=3 "; if (!identificacion.equals("0")) { if (!identificacion.equals("")) { query = query + "and s.numDoc = ? "; } } query = query + " order by 1 desc"; break;//Ventas vendedores case "productosA": query = "select c.nombreCategoria, " + " r.nombreReferencia, " + " m.nombreMarca, " + " p.codigoInterno, " + " p.nomProducto, " + " p.precio " + "from cfg_producto p " + "inner join cfg_empresasede s on s.cfg_empresa_idEmpresa = p.cfg_empresa_idEmpresa " + "inner join cfg_categoriaproducto c on c.idCategoria = p.cfg_categoriaproducto_idCategoria " + "inner join cfg_referenciaproducto r on r.idReferencia = p.cfg_referenciaproducto_idReferencia " + "inner join cfg_marcaproducto m on m.idMarca = p.cfg_marcaproducto_idMarca " + "where s.idSede =? "; if (producto != null) { if (!producto.equals("")) { query = query + "and p.codigoInterno= ? "; } } break;//Productos case "servicios": query = "SELECT c.nombreCategoria, " + " r.nombreReferencia, " + " m.nombreMarca, " + " p.codigoInterno, " + " p.codBarProducto , " + " p.nomProducto, " + " p.costo, " + " p.utilidad, " + " p.precio, " + " p.fecCrea " + "FROM " + " cfg_producto p " + "inner join cfg_empresasede s on s.cfg_empresa_idEmpresa = p.cfg_empresa_idEmpresa " + "inner join cfg_categoriaproducto c on c.idCategoria = p.cfg_categoriaproducto_idCategoria " + "inner join cfg_referenciaproducto r on r.idReferencia = p.cfg_referenciaproducto_idReferencia " + "inner join cfg_marcaproducto m on m.idMarca = p.cfg_marcaproducto_idMarca " + "where p.esServicio=1 " + "and s.idSede =? "; if (producto != null) { if (!producto.equals("")) { query = query + "and p.codigoInterno= ? "; } } query = query + "order by 1 desc"; break;//Servicios case "stock": query = "SELECT c.nombreCategoria, " + " r.nombreReferencia, " + " m.nombreMarca, " + " p.codigoInterno, " + " p.nomProducto, " + " co.existencia " + "FROM kalamarypos.cfg_producto p " + "inner join inv_consolidado co on co.cfg_producto_idProducto = p.idProducto " + "inner join cfg_categoriaproducto c on c.idCategoria = p.cfg_categoriaproducto_idCategoria " + "inner join cfg_referenciaproducto r on r.idReferencia = p.cfg_referenciaproducto_idReferencia " + "inner join cfg_marcaproducto m on m.idMarca = p.cfg_marcaproducto_idMarca " + "where co.cfg_empresasede_idSede=? "; if (producto != null) { if (!producto.equals("")) { query = query + "and p.codigoInterno= ? "; } } query = query + " order by co.existencia desc"; break;//stock case "productoClientes": query = "SELECT concat(ti.abreviatura,' ',c.numDoc,' ',c.nom1Cliente,' ',c.nom2Cliente,' ',c.apellido1,' ',c.apellido2) as nombre, " + "fc.cfg_cliente_idCliente,fc.fac_documentosmaster_numDocumento,sum(cantidad) as total,fd.cfg_producto_idProducto, " + "max(f.fecCrea),concat(ca.nombreCategoria,' ',r.nombreReferencia,' ',m.nombreMarca,' ',p.codigoInterno,' ',p.nomProducto) as producto " + "FROM kalamarypos.fac_cartera_cliente fc " + "inner join fac_documentosmaster f on fc.fac_documentosmaster_numDocumento = f.numDocumento " + "inner join fac_documentodetalle fd on fd.fac_documentosmaster_cfg_documento_idDoc=f.cfg_documento_idDoc " + "inner join cfg_cliente c on c.idCliente = fc.cfg_cliente_idCliente " + "inner join cfg_documento d on d.cfg_empresasede_idSede = f.cfg_empresasede_idSede " + "inner join cfg_tipoidentificacion ti on ti.id = c.cfg_tipoidentificacion_id " + "inner join cfg_producto p on p.idProducto = fd.cfg_producto_idProducto " + "inner join cfg_categoriaproducto ca on ca.idCategoria = p.cfg_categoriaproducto_idCategoria " + "inner join cfg_referenciaproducto r on r.idReferencia = p.cfg_referenciaproducto_idReferencia " + "inner join cfg_marcaproducto m on m.idMarca = p.cfg_marcaproducto_idMarca " + "where f.estado!='CANCELADA' and f.estado!='ANULADA' " + "and f.cfg_empresasede_idSede=? " + "and fd.fac_documentosmaster_numDocumento = f.numDocumento " + "and d.codDocumento =1 "; if (producto != null) { if (!producto.equals("")) { query = query + "and p.codigoInterno= ? "; } } query = query + " group by fd.cfg_producto_idProducto " + "order by 4 desc"; break;//productosClientes case "cotizacion": query = "SELECT p.codigoInterno, " + "p.nomProducto, " + "p.precio, " + "ifnull(di.valorImpuesto,0) as iva, " + "f.total, " + "concat(ti.abreviatura,' ',cl.numDoc,' ',cl.nom1Cliente,' ',cl.nom2Cliente,' ',cl.apellido1,' TELEFONO ',cl.tel1) as nombre, " + "concat('No Cotizacin:',' ',d.prefijoDoc,' ', fc.fac_documentosmaster_numDocumento) cotizacion, " + "f.fecCrea, concat('Vendedor:',s.nom1Usuario,' ',s.nom2Usuario,' ',s.apellido1,' ',s.apellido2) as vendedor " + " " + "FROM kalamarypos.fac_documentosmaster fc " + "inner join fac_documentosmaster f on fc.fac_documentosmaster_numDocumento = f.numDocumento " + "inner join fac_documentodetalle fd on fd.fac_documentosmaster_cfg_documento_idDoc=f.cfg_documento_idDoc " + "inner join cfg_cliente cl on cl.idCliente = fc.cfg_cliente_idCliente " + "inner join cfg_documento d on d.cfg_empresasede_idSede = f.cfg_empresasede_idSede " + "inner join cfg_producto p on p.idProducto = fd.cfg_producto_idProducto " + "inner join cfg_categoriaproducto c on c.idCategoria = p.cfg_categoriaproducto_idCategoria " + "inner join cfg_referenciaproducto r on r.idReferencia = p.cfg_referenciaproducto_idReferencia " + "inner join cfg_marcaproducto m on m.idMarca = p.cfg_marcaproducto_idMarca " + "inner join cfg_tipoidentificacion ti on ti.id = cl.cfg_tipoidentificacion_id " + "inner join seg_usuario s on s.idUsuario = f.seg_usuario_idUsuario1 " + "left join fac_documentoimpuesto di on di.fac_documentosmaster_numDocumento = fc.fac_documentosmaster_numDocumento and di.porcentajeImpuesto=16 and di.fac_documentosmaster_cfg_documento_idDoc=f.cfg_documento_idDoc " + "where f.estado!='CANCELADA' and f.estado!='ANULADA' " + "and f.cfg_empresasede_idSede=? "; if (identificacion != null) { if (!identificacion.equals("")) query = query + "and cl.numDoc = ? "; } if (cotizacion != null) { if (!cotizacion.equals("")) query = query + "and fc.fac_documentosmaster_numDocumento = ? "; } query = query + " and d.codDocumento =6 " + "and fd.fac_documentosmaster_numDocumento=f.numDocumento " + "and f.cfg_documento_idDoc = d.idDoc " + "and s.cfg_rol_idrol=3"; break;//cotizacion case "vencimientosFacturas": query = "SELECT cc.numDoc, " + " concat(cc.nom1Cliente,' ',cc.nom2Cliente,' ',cc.apellido1,' ',cc.apellido2) cliente, " + " cc.tel1, " + " fc.valor, " + " fc.saldo, " + " fc.fecha_limite, " + " concat(d.prefijoDoc,' ',fd.numDocumento) documento " + "FROM kalamarypos.fac_cartera_cliente fc " + "inner join fac_documentosmaster fd on fc.fac_documentosmaster_numDocumento = fd.numDocumento " + "inner join cfg_cliente cc on cc.idCliente = fc.cfg_cliente_idCliente " + "inner join cfg_documento d on d.cfg_empresasede_idSede = fd.cfg_empresasede_idSede " + " where fc.estado='PENDIENTE' " + "and fecha_limite<=ADDDATE(now(), INTERVAL 15 DAY) " + "and fd.cfg_documento_idDoc = fc.fac_documentosmaster_cfg_documento_idDoc " + "and d.codDocumento =1 " + "and fd.cfg_documento_idDoc =d.idDoc " + "and fd.cfg_empresasede_idSede=? " + "and fd.cfg_documento_idDoc = fc.fac_documentosmaster_cfg_documento_idDoc " + "order by fecha_limite"; break;//vencimiento factruas case "vencimientosSeparados": query = "SELECT cc.numDoc, " + " concat(cc.nom1Cliente,' ',cc.nom2Cliente,' ',cc.apellido1,' ',cc.apellido2) cliente, " + " cc.tel1, " + " fc.valor, " + " fc.saldo, " + " fc.fecha_limite, " + " concat(d.prefijoDoc,' ',fd.numDocumento) documento " + "FROM kalamarypos.fac_cartera_cliente fc " + "inner join fac_documentosmaster fd on fc.fac_documentosmaster_numDocumento = fd.numDocumento " + "inner join cfg_cliente cc on cc.idCliente = fc.cfg_cliente_idCliente " + "inner join cfg_documento d on d.cfg_empresasede_idSede = fd.cfg_empresasede_idSede " + " where fc.estado='PENDIENTE' " + "and fecha_limite<=ADDDATE(now(), INTERVAL 15 DAY) " + "and fd.cfg_documento_idDoc = fc.fac_documentosmaster_cfg_documento_idDoc " + "and d.codDocumento =7 " + "and fd.cfg_empresasede_idSede=? " + "and fd.cfg_documento_idDoc =d.idDoc " + "and fd.cfg_documento_idDoc = fc.fac_documentosmaster_cfg_documento_idDoc " + "order by fecha_limite"; break;//vencimientoSeparados case "productosSinRotacion": query = "SELECT c.nombreCategoria, " + " r.nombreReferencia, " + " ma.nombreMarca, " + " p.codigoInterno, " + " p.nomProducto, " + " max(m.fecha) fechaultimo " + " " + "FROM kalamarypos.inv_movimiento_detalle im " + "inner join inv_movimiento m on im.inv_movimiento_numDoc = m.numDoc " + "left join cfg_producto p on p.idProducto = im.cfg_producto_idProducto " + "inner join cfg_categoriaproducto c on c.idCategoria = p.cfg_categoriaproducto_idCategoria " + "inner join cfg_referenciaproducto r on r.idReferencia = p.cfg_referenciaproducto_idReferencia " + "inner join cfg_marcaproducto ma on ma.idMarca = p.cfg_marcaproducto_idMarca " + "where cfg_empresasede_idSede=? " + "group by im.cfg_producto_idProducto " + "having DATEDIFF(now(),max(m.fecha))>=90 " + "order by 6"; break;//productosSinRotacion } ps = con.prepareStatement(query); ps.clearParameters(); int indice = 1; switch (tipo) { case "rotacion": ps.setString(indice++, fechaInicial); ps.setString(indice++, fechaFinal); ps.setInt(indice++, sede); break; case "vc": ps.setInt(indice++, sede); if (!identificacion.equals("0")) { ps.setString(indice++, identificacion); } break; case "productosVendidos": ps.setInt(indice++, sede); ps.setString(indice++, fechaInicial); ps.setString(indice++, fechaFinal); break; case "ventasVendedores": ps.setInt(indice++, sede); ps.setString(indice++, fechaInicial); ps.setString(indice++, fechaFinal); if (!identificacion.equals("0")) { if (!identificacion.equals("")) ps.setString(indice++, identificacion); } break; case "productosA": ps.setInt(indice++, sede); if (producto != null) { if (!producto.equals("")) { ps.setString(indice++, producto); } } break;//Producros case "servicios": ps.setInt(indice++, sede); if (producto != null) { if (!producto.equals("")) { ps.setString(indice++, producto); } } break;//servicios case "stock": ps.setInt(indice++, sede); if (producto != null) { if (!producto.equals("")) { ps.setString(indice++, producto); } } break;//stock case "productoClientes": ps.setInt(indice++, sede); if (producto != null) { if (!producto.equals("")) { ps.setString(indice++, producto); } } break;//productoClientes case "cotizacion": ps.setInt(indice++, sede); if (identificacion != null) { if (!identificacion.equals("")) ps.setString(indice++, identificacion); } if (cotizacion != null) { if (!cotizacion.equals("")) ps.setString(indice++, cotizacion); } break; case "vencimientosFacturas": ps.setInt(indice++, sede); break; case "vencimientosSeparados": ps.setInt(indice++, sede); break; case "productosSinRotacion": ps.setInt(indice++, sede); break; } rs = ps.executeQuery(); Date fechaActual = new Date(); SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd"); SimpleDateFormat sd2 = new SimpleDateFormat("dd-MMM-yyyy"); String rutaArchivo = url + "/informes/reportes/"; String nombreArchivo = null; switch (tipo) { case "rotacion": nombreArchivo = "rotacionProducto_" + sd.format(fechaActual) + "_" + sede + ".xls"; break; case "vc": nombreArchivo = "ventasPorCliente_" + sd.format(fechaActual) + "_" + sede + ".xls"; break; case "productosVendidos": nombreArchivo = "productosVendidos" + sd.format(fechaActual) + "_" + sede + ".xls"; break; case "ventasVendedores": nombreArchivo = "ventasVendedores" + sd.format(fechaActual) + "_" + sede + ".xls"; break; case "productosA": nombreArchivo = "productos" + sd.format(fechaActual) + "_" + sede + ".xls"; break; case "servicios": nombreArchivo = "servicios" + sd.format(fechaActual) + "_" + sede + ".xls"; break; case "stock": nombreArchivo = "stock" + sd.format(fechaActual) + "_" + sede + ".xls"; break; case "productoClientes": nombreArchivo = "ventasProductosClientes" + sd.format(fechaActual) + "_" + sede + ".xls"; break; case "cotizacion": nombreArchivo = "cotizacion" + sd.format(fechaActual) + "_" + sede + ".xls"; break; case "vencimientosFacturas": nombreArchivo = "vencimientoFacturas" + sd.format(fechaActual) + "_" + sede + ".xls"; break; case "vencimientosSeparados": nombreArchivo = "vencimientoSeparados" + sd.format(fechaActual) + "_" + sede + ".xls"; break; case "productosSinRotacion": nombreArchivo = "productosSinRotacion" + sd.format(fechaActual) + "_" + sede + ".xls"; break; } rutaArchivo = rutaArchivo + nombreArchivo; File archivo1 = new File(rutaArchivo); if (archivo1.exists()) { archivo1.delete(); } archivo1.createNewFile(); HSSFWorkbook libro = new HSSFWorkbook(); HSSFCellStyle cellStyle = libro.createCellStyle(); HSSFFont font = libro.createFont(); font.setFontName(HSSFFont.FONT_ARIAL); font.setFontHeightInPoints((short) 10); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.BLACK.index); font.setCharSet(HSSFFont.ANSI_CHARSET); cellStyle.setFont(font); /*Se inicializa el flujo de datos con el archivo xls*/ FileOutputStream archi = new FileOutputStream(rutaArchivo); Sheet hoja; Cell celda; Row fila; int i = 0; switch (tipo) { case "rotacion": hoja = libro.createSheet("Rotacin de Producto"); fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue("CODIGO PRODUCTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(1); celda.setCellValue("PRODUCTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(2); celda.setCellValue("CANTIDAD MOVIMIENTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(3); celda.setCellValue("TIPO MOVIMIENTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(4); celda.setCellValue("COSTO UNITARIO"); celda.setCellStyle(cellStyle); celda = fila.createCell(5); celda.setCellValue("COSTO TOTAL"); celda.setCellStyle(cellStyle); i = i + 1; for (int j = 0; j <= 12; j++) { hoja.autoSizeColumn(j); } while (rs.next()) { fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue(rs.getString(1)); celda = fila.createCell(1); celda.setCellValue(rs.getString(2)); celda = fila.createCell(2); celda.setCellValue(rs.getInt(3)); celda = fila.createCell(3); celda.setCellValue(rs.getString(4)); celda = fila.createCell(4); celda.setCellValue(rs.getDouble(5)); celda = fila.createCell(5); celda.setCellValue((rs.getDouble(5) * rs.getInt(3))); ////celda.getStringCellValue().getBytes(Charset.forName("UTF-8")); i = i + 1; } break; //En rotacin case "vc": hoja = libro.createSheet("Ventas por clientes"); fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue("FECHA"); celda.setCellStyle(cellStyle); celda = fila.createCell(1); celda.setCellValue("DOCUMENTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(2); celda.setCellValue("CLIENTE"); celda.setCellStyle(cellStyle); celda = fila.createCell(3); celda.setCellValue("SUBTOTAL"); celda.setCellStyle(cellStyle); celda = fila.createCell(4); celda.setCellValue("IVA"); celda.setCellStyle(cellStyle); celda = fila.createCell(5); celda.setCellValue("COSTO TOTAL"); celda.setCellStyle(cellStyle); i = i + 1; for (int j = 0; j <= 12; j++) { hoja.autoSizeColumn(j); } while (rs.next()) { fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue(sd2.format(rs.getDate(1))); celda = fila.createCell(1); celda.setCellValue(rs.getString(2) + " " + rs.getString(3)); celda = fila.createCell(2); celda.setCellValue(rs.getString(7)); celda = fila.createCell(3); celda.setCellValue(rs.getDouble(5)); celda = fila.createCell(4); celda.setCellValue(rs.getDouble(4)); celda = fila.createCell(5); celda.setCellValue(rs.getDouble(6)); i = i + 1; } break;//Ventas por cliente case "productosVendidos": hoja = libro.createSheet("Productos Vendidos"); fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue("CATEGORIA"); celda.setCellStyle(cellStyle); celda = fila.createCell(1); celda.setCellValue("REFERENCIA"); celda.setCellStyle(cellStyle); celda = fila.createCell(2); celda.setCellValue("MARCA"); celda.setCellStyle(cellStyle); celda = fila.createCell(3); celda.setCellValue("CODIGO INTERNO"); celda.setCellStyle(cellStyle); celda = fila.createCell(4); celda.setCellValue("PRODUCTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(5); celda.setCellValue("CANTIDAD VENDIDA"); celda.setCellStyle(cellStyle); celda = fila.createCell(6); celda.setCellValue("FECHA ULTIMA VENTA"); celda.setCellStyle(cellStyle); i = i + 1; for (int j = 0; j <= 12; j++) { hoja.autoSizeColumn(j); } while (rs.next()) { fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue(rs.getString(1)); celda = fila.createCell(1); celda.setCellValue(rs.getString(2)); celda = fila.createCell(2); celda.setCellValue(rs.getString(3)); celda = fila.createCell(3); celda.setCellValue(rs.getString(4)); celda = fila.createCell(4); celda.setCellValue(rs.getString(5)); celda = fila.createCell(5); celda.setCellValue(rs.getDouble(6)); celda = fila.createCell(6); celda.setCellValue(sd2.format(rs.getDate(7))); i = i + 1; } break;//Productos Vendidos case "ventasVendedores": hoja = libro.createSheet("Ventas Vendedores"); fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue("FECHA"); celda.setCellStyle(cellStyle); celda = fila.createCell(1); celda.setCellValue("VENDEDOR"); celda.setCellStyle(cellStyle); celda = fila.createCell(2); celda.setCellValue("DOCUMENTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(3); celda.setCellValue("SUBTOTAL"); celda.setCellStyle(cellStyle); celda = fila.createCell(4); celda.setCellValue("IVA"); celda.setCellStyle(cellStyle); celda = fila.createCell(5); celda.setCellValue("TOTAL"); celda.setCellStyle(cellStyle); i = i + 1; for (int j = 0; j <= 12; j++) { hoja.autoSizeColumn(j); } while (rs.next()) { fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue(sd2.format(rs.getDate(1))); celda = fila.createCell(1); celda.setCellValue(rs.getString("nombre")); celda = fila.createCell(2); celda.setCellValue(rs.getString(2) + " " + rs.getString(3)); celda = fila.createCell(3); celda.setCellValue(rs.getDouble("subtotal")); celda = fila.createCell(4); celda.setCellValue(rs.getDouble("IVA")); celda = fila.createCell(5); celda.setCellValue(rs.getDouble("TOTAL")); i = i + 1; } break;//ventas vendedores case "productosA": hoja = libro.createSheet("Informe Productos"); fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue("CATEGORIA"); celda.setCellStyle(cellStyle); celda = fila.createCell(1); celda.setCellValue("REFERENCIA"); celda.setCellStyle(cellStyle); celda = fila.createCell(2); celda.setCellValue("MARCA"); celda.setCellStyle(cellStyle); celda = fila.createCell(3); celda.setCellValue("CODIGO INTERNO"); celda.setCellStyle(cellStyle); celda = fila.createCell(4); celda.setCellValue("PRODUCTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(5); celda.setCellValue("VALOR"); celda.setCellStyle(cellStyle); i = i + 1; for (int j = 0; j <= 12; j++) { hoja.autoSizeColumn(j); } while (rs.next()) { fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue(rs.getString("nombreCategoria")); celda = fila.createCell(1); celda.setCellValue(rs.getString("nombreReferencia")); celda = fila.createCell(2); celda.setCellValue(rs.getString("nombreMarca")); celda = fila.createCell(3); celda.setCellValue(rs.getString("codigoInterno")); celda = fila.createCell(4); celda.setCellValue(rs.getString("nomProducto")); celda = fila.createCell(5); celda.setCellValue(rs.getDouble("PRECIO")); i = i + 1; } break;//productos case "servicios": hoja = libro.createSheet("Servicios"); fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue("CATEGORIA"); celda.setCellStyle(cellStyle); celda = fila.createCell(1); celda.setCellValue("REFERENCIA"); celda.setCellStyle(cellStyle); celda = fila.createCell(2); celda.setCellValue("MARCA"); celda.setCellStyle(cellStyle); celda = fila.createCell(3); celda.setCellValue("CODIGO INTERNO"); celda.setCellStyle(cellStyle); celda = fila.createCell(4); celda.setCellValue("CODIGO DE BARRAS"); celda.setCellStyle(cellStyle); celda = fila.createCell(5); celda.setCellValue("PRODUCTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(6); celda.setCellValue("COSTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(7); celda.setCellValue("UTILIDAD"); celda.setCellStyle(cellStyle); celda = fila.createCell(8); celda.setCellValue("PRECIO"); celda.setCellStyle(cellStyle); celda = fila.createCell(9); celda.setCellValue("FECHA CREACION"); celda.setCellStyle(cellStyle); i = i + 1; for (int j = 0; j <= 12; j++) { hoja.autoSizeColumn(j); } while (rs.next()) { fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue(rs.getString("nombreCategoria")); celda = fila.createCell(1); celda.setCellValue(rs.getString("nombreReferencia")); celda = fila.createCell(2); celda.setCellValue(rs.getString("nombreMarca")); celda = fila.createCell(3); celda.setCellValue(rs.getString("codigoInterno")); celda = fila.createCell(4); celda.setCellValue(rs.getString("codBarProducto")); celda = fila.createCell(5); celda.setCellValue(rs.getString("nomProducto")); celda = fila.createCell(6); celda.setCellValue(rs.getDouble("costo")); celda = fila.createCell(7); celda.setCellValue(rs.getDouble("utilidad")); celda = fila.createCell(8); celda.setCellValue(rs.getDouble("precio")); celda = fila.createCell(9); celda.setCellValue(sd2.format(rs.getDate("fecCrea"))); i = i + 1; } break;//Servicios case "stock": hoja = libro.createSheet("Stock"); fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue("CATEGORIA"); celda.setCellStyle(cellStyle); celda = fila.createCell(1); celda.setCellValue("REFERENCIA"); celda.setCellStyle(cellStyle); celda = fila.createCell(2); celda.setCellValue("MARCA"); celda.setCellStyle(cellStyle); celda = fila.createCell(3); celda.setCellValue("CODIGO INTERNO"); celda.setCellStyle(cellStyle); celda = fila.createCell(4); celda.setCellValue("PRODUCTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(5); celda.setCellValue("EXISTENCIA"); celda.setCellStyle(cellStyle); i = i + 1; for (int j = 0; j <= 12; j++) { hoja.autoSizeColumn(j); } while (rs.next()) { fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue(rs.getString("nombreCategoria")); celda = fila.createCell(1); celda.setCellValue(rs.getString("nombreReferencia")); celda = fila.createCell(2); celda.setCellValue(rs.getString("nombreMarca")); celda = fila.createCell(3); celda.setCellValue(rs.getString("codigoInterno")); celda = fila.createCell(4); celda.setCellValue(rs.getString("nomProducto")); celda = fila.createCell(5); celda.setCellValue(rs.getLong("existencia")); i = i + 1; } break;//stock case "productoClientes": hoja = libro.createSheet("Ventas productos Clientes"); fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue("CLIENTE"); celda.setCellStyle(cellStyle); celda = fila.createCell(1); celda.setCellValue("PRODUCTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(2); celda.setCellValue("CANTIDAD VENDIDA"); celda.setCellStyle(cellStyle); celda = fila.createCell(3); celda.setCellValue("FECHA ULTIMA VENTA"); celda.setCellStyle(cellStyle); i = i + 1; for (int j = 0; j <= 12; j++) { hoja.autoSizeColumn(j); } while (rs.next()) { fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue(rs.getString("nombre")); celda = fila.createCell(1); celda.setCellValue(rs.getString("producto")); celda = fila.createCell(2); celda.setCellValue(rs.getDouble("total")); celda = fila.createCell(3); celda.setCellValue(sd2.format(rs.getDate(6))); i = i + 1; } break;//productosClientes case "cotizacion": hoja = libro.createSheet("Ventas Cotizaciones"); fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue("CLIENTE"); celda.setCellStyle(cellStyle); celda = fila.createCell(1); celda.setCellValue("COTIZACION"); celda.setCellStyle(cellStyle); celda = fila.createCell(2); celda.setCellValue("FECHA"); celda.setCellStyle(cellStyle); celda = fila.createCell(3); celda.setCellValue("CODIGO PRODUCTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(4); celda.setCellValue("PRODUCTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(5); celda.setCellValue("PRECIO"); celda.setCellStyle(cellStyle); celda = fila.createCell(6); celda.setCellValue("IVA"); celda.setCellStyle(cellStyle); celda = fila.createCell(7); celda.setCellValue("TOTAL"); celda.setCellStyle(cellStyle); i = i + 1; for (int j = 0; j <= 12; j++) { hoja.autoSizeColumn(j); } while (rs.next()) { fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue(rs.getString("nombre")); celda = fila.createCell(1); celda.setCellValue(rs.getString("cotizacion")); celda = fila.createCell(2); celda.setCellValue(sd2.format(rs.getDate("fecCrea"))); celda = fila.createCell(3); celda.setCellValue(rs.getString("codigoInterno")); celda = fila.createCell(4); celda.setCellValue(rs.getString("nomProducto")); celda = fila.createCell(5); celda.setCellValue(rs.getDouble("precio")); celda = fila.createCell(6); celda.setCellValue(rs.getString("iva")); celda = fila.createCell(7); celda.setCellValue(rs.getString("total")); i = i + 1; } break;//cotizacion case "vencimientosFacturas": hoja = libro.createSheet("Vencimiento Facturas"); fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue("DOCUMENTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(1); celda.setCellValue("NUMERO IDENTIFICACION CLIENTE"); celda.setCellStyle(cellStyle); celda = fila.createCell(2); celda.setCellValue("CLIENTE"); celda.setCellStyle(cellStyle); celda = fila.createCell(3); celda.setCellValue("TELEFONO"); celda.setCellStyle(cellStyle); celda = fila.createCell(4); celda.setCellValue("VALOR"); celda.setCellStyle(cellStyle); celda = fila.createCell(5); celda.setCellValue("SALDO"); celda.setCellStyle(cellStyle); celda = fila.createCell(6); celda.setCellValue("FECHA VENCIMIENTO"); celda.setCellStyle(cellStyle); i = i + 1; for (int j = 0; j <= 12; j++) { hoja.autoSizeColumn(j); } while (rs.next()) { fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue(rs.getString("documento")); celda = fila.createCell(1); celda.setCellValue(rs.getString("numDoc")); celda = fila.createCell(2); celda.setCellValue(rs.getString("cliente")); celda = fila.createCell(3); celda.setCellValue(rs.getString("tel1")); celda = fila.createCell(4); celda.setCellValue(rs.getDouble("valor")); celda = fila.createCell(5); celda.setCellValue(rs.getDouble("saldo")); celda = fila.createCell(6); celda.setCellValue(sd2.format(rs.getDate("fecha_limite"))); i = i + 1; } break;//vencimientofactuas case "vencimientosSeparados": hoja = libro.createSheet("Vencimiento Separados"); fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue("DOCUMENTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(1); celda.setCellValue("NUMERO IDENTIFICACION CLIENTE"); celda.setCellStyle(cellStyle); celda = fila.createCell(2); celda.setCellValue("CLIENTE"); celda.setCellStyle(cellStyle); celda = fila.createCell(3); celda.setCellValue("TELEFONO"); celda.setCellStyle(cellStyle); celda = fila.createCell(4); celda.setCellValue("VALOR"); celda.setCellStyle(cellStyle); celda = fila.createCell(5); celda.setCellValue("SALDO"); celda.setCellStyle(cellStyle); celda = fila.createCell(6); celda.setCellValue("FECHA VENCIMIENTO"); celda.setCellStyle(cellStyle); i = i + 1; for (int j = 0; j <= 12; j++) { hoja.autoSizeColumn(j); } while (rs.next()) { fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue(rs.getString("documento")); celda = fila.createCell(1); celda.setCellValue(rs.getString("numDoc")); celda = fila.createCell(2); celda.setCellValue(rs.getString("cliente")); celda = fila.createCell(3); celda.setCellValue(rs.getString("tel1")); celda = fila.createCell(4); celda.setCellValue(rs.getDouble("valor")); celda = fila.createCell(5); celda.setCellValue(rs.getDouble("saldo")); celda = fila.createCell(6); celda.setCellValue(sd2.format(rs.getDate("fecha_limite"))); i = i + 1; } break;//vencimientosSeprados case "productosSinRotacion": hoja = libro.createSheet("productos sin rotacin"); fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue("CATEGORIA"); celda.setCellStyle(cellStyle); celda = fila.createCell(1); celda.setCellValue("REFERENCIA"); celda.setCellStyle(cellStyle); celda = fila.createCell(2); celda.setCellValue("MARCA"); celda.setCellStyle(cellStyle); celda = fila.createCell(3); celda.setCellValue("CODIGO INTERNO"); celda.setCellStyle(cellStyle); celda = fila.createCell(4); celda.setCellValue("PRODUCTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(5); celda.setCellValue("FECHA ULTIMO MOVIMIENTO"); celda.setCellStyle(cellStyle); i = i + 1; for (int j = 0; j <= 12; j++) { hoja.autoSizeColumn(j); } while (rs.next()) { fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue(rs.getString("nombreCategoria")); celda = fila.createCell(1); celda.setCellValue(rs.getString("nombreReferencia")); celda = fila.createCell(2); celda.setCellValue(rs.getString("nombreMarca")); celda = fila.createCell(3); celda.setCellValue(rs.getString("codigoInterno")); celda = fila.createCell(4); celda.setCellValue(rs.getString("nomProducto")); celda = fila.createCell(5); celda.setCellValue(sd2.format(rs.getDate("fechaultimo"))); i = i + 1; } break;//ProductosSinRotacion } /*Escribimos en el libro*/ libro.write(archi); /*Cerramos el flujo de datos*/ archi.close(); /*DESCARGAMOS EL ARCHIVO */ File f; f = new File(rutaArchivo); int bit; InputStream in; ServletOutputStream out; response.setContentType("application/vnd.ms-excel"); //Tipo de fichero. response.setHeader("Content-Disposition", "attachment;filename=\"" + nombreArchivo + "\""); //Configurar cabecera http in = new FileInputStream(f); out = response.getOutputStream(); bit = 256; while ((bit) >= 0) { bit = in.read(); out.write(bit); } out.flush(); out.close(); in.close(); } catch (Exception e) { e.printStackTrace(); } finally { try { if (rs != null) { rs.close(); } if (ps != null) { ps.close(); } if (con != null) { con.close(); } DBConnector.getInstance().closeConnection(); } catch (Exception e) { } } }
From source file:temp1.ExportExcel.java
public void export() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet hs = wb.createSheet();//from w ww .j a va 2 s. c om TableModel tm = table.getModel(); int row = tm.getRowCount(); int cloumn = tm.getColumnCount(); HSSFCellStyle style = wb.createCellStyle(); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 11); style.setFont(font); HSSFCellStyle style1 = wb.createCellStyle(); style1.setBorderBottom(HSSFCellStyle.BORDER_THIN); style1.setBorderLeft(HSSFCellStyle.BORDER_THIN); style1.setBorderRight(HSSFCellStyle.BORDER_THIN); style1.setBorderTop(HSSFCellStyle.BORDER_THIN); style1.setFillForegroundColor(HSSFColor.ORANGE.index); style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont font1 = wb.createFont(); font1.setFontHeightInPoints((short) 15); font1.setBoldweight((short) 700); style1.setFont(font); for (int i = 0; i < row + 1; i++) { HSSFRow hr = hs.createRow(i); for (int j = 0; j < cloumn; j++) { if (i == 0) { String value = tm.getColumnName(j); int len = value.length(); hs.setColumnWidth((short) j, (short) (len * 400)); HSSFRichTextString srts = new HSSFRichTextString(value); HSSFCell hc = hr.createCell((short) j); hc.setEncoding((short) 1); hc.setCellStyle(style1); hc.setCellValue(srts); } else { System.out.println("vlue " + tm.getValueAt(i - 1, j)); if (tm.getValueAt(i - 1, j) != null) { String value = tm.getValueAt(i - 1, j).toString(); HSSFRichTextString srts = new HSSFRichTextString(value); HSSFCell hc = hr.createCell((short) j); hc.setEncoding((short) 1); hc.setCellStyle(style); if (value.equals("") || value == null) { hc.setCellValue(new HSSFRichTextString("")); } else { hc.setCellValue(srts); } } } } } try { wb.write(fos); fos.close(); } catch (IOException ex) { ex.printStackTrace(); } }
From source file:tw.edu.chit.struts.action.course.ReportPrintAction.java
/** * ?//w ww . ja va 2 s .co m * * @param mapping org.apache.struts.action.ActionMapping object * @param form org.apache.struts.action.ActionForm object * @param request request javax.servlet.http.HttpServletRequest object * @param response response javax.servlet.http.HttpServletResponse object * @param sterm */ @SuppressWarnings("unchecked") private void printCalculate(ActionMapping mapping, DynaActionForm form, HttpServletRequest request, HttpServletResponse response, String sterm) throws Exception { HttpSession session = request.getSession(false); AdminManager am = (AdminManager) getBean(ADMIN_MANAGER_BEAN_NAME); MemberManager mm = (MemberManager) getBean(IConstants.MEMBER_MANAGER_BEAN_NAME); CourseManager cm = (CourseManager) getBean(IConstants.COURSE_MANAGER_BEAN_NAME); ScoreManager sm = (ScoreManager) getBean(IConstants.SCORE_MANAGER_BEAN_NAME); ServletContext context = request.getSession().getServletContext(); String thisYear = cm.getSchoolYear().toString(); String thisTerm = am.findTermBy(PARAMETER_SCHOOL_TERM); List<Clazz> clazzes = sm.findClassBy(new Clazz(processClassInfo(form)), getUserCredential(session).getClassInChargeAry(), false); int thisTermCounts = 0, lastTermCounts = 0; String departClass = null, deptCode = null, histDeptCode = null, currentDeptCode = null, chiName = null; ScoreHist scoreHist = null; List<Student> students = null; List<ScoreHist> scoreHistList = null; List<Map> seldInfo = null; List csnos = null; if (!clazzes.isEmpty()) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("?"); sheet.setColumnWidth(0, 3000); sheet.setColumnWidth(1, 3000); sheet.setColumnWidth(2, 5000); sheet.setColumnWidth(3, 5000); sheet.setColumnWidth(4, 5000); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4)); HSSFFont fontSize16 = workbook.createFont(); fontSize16.setFontHeightInPoints((short) 16); fontSize16.setFontName("Arial Unicode MS"); HSSFFont fontSize10 = workbook.createFont(); fontSize10.setFontHeightInPoints((short) 10); fontSize10.setFontName("Arial Unicode MS"); // Header Toolket.setCellValue(workbook, sheet, 0, 0, "?", fontSize16, HSSFCellStyle.ALIGN_CENTER, false, 35.0F, null); // Column Header Toolket.setCellValue(workbook, sheet, 1, 0, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 1, "??", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 2, "?", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 3, "??", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 4, "?", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); int index = 2; for (Clazz clazz : clazzes) { departClass = clazz.getClassNo(); deptCode = StringUtils.substring(departClass, 3, 4); if (Toolket.isDelayClass(departClass) || Toolket.isLiteracyClass(departClass)) continue; students = mm.findStudentsByClassNo(departClass); if (!students.isEmpty()) { if (thisYear.equals(request.getParameter("year")) && thisTerm.equals(sterm)) { // (Seld) for (Student student : students) { seldInfo = cm.findStudentSeldCourse(student.getStudentNo(), sterm); if (!seldInfo.isEmpty()) { for (Map m : seldInfo) { currentDeptCode = StringUtils.substring((String) m.get("depart_class"), 3, 4); if (!deptCode.equalsIgnoreCase(currentDeptCode) && !Toolket.isLiteracyClass((String) m.get("depart_class"))) { thisTermCounts++; Toolket.setCellValue(workbook, sheet, index, 0, student.getStudentNo(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 1, student.getStudentName(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 2, Toolket.getClassFullName(student.getDepartClass()), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 3, Toolket.getClassFullName((String) m.get("depart_class")), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index++, 4, (String) m.get("chi_name"), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); break; } } } } } else { // ?(ScoreHist) for (Student student : students) { scoreHist = new ScoreHist(student.getStudentNo()); scoreHist.setSchoolYear((short) Integer.parseInt(request.getParameter("year"))); scoreHist.setSchoolTerm(sterm); scoreHistList = sm.findScoreHistBy(scoreHist); HIST: { if (!scoreHistList.isEmpty()) { for (ScoreHist hist : scoreHistList) { if (StringUtils.isNotBlank(hist.getStdepartClass()) && !Toolket.isLiteracyClass(hist.getStdepartClass())) { histDeptCode = StringUtils.substring(hist.getStdepartClass(), 3, 4); if (!deptCode.equalsIgnoreCase(histDeptCode)) { lastTermCounts++; Toolket.setCellValue(workbook, sheet, index, 0, student.getStudentNo(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 1, student.getStudentName(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 2, Toolket.getClassFullName(student.getDepartClass()), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 3, Toolket.getClassFullName(hist.getStdepartClass()), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); csnos = cm.getCsnameBy(hist.getCscode()); if (!csnos.isEmpty()) chiName = ((Csno) csnos.get(0)).getChiName(); else chiName = ""; Toolket.setCellValue(workbook, sheet, index++, 4, chiName, fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); break HIST; } } } } } } } } } File tempDir = new File( context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno() + (new SimpleDateFormat("yyyyMMdd").format(new Date())))); if (!tempDir.exists()) tempDir.mkdirs(); File output = new File(tempDir, "Calculate.xls"); FileOutputStream fos = new FileOutputStream(output); workbook.write(fos); fos.close(); JasperReportUtils.printXlsToFrontEnd(response, output); output.delete(); tempDir.delete(); System.out.println("This Term: " + thisTermCounts); System.out.println("Last Term: " + lastTermCounts); } }
From source file:tw.edu.chit.struts.action.course.ReportPrintAction.java
/** * /*from w w w . j av a2 s .co m*/ * * @param mapping * @param form * @param request * @param response * @param sterm * @throws Exception */ @SuppressWarnings("unchecked") private void printStayTimePrint(ActionMapping mapping, DynaActionForm form, HttpServletRequest request, HttpServletResponse response, String sterm) throws Exception { HttpSession session = request.getSession(false); MemberManager mm = (MemberManager) getBean(IConstants.MEMBER_MANAGER_BEAN_NAME); CourseManager cm = (CourseManager) getBean(IConstants.COURSE_MANAGER_BEAN_NAME); ScoreManager sm = (ScoreManager) getBean(IConstants.SCORE_MANAGER_BEAN_NAME); ServletContext context = request.getSession().getServletContext(); Integer year = cm.getSchoolYear(); String term = form.getString("sterm"); List<Clazz> clazzes = sm.findClassBy(new Clazz(processClassInfo(form)), getUserCredential(session).getClassInChargeAry(), true); if (!clazzes.isEmpty()) { File templateXLS = new File(context.getRealPath("/WEB-INF/reports/TeachSchedAll.xls")); HSSFWorkbook workbook = Toolket.getHSSFWorkbook(templateXLS); HSSFFont fontSize12 = workbook.createFont(); fontSize12.setFontHeightInPoints((short) 12); fontSize12.setFontName("Arial Unicode MS"); HSSFSheet sheet = null; int sheetIndex = 0, colOffset = 1, col = 0; boolean isLocationNull = false; String departClass = null; Dtime dtime = null; Empl empl = null; Set<String> idnoSet = new HashSet<String>(); Short colorForStayTime = HSSFColor.AUTOMATIC.index; Short colorForLifeCounseling = HSSFColor.LIGHT_GREEN.index; List<TeacherStayTime> tsts = null; List<LifeCounseling> lcs = null; List<Dtime> dtimes = null; List<Map> map = null; Map content = null; for (Clazz clazz : clazzes) { departClass = clazz.getClassNo(); dtime = new Dtime(); dtime.setDepartClass(departClass); dtime.setSterm(sterm); dtimes = cm.findDtimeBy(dtime, "cscode"); if (!dtimes.isEmpty()) { for (Dtime d : dtimes) { if (StringUtils.isNotBlank(d.getTechid())) idnoSet.add(d.getTechid()); } } } for (String idno : idnoSet) { empl = mm.findEmplByIdno(idno); if (empl != null && "1".equalsIgnoreCase(empl.getCategory())) { sheet = workbook.getSheetAt(sheetIndex); workbook.setSheetName(sheetIndex++, empl.getCname()); isLocationNull = empl.getLocation() == null; Toolket.setCellValue(sheet, 0, 1, year + "" + term + "" + empl.getCname() + "?" + " (:" + (isLocationNull ? "" : StringUtils.defaultIfEmpty(empl.getLocation().getExtension(), "")) + " ?:" + (isLocationNull ? "" : StringUtils.defaultIfEmpty(empl.getLocation().getRoomId(), "")) + ")"); map = cm.findCourseByTeacherTermWeekdaySched(empl.getIdno(), term.toString()); for (int i = 0; i < 14; i++) { for (int j = 0; j < 7; j++) { content = map.get(j * 15 + i); if (!CollectionUtils.isEmpty(content)) { Toolket.setCellValue(sheet, i + 2, j + 2, (String) content.get("ClassName") + "\n" + (String) content.get("chi_name") + "\n" + (String) content.get("place")); } } } List<TeacherStayTime> myTsts = cm.ezGetBy( " Select Week, Node1, Node2, Node3, Node4, Node5, Node6, Node7, Node8, Node9, Node10, " + " Node11, Node12, Node13, Node14 " + " From TeacherStayTime " + " Where SchoolYear='" + year + "'" + " And SchoolTerm='" + term + "' " + " And parentOid='" + empl.getOid() + "'"); List myTsts2 = new ArrayList(); //int colOffset = 1, col = 0; for (int i = 0; i < myTsts.size(); i++) { //for (TeacherStayTime tst : tsts) { myTsts2.add(myTsts.get(i)); String s = myTsts2.get(i).toString(); col = Integer.parseInt(s.substring(6, 7)) + colOffset; //Week //if (tst.getNode1() != null && tst.getNode1() == 1) { if (Integer.parseInt(s.substring(15, 16)) == 1) { //Node1 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 2, col))) Toolket.setCellValue(workbook, sheet, 2, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (Integer.parseInt(s.substring(24, 25)) == 1) { //Node2 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 3, col))) Toolket.setCellValue(workbook, sheet, 3, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (Integer.parseInt(s.substring(33, 34)) == 1) { //Node3 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 4, col))) Toolket.setCellValue(workbook, sheet, 4, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (Integer.parseInt(s.substring(42, 43)) == 1) { //Node4 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 5, col))) Toolket.setCellValue(workbook, sheet, 5, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (Integer.parseInt(s.substring(51, 52)) == 1) { //Node5 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 6, col))) Toolket.setCellValue(workbook, sheet, 6, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (Integer.parseInt(s.substring(60, 61)) == 1) { //Node6 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 7, col))) Toolket.setCellValue(workbook, sheet, 7, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (Integer.parseInt(s.substring(69, 70)) == 1) { //Node7 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 8, col))) Toolket.setCellValue(workbook, sheet, 8, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (Integer.parseInt(s.substring(78, 79)) == 1) { //Node8 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 9, col))) Toolket.setCellValue(workbook, sheet, 9, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (Integer.parseInt(s.substring(87, 88)) == 1) { //Node9 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 10, col))) Toolket.setCellValue(workbook, sheet, 10, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (Integer.parseInt(s.substring(97, 98)) == 1) { //Node10 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 11, col))) Toolket.setCellValue(workbook, sheet, 11, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (Integer.parseInt(s.substring(107, 108)) == 1) { //Node11 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 12, col))) Toolket.setCellValue(workbook, sheet, 12, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (Integer.parseInt(s.substring(117, 118)) == 1) { //Node12 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 13, col))) Toolket.setCellValue(workbook, sheet, 13, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (Integer.parseInt(s.substring(127, 128)) == 1) { //Node13 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 14, col))) Toolket.setCellValue(workbook, sheet, 14, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (Integer.parseInt(s.substring(137, 138)) == 1) { //Node14 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 15, col))) Toolket.setCellValue(workbook, sheet, 15, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } } //List<LifeCounseling> lcs = empl.getLifeCounseling(); List<LifeCounseling> myLcs = cm.ezGetBy( " Select Week, Node1, Node2, Node3, Node4, Node5, Node6, Node7, Node8, Node9, Node10, " + " Node11, Node12, Node13, Node14 " + " From LifeCounseling Where ParentOid='" + empl.getOid() + "'"); List myLcs2 = new ArrayList(); colOffset = 1; col = 0; //for (LifeCounseling lc : lcs) { for (int y = 0; y < myLcs.size(); y++) { myLcs2.add(myLcs.get(y)); String st = myLcs2.get(y).toString(); col = Integer.parseInt(st.substring(6, 7)) + colOffset; //col = lc.getWeek() + colOffset; //if (lc.getNode1() != null && lc.getNode1() == 1) { if (Integer.parseInt(st.substring(15, 16)) == 1) { //Node1 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 2, col))) Toolket.setCellValue(workbook, sheet, 2, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode2() != null && lc.getNode2() == 1) { if (Integer.parseInt(st.substring(24, 25)) == 1) { //Node2 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 3, col))) Toolket.setCellValue(workbook, sheet, 3, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode3() != null && lc.getNode3() == 1) { if (Integer.parseInt(st.substring(33, 34)) == 1) { //Node3 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 4, col))) Toolket.setCellValue(workbook, sheet, 4, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode4() != null && lc.getNode4() == 1) { if (Integer.parseInt(st.substring(42, 43)) == 1) { //Node4 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 5, col))) Toolket.setCellValue(workbook, sheet, 5, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode5() != null && lc.getNode5() == 1) { if (Integer.parseInt(st.substring(51, 52)) == 1) { //Node5 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 6, col))) Toolket.setCellValue(workbook, sheet, 6, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode6() != null && lc.getNode6() == 1) { if (Integer.parseInt(st.substring(60, 61)) == 1) { //Node6 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 7, col))) Toolket.setCellValue(workbook, sheet, 7, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode7() != null && lc.getNode7() == 1) { if (Integer.parseInt(st.substring(69, 70)) == 1) { //Node7 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 8, col))) Toolket.setCellValue(workbook, sheet, 8, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode8() != null && lc.getNode8() == 1) { if (Integer.parseInt(st.substring(78, 79)) == 1) { //Node8 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 9, col))) Toolket.setCellValue(workbook, sheet, 9, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode9() != null && lc.getNode9() == 1) { if (Integer.parseInt(st.substring(87, 88)) == 1) { //Node9 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 10, col))) Toolket.setCellValue(workbook, sheet, 10, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode10() != null && lc.getNode10() == 1) { if (Integer.parseInt(st.substring(97, 98)) == 1) { //Node10 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 11, col))) Toolket.setCellValue(workbook, sheet, 11, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode11() != null && lc.getNode11() == 1) { if (Integer.parseInt(st.substring(107, 108)) == 1) { //Node11 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 12, col))) Toolket.setCellValue(workbook, sheet, 12, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode12() != null && lc.getNode12() == 1) { if (Integer.parseInt(st.substring(117, 118)) == 1) { //Node12 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 13, col))) Toolket.setCellValue(workbook, sheet, 13, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode13() != null && lc.getNode13() == 1) { if (Integer.parseInt(st.substring(127, 128)) == 1) { //Node13 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 14, col))) Toolket.setCellValue(workbook, sheet, 14, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode14() != null && lc.getNode14() == 1) { if (Integer.parseInt(st.substring(137, 138)) == 1) { //Node14 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 15, col))) Toolket.setCellValue(workbook, sheet, 15, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } } } } //===================================>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> /* tsts = empl.getStayTime(); for (TeacherStayTime tst : tsts) { col = tst.getWeek() + colOffset; if (tst.getNode1() != null && tst.getNode1() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 2, col))) Toolket.setCellValue(workbook, sheet, 2, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (tst.getNode2() != null && tst.getNode2() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 3, col))) Toolket.setCellValue(workbook, sheet, 3, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (tst.getNode3() != null && tst.getNode3() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 4, col))) Toolket.setCellValue(workbook, sheet, 4, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (tst.getNode4() != null && tst.getNode4() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 5, col))) Toolket.setCellValue(workbook, sheet, 5, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (tst.getNode5() != null && tst.getNode5() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 6, col))) Toolket.setCellValue(workbook, sheet, 6, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (tst.getNode6() != null && tst.getNode6() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 7, col))) Toolket.setCellValue(workbook, sheet, 7, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (tst.getNode7() != null && tst.getNode7() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 8, col))) Toolket.setCellValue(workbook, sheet, 8, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (tst.getNode8() != null && tst.getNode8() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 9, col))) Toolket.setCellValue(workbook, sheet, 9, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (tst.getNode9() != null && tst.getNode9() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 10, col))) Toolket.setCellValue(workbook, sheet, 10, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (tst.getNode10() != null && tst.getNode10() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 11, col))) Toolket.setCellValue(workbook, sheet, 11, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (tst.getNode11() != null && tst.getNode11() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 12, col))) Toolket.setCellValue(workbook, sheet, 12, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (tst.getNode12() != null && tst.getNode12() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 13, col))) Toolket.setCellValue(workbook, sheet, 13, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (tst.getNode13() != null && tst.getNode13() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 14, col))) Toolket.setCellValue(workbook, sheet, 14, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (tst.getNode14() != null && tst.getNode14() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 15, col))) Toolket.setCellValue(workbook, sheet, 15, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } } lcs = empl.getLifeCounseling(); colOffset = 1; col = 0; for (LifeCounseling lc : lcs) { col = lc.getWeek() + colOffset; if (lc.getNode1() != null && lc.getNode1() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 2, col))) Toolket.setCellValue(workbook, sheet, 2, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } if (lc.getNode2() != null && lc.getNode2() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 3, col))) Toolket.setCellValue(workbook, sheet, 3, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } if (lc.getNode3() != null && lc.getNode3() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 4, col))) Toolket.setCellValue(workbook, sheet, 4, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } if (lc.getNode4() != null && lc.getNode4() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 5, col))) Toolket.setCellValue(workbook, sheet, 5, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } if (lc.getNode5() != null && lc.getNode5() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 6, col))) Toolket.setCellValue(workbook, sheet, 6, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } if (lc.getNode6() != null && lc.getNode6() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 7, col))) Toolket.setCellValue(workbook, sheet, 7, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } if (lc.getNode7() != null && lc.getNode7() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 8, col))) Toolket.setCellValue(workbook, sheet, 8, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } if (lc.getNode8() != null && lc.getNode8() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 9, col))) Toolket.setCellValue(workbook, sheet, 9, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } if (lc.getNode9() != null && lc.getNode9() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 10, col))) Toolket.setCellValue(workbook, sheet, 10, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } if (lc.getNode10() != null && lc.getNode10() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 11, col))) Toolket.setCellValue(workbook, sheet, 11, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } if (lc.getNode11() != null && lc.getNode11() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 12, col))) Toolket.setCellValue(workbook, sheet, 12, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } if (lc.getNode12() != null && lc.getNode12() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 13, col))) Toolket.setCellValue(workbook, sheet, 13, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } if (lc.getNode13() != null && lc.getNode13() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 14, col))) Toolket.setCellValue(workbook, sheet, 14, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } if (lc.getNode14() != null && lc.getNode14() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 15, col))) Toolket.setCellValue(workbook, sheet, 15, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } } } */ //=================================>>>>>>>>>>>>>>>>>>>>> File tempDir = new File( context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno() + (new SimpleDateFormat("yyyyMMdd").format(new Date())))); if (!tempDir.exists()) tempDir.mkdirs(); File output = new File(tempDir, "StayTimeList.xls"); FileOutputStream fos = new FileOutputStream(output); workbook.write(fos); fos.close(); JasperReportUtils.printXlsToFrontEnd(response, output); output.delete(); tempDir.delete(); } }
From source file:tw.edu.chit.struts.action.course.ReportPrintAction.java
/** * //from ww w . j av a 2 s . co m * * @param mapping org.apache.struts.action.ActionMapping object * @param form org.apache.struts.action.ActionForm object * @param request request javax.servlet.http.HttpServletRequest object * @param response response javax.servlet.http.HttpServletResponse object * @param sterm */ private void printIdnoCheckErrorStudentsList(ActionMapping mapping, DynaActionForm form, HttpServletRequest request, HttpServletResponse response, String sterm) throws Exception { HttpSession session = request.getSession(false); MemberManager mm = (MemberManager) getBean(IConstants.MEMBER_MANAGER_BEAN_NAME); ScoreManager sm = (ScoreManager) getBean(IConstants.SCORE_MANAGER_BEAN_NAME); ServletContext context = request.getSession().getServletContext(); List<Clazz> clazzes = sm.findClassBy(new Clazz(processClassInfo(form)), getUserCredential(session).getClassInChargeAry(), false); if (!clazzes.isEmpty()) { List<Student> students = null; HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("?"); sheet.setColumnWidth(0, 3000); sheet.setColumnWidth(1, 3000); sheet.setColumnWidth(2, 5000); sheet.setColumnWidth(3, 3500); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3)); HSSFFont fontSize16 = workbook.createFont(); fontSize16.setFontHeightInPoints((short) 16); fontSize16.setFontName("Arial Unicode MS"); HSSFFont fontSize12 = workbook.createFont(); fontSize12.setFontHeightInPoints((short) 12); fontSize12.setFontName("Arial Unicode MS"); // Header Toolket.setCellValue(workbook, sheet, 0, 0, "?", fontSize16, HSSFCellStyle.ALIGN_CENTER, false, 35.0F, null); // Column Header Toolket.setCellValue(workbook, sheet, 1, 0, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 1, "??", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 2, "?", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 3, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); int index = 2; for (Clazz clazz : clazzes) { if (Toolket.isDelayClass(clazz.getClassNo())) continue; students = mm.findStudentsByClassNo(clazz.getClassNo()); if (!students.isEmpty()) { for (Student student : students) { if (!Toolket.checkIdno(student.getIdno())) { Toolket.setCellValue(workbook, sheet, index, 0, student.getStudentNo(), fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 1, student.getStudentName(), fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 2, Toolket.getClassFullName(student.getDepartClass()), null, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index++, 3, student.getIdno(), null, HSSFCellStyle.ALIGN_CENTER, true, null); } } } } File tempDir = new File( context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno() + (new SimpleDateFormat("yyyyMMdd").format(new Date())))); if (!tempDir.exists()) tempDir.mkdirs(); File output = new File(tempDir, "RegisterList.xls"); FileOutputStream fos = new FileOutputStream(output); workbook.write(fos); fos.close(); JasperReportUtils.printXlsToFrontEnd(response, output); output.delete(); tempDir.delete(); } else { Map<String, String> param = new HashMap<String, String>(); File image = new File(context.getRealPath("/pages/images/2002chitS.jpg")); param.put("IMAGE", image.getAbsolutePath()); byte[] bytes = JasperRunManager.runReportToPdf(JasperReportUtils.getNoResultReport(context), param, new JREmptyDataSource()); JasperReportUtils.printPdfToFrontEnd(response, bytes); } }
From source file:tw.edu.chit.struts.action.course.ReportPrintAction.java
/** * ?// ww w .j av a 2 s .co m * * @param mapping org.apache.struts.action.ActionMapping object * @param form org.apache.struts.action.ActionForm object * @param request request javax.servlet.http.HttpServletRequest object * @param response response javax.servlet.http.HttpServletResponse object * @param sterm */ @SuppressWarnings("unchecked") private void printSyllabusCheck(ActionMapping mapping, DynaActionForm form, HttpServletRequest request, HttpServletResponse response, String sterm) throws Exception { HttpSession session = request.getSession(false); AdminManager am = (AdminManager) getBean(IConstants.ADMIN_MANAGER_BEAN_NAME); CourseManager cm = (CourseManager) getBean(IConstants.COURSE_MANAGER_BEAN_NAME); MemberManager mm = (MemberManager) getBean(IConstants.MEMBER_MANAGER_BEAN_NAME); ScoreManager sm = (ScoreManager) getBean(IConstants.SCORE_MANAGER_BEAN_NAME); ServletContext context = request.getSession().getServletContext(); String year = am.findTermBy(IConstants.PARAMETER_SCHOOL_YEAR); // ??? List<Clazz> clazzes = sm.findClassBy(new Clazz(processClassInfo(form)), getUserCredential(session).getClassInChargeAry(), true); String hql = "SELECT COUNT(*) FROM Dtime d WHERE d.techid != '' AND d.techid IS NOT NULL " + "AND d.sterm = ? AND d.departClass LIKE ?"; List<Object> count = (List<Object>) am.find(hql, new Object[] { sterm, processClassInfo(form) + "%" }); if (!clazzes.isEmpty()) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("?"); sheet.setColumnWidth(0, 3000); sheet.setColumnWidth(1, 5500); sheet.setColumnWidth(2, 3000); sheet.setColumnWidth(3, 8000); sheet.setColumnWidth(4, 2000); sheet.setColumnWidth(5, 5000); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); HSSFFont fontSize16 = workbook.createFont(); fontSize16.setFontHeightInPoints((short) 16); fontSize16.setFontName("Arial Unicode MS"); HSSFFont fontSize12 = workbook.createFont(); fontSize12.setFontHeightInPoints((short) 12); fontSize12.setFontName("Arial Unicode MS"); // Header Toolket.setCellValue(workbook, sheet, 0, 0, year + "" + sterm + "?", fontSize16, HSSFCellStyle.ALIGN_CENTER, false, 35.0F, null); // Column Header Toolket.setCellValue(workbook, sheet, 1, 0, "?", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 1, "???", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 2, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 3, "??", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 4, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 5, "??", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); int index = 2; List<Object> maps = null; Dtime dtime = null; Csno csno = null; CourseSyllabus cs = null, target = null; Empl empl = null; String[] excluded = { "50000", "T0001", "T0002" }; target = new CourseSyllabus(); target.setSchoolYear(Integer.parseInt(year)); target.setSchoolTerm(Integer.parseInt(sterm)); for (Clazz clazz : clazzes) { if (Toolket.isDelayClass(clazz.getClassNo())) continue; //maps = cm.findDtimeCsnoBy(new Dtime(clazz.getClassNo(), sterm), //"cscode"); if (maps != null) { for (Object o : maps) { dtime = (Dtime) ((Object[]) o)[0]; csno = (Csno) ((Object[]) o)[1]; // ? if (!ArrayUtils.contains(excluded, csno.getCscode()) && !ArrayUtils.contains(IConstants.COURSE_SYLLABUS_INTRO, dtime.getCscode()) && StringUtils.isNotBlank(dtime.getTechid().trim())) { target.setDepartClass(clazz.getClassNo()); target.setCscode(csno.getCscode()); cs = cm.findCourseSyllabusBy1(target); if (cs == null) { empl = mm.findEmplByIdno(dtime.getTechid()); Toolket.setCellValue(workbook, sheet, index, 0, clazz.getClassNo(), fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 1, Toolket.getClassFullName(clazz.getClassNo()), fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 2, csno.getCscode(), fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 3, csno.getChiName().trim(), fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 4, dtime.getCredit().toString(), fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index++, 5, (empl == null ? "" : empl.getCname().trim()), fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); } else { cs = null; } } dtime = null; csno = null; } } } int xx = index - 2; // ? int yy = (Integer) count.get(0); // ? index++; sheet.addMergedRegion(new CellRangeAddress(index, index, 3, 5)); Toolket.setCellValue(workbook, sheet, index++, 3, " : " + xx + " / " + yy + " = " + ((float) xx / (float) yy), fontSize16, HSSFCellStyle.ALIGN_RIGHT, false, null); File tempDir = new File( context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno() + (new SimpleDateFormat("yyyyMMdd").format(new Date())))); if (!tempDir.exists()) tempDir.mkdirs(); File output = new File(tempDir, "RegisterList.xls"); FileOutputStream fos = new FileOutputStream(output); workbook.write(fos); fos.close(); JasperReportUtils.printXlsToFrontEnd(response, output); output.delete(); tempDir.delete(); } else { Map<String, String> param = new HashMap<String, String>(); File image = new File(context.getRealPath("/pages/images/2002chitS.jpg")); param.put("IMAGE", image.getAbsolutePath()); byte[] bytes = JasperRunManager.runReportToPdf(JasperReportUtils.getNoResultReport(context), param, new JREmptyDataSource()); JasperReportUtils.printPdfToFrontEnd(response, bytes); } }
From source file:tw.edu.chit.struts.action.course.ReportPrintAction.java
/** * //from w w w . j a va2s . c o m * * @param mapping org.apache.struts.action.ActionMapping object * @param form org.apache.struts.action.ActionForm object * @param request request javax.servlet.http.HttpServletRequest object * @param response response javax.servlet.http.HttpServletResponse object * @param sterm */ private void printIntroCheck(ActionMapping mapping, DynaActionForm form, HttpServletRequest request, HttpServletResponse response, String sterm) throws Exception { HttpSession session = request.getSession(false); AdminManager am = (AdminManager) getBean(IConstants.ADMIN_MANAGER_BEAN_NAME); CourseManager cm = (CourseManager) getBean(IConstants.COURSE_MANAGER_BEAN_NAME); MemberManager mm = (MemberManager) getBean(IConstants.MEMBER_MANAGER_BEAN_NAME); ScoreManager sm = (ScoreManager) getBean(IConstants.SCORE_MANAGER_BEAN_NAME); ServletContext context = request.getSession().getServletContext(); String year = am.findTermBy(IConstants.PARAMETER_SCHOOL_YEAR); // ??? List<Clazz> clazzes = sm.findClassBy(new Clazz(processClassInfo(form)), getUserCredential(session).getClassInChargeAry(), true); if (!clazzes.isEmpty()) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(""); sheet.setColumnWidth(0, 3000); sheet.setColumnWidth(1, 5000); sheet.setColumnWidth(2, 3000); sheet.setColumnWidth(3, 8000); sheet.setColumnWidth(4, 2000); sheet.setColumnWidth(5, 5000); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); HSSFFont fontSize16 = workbook.createFont(); fontSize16.setFontHeightInPoints((short) 16); fontSize16.setFontName("Arial Unicode MS"); HSSFFont fontSize12 = workbook.createFont(); fontSize12.setFontHeightInPoints((short) 12); fontSize12.setFontName("Arial Unicode MS"); // Header Toolket.setCellValue(workbook, sheet, 0, 0, year + "" + sterm + "", fontSize16, HSSFCellStyle.ALIGN_CENTER, false, 35.0F, null); // Column Header Toolket.setCellValue(workbook, sheet, 1, 0, "?", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 1, "???", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 2, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 3, "??", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 4, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 5, "??", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); int index = 2; List<Object> maps = null; Dtime dtime = null; Csno csno = null; CourseIntroduction ci = null; Empl empl = null; String[] excluded = { "50000", "T0001", "T0002" }; for (Clazz clazz : clazzes) { if (Toolket.isDelayClass(clazz.getClassNo())) continue; //maps = cm.findDtimeCsnoBy(new Dtime(clazz.getClassNo(), sterm), //"cscode"); if (maps != null) { for (Object o : maps) { dtime = (Dtime) ((Object[]) o)[0]; csno = (Csno) ((Object[]) o)[1]; // ? if (!ArrayUtils.contains(excluded, csno.getCscode()) && !ArrayUtils.contains(IConstants.COURSE_SYLLABUS_INTRO, dtime.getCscode()) && StringUtils.isNotBlank(dtime.getTechid().trim())) { ci = cm.getCourseIntrosByDtimeOid(dtime.getOid(), Integer.parseInt(year), Integer.parseInt(sterm)); if (ci == null) { empl = mm.findEmplByIdno(dtime.getTechid()); Toolket.setCellValue(workbook, sheet, index, 0, clazz.getClassNo(), null, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 1, Toolket.getClassFullName(clazz.getClassNo()), null, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 2, csno.getCscode(), null, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 3, csno.getChiName().trim(), null, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 4, dtime.getCredit().toString(), null, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index++, 5, (empl == null ? "" : empl.getCname().trim()), null, HSSFCellStyle.ALIGN_CENTER, true, null); } else { ci = null; } } dtime = null; csno = null; } } } File tempDir = new File( context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno() + (new SimpleDateFormat("yyyyMMdd").format(new Date())))); if (!tempDir.exists()) tempDir.mkdirs(); File output = new File(tempDir, "RegisterList.xls"); FileOutputStream fos = new FileOutputStream(output); workbook.write(fos); fos.close(); JasperReportUtils.printXlsToFrontEnd(response, output); output.delete(); tempDir.delete(); } else { Map<String, String> param = new HashMap<String, String>(); File image = new File(context.getRealPath("/pages/images/2002chitS.jpg")); param.put("IMAGE", image.getAbsolutePath()); byte[] bytes = JasperRunManager.runReportToPdf(JasperReportUtils.getNoResultReport(context), param, new JREmptyDataSource()); JasperReportUtils.printPdfToFrontEnd(response, bytes); } }
From source file:tw.edu.chit.struts.action.course.ReportPrintAction.java
/** * ?/*from ww w . ja v a 2s. c o m*/ * * @param mapping org.apache.struts.action.ActionMapping object * @param form org.apache.struts.action.ActionForm object * @param request request javax.servlet.http.HttpServletRequest object * @param response response javax.servlet.http.HttpServletResponse object * @param sterm */ @SuppressWarnings("unchecked") private void printCourseDataPrint(ActionMapping mapping, DynaActionForm form, HttpServletRequest request, HttpServletResponse response, String sterm) throws Exception { HttpSession session = request.getSession(false); AdminManager am = (AdminManager) getBean(IConstants.ADMIN_MANAGER_BEAN_NAME); MemberManager mm = (MemberManager) getBean(IConstants.MEMBER_MANAGER_BEAN_NAME); CourseManager cm = (CourseManager) getBean(IConstants.COURSE_MANAGER_BEAN_NAME); ScoreManager sm = (ScoreManager) getBean(IConstants.SCORE_MANAGER_BEAN_NAME); ServletContext context = request.getSession().getServletContext(); Integer year = cm.getSchoolYear(); List<Clazz> clazzes = sm.findClassBy(new Clazz(processClassInfo(form)), getUserCredential(session).getClassInChargeAry(), true); if (!clazzes.isEmpty()) { File templateXLS = new File(context.getRealPath("/WEB-INF/reports/CourseDataPrint.xls")); HSSFWorkbook workbook = Toolket.getHSSFWorkbook(templateXLS); HSSFFont fontSize12 = workbook.createFont(); fontSize12.setFontHeightInPoints((short) 12); fontSize12.setFontName("Arial Unicode MS"); HSSFSheet sheet = workbook.getSheetAt(0); Toolket.setCellValue(sheet, 0, 0, "?" + year + "?"); Toolket.setCellValue(sheet, 0, 30, "" + new SimpleDateFormat("yyyy/MM/dd").format(new Date())); int index = 2, boys = 0, girls = 0; boolean flag = false; String departClass = null, optValue = null; Dtime dtime = null; Empl empl = null; Csno csno = null; List<Object> objs = null; List<DtimeTeacher> dtimeTeachers = null; List<String> csGroups = null; List<Student> stmds = null; Object[] data = null; String[] no = { "50000", "T0001", "T0002" }; String[] no1 = { "", "", "", "", "()", "()", "()", "()", "()", "()" }; String[] no2 = { "", "" }; String[] english = { "S0351", "S0352", "S0353", "S0391", "T0090", "T0350", "T0351", "T0352", "T0360", "T0B20", "TD890", "TG321", "TH821", "THQ20", "TP3W0", "TQ090" }; String[] japan = { "T0161" }; StringBuffer buffer = new StringBuffer(); String hql = "SELECT d, cs FROM Dtime d, Csno cs " + "WHERE d.cscode = cs.cscode " + "AND d.sterm = ? AND d.departClass = ?"; String hql1 = "SELECT DISTINCT cg.cname FROM CsGroup cg, CsGroupSet cs " + "WHERE cg.oid = cs.groupOid AND cs.cscode = ? ORDER BY cg.oid"; for (Clazz clazz : clazzes) { departClass = clazz.getClassNo(); if (Toolket.isDelayClass(departClass)) continue; objs = am.find(hql, new Object[] { sterm, departClass }, -1); for (Object o : objs) { data = (Object[]) o; dtime = (Dtime) data[0]; csno = (Csno) data[1]; stmds = cm.findSeldStudentByDtimeOid(dtime.getOid()); if (!ArrayUtils.contains(no, dtime.getCscode()) && !stmds.isEmpty()) { csGroups = am.find(hql1, new Object[] { dtime.getCscode() }, -1); if (csGroups.isEmpty()) Toolket.setCellValue(sheet, index, 0, ""); else { buffer = new StringBuffer(); for (String cg : csGroups) buffer.append(cg).append(","); Toolket.setCellValue(sheet, index, 0, StringUtils.substring(buffer.toString(), 0, buffer.toString().length() - 1)); // ?? } Toolket.setCellValue(sheet, index, 1, ""); // Toolket.setCellValue(sheet, index, 2, "D".equalsIgnoreCase(clazz.getSchoolType()) ? "0" : ("N".equalsIgnoreCase(clazz.getSchoolType()) ? "1" : "2")); // Toolket.setCellValue(sheet, index, 3, Toolket.getSchoolNoBy(clazz)); // / Toolket.setCellValue(sheet, index, 4, StringUtils.abbreviate(csno.getChiName(), 64)); // ?? Toolket.setCellValue(sheet, index, 5, StringUtils.abbreviate(Toolket.getDepartName(departClass), 25)); // Toolket.setCellValue(sheet, index, 6, ""); // Toolket.setCellValue(sheet, index, 7, "1"); // flag = false; for (String noValue : no1) { if (csno.getChiName().endsWith(noValue)) { flag = true; break; } } if (!flag) { for (String noValue : no2) { if (StringUtils.contains(csno.getChiName(), noValue)) { flag = true; break; } } } Toolket.setCellValue(sheet, index, 8, flag ? "1" : "0"); // ? optValue = "1".equals(dtime.getOpt()) ? "0" : ("2".equals(dtime.getOpt()) ? "1" : "3"); Toolket.setCellValue(sheet, index, 9, optValue); // ? Toolket.setCellValue(sheet, index, 10, dtime.getCredit().toString()); // Toolket.setCellValue(sheet, index, 11, dtime.getThour().toString()); // ? Toolket.setCellValue(sheet, index, 12, ""); // ? Toolket.setCellValue(sheet, index, 13, clazz.getGrade()); // Toolket.setCellValue(sheet, index, 14, clazz.getClassName()); // ? Toolket.setCellValue(sheet, index, 15, "http://www.cust.edu.tw/www/info/intro_en.php?coursenum=" + dtime.getOid()); // ? Toolket.setCellValue(sheet, index, 16, "http://www.cust.edu.tw/www/info/intro_obj.php?coursenum=" + dtime.getOid()); // Toolket.setCellValue(sheet, index, 17, ""); // ? buffer = new StringBuffer(); empl = mm.findEmplByIdno(dtime.getTechid()); if (empl != null) buffer.append(empl.getCname()).append(","); dtimeTeachers = cm.getDtimeTeacherBy(dtime.getOid().toString()); for (DtimeTeacher dt : dtimeTeachers) buffer.append(dt.getChiName2()).append(","); Toolket.setCellValue(sheet, index, 18, StringUtils.substring(buffer.toString(), 0, buffer.toString().length() - 1)); // ? Toolket.setCellValue(sheet, index, 19, ""); // Toolket.setCellValue(sheet, index, 20, ""); // if (ArrayUtils.contains(english, csno.getCscode())) { Toolket.setCellValue(sheet, index, 21, ""); // 1 Toolket.setCellValue(sheet, index, 22, ""); // 2 } else if (ArrayUtils.contains(japan, csno.getCscode())) { Toolket.setCellValue(sheet, index, 21, ""); // 1 Toolket.setCellValue(sheet, index, 22, ""); // 2 } else { Toolket.setCellValue(sheet, index, 21, ""); // 1 Toolket.setCellValue(sheet, index, 22, ""); // 2 } Toolket.setCellValue(sheet, index, 23, ""); // Toolket.setCellValue(sheet, index, 24, ""); // 1 Toolket.setCellValue(sheet, index, 25, ""); // 2 Toolket.setCellValue(sheet, index, 26, StringUtils.abbreviate(csno.getEngName(), 255)); // ?? Toolket.setCellValue(sheet, index, 27, "0"); // Toolket.setCellValue(sheet, index, 28, StringUtils.contains(csno.getChiName(), "") ? "2" : Toolket.getCourseStyleBy(dtime.getElearning())); // Toolket.setCellValue(sheet, index, 29, empl == null ? "" : Toolket.getTeacherSourceBy(empl.getUnit())); // ? boys = 0; girls = 0; for (Student student : stmds) { if ("1".equals(student.getSex())) boys++; else if ("2".equals(student.getSex())) girls++; } Toolket.setCellValue(sheet, index, 30, String.valueOf(boys)); // Toolket.setCellValue(sheet, index++, 31, String.valueOf(girls)); // } } } File tempDir = new File( context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno() + (new SimpleDateFormat("yyyyMMdd").format(new Date())))); if (!tempDir.exists()) tempDir.mkdirs(); File output = new File(tempDir, "CourseDataPrint.xls"); FileOutputStream fos = new FileOutputStream(output); workbook.write(fos); fos.close(); JasperReportUtils.printXlsToFrontEnd(response, output); output.delete(); tempDir.delete(); } else { Map<String, String> param = new HashMap<String, String>(); File image = new File(context.getRealPath("/pages/images/2002chitS.jpg")); param.put("IMAGE", image.getAbsolutePath()); byte[] bytes = JasperRunManager.runReportToPdf(JasperReportUtils.getNoResultReport(context), param, new JREmptyDataSource()); JasperReportUtils.printPdfToFrontEnd(response, bytes); } }
From source file:tw.edu.chit.struts.action.deptassist.ReportPrintAction.java
/** * ?//from w w w . jav a 2s .com * * @param mapping org.apache.struts.action.ActionMapping object * @param form org.apache.struts.action.ActionForm object * @param request request javax.servlet.http.HttpServletRequest object * @param response response javax.servlet.http.HttpServletResponse object * @param sterm */ @SuppressWarnings("unchecked") private void printCalculate(ActionMapping mapping, DynaActionForm form, HttpServletRequest request, HttpServletResponse response, String sterm) throws Exception { HttpSession session = request.getSession(false); AdminManager am = (AdminManager) getBean(ADMIN_MANAGER_BEAN_NAME); MemberManager mm = (MemberManager) getBean(IConstants.MEMBER_MANAGER_BEAN_NAME); CourseManager cm = (CourseManager) getBean(IConstants.COURSE_MANAGER_BEAN_NAME); ScoreManager sm = (ScoreManager) getBean(IConstants.SCORE_MANAGER_BEAN_NAME); ServletContext context = request.getSession().getServletContext(); String thisYear = cm.getNowBy("School_year"); String thisTerm = am.findTermBy(PARAMETER_SCHOOL_TERM); List<Clazz> clazzes = sm.findClassBy(new Clazz(processClassInfo(form)), getUserCredential(session).getClassInChargeAry(), false); int thisTermCounts = 0, lastTermCounts = 0; String departClass = null, deptCode = null, histDeptCode = null, currentDeptCode = null, chiName = null; ScoreHist scoreHist = null; List<Student> students = null; List<ScoreHist> scoreHistList = null; List<Map> seldInfo = null; List csnos = null; if (!clazzes.isEmpty()) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("?"); sheet.setColumnWidth(0, 3000); sheet.setColumnWidth(1, 3000); sheet.setColumnWidth(2, 5000); sheet.setColumnWidth(3, 5000); sheet.setColumnWidth(4, 5000); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4)); HSSFFont fontSize16 = workbook.createFont(); fontSize16.setFontHeightInPoints((short) 16); fontSize16.setFontName("Arial Unicode MS"); HSSFFont fontSize10 = workbook.createFont(); fontSize10.setFontHeightInPoints((short) 10); fontSize10.setFontName("Arial Unicode MS"); // Header Toolket.setCellValue(workbook, sheet, 0, 0, "?", fontSize16, HSSFCellStyle.ALIGN_CENTER, false, 35.0F, null); // Column Header Toolket.setCellValue(workbook, sheet, 1, 0, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 1, "??", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 2, "?", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 3, "??", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 4, "?", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); int index = 2; for (Clazz clazz : clazzes) { departClass = clazz.getClassNo(); deptCode = StringUtils.substring(departClass, 3, 4); if (Toolket.isDelayClass(departClass) || Toolket.isLiteracyClass(departClass)) continue; students = mm.findStudentsByClassNo(departClass); if (!students.isEmpty()) { if (thisYear.equals(request.getParameter("year")) && thisTerm.equals(sterm)) { // (Seld) for (Student student : students) { seldInfo = cm.findStudentSeldCourse(student.getStudentNo(), sterm); if (!seldInfo.isEmpty()) { for (Map m : seldInfo) { currentDeptCode = StringUtils.substring((String) m.get("depart_class"), 3, 4); if (!deptCode.equalsIgnoreCase(currentDeptCode) && !Toolket.isLiteracyClass((String) m.get("depart_class"))) { thisTermCounts++; Toolket.setCellValue(workbook, sheet, index, 0, student.getStudentNo(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 1, student.getStudentName(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 2, Toolket.getClassFullName(student.getDepartClass()), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 3, Toolket.getClassFullName((String) m.get("depart_class")), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index++, 4, (String) m.get("chi_name"), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); break; } } } } } else { // ?(ScoreHist) for (Student student : students) { scoreHist = new ScoreHist(student.getStudentNo()); scoreHist.setSchoolYear((short) Integer.parseInt(request.getParameter("year"))); scoreHist.setSchoolTerm(sterm); scoreHistList = sm.findScoreHistBy(scoreHist); HIST: { if (!scoreHistList.isEmpty()) { for (ScoreHist hist : scoreHistList) { if (StringUtils.isNotBlank(hist.getStdepartClass()) && !Toolket.isLiteracyClass(hist.getStdepartClass())) { histDeptCode = StringUtils.substring(hist.getStdepartClass(), 3, 4); if (!deptCode.equalsIgnoreCase(histDeptCode)) { lastTermCounts++; Toolket.setCellValue(workbook, sheet, index, 0, student.getStudentNo(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 1, student.getStudentName(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 2, Toolket.getClassFullName(student.getDepartClass()), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 3, Toolket.getClassFullName(hist.getStdepartClass()), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); csnos = cm.getCsnameBy(hist.getCscode()); if (!csnos.isEmpty()) chiName = ((Csno) csnos.get(0)).getChiName(); else chiName = ""; Toolket.setCellValue(workbook, sheet, index++, 4, chiName, fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); break HIST; } } } } } } } } } File tempDir = new File( context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno() + (new SimpleDateFormat("yyyyMMdd").format(new Date())))); if (!tempDir.exists()) tempDir.mkdirs(); File output = new File(tempDir, "Calculate.xls"); FileOutputStream fos = new FileOutputStream(output); workbook.write(fos); fos.close(); JasperReportUtils.printXlsToFrontEnd(response, output); output.delete(); tempDir.delete(); System.out.println("This Term: " + thisTermCounts); System.out.println("Last Term: " + lastTermCounts); } }