List of usage examples for org.apache.poi.ss.usermodel CellStyle setBorderBottom
void setBorderBottom(BorderStyle border);
From source file:com.ideaspymes.arthyweb.ventas.web.controllers.VentasCantadasBean.java
public void generarExcelVentasCantadas() throws IOException { cargaResumen();// w w w .ja v a 2 s . c om 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();//from w w w . j ava 2 s . c o 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.ipcglobal.fredimport.xls.BaseXls.java
License:Apache License
/** * Adds the border to style.//from w w w. j a v a 2 s. c o m * * @param style the style * @param cellBorder the cell border * @throws Exception the exception */ protected void addBorderToStyle(CellStyle style, CellBorder cellBorder) throws Exception { if (cellBorder == CellBorder.All_Thin || cellBorder == CellBorder.Bottom_Thin) { style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); } if (cellBorder == CellBorder.All_Thin || cellBorder == CellBorder.Left_Thin) { style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); } if (cellBorder == CellBorder.All_Thin || cellBorder == CellBorder.Right_Thin) { style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); } if (cellBorder == CellBorder.All_Thin || cellBorder == CellBorder.Top_Thin) { style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); } if (cellBorder == CellBorder.All_Medium || cellBorder == CellBorder.Bottom_Medium) { style.setBorderBottom(CellStyle.BORDER_MEDIUM); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); } if (cellBorder == CellBorder.All_Medium || cellBorder == CellBorder.Left_Medium) { style.setBorderLeft(CellStyle.BORDER_MEDIUM); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); } if (cellBorder == CellBorder.All_Medium || cellBorder == CellBorder.Right_Medium) { style.setBorderRight(CellStyle.BORDER_MEDIUM); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); } if (cellBorder == CellBorder.All_Medium || cellBorder == CellBorder.Top_Medium) { style.setBorderTop(CellStyle.BORDER_MEDIUM); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); } if (cellBorder == CellBorder.All_Thick || cellBorder == CellBorder.Bottom_Thick) { style.setBorderBottom(CellStyle.BORDER_THICK); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); } if (cellBorder == CellBorder.All_Thick || cellBorder == CellBorder.Left_Thick) { style.setBorderLeft(CellStyle.BORDER_THICK); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); } if (cellBorder == CellBorder.All_Thick || cellBorder == CellBorder.Right_Thick) { style.setBorderRight(CellStyle.BORDER_THICK); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); } if (cellBorder == CellBorder.All_Thick || cellBorder == CellBorder.Top_Thick) { style.setBorderTop(CellStyle.BORDER_THICK); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); } }
From source file:com.liferay.portlet.documentlibrary.action.EditEntryAction.java
License:Open Source License
public static void exportDocumentData(ResourceRequest resourceRequest, ResourceResponse resourceResponse) throws NumberFormatException, PortalException, SystemException { long file_id = 0; String fileEntryIds = ParamUtil.getString(resourceRequest, "fileEntryIds"); String[] fileentires = fileEntryIds.split(","); List<Long> tempResults = new ArrayList<Long>(); if (!fileEntryIds.isEmpty()) { if (fileentires[0].equals("true")) { for (int i = 1; i < fileentires.length; i++) { DLFileEntry FileEntry = DLFileEntryLocalServiceUtil .getDLFileEntry(Long.parseLong(fileentires[i])); file_id = FileEntry.getFileEntryId(); tempResults.add(file_id); }/*from w w w . j av a 2 s .c om*/ } else { for (int i = 0; i < fileentires.length; i++) { DLFileEntry FileEntry = DLFileEntryLocalServiceUtil .getDLFileEntry(Long.parseLong(fileentires[i])); file_id = FileEntry.getFileEntryId(); tempResults.add(file_id); } } } try { HSSFWorkbook hwb = new HSSFWorkbook(); HSSFSheet sheet = hwb.createSheet("Site Information"); org.apache.poi.ss.usermodel.Font font = hwb.createFont(); font.setFontHeightInPoints((short) 11); font.setFontName("Arial"); font.setItalic(false); font.setStrikeout(false); font.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD); CellStyle style = hwb.createCellStyle(); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBorderLeft(CellStyle.BORDER_THIN); style.setBorderTop(CellStyle.BORDER_THIN); style.setBorderRight(CellStyle.BORDER_THIN); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(HSSFColor.BLACK.index); style.setFont(font); CellStyle filterStyle = hwb.createCellStyle(); org.apache.poi.ss.usermodel.Font filterfont = hwb.createFont(); filterfont.setFontHeightInPoints((short) 9); filterfont.setFontName("Courier New"); filterfont.setItalic(false); filterfont.setStrikeout(false); filterfont.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD); filterStyle.setFont(filterfont); filterStyle.setBorderBottom(CellStyle.BORDER_THIN); filterStyle.setBorderLeft(CellStyle.BORDER_THIN); filterStyle.setBorderTop(CellStyle.BORDER_THIN); filterStyle.setBorderRight(CellStyle.BORDER_THIN); HSSFRow rowhead = sheet.createRow((short) 2); rowhead.createCell((short) 0).setCellValue("S.No."); rowhead.createCell((short) 1).setCellValue("Title "); rowhead.createCell((short) 2).setCellValue("Site Name "); rowhead.createCell((short) 3).setCellValue("Category Type"); rowhead.createCell((short) 4).setCellValue("Com"); rowhead.createCell((short) 5).setCellValue("Upload Date"); rowhead.createCell((short) 6).setCellValue("File Type"); int index = 3; int sno = 0; for (int i = 0; i < tempResults.size(); i++) { sno++; DLFileEntry objdlfileentry = DLFileEntryLocalServiceUtil.getDLFileEntry(tempResults.get(i)); docs_customData objdocs_customData = null; try { objdocs_customData = docs_customDataLocalServiceUtil.getfileEntry(tempResults.get(i)); } catch (Exception e) { // TODO Auto-generated catch block //e.printStackTrace(); } String com = ""; if (objdocs_customData != null) { com = objdocs_customData.getCom(); } String siteName = ""; if (objdocs_customData != null) { siteName = objdocs_customData.getSite(); } String doccategory = ""; if (objdocs_customData != null) { doccategory = objdocs_customData.getCategory(); } HSSFRow row = sheet.createRow((short) index); row.createCell((short) 0).setCellValue(sno); row.createCell((short) 1).setCellValue(objdlfileentry.getTitle()); row.createCell((short) 2).setCellValue(siteName); row.createCell((short) 3).setCellValue(doccategory); row.createCell((short) 4).setCellValue(com); row.createCell((short) 5).setCellValue(objdlfileentry.getModifiedDate()); row.createCell((short) 6).setCellValue(objdlfileentry.getMimeType()); index++; } resourceResponse.setContentType("application/vnd.ms-excel"); resourceResponse.addProperty("Content-Disposition", "attachment;filename=Document_Listing.xls"); OutputStream fileOut = resourceResponse.getPortletOutputStream(); hwb.write(fileOut); fileOut.close(); } catch (Exception ex) { ex.printStackTrace(); } }
From source file:com.lufs.java.apache.poi.example.CalendarDemo.java
License:Apache License
/** * cell styles used for formatting calendar sheets *//* www . j a v a 2 s.c o m*/ private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<>(); short borderColor = IndexedColors.GREY_50_PERCENT.getIndex(); CellStyle style; Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 48); titleFont.setColor(IndexedColors.DARK_BLUE.getIndex()); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); style.setFont(titleFont); styles.put("title", style); Font monthFont = wb.createFont(); monthFont.setFontHeightInPoints((short) 12); monthFont.setColor(IndexedColors.WHITE.getIndex()); monthFont.setBold(true); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); style.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setFont(monthFont); styles.put("month", style); Font dayFont = wb.createFont(); dayFont.setFontHeightInPoints((short) 14); dayFont.setBold(true); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.LEFT); style.setVerticalAlignment(VerticalAlignment.TOP); style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderLeft(BorderStyle.THIN); style.setLeftBorderColor(borderColor); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(borderColor); style.setFont(dayFont); styles.put("weekend_left", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.TOP); style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderRight(BorderStyle.THIN); style.setRightBorderColor(borderColor); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(borderColor); styles.put("weekend_right", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.LEFT); style.setVerticalAlignment(VerticalAlignment.TOP); style.setBorderLeft(BorderStyle.THIN); style.setFillForegroundColor(IndexedColors.WHITE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setLeftBorderColor(borderColor); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(borderColor); style.setFont(dayFont); styles.put("workday_left", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.TOP); style.setFillForegroundColor(IndexedColors.WHITE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderRight(BorderStyle.THIN); style.setRightBorderColor(borderColor); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(borderColor); styles.put("workday_right", style); style = wb.createCellStyle(); style.setBorderLeft(BorderStyle.THIN); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(borderColor); styles.put("grey_left", style); style = wb.createCellStyle(); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderRight(BorderStyle.THIN); style.setRightBorderColor(borderColor); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(borderColor); styles.put("grey_right", style); return styles; }
From source file:com.lw.common.utils.ExcelUtil.java
/** * excel// w w w . j av a2 s.c om * @param list ? * @param keys listmapkey? * @param columnNames excel?? * */ public static Workbook createWorkBook(List<Map<String, Object>> list, String[] keys, String columnNames[]) { // excel Workbook wb = new HSSFWorkbook(); // sheet?? Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString()); // ???n? for (int i = 0; i < keys.length; i++) { sheet.setColumnWidth((short) i, (short) (35.7 * 150)); } // Row row = sheet.createRow((short) 0); // ??? CellStyle cs = wb.createCellStyle(); CellStyle cs2 = wb.createCellStyle(); // ? Font f = wb.createFont(); Font f2 = wb.createFont(); // ???? f.setFontHeightInPoints((short) 10); f.setColor(IndexedColors.BLACK.getIndex()); f.setBoldweight(Font.BOLDWEIGHT_BOLD); // ?? f2.setFontHeightInPoints((short) 10); f2.setColor(IndexedColors.BLACK.getIndex()); // Font f3=wb.createFont(); // f3.setFontHeightInPoints((short) 10); // f3.setColor(IndexedColors.RED.getIndex()); // ????? cs.setFont(f); cs.setBorderLeft(CellStyle.BORDER_THIN); cs.setBorderRight(CellStyle.BORDER_THIN); cs.setBorderTop(CellStyle.BORDER_THIN); cs.setBorderBottom(CellStyle.BORDER_THIN); cs.setAlignment(CellStyle.ALIGN_CENTER); // ??? cs2.setFont(f2); cs2.setBorderLeft(CellStyle.BORDER_THIN); cs2.setBorderRight(CellStyle.BORDER_THIN); cs2.setBorderTop(CellStyle.BORDER_THIN); cs2.setBorderBottom(CellStyle.BORDER_THIN); cs2.setAlignment(CellStyle.ALIGN_CENTER); //?? for (int i = 0; i < columnNames.length; i++) { Cell cell = row.createCell(i); cell.setCellValue(columnNames[i]); cell.setCellStyle(cs); } //?? for (short i = 1; i < list.size(); i++) { // Row ,Cell , Row Cell 0 // sheet Row row1 = sheet.createRow((short) i); // row for (short j = 0; j < keys.length; j++) { Cell cell = row1.createCell(j); cell.setCellValue(list.get(i).get(keys[j]) == null ? " " : list.get(i).get(keys[j]).toString()); cell.setCellStyle(cs2); } } return wb; }
From source file:com.lwr.software.reporter.restservices.ReportExportService.java
License:Open Source License
public Response exportExcel(Report toExport, Set<ReportParameter> reportParams) { Workbook wb = new XSSFWorkbook(); Font boldFont = wb.createFont(); boldFont.setBold(true);//from w w w. ja v a 2s. co m CellStyle headerStyle = wb.createCellStyle(); headerStyle.setBorderBottom(BorderStyle.THIN); headerStyle.setBorderTop(BorderStyle.THIN); headerStyle.setBorderRight(BorderStyle.THIN); headerStyle.setBorderLeft(BorderStyle.THIN); headerStyle.setFillBackgroundColor(IndexedColors.BLUE.getIndex()); headerStyle.setFont(boldFont); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); CellStyle titleStyle = wb.createCellStyle(); titleStyle.setBorderBottom(BorderStyle.THIN); titleStyle.setBorderTop(BorderStyle.THIN); titleStyle.setBorderRight(BorderStyle.THIN); titleStyle.setBorderLeft(BorderStyle.THIN); List<RowElement> rows = toExport.getRows(); int sheetIndex = 0; for (RowElement rowElement : rows) { List<Element> elements = rowElement.getElements(); for (Element element : elements) { try { element.setParams(reportParams); element.init(); } catch (Exception e) { logger.error("Unable to init '" + element.getTitle() + "' element of report '" + toExport.getTitle() + "' Error " + e.getMessage(), e); return Response.serverError().entity("Unable to init '" + element.getTitle() + "' element of report '" + toExport.getTitle() + "' Error " + e.getMessage()).build(); } String sheetName = element.getTitle().substring(0, element.getTitle().length() > 30 ? 30 : element.getTitle().length()) + (sheetIndex++); Sheet sheet = wb.createSheet(sheetName); Row reportTitleRow = sheet.createRow(0); Cell reportTitleHeader = reportTitleRow.createCell(0); reportTitleHeader.setCellStyle(headerStyle); reportTitleHeader.setCellValue("Report Title:"); Cell reportTitleCell = reportTitleRow.createCell(1); reportTitleCell.setCellStyle(titleStyle); reportTitleCell.setCellValue(toExport.getTitle()); Row elementTitleRow = sheet.createRow(1); Cell elementTitleHeader = elementTitleRow.createCell(0); elementTitleHeader.setCellStyle(headerStyle); elementTitleHeader.setCellValue("Element Title:"); Cell elementTitleCell = elementTitleRow.createCell(1); elementTitleCell.setCellStyle(titleStyle); elementTitleCell.setCellValue(element.getTitle()); List<List<Object>> dataToExport = element.getData(); int rowIndex = 3; Row headerRow = sheet.createRow(rowIndex++); List<Object> unifiedHeaderRow = element.getHeader(); for (int i = 0; i < unifiedHeaderRow.size(); i++) { Cell headerCell = headerRow.createCell(i); String headerCellValue = unifiedHeaderRow.get(i).toString(); headerCell.setCellValue(headerCellValue); headerCell.setCellStyle(headerStyle); } for (int i = 0; i < dataToExport.size(); i++) { Row row = sheet.createRow(rowIndex++); List<Object> unifiedRow = dataToExport.get(i); int cellIndex = 0; for (Object cellValue : unifiedRow) { Cell cell = row.createCell(cellIndex); cell.setCellStyle(cellStyle); try { double val = Double.parseDouble(cellValue.toString()); cell.setCellValue(val); } catch (NumberFormatException e) { cell.setCellValue(cellValue.toString()); } cellIndex++; } } } } try { File file = new File(DashboardConstants.APPLN_TEMP_DIR + System.nanoTime()); logger.info("Export CSV temp file path is " + file.getAbsoluteFile()); wb.write(new FileOutputStream(file)); wb.close(); ResponseBuilder responseBuilder = Response.ok((Object) file); responseBuilder.header("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); responseBuilder.header("Content-Transfer-Encoding", "binary"); responseBuilder.header("Content-Disposition", "attachment;filename=" + file.getName()); responseBuilder.header("Content-Length", file.length()); Response responseToSend = responseBuilder.build(); file.deleteOnExit(); return responseToSend; } catch (Exception e1) { return Response.serverError() .entity("Unable to export " + toExport.getTitle() + " report " + e1.getMessage()).build(); } }
From source file:com.nc.common.utils.ExcelUtil.java
License:Open Source License
/** * <pre>//from w w w.j a v a2s . c om * 1. : POI UTIL * 2. : POI WORKBOOK ? / String , List<Map<String, Object>> ? , List<Map<String, Object>> * </pre> * * @method Name : createWorkBook * @param workbook, sheetName, list, colNames * @return Workbook * @throws Exception * */ @SuppressWarnings("deprecation") public static Workbook createWorkBook(Workbook workbook, String sheetName, List<Map<String, Object>> list, List<Map<String, Object>> colNames) throws Exception { Row row; Cell cell; /* ? ? START */ /* Workbook workbook = new Workbook(); */ CellStyle titleStyle = workbook.createCellStyle(); CellStyle cellStyle = workbook.createCellStyle(); CellStyle contentStyle = workbook.createCellStyle(); CellStyle contentStyle_2 = workbook.createCellStyle(); /* ? */ /* ? ? */ Font titleFont = workbook.createFont(); titleFont.setFontHeightInPoints((short) 13); titleFont.setFontName("? "); /* ? */ Font colNameFont = workbook.createFont(); colNameFont.setFontHeightInPoints((short) 10); colNameFont.setFontName("? "); /* ? */ Font contentFont = workbook.createFont(); /* ? ? ? */ titleStyle.setFont(titleFont); /* ? / ? ? */ cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); /* ? */ cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//? cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); cellStyle.setFont(colNameFont); /* ? / ? */ contentStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); /* ? */ contentStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); contentStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); contentStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); contentStyle.setFont(contentFont); /* ? / ? */ contentStyle_2.setBorderRight(HSSFCellStyle.BORDER_THIN); /* ? */ contentStyle_2.setBorderLeft(HSSFCellStyle.BORDER_THIN); contentStyle_2.setBorderTop(HSSFCellStyle.BORDER_THIN); contentStyle_2.setBorderBottom(HSSFCellStyle.BORDER_THIN); contentStyle_2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); contentStyle_2.setAlignment(HSSFCellStyle.ALIGN_LEFT); contentStyle_2.setFont(contentFont); /* ? */ Sheet sheet = workbook.createSheet(sheetName); /* ?? */ int sheet1_row = 0; List<Map<String, Object>> colList = colNames; /* ? START */ row = sheet.createRow(sheet1_row); for (int i = 0; i < colList.size(); i++) { cell = row.createCell(i); cell.setCellValue(String.valueOf(colList.get(i).get("item" + i))); cell.setCellStyle(cellStyle); } sheet1_row++; /* ? END */ for (Map<String, Object> map : list) { row = sheet.createRow(sheet1_row); for (int j = 0; j < colList.size(); j++) { cell = row.createCell(j); cell.setCellStyle(contentStyle); if (null != map.get(colList.get(j).get("item" + j))) { cell.setCellValue((double) map.get(String.valueOf(colList.get(j).get("item" + j)))); } else { cell.setCellValue(""); } /* ? ?? */ sheet.autoSizeColumn((short) j); sheet.setColumnWidth(j, (sheet.getColumnWidth(j)) + 312); } sheet1_row++; } return workbook; }
From source file:com.ncc.excel.test.ExcelUtil.java
License:Apache License
/** * ????? /*w w w . j a va 2s. c o m*/ * * @param fromStyle * @param toStyle */ public static void copyCellStyle(CellStyle fromStyle, CellStyle toStyle) { toStyle.setAlignment(fromStyle.getAlignment()); // toStyle.setBorderBottom(fromStyle.getBorderBottom()); toStyle.setBorderLeft(fromStyle.getBorderLeft()); toStyle.setBorderRight(fromStyle.getBorderRight()); toStyle.setBorderTop(fromStyle.getBorderTop()); toStyle.setTopBorderColor(fromStyle.getTopBorderColor()); toStyle.setBottomBorderColor(fromStyle.getBottomBorderColor()); toStyle.setRightBorderColor(fromStyle.getRightBorderColor()); toStyle.setLeftBorderColor(fromStyle.getLeftBorderColor()); // ? toStyle.setFillBackgroundColor(fromStyle.getFillBackgroundColor()); toStyle.setFillForegroundColor(fromStyle.getFillForegroundColor()); // ?? toStyle.setDataFormat(fromStyle.getDataFormat()); toStyle.setFillPattern(fromStyle.getFillPattern()); // toStyle.setFont(fromStyle.getFont(null)); toStyle.setHidden(fromStyle.getHidden()); toStyle.setIndention(fromStyle.getIndention());// toStyle.setLocked(fromStyle.getLocked()); toStyle.setRotation(fromStyle.getRotation());// toStyle.setVerticalAlignment(fromStyle.getVerticalAlignment()); toStyle.setWrapText(fromStyle.getWrapText()); }
From source file:com.netxforge.netxstudio.models.export.MasterDataExporterRevenge.java
License:Open Source License
private void _generateID(Sheet sheet) { // Generate name. {/*from ww w.ja va 2s . c o m*/ // Style, cell color. CellStyle attributeStyle = workBook.createCellStyle(); attributeStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); attributeStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); // Style, font HSSFFont attributeFont = workBook.createFont(); attributeFont.setFontName("Verdana"); attributeFont.setColor(HSSFColor.BLUE.index); attributeStyle.setFont(attributeFont); // Style, border. attributeStyle.setBorderBottom(CellStyle.BORDER_MEDIUM); Row row = sheet.getRow(0); if (row == null) { row = sheet.createRow(0); } short lastCellNum = row.getLastCellNum(); if (lastCellNum == -1) { lastCellNum = 0; } Cell cell = row.createCell(lastCellNum); cell.setCellValue("ID"); cell.setCellStyle(attributeStyle); } // Generate type { CellStyle typeStyle = workBook.createCellStyle(); typeStyle.setBorderBottom(CellStyle.BORDER_MEDIUM); Row row = sheet.getRow(1); if (row == null) { row = sheet.createRow(1); } short lastCellNum = row.getLastCellNum(); if (lastCellNum == -1) { lastCellNum = 0; } Cell cell = row.createCell(lastCellNum); cell.setCellStyle(typeStyle); } }