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

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

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

Introduction

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

Prototype

CellStyle createCellStyle();

Source Link

Document

Create a new Cell style and add it to the workbook's style table

Usage

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);
    }//from w w w  . j  a  v a2s. 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  w  w .  j a  v a  2 s.  co  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.CreateCell.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
    CreationHelper creationHelper = wb.getCreationHelper();
    Sheet sheet = wb.createSheet("new sheet");

    // Create a row and put some cells in it. Rows are 0 based.
    Row row = sheet.createRow((short) 0);
    // Create a cell and put a value in it.
    Cell cell = row.createCell((short) 0);
    cell.setCellValue(1);//from   w  w w  .  j a  v  a  2  s.c  o m

    //numeric value
    row.createCell(1).setCellValue(1.2);

    //plain string value
    row.createCell(2).setCellValue("This is a string cell");

    //rich text string
    RichTextString str = creationHelper.createRichTextString("Apache");
    Font font = wb.createFont();
    font.setItalic(true);
    font.setUnderline(Font.U_SINGLE);
    str.applyFont(font);
    row.createCell(3).setCellValue(str);

    //boolean value
    row.createCell(4).setCellValue(true);

    //formula
    row.createCell(5).setCellFormula("SUM(A1:B1)");

    //date
    CellStyle style = wb.createCellStyle();
    style.setDataFormat(creationHelper.createDataFormat().getFormat("m/d/yy h:mm"));
    cell = row.createCell(6);
    cell.setCellValue(new Date());
    cell.setCellStyle(style);

    //hyperlink
    row.createCell(7).setCellFormula("SUM(A1:B1)");
    cell.setCellFormula("HYPERLINK(\"http://google.com\",\"Google\")");

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("ooxml-cell.xlsx");
    wb.write(fileOut);
    fileOut.close();
}

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 a  2s  .  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:packtest.FillsAndColors.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
    Sheet sheet = wb.createSheet("new sheet");

    // Create a row and put some cells in it. Rows are 0 based.
    Row row = sheet.createRow((short) 1);

    // Aqua background
    CellStyle style = wb.createCellStyle();
    style.setFillBackgroundColor(IndexedColors.AQUA.getIndex());
    style.setFillPattern(CellStyle.BIG_SPOTS);
    Cell cell = row.createCell((short) 1);
    cell.setCellValue(new XSSFRichTextString("X"));
    cell.setCellStyle(style);// w  ww  .  j a v a  2s .c  o m

    // Orange "foreground", foreground being the fill foreground not the font color.
    style = wb.createCellStyle();
    style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cell = row.createCell((short) 2);
    cell.setCellValue(new XSSFRichTextString("X"));
    cell.setCellStyle(style);

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream(Utils.getPath("fill_colors.xlsx"));
    wb.write(fileOut);
    fileOut.close();

}

From source file:packtest.HyperlinkExample.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();

    //cell style for hyperlinks
    //by default hyperlinks are blue and underlined
    CellStyle hlink_style = wb.createCellStyle();
    Font hlink_font = wb.createFont();
    hlink_font.setUnderline(Font.U_SINGLE);
    hlink_font.setColor(IndexedColors.BLUE.getIndex());
    hlink_style.setFont(hlink_font);//w w w  .  j a  v  a  2s  .  co  m

    Cell cell;
    Sheet sheet = wb.createSheet("Hyperlinks");
    //URL
    cell = sheet.createRow(0).createCell((short) 0);
    cell.setCellValue("URL Link");

    Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_URL);
    link.setAddress("http://poi.apache.org/");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);

    //link to a file in the current directory
    cell = sheet.createRow(1).createCell((short) 0);
    cell.setCellValue("File Link");
    link = createHelper.createHyperlink(Hyperlink.LINK_FILE);
    link.setAddress("link1.xls");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);

    //e-mail link
    cell = sheet.createRow(2).createCell((short) 0);
    cell.setCellValue("Email Link");
    link = createHelper.createHyperlink(Hyperlink.LINK_EMAIL);
    //note, if subject contains white spaces, make sure they are url-encoded
    link.setAddress("mailto:poi@apache.org?subject=Hyperlinks");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);

    //link to a place in this workbook

    //create a target sheet and cell
    Sheet sheet2 = wb.createSheet("Target Sheet");
    sheet2.createRow(0).createCell((short) 0).setCellValue("Target Cell");

    cell = sheet.createRow(3).createCell((short) 0);
    cell.setCellValue("Worksheet Link");
    Hyperlink link2 = createHelper.createHyperlink(Hyperlink.LINK_DOCUMENT);
    link2.setAddress("'Target Sheet'!A1");
    cell.setHyperlink(link2);
    cell.setCellStyle(hlink_style);

    FileOutputStream out = new FileOutputStream(Utils.getPath("hyperinks.xlsx"));
    wb.write(out);
    out.close();

}

From source file:packtest.NewLinesInCells.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
    Sheet sheet = wb.createSheet();//w w  w .  j  a v a 2 s  . c om

    Row row = sheet.createRow(2);
    Cell cell = row.createCell(2);
    cell.setCellValue("Use \n with word wrap on to create a new line");

    //to enable newlines you need set a cell styles with wrap=true
    CellStyle cs = wb.createCellStyle();
    cs.setWrapText(true);
    cell.setCellStyle(cs);

    //increase row height to accomodate two lines of text
    row.setHeightInPoints((2 * sheet.getDefaultRowHeightInPoints()));

    //adjust column width to fit the content
    sheet.autoSizeColumn(2);

    FileOutputStream fileOut = new FileOutputStream("ooxml-newlines.xlsx");
    wb.write(fileOut);
    fileOut.close();
}

