List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook createDataFormat
@Override
public HSSFDataFormat createDataFormat()
From source file:com.eryansky.core.excelTools.ExcelUtils.java
License:Apache License
public static void copyCellStyle(HSSFWorkbook destwb, HSSFCellStyle dest, HSSFWorkbook srcwb, HSSFCellStyle src) {//from w w w . j ava2 s .com if (src == null || dest == null) return; dest.setAlignment(src.getAlignment()); dest.setBorderBottom(src.getBorderBottom()); dest.setBorderLeft(src.getBorderLeft()); dest.setBorderRight(src.getBorderRight()); dest.setBorderTop(src.getBorderTop()); dest.setBottomBorderColor(findColor(src.getBottomBorderColor(), srcwb, destwb)); dest.setDataFormat( destwb.createDataFormat().getFormat(srcwb.createDataFormat().getFormat(src.getDataFormat()))); dest.setFillPattern(src.getFillPattern()); dest.setFillForegroundColor(findColor(src.getFillForegroundColor(), srcwb, destwb)); dest.setFillBackgroundColor(findColor(src.getFillBackgroundColor(), srcwb, destwb)); dest.setHidden(src.getHidden()); dest.setIndention(src.getIndention()); dest.setLeftBorderColor(findColor(src.getLeftBorderColor(), srcwb, destwb)); dest.setLocked(src.getLocked()); dest.setRightBorderColor(findColor(src.getRightBorderColor(), srcwb, destwb)); dest.setRotation(src.getRotation()); dest.setTopBorderColor(findColor(src.getTopBorderColor(), srcwb, destwb)); dest.setVerticalAlignment(src.getVerticalAlignment()); dest.setWrapText(src.getWrapText()); HSSFFont f = srcwb.getFontAt(src.getFontIndex()); HSSFFont nf = findFont(f, srcwb, destwb); if (nf == null) { nf = destwb.createFont(); nf.setBoldweight(f.getBoldweight()); nf.setCharSet(f.getCharSet()); nf.setColor(findColor(f.getColor(), srcwb, destwb)); nf.setFontHeight(f.getFontHeight()); nf.setFontHeightInPoints(f.getFontHeightInPoints()); nf.setFontName(f.getFontName()); nf.setItalic(f.getItalic()); nf.setStrikeout(f.getStrikeout()); nf.setTypeOffset(f.getTypeOffset()); nf.setUnderline(f.getUnderline()); } dest.setFont(nf); }
From source file:com.github.gaborfeher.grantmaster.framework.base.ExcelExporter.java
License:Open Source License
private void setExcelCell(HSSFWorkbook workbook, Object cellValue, Cell excelCell) { if (cellValue instanceof BigDecimal) { double doubleValue = ((BigDecimal) cellValue).doubleValue(); excelCell.setCellValue(doubleValue); HSSFCellStyle cellStyle = workbook.createCellStyle(); HSSFDataFormat hssfDataFormat = workbook.createDataFormat(); cellStyle.setDataFormat(hssfDataFormat.getFormat("#,##0.00")); excelCell.setCellStyle(cellStyle); excelCell.setCellType(Cell.CELL_TYPE_NUMERIC); } else if (cellValue instanceof LocalDate) { LocalDate localDate = (LocalDate) cellValue; Calendar calendar = Calendar.getInstance(); calendar.set(localDate.getYear(), localDate.getMonthValue() - 1, localDate.getDayOfMonth()); excelCell.setCellValue(calendar); String excelFormatPattern = DateFormatConverter.convert(Locale.US, "yyyy-MM-DD"); CellStyle cellStyle = workbook.createCellStyle(); DataFormat poiFormat = workbook.createDataFormat(); cellStyle.setDataFormat(poiFormat.getFormat(excelFormatPattern)); excelCell.setCellStyle(cellStyle); } else if (cellValue != null) { excelCell.setCellValue(cellValue.toString()); }//w w w. j a v a2 s . com }
From source file:com.github.gujou.deerbelling.sonarqube.service.XlsTasksGenerator.java
License:Open Source License
public static File generateFile(Project sonarProject, FileSystem sonarFileSystem, String sonarUrl, String sonarLogin, String sonarPassword) { short formatIndex; HSSFDataFormat dataFormat = null;//from w w w . j a v a 2s . c o m FileOutputStream out = null; HSSFWorkbook workbook = null; String filePath = sonarFileSystem.workDir().getAbsolutePath() + File.separator + "tasks_report_" + sonarProject.getEffectiveKey().replace(':', '-') + "." + ReportsKeys.TASKS_REPORT_TYPE_XLS_EXTENSION; File resultFile = new File(filePath); try { out = new FileOutputStream(resultFile); workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Tasks list"); // Date format. dataFormat = workbook.createDataFormat(); formatIndex = dataFormat.getFormat("yyyy-MM-ddTHH:mm:ss"); HSSFCellStyle dateStyle = workbook.createCellStyle(); dateStyle.setDataFormat(formatIndex); Issues rootIssue = IssueGateway.getOpenIssues(sonarProject.getEffectiveKey(), sonarUrl, sonarLogin, sonarPassword); if (rootIssue == null) { return null; } DataValidationHelper validationHelper = new HSSFDataValidationHelper(sheet); DataValidationConstraint constraint = validationHelper.createExplicitListConstraint( new String[] { "OPENED", "CONFIRMED", "REOPENED", "RESOLVED", "CLOSE" }); CellRangeAddressList addressList = new CellRangeAddressList(1, rootIssue.getIssues().size() + 1, STATUS_COLUMN_INDEX, STATUS_COLUMN_INDEX); DataValidation dataValidation = validationHelper.createValidation(constraint, addressList); dataValidation.setSuppressDropDownArrow(false); sheet.addValidationData(dataValidation); int rownum = 0; Row row = sheet.createRow(rownum++); row.createCell(STATUS_COLUMN_INDEX).setCellValue("Status"); row.createCell(SEVERITY_COLUMN_INDEX).setCellValue("Severity"); row.createCell(COMPONENT_COLUMN_INDEX).setCellValue("Component"); row.createCell(LINE_COLUMN_INDEX).setCellValue("Line"); row.createCell(MESSAGE_COLUMN_INDEX).setCellValue("Message"); row.createCell(AUTHOR_COLUMN_INDEX).setCellValue("Author"); row.createCell(ASSIGNED_COLUMN_INDEX).setCellValue("Assigned"); row.createCell(CREATION_DATE_COLUMN_INDEX).setCellValue("CreationDate"); row.createCell(UPDATE_DATE_COLUMN_INDEX).setCellValue("UpdateDate"); row.createCell(COMPONENT_PATH_COLUMN_INDEX).setCellValue("Path"); for (Issue issue : rootIssue.getIssues()) { if (issue != null) { row = sheet.createRow(rownum++); int componentIndex = 0; if (issue.getComponent() != null) { componentIndex = issue.getComponent().lastIndexOf('/'); } String component; String path; if (componentIndex > 0) { component = issue.getComponent().substring(componentIndex + 1); path = issue.getComponent().substring(0, componentIndex); } else { component = issue.getComponent(); path = ""; } // Set values. row.createCell(STATUS_COLUMN_INDEX).setCellValue(issue.getStatus()); row.createCell(SEVERITY_COLUMN_INDEX).setCellValue(issue.getSeverity()); row.createCell(COMPONENT_COLUMN_INDEX).setCellValue(component); row.createCell(LINE_COLUMN_INDEX).setCellValue(issue.getLine()); row.createCell(MESSAGE_COLUMN_INDEX).setCellValue(issue.getMessage()); row.createCell(AUTHOR_COLUMN_INDEX).setCellValue(issue.getAuthor()); row.createCell(ASSIGNED_COLUMN_INDEX).setCellValue(issue.getAssignee()); row.createCell(CREATION_DATE_COLUMN_INDEX).setCellValue(issue.getCreationDate()); row.createCell(UPDATE_DATE_COLUMN_INDEX).setCellValue(issue.getUpdateDate()); row.createCell(COMPONENT_PATH_COLUMN_INDEX).setCellValue(path); // Set date style to date column. row.getCell(CREATION_DATE_COLUMN_INDEX).setCellStyle(dateStyle); row.getCell(UPDATE_DATE_COLUMN_INDEX).setCellStyle(dateStyle); } } // Auto-size sheet columns. sheet.autoSizeColumn(STATUS_COLUMN_INDEX); sheet.autoSizeColumn(STATUS_COLUMN_INDEX); sheet.autoSizeColumn(COMPONENT_COLUMN_INDEX); sheet.autoSizeColumn(LINE_COLUMN_INDEX); sheet.autoSizeColumn(MESSAGE_COLUMN_INDEX); sheet.autoSizeColumn(AUTHOR_COLUMN_INDEX); sheet.autoSizeColumn(ASSIGNED_COLUMN_INDEX); sheet.autoSizeColumn(CREATION_DATE_COLUMN_INDEX); sheet.autoSizeColumn(UPDATE_DATE_COLUMN_INDEX); sheet.autoSizeColumn(COMPONENT_PATH_COLUMN_INDEX); workbook.write(out); } catch (FileNotFoundException e) { // TODO manage error. e.printStackTrace(); } catch (IOException e) { // TODO manage error. e.printStackTrace(); } finally { IOUtils.closeQuietly(workbook); IOUtils.closeQuietly(out); } return resultFile; }
From source file:com.ideaspymes.arthyweb.ventas.web.controllers.VentasCantadasBean.java
public void generarExcelVentasCantadas() throws IOException { cargaResumen();/*w ww .ja v a 2s . com*/ if (ventasCantadas == null || ventasCantadas.isEmpty()) { JsfUtil.addErrorMessage("No hay datos para generar"); } else { Map<String, List<ResumenVentasCantadas>> map2 = new HashMap<>(); for (ResumenVentasCantadas rc : ventasCantadas) { List<ResumenVentasCantadas> valueList = map2.get(rc.getTerritorio()); if (valueList == null) { valueList = new ArrayList<>(); valueList.add(rc); map2.put(rc.getTerritorio(), valueList); } else { valueList.add(rc); } } HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Ventas Detalle"); configAnchoCols(sheet); //Fonts Font fontSubTitulo = workbook.createFont(); fontSubTitulo.setFontHeightInPoints((short) 8); fontSubTitulo.setBoldweight(Font.BOLDWEIGHT_BOLD); //Fonts Font fontTotal3 = workbook.createFont(); fontTotal3.setFontHeightInPoints((short) 8); fontTotal3.setColor(HSSFColor.RED.index); fontTotal3.setBoldweight(Font.BOLDWEIGHT_BOLD); //Fonts Font fontTerritorioTotal3 = workbook.createFont(); fontTerritorioTotal3.setFontHeightInPoints((short) 8); fontTerritorioTotal3.setColor(HSSFColor.ORANGE.index); fontTerritorioTotal3.setBoldweight(Font.BOLDWEIGHT_BOLD); //Estilos DataFormat format = workbook.createDataFormat(); CellStyle styleTotal3 = workbook.createCellStyle(); styleTotal3.setFont(fontTotal3); styleTotal3.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleTotal3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleTotal3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); styleTotal3.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleTotal3.setDataFormat(format.getFormat("0.0%")); styleTerritorioTotal3 = workbook.createCellStyle(); styleTerritorioTotal3.setFont(fontTerritorioTotal3); styleTerritorioTotal3.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleTerritorioTotal3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); // styleTerritorioTotal3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); // styleTerritorioTotal3.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); creaCabecera(workbook, sheet); String[] aCols20 = null; String[] aCols10 = null; String[] aColsGrue = null; String indicesTotales = ""; for (Map.Entry<String, List<ResumenVentasCantadas>> entry : map2.entrySet()) { ++indexRow; HSSFRow row = sheet.createRow((++indexRow)); HSSFCell cellTerr = row.createCell(indexCol); cellTerr.setCellValue(entry.getKey().toUpperCase()); cellTerr.setCellStyle(styleTerritorio); int indexInicioGrupo = indexRow + 2; List<ResumenVentasCantadas> detalles = entry.getValue(); Comparator<ResumenVentasCantadas> comp = new Comparator<ResumenVentasCantadas>() { @Override public int compare(ResumenVentasCantadas o1, ResumenVentasCantadas o2) { return o1.getOrden() > o2.getOrden() ? 1 : -1; } }; Collections.sort(detalles, comp); for (ResumenVentasCantadas rv : detalles) { cols20 = ""; cols10 = ""; colsGrue = ""; int indexFilaActual = ++indexRow; HSSFRow row1 = sheet.createRow((indexFilaActual)); HSSFCell cellZona = row1.createCell(indexCol + 0); cellZona.setCellValue(rv.getZona()); cellZona.setCellStyle(styleTitulo9); HSSFCell cellVendedor = row1.createCell(indexCol + 1); cellVendedor.setCellValue(rv.getVendedor()); cellVendedor.setCellStyle(styleTitulo9); HSSFCell cellBoletas = row1.createCell(indexCol + 2); cellBoletas.setCellValue(rv.getCantboletas()); cellBoletas.setCellStyle(styleCantidad); generarDetalles(row1, indexFilaActual, rv); } int indexFinGrupo = indexRow + 1; int indexTotal1 = ++indexRow; int indexTotal2 = ++indexRow; int indexTotal3 = ++indexRow; HSSFRow rowTotal1 = sheet.createRow(indexTotal1); HSSFRow rowTotal2 = sheet.createRow((indexTotal2)); HSSFRow rowTotal3 = sheet.createRow((indexTotal3)); aCols20 = cols20.split(","); aCols10 = cols10.split(","); aColsGrue = colsGrue.split(","); //TOTAL 1 generarTotal1(rowTotal1, indexInicioGrupo, indexFinGrupo, aColsGrue, aCols20, aCols10, cellTerr); //TOTAL 2 generarTotal2(rowTotal2, indexFinGrupo, aColsGrue, cellTerr); //TOTAL 3 generarTotal3(rowTotal3, indexFinGrupo, styleTotal3, cellTerr, sheet); //++indexRow; indicesTotales += (indexFinGrupo + 1) + ","; } String[] aIndexTotales = indicesTotales.split(","); //TOTAL pais int indexTotalPais = ++indexRow; HSSFRow rowTotalPais = sheet.createRow((indexTotalPais)); generarTotalPais(rowTotalPais, styleTotal3, sheet, aIndexTotales, aColsGrue, aCols20, aCols10); HttpServletResponse response = (HttpServletResponse) FacesContext.getCurrentInstance() .getExternalContext().getResponse(); response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment; filename=Ventas Cantadas.xls"); workbook.write(response.getOutputStream()); response.getOutputStream().flush(); response.getOutputStream().close(); FacesContext.getCurrentInstance().responseComplete(); indexRow = 1; } }
From source file:com.ideaspymes.arthyweb.ventas.web.controllers.VentasCantadasBean.java
public void generarExcelVentasCantadasDetalle() throws IOException { cargaResumenPorVendedor();//w ww . j av a 2 s. co m if (ventasCantadas == null || ventasCantadas.isEmpty()) { JsfUtil.addErrorMessage("No hay datos para generar"); } else { Map<String, List<ResumenVentasCantadas>> map2 = new HashMap<>(); for (ResumenVentasCantadas rc : ventasCantadas) { List<ResumenVentasCantadas> valueList = map2.get(rc.getTerritorio()); if (valueList == null) { valueList = new ArrayList<>(); valueList.add(rc); map2.put(rc.getTerritorio(), valueList); } else { valueList.add(rc); } } HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Ventas Detalle"); configAnchoCols(sheet); //Fonts Font fontSubTitulo = workbook.createFont(); fontSubTitulo.setFontHeightInPoints((short) 8); fontSubTitulo.setBoldweight(Font.BOLDWEIGHT_BOLD); //Fonts Font fontTotal3 = workbook.createFont(); fontTotal3.setFontHeightInPoints((short) 8); fontTotal3.setColor(HSSFColor.RED.index); fontTotal3.setBoldweight(Font.BOLDWEIGHT_BOLD); //Fonts Font fontTerritorioTotal3 = workbook.createFont(); fontTerritorioTotal3.setFontHeightInPoints((short) 8); fontTerritorioTotal3.setColor(HSSFColor.ORANGE.index); fontTerritorioTotal3.setBoldweight(Font.BOLDWEIGHT_BOLD); //Estilos DataFormat format = workbook.createDataFormat(); CellStyle styleTotal3 = workbook.createCellStyle(); styleTotal3.setFont(fontTotal3); styleTotal3.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleTotal3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleTotal3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); styleTotal3.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleTotal3.setDataFormat(format.getFormat("0.0%")); styleTerritorioTotal3 = workbook.createCellStyle(); styleTerritorioTotal3.setFont(fontTerritorioTotal3); styleTerritorioTotal3.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleTerritorioTotal3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); // styleTerritorioTotal3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); // styleTerritorioTotal3.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); creaCabecera(workbook, sheet); String[] aCols20 = null; String[] aCols10 = null; String[] aColsGrue = null; String indicesTotales = ""; for (Map.Entry<String, List<ResumenVentasCantadas>> entry : map2.entrySet()) { ++indexRow; HSSFRow row = sheet.createRow((++indexRow)); HSSFCell cellTerr = row.createCell(indexCol); cellTerr.setCellValue(entry.getKey().toUpperCase()); cellTerr.setCellStyle(styleTerritorio); int indexInicioGrupo = indexRow + 2; List<ResumenVentasCantadas> detalles = entry.getValue(); Comparator<ResumenVentasCantadas> comp = new Comparator<ResumenVentasCantadas>() { @Override public int compare(ResumenVentasCantadas o1, ResumenVentasCantadas o2) { return o1.getOrden() > o2.getOrden() ? 1 : -1; } }; Collections.sort(detalles, comp); for (ResumenVentasCantadas rv : detalles) { cols20 = ""; cols10 = ""; colsGrue = ""; int indexFilaActual = ++indexRow; HSSFRow row1 = sheet.createRow((indexFilaActual)); HSSFCell cellZona = row1.createCell(indexCol + 0); cellZona.setCellValue(rv.getZona()); cellZona.setCellStyle(styleTitulo9); HSSFCell cellVendedor = row1.createCell(indexCol + 1); cellVendedor.setCellValue(rv.getVendedor()); cellVendedor.setCellStyle(styleTitulo9); HSSFCell cellBoletas = row1.createCell(indexCol + 2); cellBoletas.setCellValue(rv.getCantboletas()); cellBoletas.setCellStyle(styleCantidad); generarDetalles(row1, indexFilaActual, rv); } int indexFinGrupo = indexRow + 1; int indexTotal1 = ++indexRow; int indexTotal2 = ++indexRow; int indexTotal3 = ++indexRow; HSSFRow rowTotal1 = sheet.createRow(indexTotal1); HSSFRow rowTotal2 = sheet.createRow((indexTotal2)); HSSFRow rowTotal3 = sheet.createRow((indexTotal3)); aCols20 = cols20.split(","); aCols10 = cols10.split(","); aColsGrue = colsGrue.split(","); //TOTAL 1 generarTotal1(rowTotal1, indexInicioGrupo, indexFinGrupo, aColsGrue, aCols20, aCols10, cellTerr); //TOTAL 2 generarTotal2(rowTotal2, indexFinGrupo, aColsGrue, cellTerr); //TOTAL 3 generarTotal3(rowTotal3, indexFinGrupo, styleTotal3, cellTerr, sheet); //++indexRow; indicesTotales += (indexFinGrupo + 1) + ","; } String[] aIndexTotales = indicesTotales.split(","); //TOTAL pais int indexTotalPais = ++indexRow; HSSFRow rowTotalPais = sheet.createRow((indexTotalPais)); generarTotalPais(rowTotalPais, styleTotal3, sheet, aIndexTotales, aColsGrue, aCols20, aCols10); HttpServletResponse response = (HttpServletResponse) FacesContext.getCurrentInstance() .getExternalContext().getResponse(); response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment; filename=Ventas Cantadas.xls"); workbook.write(response.getOutputStream()); response.getOutputStream().flush(); response.getOutputStream().close(); FacesContext.getCurrentInstance().responseComplete(); indexRow = 1; } }
From source file:com.ideaspymes.arthyweb.ventas.web.controllers.VentasCantadasBean.java
private void creaCabecera(HSSFWorkbook workbook, HSSFSheet sheet) { Font fontSubTitulo = workbook.createFont(); fontSubTitulo.setFontHeightInPoints((short) 8); fontSubTitulo.setBoldweight(Font.BOLDWEIGHT_BOLD); Font fontSubTituloAzul = workbook.createFont(); fontSubTituloAzul.setFontHeightInPoints((short) 8); fontSubTituloAzul.setColor(HSSFColor.DARK_BLUE.index); fontSubTituloAzul.setBoldweight(Font.BOLDWEIGHT_BOLD); Font fontSubTituloCaje = workbook.createFont(); fontSubTituloCaje.setFontHeightInPoints((short) 7); fontSubTituloCaje.setColor(HSSFColor.DARK_BLUE.index); fontSubTituloCaje.setBoldweight(Font.BOLDWEIGHT_BOLD); Font font7 = workbook.createFont(); font7.setFontHeightInPoints((short) 6); Font fontTerritorio = workbook.createFont(); fontTerritorio.setItalic(true);/*from w w w .j a va 2s. c o m*/ fontTerritorio.setFontHeightInPoints((short) 12); fontTerritorio.setBoldweight(Font.BOLDWEIGHT_BOLD); Font fontTerritorioTotal1 = workbook.createFont(); fontTerritorioTotal1.setItalic(true); fontTerritorioTotal1.setFontHeightInPoints((short) 8); fontTerritorioTotal1.setBoldweight(Font.BOLDWEIGHT_BOLD); Font fontTitulo9 = workbook.createFont(); fontTitulo9.setFontHeightInPoints((short) 8); Font fontTitulo = workbook.createFont(); fontTitulo.setFontHeightInPoints((short) 12); fontTitulo.setBoldweight(Font.BOLDWEIGHT_BOLD); styleTitulo = workbook.createCellStyle(); styleTitulo.setFont(fontTitulo); styleTitulo.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styleTerritorio = workbook.createCellStyle(); styleTerritorio.setFont(fontTerritorio); styleTerritorioTotal1 = workbook.createCellStyle(); styleTerritorioTotal1.setFont(fontTerritorioTotal1); styleTerritorioTotal1.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleTerritorioTotal1.setBorderTop(HSSFCellStyle.BORDER_THIN); //styleCantidad.setBorderRight(HSSFCellStyle.BORDER_THIN); styleTerritorioTotal1.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleTitulo9 = workbook.createCellStyle(); styleTitulo9.setFont(fontTitulo9); styleTitulo9.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleTitulo9.setBorderTop(HSSFCellStyle.BORDER_THIN); //styleCantidad.setBorderRight(HSSFCellStyle.BORDER_THIN); styleTitulo9.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleSubTitulo = workbook.createCellStyle(); styleSubTitulo.setFont(fontSubTitulo); styleSubTitulo.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styleSubTitulo.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleSubTitulo.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleSubTitulo.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); styleSubTitulo.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleSubTituloCaje = workbook.createCellStyle(); styleSubTituloCaje.setFont(fontSubTituloCaje); styleSubTituloCaje.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styleSubTituloCaje.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleSubTituloCaje.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleSubTituloCaje.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); styleSubTituloCaje.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleMergeCentrado = workbook.createCellStyle(); styleMergeCentrado.setFont(fontSubTituloAzul); styleMergeCentrado.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styleMergeCentrado.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleMergeCentrado.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleMergeCentrado.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleMergeCentrado.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleMergeCentrado.setWrapText(true); styleMergeTotal = workbook.createCellStyle(); styleMergeTotal.setFont(fontSubTitulo); styleMergeTotal.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); styleMergeTotal.setFillPattern(CellStyle.SOLID_FOREGROUND); styleMergeTotal.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styleMergeTotal.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleMergeTotal.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleMergeTotal.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); styleMergeTotal.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleMergeTotal.setAlignment(HSSFCellStyle.ALIGN_CENTER); DataFormat format = workbook.createDataFormat(); styleCantidadAzul = workbook.createCellStyle(); styleCantidadAzul.setFont(fontSubTituloAzul); styleCantidadAzul.setDataFormat(format.getFormat("#,##0")); styleCantidadAzul.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleCantidadAzul.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleCantidadAzul.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); styleCantidadAzul.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleCantidadAzul.setAlignment(HSSFCellStyle.ALIGN_RIGHT); styleCantidadNegrita = workbook.createCellStyle(); styleCantidadNegrita.setFont(fontSubTitulo); styleCantidadNegrita.setDataFormat(format.getFormat("#,##0")); styleCantidadNegrita.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleCantidadNegrita.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleCantidadNegrita.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); styleCantidadNegrita.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleCantidadGris = workbook.createCellStyle(); styleCantidadGris.setFont(fontSubTitulo); styleCantidadGris.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); styleCantidadGris.setFillPattern(CellStyle.SOLID_FOREGROUND); styleCantidadGris.setDataFormat(format.getFormat("#,##0")); styleCantidadGris.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleCantidadGris.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleCantidadGris.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); styleCantidadGris.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleCantidad = workbook.createCellStyle(); styleCantidad.setFont(fontTitulo9); styleCantidad.setDataFormat(format.getFormat("#,##0")); styleCantidad.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCantidad.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCantidad.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCantidad.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCantidadGris1 = workbook.createCellStyle(); styleCantidadGris1.setFont(fontTitulo9); styleCantidadGris1.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); styleCantidadGris1.setFillPattern(CellStyle.SOLID_FOREGROUND); styleCantidadGris1.setDataFormat(format.getFormat("#,##0")); styleCantidadGris1.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCantidadGris1.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCantidadGris1.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCantidadGris1.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCantidadCaje = workbook.createCellStyle(); styleCantidadCaje.setFont(font7); styleCantidadCaje.setDataFormat(format.getFormat("#,##0")); styleCantidadCaje.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCantidadCaje.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCantidadCaje.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCantidadCaje.setBorderLeft(HSSFCellStyle.BORDER_THIN); HSSFRow rowTitulo = sheet.createRow(0); HSSFCell cellTitulo = rowTitulo.createCell(18); cellTitulo.setCellValue("CONTROL DIARIO DE VENTAS"); cellTitulo.setCellStyle(styleTitulo); HSSFRow rowFecha = sheet.createRow(++indexRow); HSSFCell cellFecha = rowFecha.createCell(18); // SimpleDateFormat sdf = new SimpleDateFormat("EEEE, dd 'de' MMMM 'de' yyyy", new Locale("es", "py")); SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy", new Locale("es", "py")); cellFecha.setCellValue("De " + sdf.format(inicio) + " al " + sdf.format(fin)); //Calendar calender = Calendar.getInstance(); //cellFecha.setCellValue(calender.get(getInicio())); cellFecha.setCellStyle(styleTitulo); ++indexRow; HSSFRow rowCabeceraMarca = sheet.createRow((++indexRow)); HSSFRow rowCabeceraProducto = sheet.createRow((++indexRow)); HSSFRow rowCabecerauM = sheet.createRow((++indexRow)); rowCabeceraProducto.setHeightInPoints((2 * sheet.getDefaultRowHeightInPoints())); HSSFCell cell0 = rowCabecerauM.createCell(indexCol); cell0.setCellValue("Zona"); cell0.setCellStyle(styleSubTitulo); HSSFCell cell1 = rowCabecerauM.createCell(indexCol + 1); cell1.setCellValue("Vendedor"); cell1.setCellStyle(styleSubTitulo); HSSFCell cell2 = rowCabecerauM.createCell(indexCol + 2); cell2.setCellValue("Boletas"); cell2.setCellStyle(styleSubTitulo); HSSFCell cell3 = rowCabeceraProducto.createCell(indexCol + 3); HSSFCell cell4 = rowCabeceraProducto.createCell(indexCol + 4); cell3.setCellValue("Palermo Red Box 20"); cell3.setCellStyle(styleMergeCentrado); cell4.setCellValue(""); cell4.setCellStyle(styleMergeCentrado); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 3) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 4) + "$" + indexRow)); HSSFCell cell3g = rowCabecerauM.createCell(indexCol + 3); cell3g.setCellValue("Grue"); cell3g.setCellStyle(styleMergeCentrado); HSSFCell cell3c = rowCabecerauM.createCell(indexCol + 4); cell3c.setCellValue("Caj"); cell3c.setCellStyle(styleSubTituloCaje); HSSFCell cell5 = (rowCabeceraProducto).createCell(indexCol + 5); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 5) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 6) + "$" + indexRow)); cell5.setCellValue("Palermo Blue Box 20"); cell5.setCellStyle(styleMergeCentrado); HSSFCell cell5g = rowCabecerauM.createCell(indexCol + 5); cell5g.setCellValue("Grue"); cell5g.setCellStyle(styleMergeCentrado); HSSFCell cell5c = rowCabecerauM.createCell(indexCol + 6); cell5c.setCellValue("Caj"); cell5c.setCellStyle(styleSubTituloCaje); HSSFCell cell7 = (rowCabeceraProducto).createCell(indexCol + 7); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 7) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 8) + "$" + indexRow)); cell7.setCellValue("Palermo Green Box 20"); cell7.setCellStyle(styleMergeCentrado); HSSFCell cell7g = rowCabecerauM.createCell(indexCol + 7); cell7g.setCellValue("Grue"); cell7g.setCellStyle(styleMergeCentrado); HSSFCell cell7c = rowCabecerauM.createCell(indexCol + 8); cell7c.setCellValue("Caj"); cell7c.setCellStyle(styleSubTituloCaje); HSSFCell cell9 = (rowCabeceraProducto).createCell(indexCol + 9); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 9) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 10) + "$" + indexRow)); cell9.setCellValue("Palermo Tres Box 20"); cell9.setCellStyle(styleMergeCentrado); HSSFCell cell9g = rowCabecerauM.createCell(indexCol + 9); cell9g.setCellValue("Grue"); cell9g.setCellStyle(styleMergeCentrado); HSSFCell cell9c = rowCabecerauM.createCell(indexCol + 10); cell9c.setCellValue("Caj"); cell9c.setCellStyle(styleSubTituloCaje); HSSFCell cell11 = (rowCabeceraProducto).createCell(indexCol + 11); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 11) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 12) + "$" + indexRow)); cell11.setCellValue("Palermo Duo Box 20"); cell11.setCellStyle(styleMergeCentrado); HSSFCell cell11g = rowCabecerauM.createCell(indexCol + 11); cell11g.setCellValue("Grue"); cell11g.setCellStyle(styleMergeCentrado); HSSFCell cell11c = rowCabecerauM.createCell(indexCol + 12); cell11c.setCellValue("Caj"); cell11c.setCellStyle(styleSubTituloCaje); HSSFCell cell13 = rowCabeceraProducto.createCell(indexCol + 13); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 13) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 14) + "$" + indexRow)); cell13.setCellValue("Palermo Red Box 10"); cell13.setCellStyle(styleMergeCentrado); HSSFCell cell13g = rowCabecerauM.createCell(indexCol + 13); cell13g.setCellValue("Grue"); cell13g.setCellStyle(styleMergeCentrado); HSSFCell cell13c = rowCabecerauM.createCell(indexCol + 14); cell13c.setCellValue("Caj"); cell13c.setCellStyle(styleSubTituloCaje); HSSFCell cell15 = (rowCabeceraProducto).createCell(indexCol + 15); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 15) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 16) + "$" + indexRow)); cell15.setCellValue("Palermo Blue Box 10"); cell15.setCellStyle(styleMergeCentrado); HSSFCell cell15g = rowCabecerauM.createCell(indexCol + 15); cell15g.setCellValue("Grue"); cell15g.setCellStyle(styleMergeCentrado); HSSFCell cell15c = rowCabecerauM.createCell(indexCol + 16); cell15c.setCellValue("Caj"); cell15c.setCellStyle(styleSubTituloCaje); HSSFCell cell17 = (rowCabeceraProducto).createCell(indexCol + 17); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 17) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 18) + "$" + indexRow)); cell17.setCellValue("Palermo Green Box 10"); cell17.setCellStyle(styleMergeCentrado); HSSFCell cell17g = rowCabecerauM.createCell(indexCol + 17); cell17g.setCellValue("Grue"); cell17g.setCellStyle(styleMergeCentrado); HSSFCell cell17c = rowCabecerauM.createCell(indexCol + 18); cell17c.setCellValue("Caj"); cell17c.setCellStyle(styleSubTituloCaje); HSSFCell cell19 = (rowCabeceraProducto).createCell(indexCol + 19); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 19) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 20) + "$" + indexRow)); cell19.setCellValue("Palermo Tres Box 10"); cell19.setCellStyle(styleMergeCentrado); HSSFCell cell19g = rowCabecerauM.createCell(indexCol + 19); cell19g.setCellValue("Grue"); cell19g.setCellStyle(styleMergeCentrado); HSSFCell cell19c = rowCabecerauM.createCell(indexCol + 20); cell19c.setCellValue("Caj"); cell19c.setCellStyle(styleSubTituloCaje); HSSFCell cell21 = (rowCabeceraProducto).createCell(indexCol + 21); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 21) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 22) + "$" + indexRow)); cell21.setCellValue("Palermo Duo Box 10"); cell21.setCellStyle(styleMergeCentrado); HSSFCell cell21g = rowCabecerauM.createCell(indexCol + 21); cell21g.setCellValue("Grue"); cell21g.setCellStyle(styleMergeCentrado); HSSFCell cell21c = rowCabecerauM.createCell(indexCol + 22); cell21c.setCellValue("Caj"); cell21c.setCellStyle(styleSubTituloCaje); HSSFCell cell23 = (rowCabeceraProducto).createCell(indexCol + 23); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 23) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 24) + "$" + indexRow)); cell23.setCellValue("San Marino TYPE Box 20"); cell23.setCellStyle(styleMergeCentrado); HSSFCell cell23g = rowCabecerauM.createCell(indexCol + 23); cell23g.setCellValue("Grue"); cell23g.setCellStyle(styleMergeCentrado); HSSFCell cell23c = rowCabecerauM.createCell(indexCol + 24); cell23c.setCellValue("Caj"); cell23c.setCellStyle(styleSubTituloCaje); HSSFCell cell25 = (rowCabeceraProducto).createCell(indexCol + 25); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 25) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 26) + "$" + indexRow)); cell25.setCellValue("San Marino TYPE Box 10"); cell25.setCellStyle(styleMergeCentrado); HSSFCell cell25g = rowCabecerauM.createCell(indexCol + 25); cell25g.setCellValue("Grue"); cell25g.setCellStyle(styleMergeCentrado); HSSFCell cell25c = rowCabecerauM.createCell(indexCol + 26); cell25c.setCellValue("Caj"); cell25c.setCellStyle(styleSubTituloCaje); HSSFCell cell27 = (rowCabeceraProducto).createCell(indexCol + 27); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 27) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 28) + "$" + indexRow)); cell27.setCellValue("San Marino TYPE Soft"); cell27.setCellStyle(styleMergeCentrado); HSSFCell cell27g = rowCabecerauM.createCell(indexCol + 27); cell27g.setCellValue("Grue"); cell27g.setCellStyle(styleMergeCentrado); HSSFCell cell27c = rowCabecerauM.createCell(indexCol + 28); cell27c.setCellValue("Caj"); cell27c.setCellStyle(styleSubTituloCaje); HSSFCell cell29 = (rowCabeceraProducto).createCell(indexCol + 29); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 29) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 30) + "$" + indexRow)); cell29.setCellValue("Kentucky Box 20"); cell29.setCellStyle(styleMergeCentrado); HSSFCell cell29g = rowCabecerauM.createCell(indexCol + 29); cell29g.setCellValue("Grue"); cell29g.setCellStyle(styleMergeCentrado); HSSFCell cell29c = rowCabecerauM.createCell(indexCol + 30); cell29c.setCellValue("Caj"); cell29c.setCellStyle(styleSubTituloCaje); HSSFCell cell31 = (rowCabeceraProducto).createCell(indexCol + 31); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 31) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 32) + "$" + indexRow)); cell31.setCellValue("Kentucky Box 10"); cell31.setCellStyle(styleMergeCentrado); HSSFCell cell31g = rowCabecerauM.createCell(indexCol + 31); cell31g.setCellValue("Grue"); cell31g.setCellStyle(styleMergeCentrado); HSSFCell cell31c = rowCabecerauM.createCell(indexCol + 32); cell31c.setCellValue("Caj"); cell31c.setCellStyle(styleSubTituloCaje); HSSFCell cell33 = (rowCabeceraProducto).createCell(indexCol + 33); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 33) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 34) + "$" + indexRow)); cell33.setCellValue("Kentucky Soft"); cell33.setCellStyle(styleMergeCentrado); HSSFCell cell33g = rowCabecerauM.createCell(indexCol + 33); cell33g.setCellValue("Grue"); cell33g.setCellStyle(styleMergeCentrado); HSSFCell cell33c = rowCabecerauM.createCell(indexCol + 34); cell33c.setCellValue("Caj"); cell33c.setCellStyle(styleSubTituloCaje); HSSFCell cell35 = rowCabeceraProducto.createCell(indexCol + 35); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 35) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 36) + "$" + indexRow)); cell35.setCellValue("Total"); cell35.setCellStyle(styleMergeTotal); HSSFCell cell38 = rowCabeceraProducto.createCell(indexCol + 36); cell38.setCellValue(""); cell38.setCellStyle(styleMergeTotal); HSSFCell cell35g = rowCabecerauM.createCell(indexCol + 35); cell35g.setCellValue("Gruesas"); cell35g.setCellStyle(styleSubTitulo); HSSFCell cell35c = rowCabecerauM.createCell(indexCol + 36); cell35c.setCellValue("Caje"); cell35c.setCellStyle(styleSubTitulo); HSSFCell cell37c = rowCabecerauM.createCell(indexCol + 37); cell37c.setCellValue("Cajas"); cell37c.setCellStyle(styleSubTitulo); HSSFCell cell38g = rowCabecerauM.createCell(indexCol + 38); cell38g.setCellValue("+gr"); cell38g.setCellStyle(styleSubTitulo); sheet.createFreezePane(2, 7); }
From source file:com.mss.mirage.employee.general.EmployeeServiceImpl.java
License:Open Source License
public String generateEmployeeList(String loginId) { DateUtility dateutility = new DateUtility(); String filePath = ""; StringBuffer sb = null;/* w w w .j a va 2 s . c o m*/ Connection connection = null; /** callableStatement is a reference variable for CallableStatement . */ CallableStatement callableStatement = null; /** preStmt,preStmtTemp are reference variable for PreparedStatement . */ PreparedStatement preStmt = null, preStmtTemp = null; /** The queryString is useful to get queryString result to the particular jsp page */ String queryString = ""; Statement statement = null; /** The statement is useful to execute the above queryString */ ResultSet resultSet = null; String timeSheetStatus = ""; HashMap map = null; HashMap map1 = null; List finalList = new ArrayList(); try { String TABLE_EMP_STATE_HISTORY = Properties.getProperty("TABLE_EMP_STATE_HISTORY"); File file = new File(Properties.getProperty("Emp.StateHistory.Path")); if (!file.exists()) { file.mkdirs(); } FileOutputStream fileOut = new FileOutputStream( file.getAbsolutePath() + File.separator + loginId + "_StateHistory.xls"); connection = ConnectionProvider.getInstance().getConnection(); String query = null; // query = "SELECT * FROM tblEmpStateHistory WHERE LoginId='"+loginId+"' ORDER BY StartDate DESC"; query = "SELECT * FROM " + TABLE_EMP_STATE_HISTORY + " WHERE LoginId='" + loginId + "' ORDER BY StartDate DESC"; // System.out.println("query123-->"+query); String reportToName = ""; List teamList = null; int j = 1; preStmt = connection.prepareStatement(query); resultSet = preStmt.executeQuery(); while (resultSet.next()) { String state = ""; if (!"".equals(resultSet.getString("State")) && resultSet.getString("State") != null) { state = resultSet.getString("State"); } double IntRatePerHour = resultSet.getFloat("IntRatePerHour"); double InvRatePerHour = resultSet.getFloat("InvRatePerHour"); String LoginId = resultSet.getString("LoginId"); String SkillSet = ""; if (!"".equals(resultSet.getString("SkillSet")) && resultSet.getString("SkillSet") != null) { SkillSet = resultSet.getString("SkillSet"); } String EndDate = ""; if (!"".equals(resultSet.getString("EndDate")) && resultSet.getString("EndDate") != null) { EndDate = resultSet.getString("EndDate"); } String StartDate = ""; if (!"".equals(resultSet.getString("StartDate")) && resultSet.getString("StartDate") != null) { StartDate = resultSet.getString("StartDate"); } String CreatedDate = ""; if (!"".equals(resultSet.getString("CreatedDate")) && resultSet.getString("CreatedDate") != null) { CreatedDate = resultSet.getString("CreatedDate"); } String PrjName = ""; if (!"".equals(resultSet.getString("PrjName")) && resultSet.getString("PrjName") != null) { PrjName = resultSet.getString("PrjName"); } String reportsTo = ""; map = new HashMap(); map.put("SNO", String.valueOf(j)); map.put("state", state); map.put("IntRatePerHour", IntRatePerHour); map.put("InvRatePerHour", InvRatePerHour); map.put("LoginId", LoginId); map.put("SkillSet", SkillSet); map.put("EndDate", EndDate); map.put("StartDate", StartDate); map.put("CreatedDate", CreatedDate); map.put("PrjName", PrjName); //System.out.println("map=="+map); finalList.add(map); j++; } if (finalList.size() > 0) { filePath = file.getAbsolutePath() + File.separator + loginId + "_StateHistory.xls"; HSSFWorkbook hssfworkbook = new HSSFWorkbook(); HSSFSheet sheet = hssfworkbook.createSheet("State History"); HSSFCellStyle cs = hssfworkbook.createCellStyle(); HSSFCellStyle headercs = hssfworkbook.createCellStyle(); headercs.setFillForegroundColor(HSSFColor.BLACK.index); headercs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headercs.setBorderTop((short) 1); // single line border headercs.setBorderBottom((short) 1); // single line border HSSFFont timesBoldFont = hssfworkbook.createFont(); timesBoldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); timesBoldFont.setColor(HSSFColor.WHITE.index); timesBoldFont.setFontName("Arial"); headercs.setFont(timesBoldFont); HSSFFont footerFont = hssfworkbook.createFont(); footerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); footerFont.setFontName("Arial"); HSSFCellStyle footercs = hssfworkbook.createCellStyle(); footercs.setFont(footerFont); HSSFDataFormat df = hssfworkbook.createDataFormat(); HSSFRow row = sheet.createRow((short) 0); HSSFCell cell = row.createCell((short) 0); HSSFCell cell1 = row.createCell((short) 1); HSSFCell cell2 = row.createCell((short) 2); HSSFCell cell3 = row.createCell((short) 3); HSSFCell cell4 = row.createCell((short) 4); HSSFCell cell5 = row.createCell((short) 5); HSSFCell cell6 = row.createCell((short) 6); HSSFCell cell7 = row.createCell((short) 7); HSSFCell cell8 = row.createCell((short) 8); cell.setCellValue("SNO"); cell1.setCellValue("State"); cell2.setCellValue("StartDate"); cell3.setCellValue("EndDate"); cell4.setCellValue("IntRatePerHour"); cell5.setCellValue("InvRatePerHour"); cell6.setCellValue("SkillSet"); cell7.setCellValue("ProjectName"); cell8.setCellValue("CreatedDate"); cell.setCellStyle(headercs); cell1.setCellStyle(headercs); cell2.setCellStyle(headercs); cell3.setCellStyle(headercs); cell4.setCellStyle(headercs); cell5.setCellStyle(headercs); cell6.setCellStyle(headercs); cell7.setCellStyle(headercs); cell8.setCellStyle(headercs); int count = 1; if (finalList.size() > 0) { Map stateHistorylMap = null; for (int i = 0; i < finalList.size(); i++) { stateHistorylMap = (Map) finalList.get(i); row = sheet.createRow((short) count++); cell = row.createCell((short) 0); cell1 = row.createCell((short) 1); cell2 = row.createCell((short) 2); cell3 = row.createCell((short) 3); cell4 = row.createCell((short) 4); cell5 = row.createCell((short) 5); cell6 = row.createCell((short) 6); cell7 = row.createCell((short) 7); cell8 = row.createCell((short) 8); cell.setCellValue((String) stateHistorylMap.get("SNO")); cell1.setCellValue((String) stateHistorylMap.get("state")); cell2.setCellValue((String) stateHistorylMap.get("StartDate")); cell3.setCellValue((String) stateHistorylMap.get("EndDate")); cell4.setCellValue((Double) stateHistorylMap.get("IntRatePerHour")); cell5.setCellValue((Double) stateHistorylMap.get("InvRatePerHour")); cell6.setCellValue((String) stateHistorylMap.get("SkillSet")); cell7.setCellValue((String) stateHistorylMap.get("PrjName")); cell8.setCellValue((String) stateHistorylMap.get("CreatedDate")); cell.setCellStyle(cs); cell1.setCellStyle(cs); cell2.setCellStyle(cs); cell3.setCellStyle(cs); cell4.setCellStyle(cs); cell5.setCellStyle(cs); cell6.setCellStyle(cs); cell7.setCellStyle(cs); cell8.setCellStyle(cs); } row = sheet.createRow((short) count++); cell = row.createCell((short) 0); cell1 = row.createCell((short) 1); cell2 = row.createCell((short) 2); cell3 = row.createCell((short) 3); cell4 = row.createCell((short) 4); cell.setCellValue(""); cell4.setCellValue(""); cell.setCellStyle(footercs); cell1.setCellStyle(footercs); cell2.setCellStyle(footercs); cell3.setCellStyle(footercs); cell4.setCellStyle(footercs); } sheet.autoSizeColumn((int) 0); sheet.autoSizeColumn((int) 1); sheet.autoSizeColumn((int) 2); sheet.autoSizeColumn((int) 3); sheet.autoSizeColumn((int) 4); hssfworkbook.write(fileOut); fileOut.flush(); fileOut.close(); } } catch (FileNotFoundException fne) { fne.printStackTrace(); } catch (IOException ioe) { ioe.printStackTrace(); } catch (Exception ex) { ex.printStackTrace(); } finally { System.out.println("finally"); try { if (resultSet != null) { resultSet.close(); resultSet = null; } if (preStmt != null) { preStmt.close(); preStmt = null; } if (connection != null) { connection.close(); connection = null; } } catch (Exception se) { se.printStackTrace(); } } return filePath; }
From source file:com.mss.mirage.marketing.MarketingServiceImpl.java
License:Open Source License
public String generateInvestmentXls(String queryString) throws ServiceLocatorException { String filePath = ""; StringBuffer sb = null;// w ww . ja v a2 s .c o m Connection connection = null; /** * preStmt,preStmtTemp are reference variable for PreparedStatement . */ PreparedStatement preStmt = null; /** * The queryString is useful to get queryString result to the particular * jsp page */ /** * The statement is useful to execute the above queryString */ ResultSet resultSet = null; HashMap map = null; double totalAmount = 0.0; double totalOpprtunity = 0.0; double floortotalsum = 0.0; String generatedPath = ""; List finalList = new ArrayList(); try { generatedPath = com.mss.mirage.util.Properties.getProperty("Marketing.Investment.Path"); File file = new File(generatedPath); if (!file.exists()) { file.mkdirs(); } FileOutputStream fileOut = new FileOutputStream(file.getAbsolutePath() + "/Investment.xls"); connection = ConnectionProvider.getInstance().getConnection(); String query = null; if (!"".equals(queryString)) { query = queryString; } else { query = "SELECT * from vwInvestments WHERE STATUS='Active' ORDER BY createdDate DESC"; } String reportToName = ""; List teamList = null; int j = 1; // System.out.println("query...."+query); preStmt = connection.prepareStatement(query); resultSet = preStmt.executeQuery(); while (resultSet.next()) { String InvestmentName = resultSet.getString("Inv_Name"); String TotalExpenses = resultSet.getString("TotalExpenses"); String StartDate = resultSet.getString("StartDate"); String EndDate = resultSet.getString("EndDate"); String Location = resultSet.getString("Location"); String InvestmentType = resultSet.getString("InvestmentType"); String TotalOpprtunity = resultSet.getString("TotalOpprtunity"); totalAmount = totalAmount + resultSet.getDouble("TotalExpenses"); totalOpprtunity = totalOpprtunity + resultSet.getDouble("TotalOpprtunity"); map = new HashMap(); map.put("SNO", String.valueOf(j)); map.put("InvestmentName", InvestmentName); map.put("TotalExpenses", TotalExpenses); map.put("StartDate", StartDate); map.put("EndDate", EndDate); map.put("Location", Location); if ("S".equalsIgnoreCase(InvestmentType)) { map.put("InvestmentType", "Lead Source"); } else if ("P".equalsIgnoreCase(InvestmentType)) { map.put("InvestmentType", "Lead Pass"); } map.put("TotalOpprtunity", TotalOpprtunity); map.put("Sum", totalAmount); map.put("SumOpp", totalOpprtunity); finalList.add(map); j++; } if (finalList.size() > 0) { filePath = file.getAbsolutePath() + "/Investment.xls"; HSSFWorkbook hssfworkbook = new HSSFWorkbook(); HSSFSheet sheet = hssfworkbook.createSheet("Investment Sheet"); HSSFFont timesBoldFont1 = hssfworkbook.createFont(); timesBoldFont1.setFontHeightInPoints((short) 13); timesBoldFont1.setColor(HSSFColor.BLACK.index); timesBoldFont1.setFontName("Arial"); HSSFCellStyle cellColor = hssfworkbook.createCellStyle(); cellColor.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); cellColor.setAlignment(HSSFCellStyle.ALIGN_LEFT); cellColor.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellColor.setBorderTop((short) 1); // single line border cellColor.setBorderBottom((short) 1); // single line border cellColor.setFont(timesBoldFont1); HSSFCellStyle cellColor1 = hssfworkbook.createCellStyle(); cellColor1.setFillForegroundColor(HSSFColor.WHITE.index); cellColor1.setAlignment(HSSFCellStyle.ALIGN_LEFT); cellColor1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellColor1.setBorderTop((short) 1); // single line border cellColor1.setBorderBottom((short) 1); // single line border cellColor1.setFont(timesBoldFont1); HSSFCellStyle cs = hssfworkbook.createCellStyle(); HSSFCellStyle headercs = hssfworkbook.createCellStyle(); headercs.setFillForegroundColor(HSSFColor.BLUE.index); headercs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headercs.setBorderTop((short) 1); // single line border headercs.setBorderBottom((short) 1); // single line border // cs.setFont(timesBoldFont1); HSSFFont timesBoldFont = hssfworkbook.createFont(); timesBoldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); timesBoldFont.setFontHeightInPoints((short) 13); timesBoldFont.setColor(HSSFColor.WHITE.index); timesBoldFont.setFontName("Calibri"); headercs.setFont(timesBoldFont); // cs.setFont(timesBoldFont); HSSFFont footerFont = hssfworkbook.createFont(); footerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); timesBoldFont.setFontHeightInPoints((short) 13); footerFont.setFontName("Calibri"); HSSFCellStyle footercs = hssfworkbook.createCellStyle(); footercs.setFont(footerFont); HSSFDataFormat df = hssfworkbook.createDataFormat(); HSSFRow row = sheet.createRow((short) 0); HSSFCell cell = row.createCell((short) 0); HSSFCell cell1 = row.createCell((short) 1); HSSFCell cell2 = row.createCell((short) 2); HSSFCell cell3 = row.createCell((short) 3); HSSFCell cell4 = row.createCell((short) 4); HSSFCell cell5 = row.createCell((short) 5); HSSFCell cell6 = row.createCell((short) 6); HSSFCell cell7 = row.createCell((short) 7); cell.setCellValue("SNO"); cell1.setCellValue("InvestmentName"); cell2.setCellValue("TotalExpenses"); cell3.setCellValue("StartDate"); cell4.setCellValue("EndDate"); cell5.setCellValue("Location"); cell6.setCellValue("InvestmentType"); cell7.setCellValue("TotalOpprtunity"); cell.setCellStyle(headercs); cell1.setCellStyle(headercs); cell2.setCellStyle(headercs); cell3.setCellStyle(headercs); cell4.setCellStyle(headercs); cell5.setCellStyle(headercs); cell6.setCellStyle(headercs); cell7.setCellStyle(headercs); int count = 1; if (finalList.size() > 0) { Map stateHistorylMap = null; for (int i = 0; i < finalList.size(); i++) { stateHistorylMap = (Map) finalList.get(i); row = sheet.createRow((short) count++); cell = row.createCell((short) 0); cell1 = row.createCell((short) 1); cell2 = row.createCell((short) 2); cell3 = row.createCell((short) 3); cell4 = row.createCell((short) 4); cell5 = row.createCell((short) 5); cell6 = row.createCell((short) 6); cell7 = row.createCell((short) 7); cell.setCellValue((String) stateHistorylMap.get("SNO")); cell1.setCellValue((String) stateHistorylMap.get("InvestmentName")); HSSFCellStyle css1 = hssfworkbook.createCellStyle(); HSSFCellStyle css2 = hssfworkbook.createCellStyle(); css1.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); css1.setAlignment(HSSFCellStyle.ALIGN_LEFT); css1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); css1.setBorderTop((short) 1); // single line border css1.setBorderBottom((short) 1); // single line border css1.setFont(timesBoldFont1); HSSFDataFormat df1 = hssfworkbook.createDataFormat(); css1.setDataFormat(df1.getFormat("#,##0.0")); css2.setDataFormat(df1.getFormat("#,##0.0")); css2.setAlignment(HSSFCellStyle.ALIGN_LEFT); css2.setFont(timesBoldFont1); cell2.setCellValue(Convert.toDouble(stateHistorylMap.get("TotalExpenses"))); cell3.setCellValue((String) stateHistorylMap.get("StartDate")); cell4.setCellValue((String) stateHistorylMap.get("EndDate")); cell5.setCellValue((String) stateHistorylMap.get("Location")); cell6.setCellValue((String) stateHistorylMap.get("InvestmentType")); cell7.setCellValue((String) stateHistorylMap.get("TotalOpprtunity")); if (count % 2 == 0) { cell.setCellStyle(cellColor1); cell1.setCellStyle(cellColor1); cell2.setCellStyle(css2); cell3.setCellStyle(cellColor1); cell4.setCellStyle(cellColor1); cell5.setCellStyle(cellColor1); cell6.setCellStyle(cellColor1); cell7.setCellStyle(cellColor1); } else { cell.setCellStyle(cellColor); cell1.setCellStyle(cellColor); cell2.setCellStyle(css1); cell3.setCellStyle(cellColor); cell4.setCellStyle(cellColor); cell5.setCellStyle(cellColor); cell6.setCellStyle(cellColor); cell7.setCellStyle(cellColor); } } row = sheet.createRow((short) count++); cell = row.createCell((short) 0); cell1 = row.createCell((short) 1); cell2 = row.createCell((short) 2); cell3 = row.createCell((short) 3); cell4 = row.createCell((short) 4); cell5 = row.createCell((short) 5); cell6 = row.createCell((short) 6); cell7 = row.createCell((short) 7); cell.setCellValue(""); cell7.setCellValue(""); cell.setCellStyle(footercs); cell1.setCellStyle(footercs); cell2.setCellStyle(footercs); cell3.setCellStyle(footercs); cell4.setCellStyle(footercs); cell5.setCellStyle(footercs); cell6.setCellStyle(footercs); cell7.setCellStyle(footercs); } HSSFCellStyle totalSum = hssfworkbook.createCellStyle(); totalSum.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index); totalSum.setAlignment(HSSFCellStyle.ALIGN_LEFT); totalSum.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); totalSum.setBorderTop((short) 1); // single line border totalSum.setBorderBottom((short) 1); // single line border totalSum.setFont(timesBoldFont1); HSSFCellStyle totalSum1 = hssfworkbook.createCellStyle(); totalSum1.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index); totalSum1.setAlignment(HSSFCellStyle.ALIGN_RIGHT); totalSum1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); totalSum1.setBorderTop((short) 1); // single line border totalSum1.setBorderBottom((short) 1); // single line border totalSum1.setFont(timesBoldFont1); HSSFDataFormat totalSumdf1 = hssfworkbook.createDataFormat(); totalSum.setDataFormat((short) 7); HSSFCellStyle test = hssfworkbook.createCellStyle(); HSSFDataFormat testdf = hssfworkbook.createDataFormat(); sheet.autoSizeColumn((int) 0); sheet.autoSizeColumn((int) 3); sheet.autoSizeColumn((int) 4); sheet.setColumnWidth(1, 50 * 256); sheet.setColumnWidth(2, 35 * 256); sheet.setColumnWidth(5, 25 * 256); sheet.setColumnWidth(6, 25 * 256); sheet.setColumnWidth(7, 25 * 256); BigDecimal bb, bc, cc, cd; bb = new BigDecimal(totalAmount); bc = bb.setScale(2, RoundingMode.CEILING); cc = new BigDecimal(totalOpprtunity); cd = cc.setScale(2, RoundingMode.CEILING); totalSum.setDataFormat(testdf.getFormat("#,##0.0")); cell.setCellStyle(totalSum); cell1.setCellValue("Sum "); cell1.setCellStyle(totalSum1); cell2.setCellValue(bc.longValue()); cell2.setCellStyle(totalSum); cell3.setCellStyle(totalSum); cell4.setCellStyle(totalSum); cell5.setCellStyle(totalSum); cell6.setCellStyle(totalSum); cell7.setCellValue(cd.longValue()); cell7.setCellStyle(totalSum); hssfworkbook.write(fileOut); fileOut.flush(); fileOut.close(); } } catch (FileNotFoundException fne) { fne.printStackTrace(); } catch (IOException ioe) { ioe.printStackTrace(); } catch (Exception ex) { ex.printStackTrace(); } finally { try { if (resultSet != null) { resultSet.close(); resultSet = null; } if (preStmt != null) { preStmt.close(); preStmt = null; } if (connection != null) { connection.close(); connection = null; } } catch (Exception se) { se.printStackTrace(); } } return filePath; }
From source file:com.openitech.util.HSSFWrapper.java
License:Apache License
public static final HSSFWorkbook getWorkbook(JTable source, boolean countRows) { HSSFWorkbook xls_workbook = new HSSFWorkbook(); HSSFSheet xls_sheet = xls_workbook.createSheet("Pregled podatkov"); HSSFPrintSetup xls_sheet_printsetup = xls_sheet.getPrintSetup(); xls_sheet_printsetup.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); xls_sheet_printsetup.setFitWidth((short) 1); TableColumnModel columnModel = source.getColumnModel(); Enumeration<TableColumn> columns = columnModel.getColumns(); HSSFRow xls_row = xls_sheet.createRow(0); short cell = 1; HSSFCellStyle xls_header_cell_style = xls_workbook.createCellStyle(); HSSFFont xls_header_font = xls_workbook.createFont(); xls_header_font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); xls_header_cell_style.setFont(xls_header_font); xls_header_cell_style.setAlignment(HSSFCellStyle.ALIGN_CENTER); xls_header_cell_style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); xls_header_cell_style.setFillForegroundColor(new HSSFColor.GREY_25_PERCENT().getIndex()); //xls_header_cell_style.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE); java.util.Map<String, HSSFCellStyle> cellStyles = new java.util.HashMap<String, HSSFCellStyle>(); HSSFDataFormat xls_data_format = xls_workbook.createDataFormat(); HSSFCellStyle xls_date_cell_style = xls_workbook.createCellStyle(); xls_date_cell_style.setDataFormat(xls_data_format.getFormat("d.m.yyyy")); cellStyles.put("d.m.yyyy", xls_date_cell_style); HSSFCellStyle xls_double_cell_style = xls_workbook.createCellStyle(); xls_double_cell_style.setDataFormat(xls_data_format.getFormat("#,##0.00")); cellStyles.put("#,##0.00", xls_double_cell_style); while (columns.hasMoreElements()) { TableColumn column = columns.nextElement(); HSSFCell xls_cell = xls_row.createCell(cell++); xls_cell.setCellValue(new HSSFRichTextString(column.getHeaderValue().toString())); xls_cell.setCellStyle(xls_header_cell_style); }//from w w w . ja v a 2 s . co m TableModel tableModel = source.getModel(); DbTableModel dbTableModel = (tableModel instanceof DbTableModel) ? (DbTableModel) tableModel : null; Integer fetchSize = null; if (dbTableModel != null) { try { fetchSize = dbTableModel.getDataSource().getFetchSize(); dbTableModel.getDataSource().setFetchSize(2000); } catch (SQLException ex) { Logger.getLogger(HSSFWrapper.class.getName()).log(Level.WARNING, null, ex); fetchSize = null; } } short row = 1; JWProgressMonitor progress = new JWProgressMonitor((java.awt.Frame) null); progress.setTitle("Izvoz podatkov v Excel"); progress.setMax(tableModel.getRowCount()); progress.setVisible(true); try { while (row <= tableModel.getRowCount()) { xls_row = xls_sheet.createRow(row); cell = 0; HSSFCell xls_cell = xls_row.createCell(cell++); if (countRows) { xls_cell.setCellValue(new HSSFRichTextString(Short.toString(row))); } while (cell <= columnModel.getColumnCount()) { Object value = tableModel.getValueAt(source.convertRowIndexToModel(row - 1), source.convertColumnIndexToModel(cell - 1)); if (value != null) { if (value instanceof DbTableModel.ColumnDescriptor.ValueMethod) { DbTableModel.ColumnDescriptor.ValueMethod vm = (DbTableModel.ColumnDescriptor.ValueMethod) value; if (vm.getColumnNames().size() == 1) { java.util.List<Object> values = vm.getValues(); java.util.List<String> cellFormats = vm.getCellFormats(); for (String cellFormat : cellFormats) { if (cellFormat != null) { if (!cellStyles.containsKey(cellFormat)) { HSSFCellStyle xls_cell_style = xls_workbook.createCellStyle(); xls_cell_style.setDataFormat(xls_data_format.getFormat(cellFormat)); cellStyles.put(cellFormat, xls_cell_style); } } } Object vm_value = values.get(0); HSSFCellStyle xls_cell_style = cellFormats.get(0) == null ? null : cellStyles.get(cellFormats.get(0)); if (vm_value != null) { xls_cell = xls_row.createCell(cell); if (vm_value instanceof java.util.Date) { xls_cell.setCellValue((java.util.Date) vm_value); xls_cell.setCellStyle( xls_cell_style == null ? xls_date_cell_style : xls_cell_style); } else if (vm_value instanceof java.lang.Number) { xls_cell.setCellValue(((java.lang.Number) vm_value).doubleValue()); if ((vm_value instanceof java.math.BigDecimal) || (vm_value instanceof java.lang.Double) || (vm_value instanceof java.lang.Float)) { xls_cell.setCellStyle(xls_cell_style == null ? xls_double_cell_style : xls_cell_style); } } else if (vm_value instanceof java.lang.Boolean) { xls_cell.setCellValue(((java.lang.Boolean) vm_value).booleanValue()); } else { xls_cell.setCellValue(new HSSFRichTextString(value.toString())); } } } else { xls_cell = xls_row.createCell(cell); xls_cell.setCellValue(new HSSFRichTextString(value.toString())); } } else { xls_cell = xls_row.createCell(cell); xls_cell.setCellValue(new HSSFRichTextString(value.toString())); } } cell++; } row++; progress.next(); } for (cell = 0; cell <= columnModel.getColumnCount(); cell++) { xls_sheet.autoSizeColumn(cell); } xls_sheet.createFreezePane(1, 1); } finally { progress.setVisible(false); if (fetchSize != null) { try { dbTableModel.getDataSource().setFetchSize(fetchSize); } catch (SQLException ex) { Logger.getLogger(HSSFWrapper.class.getName()).log(Level.WARNING, null, ex); } } } return xls_workbook; }
From source file:com.project.jsica.cdi.ReporteBean.java
public void reporte2(List<ReportePermisoBean> reporte) { LOG.info("TAMAO reporte: " + reporte.size()); FacesContext fc = FacesContext.getCurrentInstance(); HttpServletResponse response = (HttpServletResponse) fc.getExternalContext().getResponse(); HSSFWorkbook libro = new HSSFWorkbook(); HSSFFont fuente = libro.createFont(); fuente.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle estiloCeldaCabecera = libro.createCellStyle(); estiloCeldaCabecera.setFont(fuente); estiloCeldaCabecera.setAlignment(HSSFCellStyle.ALIGN_CENTER); DataFormat format = libro.createDataFormat(); HSSFCellStyle style = libro.createCellStyle(); style.setDataFormat(format.getFormat("hh:mm:ss")); HSSFCellStyle fechas = libro.createCellStyle(); fechas.setDataFormat(format.getFormat("dd.MM.yyyy")); HSSFSheet hoja = libro.createSheet("hoja 1"); //CREAR LAS CABECERAS String[] cabeceras = { "CODIGO", "NOMBRE", "HORA INICIO", "HORA FIN", "HORAS", "MINUTOS", "FECHA", "MOTIVO" }; HSSFRow filaCabecera = hoja.createRow(0); for (int x = 0; x < cabeceras.length; x++) { HSSFCell cabecera = filaCabecera.createCell(x); cabecera.setCellValue(cabeceras[x]); cabecera.setCellStyle(estiloCeldaCabecera); }//w ww. j a v a 2 s . c o m //FIN DE CABECERAS for (int i = 0; i < reporte.size(); i++) { HSSFRow fila = hoja.createRow(i + 1); HSSFCell columna1 = fila.createCell(0); columna1.setCellValue(reporte.get(i).getCodigo()); HSSFCell columna2 = fila.createCell(1); columna2.setCellValue(reporte.get(i).getNombre()); HSSFCell columna3 = fila.createCell(2); columna3.setCellValue(reporte.get(i).getHoraInicio()); columna3.setCellStyle(style); HSSFCell columna4 = fila.createCell(3); columna4.setCellValue(reporte.get(i).getHoraFin()); columna4.setCellStyle(style); HSSFCell columna5 = fila.createCell(4); columna5.setCellValue(reporte.get(i).getHoras()); HSSFCell columna6 = fila.createCell(5); columna6.setCellValue(reporte.get(i).getMinutos()); HSSFCell columna7 = fila.createCell(6); columna7.setCellValue(reporte.get(i).getFechaReal()); columna7.setCellStyle(fechas); HSSFCell columna8 = fila.createCell(7); columna8.setCellValue(reporte.get(i).getMotivo()); } try { OutputStream output = response.getOutputStream(); libro.write(output); output.close(); fc.responseComplete(); } catch (IOException ex) { LOG.info("ERROR: " + ex); } }