Examples of generating OfficeOpenXML Excel (.xlsx) documents via com-hfg objects.

The OfficeOpenXML classes in the com-hfg library are still a work in progress but functional Excel .xlsx files can be created.

1. Hello World
2. Merging Cells
3. Conditional formatting

Example 1: Hello World

    Xlsx xlsx = new Xlsx();

    Workbook workbook = xlsx.getWorkbook();

    SsmlWorksheet sheet = workbook.addWorksheet();
    SsmlSheetData sheetData = sheet.getSheetData();

    sheetData.addRow().addCell("Hello World!");

    File testFile = new File("testSimple.xlsx");
    xlsx.write(testFile);

The resulting output looks like this:

The resulting xlsx file:

Example 2: Merging Cells

    Xlsx xlsx = new Xlsx();

    Workbook workbook = xlsx.getWorkbook();

    SsmlWorksheet sheet = workbook.addWorksheet();
    SsmlSheetData sheetData = sheet.getSheetData();

    sheetData.addRow().addCell("Hello Merged World!");

    sheet.mergeCells(new CellRange("A1:C2"));

    File testFile = new File("testCellMerge.xlsx");
    xlsx.write(testFile);

The resulting output looks like this:

Xlsx:

Example 3: Conditional formatting

    Xlsx xlsx = new Xlsx();

    Workbook workbook = xlsx.getWorkbook();


    SsmlDifferentialFormat level1Format = new SsmlDifferentialFormat(xlsx);
    HTMLColor bgColor = HTMLColor.WHITE;
    level1Format.getFont().setColor(bgColor.getContrastingColor());
    level1Format.getFill().setBackgroundColor(bgColor);

    SsmlDifferentialFormat level2Format = new SsmlDifferentialFormat(xlsx);
    bgColor = HTMLColor.valueOf("FF00FF00");
    level2Format.getFont().setColor(bgColor.getContrastingColor());
    level2Format.getFill().setBackgroundColor(bgColor);

    SsmlDifferentialFormat level3Format = new SsmlDifferentialFormat(xlsx);
    bgColor = HTMLColor.valueOf("FF00A9FF");
    level3Format.getFont().setColor(bgColor.getContrastingColor());
    level3Format.getFill().setBackgroundColor(bgColor);

    SsmlDifferentialFormat level4Format = new SsmlDifferentialFormat(xlsx);
    bgColor = HTMLColor.valueOf("FFAA00FF");
    level4Format.getFont().setColor(bgColor.getContrastingColor());
    level4Format.getFill().setBackgroundColor(bgColor);

    SsmlDifferentialFormat level5Format = new SsmlDifferentialFormat(xlsx);
    bgColor = HTMLColor.valueOf("FFFF0000");
    level5Format.getFont().setColor(bgColor.getContrastingColor());
    level5Format.getFill().setBackgroundColor(bgColor);

    SsmlDifferentialFormat level6Format = new SsmlDifferentialFormat(xlsx);
    bgColor = HTMLColor.valueOf("FFC00000");
    level6Format.getFont().setColor(bgColor.getContrastingColor());
    level6Format.getFill().setBackgroundColor(bgColor);

    SsmlWorksheet sheet = workbook.addWorksheet();
    SsmlSheetData sheetData = sheet.getSheetData();

    // Generate some mock data
    SsmlRow row = sheetData.addRow();
    row.addCell();
    row.addCell("A");
    row.addCell("B");
    row.addCell("C");

    row = sheetData.addRow();
    row.addCell("A");
    row.addCell(100);
    row.addCell(75);
    row.addCell(80);

    row = sheetData.addRow();
    row.addCell("B");
    row.addCell(40);
    row.addCell(100);
    row.addCell(60);

    row = sheetData.addRow();
    row.addCell("C");
    row.addCell(92);
    row.addCell(83);
    row.addCell(100);

    // Assign some conditional formatting to a range of cells
    SsmlConditionalFormatting conditionalFormatting = sheet.addConditionalFormatting(new CellRange("B2:D4"));

    conditionalFormatting.addRule(SsmlCfRuleType.expression)
          .setFormat(level1Format)
          .setStopIfTrue(true)
          .addFormula("ROW()=COLUMN()");

    conditionalFormatting.addRule(SsmlCfRuleType.cellIs)
          .setFormat(level2Format)
          .setStopIfTrue(true)
          .setOperator(SsmlCfOperator.between)
          .addFormula(90)
          .addFormula(100);

    conditionalFormatting.addRule(SsmlCfRuleType.cellIs)
          .setFormat(level3Format)
          .setStopIfTrue(true)
          .setOperator(SsmlCfOperator.between)
          .addFormula(80)
          .addFormula(90);

    conditionalFormatting.addRule(SsmlCfRuleType.cellIs)
          .setFormat(level4Format)
          .setStopIfTrue(true)
          .setOperator(SsmlCfOperator.between)
          .addFormula(70)
          .addFormula(80);

    conditionalFormatting.addRule(SsmlCfRuleType.cellIs)
          .setFormat(level5Format)
          .setStopIfTrue(true)
          .setOperator(SsmlCfOperator.between)
          .addFormula(60)
          .addFormula(70);

    conditionalFormatting.addRule(SsmlCfRuleType.cellIs)
          .setFormat(level6Format)
          .setStopIfTrue(true)
          .setOperator(SsmlCfOperator.between)
          .addFormula(0)
          .addFormula(60);


    File testFile = new File("testConditionalFormatting.xlsx");
    xlsx.write(testFile);

This resulting output looks like this:

Xlsx: