2.14.0/3.0.0 Column and row format changes when there are too many .xlsx columns. Kotlin.

[Onekun]

Good day. I ran into a problem generating templates. My .xlsx template has a number of columns up to GG. jx:area(lastCell="GG56") The width of each column is 0.42. The height of the lines varies, but on average from 9.75 to 12.75. Moreover, if in one .xlsx sheet1 lastCell="GG56" and sheet2 lastCell="R26" - the result on sheet2 is correct. And the width remains normal.

I managed to fix the width problem using excelSheet1.setColumnWidth(i, width256). However, there remains a problem with the height; it is set everywhere - 12.75, and is not taken from the template.

I tried it on versions 2.14.0 and 3.0.0, I attach examples of each code and files.

I tried many different articles and methods and believe there is a solution without manually tampering with the columns, but unfortunately I haven't found it yet.

Kotlin.

class XlsxGenerator : ReportGenerator {

override val templateFormat = ReportDataFormat.XLSX

private val log = LoggerFactory.getLogger(javaClass)

@PostConstruct
fun init() {
    XlsCommentAreaBuilder.addCommandMapping("imagex", ImagexCommand::class.java)
    XlsCommentAreaBuilder.addCommandMapping("hyperlink", HyperlinkCommand::class.java)
    XlsCommentAreaBuilder.addCommandMapping("autoSize", AutoRowHeightCommand::class.java)
}

@JsonIgnoreProperties(ignoreUnknown = true)
private class Params(
    val deleteSheet: String? = null
)

override fun generateWithStreaming(
    reportName: String,
    template: InputStream,
    data: Map<String, Any?>,
    params: Map<String, Any?>,
    report: OutputStream
) {
    log.trace("generate with Streaming: enter")

    @Suppress("NAME_SHADOWING")
    val params = jacksonObjectMapper().convertValue(params, Params::class.java)

    val context = PoiContext(data)

    val workbook = WorkbookFactory.create(template)
    workbook.forceFormulaRecalculation = true

    fixColumnWidth(reportName, workbook)

    val transformer = PoiTransformer.createSxssfTransformer(workbook, -1, false, false)
    transformer.outputStream = report

    val xlsAreas = XlsCommentAreaBuilder(transformer, false).build()

    val sheetNames = LinkedHashMap<String, String>()

    log.trace("generate: substituting data")
    xlsAreas.forEachIndexed { index, xlsArea ->
        if (params.deleteSheet == null) {
            val tempSheetName = "${xlsArea.startCellRef.sheetName}_tmp-${index}"
            sheetNames.put(xlsArea.startCellRef.sheetName, tempSheetName)
            val cellRef = CellRef(tempSheetName, xlsArea.startCellRef.row, xlsArea.startCellRef.col)
            xlsArea.applyAt(cellRef, context)
        } else {
            val cellRef = CellRef(xlsArea.startCellRef.cellName)
            xlsArea.applyAt(cellRef, context)
        }
    }

    if (sheetNames.isNotEmpty()) {
        // delete template sheet
        sheetNames.forEach { (originalSheetName, tempSheetName) ->
            transformer.deleteSheet(originalSheetName)

            val sheet = workbook.getSheet(tempSheetName)

            workbook.setSheetName(workbook.getSheetIndex(sheet), originalSheetName)
        }
    }
    log.trace("generate: writing stream")
    transformer.writeButNotCloseStream()
    transformer.dispose()

    log.trace("generate: return")
}

fun fixColumnWidth(reportName: String, workbook: Workbook) {
    if ("truck4p" == reportName || "4ptest" == reportName) {
        val excelSheet1 = workbook.getSheetAt(0);
        val excelSheet2 = workbook.getSheetAt(1);

        val widthExcel = 0.05f
        val heightExcel = 0.05f

        val width256 =
            floor(((widthExcel * Units.DEFAULT_CHARACTER_WIDTH + 5) / Units.DEFAULT_CHARACTER_WIDTH * 256).toDouble())
                .toInt()

        for (i in 0..200) {
            excelSheet1.setColumnWidth(i, width256)//186
            excelSheet2.setColumnWidth(i, width256)//184
        }
    }

}

//TODO v 3.0.0
/*override fun generateWithStreaming(
    template: InputStream,
    data: Map<String, Any?>,
    params: Map<String, Any?>,
    report: OutputStream
) {
    log.trace("generate with Streaming: enter")

    val test = JxlsTemplateFillerBuilder.newInstance()
        .withTransformerFactory(PoiTransformerFactory())
        .withStreaming(JxlsStreaming.STREAMING_ON)
        .withLogger(PoiExceptionThrower())
        .withTemplate(template)
        .withKeepTemplateSheet(KeepTemplateSheet.KEEP)
        .buildAndFill(data)

    report.write(test)

    log.trace("generate: return")
}*/

Template template_truck4p.xlsx

Result without manually width fix. result from template without manually width fix.xlsx

Result with manually width fix. result manually width fix.xlsx

[SoltauFintel]

a) Does it really work with less columns? (<50)

b) Does it work without streaming?

c) withKeepTemplateSheet(KeepTemplateSheet.KEEP) is a multi-sheet option. Do you really use dynamically created sheets? (Does it work without multi-sheet?)

d) Is it a bug since 2.14.0? Do you know if it works with an older JXLS version?

e) You could try to test it with branch master. Maybe #242 solves the problem??

f) Is Excel the right solution for such a formular? (maybe better JasperReports or fill-into-PDF?)

[Onekun]

a) Yes, see attach file. Although the row height does not match the template, unfortunately.

Template bus6.xlsx Result bus6 test trim column.xlsx

Template template_esm2.xlsx Result esm2 ok.xlsx

b) To be honest, I even forgot whether I checked it or not. I'll check again.

c) Just used it to try to solve the problem on v3.0.0, it didn't help.

d) Initially I tried it on 2.10, because they had previously used it on another project, but there were no such cumbersome templates.

e) I'll definitely try it and report back afterwards.

f) It might be worth thinking about it, but initially all templates are in .xls or .xlsx. It's probably worth looking at JasperReports as well. However, I have no experience working with it. There is also a chance that users will need to manually complete the result, and it is unlikely that users will have Adobe Acrobat or internet access for the PDF.