Asciidoctor and excel sheets

Today I got an interesting problem with asciidoctor: how to handle dynamic tables. Let me explain my issue: I have a table which is a kind of abacus, in short, the last column is computed from the 6 previous ones.

Here is a sample:

|===
|A|B|SUM
|1|2|3
|3|4|7
|===

For this table, it is very tempting to do:

|===
|A|B|SUM
|1|2|=A+B
|3|4|=A+B
|===

But with asciidoctor this is not possible out of the box.

However, I'm sure you already thought about using Excel or OpenOffice Calc to do the CSV file, export it and include it in the table. This works but you need to export it in your build or manually to ensure your document reflect the accurate value.

To solve that you can write a simple extension. I did it in Java but you can do it in plain ruby as easily.

The first step is to grab an excel library able to read the excel file, I picked Apache POI:

<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>4.1.2</version>
</dependency>

Then, you just write an asciidoctor block macro reading the file and converting it to a plain CSV (or DSV) table:

@Name("excel") // <1>
@Contexts(Contexts.OPEN)
@ContentModel(ContentModel.COMPOUND)
public class ExcelTableMacro extends BlockProcessor {
    @Override
    public Object process(final StructuralNode parent, final Reader target, final Map<String, Object> attributes) {
        try (final XSSFWorkbook wb = new XSSFWorkbook(new File(target.readLine().replace("{partialsdir}", String.valueOf(parent.getDocument().getAttribute("partialsdir"))))) ){ // <2>
            // <3>
            final DataFormatter formatter = new DataFormatter();
            final FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
            // <4>
            final String sheetName = String.class.cast(attributes.get("sheet"));
            final Sheet sheet = sheetName == null ? wb.iterator().next() : wb.getSheet(sheetName);
            // <5>
            final String tableContent = StreamSupport.stream(sheet.spliterator(), false)
                    .map(row -> StreamSupport.stream(row.spliterator(), false)
                            .map(cell -> formatter.formatCellValue(cell, evaluator))
                            .collect(joining(";")))
                    .collect(joining("\n"));
            // <6>
            parseContent(parent, asList(("" +
                    "[opts=header,format=dsv,separator=;]\n" +
                    "|===\n" +
                    tableContent + '\n' +
                    "|===\n" +
                    "\n").split("\n")));
            return null;
        } catch (final IOException | InvalidFormatException e) {
            throw new IllegalStateException(e);
        }
    }
}

1. We name our block excel - see the next snippet,
2. We open the excel file reading its location in the body of our block - see next snippet. Note we handle partialsdir option to be able to use relative paths,
3. We reate a formatter and evalutaor - from POI - which enables us to evaluate formula and render them already computed,
4. We extract from the block attributes the name of the sheet to render (optional, the first one is used if not set),
5. We iterate over each row and each cell of each row to render the value thanks the formatter and evaluator and we format it as a DSV (separator = ;),
6. Finally we create a plain asciidoctor table and call parseContent to replace our original block with this content.

To make this extension available we just have to call on Asciidoctor instance the register method:

registry.block(new ExcelTableMacro());

Now we are ready to import our excel as a table with such snippet:

= Sample

Here are the data:

[excel]
--
{partialsdir}/data/demo.xslx
--

Better than maintaining multiple files no?