List of usage examples for org.apache.poi.hssf.usermodel HSSFCellStyle setFont
public void setFont(HSSFFont font)
From source file:Excel.IvaVentas.java
public void GenerrarInformeIvaVentas(String desde, String hasta) throws SQLException { HSSFWorkbook libro = new HSSFWorkbook(); HSSFSheet hoja = libro.createSheet("Iva Ventas"); /*// w w w . j a v a2s .c o m * GENERAR LAS SIGUIENTES HOJAS * 1- DETALLE DE MOVIMIENTOS DE CAJA - LEE EN MOVIMIENTOS CAJA INDENTIFICANDO EL TIPO DE MOVIMIENTO, USUARIOS Y * NUMERO DE CAJA * 2- DETALLE DE ARTICULOS VENDIDOS: LISTADO DE MOVIEMIENTOS DE ARTICULOS, CON USUARIOS Y CAJA * 3- DETALLE DE GASTOS : MOVIMIENTOS DE CAJA DETALLANDO LOS GASTOS * */ String ttx = "celda numero :"; HSSFRow fila = null; HSSFCell celda; HSSFCell celda1; HSSFCell celda2; HSSFCell celda3; HSSFCell celda4; HSSFCell celda5; HSSFCell celda6; HSSFCell celda7; HSSFCell celda8; HSSFFont fuente = libro.createFont(); //fuente.setFontHeight((short)21); fuente.setFontName(fuente.FONT_ARIAL); fuente.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); String form = null; String sql = "SELECT *,round(gravado,2)as gravadoR,round(impuesto,2)as impuestoR,round(total,2)as totalR FROM fiscal where fechaRegistro between '" + desde + "' and '" + hasta + "' group by numero order by numero"; //String sql="SELECT *,round(gravado,2)as gravadoR,round(impuesto,2)as impuestoR,round(total,2)as totalR FROM fiscal where fecha like '201607%' group by numero order by numero"; System.out.println(sql); Transaccionable tra = new ConeccionLocal(); ResultSet rs = tra.leerConjuntoDeRegistros(sql); HSSFCellStyle titulo = libro.createCellStyle(); titulo.setFont(fuente); //titulo.setFillBackgroundColor((short)22); titulo.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); titulo.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //for(int a=0;a < 100;a++){ int col = 0; int a = 0; if (a == 0) { fila = hoja.createRow(a); celda = fila.createCell(0); celda.setCellStyle(titulo); celda.setCellValue("Fecha"); celda1 = fila.createCell(1); celda1.setCellStyle(titulo); celda1.setCellValue("Tipo comp."); celda2 = fila.createCell(2); celda2.setCellStyle(titulo); celda2.setCellValue("Numero"); celda3 = fila.createCell(3); celda3.setCellStyle(titulo); celda3.setCellValue("Gravado"); celda4 = fila.createCell(4); celda4.setCellStyle(titulo); celda4.setCellValue("Impuesto"); celda5 = fila.createCell(5); celda5.setCellStyle(titulo); celda5.setCellValue("Total"); celda6 = fila.createCell(6); celda6.setCellStyle(titulo); celda6.setCellValue("Razon Social"); } while (rs.next()) { a++; //col=rs.getInt("tipoMovimiento"); switch (col) { case 1: break; default: break; } fila = hoja.createRow(a); celda = fila.createCell(0); ttx = ttx; celda.setCellType(HSSFCell.CELL_TYPE_STRING); celda.setCellValue(rs.getString("fecha")); celda1 = fila.createCell(1); ttx = ttx; celda1.setCellType(HSSFCell.CELL_TYPE_STRING); celda1.setCellValue(rs.getInt("tipo")); celda2 = fila.createCell(2); celda2.setCellType(HSSFCell.CELL_TYPE_STRING); String numero = rs.getString("numero").replaceFirst("80", "00"); celda2.setCellValue(numero); celda3 = fila.createCell(3); celda3.setCellType(HSSFCell.CELL_TYPE_NUMERIC); celda3.setCellValue(rs.getDouble("gravadoR")); celda4 = fila.createCell(4); celda4.setCellType(HSSFCell.CELL_TYPE_NUMERIC); celda4.setCellValue(rs.getDouble("impuestoR")); celda5 = fila.createCell(5); //celda5.setCellFormula(rs.getString("observaciones")); celda5.setCellType(HSSFCell.CELL_TYPE_NUMERIC); celda5.setCellValue(rs.getDouble("totalR")); //celda5.setCellValue(rs.getDate("fecha")); celda6 = fila.createCell(6); celda6.setCellType(HSSFCell.CELL_TYPE_STRING); celda6.setCellValue(rs.getString("razon")); } rs.close(); //texto+="\r\n"; String ruta = "fiscal/" + desde + "_" + hasta + " Iva Ventas.xls"; try { FileOutputStream elFichero = new FileOutputStream(ruta); try { libro.write(elFichero); elFichero.close(); Runtime.getRuntime().exec("rundll32 url.dll,FileProtocolHandler " + ruta); } catch (IOException ex) { Logger.getLogger(InformeMensual.class.getName()).log(Level.SEVERE, null, ex); } } catch (FileNotFoundException ex) { Logger.getLogger(InformeMensual.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:Excel.PlanillaStock.java
public void GenerarInforme() throws SQLException { HSSFWorkbook libro = new HSSFWorkbook(); HSSFSheet hoja = libro.createSheet("Listado de Articulos"); ArrayList listadoPorSucursal = new ArrayList(); Editables edi = new Articulos(); /*//from w w w . j a v a 2 s. c o m * GENERAR LAS SIGUIENTES HOJAS * 1- DETALLE DE MOVIMIENTOS DE CAJA - LEE EN MOVIMIENTOS CAJA INDENTIFICANDO EL TIPO DE MOVIMIENTO, USUARIOS Y * NUMERO DE CAJA * 2- DETALLE DE ARTICULOS VENDIDOS: LISTADO DE MOVIEMIENTOS DE ARTICULOS, CON USUARIOS Y CAJA * 3- DETALLE DE GASTOS : MOVIMIENTOS DE CAJA DETALLANDO LOS GASTOS * */ String ttx = "celda numero :"; HSSFRow fila = null; HSSFCell celda; HSSFCell celda1; HSSFCell celda2; HSSFCell celda3; HSSFCell celda4; HSSFCell celda5; HSSFCell celda6; HSSFCell celda7; HSSFCell celda8; HSSFCell celda9; HSSFCell celda10; HSSFCell celda11; HSSFFont fuente = libro.createFont(); //fuente.setFontHeight((short)21); fuente.setFontName(fuente.FONT_ARIAL); fuente.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); String form = null; HSSFCellStyle titulo = libro.createCellStyle(); //Iterator iCli=listadoClientes.listIterator(); Articulos cliente; titulo.setFont(fuente); //titulo.setFillBackgroundColor((short)22); titulo.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); titulo.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFCellStyle celdas = libro.createCellStyle(); celdas.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); celdas.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); celdas.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); celdas.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); celdas.setBottomBorderColor(IndexedColors.BLACK.getIndex()); celdas.setLeftBorderColor(IndexedColors.BLACK.getIndex()); celdas.setRightBorderColor(IndexedColors.BLACK.getIndex()); celdas.setTopBorderColor(IndexedColors.BLACK.getIndex()); int a = 0; String sql = "select *,(select sum(cantidad) FROM movimientosarticulos where movimientosarticulos.idArticulo=articulos.ID group by idArticulo,numeroDeposito limit 0,1)as sstock,round((articulos.dolar * articulos.PRECIO),2)as pl1,round((articulos.dolar * articulos.lista2),2)as pl2,round((articulos.dolar * articulos.lista3),2)as pl3,round((articulos.lista4 * articulos.dolar),2)as pl4,round((articulos.dolar * articulos.COSTO),2)as pcto from articulos where (select sum(cantidad) FROM movimientosarticulos where movimientosarticulos.idArticulo=articulos.ID group by idArticulo limit 0,1) > 0 order by sstock desc"; Transaccionable tra1 = new ConeccionLocal(); ResultSet rr1 = tra1.leerConjuntoDeRegistros(sql); //Facturar fact=new Articulos(); //ArrayList listadoClientes=fact.listadoBusqueda(""); System.out.println("cantidad registros " + rr1.getRow()); while (rr1.next()) { int col = 0; //int a=0; if (a == 0) { fila = hoja.createRow(a); celda = fila.createCell(0); celda.setCellStyle(titulo); celda.setCellValue("Codigo"); celda1 = fila.createCell(1); celda1.setCellStyle(titulo); celda1.setCellValue("Descripcion"); celda2 = fila.createCell(2); celda2.setCellStyle(titulo); celda2.setCellValue("cod. barras"); celda3 = fila.createCell(3); celda3.setCellStyle(titulo); celda3.setCellValue("Cantidad"); a++; } // cliente=new Articulos(); //cliente=(Articulos)listadoClientes.get(a); fila = hoja.createRow(a); celda = fila.createCell(0); ttx = ttx; celda.setCellStyle(celdas); celda.setCellType(HSSFCell.CELL_TYPE_STRING); celda.setCellValue(rr1.getString("ID")); celda1 = fila.createCell(1); ttx = ttx; celda1.setCellStyle(celdas); celda1.setCellType(HSSFCell.CELL_TYPE_STRING); celda1.setCellValue(rr1.getString("NOMBRE")); celda2 = fila.createCell(2); celda2.setCellStyle(celdas); celda2.setCellType(HSSFCell.CELL_TYPE_STRING); celda2.setCellValue(rr1.getString("BARRAS")); celda3 = fila.createCell(3); celda3.setCellStyle(celdas); celda3.setCellType(HSSFCell.CELL_TYPE_NUMERIC); celda3.setCellValue(rr1.getDouble("sstock")); celda4 = fila.createCell(4); celda4.setCellStyle(celdas); celda4.setCellType(HSSFCell.CELL_TYPE_STRING); celda4.setCellValue(""); celda5 = fila.createCell(5); celda5.setCellStyle(celdas); //celda5.setCellFormula(rs.getString("observaciones")); celda5.setCellType(HSSFCell.CELL_TYPE_STRING); celda5.setCellValue(""); //celda5.setCellValue(rs.getDate("fecha")); celda6 = fila.createCell(6); //celda5.setCellFormula(rs.getString("observaciones")); celda6.setCellStyle(celdas); celda6.setCellType(HSSFCell.CELL_TYPE_STRING); celda6.setCellValue(""); //listadoPorSucursal=edi.ListarPorSucursal(cliente); //Iterator il=listadoPorSucursal.listIterator(); a++; } rr1.close(); String ruta = "Informes\\planillaStock.xls"; try { FileOutputStream elFichero = new FileOutputStream(ruta); try { libro.write(elFichero); elFichero.close(); Runtime.getRuntime().exec("rundll32 url.dll,FileProtocolHandler " + ruta); } catch (IOException ex) { Logger.getLogger(InformeMensual.class.getName()).log(Level.SEVERE, null, ex); } } catch (FileNotFoundException ex) { Logger.getLogger(InformeMensual.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:excel.PoiWriteExcelFile.java
public static int generarReporte() { //Calendar cal=Calendar.getInstance(); Calendar cal = WorkMonitorUI.instante; try {//from www.j a v a 2s. c o m FileOutputStream fileOut = new FileOutputStream("HH_" + instante.getDisplayName(Calendar.MONTH, Calendar.SHORT_FORMAT, Locale.getDefault()) .toUpperCase() + "_" + persona.getNombre().toUpperCase().charAt(0) + "." + persona.getApellido().toUpperCase() + "_" + instante.get(Calendar.YEAR) + ".xls"); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet worksheet = workbook.createSheet( cal.getDisplayName(Calendar.MONTH, Calendar.SHORT_FORMAT, Locale.getDefault()).toUpperCase() + "-" + cal.get(Calendar.YEAR)); HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.YELLOW.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 12); font.setFontName("Calibri"); font.setItalic(false); font.setBold(true); font.setColor(HSSFColor.BLACK.index); cellStyle.setFont(font); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle diasStyle = workbook.createCellStyle(); diasStyle.setFillForegroundColor(HSSFColor.SEA_GREEN.index); diasStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); font = workbook.createFont(); font.setFontHeightInPoints((short) 11); font.setFontName("Calibri"); font.setItalic(false); font.setBold(true); font.setColor(HSSFColor.WHITE.index); diasStyle.setFont(font); diasStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); diasStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); diasStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); diasStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); diasStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); diasStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle schedStyle = workbook.createCellStyle(); schedStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); schedStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont font3 = workbook.createFont(); font3.setFontHeightInPoints((short) 11); font3.setFontName("Calibri"); font3.setItalic(false); font3.setColor(HSSFColor.BLACK.index); schedStyle.setFont(font3); schedStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); schedStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); schedStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); schedStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); schedStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); schedStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle workdayStyle = workbook.createCellStyle(); //workdayStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); workdayStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); workdayStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); workdayStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); workdayStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); workdayStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); workdayStyle.setWrapText(true); HSSFFont font2 = workbook.createFont(); font2.setFontHeightInPoints((short) 8); font2.setFontName("Serif"); font2.setItalic(false); //font2.setColor(HSSFColor.YELLOW.index); workdayStyle.setFont(font2); workdayStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle weekendStyle = workbook.createCellStyle(); weekendStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); weekendStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); weekendStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); weekendStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); weekendStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); weekendStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); weekendStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); weekendStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle horarioStyle = workbook.createCellStyle(); horarioStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); horarioStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); horarioStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); horarioStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); horarioStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); horarioStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); horarioStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); horarioStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont font4 = workbook.createFont(); font4.setFontHeightInPoints((short) 10); font4.setFontName("Serif"); font4.setItalic(false); font4.setBold(true); //font2.setColor(HSSFColor.YELLOW.index); horarioStyle.setFont(font4); // index from 0,0... cell A1 is cell(0,0) HSSFRow row1 = worksheet.createRow((short) 0); row1.setHeight((short) 500); //System.out.println("cal.get(Calendar.YEAR)="+cal.get(Calendar.YEAR)); HSSFCell cellA1 = row1.createCell((short) 0); cellA1.setCellValue( cal.getDisplayName(Calendar.MONTH, Calendar.SHORT_FORMAT, Locale.getDefault()).toUpperCase() + "-" + cal.get(Calendar.YEAR)); cellA1.setCellStyle(cellStyle); HSSFRow row2 = worksheet.createRow((short) 1); HSSFCell cellA4 = row2.createCell((short) 0); cellA4.setCellValue("Horario"); cellA4.setCellStyle(horarioStyle); //row2.setHeight((short)500); HSSFRow row3 = worksheet.createRow((short) 2); HSSFCell cellA3 = row3.createCell((short) 0); cellA3.setCellValue("Inicio - Trmino"); cellA3.setCellStyle(diasStyle); Calendar hora = Calendar.getInstance(); hora.set(Calendar.HOUR_OF_DAY, 9); hora.set(Calendar.MINUTE, 0); hora.set(Calendar.SECOND, 0); SimpleDateFormat sdf = new SimpleDateFormat("HH:mm"); HSSFCell cellXn; for (int i = 0; i < 29; ++i) { HSSFRow row = worksheet.createRow((short) i + 3); row.setHeight((short) 500); cellXn = row.createCell((short) 0); String horaIni = sdf.format(hora.getTime()); hora.add(Calendar.MINUTE, 30); String horaFin = sdf.format(hora.getTime()); cellXn.setCellValue(horaIni + " - " + horaFin); cellXn.setCellStyle(schedStyle); } System.out.println("cal.get(Calendar.MONTH)1=" + cal.get(Calendar.MONTH)); cal.add(Calendar.DAY_OF_MONTH, -cal.get(Calendar.DAY_OF_MONTH) + 1); int diasMes = cal.getActualMaximum(Calendar.DAY_OF_MONTH); System.out.println("cal.get(Calendar.MONTH)2=" + cal.get(Calendar.MONTH)); sdf = new SimpleDateFormat("EEEE d"); System.out.println( "cal.getActualMaximum(Calendar.DAY_OF_MONTH)1=" + cal.getActualMaximum(Calendar.DAY_OF_MONTH)); for (int i = 0; i < diasMes; ++i) { cellXn = row2.createCell((short) i + 1); String dia = sdf.format(cal.getTime()); dia = Character.toUpperCase(dia.charAt(0)) + dia.substring(1); cellXn.setCellValue(dia); cellXn.setCellStyle(horarioStyle); //System.out.println("cal.get(Calendar.DAY_OF_MONTH)="+cal.get(Calendar.DAY_OF_MONTH)); cal.add(Calendar.DAY_OF_MONTH, 1); } for (int i = 0; i < diasMes; ++i) { cellXn = row3.createCell((short) i + 1); cellXn.setCellValue("Descripcin"); cellXn.setCellStyle(diasStyle); } System.out.println( "cal.getActualMaximum(Calendar.DAY_OF_MONTH)2=" + cal.getActualMaximum(Calendar.DAY_OF_MONTH)); // Retroceder mes para que quede como estaba cal.add(Calendar.MONTH, -1); //cal.add(Calendar.DAY_OF_MONTH, -1); System.out.println( "cal.getActualMaximum(Calendar.DAY_OF_MONTH)3=" + cal.getActualMaximum(Calendar.DAY_OF_MONTH)); HhDao hhDao = new HhDao(); Object[][] hh = new Object[29][cal.getActualMaximum(Calendar.DAY_OF_MONTH)]; hh = hhDao.getByMes(WorkMonitorUI.persona.getId(), cal.getTime()); cal.set(Calendar.DAY_OF_MONTH, 1); Sheet sheet = workbook.getSheetAt(0); sdf = new SimpleDateFormat("EEEE"); HSSFPatriarch _drawing = (HSSFPatriarch) sheet.createDrawingPatriarch(); CreationHelper factory = workbook.getCreationHelper(); for (int i = 0; i < 29; ++i) { Row r = sheet.getRow(i + 3); for (int j = 0; j < diasMes; ++j) { if (hh[i][j].toString() != "") { cellXn = (HSSFCell) r.createCell((short) j + 1); Hh _hh = (Hh) hh[i][j]; cellXn.setCellValue( _hh.getTarea().getNombre().trim() + ": " + _hh.getActividad().getNombre().trim()); HSSFAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5); org.apache.poi.ss.usermodel.Comment comment = _drawing.createComment(anchor); String comentario = _hh.getTarea().getComentario().toLowerCase(); if (_hh.getComentario() != null) comentario = comentario + _hh.getComentario().toLowerCase(); RichTextString str = factory.createRichTextString(comentario); comment.setString(str); cellXn.setCellComment(comment); } else { cellXn = (HSSFCell) r.createCell((short) j + 1); cellXn.setCellValue(""); } //System.out.println("sdf.format(cal.getTime())="+sdf.format(cal.getTime())); if (Arrays.asList("sbado", "domingo").contains(sdf.format(cal.getTime()))) cellXn.setCellStyle(weekendStyle); else cellXn.setCellStyle(workdayStyle); sheet.setColumnWidth(j, 5000); cal.add(Calendar.DAY_OF_MONTH, 1); //sheet.autoSizeColumn(j); } // Retroceder mes para que quede como estaba cal.add(Calendar.MONTH, -1); System.out.println("cal.get(Calendar.MONTH)3=" + cal.get(Calendar.MONTH)); cal.set(Calendar.DAY_OF_MONTH, 1); } sheet.setColumnWidth(diasMes, 5000); WorkMonitorUI.instante = Calendar.getInstance(); sheet.setColumnWidth(0, 5000); sheet.createFreezePane(1, 3); // Freeze just one row //sheet.createFreezePane( 0, 1, 0, 1 ); workbook.write(fileOut); fileOut.flush(); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); return -1; } catch (IOException e) { e.printStackTrace(); return -2; } return 1; }
From source file:exportToExcel.ResultSetToExcel.java
License:Open Source License
public HSSFWorkbook dump(ResultSet resultSet) throws SQLException { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFFont boldFont = workbook.createFont(); boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFSheet sheet = workbook.createSheet("sheet"); HSSFRow titleRow = sheet.createRow(0); ResultSetMetaData metaData = resultSet.getMetaData(); int columnCount = metaData.getColumnCount(); HSSFDataFormat dataFormat = workbook.createDataFormat(); HSSFCellStyle[] dataStyles = new HSSFCellStyle[columnCount + 1]; for (int colIndex = 0; colIndex < columnCount; colIndex++) { String title = metaData.getColumnLabel(colIndex + 1); HSSFCell cell = HSSFCellUtil.createCell(titleRow, colIndex, title); HSSFCellStyle style = workbook.createCellStyle(); style.setFont(boldFont); cell.setCellStyle(style);/*from ww w .j a va 2s . c o m*/ HSSFCellStyle dataStyle = getDataStyle(workbook, metaData, colIndex, dataFormat); dataStyles[colIndex] = dataStyle; } dumpData(resultSet, sheet, columnCount, dataStyles); return workbook; }
From source file:FILING.cboreport.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods.//from w w w . j av a 2 s . com * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { response.setContentType("text/html;charset=UTF-8"); // PrintWriter out = response.getWriter(); try { dbConn conn = new dbConn("1"); String District[]; String Year = ""; District = request.getParameterValues("District"); Year = request.getParameter("Year"); String FirstName = ""; String MiddleName = ""; String Surname = ""; String ovcid1 = ""; String ovcid2 = ""; int value0 = 0; int value1 = 0; String Districtid = ""; String cboid = ""; String doc = ""; String docname = ""; String cboname = ""; String districtname = ""; String distval = ""; int activeOVC = 0; int activeHH = 0; float activeovc = 0; float activehh = 0; float percent = 0; // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet shet1 = wb.createSheet(); //%%%%%%%%%%%%%%%%HEADER FONTS AND COLORATION%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% // style header with font color yello HSSFFont font_header = wb.createFont(); font_header.setFontHeightInPoints((short) 10); font_header.setFontName("Arial Black"); font_header.setItalic(true); font_header.setBoldweight((short) 05); font_header.setColor(HSSFColor.BLACK.index); CellStyle style_header = wb.createCellStyle(); style_header.setFont(font_header); style_header.setWrapText(true); style_header.setFillForegroundColor(HSSFColor.YELLOW.index); style_header.setBorderBottom(HSSFCellStyle.BORDER_THIN); style_header.setBorderTop(HSSFCellStyle.BORDER_THIN); style_header.setBorderRight(HSSFCellStyle.BORDER_THIN); style_header.setBorderLeft(HSSFCellStyle.BORDER_THIN); style_header.setAlignment(CellStyle.ALIGN_CENTER); style_header.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // stylex with font color blue and backgound grey HSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.PALE_BLUE.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(CellStyle.ALIGN_CENTER); // gold bg color -style HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 12); font.setFontName("Cambria"); font.setItalic(true); font.setBoldweight((short) 02); font.setColor(HSSFColor.BLACK.index); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setWrapText(true); style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); // for border with no font color CellStyle style_border = wb.createCellStyle(); style_border.setBorderBottom(HSSFCellStyle.BORDER_THIN); style_border.setBorderTop(HSSFCellStyle.BORDER_THIN); style_border.setBorderRight(HSSFCellStyle.BORDER_THIN); style_border.setBorderLeft(HSSFCellStyle.BORDER_THIN); HSSFFont font1 = wb.createFont(); font1.setFontHeightInPoints((short) 18); font1.setFontName("Cambria"); font1.setBoldweight((short) 7); font1.setColor(HSSFColor.BLACK.index); CellStyle style_border1 = wb.createCellStyle(); style_border1.setBorderBottom(HSSFCellStyle.BORDER_THIN); style_border1.setBorderTop(HSSFCellStyle.BORDER_THIN); style_border1.setBorderRight(HSSFCellStyle.BORDER_THIN); style_border1.setBorderLeft(HSSFCellStyle.BORDER_THIN); style_border1.setFont(font); style_border1.setAlignment(CellStyle.ALIGN_CENTER); style_border1.setVerticalAlignment(CellStyle.ALIGN_LEFT); HSSFRow rw1 = shet1.createRow(1); rw1.setHeightInPoints(30); for (int y = 0; y < 11; ++y) { HSSFCell cell = rw1.createCell(y); cell.setCellStyle(stylex); if (y == 0) { cell.setCellValue("CBO FILING TRACKER REPORT" + "( " + Year + ")"); } } shet1.addMergedRegion(new CellRangeAddress(1, 1, 0, 11)); shet1.setColumnWidth(0, 4500); shet1.setColumnWidth(1, 8500); shet1.setColumnWidth(2, 5000); shet1.setColumnWidth(3, 5000); shet1.setColumnWidth(4, 5000); shet1.setColumnWidth(5, 5000); shet1.setColumnWidth(6, 5000); shet1.setColumnWidth(7, 5000); shet1.setColumnWidth(8, 5000); shet1.setColumnWidth(9, 5000); shet1.setColumnWidth(10, 5000); shet1.setColumnWidth(11, 5000); shet1.setColumnWidth(12, 5000); shet1.setColumnWidth(13, 5000); shet1.setColumnWidth(14, 5000); shet1.setColumnWidth(15, 5000); shet1.setColumnWidth(16, 5000); shet1.setColumnWidth(17, 4000); shet1.setColumnWidth(18, 4000); shet1.setColumnWidth(19, 4200); shet1.setColumnWidth(20, 4200); shet1.setColumnWidth(21, 4200); shet1.setColumnWidth(22, 4200); // Merge the cells // shet1.addMergedRegion(new CellRangeAddress(1,1,1,3)); HSSFRow rw4 = shet1.createRow(2); rw4.setHeightInPoints(40); HSSFRow rw6 = shet1.createRow(3); rw6.setHeightInPoints(25); // rw4.setRowStyle(style); // // // rw6.setRowStyle(style); // // rw4.createCell(1).setCellValue("Number"); HSSFCell cell1, cell2, cell3, cell4, cell5, cell6, cell7, cell8, cell9, cell10, cell11, cell12, cell13, cell14, cell15, cell16; // cells fo row 2 cell1 = rw4.createCell(0); cell2 = rw4.createCell(1); cell4 = rw4.createCell(3); cell1.setCellValue("DISTRICT"); cell1.setCellStyle(style); cell2.setCellValue("CBO"); cell2.setCellStyle(style); cell6 = rw6.createCell(0); cell6.setCellValue("Status"); cell6.setCellStyle(style); cell7 = rw6.createCell(1); cell7.setCellValue(""); cell7.setCellStyle(style); shet1.addMergedRegion(new CellRangeAddress(3, 3, 0, 1)); int rowcount = 3; int doccounter = 4; int doccounter1 = 4; int columcounter = 3; String cboids = ""; int mergecounter = 2; cell3 = rw4.createCell(2); cell3.setCellValue("ACTIVE OVC"); cell3.setCellStyle(style); cell3 = rw4.createCell(3); cell3.setCellValue("ACTIVE HH"); cell3.setCellStyle(style); cell6 = rw6.createCell(2); cell6.setCellValue(""); cell6.setCellStyle(stylex); cell6 = rw6.createCell(3); cell6.setCellValue(""); cell6.setCellStyle(stylex); ArrayList docidarray = new ArrayList(); String getdocname = "select * from ovcdocuments WHERE DocumentName!=''"; conn.rs3 = conn.state3.executeQuery(getdocname); while (conn.rs3.next()) { docidarray.add(conn.rs3.getString(1)); System.out.println(conn.rs3.getString(2)); docname = conn.rs3.getString(2); cell3 = rw4.createCell(doccounter1); cell3.setCellValue(docname); cell3.setCellStyle(style); cell6 = rw6.createCell(doccounter1); cell6.setCellValue("Available"); cell6.setCellStyle(stylex); // cell5=rw6.createCell(doccounter1); // cell5.setCellValue("Not Available"); // cell5.setCellStyle(stylex); doccounter1++; System.out.println("mergecounter b4" + mergecounter); // shet1.addMergedRegion(new CellRangeAddress(2,2,2,3)); // shet1.addMergedRegion(new CellRangeAddress(2,2,4,5)); // shet1.addMergedRegion(new CellRangeAddress(2,2,6,7)); // shet1.addMergedRegion(new CellRangeAddress(2,2,8,9)); // shet1.addMergedRegion(new CellRangeAddress(2,2,10,11)); // shet1.addMergedRegion(new CellRangeAddress(2,2,12,13)); // shet1.addMergedRegion(new CellRangeAddress(2,2,14,15)); // shet1.addMergedRegion(new CellRangeAddress(2,2,16,17)); // shet1.addMergedRegion(new CellRangeAddress(2,2,18,19)); // shet1.addMergedRegion(new CellRangeAddress(2,2,20,21)); System.out.println("mergecounter after" + mergecounter); // mergecounter++; //shet1.addMergedRegion(new CellRangeAddress(2,2,doccounter1,doccounter1++)); } System.out.println("lll " + doccounter1); int rowcounter = 4; int counter = 0; int countercopy = 4; HSSFRow rw5 = null; for (int j = 0; j < District.length; j++) { String getcboCOUNT = "select * from CBO where DistrictID='" + District[j] + "' Order by DistrictID "; conn.rs2 = conn.state2.executeQuery(getcboCOUNT); while (conn.rs2.next()) { counter++; rowcount++; rw5 = shet1.createRow(rowcount); for (int i = 2; i < doccounter1; i++) { System.out.println("mm " + i); cell5 = rw5.createCell(i); cell5.setCellValue(""); cell5.setCellStyle(style_border1); } cell2 = rw5.createCell(1); cell2.setCellValue(conn.rs2.getString(2)); cell2.setCellStyle(style_border); // cell5=rw5.createCell(rowcount); // cell6=rw5.createCell(rowcounter++); // cell5.setCellValue("x"); // cell6.setCellValue("y"); System.out.println("rowcount " + rowcount + " lll " + counter + " rowcounter " + rowcounter); rw5.setHeightInPoints(25); // cell3=rw5.createCell(2); String getDistrict = "select * from District where DistrictID='" + District[j] + "' Order by DistrictID "; conn.rs4 = conn.state4.executeQuery(getDistrict); while (conn.rs4.next()) { districtname = conn.rs4.getString("District"); cell1 = rw5.createCell(0); cell1.setCellValue(districtname); cell1.setCellStyle(style_border1); } doccounter = 4; System.out.println("doccount " + doccounter); String getdocname1 = "select * from ovcdocuments"; // conn.rs3 = conn.state.executeQuery(getdocname1); // while(conn.rs3.next()){ String getData = "select " + "SUM(CASE WHEN ovcfiling.value='1' THEN 1 ELSE 0 END) AS COUNT1, " + "SUM(CASE WHEN ovcfiling.value='0' THEN 1 ELSE 0 END) AS COUNT0," + "Clientdetails.Cbo," + "ovcfiling.ovcdocid,Clientdetails.District," + "count(Clientdetails.OVCID)," + "count(DISTINCT(Clientdetails.HouseHoldheadID))" + " from ovcfiling,Clientdetails " + "WHERE Clientdetails.District ='" + District[j] + "' and " + "Clientdetails.Cbo='" + conn.rs2.getString(1) + "' and Clientdetails.Exited='1' and " + " (Year='" + Year + "' OR Year='') AND Clientdetails.OVCID = ovcfiling.ovcid " + "group by ovcfiling.ovcdocid,Clientdetails.Cbo,Clientdetails.District Order by ovcfiling.ovcdocid,District "; System.out.println("aaaaa " + getData); conn.rs = conn.state.executeQuery(getData); while (conn.rs.next()) { value1 = conn.rs.getInt(1); value0 = conn.rs.getInt(2); cboid = conn.rs.getString(3); doc = conn.rs.getString(4); Districtid = conn.rs.getString(5); activeOVC = conn.rs.getInt(6); activeHH = conn.rs.getInt(7); int a = 0; for (int i = 0; i < docidarray.size(); i++) { System.out.println("hh " + docidarray.get(i) + " " + doc); if (docidarray.get(i).equals(doc)) { System.out.println(doc + "lll" + docidarray.get(i)); int cellcount = i + 2; activeovc = activeOVC; activehh = activeHH; if (doc.equals("8")) { percent = value1 / activehh * 100; } else { percent = value1 / activeovc * 100; } cell7 = rw5.createCell(2); cell8 = rw5.createCell(3); cell5 = rw5.createCell(doccounter); // cell6=rw5.createCell(doccounter); cell5.setCellValue(Math.round(percent) + "%"); // cell6.setCellValue(value0); //FOR ACTICE OVCs cell7.setCellValue(activeOVC); cell7.setCellStyle(style_border); //FOR ACTICE hhs cell8.setCellValue(activeHH); cell8.setCellStyle(style_border); cell5.setCellStyle(style_border); cell6.setCellStyle(style_border); System.out.println("****a " + doc + " " + doccounter); if (doc.equals("2")) { // System.out.println("****i "+doc +" "+doccounter); doccounter++; // doccounter++; System.out.println("am in2"); System.out.println("****b " + doc + " " + doccounter); } if (doc.equals("3")) { doccounter--; // doccounter--; // doccounter--; System.out.println("****f " + doc + " " + doccounter); cell5 = rw5.createCell(doccounter++); cell5.setCellStyle(style_border); cell5.setCellValue(Math.round(percent) + "%"); cell6 = rw5.createCell(doccounter); // cell6.setCellStyle(style_border); // cell6.setCellValue(value0); System.out.println("****b " + doc + " " + doccounter); doccounter--; } // else if(!doc.equals("4") && !docidarray.get(i).equals("4")){ // doccounter++; // doccounter++; // System.out.println("****c "+doc +" "+doccounter); // } doccounter++; } } } doccounter = 2; // String getcbo= "select * from CBO where cboid='"+cboid+"'"; // conn.rs2 = conn.state2.executeQuery(getcbo); // while(conn.rs2.next()){ // // cell2=rw5.createCell(1); // cell2.setCellValue(conn.rs2.getString(2)); // // System.out.println("rowcount "+rowcount ); // // } //} System.out.println("aaaaaa " + districtname + "__" + cboname + "____" + docname + "___" + value1 + "__" + value0); } //} // end of while loop if (distval.equals("")) { // totalvalue= countercopy+counter; System.out.println(countercopy + " counter " + counter + " " + rowcount); distval = districtname; System.out.println(countercopy + " nnnn " + counter + " " + rowcount); if (counter > countercopy) { shet1.addMergedRegion(new CellRangeAddress(countercopy, rowcount, 0, 0)); countercopy = rowcount; //cell1.setCellValue(districtname); } System.out.println(countercopy + " nnn " + counter + " " + rowcount + " " + distval); } // cell1.setCellValue(districtname); if (!distval.equals(districtname) && !distval.equals("")) { distval = districtname; // cell1.setCellValue(districtname); shet1.addMergedRegion(new CellRangeAddress(countercopy + 1, rowcount, 0, 0)); countercopy = rowcount; // System.out.println(counter + "@@@@1 " + rowcount + "__" + countercopy); System.out.println(distval + "@@@@1 " + districtname); } System.out.println(counter + "@@@@ " + rowcount); //shet1.addMergedRegion(new CellRangeAddress(counter,rowcount,0,0)); System.out.println(distval + "@@@@ " + districtname); } // end of for loop // int totalvalue=countercopy+counter; // System.out.println(counter+" hhhh "+countercopy); // if(counter>countercopy){ // shet1.addMergedRegion(new CellRangeAddress(countercopy,totalvalue-1,0,0)); // // countercopy=counter; // } // System.out.println("aaaaaannnn "+districtname+"__"+ cboname +"____"+ doc +"___"+value1 +"__"+value0 ); // write it as an excel attachment ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=CBO_FILING_TRACKER_REPORT_FOR_" + Year + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } finally { // out.close(); } }
From source file:FILING.childdetailsreport.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods.// w ww.j a va 2s . co m * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { response.setContentType("text/html;charset=UTF-8"); // PrintWriter out = response.getWriter(); try { dbConn conn = new dbConn("1"); String District = ""; District = request.getParameter("District"); String Year = ""; Year = request.getParameter("Year"); String chw = ""; String FirstName = ""; String MiddleName = ""; String Surname = ""; String ovcid1 = ""; String ovcid2 = ""; int value0 = 0; int value1 = 0; String Districtid = ""; String cboid = ""; String doc = ""; String docname = ""; String cboname = ""; String districtname = ""; String chwval = ""; String docid = ""; // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet shet1 = wb.createSheet(); int cbocount = 3; //%%%%%%%%%%%%%%%%HEADER FONTS AND COLORATION%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% // style header with font color yello HSSFFont font_header = wb.createFont(); font_header.setFontHeightInPoints((short) 10); font_header.setFontName("Arial Black"); font_header.setItalic(true); font_header.setBoldweight((short) 05); font_header.setColor(HSSFColor.BLACK.index); CellStyle style_header = wb.createCellStyle(); style_header.setFont(font_header); style_header.setWrapText(true); style_header.setFillForegroundColor(HSSFColor.YELLOW.index); style_header.setBorderBottom(HSSFCellStyle.BORDER_THIN); style_header.setBorderTop(HSSFCellStyle.BORDER_THIN); style_header.setBorderRight(HSSFCellStyle.BORDER_THIN); style_header.setBorderLeft(HSSFCellStyle.BORDER_THIN); style_header.setAlignment(CellStyle.ALIGN_CENTER); style_header.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // stylex with font color blue and backgound grey HSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.PALE_BLUE.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(CellStyle.ALIGN_CENTER); // gold bg color -style HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 12); font.setFontName("Cambria"); font.setItalic(true); font.setBoldweight((short) 02); font.setColor(HSSFColor.BLACK.index); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setWrapText(true); style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); // for border with no font color CellStyle style_border = wb.createCellStyle(); style_border.setBorderBottom(HSSFCellStyle.BORDER_THIN); style_border.setBorderTop(HSSFCellStyle.BORDER_THIN); style_border.setBorderRight(HSSFCellStyle.BORDER_THIN); style_border.setBorderLeft(HSSFCellStyle.BORDER_THIN); HSSFFont font1 = wb.createFont(); font1.setFontHeightInPoints((short) 18); font1.setFontName("Cambria"); font1.setBoldweight((short) 7); font1.setColor(HSSFColor.BLACK.index); CellStyle style_border1 = wb.createCellStyle(); style_border1.setBorderBottom(HSSFCellStyle.BORDER_THIN); style_border1.setBorderTop(HSSFCellStyle.BORDER_THIN); style_border1.setBorderRight(HSSFCellStyle.BORDER_THIN); style_border1.setBorderLeft(HSSFCellStyle.BORDER_THIN); style_border1.setFont(font); style_border1.setAlignment(CellStyle.ALIGN_CENTER); style_border1.setVerticalAlignment(CellStyle.ALIGN_LEFT); HSSFRow rw1 = shet1.createRow(1); rw1.setHeightInPoints(30); for (int y = 0; y < 11; ++y) { HSSFCell cell = rw1.createCell(y); cell.setCellStyle(stylex); if (y == 0) { cell.setCellValue("OVC DOCUMENT DETAILS REPORT"); } } shet1.addMergedRegion(new CellRangeAddress(1, 1, 0, 11)); shet1.setColumnWidth(0, 9000); shet1.setColumnWidth(1, 9000); shet1.setColumnWidth(2, 9000); shet1.setColumnWidth(3, 9000); shet1.setColumnWidth(4, 5000); shet1.setColumnWidth(5, 5000); shet1.setColumnWidth(6, 5000); shet1.setColumnWidth(7, 5000); shet1.setColumnWidth(8, 5000); shet1.setColumnWidth(9, 5000); shet1.setColumnWidth(10, 5000); shet1.setColumnWidth(11, 5000); shet1.setColumnWidth(12, 5000); shet1.setColumnWidth(13, 5000); // Merge the cells // shet1.addMergedRegion(new CellRangeAddress(1,1,1,3)); HSSFRow rw4 = shet1.createRow(2); rw4.setHeightInPoints(50); HSSFRow rw6 = shet1.createRow(3); rw6.setHeightInPoints(25); // rw4.setRowStyle(style); // // // rw6.setRowStyle(style); // // rw4.createCell(1).setCellValue("Number"); HSSFCell cell1, cell0, cell2, cell3, cell4, cell5, cell6, cell7, cell8, cell9, cell10, cell11, cell12, cell13, cell14, cell15, cell16; // cells fo row 2 cell0 = rw4.createCell(0); cell1 = rw4.createCell(1); cell2 = rw4.createCell(2); cell4 = rw4.createCell(3); cell0.setCellValue("CBO"); cell0.setCellStyle(style); cell1.setCellValue("CHW"); cell1.setCellStyle(style); cell2.setCellValue("OVCID"); cell2.setCellStyle(style); cell4.setCellValue("FULLNAME"); cell4.setCellStyle(style); ArrayList docidarray = new ArrayList(); int rowcount = 3; int doccounter = 3; int doccounter1 = 4; int columcounter = 3; String cboids = ""; int mergecounter = 2; String getdocname = "select * from ovcdocuments WHERE DocumentName!=''"; conn.rs3 = conn.state3.executeQuery(getdocname); while (conn.rs3.next()) { System.out.println(conn.rs3.getString(2)); docname = conn.rs3.getString(2); cell3 = rw4.createCell(doccounter1); cell3.setCellValue(docname); cell3.setCellStyle(style); docidarray.add(conn.rs3.getString(1)); doccounter1++; } System.out.println("lll " + doccounter1); int rowcounter = 4; int counter = 0; int countercopy = 3; int countercopy1 = 3; HSSFRow rw5 = null; String getcboCOUNT = "select * from CBO where DistrictID='" + District + "' Order by DistrictID "; conn.rs2 = conn.state2.executeQuery(getcboCOUNT); while (conn.rs2.next()) { counter++; System.out.println("bb b " + conn.rs2.getString(2)); System.out.println("rowcount " + rowcount + " lll " + counter + " rowcounter " + rowcounter); System.out.println("doccount " + doccounter); String getData = "select " + "ovcfiling.value,Clientdetails.FirstName, Middlename,SurName,ovcfiling.OVCID" + " , Clientdetails.Cbo," + "ovcfiling.ovcdocid,Clientdetails.District,Clientdetails.Volunteerid " + " from ovcfiling,Clientdetails " + "WHERE Clientdetails.District ='" + District + "' and Clientdetails.Cbo='" + conn.rs2.getString(1) + "' " + "and Clientdetails.OVCID = ovcfiling.ovcid and (Year='" + Year + "' OR Year='') " + "group by Clientdetails.FirstName, Middlename,Surname,ovcfiling.OVCID,Clientdetails.Volunteerid,ovcfiling.value,ovcfiling.ovcdocid,Clientdetails.Cbo,Clientdetails.District " + "Order by Clientdetails.Volunteerid,ovcfiling.OVCID,ovcfiling.ovcdocid"; System.out.println("aaaaa " + getData); conn.rs = conn.state.executeQuery(getData); while (conn.rs.next()) { value1 = conn.rs.getInt(1); FirstName = conn.rs.getString(2); MiddleName = conn.rs.getString(3); Surname = conn.rs.getString(4); ovcid1 = conn.rs.getString(5); // String getcbo= "select * from CBO where CBOID='"+cboid+"' "; // System.out.println("aaa "+getcbo); // conn.rs_1= conn.state4.executeQuery(getcbo); // while(conn.rs_1.next()){ //// // cbocount++; // cboname=conn.rs_1.getString("CBO"); // System.out.println("aaab "+cboname); // } cboname = conn.rs2.getString(2); cboid = conn.rs.getString(6); docid = conn.rs.getString(7); String getchw = "select * from CHW where CHWID='" + conn.rs.getString(9) + "' "; conn.rs3 = conn.state3.executeQuery(getchw); while (conn.rs3.next()) { chw = conn.rs3.getString("FirstName") + " " + conn.rs3.getString("MiddleName") + " " + conn.rs3.getString("Surname") + " " + conn.rs3.getString("CBOID"); } // fro holding ovc id // to create rows if (docid.equals("1")) { rw5 = shet1.createRow(rowcount); rw5.setHeightInPoints(25); for (int i = 2; i < doccounter1; i++) { System.out.println("mm " + i); cell5 = rw5.createCell(i); cell5.setCellValue(""); cell5.setCellStyle(style_border1); } rowcount++; cbocount++; } cell1 = rw5.createCell(0); cell1.setCellValue(cboname); cell1.setCellStyle(style_border1); cell1 = rw5.createCell(1); cell1.setCellValue(chw); cell1.setCellStyle(style_border1); cell1 = rw5.createCell(2); cell1.setCellValue(ovcid1); cell1.setCellStyle(style_border1); cell1 = rw5.createCell(3); cell1.setCellValue(FirstName + " " + MiddleName + " " + Surname); cell1.setCellStyle(style_border1); for (int i = 0; i < docidarray.size(); i++) { System.out.println("hh " + docidarray.get(i)); if (rw5 == null) { rw5 = shet1.createRow(rowcount); } if (docidarray.get(i).equals(docid)) { int cellcount = i + 4; cell2 = rw5.createCell(cellcount); cell2.setCellValue(value1); cell2.setCellStyle(style_border1); // if( docid.equals("3") ) { // System.out.println("am in2"); // System.out.println("****a "+docid +" "+cellcount); // cellcount++; // } System.out.println("****b " + docid + " " + cellcount); } } // cell3=rw5.createCell(2); // cell3.setCellValue(docid); // cell3.setCellStyle(style_border1); System.out.println( FirstName + " " + ovcid1 + " " + value1 + "___" + doccounter + "_____" + rowcount); if (docid.equals("10")) { // rowcount++; doccounter = 2; } if (chwval.equals("")) { chwval = chw; System.out.println(countercopy + " nnnna " + rowcount); // shet1.addMergedRegion(new CellRangeAddress(countercopy,rowcount-1,0,0)); // countercopy=rowcount; System.out.println(countercopy + " nnnnb " + rowcount + " " + chwval); } if (!chwval.equals(chw) && !chwval.equals("")) { chwval = chw; System.out.println(countercopy + " nnna" + rowcount + " " + chwval); shet1.addMergedRegion(new CellRangeAddress(countercopy, rowcount - 2, 1, 1)); countercopy = rowcount - 1; System.out.println(countercopy + " nnnb " + rowcount + " " + chwval); } String cboval = ""; if (cboval.equals("")) { cboval = cboname; // shet1.addMergedRegion(new CellRangeAddress(countercopy1,cbocount-1,0,0)); // countercopy1=cbocount; } if (!cboval.equals(cboname) && !cboval.equals("")) { cboval = cboname; System.out.println(countercopy1 + " nnna" + cbocount + " " + chwval); shet1.addMergedRegion(new CellRangeAddress(countercopy1, cbocount - 2, 0, 0)); countercopy1 = cbocount - 1; System.out.println(countercopy1 + " nnnb " + rowcount + " " + cboval); } // // if(monthval.equals("")){ // monthval= months; // System.out.println("88"+monthval +"___"+months); // System.out.println("88"+monthcopy1); // System.out.println("88"+counter1); // cell31.setCellValue(""+months+ " ("+conn.rs3.getInt(5)+")"); // shet2.addMergedRegion(new CellRangeAddress(monthcopy_1,counter1-1,1,1)); // monthcopy1=counter1; // // } // if(!monthval.equals("") && !monthval.equals(months)){ // monthval= months; // System.out.println("!!!"+monthval +"___"+months); // System.out.println("!!!"+monthcopy_1); // System.out.println("!!!!"+counter1); //// cell31.setCellValue(months); //// shet1.addMergedRegion(new CellRangeAddress(monthcopy,counter-1,1,1)); // monthcopy_1=counter1; // // } // if(rowcount>countercopy) { // // shet1.addMergedRegion(new CellRangeAddress(countercopy,rowcount-1,0,0)); // countercopy=rowcount; // } } if (rowcount > countercopy) { shet1.addMergedRegion(new CellRangeAddress(countercopy, rowcount - 1, 1, 1)); countercopy = rowcount; } if (cbocount > countercopy1) { shet1.addMergedRegion(new CellRangeAddress(countercopy1, cbocount - 1, 0, 0)); countercopy1 = cbocount; } //} System.out.println("aaaaaa " + districtname + "__" + cboname + "____" + docname + "___" + value1 + "__" + value0); } // // end of while loop ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=CBO_Details_Report_" + Year + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } finally { // out.close(); } }
From source file:FILING.countyreport.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods./* w w w.j av a2s. c o m*/ * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { response.setContentType("text/html;charset=UTF-8"); // PrintWriter out = response.getWriter(); try { dbConn conn = new dbConn("1"); String County[]; County = request.getParameterValues("County"); String Year = ""; Year = request.getParameter("Year"); float percent = 0; ArrayList docidarray = new ArrayList(); System.out.println("countyv " + County); String FirstName = ""; String MiddleName = ""; String Surname = ""; String ovcid1 = ""; String ovcid2 = ""; int value0 = 0; int value1 = 0; String Districtid = ""; String cboid = ""; String doc = ""; String docname = ""; String cboname = ""; String districtname = ""; String countyval = ""; int countercopy = 4; int activeOVC = 0; int activeHH = 0; // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet shet1 = wb.createSheet(); //%%%%%%%%%%%%%%%%HEADER FONTS AND COLORATION%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% // style header with font color yello HSSFFont font_header = wb.createFont(); font_header.setFontHeightInPoints((short) 10); font_header.setFontName("Arial Black"); font_header.setItalic(true); font_header.setBoldweight((short) 05); font_header.setColor(HSSFColor.BLACK.index); CellStyle style_header = wb.createCellStyle(); style_header.setFont(font_header); style_header.setWrapText(true); style_header.setFillForegroundColor(HSSFColor.YELLOW.index); style_header.setBorderBottom(HSSFCellStyle.BORDER_THIN); style_header.setBorderTop(HSSFCellStyle.BORDER_THIN); style_header.setBorderRight(HSSFCellStyle.BORDER_THIN); style_header.setBorderLeft(HSSFCellStyle.BORDER_THIN); style_header.setAlignment(CellStyle.ALIGN_CENTER); style_header.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // stylex with font color blue and backgound grey HSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.PALE_BLUE.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(CellStyle.ALIGN_CENTER); // gold bg color -style HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 12); font.setFontName("Cambria"); font.setItalic(true); font.setBoldweight((short) 02); font.setColor(HSSFColor.BLACK.index); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setWrapText(true); style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); // for border with no font color CellStyle style_border = wb.createCellStyle(); style_border.setBorderBottom(HSSFCellStyle.BORDER_THIN); style_border.setBorderTop(HSSFCellStyle.BORDER_THIN); style_border.setBorderRight(HSSFCellStyle.BORDER_THIN); style_border.setBorderLeft(HSSFCellStyle.BORDER_THIN); HSSFFont font1 = wb.createFont(); font1.setFontHeightInPoints((short) 18); font1.setFontName("Cambria"); font1.setBoldweight((short) 7); font1.setColor(HSSFColor.BLACK.index); CellStyle style_border1 = wb.createCellStyle(); style_border1.setBorderBottom(HSSFCellStyle.BORDER_THIN); style_border1.setBorderTop(HSSFCellStyle.BORDER_THIN); style_border1.setBorderRight(HSSFCellStyle.BORDER_THIN); style_border1.setBorderLeft(HSSFCellStyle.BORDER_THIN); style_border1.setFont(font); style_border1.setAlignment(CellStyle.ALIGN_JUSTIFY); style_border1.setVerticalAlignment(CellStyle.ALIGN_LEFT); HSSFRow rw1 = shet1.createRow(1); //cell; rw1.setHeightInPoints(30); for (int y = 0; y < 11; ++y) { HSSFCell cell = rw1.createCell(y); cell.setCellStyle(stylex); if (y == 0) { cell.setCellValue("COUNTY FILING TRACKER REPORT " + Year); } } shet1.addMergedRegion(new CellRangeAddress(1, 1, 0, 11)); shet1.setColumnWidth(0, 4500); shet1.setColumnWidth(1, 8500); shet1.setColumnWidth(2, 5000); shet1.setColumnWidth(3, 5000); shet1.setColumnWidth(4, 5000); shet1.setColumnWidth(5, 5000); shet1.setColumnWidth(6, 5000); shet1.setColumnWidth(7, 5000); shet1.setColumnWidth(8, 5000); shet1.setColumnWidth(9, 5000); shet1.setColumnWidth(10, 5000); shet1.setColumnWidth(11, 5000); shet1.setColumnWidth(12, 5000); shet1.setColumnWidth(13, 5000); shet1.setColumnWidth(14, 5000); shet1.setColumnWidth(15, 5000); shet1.setColumnWidth(16, 5000); shet1.setColumnWidth(17, 5000); shet1.setColumnWidth(18, 5000); shet1.setColumnWidth(19, 5000); shet1.setColumnWidth(20, 4500); shet1.setColumnWidth(21, 4500); shet1.setColumnWidth(22, 3500); // Merge the cells // shet1.addMergedRegion(new CellRangeAddress(1,1,1,3)); HSSFRow rw4 = shet1.createRow(2); rw4.setHeightInPoints(40); HSSFRow rw6 = shet1.createRow(3); rw6.setHeightInPoints(25); // rw4.setRowStyle(style); // // // rw6.setRowStyle(style); // // rw4.createCell(1).setCellValue("Number"); HSSFCell cell1, cell2, cell3, cell4, cell5, cell6, cell7, cell8, cell9, cell10, cell11, cell12, cell13, cell14, cell15, cell16; // cells fo row 2 cell1 = rw4.createCell(0); cell2 = rw4.createCell(1); cell4 = rw4.createCell(3); cell1.setCellValue("COUNTY"); cell1.setCellStyle(style); cell2.setCellValue("DISTRICT"); cell2.setCellStyle(style); cell6 = rw6.createCell(0); cell6.setCellValue("Status"); cell6.setCellStyle(style); cell7 = rw6.createCell(1); cell7.setCellValue(""); cell7.setCellStyle(style_header); cell3 = rw4.createCell(2); cell3.setCellValue("ACTIVE OVC"); cell3.setCellStyle(style); cell3 = rw4.createCell(3); cell3.setCellValue("ACTIVE HH"); cell3.setCellStyle(style); shet1.addMergedRegion(new CellRangeAddress(3, 3, 0, 1)); int rowcount = 3; int doccounter = 4; int doccounter1 = 4; int columcounter = 3; String cboids = ""; int mergecounter = 2; cell6 = rw6.createCell(2); cell6.setCellValue(""); cell6.setCellStyle(stylex); cell6 = rw6.createCell(3); cell6.setCellValue(""); cell6.setCellStyle(stylex); String getdocname = "select * from ovcdocuments where DocumentName!=''"; conn.rs3 = conn.state3.executeQuery(getdocname); while (conn.rs3.next()) { System.out.println("mmm " + doccounter1); System.out.println(conn.rs3.getString(2)); docname = conn.rs3.getString(2); cell3 = rw4.createCell(doccounter1); cell3.setCellValue(docname); cell3.setCellStyle(style); docidarray.add(conn.rs3.getString(1)); cell6 = rw6.createCell(doccounter1++); cell6.setCellValue("Available"); cell6.setCellStyle(stylex); // cell5=rw6.createCell(doccounter1); // cell5.setCellValue("Not Available"); // cell5.setCellStyle(stylex); // doccounter1++; // System.out.println("mergecounter b4"+mergecounter); // System.out.println("mergecounter after"+mergecounter); // mergecounter++; //shet1.addMergedRegion(new CellRangeAddress(2,2,doccounter1,doccounter1++)); } System.out.println("lll " + doccounter1); int rowcounter = 4; int counter = 0; HSSFRow rw5 = null; String DistrictID = ""; for (int j = 0; j < County.length; j++) { String getDistrictCounts = "select * from District where Countyid='" + County[j] + "' order by District"; System.out.println("districtID " + getDistrictCounts); conn.rs2 = conn.state2.executeQuery(getDistrictCounts); while (conn.rs2.next()) { System.out.println("district1" + conn.rs2.getString("District")); counter++; rowcount++; rw5 = shet1.createRow(rowcount); for (int i = 2; i < doccounter1; i++) { System.out.println("mm " + i); cell5 = rw5.createCell(i); cell5.setCellValue(""); cell5.setCellStyle(style_border1); } cell2 = rw5.createCell(1); cell2.setCellValue(conn.rs2.getString(2)); cell2.setCellStyle(style_border); // cell5=rw5.createCell(rowcount); // cell6=rw5.createCell(rowcounter++); // cell5.setCellValue("x"); // cell6.setCellValue("y"); System.out.println("rowcount " + rowcount + " lll " + counter + " rowcounter " + rowcounter); rw5.setHeightInPoints(25); // cell3=rw5.createCell(2); String getDistrict = "select * from County where CountyID='" + County[j] + "' order by County"; System.out.println("dname " + getDistrict); conn.rs4 = conn.state4.executeQuery(getDistrict); while (conn.rs4.next()) { districtname = conn.rs4.getString("County"); System.out.println("district2" + districtname); cell1 = rw5.createCell(0); cell1.setCellValue(districtname); cell1.setCellStyle(style_border1); // to marge these values } System.out.println("doccount " + doccounter); doccounter = 4; String getData = "select " + "SUM(CASE WHEN ovcfiling.value='1' THEN 1 ELSE 0 END) AS COUNT1, " + "SUM(CASE WHEN ovcfiling.value='0' THEN 1 ELSE 0 END) AS COUNT0," + "Clientdetails.Cbo," + "ovcfiling.ovcdocid,Clientdetails.District, " + "count(Clientdetails.OVCID)," + "count(DISTINCT(Clientdetails.HouseHoldheadID))" + " from ovcfiling,Clientdetails " + "WHERE Clientdetails.District ='" + conn.rs2.getString("DistrictID") + "' and Clientdetails.OVCID = ovcfiling.ovcid and Clientdetails.Exited='1' and (Year='" + Year + "' OR Year='') " + "group by ovcfiling.ovcdocid,Clientdetails.Cbo,Clientdetails.District order by Clientdetails.District,ovcfiling.ovcdocid"; System.out.println("aaaaa " + getData); conn.rs = conn.state.executeQuery(getData); while (conn.rs.next()) { value1 = conn.rs.getInt(1); value0 = conn.rs.getInt(2); cboid = conn.rs.getString(3); doc = conn.rs.getString(4); Districtid = conn.rs.getString(5); activeOVC = conn.rs.getInt(6); activeHH = conn.rs.getInt(7); System.out.println("district3 " + Districtid); float activeovc = 0; float activehh = 0; for (int i = 0; i < docidarray.size(); i++) { System.out.println("hh " + docidarray.get(i) + " " + doc); // if(rw5==null){ // rw5=shet1.createRow(rowcount); // } if (docidarray.get(i).equals(doc)) { System.out.println(doc + "lll" + docidarray.get(i)); int cellcount = i + 2; // cell2=rw5.createCell(cellcount++); // cell2.setCellValue(value1); // cell2.setCellStyle(style_border1); // String getdocname1="select * from ovcdocuments"; // conn.rs3= conn.state3.executeQuery(getdocname1); // if(conn.rs3.next()){ cell7 = rw5.createCell(2); cell8 = rw5.createCell(3); cell5 = rw5.createCell(doccounter); // cell6=rw5.createCell(doccounter); activeovc = activeOVC; activehh = activeHH; if (doc.equals("8")) { percent = value1 / activehh * 100; } else { percent = value1 / activeovc * 100; } System.out.println("percenta " + percent + " " + value1 + " " + activeOVC + " act " + activeovc); cell5.setCellValue(Math.round(percent) + "%"); // cell6.setCellValue(value0); //FOR ACTICE OVCs cell7.setCellValue(activeOVC); cell7.setCellStyle(style_border); //FOR ACTICE hhs cell8.setCellValue(activeHH); cell8.setCellStyle(style_border); cell5.setCellStyle(style_border); cell6.setCellStyle(style_border); System.out.println("****a " + doc + " " + doccounter); if (doc.equals("2")) { // System.out.println("****i "+doc +" "+doccounter); doccounter++; // doccounter++; System.out.println("am in2"); System.out.println("****b " + doc + " " + doccounter); } if (doc.equals("3")) { percent = value1 / activeovc * 100; doccounter--; // doccounter--; // doccounter--; System.out.println("****f " + doc + " " + doccounter); cell5 = rw5.createCell(doccounter++); cell5.setCellStyle(style_border); cell5.setCellValue(percent); cell6 = rw5.createCell(doccounter); // cell6.setCellStyle(style_border); // cell6.setCellValue(value0); System.out.println("****b " + doc + " " + doccounter); doccounter--; } // else if(!doc.equals("4") && !docidarray.get(i).equals("4")){ // doccounter++; // doccounter++; // System.out.println("****c "+doc +" "+doccounter); // } doccounter++; } } } doccounter = 2; // String getcbo= "select * from CBO where cboid='"+cboid+"'"; // conn.rs2 = conn.state2.executeQuery(getcbo); // while(conn.rs2.next()){ // // cell2=rw5.createCell(1); // cell2.setCellValue(conn.rs2.getString(2)); // // System.out.println("rowcount "+rowcount ); // // } //} System.out.println("aaaaaa " + districtname + "__" + cboname + "____" + docname + "___" + value1 + "__" + value0); // shet1.addMergedRegion(new CellRangeAddress(countercopy,counter,0,0)); // countercopy=counter; // cell1.setCellValue(districtname); } if (countyval.equals("")) { // totalvalue= countercopy+counter; System.out.println(countercopy + " counter " + counter + " " + rowcount); countyval = districtname; System.out.println(countercopy + " nnnn " + counter + " " + rowcount); // if(counter>countercopy){ shet1.addMergedRegion(new CellRangeAddress(countercopy, rowcount, 0, 0)); countercopy = rowcount; //cell1.setCellValue(districtname); // } System.out.println(countercopy + " nnn " + counter + " " + rowcount + " " + countyval); } // cell1.setCellValue(districtname); if (!countyval.equals(districtname) && !countyval.equals("")) { countyval = districtname; // cell1.setCellValue(districtname); shet1.addMergedRegion(new CellRangeAddress(countercopy + 1, rowcount, 0, 0)); countercopy = rowcount; // System.out.println(counter + "@@@@1 " + rowcount + "__" + countercopy); System.out.println(countyval + "@@@@1 " + districtname); } System.out.println(counter + "@@@@ " + rowcount); //shet1.addMergedRegion(new CellRangeAddress(counter,rowcount,0,0)); System.out.println(countyval + "@@@@ " + districtname); } System.out.println(counter + "@@@@2 " + rowcount + " copy "); // System.out.println("aaaaaannnn "+districtname+"__"+ cboname +"____"+ doc +"___"+value1 +"__"+value0 ); // write it as an excel attachment ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=County_Filing_Tracker_Report_" + Year + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } finally { // out.close(); } }
From source file:fr.univlorraine.mondossierweb.controllers.ListeInscritsController.java
License:Apache License
/** * crer le fichier excel partir de la liste des inscrits. * @return le fichier excel de la liste des inscrits. *///from w w w . jav a2 s. c om @SuppressWarnings("deprecation") public HSSFWorkbook creerExcel(List<Inscrit> listeInscrits, List<String> listeCodInd, ComboBox listeGroupes, boolean isTraiteEtape) { // creation du fichier excel HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("page1"); boolean isSession1 = true; boolean isSession2 = true; //formatage de la taille des colonne sheet.setColumnWidth((short) 0, (short) (4000)); sheet.setColumnWidth((short) 1, (short) (6000)); sheet.setColumnWidth((short) 2, (short) (5120)); sheet.setColumnWidth((short) 3, (short) (4000)); sheet.setColumnWidth((short) 4, (short) (8000)); if (isTraiteEtape) { sheet.setColumnWidth((short) 5, (short) (1200)); sheet.setColumnWidth((short) 6, (short) (2000)); sheet.setColumnWidth((short) 7, (short) (3000)); sheet.setColumnWidth((short) 8, (short) (2000)); sheet.setColumnWidth((short) 9, (short) (3000)); } else { sheet.setColumnWidth((short) 5, (short) (2000)); sheet.setColumnWidth((short) 6, (short) (3000)); sheet.setColumnWidth((short) 7, (short) (8000)); sheet.setColumnWidth((short) 8, (short) (2000)); sheet.setColumnWidth((short) 9, (short) (3000)); sheet.setColumnWidth((short) 10, (short) (2000)); sheet.setColumnWidth((short) 11, (short) (3000)); sheet.setColumnWidth((short) 12, (short) (8000)); } // Creation des lignes HSSFRow row = sheet.createRow((short) 0); //CREATION DES STYLES: //STYLE1: HSSFCellStyle headerStyle = wb.createCellStyle(); headerStyle.setFillBackgroundColor(HSSFColor.BLUE.index); headerStyle.setFillForegroundColor(HSSFColor.BLUE.index); headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont font = wb.createFont(); font.setColor(HSSFColor.WHITE.index); font.setBoldweight((short) 10); headerStyle.setFont(font); //bordure style1 headerStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); headerStyle.setBottomBorderColor(HSSFColor.BLACK.index); headerStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); headerStyle.setLeftBorderColor(HSSFColor.BLACK.index); headerStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); headerStyle.setRightBorderColor(HSSFColor.BLACK.index); headerStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); headerStyle.setTopBorderColor(HSSFColor.BLACK.index); int rang_cellule = 0; HSSFCell cellLib1 = row.createCell((short) rang_cellule); cellLib1.setCellStyle(headerStyle); cellLib1.setCellValue(applicationContext.getMessage("xls.folder", null, Locale.getDefault()).toUpperCase()); rang_cellule++; HSSFCell cellLib2 = row.createCell((short) rang_cellule); cellLib2.setCellStyle(headerStyle); cellLib2.setCellValue(applicationContext.getMessage("xls.nom", null, Locale.getDefault()).toUpperCase()); rang_cellule++; HSSFCell cellLib3 = row.createCell((short) rang_cellule); cellLib3.setCellStyle(headerStyle); cellLib3.setCellValue(applicationContext.getMessage("xls.prenom", null, Locale.getDefault()).toUpperCase()); rang_cellule++; HSSFCell cellLib4 = row.createCell((short) rang_cellule); cellLib4.setCellStyle(headerStyle); cellLib4.setCellValue( applicationContext.getMessage("xls.naissance", null, Locale.getDefault()).toUpperCase()); rang_cellule++; HSSFCell cellLib5 = row.createCell((short) rang_cellule); cellLib5.setCellStyle(headerStyle); cellLib5.setCellValue( applicationContext.getMessage("xls.messagerie", null, Locale.getDefault()).toUpperCase()); rang_cellule++; if (isTraiteEtape) { HSSFCell cellLib6 = row.createCell((short) rang_cellule); cellLib6.setCellStyle(headerStyle); cellLib6.setCellValue( applicationContext.getMessage("xls.iae", null, Locale.getDefault()).toUpperCase() + "?"); rang_cellule++; } if (!isTraiteEtape) { HSSFCell cellLib7 = row.createCell((short) rang_cellule); cellLib7.setCellStyle(headerStyle); cellLib7.setCellValue( applicationContext.getMessage("xls.code", null, Locale.getDefault()).toUpperCase()); rang_cellule++; HSSFCell cellLib8 = row.createCell((short) rang_cellule); cellLib8.setCellStyle(headerStyle); cellLib8.setCellValue( applicationContext.getMessage("xls.version", null, Locale.getDefault()).toUpperCase()); rang_cellule++; HSSFCell cellLib9 = row.createCell((short) rang_cellule); cellLib9.setCellStyle(headerStyle); cellLib9.setCellValue( applicationContext.getMessage("xls.etape", null, Locale.getDefault()).toUpperCase()); rang_cellule++; } if (isSession1) { HSSFCell cellLib10 = row.createCell((short) rang_cellule); cellLib10.setCellStyle(headerStyle); cellLib10.setCellValue( applicationContext.getMessage("xls.note1", null, Locale.getDefault()).toUpperCase()); rang_cellule++; HSSFCell cellLib11 = row.createCell((short) rang_cellule); cellLib11.setCellStyle(headerStyle); cellLib11.setCellValue( applicationContext.getMessage("xls.result1", null, Locale.getDefault()).toUpperCase()); rang_cellule++; } if (isSession2) { HSSFCell cellLib12 = row.createCell((short) rang_cellule); cellLib12.setCellStyle(headerStyle); cellLib12.setCellValue( applicationContext.getMessage("xls.note2", null, Locale.getDefault()).toUpperCase()); rang_cellule++; HSSFCell cellLib13 = row.createCell((short) rang_cellule); cellLib13.setCellStyle(headerStyle); cellLib13.setCellValue( applicationContext.getMessage("xls.result2", null, Locale.getDefault()).toUpperCase()); rang_cellule++; } //info sur les groupes if (!isTraiteEtape) { HSSFCell cellLib14 = row.createCell((short) rang_cellule); cellLib14.setCellStyle(headerStyle); cellLib14.setCellValue( applicationContext.getMessage("xls.groupes", null, Locale.getDefault()).toUpperCase()); rang_cellule++; } int nbrow = 1; for (Inscrit inscrit : listeInscrits) { if (listeCodInd.contains(inscrit.getCod_ind())) { HSSFRow rowInscrit = sheet.createRow((short) nbrow); int rang_cellule_inscrit = 0; HSSFCell cellLibInscrit1 = rowInscrit.createCell((short) rang_cellule_inscrit); cellLibInscrit1.setCellValue(inscrit.getCod_etu()); rang_cellule_inscrit++; HSSFCell cellLibInscrit2 = rowInscrit.createCell((short) rang_cellule_inscrit); cellLibInscrit2.setCellValue(inscrit.getNom()); rang_cellule_inscrit++; HSSFCell cellLibInscrit3 = rowInscrit.createCell((short) rang_cellule_inscrit); cellLibInscrit3.setCellValue(inscrit.getPrenom()); rang_cellule_inscrit++; HSSFCell cellLibInscrit31 = rowInscrit.createCell((short) rang_cellule_inscrit); cellLibInscrit31.setCellValue(inscrit.getDate_nai_ind()); rang_cellule_inscrit++; HSSFCell cellLibInscrit4 = rowInscrit.createCell((short) rang_cellule_inscrit); cellLibInscrit4.setCellValue(inscrit.getEmail()); rang_cellule_inscrit++; if (isTraiteEtape) { HSSFCell cellLibInscrit5 = rowInscrit.createCell((short) rang_cellule_inscrit); cellLibInscrit5.setCellValue(inscrit.getIae()); rang_cellule_inscrit++; } if (!isTraiteEtape) { //if (isEtape) { HSSFCell cellLibInscrit6 = rowInscrit.createCell((short) rang_cellule_inscrit); cellLibInscrit6.setCellValue(inscrit.getCod_etp()); rang_cellule_inscrit++; HSSFCell cellLibInscrit7 = rowInscrit.createCell((short) rang_cellule_inscrit); cellLibInscrit7.setCellValue(inscrit.getCod_vrs_vet()); rang_cellule_inscrit++; HSSFCell cellLibInscrit8 = rowInscrit.createCell((short) rang_cellule_inscrit); cellLibInscrit8.setCellValue(inscrit.getLib_etp()); rang_cellule_inscrit++; //} } if (isSession1) { HSSFCell cellLibInscrit9 = rowInscrit.createCell((short) rang_cellule_inscrit); cellLibInscrit9.setCellValue(inscrit.getNotej()); rang_cellule_inscrit++; HSSFCell cellLibInscrit10 = rowInscrit.createCell((short) rang_cellule_inscrit); cellLibInscrit10.setCellValue(inscrit.getResj()); rang_cellule_inscrit++; } if (isSession2) { HSSFCell cellLibInscrit11 = rowInscrit.createCell((short) rang_cellule_inscrit); cellLibInscrit11.setCellValue(inscrit.getNotes()); rang_cellule_inscrit++; HSSFCell cellLibInscrit12 = rowInscrit.createCell((short) rang_cellule_inscrit); cellLibInscrit12.setCellValue(inscrit.getRess()); rang_cellule_inscrit++; } //ajout info sur les groupes si il y a lieu if (!isTraiteEtape) { HSSFCell cellLibGroupes = rowInscrit.createCell((short) rang_cellule_inscrit); String grpXls = ""; List<String> lcodegroup = Utils.splitStringFromSemiColon(inscrit.getCodes_groupes()); for (String codegroupe : lcodegroup) { if (StringUtils.hasText(grpXls)) { grpXls += ", "; } grpXls += listeGroupes.getItemCaption(codegroupe); } cellLibGroupes.setCellValue(grpXls); rang_cellule_inscrit++; } nbrow++; } } return wb; }
From source file:gda.hrpd.data.HSSF.java
License:Apache License
/** * Constructor HSSF - given a filename this outputs a sample sheet with just a set of rows/cells. * * @param filename/* ww w . j ava 2 s .co m*/ * @param write * @exception IOException */ public HSSF(String filename, @SuppressWarnings("unused") boolean write) throws IOException { short rownum = 0; FileOutputStream out = new FileOutputStream(filename); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet(); HSSFRow r; HSSFCell c = null; HSSFCellStyle cs = wb.createCellStyle(); HSSFCellStyle cs2 = wb.createCellStyle(); HSSFCellStyle cs3 = wb.createCellStyle(); HSSFFont f = wb.createFont(); HSSFFont f2 = wb.createFont(); f.setFontHeightInPoints((short) 12); f.setColor((short) 0xA); f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); f2.setFontHeightInPoints((short) 10); f2.setColor((short) 0xf); f2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cs.setFont(f); cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)")); cs2.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs2.setFillPattern((short) 1); // fill w fg cs2.setFillForegroundColor((short) 0xA); cs2.setFont(f2); wb.setSheetName(0, "HSSF Test"); for (rownum = (short) 0; rownum < 300; rownum++) { r = s.createRow(rownum); if ((rownum % 2) == 0) { r.setHeight((short) 0x249); } // r.setRowNum(( short ) rownum); for (short cellnum = (short) 0; cellnum < 50; cellnum += 2) { c = r.createCell(cellnum, HSSFCell.CELL_TYPE_NUMERIC); c.setCellValue(rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000))); if ((rownum % 2) == 0) { c.setCellStyle(cs); } c = r.createCell((short) (cellnum + 1), HSSFCell.CELL_TYPE_STRING); c.setCellValue(new HSSFRichTextString("TEST")); s.setColumnWidth((short) (cellnum + 1), (short) ((50 * 8) / ((double) 1 / 20))); if ((rownum % 2) == 0) { c.setCellStyle(cs2); } } // 50 characters divided by 1/20th of a point } // draw a thick black border on the row at the bottom using BLANKS rownum++; rownum++; r = s.createRow(rownum); cs3.setBorderBottom(HSSFCellStyle.BORDER_THICK); for (short cellnum = (short) 0; cellnum < 50; cellnum++) { c = r.createCell(cellnum, HSSFCell.CELL_TYPE_BLANK); // c.setCellValue(0); c.setCellStyle(cs3); } s.addMergedRegion(new Region((short) 0, (short) 0, (short) 3, (short) 3)); s.addMergedRegion(new Region((short) 100, (short) 100, (short) 110, (short) 110)); // end draw thick black border // create a sheet, set its title then delete it s = wb.createSheet(); wb.setSheetName(1, "DeletedSheet"); wb.removeSheetAt(1); // end deleted sheet wb.write(out); out.close(); }
From source file:gov.guilin.ExcelView.java
License:Open Source License
/** * ?Excel//w ww . ja va2s. com * * @param model * ? * @param workbook * workbook * @param request * request * @param response * response */ public void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { Assert.notEmpty(properties); HSSFSheet sheet; if (StringUtils.isNotEmpty(sheetName)) { sheet = workbook.createSheet(sheetName); } else { sheet = workbook.createSheet(); } int rowNumber = 0; if (titles != null && titles.length > 0) { HSSFRow header = sheet.createRow(rowNumber); header.setHeight((short) 400); for (int i = 0; i < properties.length; i++) { HSSFCell cell = header.createCell(i); HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 11); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyle.setFont(font); cell.setCellStyle(cellStyle); if (i == 0) { HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); HSSFComment comment = patriarch .createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 1, 1, (short) 4, 4)); comment.setString(new HSSFRichTextString("P" + "o" + "w" + "e" + "r" + "e" + "d" + " " + "B" + "y" + " " + "S" + "H" + "O" + "P" + "+" + "+")); cell.setCellComment(comment); } if (titles.length > i && titles[i] != null) { cell.setCellValue(titles[i]); } else { cell.setCellValue(properties[i]); } if (widths != null && widths.length > i && widths[i] != null) { sheet.setColumnWidth(i, widths[i]); } else { sheet.autoSizeColumn(i); } } rowNumber++; } if (data != null) { for (Object item : data) { HSSFRow row = sheet.createRow(rowNumber); for (int i = 0; i < properties.length; i++) { HSSFCell cell = row.createCell(i); if (converters != null && converters.length > i && converters[i] != null) { Class<?> clazz = PropertyUtils.getPropertyType(item, properties[i]); ConvertUtils.register(converters[i], clazz); cell.setCellValue(BeanUtils.getProperty(item, properties[i])); ConvertUtils.deregister(clazz); if (clazz.equals(Date.class)) { DateConverter dateConverter = new DateConverter(); dateConverter.setPattern(DEFAULT_DATE_PATTERN); ConvertUtils.register(dateConverter, Date.class); } } else { cell.setCellValue(BeanUtils.getProperty(item, properties[i])); } if (rowNumber == 0 || rowNumber == 1) { if (widths != null && widths.length > i && widths[i] != null) { sheet.setColumnWidth(i, widths[i]); } else { sheet.autoSizeColumn(i); } } } rowNumber++; } } if (contents != null && contents.length > 0) { rowNumber++; for (String content : contents) { HSSFRow row = sheet.createRow(rowNumber); HSSFCell cell = row.createCell(0); HSSFCellStyle cellStyle = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); font.setColor(HSSFColor.GREY_50_PERCENT.index); cellStyle.setFont(font); cell.setCellStyle(cellStyle); cell.setCellValue(content); rowNumber++; } } response.setContentType("application/force-download"); if (StringUtils.isNotEmpty(filename)) { response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(filename, "UTF-8")); } else { response.setHeader("Content-disposition", "attachment"); } }