List of usage examples for org.apache.poi.ss.usermodel DataFormat getFormat
String getFormat(short index);
From source file:org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep_StyleFormatTest.java
License:Apache License
/** * Setup the data necessary for Excel Writer step * * @param fileType/*from w ww.j av a2 s . c o m*/ * @throws KettleException */ private void createStepData(String fileType) throws KettleException { stepData = new ExcelWriterStepData(); stepData.inputRowMeta = step.getInputRowMeta().clone(); stepData.outputRowMeta = step.getInputRowMeta().clone(); // we don't run transformation so ExcelWriterStep.processRow() doesn't get executed // we populate the ExcelWriterStepData with bare minimum required values CellReference cellRef = new CellReference(stepMeta.getStartingCell()); stepData.startingRow = cellRef.getRow(); stepData.startingCol = cellRef.getCol(); stepData.posX = stepData.startingCol; stepData.posY = stepData.startingRow; int numOfFields = stepData.inputRowMeta.size(); stepData.fieldnrs = new int[numOfFields]; stepData.linkfieldnrs = new int[numOfFields]; stepData.commentfieldnrs = new int[numOfFields]; for (int i = 0; i < numOfFields; i++) { stepData.fieldnrs[i] = i; stepData.linkfieldnrs[i] = -1; stepData.commentfieldnrs[i] = -1; } // we avoid reading/writing Excel files, so ExcelWriterStep.prepareNextOutputFile() doesn't get executed // create Excel workbook object stepData.wb = stepMeta.getExtension().equalsIgnoreCase("xlsx") ? new XSSFWorkbook() : new HSSFWorkbook(); stepData.sheet = stepData.wb.createSheet(); stepData.file = null; stepData.clearStyleCache(numOfFields); // we avoid reading template file from disk // so set beforehand cells with custom style and formatting DataFormat format = stepData.wb.createDataFormat(); Row xlsRow = stepData.sheet.createRow(0); // Cell F1 has custom style applied, used as template Cell cell = xlsRow.createCell(5); CellStyle cellStyle = stepData.wb.createCellStyle(); cellStyle.setBorderRight(BorderStyle.THICK); cellStyle.setFillPattern(FillPatternType.FINE_DOTS); cell.setCellStyle(cellStyle); // Cell G1 has same style, but also a custom data format cellStyle = stepData.wb.createCellStyle(); cellStyle.cloneStyleFrom(cell.getCellStyle()); cell = xlsRow.createCell(6); cellStyle.setDataFormat(format.getFormat("##0,000.0")); cell.setCellStyle(cellStyle); }
From source file:org.rhq.helpers.perftest.support.reporting.ExcelExporter.java
License:Open Source License
@Override public void export(Map<String, Long> timings, ITestResult result) { Workbook wb;//ww w . j a va 2s. co m InputStream inp = null; String fileName = getFileName(); // Check if Workbook is present - otherwise create it try { inp = new FileInputStream(fileName); wb = new HSSFWorkbook(inp); } catch (Exception e) { wb = new HSSFWorkbook(); } finally { if (inp != null) try { inp.close(); } catch (IOException e) { e.printStackTrace(); // TODO: Customise this generated block } } // Now write to it FileOutputStream fileOut = null; try { // Check if we have our sheet, otherwise create if (wb.getNumberOfSheets() == 0) { wb.createSheet("Overview"); } Sheet sheet = wb.getSheetAt(0); DataFormat df = wb.createDataFormat(); integerStyle = wb.createCellStyle(); integerStyle.setDataFormat(df.getFormat("#######0")); Font boldFont = wb.createFont(); boldFont.setBoldweight(Font.BOLDWEIGHT_BOLD); boldText = wb.createCellStyle(); boldText.setFont(boldFont); createOverviewHeaderIfNeeded(sheet); long time = getTotalTime(timings); createOverviewEntry(sheet, time, result); createDetailsSheet(wb, timings, result); // Write the output to a file File outFile = new File(fileName); System.out.println("ExcelExporter, writing to " + outFile.getAbsolutePath()); fileOut = new FileOutputStream(outFile); wb.write(fileOut); fileOut.flush(); } catch (Exception e) { e.printStackTrace(); } finally { try { if (fileOut != null) fileOut.close(); } catch (IOException e) { System.err.println("Failed to close the workbook: " + e.getMessage()); } } }
From source file:org.riflemansd.businessprofit.excel.ExcelExampleFont.java
License:Open Source License
public static void main(String[] args) { // create a new file FileOutputStream out = null;/*from www . ja va 2 s . co m*/ try { out = new FileOutputStream("workbook.xls"); } catch (FileNotFoundException ex) { Logger.getLogger(ExcelExampleFont.class.getName()).log(Level.SEVERE, null, ex); } // create a new workbook Workbook wb = new HSSFWorkbook(); // create a new sheet Sheet s = wb.createSheet(); // declare a row object reference Row r = null; // declare a cell object reference Cell c = null; // create 3 cell styles CellStyle cs = wb.createCellStyle(); CellStyle cs2 = wb.createCellStyle(); CellStyle cs3 = 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 f.setFontHeightInPoints((short) 12); //make it blue f.setColor((short) 0xc); // make it bold //arial is the default font f.setBoldweight(Font.BOLDWEIGHT_BOLD); //set font 2 to 10 point type f2.setFontHeightInPoints((short) 10); //make it red f2.setColor((short) Font.COLOR_RED); //make it bold f2.setBoldweight(Font.BOLDWEIGHT_BOLD); f2.setStrikeout(true); //set cell stlye cs.setFont(f); //set the cell format cs.setDataFormat(df.getFormat("#,##0.0")); //set a thin border cs2.setBorderBottom(cs2.BORDER_THIN); //fill w fg fill color cs2.setFillPattern((short) CellStyle.SOLID_FOREGROUND); //set the cell format to text see DataFormat for a full list cs2.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); // set the font cs2.setFont(f2); // set the sheet name in Unicode wb.setSheetName(0, "\u0422\u0435\u0441\u0442\u043E\u0432\u0430\u044F " + "\u0421\u0442\u0440\u0430\u043D\u0438\u0447\u043A\u0430"); // in case of plain ascii // wb.setSheetName(0, "HSSF Test"); // create a sheet with 30 rows (0-29) int rownum; for (rownum = (short) 0; rownum < 30; rownum++) { // create a row r = s.createRow(rownum); // on every other row if ((rownum % 2) == 0) { // make the row height bigger (in twips - 1/20 of a point) r.setHeight((short) 0x249); } //r.setRowNum(( short ) rownum); // create 10 cells (0-9) (the += 2 becomes apparent later for (short cellnum = (short) 0; cellnum < 10; cellnum += 2) { // create a numeric cell c = r.createCell(cellnum); // do some goofy math to demonstrate decimals c.setCellValue(rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000))); String cellValue; // create a string cell (see why += 2 in the c = r.createCell((short) (cellnum + 1)); // on every other row if ((rownum % 2) == 0) { // set this cell to the first cell style we defined c.setCellStyle(cs); // set the cell's string value to "Test" c.setCellValue("Test"); } else { c.setCellStyle(cs2); // set the cell's string value to "\u0422\u0435\u0441\u0442" c.setCellValue("\u0422\u0435\u0441\u0442"); } // make this column a bit wider s.setColumnWidth((short) (cellnum + 1), (short) ((50 * 8) / ((double) 1 / 20))); } } //draw a thick black border on the row at the bottom using BLANKS // advance 2 rows rownum++; rownum++; r = s.createRow(rownum); // define the third style to be the default // except with a thick black border at the bottom cs3.setBorderBottom(cs3.BORDER_THICK); //create 50 cells for (short cellnum = (short) 0; cellnum < 50; cellnum++) { //create a blank type cell (no value) c = r.createCell(cellnum); // set it to the thick black border style c.setCellStyle(cs3); } //end draw thick black border // demonstrate adding/naming and deleting a sheet // create a sheet, set its title then delete it s = wb.createSheet(); wb.setSheetName(1, "DeletedSheet"); wb.removeSheetAt(1); //end deleted sheet try { // write the workbook to the output stream // close our file (don't blow out our file handles wb.write(out); } catch (IOException ex) { Logger.getLogger(ExcelExampleFont.class.getName()).log(Level.SEVERE, null, ex); } try { out.close(); } catch (IOException ex) { Logger.getLogger(ExcelExampleFont.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:org.spdx.spdxspreadsheet.AbstractSheet.java
License:Apache License
/** * create the styles in the workbook//from w w w. j a va2 s . c o m */ private void createStyles(Workbook wb) { // create the styles this.checkboxStyle = wb.createCellStyle(); this.checkboxStyle.setAlignment(CellStyle.ALIGN_CENTER); this.checkboxStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); this.checkboxStyle.setBorderBottom(CellStyle.BORDER_THIN); this.checkboxStyle.setBorderLeft(CellStyle.BORDER_THIN); this.checkboxStyle.setBorderRight(CellStyle.BORDER_THIN); this.checkboxStyle.setBorderTop(CellStyle.BORDER_THIN); Font checkboxFont = wb.createFont(); checkboxFont.setFontHeight(FONT_SIZE); checkboxFont.setFontName(CHECKBOX_FONT_NAME); this.checkboxStyle.setFont(checkboxFont); this.dateStyle = wb.createCellStyle(); DataFormat df = wb.createDataFormat(); this.dateStyle.setDataFormat(df.getFormat("m/d/yy h:mm")); }
From source file:org.zephyrsoft.sdb2.StatisticsController.java
License:Open Source License
public void exportStatisticsAll(SongsModel songs, File targetExcelFile) { // collect basic data Map<String, Song> songsByUUID = new HashMap<>(); for (Song song : songs) { songsByUUID.put(song.getUUID(), song); }/* w w w.ja v a 2 s . c o m*/ List<String> months = statistics.getUsedMonths(); // create a new workbook Workbook workbook = new HSSFWorkbook(); // define formats CellStyle integerStyle = workbook.createCellStyle(); DataFormat df = workbook.createDataFormat(); integerStyle.setDataFormat(df.getFormat("0")); CellStyle textStyle = workbook.createCellStyle(); textStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); CellStyle textBoldStyle = workbook.createCellStyle(); textBoldStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); org.apache.poi.ss.usermodel.Font font = workbook.createFont(); font.setColor(org.apache.poi.ss.usermodel.Font.COLOR_RED); font.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD); textBoldStyle.setFont(font); for (String month : months) { Map<String, Integer> monthStatsByUUID = statistics.getStatisticsForMonth(month); Map<Song, Integer> monthStatsBySong = new TreeMap<>(); for (String uuid : monthStatsByUUID.keySet()) { Song song = songs.getByUUID(uuid); if (song != null) { monthStatsBySong.put(song, monthStatsByUUID.get(uuid)); } else { LOG.info("no song found in database for UUID {}", uuid); } } Sheet sheet = workbook.createSheet(month); Row row = null; int rownum = 0; row = sheet.createRow(rownum); int cellnum = 0; addTextCell(row, cellnum++, textBoldStyle, "Presentation Count"); addTextCell(row, cellnum++, textBoldStyle, "Song Title"); addTextCell(row, cellnum++, textBoldStyle, "Composer (Music)"); addTextCell(row, cellnum++, textBoldStyle, "Author (Text)"); addTextCell(row, cellnum++, textBoldStyle, "Publisher"); addTextCell(row, cellnum++, textBoldStyle, "Copyright Notes"); addTextCell(row, cellnum++, textBoldStyle, "Song Lyrics"); rownum++; for (Song song : monthStatsBySong.keySet()) { row = sheet.createRow(rownum); cellnum = 0; addIntegerCell(row, cellnum++, integerStyle, monthStatsBySong.get(song)); addTextCell(row, cellnum++, textStyle, song.getTitle()); addTextCell(row, cellnum++, textStyle, song.getComposer()); addTextCell(row, cellnum++, textStyle, song.getAuthorText()); addTextCell(row, cellnum++, textStyle, song.getPublisher()); addTextCell(row, cellnum++, textStyle, song.getAdditionalCopyrightNotes()); addTextCell(row, cellnum++, textStyle, song.getLyrics()); rownum++; } for (int i = 0; i < cellnum; i++) { sheet.autoSizeColumn(i); } sheet.createFreezePane(0, 1); } try (FileOutputStream out = new FileOutputStream(targetExcelFile)) { workbook.write(out); out.close(); LOG.info("all statistics exported"); } catch (IOException e) { ErrorDialog.openDialog(null, "Could not export the statistics to:\n" + targetExcelFile.getAbsolutePath() + "\n\nPlease verify that you have write access and the file is not opened by any other program!"); LOG.warn("could not write statistics to file", e); } }
From source file:output.ExcelM3Upgrad.java
/** * create a library of cell styles//from w ww . j av a 2s . c om */ private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<>(); DataFormat df = wb.createDataFormat(); Font font1 = wb.createFont(); CellStyle style; Font headerFont = wb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(headerFont); styles.put("header", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFont(font1); style.setLocked(false); styles.put("cell_centered", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFont(font1); style.setLocked(true); styles.put("cell_centered_locked", style); // style = createBorderedStyle(wb); // style.setAlignment(CellStyle.ALIGN_CENTER); // style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); // style.setFillPattern(CellStyle.SOLID_FOREGROUND); // style.setFont(headerFont); // style.setDataFormat(df.getFormat("d-mmm")); // styles.put("header_date", style); font1.setBoldweight(Font.BOLDWEIGHT_BOLD); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_LEFT); style.setFont(font1); styles.put("cell_b", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFont(font1); style.setLocked(false); styles.put("cell_b_centered", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFont(font1); style.setLocked(true); styles.put("cell_b_centered_locked", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(font1); style.setDataFormat(df.getFormat("d-mmm")); styles.put("cell_b_date", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(font1); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(df.getFormat("d-mmm")); styles.put("cell_g", style); Font font2 = wb.createFont(); font2.setColor(IndexedColors.BLUE.getIndex()); font2.setBoldweight(Font.BOLDWEIGHT_BOLD); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_LEFT); style.setFont(font2); styles.put("cell_bb", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(font1); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(df.getFormat("d-mmm")); styles.put("cell_bg", style); Font font3 = wb.createFont(); font3.setFontHeightInPoints((short) 14); font3.setColor(IndexedColors.DARK_BLUE.getIndex()); font3.setBoldweight(Font.BOLDWEIGHT_BOLD); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_LEFT); style.setFont(font3); style.setWrapText(true); styles.put("cell_h", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_LEFT); style.setWrapText(true); styles.put("cell_normal", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_CENTER); style.setWrapText(true); styles.put("cell_normal_centered", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setWrapText(true); style.setDataFormat(df.getFormat("d-mmm")); styles.put("cell_normal_date", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_LEFT); style.setIndention((short) 1); style.setWrapText(true); styles.put("cell_indented", style); style = createBorderedStyle(wb); style.setFillForegroundColor(IndexedColors.BLUE.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); styles.put("cell_blue", style); return styles; }
From source file:packtest.CreateUserDefinedDataFormats.java
License:Apache License
public static void main(String[] args) throws IOException { Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); Sheet sheet = wb.createSheet("format sheet"); CellStyle style;/*from w w w . j a v a2 s. c om*/ DataFormat format = wb.createDataFormat(); Row row; Cell cell; short rowNum = 0; short colNum = 0; row = sheet.createRow(rowNum); cell = row.createCell(colNum); cell.setCellValue(11111.25); style = wb.createCellStyle(); style.setDataFormat(format.getFormat("0.0")); cell.setCellStyle(style); row = sheet.createRow(++rowNum); cell = row.createCell(colNum); cell.setCellValue(11111.25); style = wb.createCellStyle(); style.setDataFormat(format.getFormat("#,##0.0000")); cell.setCellStyle(style); FileOutputStream fileOut = new FileOutputStream("ooxml_dataFormat.xlsx"); wb.write(fileOut); fileOut.close(); wb.close(); }
From source file:poi.xssf.usermodel.examples.CreateUserDefinedDataFormats.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); Sheet sheet = wb.createSheet("format sheet"); CellStyle style;/*from w w w . ja va 2 s. c om*/ DataFormat format = wb.createDataFormat(); Row row; Cell cell; short rowNum = 0; short colNum = 0; row = sheet.createRow(rowNum++); cell = row.createCell(colNum); cell.setCellValue(11111.25); style = wb.createCellStyle(); style.setDataFormat(format.getFormat("0.0")); cell.setCellStyle(style); row = sheet.createRow(rowNum++); cell = row.createCell(colNum); cell.setCellValue(11111.25); style = wb.createCellStyle(); style.setDataFormat(format.getFormat("#,##0.0000")); cell.setCellStyle(style); FileOutputStream fileOut = new FileOutputStream("ooxml_dataFormat.xlsx"); wb.write(fileOut); fileOut.close(); }
From source file:pt.webdetails.cda.exporter.CXlsExporter.java
License:Open Source License
public void export(final OutputStream out, final TableModel tableModel) throws ExporterException { // <Template file="testTemplate.xls"> // <RowOffset>3</RowOffset> // <ColumnOffset>2</ColumnOffset> // <WriteColumnNames>true</WriteColumnNames> // </Template> Workbook wb;/* ww w.ja v a2 s.c o m*/ InputStream inputStream = null; MetadataTableModel table = (MetadataTableModel) tableModel; Sheet sheet; int ignoreFirstXRows = 0; int rowOffset = 0; int columnOffset = 0; boolean writeColumns = true; boolean templateFound = false; String csvSeperator = ""; int numberOfHeaderRows = 0; if (templateSettings.keySet().size() > 0) { templateFound = true; try { //inputStream = new ClassPathResource(templateSettings.get("filename")).getInputStream(); inputStream = new FileInputStream(templatesDir + templateSettings.get("filename")); wb = new HSSFWorkbook(inputStream); sheet = wb.getSheetAt(0); if (templateSettings.containsKey("RowOffset")) { rowOffset = Integer.parseInt(templateSettings.get("RowOffset")); } if (templateSettings.containsKey("ColumnOffset")) { columnOffset = Integer.parseInt(templateSettings.get("ColumnOffset")); } if (templateSettings.containsKey("WriteColumnNames")) { writeColumns = Boolean.parseBoolean(templateSettings.get("WriteColumnNames")); } if (templateSettings.containsKey("CsvSeperator")) { csvSeperator = "\\" + templateSettings.get("CsvSeperator").toString(); } if (templateSettings.containsKey("WriteFirstXRowsAsHeader")) { numberOfHeaderRows = Integer.parseInt(templateSettings.get("WriteFirstXRowsAsHeader")); } } catch (Exception e) { throw new ExporterException("Error at loading TemplateFile", e); } } else { wb = new HSSFWorkbook(); sheet = wb.createSheet("Sheet1"); } DataFormat cf = wb.createDataFormat(); euroCellStyle = wb.createCellStyle(); euroCellStyle.setDataFormat(cf.getFormat("#,##0.00 \"\"")); doubleCellStyle = wb.createCellStyle(); doubleCellStyle.setDataFormat(cf.getFormat("0.00")); integerCellStyle = wb.createCellStyle(); integerCellStyle.setDataFormat(cf.getFormat("0")); percentCellStyle = wb.createCellStyle(); percentCellStyle.setDataFormat(cf.getFormat("0.00%")); dateCellStyle = wb.createCellStyle(); dateCellStyle.setDataFormat(cf.getFormat("dd.mm.yyyy")); datemonthCellStyle = wb.createCellStyle(); datemonthCellStyle.setDataFormat(cf.getFormat("mm.yyyy")); dateyearCellStyle = wb.createCellStyle(); dateyearCellStyle.setDataFormat(cf.getFormat("yyyy")); dateAndTimeCellStyle = wb.createCellStyle(); dateAndTimeCellStyle.setDataFormat(cf.getFormat("dd.mm.yyyy hh:mm:ss")); boolean interpretCsv = !csvSeperator.equals(""); if (writeColumns) { CellStyle headerCellStyle = null; if (templateFound) headerCellStyle = sheet.getRow(rowOffset).getCell(columnOffset).getCellStyle(); if (numberOfHeaderRows > 0) { ignoreFirstXRows = numberOfHeaderRows; for (int i = 0; i < numberOfHeaderRows; i++) { String[] seperatedRow = new String[0]; int colCount = table.getColumnCount(); if (interpretCsv) { seperatedRow = table.getValueAt(i, 0).toString().split(csvSeperator, -1); colCount = seperatedRow.length; } Row header = sheet.createRow(rowOffset); for (int col = 0; col < colCount; col++) { Cell cell = header.createCell(col + columnOffset); if (templateFound) cell.setCellStyle(headerCellStyle); if (interpretCsv) { cell.setCellValue(seperatedRow[col]); } else { cell.setCellValue(table.getColumnName(col)); } } rowOffset++; } } else { Row header = sheet.createRow(rowOffset); for (int col = 0; col < table.getColumnCount(); col++) { Cell cell = header.createCell(col + columnOffset); if (templateFound) cell.setCellStyle(headerCellStyle); cell.setCellValue(table.getColumnName(col)); } rowOffset++; } sheet.createFreezePane(0, rowOffset); } for (int r = ignoreFirstXRows; r < table.getRowCount(); r++) { CellStyle rowCellStyle = null; // if(templateFound) // try{ // rowCellStyle = sheet.getRow(rowOffset).getCell(columnOffset).getCellStyle(); // }catch (Exception e){} // // int rows = table.getRowCount(); // // Row r1 = sheet.getRow(rowOffset); // // int cols = r1.getLastCellNum(); // // Cell c1 = r1.getCell(columnOffset); Row row = sheet.getRow(r + rowOffset - ignoreFirstXRows); if (row == null) { row = sheet.createRow(r + rowOffset - ignoreFirstXRows); } int colCount; String[] seperatedRow = new String[0]; if (interpretCsv) { seperatedRow = table.getValueAt(r, 0).toString().split(csvSeperator); colCount = seperatedRow.length; } else { colCount = table.getColumnCount(); } for (int col = 0; col < colCount; col++) { Cell cell = null; if (templateFound) { cell = row.getCell(col + columnOffset); if (cell == null) { cell = row.createCell(col + columnOffset); } try { rowCellStyle = sheet.getRow(rowOffset).getCell(col + columnOffset).getCellStyle(); cell.setCellStyle(rowCellStyle); } catch (Exception e) { } } else { cell = row.createCell(col + columnOffset); } if (!interpretCsv) { try { setConvertedValue(cell, table.getValueAt(r, col), col, table); } catch (Exception e) { setConvertedValue(cell, Cell.CELL_TYPE_ERROR, col, table); } } else { setConvertedValue(cell, seperatedRow[col], col, table); } } } try { wb.write(out); } catch (IOException e) { throw new ExporterException("IO Exception converting to utf-8", e); } finally { if (templateSettings.keySet().size() > 0) { try { inputStream.close(); } catch (Exception e) { throw new ExporterException("Error at closing TemplateFile", e); } } } }
From source file:ro.dabuno.office.integration.BusinessPlan.java
License:Apache License
/** * create a library of cell styles//from w ww. ja va 2s . c o m */ private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); DataFormat df = wb.createDataFormat(); CellStyle style; Font headerFont = wb.createFont(); headerFont.setBold(true); style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.CENTER); style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setFont(headerFont); styles.put("header", style); style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.CENTER); style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setFont(headerFont); style.setDataFormat(df.getFormat("d-mmm")); styles.put("header_date", style); Font font1 = wb.createFont(); font1.setBold(true); style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.LEFT); style.setFont(font1); styles.put("cell_b", style); style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.CENTER); style.setFont(font1); styles.put("cell_b_centered", style); style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.RIGHT); style.setFont(font1); style.setDataFormat(df.getFormat("d-mmm")); styles.put("cell_b_date", style); style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.RIGHT); style.setFont(font1); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setDataFormat(df.getFormat("d-mmm")); styles.put("cell_g", style); Font font2 = wb.createFont(); font2.setColor(IndexedColors.BLUE.getIndex()); font2.setBold(true); style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.LEFT); style.setFont(font2); styles.put("cell_bb", style); style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.RIGHT); style.setFont(font1); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setDataFormat(df.getFormat("d-mmm")); styles.put("cell_bg", style); Font font3 = wb.createFont(); font3.setFontHeightInPoints((short) 14); font3.setColor(IndexedColors.DARK_BLUE.getIndex()); font3.setBold(true); style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.LEFT); style.setFont(font3); style.setWrapText(true); styles.put("cell_h", style); style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.LEFT); style.setWrapText(true); styles.put("cell_normal", style); style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.CENTER); style.setWrapText(true); styles.put("cell_normal_centered", style); style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.RIGHT); style.setWrapText(true); style.setDataFormat(df.getFormat("d-mmm")); styles.put("cell_normal_date", style); style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.LEFT); style.setIndention((short) 1); style.setWrapText(true); styles.put("cell_indented", style); style = createBorderedStyle(wb); style.setFillForegroundColor(IndexedColors.BLUE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); styles.put("cell_blue", style); return styles; }