exportExcel

Johannes Thorn Jody Winter Schrotti Jody Winter Ralf D. Müller Ralf D. Müller Dr. Stefan Pfeiffer

2 minutes to read

About This Task

Sometimes you need to include tabular data in your documentation. Most likely, this data will be stored as a MS Excel spreadsheet, or you may like to use Excel to create and edit it. Either way, this task lets you export an Excel spreadsheet and include it directly in your docs. It searches for .xlsx files and exports each contained worksheet as .csv and as .adoc. Note that formulas contained in your spreadsheet are evaluated and exported statically.

The generated files are written to src/excel/[filename]/[worksheet].(adoc|cvs). The src folder is used instead of the build folder because a better history of worksheet changes is captured.

The files can be included either as AsciiDoc:

include::excel/Sample.xlsx/Numerical.adoc[]

…​or as a CSV file:

[options="header",format="csv"]
|===
include::excel/Sample.xlsx/Numerical.csv[]
|===

The AsciiDoc version gives you a bit more control because the following are preserved:

  • Horizontal and vertical alignment.

  • col-span and row-span.

  • Line breaks.

  • Column width relative to other columns.

  • Background colors.

Further Reading and Resources

See asciidoctorj-office-extension to learn another way to use Excel spreadsheets in your docs.

Source

