Example usage for org.apache.poi.ss.usermodel Workbook createDataFormat

List of usage examples for org.apache.poi.ss.usermodel Workbook createDataFormat

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. createDataFormat

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Workbook createDataFormat.

Prototype

DataFormat createDataFormat();

Source Link

Document

Returns the instance of DataFormat for this workbook.

Usage

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;
    InputStream inp = null;//  w ww .j av  a 2  s  .c o  m

    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  w w  w . j  a  v  a 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// ww  w  . jav a2 s  .co 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 .  j a  va2  s .  c  om*/
    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//  ww  w .  j a v a2 s .c o m
 */
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 ww.  j a  v a2  s.c o  m*/
    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;//w  w  w  .ja v  a 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;
    InputStream inputStream = null;
    MetadataTableModel table = (MetadataTableModel) tableModel;
    Sheet sheet;//from w ww . j  av a2 s.c o m

    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  w  w  . j a va 2  s .co  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;
}

From source file:ro.dabuno.office.integration.LoadCalculator.java

/**
 * cell styles used for formatting calendar sheets
 *///from  w  ww .  j  a va 2  s  .c  o m
private static Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();

    CellStyle style;
    Font titleFont = wb.createFont();
    titleFont.setFontHeightInPoints((short) 14);
    titleFont.setFontName("Trebuchet MS");
    style = wb.createCellStyle();
    style.setFont(titleFont);
    style.setBorderBottom(BorderStyle.DOTTED);
    style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
    styles.put("title", style);

    Font itemFont = wb.createFont();
    itemFont.setFontHeightInPoints((short) 9);
    itemFont.setFontName("Trebuchet MS");
    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.LEFT);
    style.setFont(itemFont);
    styles.put("item_left", style);

    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.RIGHT);
    style.setFont(itemFont);
    styles.put("item_right", style);

    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.RIGHT);
    style.setFont(itemFont);
    style.setBorderRight(BorderStyle.DOTTED);
    style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
    style.setBorderBottom(BorderStyle.DOTTED);
    style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
    style.setBorderLeft(BorderStyle.DOTTED);
    style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
    style.setBorderTop(BorderStyle.DOTTED);
    style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
    style.setDataFormat(
            wb.createDataFormat().getFormat("_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)"));
    styles.put("input_$", style);

    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.RIGHT);
    style.setFont(itemFont);
    style.setBorderRight(BorderStyle.DOTTED);
    style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
    style.setBorderBottom(BorderStyle.DOTTED);
    style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
    style.setBorderLeft(BorderStyle.DOTTED);
    style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
    style.setBorderTop(BorderStyle.DOTTED);
    style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
    style.setDataFormat(wb.createDataFormat().getFormat("0.000%"));
    styles.put("input_%", style);

    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.RIGHT);
    style.setFont(itemFont);
    style.setBorderRight(BorderStyle.DOTTED);
    style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
    style.setBorderBottom(BorderStyle.DOTTED);
    style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
    style.setBorderLeft(BorderStyle.DOTTED);
    style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
    style.setBorderTop(BorderStyle.DOTTED);
    style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
    style.setDataFormat(wb.createDataFormat().getFormat("0"));
    styles.put("input_i", style);

    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setFont(itemFont);
    style.setDataFormat(wb.createDataFormat().getFormat("m/d/yy"));
    styles.put("input_d", style);

    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.RIGHT);
    style.setFont(itemFont);
    style.setBorderRight(BorderStyle.DOTTED);
    style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
    style.setBorderBottom(BorderStyle.DOTTED);
    style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
    style.setBorderLeft(BorderStyle.DOTTED);
    style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
    style.setBorderTop(BorderStyle.DOTTED);
    style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
    style.setDataFormat(wb.createDataFormat().getFormat("$##,##0.00"));
    style.setBorderBottom(BorderStyle.DOTTED);
    style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    styles.put("formula_$", style);

    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.RIGHT);
    style.setFont(itemFont);
    style.setBorderRight(BorderStyle.DOTTED);
    style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
    style.setBorderBottom(BorderStyle.DOTTED);
    style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
    style.setBorderLeft(BorderStyle.DOTTED);
    style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
    style.setBorderTop(BorderStyle.DOTTED);
    style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
    style.setDataFormat(wb.createDataFormat().getFormat("0"));
    style.setBorderBottom(BorderStyle.DOTTED);
    style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    styles.put("formula_i", style);

    return styles;
}