From source file:packtest.WorkingWithBorders.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
    Sheet sheet = wb.createSheet("borders");

    // Create a row and put some cells in it. Rows are 0 based.
    Row row = sheet.createRow((short) 1);

    // Create a cell and put a value in it.
    Cell cell = row.createCell((short) 1);
    cell.setCellValue(4);//from w w w  .  j a  v  a  2s.  c om

    // Style the cell with borders all around.
    CellStyle style = wb.createCellStyle();
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.GREEN.getIndex());
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLUE.getIndex());
    style.setBorderTop(CellStyle.BORDER_MEDIUM_DASHED);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    cell.setCellStyle(style);

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("xssf-borders.xlsx");
    wb.write(fileOut);
    fileOut.close();
}

From source file:packtest.WorkingWithFonts.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
    Sheet sheet = wb.createSheet("Fonts");

    Font font0 = wb.createFont();
    font0.setColor(IndexedColors.BROWN.getIndex());
    CellStyle style0 = wb.createCellStyle();
    style0.setFont(font0);//  w w  w .j  a v  a  2s. c  om

    Font font1 = wb.createFont();
    font1.setFontHeightInPoints((short) 14);
    font1.setFontName("Courier New");
    font1.setColor(IndexedColors.RED.getIndex());
    CellStyle style1 = wb.createCellStyle();
    style1.setFont(font1);

    Font font2 = wb.createFont();
    font2.setFontHeightInPoints((short) 16);
    font2.setFontName("Arial");
    font2.setColor(IndexedColors.GREEN.getIndex());
    CellStyle style2 = wb.createCellStyle();
    style2.setFont(font2);

    Font font3 = wb.createFont();
    font3.setFontHeightInPoints((short) 18);
    font3.setFontName("Times New Roman");
    font3.setColor(IndexedColors.LAVENDER.getIndex());
    CellStyle style3 = wb.createCellStyle();
    style3.setFont(font3);

    Font font4 = wb.createFont();
    font4.setFontHeightInPoints((short) 18);
    font4.setFontName("Wingdings");
    font4.setColor(IndexedColors.GOLD.getIndex());
    CellStyle style4 = wb.createCellStyle();
    style4.setFont(font4);

    Font font5 = wb.createFont();
    font5.setFontName("Symbol");
    CellStyle style5 = wb.createCellStyle();
    style5.setFont(font5);

    Cell cell0 = sheet.createRow(0).createCell(1);
    cell0.setCellValue("Default");
    cell0.setCellStyle(style0);

    Cell cell1 = sheet.createRow(1).createCell(1);
    cell1.setCellValue("Courier");
    cell1.setCellStyle(style1);

    Cell cell2 = sheet.createRow(2).createCell(1);
    cell2.setCellValue("Arial");
    cell2.setCellStyle(style2);

    Cell cell3 = sheet.createRow(3).createCell(1);
    cell3.setCellValue("Times New Roman");
    cell3.setCellStyle(style3);

    Cell cell4 = sheet.createRow(4).createCell(1);
    cell4.setCellValue("Wingdings");
    cell4.setCellStyle(style4);

    Cell cell5 = sheet.createRow(5).createCell(1);
    cell5.setCellValue("Symbol");
    cell5.setCellStyle(style5);

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream(Utils.getPath("xssf-fonts.xlsx"));
    wb.write(fileOut);
    fileOut.close();
}

From source file:pl.softech.knf.ofe.opf.members.xls.export.XlsMembersWritter.java

License:Apache License

private void buildHeader(final List<Date> dates, final Sheet sheet, final int rowIdx, final int colIdx) {

    final Workbook wb = sheet.getWorkbook();
    final CreationHelper createHelper = wb.getCreationHelper();
    final CellStyle dateCellStyle = wb.createCellStyle();
    dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("mm-yyyy"));

    Row row = sheet.createRow(rowIdx);//from  w ww .  j a  va  2  s. c om

    Cell cell = row.createCell(colIdx);
    cell.setCellValue("Open Pension Fund");

    final CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    cellStyle.setFont(createHeaderFont(wb, (short) 12));
    cell.setCellStyle(cellStyle);

    cell = row.createCell(colIdx + 1);
    cell.setCellValue("Number of members");
    cell.setCellStyle(cellStyle);
    row = sheet.createRow(rowIdx + 1);
    sheet.addMergedRegion(new CellRangeAddress(// merge Open Pension Fund
            rowIdx, // first row (0-based)
            rowIdx + 1, // last row (0-based)
            colIdx, // first column (0-based)
            colIdx // last column (0-based)
    ));

    sheet.addMergedRegion(new CellRangeAddress(// merge Number of members
            rowIdx, // first row (0-based)
            rowIdx, // last row (0-based)
            colIdx + 1, // first column (0-based)
            colIdx + dates.size() // last column (0-based)
    ));

    int colIt = colIdx + 1;
    for (final Date date : dates) {
        cell = row.createCell(colIt++);
        cell.setCellValue(date);
        cell.setCellStyle(dateCellStyle);
    }

}