Show source code of scripts/exportExcel.gradle or go directly to GitHub · docToolchain/scripts/exportExcel.gradle.
scripts/exportExcel.gradle
task exportExcel(
        description: 'exports all excelsheets to csv and AsciiDoc',
        group: 'docToolchain'
) {
    doFirst {
        File sourceDir = file(srcDir)

        def tree = fileTree(srcDir).include('**/*.xlsx').exclude('**/~*')

        def exportFileDir = new File(sourceDir, 'excel')

        //make sure path for notes exists
        exportFileDir.deleteDir()
        //create a readme to clarify things
        def readme = """This folder contains exported workbooks from Excel.

Please note that these are generated files but reside in the `src`-folder in order to be versioned.

This is to make sure that they can be used from environments other than windows.

# Warning!

**The contents of this folder will be overwritten with each re-export!**

use `gradle exportExcel` to re-export files
"""
        exportFileDir.mkdirs()
        new File(exportFileDir, '/readme.ad').write(readme)
    }
    doLast {
        File sourceDir = file(srcDir)
        def exportFileDir = new File(sourceDir, 'excel')
        def tree = fileTree(srcDir).include('**/*.xlsx').exclude('**/~*')

        def nl = System.getProperty("line.separator")

        def export = { sheet, evaluator, targetFileName ->
            def targetFileCSV = new File(targetFileName + '.csv')
            def targetFileAD = new File(targetFileName + '.adoc')
            def df = new org.apache.poi.ss.usermodel.DataFormatter();
            def regions = []
            sheet.numMergedRegions.times {
                regions << sheet.getMergedRegion(it)
            }
            logger.debug "sheet contains ${regions.size()} regions"
            def color = ''
            def resetColor = false
            def numCols = 0
            def headerCreated = false
            def emptyRows = 0
            for (int rowNum=0; rowNum<=sheet.lastRowNum; rowNum++) {
                def row = sheet.getRow(rowNum)
                if (row && !headerCreated) {
                    headerCreated = true
                    // create AsciiDoc table header
                    def width = []
                    numCols = row.lastCellNum
                    numCols.times { columnIndex ->
                        width << sheet.getColumnWidth((int) columnIndex)
                    }
                    //lets make those numbers nicer:
                    width = width.collect { Math.round(100 * it / width.sum()) }
                    targetFileAD.append('[options="header",cols="' + width.join(',') + '"]' + nl)
                    targetFileAD.append('|===' + nl)
                }
                def data = []
                def style = []
                def colors = []
                // For each row, iterate through each columns
                if (row && (row?.lastCellNum!=-1)) {
                    numCols.times { columnIndex ->
                        def cell = row.getCell(columnIndex)
                        if (cell) {
                            def cellValue = df.formatCellValue(cell, evaluator)
                            if (cellValue.startsWith('*') && cellValue.endsWith('\u20AC')) {
                                // Remove special characters at currency
                                cellValue = cellValue.substring(1).trim();
                            }
                            def cellStyle = ''
                            def region = regions.find { it.isInRange(cell.rowIndex, cell.columnIndex) }
                            def skipCell = false
                            if (region) {
                                //check if we are in the upper left corner of the region
                                if (region.firstRow == cell.rowIndex && region.firstColumn == cell.columnIndex) {
                                    def colspan = 1 + region.lastRow - region.firstRow
                                    def rowspan = 1 + region.lastColumn - region.firstColumn
                                    if (rowspan > 1) {
                                        cellStyle += "${rowspan}"
                                    }
                                    if (colspan > 1) {
                                        cellStyle += ".${colspan}"
                                    }
                                    cellStyle += "+"
                                } else {
                                    skipCell = true
                                }
                            }
                            if (!skipCell) {
                                switch (cell.cellStyle.getCellAlignment().getHorizontal().toString()) {
                                    case 'RIGHT':
                                        cellStyle += '>'
                                        break
                                    case 'CENTER':
                                        cellStyle += '^'
                                        break
                                }
                                switch (cell.cellStyle.getCellAlignment().getVertical().toString()) {
                                    case 'BOTTOM':
                                        cellStyle += '.>'
                                        break
                                    case 'CENTER':
                                        cellStyle += '.^'
                                        break
                                }
                                color = cell.cellStyle.fillForegroundXSSFColor?.RGB?.encodeHex()
                                color = color != null ? nl + "{set:cellbgcolor:#${color}}" : ''
                                data << cellValue
                                if (color == '' && resetColor) {
                                    colors << nl + "{set:cellbgcolor!}"
                                    resetColor = false
                                } else {
                                    colors << color
                                }
                                if (color != '') {
                                    resetColor = true
                                }
                                style << cellStyle
                            } else {
                                data << ""
                                colors << ""
                                style << "skip"
                            }
                        } else {
                            data << ""
                            colors << ""
                            style << ""
                        }

                    }
                    emptyRows = 0
                } else {
                    if (emptyRows<3) {
                        //insert empty row
                        numCols.times {
                            data << ""
                            colors << ""
                            style << ""
                        }
                        emptyRows++
                    } else {
                        break
                    }
                }

                targetFileCSV.append(data
                        .collect {
                    "\"${it.replaceAll('"', '""')}\""
                }
                .join(',') + nl, 'UTF-8')

                // fix #1192 https://github.com/docToolchain/docToolchain/issues/1192
                // remove unnecessary spans which break Asciidoctor rendering
                def prev = ''
                def removed = []
                def useRemoved = true
                style.eachWithIndex { s, i ->
                    if (s!="skip") {
                        if (s.contains('+')) {
                            def span = s.split('[+]')[0].split('[.]')
                            def current = ""
                            if (span.size()>1) {
                                current = span[1]
                            }
                            if (span[0] != '') {
                                removed << span[0] + '+' + s.split('[+]')[1]
                            } else {
                                removed << s.split('[+]')[1]
                            }
                            if (i > 0) {
                                if (current != prev) {
                                    useRemoved = false
                                }
                            }
                            prev = current
                        } else {
                            removed << s
                            useRemoved = false
                        }
                    } else {
                        removed << "skip"
                    }
                }
                if (useRemoved) { style = removed }
                // fix #1192 https://github.com/docToolchain/docToolchain/issues/1192

                targetFileAD.append(data
                        .withIndex()
                        .collect { value, index ->
                    if (style[index] == "skip") {
                        ""
                    } else {
                        style[index] + "| ${value.replaceAll('[|]', '{vbar}').replaceAll("\n", ' +$0') + colors[index]}"
                    }
                }
                .join(nl) + nl * 2, 'UTF-8')
            }
            targetFileAD.append('|===' + nl)
            // rewrite file to remove consecutive nl
            targetFileAD.write(targetFileAD.text.replaceAll("(?m)(\\r?\\n){2,}", nl+nl))
        }


        tree.each { File excel ->
            println "file: " + excel
            def excelDir = new File(exportFileDir, excel.getName())
            excelDir.mkdirs()
            InputStream inp
            inp = new FileInputStream(excel)
            def wb = org.apache.poi.ss.usermodel.WorkbookFactory.create(inp);
            def evaluator = wb.getCreationHelper().createFormulaEvaluator();
            for (int wbi = 0; wbi < wb.getNumberOfSheets(); wbi++) {
                def sheetName = wb.getSheetAt(wbi).getSheetName()
                println " -- sheet: " + sheetName
                def targetFile = new File(excelDir, sheetName)
                export(wb.getSheetAt(wbi), evaluator, targetFile.getAbsolutePath())
            }
            inp.close();
        }
    }
}