List of usage examples for org.apache.poi.ss.usermodel CreationHelper createRichTextString
RichTextString createRichTextString(String text);
From source file:excel.CellComments.java
License:Apache License
public static void main(String[] args) throws IOException { try (Workbook wb = new XSSFWorkbook()) { CreationHelper factory = wb.getCreationHelper(); Sheet sheet = wb.createSheet();/*from ww w .ja va 2 s. c o m*/ Cell cell1 = sheet.createRow(3).createCell(5); cell1.setCellValue("F4"); Drawing<?> drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = factory.createClientAnchor(); Comment comment1 = drawing.createCellComment(anchor); RichTextString str1 = factory.createRichTextString("Hello, World!"); comment1.setString(str1); comment1.setAuthor("Apache POI"); cell1.setCellComment(comment1); Cell cell2 = sheet.createRow(2).createCell(2); cell2.setCellValue("C3"); Comment comment2 = drawing.createCellComment(anchor); RichTextString str2 = factory.createRichTextString("XSSF can set cell comments"); //apply custom font to the text in the comment Font font = wb.createFont(); font.setFontName("Arial"); font.setFontHeightInPoints((short) 14); font.setBold(true); font.setColor(IndexedColors.RED.getIndex()); str2.applyFont(font); comment2.setString(str2); comment2.setAuthor("Apache POI"); comment2.setAddress(new CellAddress("C3")); try (FileOutputStream out = new FileOutputStream("comments.xlsx")) { wb.write(out); } } }
From source file:excel.PoiWriteExcelFile.java
public static int generarReporte() { //Calendar cal=Calendar.getInstance(); Calendar cal = WorkMonitorUI.instante; try {/*w w w .j av a2 s . c om*/ FileOutputStream fileOut = new FileOutputStream("HH_" + instante.getDisplayName(Calendar.MONTH, Calendar.SHORT_FORMAT, Locale.getDefault()) .toUpperCase() + "_" + persona.getNombre().toUpperCase().charAt(0) + "." + persona.getApellido().toUpperCase() + "_" + instante.get(Calendar.YEAR) + ".xls"); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet worksheet = workbook.createSheet( cal.getDisplayName(Calendar.MONTH, Calendar.SHORT_FORMAT, Locale.getDefault()).toUpperCase() + "-" + cal.get(Calendar.YEAR)); HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.YELLOW.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 12); font.setFontName("Calibri"); font.setItalic(false); font.setBold(true); font.setColor(HSSFColor.BLACK.index); cellStyle.setFont(font); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle diasStyle = workbook.createCellStyle(); diasStyle.setFillForegroundColor(HSSFColor.SEA_GREEN.index); diasStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); font = workbook.createFont(); font.setFontHeightInPoints((short) 11); font.setFontName("Calibri"); font.setItalic(false); font.setBold(true); font.setColor(HSSFColor.WHITE.index); diasStyle.setFont(font); diasStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); diasStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); diasStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); diasStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); diasStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); diasStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle schedStyle = workbook.createCellStyle(); schedStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); schedStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont font3 = workbook.createFont(); font3.setFontHeightInPoints((short) 11); font3.setFontName("Calibri"); font3.setItalic(false); font3.setColor(HSSFColor.BLACK.index); schedStyle.setFont(font3); schedStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); schedStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); schedStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); schedStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); schedStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); schedStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle workdayStyle = workbook.createCellStyle(); //workdayStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); workdayStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); workdayStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); workdayStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); workdayStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); workdayStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); workdayStyle.setWrapText(true); HSSFFont font2 = workbook.createFont(); font2.setFontHeightInPoints((short) 8); font2.setFontName("Serif"); font2.setItalic(false); //font2.setColor(HSSFColor.YELLOW.index); workdayStyle.setFont(font2); workdayStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle weekendStyle = workbook.createCellStyle(); weekendStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); weekendStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); weekendStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); weekendStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); weekendStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); weekendStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); weekendStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); weekendStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle horarioStyle = workbook.createCellStyle(); horarioStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); horarioStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); horarioStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); horarioStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); horarioStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); horarioStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); horarioStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); horarioStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont font4 = workbook.createFont(); font4.setFontHeightInPoints((short) 10); font4.setFontName("Serif"); font4.setItalic(false); font4.setBold(true); //font2.setColor(HSSFColor.YELLOW.index); horarioStyle.setFont(font4); // index from 0,0... cell A1 is cell(0,0) HSSFRow row1 = worksheet.createRow((short) 0); row1.setHeight((short) 500); //System.out.println("cal.get(Calendar.YEAR)="+cal.get(Calendar.YEAR)); HSSFCell cellA1 = row1.createCell((short) 0); cellA1.setCellValue( cal.getDisplayName(Calendar.MONTH, Calendar.SHORT_FORMAT, Locale.getDefault()).toUpperCase() + "-" + cal.get(Calendar.YEAR)); cellA1.setCellStyle(cellStyle); HSSFRow row2 = worksheet.createRow((short) 1); HSSFCell cellA4 = row2.createCell((short) 0); cellA4.setCellValue("Horario"); cellA4.setCellStyle(horarioStyle); //row2.setHeight((short)500); HSSFRow row3 = worksheet.createRow((short) 2); HSSFCell cellA3 = row3.createCell((short) 0); cellA3.setCellValue("Inicio - Trmino"); cellA3.setCellStyle(diasStyle); Calendar hora = Calendar.getInstance(); hora.set(Calendar.HOUR_OF_DAY, 9); hora.set(Calendar.MINUTE, 0); hora.set(Calendar.SECOND, 0); SimpleDateFormat sdf = new SimpleDateFormat("HH:mm"); HSSFCell cellXn; for (int i = 0; i < 29; ++i) { HSSFRow row = worksheet.createRow((short) i + 3); row.setHeight((short) 500); cellXn = row.createCell((short) 0); String horaIni = sdf.format(hora.getTime()); hora.add(Calendar.MINUTE, 30); String horaFin = sdf.format(hora.getTime()); cellXn.setCellValue(horaIni + " - " + horaFin); cellXn.setCellStyle(schedStyle); } System.out.println("cal.get(Calendar.MONTH)1=" + cal.get(Calendar.MONTH)); cal.add(Calendar.DAY_OF_MONTH, -cal.get(Calendar.DAY_OF_MONTH) + 1); int diasMes = cal.getActualMaximum(Calendar.DAY_OF_MONTH); System.out.println("cal.get(Calendar.MONTH)2=" + cal.get(Calendar.MONTH)); sdf = new SimpleDateFormat("EEEE d"); System.out.println( "cal.getActualMaximum(Calendar.DAY_OF_MONTH)1=" + cal.getActualMaximum(Calendar.DAY_OF_MONTH)); for (int i = 0; i < diasMes; ++i) { cellXn = row2.createCell((short) i + 1); String dia = sdf.format(cal.getTime()); dia = Character.toUpperCase(dia.charAt(0)) + dia.substring(1); cellXn.setCellValue(dia); cellXn.setCellStyle(horarioStyle); //System.out.println("cal.get(Calendar.DAY_OF_MONTH)="+cal.get(Calendar.DAY_OF_MONTH)); cal.add(Calendar.DAY_OF_MONTH, 1); } for (int i = 0; i < diasMes; ++i) { cellXn = row3.createCell((short) i + 1); cellXn.setCellValue("Descripcin"); cellXn.setCellStyle(diasStyle); } System.out.println( "cal.getActualMaximum(Calendar.DAY_OF_MONTH)2=" + cal.getActualMaximum(Calendar.DAY_OF_MONTH)); // Retroceder mes para que quede como estaba cal.add(Calendar.MONTH, -1); //cal.add(Calendar.DAY_OF_MONTH, -1); System.out.println( "cal.getActualMaximum(Calendar.DAY_OF_MONTH)3=" + cal.getActualMaximum(Calendar.DAY_OF_MONTH)); HhDao hhDao = new HhDao(); Object[][] hh = new Object[29][cal.getActualMaximum(Calendar.DAY_OF_MONTH)]; hh = hhDao.getByMes(WorkMonitorUI.persona.getId(), cal.getTime()); cal.set(Calendar.DAY_OF_MONTH, 1); Sheet sheet = workbook.getSheetAt(0); sdf = new SimpleDateFormat("EEEE"); HSSFPatriarch _drawing = (HSSFPatriarch) sheet.createDrawingPatriarch(); CreationHelper factory = workbook.getCreationHelper(); for (int i = 0; i < 29; ++i) { Row r = sheet.getRow(i + 3); for (int j = 0; j < diasMes; ++j) { if (hh[i][j].toString() != "") { cellXn = (HSSFCell) r.createCell((short) j + 1); Hh _hh = (Hh) hh[i][j]; cellXn.setCellValue( _hh.getTarea().getNombre().trim() + ": " + _hh.getActividad().getNombre().trim()); HSSFAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5); org.apache.poi.ss.usermodel.Comment comment = _drawing.createComment(anchor); String comentario = _hh.getTarea().getComentario().toLowerCase(); if (_hh.getComentario() != null) comentario = comentario + _hh.getComentario().toLowerCase(); RichTextString str = factory.createRichTextString(comentario); comment.setString(str); cellXn.setCellComment(comment); } else { cellXn = (HSSFCell) r.createCell((short) j + 1); cellXn.setCellValue(""); } //System.out.println("sdf.format(cal.getTime())="+sdf.format(cal.getTime())); if (Arrays.asList("sbado", "domingo").contains(sdf.format(cal.getTime()))) cellXn.setCellStyle(weekendStyle); else cellXn.setCellStyle(workdayStyle); sheet.setColumnWidth(j, 5000); cal.add(Calendar.DAY_OF_MONTH, 1); //sheet.autoSizeColumn(j); } // Retroceder mes para que quede como estaba cal.add(Calendar.MONTH, -1); System.out.println("cal.get(Calendar.MONTH)3=" + cal.get(Calendar.MONTH)); cal.set(Calendar.DAY_OF_MONTH, 1); } sheet.setColumnWidth(diasMes, 5000); WorkMonitorUI.instante = Calendar.getInstance(); sheet.setColumnWidth(0, 5000); sheet.createFreezePane(1, 3); // Freeze just one row //sheet.createFreezePane( 0, 1, 0, 1 ); workbook.write(fileOut); fileOut.flush(); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); return -1; } catch (IOException e) { e.printStackTrace(); return -2; } return 1; }
From source file:htmlparser.xls.XLSFile.java
public void createScoreTable(XSSFColor oddrow_color, XSSFColor title_bg_color, XSSFColor title_font_color, int highlight) { String sheetname = WorkbookUtil.createSafeSheetName(this.parser.getCompetitionName()); this.scoresheet = this.excelfile.createSheet(sheetname); CreationHelper createHelper = this.excelfile.getCreationHelper(); CellStyle cellStyle = this.excelfile.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setBorderBottom(CellStyle.BORDER_THIN); cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); cellStyle.setBorderLeft(CellStyle.BORDER_THIN); cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); cellStyle.setBorderRight(CellStyle.BORDER_THIN); cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex()); cellStyle.setBorderTop(CellStyle.BORDER_THIN); cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); int rows = 0; Row headline = this.scoresheet.createRow(rows); Cell cheadline = headline.createCell(0); cheadline.setCellValue(createHelper.createRichTextString(this.parser.getCompetitionName())); XSSFCellStyle customstyle = (XSSFCellStyle) this.excelfile.createCellStyle(); customstyle.cloneStyleFrom(cellStyle); XSSFFont fh = (XSSFFont) this.excelfile.createFont(); fh.setFontHeightInPoints((short) 16); fh.setBoldweight(Font.BOLDWEIGHT_BOLD); fh.setColor(title_bg_color);//from w w w . j a v a 2 s. co m customstyle.setFont(fh); cheadline.setCellStyle(customstyle); int length = this.parser.getTeams().get(0).getData().size(); CellRangeAddress headrow = new CellRangeAddress(rows, rows, 0, length); this.scoresheet.addMergedRegion(headrow); RegionUtil.setBorderBottom(CellStyle.BORDER_THIN, headrow, this.scoresheet, this.excelfile); RegionUtil.setBottomBorderColor(IndexedColors.BLACK.getIndex(), headrow, this.scoresheet, this.excelfile); RegionUtil.setBorderLeft(CellStyle.BORDER_THIN, headrow, this.scoresheet, this.excelfile); RegionUtil.setLeftBorderColor(IndexedColors.BLACK.getIndex(), headrow, this.scoresheet, this.excelfile); RegionUtil.setBorderRight(CellStyle.BORDER_THIN, headrow, this.scoresheet, this.excelfile); RegionUtil.setRightBorderColor(IndexedColors.BLACK.getIndex(), headrow, this.scoresheet, this.excelfile); RegionUtil.setBorderTop(CellStyle.BORDER_THIN, headrow, this.scoresheet, this.excelfile); RegionUtil.setTopBorderColor(IndexedColors.BLACK.getIndex(), headrow, this.scoresheet, this.excelfile); rows++; Row colNms = this.scoresheet.createRow(rows++); customstyle = (XSSFCellStyle) this.excelfile.createCellStyle(); customstyle.cloneStyleFrom(cellStyle); customstyle.setFillForegroundColor(title_bg_color); customstyle.setFillPattern(CellStyle.SOLID_FOREGROUND); XSSFFont f1 = (XSSFFont) this.excelfile.createFont(); f1.setColor(title_font_color); f1.setBoldweight(Font.BOLDWEIGHT_BOLD); customstyle.setFont(f1); int cCN = 0; for (String s : this.shColNms) { Cell c = colNms.createCell(cCN); c.setCellValue(createHelper.createRichTextString(s)); c.setCellStyle(customstyle); cCN++; } double ordNum = 1; customstyle = (XSSFCellStyle) this.excelfile.createCellStyle(); customstyle.cloneStyleFrom(cellStyle); customstyle.setFillForegroundColor(oddrow_color); customstyle.setFillPattern(CellStyle.SOLID_FOREGROUND); for (Team t : this.parser.getTeams()) { Row r = this.scoresheet.createRow(rows++); int cell = 0; Cell order = r.createCell(cell++); order.setCellValue(ordNum++); if (rows % 2 == 0) order.setCellStyle(customstyle); else order.setCellStyle(cellStyle); for (String s : t.getData()) { Cell c = r.createCell(cell); c.setCellValue(createHelper.createRichTextString(s)); if (rows % 2 == 0) c.setCellStyle(customstyle); else c.setCellStyle(cellStyle); cell++; } } for (int i = 0; i <= length; i++) { this.scoresheet.autoSizeColumn(i); } if (highlight >= 0) { highlight += 2; Row r = this.scoresheet.getRow(highlight); customstyle = (XSSFCellStyle) this.excelfile.createCellStyle(); customstyle.cloneStyleFrom(r.getCell(0).getCellStyle()); Font bold = this.excelfile.createFont(); bold.setBoldweight(Font.BOLDWEIGHT_BOLD); customstyle.setFont(bold); for (Cell c : r) { c.setCellStyle(customstyle); } } }
From source file:htmlparser.xls.XLSFile.java
public void createMatchTable(XSSFColor oddrow_color, XSSFColor title_bg_color, XSSFColor title_font_color) { String sheetname = WorkbookUtil.createSafeSheetName(this.parser.getTeamName()); this.matchsheet = this.excelfile.createSheet(sheetname); CreationHelper createHelper = this.excelfile.getCreationHelper(); CellStyle cellStyle = this.excelfile.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setBorderBottom(CellStyle.BORDER_THIN); cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); cellStyle.setBorderLeft(CellStyle.BORDER_THIN); cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); cellStyle.setBorderRight(CellStyle.BORDER_THIN); cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex()); cellStyle.setBorderTop(CellStyle.BORDER_THIN); cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); int rows = 0; Row headline = this.matchsheet.createRow(rows); Cell cheadline = headline.createCell(0); cheadline.setCellValue(createHelper.createRichTextString(this.parser.getTeamName())); XSSFCellStyle customstyle = (XSSFCellStyle) this.excelfile.createCellStyle(); customstyle.cloneStyleFrom(cellStyle); XSSFFont fh = (XSSFFont) this.excelfile.createFont(); fh.setFontHeightInPoints((short) 16); fh.setBoldweight(Font.BOLDWEIGHT_BOLD); fh.setColor(title_bg_color);//from ww w . j a v a 2s . c o m customstyle.setFont(fh); cheadline.setCellStyle(customstyle); int length = this.parser.getMatches().get(0).getData().size(); CellRangeAddress headrow = new CellRangeAddress(rows, rows, 0, length - 1); this.matchsheet.addMergedRegion(headrow); RegionUtil.setBorderBottom(CellStyle.BORDER_THIN, headrow, this.matchsheet, this.excelfile); RegionUtil.setBottomBorderColor(IndexedColors.BLACK.getIndex(), headrow, this.matchsheet, this.excelfile); RegionUtil.setBorderLeft(CellStyle.BORDER_THIN, headrow, this.matchsheet, this.excelfile); RegionUtil.setLeftBorderColor(IndexedColors.BLACK.getIndex(), headrow, this.matchsheet, this.excelfile); RegionUtil.setBorderRight(CellStyle.BORDER_THIN, headrow, this.matchsheet, this.excelfile); RegionUtil.setRightBorderColor(IndexedColors.BLACK.getIndex(), headrow, this.matchsheet, this.excelfile); RegionUtil.setBorderTop(CellStyle.BORDER_THIN, headrow, this.matchsheet, this.excelfile); RegionUtil.setTopBorderColor(IndexedColors.BLACK.getIndex(), headrow, this.matchsheet, this.excelfile); rows++; Row colNms = this.matchsheet.createRow(rows++); customstyle = (XSSFCellStyle) this.excelfile.createCellStyle(); customstyle.cloneStyleFrom(cellStyle); customstyle.setFillForegroundColor(title_bg_color); customstyle.setFillPattern(CellStyle.SOLID_FOREGROUND); XSSFFont f1 = (XSSFFont) this.excelfile.createFont(); f1.setColor(title_font_color); f1.setBoldweight(Font.BOLDWEIGHT_BOLD); customstyle.setFont(f1); int cCN = 0; for (String s : this.mhColNms) { Cell c = colNms.createCell(cCN); c.setCellValue(createHelper.createRichTextString(s)); c.setCellStyle(customstyle); cCN++; } customstyle = (XSSFCellStyle) this.excelfile.createCellStyle(); customstyle.cloneStyleFrom(cellStyle); customstyle.setFillForegroundColor(oddrow_color); customstyle.setFillPattern(CellStyle.SOLID_FOREGROUND); for (Match t : this.parser.getMatches()) { Row r = this.matchsheet.createRow(rows++); int cell = 0; for (String s : t.getData()) { Cell c = r.createCell(cell); c.setCellValue(createHelper.createRichTextString(s)); if (rows % 2 == 0) c.setCellStyle(customstyle); else c.setCellStyle(cellStyle); cell++; } } for (int i = 0; i < length; i++) { this.matchsheet.autoSizeColumn(i); } }
From source file:Import.Utils.XSSFConvert.java
public void convert() throws IOException { Workbook[] wbs = new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() }; for (int i = 0; i < wbs.length; i++) { Workbook wb = wbs[i];//from w ww. j av a2 s .co m CreationHelper createHelper = wb.getCreationHelper(); // create a new sheet org.apache.poi.ss.usermodel.Sheet s = wb.createSheet(); // declare a row object reference Row r = null; // declare a cell object reference Cell c = null; // create 2 cell styles CellStyle cs = wb.createCellStyle(); CellStyle cs2 = wb.createCellStyle(); DataFormat df = wb.createDataFormat(); // create 2 fonts objects Font f = wb.createFont(); Font f2 = wb.createFont(); // Set font 1 to 12 point type, blue and bold f.setFontHeightInPoints((short) 12); f.setColor(IndexedColors.RED.getIndex()); f.setBoldweight(Font.BOLDWEIGHT_BOLD); // Set font 2 to 10 point type, red and bold f2.setFontHeightInPoints((short) 10); f2.setColor(IndexedColors.RED.getIndex()); f2.setBoldweight(Font.BOLDWEIGHT_BOLD); // Set cell style and formatting cs.setFont(f); cs.setDataFormat(df.getFormat("#,##0.0")); // Set the other cell style and formatting cs2.setBorderBottom(cs2.BORDER_THIN); cs2.setDataFormat(df.getFormat("text")); cs2.setFont(f2); // Define a few rows for (int rownum = 0; rownum < 30; rownum++) { r = s.createRow(rownum); for (int cellnum = 0; cellnum < 10; cellnum += 2) { c = r.createCell(cellnum); Cell c2 = r.createCell(cellnum + 1); c.setCellValue((double) rownum + (cellnum / 10)); c2.setCellValue(createHelper.createRichTextString("Hello! " + cellnum)); } } // Save String filename = "workbook.xls"; if (wb instanceof XSSFWorkbook) { filename = filename + "x"; } FileOutputStream out = null; try { out = new FileOutputStream(filename); } catch (FileNotFoundException ex) { Logger.getLogger(XSSFConvert.class.getName()).log(Level.SEVERE, null, ex); } wb.write(out); out.close(); } }
From source file:it.eng.spagobi.engines.console.exporter.types.ExporterExcel.java
License:Mozilla Public License
public CellStyle[] fillSheetHeader(Sheet sheet, Workbook wb, CreationHelper createHelper, int beginRowHeaderData, int beginColumnHeaderData) { CellStyle hCellStyle = buildHeaderCellStyle(sheet); int colnum = extractedFieldsMetaData.size(); Row row = sheet.getRow(beginRowHeaderData); CellStyle[] cellTypes = new CellStyle[colnum]; // array for numbers patterns storage for (int j = 0; j < colnum; j++) { Cell cell = row.createCell(j + beginColumnHeaderData); cell.setCellType(HSSFCell.CELL_TYPE_STRING); IFieldMetaData fieldMetaData = extractedFieldsMetaData.get(j); String fieldName = fieldMetaData.getName(); String format = (String) fieldMetaData.getProperty("format"); String alias = (String) fieldMetaData.getAlias(); Boolean visible = (Boolean) fieldMetaData.getProperty("visible"); if (extractedFields != null && extractedFields.get(j) != null) { Object f = extractedFields.get(j); logger.debug("Extracted field " + fieldName + " is instance of " + f.getClass().getName()); if (f instanceof Field) { Field field = (Field) f; fieldName = field.getName(); if (field.getPattern() != null) { format = field.getPattern(); }//www. j a v a 2 s . co m } } CellStyle aCellStyle = wb.createCellStyle(); if (format != null) { short formatInt = HSSFDataFormat.getBuiltinFormat(format); aCellStyle.setDataFormat(formatInt); cellTypes[j] = aCellStyle; } if (visible != null && visible.booleanValue() == true) { if (alias != null && !alias.equals("")) { cell.setCellValue(createHelper.createRichTextString(alias)); } else { cell.setCellValue(createHelper.createRichTextString(fieldName)); } cell.setCellStyle(hCellStyle); } } return cellTypes; }
From source file:it.eng.spagobi.engines.console.exporter.types.ExporterExcel.java
License:Mozilla Public License
public void fillSheetData(Sheet sheet, Workbook wb, CreationHelper createHelper, CellStyle[] cellTypes, int beginRowData, int beginColumnData) { CellStyle dCellStyle = buildDataCellStyle(sheet); int rownum = beginRowData; short formatIndexInt = HSSFDataFormat.getBuiltinFormat("#,##0"); CellStyle cellStyleInt = wb.createCellStyle(); // cellStyleInt is the default cell style for integers cellStyleInt.cloneStyleFrom(dCellStyle); cellStyleInt.setDataFormat(formatIndexInt); short formatIndexDoub = HSSFDataFormat.getBuiltinFormat("#,##0.00"); CellStyle cellStyleDoub = wb.createCellStyle(); // cellStyleDoub is the default cell style for doubles cellStyleDoub.cloneStyleFrom(dCellStyle); cellStyleDoub.setDataFormat(formatIndexDoub); CellStyle cellStyleDate = wb.createCellStyle(); // cellStyleDate is the default cell style for dates cellStyleDate.cloneStyleFrom(dCellStyle); //cellStyleDate.setDataFormat(HSSFDataFormat.getBuiltinFormat("yy-m-d h:mm")); cellStyleDate.setDataFormat(HSSFDataFormat.getBuiltinFormat("yy-m-d")); for (int i = 0; i < numberOfRows; i++) { Row rowVal = sheet.getRow(rownum); IRecord record = (IRecord) dataStore.getRecordAt(i); List fields = record.getFields(); int length = extractedFieldsMetaData.size(); for (int fieldIndex = 0; fieldIndex < length; fieldIndex++) { IFieldMetaData metaField = extractedFieldsMetaData.get(fieldIndex); IField f = (IField) record.getFieldAt((Integer) metaField.getProperty("index")); if (f != null && f.getValue() != null) { Boolean visible = (Boolean) metaField.getProperty("visible"); if (visible) { Class c = metaField.getType(); logger.debug("Column [" + (fieldIndex) + "] class is equal to [" + c.getName() + "]"); if (rowVal == null) { rowVal = sheet.createRow(rownum); }//from w w w .j a va 2s. co m Cell cell = rowVal.createCell(fieldIndex + beginColumnData); cell.setCellStyle(dCellStyle); if (Integer.class.isAssignableFrom(c) || Short.class.isAssignableFrom(c)) { logger.debug("Column [" + (fieldIndex + 1) + "] type is equal to [" + "INTEGER" + "]"); Number val = (Number) f.getValue(); cell.setCellValue(val.intValue()); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellStyle( (cellTypes[fieldIndex] != null) ? cellTypes[fieldIndex] : cellStyleInt); } else if (Number.class.isAssignableFrom(c)) { logger.debug("Column [" + (fieldIndex + 1) + "] type is equal to [" + "NUMBER" + "]"); Number val = (Number) f.getValue(); cell.setCellValue(val.doubleValue()); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellStyle( (cellTypes[fieldIndex] != null) ? cellTypes[fieldIndex] : cellStyleDoub); } else if (String.class.isAssignableFrom(c)) { logger.debug("Column [" + (fieldIndex + 1) + "] type is equal to [" + "STRING" + "]"); String val = (String) f.getValue(); cell.setCellValue(createHelper.createRichTextString(val)); cell.setCellType(HSSFCell.CELL_TYPE_STRING); } else if (Boolean.class.isAssignableFrom(c)) { logger.debug("Column [" + (fieldIndex + 1) + "] type is equal to [" + "BOOLEAN" + "]"); Boolean val = (Boolean) f.getValue(); cell.setCellValue(val.booleanValue()); cell.setCellType(HSSFCell.CELL_TYPE_BOOLEAN); } else if (Date.class.isAssignableFrom(c)) { logger.debug("Column [" + (fieldIndex + 1) + "] type is equal to [" + "DATE" + "]"); Date val = (Date) f.getValue(); SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); String dtString = df.format(val); cell.setCellValue(dtString); cell.setCellType(HSSFCell.CELL_TYPE_STRING); } else { logger.warn("Column [" + (fieldIndex + 1) + "] type is equal to [" + "???" + "]"); String val = f.getValue().toString(); cell.setCellValue(createHelper.createRichTextString(val)); cell.setCellType(HSSFCell.CELL_TYPE_STRING); } } } } rownum++; } }
From source file:it.eng.spagobi.engines.qbe.crosstable.exporter.CrosstabXLSExporter.java
License:Mozilla Public License
protected int buildDataMatrix(Sheet sheet, CrossTab cs, int rowOffset, int columnOffset, CreationHelper createHelper, MeasureFormatter measureFormatter) throws JSONException { CellStyle cellStyleForNA = buildNACellStyle(sheet); Map<Integer, CellStyle> decimalFormats = new HashMap<Integer, CellStyle>(); int endRowNum = 0; for (int i = 0; i < cs.getDataMatrix().length; i++) { for (int j = 0; j < cs.getDataMatrix()[0].length; j++) { String text = (String) cs.getDataMatrix()[i][j]; int rowNum = rowOffset + i; int columnNum = columnOffset + j; Row row = sheet.getRow(rowNum); if (row == null) { row = sheet.createRow(rowNum); }//from w w w . jav a 2 s. c om endRowNum = rowNum; Cell cell = row.createCell(columnNum); try { double value = Double.parseDouble(text); int decimals = measureFormatter.getFormatXLS(i, j); Double valueFormatted = measureFormatter.applyScaleFactor(value, i, j); cell.setCellValue(valueFormatted); cell.setCellType(this.getCellTypeNumeric()); cell.setCellStyle( getNumberFormat(decimals, decimalFormats, sheet, createHelper, cs.getCellType(i, j))); } catch (NumberFormatException e) { logger.debug("Text " + text + " is not recognized as a number"); cell.setCellValue(createHelper.createRichTextString(text)); cell.setCellType(this.getCellTypeString()); cell.setCellStyle(cellStyleForNA); } } } return endRowNum; }
From source file:it.eng.spagobi.engines.qbe.crosstable.exporter.CrosstabXLSExporter.java
License:Mozilla Public License
/** * Builds the rows' headers recursively with this order: * |-----|-----|-----|// www .j av a2 s . c om * | | | 3 | * | | |-----| * | | 2 | 4 | * | | |-----| * | 1 | | 5 | * | |-----|-----| * | | | 7 | * | | 6 |-----| * | | | 8 | * |-----|-----|-----| * | | | 11 | * | 9 | 10 |-----| * | | | 12 | * |-----|-----|-----| * * @param sheet The sheet of the XLS file * @param siblings The siblings nodes of the headers structure * @param rowNum The row number where the first sibling must be inserted * @param columnNum The column number where the siblings must be inserted * @param createHelper The file creation helper * @throws JSONException */ protected void buildRowsHeaders(Sheet sheet, CrossTab cs, List<Node> siblings, int rowNum, int columnNum, CreationHelper createHelper, Locale locale, CellStyle cellStyle) throws JSONException { int rowsCounter = rowNum; for (int i = 0; i < siblings.size(); i++) { Node aNode = siblings.get(i); List<Node> childs = aNode.getChilds(); Row row = sheet.getRow(rowsCounter); Cell cell = row.createCell(columnNum); String text = (String) aNode.getDescription(); if (cs.isMeasureOnRow() && (childs == null || childs.size() <= 0)) { //apply the measure scale factor text = MeasureScaleFactorOption.getScaledName(text, cs.getMeasureScaleFactor(text), locale); } cell.setCellValue(createHelper.createRichTextString(text)); cell.setCellType(this.getCellTypeString()); cell.setCellStyle(cellStyle); int descendants = aNode.getLeafsNumber(); if (descendants > 1) { sheet.addMergedRegion(new CellRangeAddress(rowsCounter, //first row (0-based) rowsCounter + descendants - 1, //last row (0-based) columnNum, //first column (0-based) columnNum //last column (0-based) )); } if (childs != null && childs.size() > 0) { buildRowsHeaders(sheet, cs, childs, rowsCounter, columnNum + 1, createHelper, locale, cellStyle); } int increment = descendants > 1 ? descendants : 1; rowsCounter = rowsCounter + increment; } }
From source file:it.eng.spagobi.engines.qbe.crosstable.exporter.CrosstabXLSExporter.java
License:Mozilla Public License
/** * Add the title of the columns in the row headers * @param sheet//from ww w . ja v a2 s. c o m * @param titles list of titles * @param columnHeadersNumber number of column headers * @param startColumn first column of the crosstab in the xls * @param startRow first row of the crosstab in the xls * @param createHelper * @throws JSONException */ protected void buildRowHeaderTitle(Sheet sheet, CrossTab cs, int columnHeadersNumber, int startColumn, int startRow, CreationHelper createHelper, Locale locale, CellStyle cellStyle) throws JSONException { List<String> titles = cs.getRowHeadersTitles(); if (titles != null) { Row row = sheet.getRow(startRow + columnHeadersNumber); for (int i = 0; i < titles.size(); i++) { Cell cell = row.createCell(startColumn + i); String text = titles.get(i); cell.setCellValue(createHelper.createRichTextString(text)); cell.setCellType(this.getCellTypeString()); cell.setCellStyle(cellStyle); } if (cs.isMeasureOnRow()) { Cell cell = row.createCell(startColumn + titles.size()); String text = "Measures"; if (locale != null) { text = EngineMessageBundle.getMessage("worksheet.export.crosstab.header.measures", locale); } cell.setCellValue(createHelper.createRichTextString(text)); cell.setCellType(this.getCellTypeString()); cell.setCellStyle(cellStyle); } } }