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

  1.     Xlsx xlsx = new Xlsx();
  2.  
  3.     Workbook workbook = xlsx.getWorkbook();
  4.  
  5.     SsmlWorksheet sheet = workbook.addWorksheet();
  6.     SsmlSheetData sheetData = sheet.getSheetData();
  7.  
  8.     sheetData.addRow().addCell("Hello World!");
  9.  
  10.     File testFile = new File("testSimple.xlsx");
  11.     xlsx.write(testFile);
The resulting output looks like this:

The resulting xlsx file:
testSimple.xlsx

Example 2: Merging Cells

  1.     Xlsx xlsx = new Xlsx();
  2.  
  3.     Workbook workbook = xlsx.getWorkbook();
  4.  
  5.     SsmlWorksheet sheet = workbook.addWorksheet();
  6.     SsmlSheetData sheetData = sheet.getSheetData();
  7.  
  8.     sheetData.addRow().addCell("Hello Merged World!");
  9.  
  10.     sheet.mergeCells(new CellRange("A1:C2"));
  11.  
  12.     File testFile = new File("testCellMerge.xlsx");
  13.     xlsx.write(testFile);
The resulting output looks like this:

Xlsx: testCellMerge.xlsx

Example 3: Conditional formatting

  1.     Xlsx xlsx = new Xlsx();
  2.  
  3.     Workbook workbook = xlsx.getWorkbook();
  4.  
  5.  
  6.     SsmlDifferentialFormat level1Format = new SsmlDifferentialFormat(xlsx);
  7.     HTMLColor bgColor = HTMLColor.WHITE;
  8.     level1Format.getFont().setColor(bgColor.getContrastingColor());
  9.     level1Format.getFill().setBackgroundColor(bgColor);
  10.  
  11.     SsmlDifferentialFormat level2Format = new SsmlDifferentialFormat(xlsx);
  12.     bgColor = HTMLColor.valueOf("FF00FF00");
  13.     level2Format.getFont().setColor(bgColor.getContrastingColor());
  14.     level2Format.getFill().setBackgroundColor(bgColor);
  15.  
  16.     SsmlDifferentialFormat level3Format = new SsmlDifferentialFormat(xlsx);
  17.     bgColor = HTMLColor.valueOf("FF00A9FF");
  18.     level3Format.getFont().setColor(bgColor.getContrastingColor());
  19.     level3Format.getFill().setBackgroundColor(bgColor);
  20.  
  21.     SsmlDifferentialFormat level4Format = new SsmlDifferentialFormat(xlsx);
  22.     bgColor = HTMLColor.valueOf("FFAA00FF");
  23.     level4Format.getFont().setColor(bgColor.getContrastingColor());
  24.     level4Format.getFill().setBackgroundColor(bgColor);
  25.  
  26.     SsmlDifferentialFormat level5Format = new SsmlDifferentialFormat(xlsx);
  27.     bgColor = HTMLColor.valueOf("FFFF0000");
  28.     level5Format.getFont().setColor(bgColor.getContrastingColor());
  29.     level5Format.getFill().setBackgroundColor(bgColor);
  30.  
  31.     SsmlDifferentialFormat level6Format = new SsmlDifferentialFormat(xlsx);
  32.     bgColor = HTMLColor.valueOf("FFC00000");
  33.     level6Format.getFont().setColor(bgColor.getContrastingColor());
  34.     level6Format.getFill().setBackgroundColor(bgColor);
  35.  
  36.     SsmlWorksheet sheet = workbook.addWorksheet();
  37.     SsmlSheetData sheetData = sheet.getSheetData();
  38.  
  39.     // Generate some mock data
  40.     SsmlRow row = sheetData.addRow();
  41.     row.addCell();
  42.     row.addCell("A");
  43.     row.addCell("B");
  44.     row.addCell("C");
  45.  
  46.     row = sheetData.addRow();
  47.     row.addCell("A");
  48.     row.addCell(100);
  49.     row.addCell(75);
  50.     row.addCell(80);
  51.  
  52.     row = sheetData.addRow();
  53.     row.addCell("B");
  54.     row.addCell(40);
  55.     row.addCell(100);
  56.     row.addCell(60);
  57.  
  58.     row = sheetData.addRow();
  59.     row.addCell("C");
  60.     row.addCell(92);
  61.     row.addCell(83);
  62.     row.addCell(100);
  63.  
  64.     // Assign some conditional formatting to a range of cells
  65.     SsmlConditionalFormatting conditionalFormatting = sheet.addConditionalFormatting(new CellRange("B2:D4"));
  66.  
  67.     conditionalFormatting.addRule(SsmlCfRuleType.expression)
  68.           .setFormat(level1Format)
  69.           .setStopIfTrue(true)
  70.           .addFormula("ROW()=COLUMN()");
  71.  
  72.     conditionalFormatting.addRule(SsmlCfRuleType.cellIs)
  73.           .setFormat(level2Format)
  74.           .setStopIfTrue(true)
  75.           .setOperator(SsmlCfOperator.between)
  76.           .addFormula(90)
  77.           .addFormula(100);
  78.  
  79.     conditionalFormatting.addRule(SsmlCfRuleType.cellIs)
  80.           .setFormat(level3Format)
  81.           .setStopIfTrue(true)
  82.           .setOperator(SsmlCfOperator.between)
  83.           .addFormula(80)
  84.           .addFormula(90);
  85.  
  86.     conditionalFormatting.addRule(SsmlCfRuleType.cellIs)
  87.           .setFormat(level4Format)
  88.           .setStopIfTrue(true)
  89.           .setOperator(SsmlCfOperator.between)
  90.           .addFormula(70)
  91.           .addFormula(80);
  92.  
  93.     conditionalFormatting.addRule(SsmlCfRuleType.cellIs)
  94.           .setFormat(level5Format)
  95.           .setStopIfTrue(true)
  96.           .setOperator(SsmlCfOperator.between)
  97.           .addFormula(60)
  98.           .addFormula(70);
  99.  
  100.     conditionalFormatting.addRule(SsmlCfRuleType.cellIs)
  101.           .setFormat(level6Format)
  102.           .setStopIfTrue(true)
  103.           .setOperator(SsmlCfOperator.between)
  104.           .addFormula(0)
  105.           .addFormula(60);
  106.  
  107.  
  108.     File testFile = new File("testConditionalFormatting.xlsx");
  109.     xlsx.write(testFile);
This resulting output looks like this:

Xlsx: testConditionalFormatting.xlsx
Return to Main Page