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

List of usage examples for org.apache.poi.ss.usermodel Workbook 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;
}