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();
        }
    }
}