List of usage examples for org.apache.poi.ss.usermodel CellStyle setFont
void setFont(Font font);
From source file:ke.co.mspace.nonsmppmanager.service.SMSOutServiceImpl.java
private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<>(); CellStyle style; Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 18); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle();//from w w w . j a va 2s .c o m style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFont(titleFont); styles.put("title", style); Font monthFont = wb.createFont(); monthFont.setFontHeightInPoints((short) 11); monthFont.setColor(IndexedColors.WHITE.getIndex()); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(monthFont); style.setWrapText(true); styles.put("header", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setWrapText(true); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styles.put("cell", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(wb.createDataFormat().getFormat("0.00")); styles.put("formula", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(wb.createDataFormat().getFormat("0.00")); styles.put("formula_2", style); return styles; }
From source file:ke.co.tawi.babblesms.server.utils.export.topups.AllTopupsExportUtil.java
License:Open Source License
/** * Cell styles used for formatting the sheets * * @param wb/*ww w. j ava2s . co m*/ * @return Map<String, {@link CellStyle}> */ public static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<>(); CellStyle style; Font headerFont = wb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(headerFont); styles.put("header", style); Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 48); titleFont.setColor(IndexedColors.DARK_BLUE.getIndex()); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFont(titleFont); styles.put("title", style); return styles; }
From source file:kp.servlet.ExportRpt.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods./*from w ww. j av a 2s . 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 { response.setContentType("text/html;charset=UTF-8"); Logger.getLogger(ExportRpt.class.getName()).log(Level.SEVERE, "accRole :" + request.getParameter("accRole")); Logger.getLogger(ExportRpt.class.getName()).log(Level.SEVERE, "Unit :" + request.getParameter("unit")); ArrayList<MocWfTran> Mocstatus = new ArrayList<>(); TranDao tdao = new TranDao(); Mocstatus = tdao.getMocStatusList(request.getParameter("accRole"), request.getParameter("unit"), request.getParameter("user")); //Developing Metadata String rptName = "MOC Status Excel Report"; ArrayList<String> colLabel = new ArrayList<>(); colLabel.add("Case Id"); colLabel.add("Moc NO"); colLabel.add("Moc Title"); colLabel.add("Moc Status"); colLabel.add("Creation Date"); colLabel.add("Owner's Name"); colLabel.add("Unit"); colLabel.add("Plant"); colLabel.add("Current Stage"); colLabel.add("Pending At"); //Starting EXCEL Creating //XLS Variable XSSFSheet spreadsheet; XSSFWorkbook workbook; XSSFRow row; XSSFCell cell; XSSFFont xfont = null; XSSFCellStyle xstyle = null; //2.Create WorkBook and Sheet workbook = new XSSFWorkbook(); spreadsheet = workbook.createSheet(rptName); //set header style xfont = workbook.createFont(); xfont.setFontHeight(11); xfont.setFontName("Calibri"); xfont.setBold(true); //Set font into style CellStyle borderStyle = workbook.createCellStyle(); borderStyle.setAlignment(CellStyle.ALIGN_CENTER); borderStyle.setFont(xfont); xstyle = workbook.createCellStyle(); xstyle.setFont(xfont); //header row = spreadsheet.createRow(0); cell = row.createCell(0); cell.setCellValue(rptName); cell.setCellStyle(borderStyle); spreadsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, colLabel.size() - 1)); //3.Get First Row and Set Headers row = spreadsheet.createRow(1); for (int i = 0; i < colLabel.size(); i++) { cell = row.createCell(i); cell.setCellValue(colLabel.get(i)); cell.setCellStyle(xstyle); } //Itrate or Database data and write int i = 2; for (MocWfTran bean : Mocstatus) { row = spreadsheet.createRow(i); cell = row.createCell(0); cell.setCellValue(bean.getCaseId()); cell = row.createCell(1); cell.setCellValue(bean.getMocNo()); cell = row.createCell(2); cell.setCellValue(bean.getCaseName()); cell = row.createCell(3); cell.setCellValue(bean.getMocStatus()); cell = row.createCell(4); cell.setCellValue(bean.getCrDateString()); cell = row.createCell(5); cell.setCellValue(bean.getCaseOwnerName()); cell = row.createCell(6); cell.setCellValue(bean.getUnitId()); cell = row.createCell(7); cell.setCellValue(bean.getPlantId()); cell = row.createCell(8); cell.setCellValue(bean.getStgNname()); cell = row.createCell(9); cell.setCellValue(bean.getUserNname()); i++; } //Export to Excel String file_name = "MocStatus"; String path = getServletContext().getRealPath("/"); String full_path = path + "/report/" + file_name + ".xlsx"; // FileOutputStream out = new FileOutputStream(new File("D://" + file_name + ".xlsx")); FileOutputStream out = new FileOutputStream(new File(full_path)); workbook.write(out); //Download code // reads input file from an absolute path File downloadFile = new File(full_path); OutputStream outStream; // obtains ServletContext try (FileInputStream inStream = new FileInputStream(downloadFile)) { //obtains ServletContext ServletContext context = getServletContext(); // gets MIME type of the file String mimeType = context.getMimeType(full_path); if (mimeType == null) { // set to binary type if MIME mapping not found mimeType = "application/octet-stream"; } // modifies response response.setContentType(mimeType); response.setContentLength((int) downloadFile.length()); // forces download String headerKey = "Content-Disposition"; String headerValue = String.format("attachment; filename=\"%s\"", downloadFile.getName()); response.setHeader(headerKey, headerValue); // obtains response's output stream outStream = response.getOutputStream(); byte[] buffer = new byte[4096]; int bytesRead = -1; while ((bytesRead = inStream.read(buffer)) != -1) { outStream.write(buffer, 0, bytesRead); } } outStream.close(); // response.sendRedirect("mocstatus.jsp"); }
From source file:Logic.RStoXL.java
public void genXLS(ResultSet rs, String Rpt_name, String path) { try {//ww w. j a v a 2s .co m //RS METE DATA ResultSetMetaData rsmd = rs.getMetaData(); int col_count = rsmd.getColumnCount(); ArrayList<String> col_name = new ArrayList<String>(); for (int i = 1; i <= col_count; i++) { col_name.add(rsmd.getColumnLabel(i)); } //XLS Variable XSSFSheet spreadsheet; XSSFWorkbook workbook; XSSFRow row; XSSFCell cell; XSSFFont xfont = null; XSSFCellStyle xstyle = null; //2.Create WorkBook and Sheet workbook = new XSSFWorkbook(); spreadsheet = workbook.createSheet(Rpt_name); //set header style xfont = workbook.createFont(); xfont.setFontHeight(11); xfont.setFontName("Calibri"); xfont.setBold(true); //Set font into style CellStyle borderStyle = workbook.createCellStyle(); borderStyle.setAlignment(CellStyle.ALIGN_CENTER); borderStyle.setFont(xfont); xstyle = workbook.createCellStyle(); xstyle.setFont(xfont); //header row = spreadsheet.createRow(0); cell = row.createCell(0); cell.setCellValue(Rpt_name); cell.setCellStyle(borderStyle); spreadsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col_count - 1)); //3.Get First Row and Set Headers row = spreadsheet.createRow(1); for (int i = 0; i < col_count; i++) { cell = row.createCell(i); cell.setCellValue(col_name.get(i)); cell.setCellStyle(xstyle); } //Itrate or Database data and write int i = 2; while (rs.next()) { row = spreadsheet.createRow(i); for (int j = 1; j <= col_count; j++) { cell = row.createCell(j - 1); cell.setCellValue(rs.getString(j)); } i++; } //Export to Excel // FileOutputStream out = new FileOutputStream(new File("D://" + Rpt_name + ".xlsx")); FileOutputStream out = new FileOutputStream(new File(path)); workbook.write(out); Logger.getLogger(RStoXL.class.getName()).log(Level.SEVERE, "DONE|!"); Logger.getLogger(RStoXL.class.getName()).log(Level.SEVERE, ""); } catch (SQLException ex) { Logger.getLogger(RStoXL.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } catch (IOException ex) { Logger.getLogger(RStoXL.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } }
From source file:Logic.Xls.java
public void genXLS(ResultSet rs, String Rpt_name, String path) { try {/*from w w w .java 2 s. c om*/ //RS METE DATA ResultSetMetaData rsmd = rs.getMetaData(); int col_count = rsmd.getColumnCount(); ArrayList<String> col_name = new ArrayList<>(); for (int i = 1; i <= col_count; i++) { col_name.add(rsmd.getColumnLabel(i)); } //XLS Variable XSSFSheet spreadsheet; XSSFWorkbook workbook; XSSFRow row; XSSFCell cell; XSSFFont xfont = null; XSSFCellStyle xstyle = null; //2.Create WorkBook and Sheet workbook = new XSSFWorkbook(); spreadsheet = workbook.createSheet(Rpt_name); //set header style xfont = workbook.createFont(); xfont.setFontHeight(11); xfont.setFontName("Calibri"); xfont.setBold(true); //Set font into style CellStyle borderStyle = workbook.createCellStyle(); borderStyle.setAlignment(CellStyle.ALIGN_CENTER); borderStyle.setFont(xfont); xstyle = workbook.createCellStyle(); xstyle.setFont(xfont); //header row = spreadsheet.createRow(0); cell = row.createCell(0); cell.setCellValue(Rpt_name); cell.setCellStyle(borderStyle); spreadsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col_count - 1)); //3.Get First Row and Set Headers row = spreadsheet.createRow(1); for (int i = 0; i < col_count; i++) { cell = row.createCell(i); cell.setCellValue(col_name.get(i)); cell.setCellStyle(xstyle); } //Itrate or Database data and write int i = 2; while (rs.next()) { row = spreadsheet.createRow(i); for (int j = 1; j <= col_count; j++) { cell = row.createCell(j - 1); cell.setCellValue(rs.getString(j)); } i++; } //Export to Excel // FileOutputStream out = new FileOutputStream(new File("D://" + Rpt_name + ".xlsx")); FileOutputStream out = new FileOutputStream(new File(path)); workbook.write(out); Logger.getLogger(Xls.class.getName()).log(Level.SEVERE, "DONE|!"); Logger.getLogger(Xls.class.getName()).log(Level.SEVERE, ""); } catch (Exception ex) { Logger.getLogger(Xls.class.getName()).log(Level.SEVERE, "Exception : " + ex); } }
From source file:main.ExcelUtils.java
/** * Devuelve el CellStyle indicado para las celdas de cabecera de la tabla * @return //from w w w . j ava2 s . co m */ public CellStyle GetTitleStyle() { CellStyle title = wb.createCellStyle(); Font fuenteTitle = wb.createFont(); fuenteTitle.setBold(true); title.setFont(fuenteTitle); title.setAlignment(CellStyle.ALIGN_CENTER); title.setBorderBottom(CellStyle.BORDER_MEDIUM); return title; }
From source file:main.resources.FileExcel.java
public void excelDia() throws FileNotFoundException, IOException { String nombreFile = "quincena.xlsx"; String nombreHoja = "dia x mes x ao x"; Workbook libro = new XSSFWorkbook(); Sheet hoja = libro.createSheet(nombreHoja); Font negrita = libro.createFont(); negrita.setBoldweight(Font.BOLDWEIGHT_BOLD); CellStyle estilo = libro.createCellStyle(); estilo.setAlignment(CellStyle.ALIGN_CENTER); estilo.setFillForegroundColor(IndexedColors.GREEN.getIndex()); estilo.setFillPattern(CellStyle.SOLID_FOREGROUND); estilo.setBorderBottom(CellStyle.BORDER_THIN); estilo.setBottomBorderColor(IndexedColors.AUTOMATIC.getIndex()); estilo.setBorderLeft(CellStyle.BORDER_THIN); estilo.setLeftBorderColor(IndexedColors.AUTOMATIC.getIndex()); estilo.setBorderRight(CellStyle.BORDER_THIN); estilo.setRightBorderColor(IndexedColors.AUTOMATIC.getIndex()); estilo.setBorderTop(CellStyle.BORDER_THIN); estilo.setTopBorderColor(IndexedColors.AUTOMATIC.getIndex()); estilo.setFont(negrita); CellStyle bordes = libro.createCellStyle(); bordes.setAlignment(CellStyle.ALIGN_LEFT); bordes.setBorderBottom(CellStyle.BORDER_THIN); bordes.setBottomBorderColor(IndexedColors.AUTOMATIC.getIndex()); bordes.setBorderLeft(CellStyle.BORDER_THIN); bordes.setLeftBorderColor(IndexedColors.AUTOMATIC.getIndex()); bordes.setBorderRight(CellStyle.BORDER_THIN); bordes.setRightBorderColor(IndexedColors.AUTOMATIC.getIndex()); bordes.setBorderTop(CellStyle.BORDER_THIN); bordes.setTopBorderColor(IndexedColors.AUTOMATIC.getIndex()); CellStyle estilo2 = libro.createCellStyle(); estilo2.setAlignment(CellStyle.ALIGN_CENTER); estilo2.setBorderBottom(CellStyle.BORDER_THIN); estilo2.setBottomBorderColor(IndexedColors.AUTOMATIC.getIndex()); estilo2.setBorderLeft(CellStyle.BORDER_THIN); estilo2.setLeftBorderColor(IndexedColors.AUTOMATIC.getIndex()); estilo2.setBorderRight(CellStyle.BORDER_THIN); estilo2.setRightBorderColor(IndexedColors.AUTOMATIC.getIndex()); estilo2.setBorderTop(CellStyle.BORDER_THIN); estilo2.setTopBorderColor(IndexedColors.AUTOMATIC.getIndex()); estilo2.setAlignment(CellStyle.ALIGN_CENTER); estilo2.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); estilo2.setFillPattern(CellStyle.SOLID_FOREGROUND); estilo2.setFont(negrita);/*from www. ja va2 s .c o m*/ CellStyle borderBot = libro.createCellStyle(); borderBot.setBorderBottom(CellStyle.BORDER_THIN); borderBot.setBottomBorderColor(IndexedColors.AUTOMATIC.getIndex()); Row row = hoja.createRow(1); //Row row1 = hoja.createRow(2); //empleados faltas Appi app = new Appi(); Date Fecha = new Date(); DateFormat formato = new SimpleDateFormat("YYYY-MM-dd"); String fechaActual = formato.format(Fecha); ArrayList<Empleado> faltas = app.faltas(fechaActual);//obtengo listado de empleados String grupoBandera = ""; String maestro = ""; int pRow = 3; if (!faltas.isEmpty()) { Cell celda = row.createCell(3); Cell celda2 = row.createCell(4); Cell celda3 = row.createCell(5); Cell celda4 = row.createCell(6); Cell celda5 = row.createCell(7); combinarceldas(hoja, 1, 1, 3, 7); celda.setCellValue("Asistencia " + fechaActual); celda.setCellStyle(estilo); celda2.setCellStyle(estilo); celda3.setCellStyle(estilo); celda4.setCellStyle(estilo); celda5.setCellStyle(estilo); grupoBandera = faltas.get(0).getGrupo(); //encabezados Row row2 = hoja.createRow(pRow); Cell cell = row2.createCell(1); cell.setCellValue("Nficha"); cell.setCellStyle(estilo2); Cell cell1 = row2.createCell(2); cell1.setCellValue("1er Apellido"); cell1.setCellStyle(estilo2); Cell cell2 = row2.createCell(3); cell2.setCellValue("2do Apellido"); cell2.setCellStyle(estilo2); Cell cell3 = row2.createCell(4); cell3.setCellValue("1er Nombre"); cell3.setCellStyle(estilo2); Cell cell4 = row2.createCell(5); cell4.setCellValue("2do Nombre"); cell4.setCellStyle(estilo2); Cell cell5 = row2.createCell(6); cell5.setCellValue("Identificacion"); cell5.setCellStyle(estilo2); Cell cell6 = row2.createCell(7); cell6.setCellValue("Da"); cell6.setCellStyle(estilo2); Cell cell7 = row2.createCell(8); cell7.setCellValue("Cargo"); cell7.setCellStyle(estilo2); Cell cell8 = row2.createCell(9); cell8.setCellValue("Grupo"); cell8.setCellStyle(estilo2); } Empleado emp = null; for (int i = 0; i < faltas.size(); i++) { //datos emp = (Empleado) faltas.get(i); Grupo grupo = app.grupo(emp.getGrupo()); if (!grupoBandera.equals(emp.getGrupo())) { grupoBandera = emp.getGrupo(); pRow = pRow + 2; //frima maestro Row row4 = hoja.createRow(pRow); Cell celda9 = row4.createCell(1); combinarceldas(hoja, pRow, pRow, 1, 3); celda9.setCellValue("Maestro Grupo:"); celda9.setCellStyle(estilo); Cell celda10 = row4.createCell(2); celda10.setCellStyle(bordes); Cell celda11 = row4.createCell(3); celda11.setCellStyle(bordes); Cell celda12 = row4.createCell(4); celda12.setCellStyle(borderBot); Cell celda13 = row4.createCell(5); celda13.setCellStyle(borderBot); Cell celda14 = row4.createCell(6); celda14.setCellStyle(borderBot); Cell celda15 = row4.createCell(7); celda15.setCellStyle(borderBot); Cell celda16 = row4.createCell(8); celda16.setCellStyle(borderBot); pRow++; Row row6 = hoja.createRow(pRow); Cell celda64 = row6.createCell(4); combinarceldas(hoja, pRow, pRow, 4, 8); celda64.setCellValue(maestro); pRow = pRow + 2; //encabexzados Row row2 = hoja.createRow(pRow); Cell cell = row2.createCell(1); cell.setCellValue("Nficha"); cell.setCellStyle(estilo2); Cell cell1 = row2.createCell(2); cell1.setCellValue("1er Apellido"); cell1.setCellStyle(estilo2); Cell cell2 = row2.createCell(3); cell2.setCellValue("2do Apellido"); cell2.setCellStyle(estilo2); Cell cell3 = row2.createCell(4); cell3.setCellValue("1er Nombre"); cell3.setCellStyle(estilo2); Cell cell4 = row2.createCell(5); cell4.setCellValue("2do Nombre"); cell4.setCellStyle(estilo2); Cell cell5 = row2.createCell(6); cell5.setCellValue("Identificacion"); cell5.setCellStyle(estilo2); Cell cell6 = row2.createCell(7); cell6.setCellValue("Da"); cell6.setCellStyle(estilo2); Cell cell7 = row2.createCell(8); cell7.setCellValue("Cargo"); cell7.setCellStyle(estilo2); Cell cell8 = row2.createCell(9); cell8.setCellValue("Grupo"); cell8.setCellStyle(estilo2); } Row row5 = hoja.createRow(pRow + 1); Cell celda51 = row5.createCell(1); celda51.setCellStyle(bordes); celda51.setCellValue(emp.getnFicha()); Cell celda52 = row5.createCell(2); celda52.setCellStyle(bordes); celda52.setCellValue(emp.getpApellido()); Cell celda53 = row5.createCell(3); celda53.setCellStyle(bordes); celda53.setCellValue(emp.getsApellido()); Cell celda54 = row5.createCell(4); celda54.setCellStyle(bordes); celda54.setCellValue(emp.getpNombre()); Cell celda55 = row5.createCell(5); celda55.setCellStyle(bordes); celda55.setCellValue(emp.getsNombre()); Cell celda56 = row5.createCell(6); celda56.setCellStyle(bordes); celda56.setCellValue(emp.getCedula()); Cell celda57 = row5.createCell(7); celda57.setCellStyle(bordes); celda57.setCellValue(fechaActual); Cell celda58 = row5.createCell(8); celda58.setCellStyle(bordes); celda58.setCellValue(emp.getCargo()); Cell celda59 = row5.createCell(9); celda59.setCellStyle(bordes); celda59.setCellValue(grupo.getNombre()); pRow++; Empleado supervisor = app.empleado(grupo.getSupervisor()); if (supervisor != null) { maestro = supervisor.getpNombre() + " " + supervisor.getsNombre() + " " + supervisor.getpApellido() + " " + supervisor.getsApellido(); } else { maestro = String.valueOf(grupo.getSupervisor()); } // //String cedula = (String) e; //Empleado emp = app.empleado(cedula); System.out.println(emp.getCedula()); } if (emp != null) { pRow = pRow + 2; //frima maestro Row row4 = hoja.createRow(pRow); Cell celda9 = row4.createCell(1); combinarceldas(hoja, pRow, pRow, 1, 3); celda9.setCellValue("Maestro Grupo:"); celda9.setCellStyle(estilo); Cell celda10 = row4.createCell(2); celda10.setCellStyle(bordes); Cell celda11 = row4.createCell(3); celda11.setCellStyle(bordes); Cell celda12 = row4.createCell(4); celda12.setCellStyle(borderBot); Cell celda13 = row4.createCell(5); celda13.setCellStyle(borderBot); Cell celda14 = row4.createCell(6); celda14.setCellStyle(borderBot); Cell celda15 = row4.createCell(7); celda15.setCellStyle(borderBot); Cell celda16 = row4.createCell(8); celda16.setCellStyle(borderBot); pRow++; Row row6 = hoja.createRow(pRow); Cell celda64 = row6.createCell(4); combinarceldas(hoja, pRow, pRow, 4, 8); celda64.setCellValue(maestro); } //enmcabezados //Row row3 = hoja.createRow(1); // debe ejcutarse un loop de acuerdoa consaulta //datos //datos responsable firma //CellS //celda64.setCellStyle(); try (FileOutputStream fileOut = new FileOutputStream(nombreFile)) { //escribir este libro en un OutputStream. libro.write(fileOut); fileOut.flush(); } }
From source file:model.Reports.java
private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<>(); CellStyle style; Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 18); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle();/*w ww . ja va2 s .c om*/ style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFont(titleFont); styles.put("title", style); Font monthFont = wb.createFont(); monthFont.setFontHeightInPoints((short) 11); monthFont.setColor(IndexedColors.WHITE.getIndex()); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(monthFont); style.setWrapText(true); styles.put("header", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setWrapText(true); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styles.put("cell", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(wb.createDataFormat().getFormat("0.00")); styles.put("formula", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(wb.createDataFormat().getFormat("0.00")); styles.put("formula_2", style); return styles; }
From source file:modelo.ProcesoVertimientosManagers.Visitas.java
/** * //w w w . j av a2 s.c o m * Escribe la informacion de la visita en el archivo de Excel. * * @param filaInicio * @param filaFin * @param tipoVisita * @param fechaInicial * @param fechaFinal * @param codigoProceso * @param url * @throws SQLException * @throws IOException */ public void escribirExcel(String filaInicio, String filaFin, String tipoVisita, String fechaInicial, String fechaFinal, String codigoProceso, String url, String estadoVisita, String contrato, String nit, String razonSocial, String motivoVisita) throws SQLException, IOException { //Instanciamos los objetos para el excel HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Visitas"); //Obtenemos el mapa con la informacion Map datos = organizarInfoExcel(filaInicio, filaFin, tipoVisita, fechaInicial, fechaFinal, codigoProceso, estadoVisita, contrato, nit, razonSocial, motivoVisita); //Informacion de los titulos Map<String, Object[]> data = new HashMap<String, Object[]>(); //Guardamos la cabecera del excel. data.put(String.valueOf(0), new Object[] { "CODIGO", "NOMBRES", "APELLIDOS", "FECHA VISITA", "MOTIVO", "TIPO VISITA", "ESTADO" }); //Escribimos el titulo Font font = workbook.createFont(); CellStyle style = workbook.createCellStyle(); style.setFont(font); font.setBoldweight(Font.BOLDWEIGHT_BOLD); Set<String> keyset2 = data.keySet(); for (String key : keyset2) { Row row = sheet.createRow(0); Object[] objArr = (Object[]) data.get(key); int cellnum = 0; for (Object obj : objArr) { Cell cell = row.createCell(cellnum++); cell.setCellStyle(style); if (obj instanceof Date) cell.setCellValue((Date) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Double) cell.setCellValue((Double) obj); } } //Obtenemos la informacion del mapa e insertamos la informacion en el excel Set<String> keyset = datos.keySet(); int rownum = 1; for (String key : keyset) { Row row = sheet.createRow(rownum++); Object[] objArr = (Object[]) datos.get(key); int cellnum = 0; for (Object obj : objArr) { Cell cell = row.createCell(cellnum++); if (obj instanceof Date) cell.setCellValue((Date) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Double) cell.setCellValue((Double) obj); } } //Guardamos el archivo en el disco. FileOutputStream out = new FileOutputStream(new File(url)); workbook.write(out); out.close(); }
From source file:module.siadap.domain.SiadapRootModule.java
License:Open Source License
private void populateSheet(HSSFSheet sheetToWriteTo, boolean considerQuotas, UnitSiadapWrapper unitToSearchIn, HSSFWorkbook wb, boolean shouldIncludeEndOfRole, boolean includeHarmonizationResponsibles, boolean shouldIncludeUniverse) { CreationHelper creationHelper = wb.getCreationHelper(); // make the sheet fit the page PrintSetup ps = sheetToWriteTo.getPrintSetup(); sheetToWriteTo.setAutobreaks(true);//from www. ja va 2 s .c o m ps.setFitHeight((short) 1); ps.setFitWidth((short) 1); /* ** styles ** */ // CostCenter style HSSFFont costCenterFont = wb.createFont(); costCenterFont.setColor(HSSFColor.DARK_BLUE.index); costCenterFont.setFontHeightInPoints((short) 12); costCenterFont.setBoldweight(Font.BOLDWEIGHT_BOLD); CellStyle costCenterStyle = wb.createCellStyle(); costCenterStyle.setFont(costCenterFont); // make the Unit header style CellStyle unitHeaderStyle = wb.createCellStyle(); unitHeaderStyle.setBorderBottom(CellStyle.BORDER_THIN); unitHeaderStyle.setBorderTop(CellStyle.BORDER_THIN); unitHeaderStyle.setBorderLeft(CellStyle.BORDER_THIN); unitHeaderStyle.setBorderRight(CellStyle.BORDER_THIN); unitHeaderStyle.setAlignment(CellStyle.ALIGN_CENTER); unitHeaderStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); HSSFFont headerFont = wb.createFont(); headerFont.setFontHeightInPoints((short) 12); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setItalic(true); unitHeaderStyle.setFont(headerFont); // make the default name style CellStyle defaultTextNameStyle = wb.createCellStyle(); defaultTextNameStyle.setBorderLeft(CellStyle.BORDER_THIN); defaultTextNameStyle.setBorderRight(CellStyle.BORDER_THIN); defaultTextNameStyle.setBorderBottom(CellStyle.BORDER_NONE); defaultTextNameStyle.setBorderTop(CellStyle.BORDER_NONE); defaultTextNameStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); HSSFFont defaultFont = wb.createFont(); defaultFont.setFontHeightInPoints((short) 11); defaultTextNameStyle.setFont(defaultFont); // make the last line name style CellStyle defaultTextNameLastStyle = wb.createCellStyle(); defaultTextNameLastStyle.setBorderLeft(CellStyle.BORDER_THIN); defaultTextNameLastStyle.setBorderRight(CellStyle.BORDER_THIN); defaultTextNameLastStyle.setBorderBottom(CellStyle.BORDER_THIN); defaultTextNameLastStyle.setBorderTop(CellStyle.BORDER_NONE); defaultTextNameLastStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); defaultTextNameLastStyle.setFont(defaultFont); // make the default IST-ID last line style CellStyle defaultTextIstIdLastStyle = wb.createCellStyle(); defaultTextIstIdLastStyle.setBorderLeft(CellStyle.BORDER_THIN); defaultTextIstIdLastStyle.setBorderBottom(CellStyle.BORDER_THIN); defaultTextIstIdLastStyle.setBorderTop(CellStyle.BORDER_NONE); defaultTextIstIdLastStyle.setBorderRight(CellStyle.BORDER_THIN); defaultTextIstIdLastStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); defaultTextIstIdLastStyle.setAlignment(CellStyle.ALIGN_CENTER); defaultTextIstIdLastStyle.setFont(defaultFont); // make the default IST-ID style CellStyle defaultTextIstIdStyle = wb.createCellStyle(); defaultTextIstIdStyle.setBorderLeft(CellStyle.BORDER_THIN); defaultTextIstIdStyle.setBorderBottom(CellStyle.BORDER_NONE); defaultTextIstIdStyle.setBorderTop(CellStyle.BORDER_NONE); defaultTextIstIdStyle.setBorderRight(CellStyle.BORDER_THIN); defaultTextIstIdStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); defaultTextIstIdStyle.setAlignment(CellStyle.ALIGN_CENTER); defaultTextIstIdStyle.setFont(defaultFont); // header style // CellStyle headerStyle = wb.createCellStyle(); // HSSFFont headerFont = wb.createFont(); // headerFont.setFontName(HSSFFont.FONT_ARIAL); // headerFont.setFontHeightInPoints((short) 10); // headerStyle.setFont(headerFont); // // first line style CellStyle firstLineStyle = wb.createCellStyle(); HSSFFont firstLineFont = wb.createFont(); firstLineFont.setColor(HSSFColor.DARK_BLUE.index); firstLineFont.setFontHeightInPoints((short) 14); firstLineFont.setBoldweight(Font.BOLDWEIGHT_BOLD); firstLineStyle.setFont(firstLineFont); firstLineStyle.setAlignment(CellStyle.ALIGN_CENTER); firstLineStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); // second line style CellStyle secondLineStyle = wb.createCellStyle(); HSSFFont secondLineFont = wb.createFont(); secondLineFont.setBoldweight(Font.BOLDWEIGHT_BOLD); secondLineFont.setFontHeightInPoints((short) 14); secondLineStyle.setFont(secondLineFont); secondLineStyle.setAlignment(CellStyle.ALIGN_CENTER); secondLineStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); // the style for Unit Harmonization responsibles - title CellStyle unitHarmonizationTitleStyle = wb.createCellStyle(); // the BLUE title font - is equal to 'firstLineFont' unitHarmonizationTitleStyle.setFont(firstLineFont); // now we just have to shade it unitHarmonizationTitleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); unitHarmonizationTitleStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); unitHarmonizationTitleStyle.setAlignment(CellStyle.ALIGN_CENTER); // the style for Unit Harmonization responsibles - normal // let's create the BLUE Arial 14 font for the responsibles of // harmonization HSSFFont harmonizationResponsibleFont = wb.createFont(); harmonizationResponsibleFont.setColor(HSSFColor.DARK_BLUE.index); harmonizationResponsibleFont.setFontHeightInPoints((short) 14); CellStyle unitHarmonizationResponsibleStyle = wb.createCellStyle(); unitHarmonizationResponsibleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); unitHarmonizationResponsibleStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); unitHarmonizationResponsibleStyle.setFont(harmonizationResponsibleFont); unitHarmonizationResponsibleStyle.setAlignment(CellStyle.ALIGN_CENTER); /* ** END of styles ** */ /* ** Immutable IST header ** */ HSSFHeader header = sheetToWriteTo.getHeader(); header.setCenter(HSSFHeader.font("Arial", "Normal") + HSSFHeader.fontSize((short) 10)); header.setCenter("Instituto Superior Tcnico"); int rowIndex = START_ROW_INDEX; int cellIndex = START_CELL_INDEX; int firstLineIndex = rowIndex++; int secondLineIndex = rowIndex++; /* ** Write the first lines with the dates ** */ HSSFRow row = sheetToWriteTo.createRow(firstLineIndex); HSSFCell cell = row.createCell(cellIndex); cell.setCellValue("SIADAP - LISTA DE AVALIADORES " + unitToSearchIn.getYear()); cell.setCellStyle(firstLineStyle); sheetToWriteTo .addMergedRegion(new CellRangeAddress(firstLineIndex, firstLineIndex, cellIndex, cellIndex + 3)); // second line if (!considerQuotas) { cellIndex = START_CELL_INDEX; row = sheetToWriteTo.createRow(secondLineIndex); cell = row.createCell(cellIndex); cell.setCellValue("PESSOAL CONTRATADO PELA ADIST"); cell.setCellStyle(secondLineStyle); } /* ** write the IST logo ** */ int pictureIdx = wb.addPicture(istLogoBytes, Workbook.PICTURE_TYPE_PNG); HSSFPatriarch drawingPatriarch = sheetToWriteTo.createDrawingPatriarch(); ClientAnchor clientAnchor = creationHelper.createClientAnchor(); clientAnchor.setCol1(cellIndex); clientAnchor.setRow1(rowIndex); HSSFPicture picture = drawingPatriarch.createPicture(clientAnchor, pictureIdx); // let's give the next item some space rowIndex += 6; /* ** Dynamic IST footer ** */ HSSFFooter footer = sheetToWriteTo.getFooter(); footer.setLeft("Lista gerada em: " + HSSFFooter.date() + " " + HSSFFooter.time()); footer.setCenter(HSSFFooter.page()); footer.setRight("SIADAP - Lista de avaliadores " + unitToSearchIn.getYear()); for (UnitSiadapWrapper eachUnit : unitToSearchIn .getAllChildUnits(unitToSearchIn.getConfiguration().getUnitRelations())) { Collection<Person> harmonizationResponsibles = eachUnit.getHarmonizationResponsibles(); if (includeHarmonizationResponsibles && !harmonizationResponsibles.isEmpty()) { // let's add the section stating the responsible for // Harmonization cellIndex = START_CELL_INDEX; row = sheetToWriteTo.createRow(++rowIndex); // let's merge the row sheetToWriteTo.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, cellIndex, cellIndex + 3)); cell = row.createCell(cellIndex); cell.setCellStyle(unitHarmonizationTitleStyle); cell.setCellValue("UNIDADE DE HARMONIZAO: " + eachUnit.getName()); // a 'blank' styled line row = sheetToWriteTo.createRow(++rowIndex); // merge it sheetToWriteTo.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, cellIndex, cellIndex + 3)); row.createCell(cellIndex).setCellStyle(unitHarmonizationResponsibleStyle); // each responsible has one of the following lines for (Person harmonizationResponsible : harmonizationResponsibles) { cellIndex = START_CELL_INDEX; row = sheetToWriteTo.createRow(++rowIndex); // merge it sheetToWriteTo .addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, cellIndex, cellIndex + 3)); cell = row.createCell(cellIndex); cell.setCellStyle(unitHarmonizationResponsibleStyle); cell.setCellValue("RESPONS?VEL PELA HARMONIZAO: " + harmonizationResponsible.getName()); } // and let's add an extra 'blank' styled line row = sheetToWriteTo.createRow(++rowIndex); sheetToWriteTo.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, cellIndex, cellIndex + 3)); row.createCell(cellIndex).setCellStyle(unitHarmonizationResponsibleStyle); // and a regular one! (skip one in the index) ++rowIndex; } if (eachUnit.getQuotaAwareTotalPeopleWorkingInUnit(false, considerQuotas) > 0) { row = sheetToWriteTo.createRow(++rowIndex); cellIndex = START_CELL_INDEX; // write the unit name and cost center String unitNameWithCC = eachUnit.getUnit().getPartyName().getContent(); if (eachUnit.getUnit().getPartyTypesSet().contains(PartyType.readBy("CostCenter"))) { unitNameWithCC += " - " + eachUnit.getUnit().getAcronym(); } cell = row.createCell(cellIndex++); sheetToWriteTo.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, --cellIndex, ++cellIndex)); cell.setCellValue(unitNameWithCC); cell.setCellStyle(costCenterStyle); /* **** write the Unit header ***** */ // restart the cell's index cellIndex = START_CELL_INDEX; // IST id avaliado int firstLineAfterUnitNameIndex = ++rowIndex; int secondLineAfterUnitNameIndex = ++rowIndex; row = sheetToWriteTo.createRow(firstLineAfterUnitNameIndex); cell = row.createCell(cellIndex); cell.setCellStyle(unitHeaderStyle); cell.setCellValue("IST id."); row = sheetToWriteTo.createRow(secondLineAfterUnitNameIndex); cell = row.createCell(cellIndex); cell.setCellStyle(unitHeaderStyle); // merge the IST id sheetToWriteTo.addMergedRegion(new CellRangeAddress(firstLineAfterUnitNameIndex, secondLineAfterUnitNameIndex, cellIndex, cellIndex)); // Nome avaliado row = sheetToWriteTo.getRow(firstLineAfterUnitNameIndex); cell = row.createCell(++cellIndex); cell.setCellStyle(unitHeaderStyle); cell.setCellValue("Nome"); row = sheetToWriteTo.getRow(secondLineAfterUnitNameIndex); cell = row.createCell(cellIndex); cell.setCellStyle(unitHeaderStyle); // merge sheetToWriteTo.addMergedRegion(new CellRangeAddress(firstLineAfterUnitNameIndex, secondLineAfterUnitNameIndex, cellIndex, cellIndex)); if (shouldIncludeUniverse) { // SIADAP do avaliado row = sheetToWriteTo.getRow(firstLineAfterUnitNameIndex); cell = row.createCell(++cellIndex); cell.setCellStyle(unitHeaderStyle); cell.setCellValue("SIADAP"); row = sheetToWriteTo.getRow(secondLineAfterUnitNameIndex); cell = row.createCell(cellIndex); cell.setCellStyle(unitHeaderStyle); // merge sheetToWriteTo.addMergedRegion(new CellRangeAddress(firstLineAfterUnitNameIndex, secondLineAfterUnitNameIndex, cellIndex, cellIndex)); } // Ist id do avaliador row = sheetToWriteTo.getRow(firstLineAfterUnitNameIndex); cell = row.createCell(++cellIndex); cell.setCellStyle(unitHeaderStyle); cell.setCellValue("IST id."); row = sheetToWriteTo.getRow(secondLineAfterUnitNameIndex); cell = row.createCell(cellIndex); cell.setCellStyle(unitHeaderStyle); // merge sheetToWriteTo.addMergedRegion(new CellRangeAddress(firstLineAfterUnitNameIndex, secondLineAfterUnitNameIndex, cellIndex, cellIndex)); // avaliador row = sheetToWriteTo.getRow(firstLineAfterUnitNameIndex); cell = row.createCell(++cellIndex); cell.setCellStyle(unitHeaderStyle); cell.setCellValue("Avaliador"); row = sheetToWriteTo.getRow(secondLineAfterUnitNameIndex); cell = row.createCell(cellIndex); cell.setCellStyle(unitHeaderStyle); // merge sheetToWriteTo.addMergedRegion(new CellRangeAddress(firstLineAfterUnitNameIndex, secondLineAfterUnitNameIndex, cellIndex, cellIndex)); List<PersonSiadapWrapper> listToUse = (considerQuotas) ? eachUnit.getUnitEmployeesWithQuotas(false) : eachUnit.getUnitEmployeesWithoutQuotas(true); // now let's take care of exporting the persons for (PersonSiadapWrapper personWrapper : listToUse) { row = sheetToWriteTo.createRow(++rowIndex); // restart the cell's index cellIndex = START_CELL_INDEX; String istIdEvaluated = personWrapper.getPerson().getUser().getUsername(); cell = row.createCell(cellIndex++); cell.setCellValue(istIdEvaluated); cell.setCellStyle(defaultTextIstIdStyle); String nameEvaluatedPerson = personWrapper.getPerson().getName(); cell = row.createCell(cellIndex++); cell.setCellValue(nameEvaluatedPerson); cell.setCellStyle(defaultTextNameStyle); if (shouldIncludeUniverse) { Siadap siadap = personWrapper.getSiadap(); String siadapUniverseToBeWritten = (siadap == null || siadap.getDefaultSiadapUniverse() == null) ? "No definido" : siadap.getDefaultSiadapUniverse().getLocalizedName(); cell = row.createCell(cellIndex++); cell.setCellValue(siadapUniverseToBeWritten); cell.setCellStyle(defaultTextNameStyle); } PersonSiadapWrapper evaluatorWrapper = personWrapper.getEvaluator(); String istIdEvaluator = evaluatorWrapper == null ? "-" : evaluatorWrapper.getPerson().getUser().getUsername(); cell = row.createCell(cellIndex++); cell.setCellValue(istIdEvaluator); cell.setCellStyle(defaultTextIstIdStyle); String nameEvaluatorWrapper = evaluatorWrapper == null ? "-" : evaluatorWrapper.getName(); cell = row.createCell(cellIndex++); cell.setCellValue(nameEvaluatorWrapper); cell.setCellStyle(defaultTextNameStyle); } // let's make a bottom border on the last four cells for (int i = START_CELL_INDEX; i < START_CELL_INDEX + 4; i++) { cell = row.getCell(i); // let's diferentaitate between the IST-id and the name if (i == START_CELL_INDEX || i == START_CELL_INDEX + 2) // first // cell, // IST-ID // then. // or // third // cell // the // other // IST-ID { cell.setCellStyle(defaultTextIstIdLastStyle); } else { cell.setCellStyle(defaultTextNameLastStyle); } } row = sheetToWriteTo.createRow(++rowIndex); row = sheetToWriteTo.createRow(++rowIndex); } } sheetToWriteTo.autoSizeColumn(START_CELL_INDEX); sheetToWriteTo.autoSizeColumn(START_CELL_INDEX + 1); sheetToWriteTo.autoSizeColumn(START_CELL_INDEX + 2); sheetToWriteTo.autoSizeColumn(START_CELL_INDEX + 3); sheetToWriteTo.autoSizeColumn(START_CELL_INDEX + 4); // now let's resize the logo picture.resize(); }