List of usage examples for org.apache.poi.xssf.usermodel XSSFCellStyle setFont
@Override public void setFont(Font font)
From source file:com.netsteadfast.greenstep.bsc.command.PerspectivesDashboardExcelCommand.java
License:Apache License
@SuppressWarnings("unchecked") private int putCharts(XSSFWorkbook wb, XSSFSheet sh, Context context) throws Exception { String pieBase64Content = SimpleUtils.getPNGBase64Content((String) context.get("pieCanvasToData")); String barBase64Content = SimpleUtils.getPNGBase64Content((String) context.get("barCanvasToData")); BufferedImage pieImage = SimpleUtils.decodeToImage(pieBase64Content); BufferedImage barImage = SimpleUtils.decodeToImage(barBase64Content); ByteArrayOutputStream pieBos = new ByteArrayOutputStream(); ImageIO.write(pieImage, "png", pieBos); pieBos.flush();/*from w w w.j av a 2 s . com*/ ByteArrayOutputStream barBos = new ByteArrayOutputStream(); ImageIO.write(barImage, "png", barBos); barBos.flush(); SimpleUtils.setCellPicture(wb, sh, pieBos.toByteArray(), 0, 0); SimpleUtils.setCellPicture(wb, sh, barBos.toByteArray(), 0, 9); int row = 21; List<Map<String, Object>> chartDatas = (List<Map<String, Object>>) context.get("chartDatas"); String year = (String) context.get("year"); XSSFCellStyle cellHeadStyle = wb.createCellStyle(); cellHeadStyle.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor("#f5f5f5"))); cellHeadStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellHeadFont = wb.createFont(); cellHeadFont.setBold(true); //cellHeadFont.setColor( new XSSFColor( SimpleUtils.getColorRGB4POIColor( "#000000" ) ) ); cellHeadStyle.setFont(cellHeadFont); int titleRow = row - 1; int titleCellSize = 14; Row headRow = sh.createRow(titleRow); for (int i = 0; i < titleCellSize; i++) { Cell headCell = headRow.createCell(i); headCell.setCellStyle(cellHeadStyle); headCell.setCellValue("Perspectives metrics gauge ( " + year + " )"); } sh.addMergedRegion(new CellRangeAddress(titleRow, titleRow, 0, titleCellSize - 1)); int cellLeft = 10; int rowSpace = 17; for (Map<String, Object> data : chartDatas) { Map<String, Object> nodeData = (Map<String, Object>) ((List<Object>) data.get("datas")).get(0); String pngImageData = SimpleUtils.getPNGBase64Content((String) nodeData.get("outerHTML")); BufferedImage imageData = SimpleUtils.decodeToImage(pngImageData); ByteArrayOutputStream imgBos = new ByteArrayOutputStream(); ImageIO.write(imageData, "png", imgBos); imgBos.flush(); SimpleUtils.setCellPicture(wb, sh, imgBos.toByteArray(), row, 0); XSSFColor bgColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor((String) nodeData.get("bgColor"))); XSSFColor fnColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor((String) nodeData.get("fontColor"))); XSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(bgColor); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellFont = wb.createFont(); cellFont.setBold(true); cellFont.setColor(fnColor); cellStyle.setFont(cellFont); int perTitleCellSize = 4; Row nowRow = sh.createRow(row); for (int i = 0; i < perTitleCellSize; i++) { Cell cell1 = nowRow.createCell(cellLeft); cell1.setCellStyle(cellStyle); cell1.setCellValue((String) nodeData.get("name")); } sh.addMergedRegion(new CellRangeAddress(row, row, cellLeft, cellLeft + perTitleCellSize - 1)); nowRow = sh.createRow(row + 1); Cell cell2 = nowRow.createCell(cellLeft); cell2.setCellValue("Target: " + String.valueOf(nodeData.get("target"))); nowRow = sh.createRow(row + 2); Cell cell3 = nowRow.createCell(cellLeft); cell3.setCellValue("Min: " + String.valueOf(nodeData.get("min"))); nowRow = sh.createRow(row + 3); Cell cell4 = nowRow.createCell(cellLeft); cell4.setCellValue("Score: " + String.valueOf(nodeData.get("score"))); row += rowSpace; } return row; }
From source file:com.netsteadfast.greenstep.bsc.command.TimeSeriesAnalysisExcelCommand.java
License:Apache License
private void putTables(XSSFWorkbook wb, XSSFSheet sh, Context context) throws Exception { TsaVO tsa = (TsaVO) context.get("tsa"); @SuppressWarnings("unchecked") List<BbTsaMaCoefficients> coefficients = (List<BbTsaMaCoefficients>) context.get("coefficients"); @SuppressWarnings("unchecked") List<TimeSeriesAnalysisResult> tsaResults = (List<TimeSeriesAnalysisResult>) context.get("tsaResults"); XSSFFont cellHeadFont = wb.createFont(); cellHeadFont.setBold(true);/*from w w w .j ava 2 s .co m*/ XSSFCellStyle cellHeadStyle = wb.createCellStyle(); cellHeadStyle.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor("#f5f5f5"))); cellHeadStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); cellHeadStyle.setBorderBottom(BorderStyle.THIN); cellHeadStyle.setBorderTop(BorderStyle.THIN); cellHeadStyle.setBorderRight(BorderStyle.THIN); cellHeadStyle.setBorderLeft(BorderStyle.THIN); cellHeadStyle.setFont(cellHeadFont); XSSFCellStyle cellHeadStyleBlank = wb.createCellStyle(); cellHeadStyleBlank.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor("#ffffff"))); cellHeadStyleBlank.setFont(cellHeadFont); XSSFCellStyle cellHeadStyle2 = wb.createCellStyle(); cellHeadStyle2.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor("#ffffff"))); cellHeadStyle2.setFillPattern(FillPatternType.SOLID_FOREGROUND); cellHeadStyle2.setBorderBottom(BorderStyle.THIN); cellHeadStyle2.setBorderTop(BorderStyle.THIN); cellHeadStyle2.setBorderRight(BorderStyle.THIN); cellHeadStyle2.setBorderLeft(BorderStyle.THIN); sh.setColumnWidth(0, 12000); int row = 0; // ============================================================== Row nowRow = sh.createRow(row); Cell cellTitle = nowRow.createCell(0); cellTitle.setCellStyle(cellHeadStyleBlank); cellTitle.setCellValue("Forecast analysis - " + context.get("visionName")); row++; // ============================================================== nowRow = sh.createRow(row); Cell cell0a = nowRow.createCell(0); cell0a.setCellStyle(cellHeadStyleBlank); cell0a.setCellValue("Frequency: " + context.get("frequencyName") + " , Date range: " + context.get("date1") + " - " + context.get("date2") + " , " + "Measure data type for: " + context.get("dataFor") + " , " + context.get("organizationName") + context.get("employeeName")); row++; // ============================================================== nowRow = sh.createRow(row); Cell cell0b = nowRow.createCell(0); cell0b.setCellStyle(cellHeadStyleBlank); cell0b.setCellValue(""); row++; // ============================================================== nowRow = sh.createRow(row); Cell cell1 = nowRow.createCell(0); cell1.setCellStyle(cellHeadStyleBlank); cell1.setCellValue("Param infornation"); row++; // ============================================================== nowRow = sh.createRow(row); Cell cell2_a = nowRow.createCell(0); cell2_a.setCellStyle(cellHeadStyle); cell2_a.setCellValue("Item"); Cell cell2_b = nowRow.createCell(1); cell2_b.setCellStyle(cellHeadStyle); cell2_b.setCellValue("Value"); row++; // ============================================================== nowRow = sh.createRow(row); Cell cell3_a = nowRow.createCell(0); cell3_a.setCellStyle(cellHeadStyle2); cell3_a.setCellValue("Param name"); Cell cell3_b = nowRow.createCell(1); cell3_b.setCellStyle(cellHeadStyle2); cell3_b.setCellValue(tsa.getName()); row++; // ============================================================== nowRow = sh.createRow(row); Cell cell4_a = nowRow.createCell(0); cell4_a.setCellStyle(cellHeadStyle2); cell4_a.setCellValue("Integration order"); Cell cell4_b = nowRow.createCell(1); cell4_b.setCellStyle(cellHeadStyle2); cell4_b.setCellValue(tsa.getIntegrationOrder()); row++; // ============================================================== nowRow = sh.createRow(row); Cell cell5_a = nowRow.createCell(0); cell5_a.setCellStyle(cellHeadStyle2); cell5_a.setCellValue("Forecast next"); Cell cell5_b = nowRow.createCell(1); cell5_b.setCellStyle(cellHeadStyle2); cell5_b.setCellValue(tsa.getForecastNext()); row++; // ============================================================== nowRow = sh.createRow(row); Cell cell6_a = nowRow.createCell(0); cell6_a.setCellStyle(cellHeadStyle2); cell6_a.setCellValue("Description"); Cell cell6_b = nowRow.createCell(1); cell6_b.setCellStyle(cellHeadStyle2); cell6_b.setCellValue(StringUtils.defaultString(tsa.getDescription()).trim()); row++; // ============================================================== for (int i = 0; coefficients != null && i < coefficients.size(); i++) { BbTsaMaCoefficients coefficient = coefficients.get(i); nowRow = sh.createRow(row); Cell cell7x_a = nowRow.createCell(0); cell7x_a.setCellStyle(cellHeadStyle2); cell7x_a.setCellValue("Coefficient (" + (i + 1) + ")"); Cell cell7x_b = nowRow.createCell(1); cell7x_b.setCellStyle(cellHeadStyle2); cell7x_b.setCellValue(String.valueOf(coefficient.getSeqValue())); row++; } // ============================================================== nowRow = sh.createRow(row); Cell cellTitle3a = nowRow.createCell(0); cellTitle3a.setCellStyle(cellHeadStyleBlank); cellTitle3a.setCellValue(""); row++; // nowRow = sh.createRow(row); Cell cellTitle2a = nowRow.createCell(0); cellTitle2a.setCellStyle(cellHeadStyle); cellTitle2a.setCellValue("KPIs"); int j = 1; TimeSeriesAnalysisResult firstResult = tsaResults.get(0); for (int i = 0; i < firstResult.getKpi().getDateRangeScores().size(); i++) { DateRangeScoreVO dateRangeScore = firstResult.getKpi().getDateRangeScores().get(i); Cell cellTitle2a_dateRange = nowRow.createCell(j); j++; cellTitle2a_dateRange.setCellStyle(cellHeadStyle); cellTitle2a_dateRange.setCellValue(dateRangeScore.getDate()); } for (int i = 0; i < firstResult.getForecastNext().size(); i++) { Cell cellTitle2a_dateRange = nowRow.createCell(j); j++; cellTitle2a_dateRange.setCellStyle(cellHeadStyle); cellTitle2a_dateRange.setCellValue("next(" + (i + 1) + ")"); } row++; // Date Range score Forecast next score for (int i = 0; i < tsaResults.size(); i++) { nowRow = sh.createRow(row); j = 0; TimeSeriesAnalysisResult resultModel = tsaResults.get(i); Cell cell_kpi = nowRow.createCell(j); cell_kpi.setCellStyle(cellHeadStyle); cell_kpi.setCellValue(resultModel.getKpi().getName()); j++; for (int n = 0; n < resultModel.getKpi().getDateRangeScores().size(); n++) { DateRangeScoreVO dateRangeScore = resultModel.getKpi().getDateRangeScores().get(n); Cell cell_dateRangeScore = nowRow.createCell(j); cell_dateRangeScore.setCellStyle(cellHeadStyle2); cell_dateRangeScore.setCellValue(dateRangeScore.getScore()); j++; } for (int n = 0; n < resultModel.getForecastNext().size(); n++) { double forecastScore = resultModel.getForecastNext().get(n); Cell cell_forecastScore = nowRow.createCell(j); cell_forecastScore.setCellStyle(cellHeadStyle2); cell_forecastScore.setCellValue(forecastScore); j++; } row++; } }
From source file:com.opendoorlogistics.core.tables.io.PoiIO.java
License:Open Source License
/** * See http://thinktibits.blogspot.co.uk/2012/12/Java-POI-XLS-XLSX-Change-Cell-Font-Color-Example.html * Currently only for xlsx/*from w w w. j a va 2 s. co m*/ * @param wb * @param sheet */ private static void styleHeader(Workbook wb, Sheet sheet) { if (XSSFWorkbook.class.isInstance(wb) && XSSFSheet.class.isInstance(sheet)) { XSSFWorkbook my_workbook = (XSSFWorkbook) wb; XSSFCellStyle my_style = my_workbook.createCellStyle(); XSSFFont my_font = my_workbook.createFont(); my_font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); my_style.setFont(my_font); Row row = sheet.getRow(0); if (row != null && row.getFirstCellNum() >= 0) { for (int i = row.getFirstCellNum(); i <= row.getLastCellNum(); i++) { Cell cell = row.getCell(i); if (cell != null) { cell.setCellStyle(my_style); } } } } }
From source file:com.opendoorlogistics.speedregions.excelshp.io.ExcelWriter.java
License:Apache License
public static void writeSheets(File file, ExportTable... tables) { // create empty workbook with a bold font style XSSFWorkbook wb = new XSSFWorkbook(); XSSFCellStyle headerStyle = wb.createCellStyle(); XSSFFont boldfont = wb.createFont(); boldfont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); boldfont.setFontHeight(12);//from ww w . j a v a 2s. c o m headerStyle.setFont(boldfont); // fill workbook for (ExportTable table : tables) { Sheet sheet = wb.createSheet(table.getName()); Row headerRow = sheet.createRow(0); for (int c = 0; c < table.getHeader().size(); c++) { Cell cell = headerRow.createCell(c); cell.setCellStyle(headerStyle); cell.setCellValue(table.getHeader().get(c).getName()); } List<List<String>> rows = table.getRows(); int nr = rows.size(); for (int r = 0; r < nr; r++) { Row row = sheet.createRow(r + 1); List<String> srcRow = rows.get(r); int nc = srcRow.size(); for (int c = 0; c < nc; c++) { //JsonFormatTypes type = table.getColumnType(c); Cell cell = row.createCell(c); String value = srcRow.get(c); writeToCell(value, c < table.getHeader().size() ? table.getHeader().get(c).getFormatType() : JsonFormatTypes.STRING, cell); } } } // try saving FileOutputStream fileOut = null; try { fileOut = new FileOutputStream(file); wb.write(fileOut); } catch (Exception e) { // TODO: handle exception } finally { try { if (fileOut != null) { fileOut.close(); } } catch (Exception e2) { throw new RuntimeException(e2); } try { if (wb != null) { wb.close(); } } catch (Exception e2) { throw new RuntimeException(e2); } } LOGGER.info("Wrote Excel file " + file.getAbsolutePath()); }
From source file:com.pe.nisira.movil.view.action.MultitablaAction.java
public StreamedContent downFormatExcel() throws Exception { InputStream stream = null;/*from w w w .j a v a 2 s .co m*/ StreamedContent arch = null; try { String folder = "C:\\SOLUTION\\WEB\\FORMATOS_IMPORTACION"; File ruta = new File(folder); if (!ruta.isDirectory()) { ruta.mkdirs(); } String rutaArchivo = folder + "\\FI_MULTITABLA.xlsx"; File fileXls = new File(rutaArchivo); if (fileXls.exists()) { fileXls.delete(); } fileXls.createNewFile(); XSSFWorkbook libro = new XSSFWorkbook(); FileOutputStream file = new FileOutputStream(fileXls); XSSFSheet hoja = libro.createSheet("IMPORTAR_MULTITABLA"); CreationHelper factory = libro.getCreationHelper(); hoja = libro.getSheetAt(0); XSSFCellStyle style = libro.createCellStyle(); Font font = libro.createFont(); Font font1 = libro.createFont(); Drawing drawing = hoja.createDrawingPatriarch(); ClientAnchor anchor1 = factory.createClientAnchor(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short) 8); font1.setFontHeightInPoints((short) 8); font1.setFontName("Arial"); font.setFontName("Arial"); style.setFillForegroundColor(new XSSFColor(new java.awt.Color(247, 150, 70))); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setAlignment(CellStyle.VERTICAL_CENTER); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFont(font); for (int f = 0; f < 1; f++) { XSSFRow fila = hoja.createRow(f); for (int c = 0; c < 4; c++) { XSSFCell celda = fila.createCell(c); celda.setCellStyle(style); anchor1.setCol1(celda.getColumnIndex()); anchor1.setCol2(celda.getColumnIndex() + 4); anchor1.setRow1(fila.getRowNum()); anchor1.setRow2(fila.getRowNum() + 3); Comment comment = drawing.createCellComment(anchor1); if (f == 0 && c == 0) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Indicar si es es Padre (Usar SI o NO)."); str.applyFont(font1); str.applyFont(0, 29, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("Es Padre"); celda.setCellComment(comment); } else if (f == 0 && c == 1) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Opcional \n - Escribir la Abreviatura del campo del cual depende este."); str.applyFont(font1); str.applyFont(0, 29, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("Abreviatura Padre"); celda.setCellComment(comment); } else if (f == 0 && c == 2) { RichTextString str = factory .createRichTextString("ADM:\nCampo Obligatorio \n - Descripcion de la multitabla"); str.applyFont(font1); str.applyFont(0, 29, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("DESCRIPCION"); celda.setCellComment(comment); } else if (f == 0 && c == 3) { RichTextString str = factory .createRichTextString("ADM:\nCampo Obligatorio \n - Abreviatura de la multitabla."); str.applyFont(font1); str.applyFont(0, 29, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("ABREVIATURA"); celda.setCellComment(comment); } } } hoja.autoSizeColumn((short) 0); hoja.autoSizeColumn((short) 1); hoja.autoSizeColumn((short) 2); libro.write(file); file.close(); stream = new FileInputStream(new File(rutaArchivo)); arch = new DefaultStreamedContent(stream, "application/xlsx", "FI_MULTITABLA.xlsx"); } catch (FileNotFoundException ex) { System.out.println("Error al Descargar : " + ex.getMessage()); } return arch; }
From source file:com.pe.nisira.movil.view.action.RegistroPaleta.java
public StreamedContent downFormatExcel() throws Exception { InputStream stream = null;/*from ww w . ja va2 s . c o m*/ StreamedContent arch = null; try { String folder = "C:\\SOLUTION\\WEB\\FORMATOS_IMPORTACION"; File ruta = new File(folder); if (!ruta.isDirectory()) { ruta.mkdirs(); } String rutaArchivo = folder + "\\FI_REGISTROPALE.xlsx"; File fileXls = new File(rutaArchivo); if (fileXls.exists()) { fileXls.delete(); } fileXls.createNewFile(); XSSFWorkbook libro = new XSSFWorkbook(); FileOutputStream file2 = new FileOutputStream(fileXls); XSSFSheet hoja = libro.createSheet("IMPORTAR_PALETA"); CreationHelper factory = libro.getCreationHelper(); hoja = libro.getSheetAt(0); XSSFCellStyle style = libro.createCellStyle(); Font font = libro.createFont(); Font font1 = libro.createFont(); Drawing drawing = hoja.createDrawingPatriarch(); ClientAnchor anchor1 = factory.createClientAnchor(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short) 8); font1.setFontHeightInPoints((short) 8); font1.setFontName("Arial"); font.setFontName("Arial"); style.setFillForegroundColor(new XSSFColor(new java.awt.Color(247, 150, 70))); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setAlignment(CellStyle.VERTICAL_CENTER); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFont(font); XSSFSheet hoja2 = libro.createSheet("IMPORTAR_DET_PALETA"); CreationHelper factory2 = libro.getCreationHelper(); hoja2 = libro.getSheetAt(1); XSSFCellStyle style2 = libro.createCellStyle(); Font font2 = libro.createFont(); Font font12 = libro.createFont(); Drawing drawing2 = hoja2.createDrawingPatriarch(); ClientAnchor anchor12 = factory2.createClientAnchor(); font2.setBoldweight(Font.BOLDWEIGHT_BOLD); font2.setFontHeightInPoints((short) 8); font12.setFontHeightInPoints((short) 8); font12.setFontName("Arial"); font2.setFontName("Arial"); style2.setFillForegroundColor(new XSSFColor(new java.awt.Color(247, 150, 70))); style2.setFillPattern(CellStyle.SOLID_FOREGROUND); style2.setAlignment(CellStyle.VERTICAL_CENTER); style2.setAlignment(CellStyle.ALIGN_CENTER); style2.setFont(font); for (int f = 0; f < 1; f++) { XSSFRow fila = hoja.createRow(f); for (int c = 0; c < 29; c++) { XSSFCell celda = fila.createCell(c); celda.setCellStyle(style); anchor1.setCol1(celda.getColumnIndex()); anchor1.setCol2(celda.getColumnIndex() + 5); anchor1.setRow1(fila.getRowNum()); anchor1.setRow2(fila.getRowNum() + 3); Comment comment = drawing.createCellComment(anchor1); if (f == 0 && c == 0) { RichTextString str = factory.createRichTextString("ADM:\nCampo Obligatorio \n - IDEMPRESA"); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDEMPRESA"); celda.setCellComment(comment); } else if (f == 0 && c == 1) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - IDREGISTROPALETA. \n Debe de tener (15) caracteres"); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDREGISTROPALETA"); celda.setCellComment(comment); } else if (f == 0 && c == 2) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Id del emisor. \n -Debe tener 3 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDEMISOR"); celda.setCellComment(comment); } else if (f == 0 && c == 3) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - id de la operacion.\n -Debe tener 4 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDOPERACION"); celda.setCellComment(comment); } else if (f == 0 && c == 4) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Numero de Operacion.\n -Debe tener 10 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("NUMOPERACION"); celda.setCellComment(comment); } else if (f == 0 && c == 5) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Id del motivo de Paleta.\n -Debe tener 3 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDMOTIVOPALETA"); celda.setCellComment(comment); } else if (f == 0 && c == 6) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Id documento. \n -Debe tener 3 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDDOCUMENTO"); celda.setCellComment(comment); } else if (f == 0 && c == 7) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Serie del Documento. \n -Debe tener 4 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("SERIE"); celda.setCellComment(comment); } else if (f == 0 && c == 8) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Numero de Documento.\n -Debe tener 7 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("NUMERO"); celda.setCellComment(comment); } else if (f == 0 && c == 9) { RichTextString str = factory .createRichTextString("ADM:\nCampo Obligatorio \n - Formato YYYY/MM/DD."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("FECHA"); celda.setCellComment(comment); } else if (f == 0 && c == 10) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Perido del ao \n - fromato YYYYMM."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("PERIODO"); celda.setCellComment(comment); } else if (f == 0 && c == 11) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Id del estado \n -Debe tener 2 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDESTADO"); celda.setCellComment(comment); } else if (f == 0 && c == 12) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Id del cliente o proveedor \n -Debe tener 11 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDCLIEPROV"); celda.setCellComment(comment); } else if (f == 0 && c == 13) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Numero de Paleta \n -Debe tener 20 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("NROPALETA"); celda.setCellComment(comment); } else if (f == 0 && c == 14) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Id de envase \n -Debe tener 3 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDENVASE"); celda.setCellComment(comment); } else if (f == 0 && c == 15) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Id la sucursal \n -Debe tener 3 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDSUCURSAL"); celda.setCellComment(comment); } else if (f == 0 && c == 16) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Id del almacen. \n -Debe tener 3 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDALMACEN"); celda.setCellComment(comment); } else if (f == 0 && c == 17) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Id del embalaje. \n -Debe tener 10 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDEMBALAJE"); celda.setCellComment(comment); } else if (f == 0 && c == 18) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Id de cultivo. \n -Debe tener 4 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDCULTIVO"); celda.setCellComment(comment); } else if (f == 0 && c == 19) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - id de Variadd. \n -Debe tener 3 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDVARIEDAD"); celda.setCellComment(comment); } else if (f == 0 && c == 20) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Observaciones sobre la paleta \n -como maximo 240 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("OBSERVACIONES"); celda.setCellComment(comment); } else if (f == 0 && c == 21) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n -Nombre de la venta \n como maximo 50 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("VENTANA"); celda.setCellComment(comment); } else if (f == 0 && c == 22) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Cantidad. \n - 15 numeros y 2 decimales como maximo."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("CANTIDAD"); celda.setCellComment(comment); } else if (f == 0 && c == 23) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Estado de la paleta \n- 1 = cerrado, 0 = Abierto."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("CERRADO"); celda.setCellComment(comment); } else if (f == 0 && c == 24) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Sincroniza \n - N = no , S = si."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("SINCRONIZA"); celda.setCellComment(comment); } else if (f == 0 && c == 25) { RichTextString str = factory .createRichTextString("ADM:\nCampo Obligatorio \n - Formato YYYY/MM/DD."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("FECHACREACION"); celda.setCellComment(comment); } else if (f == 0 && c == 26) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Numero de Manural\n Debe tener 10 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("NROMANUAL"); celda.setCellComment(comment); } else if (f == 0 && c == 27) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - idcliepro-destino\n debe tener 11 caracteres"); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDCLIEPROV_DESTINO"); celda.setCellComment(comment); } else if (f == 0 && c == 28) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Tipo de paleta\n debe tener 1 caraccter.."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("TIPO"); celda.setCellComment(comment); } } } hoja.autoSizeColumn((short) 0); hoja.autoSizeColumn((short) 1); hoja.autoSizeColumn((short) 2); hoja.autoSizeColumn((short) 3); hoja.autoSizeColumn((short) 4); hoja.autoSizeColumn((short) 5); hoja.autoSizeColumn((short) 6); hoja.autoSizeColumn((short) 7); hoja.autoSizeColumn((short) 8); hoja.autoSizeColumn((short) 9); hoja.autoSizeColumn((short) 10); hoja.autoSizeColumn((short) 11); hoja.autoSizeColumn((short) 12); hoja.autoSizeColumn((short) 13); hoja.autoSizeColumn((short) 14); hoja.autoSizeColumn((short) 15); hoja.autoSizeColumn((short) 16); hoja.autoSizeColumn((short) 17); hoja.autoSizeColumn((short) 18); hoja.autoSizeColumn((short) 19); hoja.autoSizeColumn((short) 20); hoja.autoSizeColumn((short) 21); hoja.autoSizeColumn((short) 22); hoja.autoSizeColumn((short) 23); hoja.autoSizeColumn((short) 24); hoja.autoSizeColumn((short) 25); hoja.autoSizeColumn((short) 26); hoja.autoSizeColumn((short) 27); hoja.autoSizeColumn((short) 28); for (int f = 0; f < 2; f++) { XSSFRow fila2 = hoja2.createRow(f); if (f == 0) { for (int c = 0; c < 15; c++) { XSSFCell celda2 = fila2.createCell(c); anchor12.setCol1(celda2.getColumnIndex()); anchor12.setCol2(celda2.getColumnIndex() + 8); anchor12.setRow1(fila2.getRowNum()); anchor12.setRow2(fila2.getRowNum() + 8); Comment comment2 = drawing2.createCellComment(anchor12); RichTextString str; switch (c) { case 0: celda2.setCellStyle(style2); str = factory2.createRichTextString( "ADM:\nCampo Obligatorio \n - El Cdigo debe de ser nico."); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDEMPRESA"); celda2.setCellComment(comment2); break; case 1: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio "); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDREGISTROPALETA"); celda2.setCellComment(comment2); break; case 2: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio "); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("ITEM"); celda2.setCellComment(comment2); break; case 3: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio "); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDCLIEPROV"); celda2.setCellComment(comment2); break; case 4: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDLOTE"); celda2.setCellComment(comment2); break; case 5: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDCONDICION"); celda2.setCellComment(comment2); break; case 6: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDTALLA"); celda2.setCellComment(comment2); break; case 7: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDCOLOR"); celda2.setCellComment(comment2); break; case 8: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("CANTIDAD"); celda2.setCellComment(comment2); break; case 9: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDEMBALAJE"); celda2.setCellComment(comment2); break; case 10: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDPRODUCTO"); celda2.setCellComment(comment2); break; case 11: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDLOTEP"); celda2.setCellComment(comment2); break; case 12: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDCONSUMIDOR"); celda2.setCellComment(comment2); break; case 13: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDLOTECAMPO"); celda2.setCellComment(comment2); break; case 14: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDPRESENTACION"); celda2.setCellComment(comment2); break; } } } } hoja2.autoSizeColumn((short) 0); hoja2.autoSizeColumn((short) 1); hoja2.autoSizeColumn((short) 2); hoja2.autoSizeColumn((short) 3); hoja2.autoSizeColumn((short) 4); hoja2.autoSizeColumn((short) 5); hoja2.autoSizeColumn((short) 6); hoja2.autoSizeColumn((short) 7); hoja2.autoSizeColumn((short) 8); hoja2.autoSizeColumn((short) 9); hoja2.autoSizeColumn((short) 10); hoja2.autoSizeColumn((short) 11); hoja2.autoSizeColumn((short) 12); hoja2.autoSizeColumn((short) 13); hoja2.autoSizeColumn((short) 14); hoja2.autoSizeColumn((short) 15); libro.write(file2); file2.close(); stream = new FileInputStream(new File(rutaArchivo)); arch = new DefaultStreamedContent(stream, "application/xlsx", "FI_REGISTROPALE.xlsx"); } catch (FileNotFoundException ex) { System.out.println("Error al Descargar : " + ex.getMessage()); } return arch; }
From source file:com.philips.his.pixiu.cdr.poi.BigGridDemo.java
License:Apache License
/** * Create a library of cell styles./*ww w. jav a 2 s. com*/ */ private static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb) { Map<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>(); XSSFDataFormat fmt = wb.createDataFormat(); XSSFCellStyle style1 = wb.createCellStyle(); style1.setAlignment(HorizontalAlignment.RIGHT); style1.setDataFormat(fmt.getFormat("0.0%")); styles.put("percent", style1); XSSFCellStyle style2 = wb.createCellStyle(); style2.setAlignment(HorizontalAlignment.CENTER); style2.setDataFormat(fmt.getFormat("0.0X")); styles.put("coeff", style2); XSSFCellStyle style3 = wb.createCellStyle(); style3.setAlignment(HorizontalAlignment.RIGHT); style3.setDataFormat(fmt.getFormat("$#,##0.00")); styles.put("currency", style3); XSSFCellStyle style4 = wb.createCellStyle(); style4.setAlignment(HorizontalAlignment.RIGHT); style4.setDataFormat(fmt.getFormat("mmm dd")); styles.put("date", style4); XSSFCellStyle style5 = wb.createCellStyle(); XSSFFont headerFont = wb.createFont(); headerFont.setBold(true); style5.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style5.setFillPattern(FillPatternType.SOLID_FOREGROUND); style5.setFont(headerFont); styles.put("header", style5); return styles; }
From source file:com.rdg.export.util.ExportXLS.java
License:Apache License
/** * create cell style for header names// w w w. ja v a 2 s . c om * * @return */ private static XSSFCellStyle getCellStyleHeader() { XSSFCellStyle cellStyle = workbook.createCellStyle(); CreationHelper createHelper = workbook.getCreationHelper(); Font headerFont = workbook.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(DateUtil.sDateFormat_1)); cellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); cellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); cellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); cellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setFont(headerFont); return cellStyle; }
From source file:com.respam.comniq.models.POIexcelExporter.java
License:Open Source License
public void createFile() throws IOException { String path = System.getProperty("user.home") + File.separator + "comniq" + File.separator + "output"; File file = new File(path + File.separator + "POImovieInfo.xlsx"); // Blank Workbook XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Movies"); // Data for Labels Map<String, Object[]> label = new TreeMap<>(); label.put("1", new Object[] { "Poster", "Title", "Release Date", "Metascore", "IMDB Rating", "Plot", "IMDB URL", "Genre", "Director", "Actors", "Rating", "Runtime" }); // Iterate over label and write to sheet Set<String> keyset = label.keySet(); // Setting Style for the Label Row Font font = workbook.createFont(); font.setFontHeight((short) 240); font.setFontName("Courier New"); font.setBold(true);//w w w.j av a 2s . c o m XSSFCellStyle labelStyle = workbook.createCellStyle(); labelStyle.setWrapText(true); labelStyle.setFont(font); // Setting column widths sheet.setColumnWidth(0, 4000); sheet.setColumnWidth(1, 8500); sheet.setColumnWidth(2, 4000); sheet.setColumnWidth(3, 4000); sheet.setColumnWidth(4, 3500); sheet.setColumnWidth(5, 9500); sheet.setColumnWidth(6, 5000); sheet.setColumnWidth(7, 4000); sheet.setColumnWidth(8, 3500); sheet.setColumnWidth(9, 4000); sheet.setColumnWidth(10, 3000); sheet.setColumnWidth(11, 4000); // Freezing the first row sheet.createFreezePane(0, 1); // Filling each cell with Label data for (String key : keyset) { Row row = sheet.createRow(0); Object[] objArr = label.get(key); int cellnum = 0; for (Object obj : objArr) { Cell cell = row.createCell(cellnum++); cell.setCellStyle(labelStyle); cell.setCellValue((String) obj); } } // Writing the excel file try { FileOutputStream out = new FileOutputStream(file); workbook.write(out); out.close(); System.out.println("Excel File Created"); } catch (Exception e) { e.printStackTrace(); } }
From source file:com.sec.ose.osi.report.standard.CoverSheetTemplate.java
License:Open Source License
protected void createTitle() { short lineThickness = (short) (6 * BASE_HEIGHT); // Top Line// w w w . j av a2 s. c o m Row row = sheet.createRow(ROW_4); row.setHeight(lineThickness); XSSFCellStyle style = wb.createCellStyle(); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); setDummyTitleStyle(row, style); // Title row = sheet.createRow(ROW_5); row.setHeightInPoints(100); sheet.addMergedRegion(CellRangeAddress.valueOf("B5:G5")); Font font = wb.createFont(); font.setFontHeightInPoints((short) 28); font.setFontName("Trebuchet MS"); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setFont(font); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); setDummyTitleStyle(row, style); row.getCell(COL_B).setCellValue("Open Source License Verification Report"); // Bottom Line row = sheet.createRow(ROW_6); row.setHeight(lineThickness); style = wb.createCellStyle(); style.setFillForegroundColor(DARK_BLUE); style.setFillPattern(CellStyle.SOLID_FOREGROUND); setDummyTitleStyle(row, style); }