SXSSFWorkbook generated xlsx file cannot be import by PowerBI

[msmith-work]

NPOI Version

Current

File Type

• XLSX

Reproduce Steps

Using streaming SXSSFWorkbook, generate a simple spreadsheet output

Issue Description

The generated xlsx file contains a stray UTF8 BOM (ZWNBSP) character in xl\worksheets\sheet1.xml between <sheetData><row r="1">.

The file can be opened without issue in Excel, however importing this file into PowerBI results in the error message "'Text' is an invalid XmlNodeType, Line 1, position x"

On investigation, it appears the temp file created for streaming is done so using a UTF8 encoding (SheetDataWriter.cs constructor), this then forces the temp file to contain the UTF8 BOM character, which then appears to be inserted into the final spreadsheet just after the opening <sheetData> tag

[tonyqus]

Can you upload the generated xlsx file? And How do you import this xlsx into PowerBI? Please let me know the reproduction steps.

[msmith-work]

Here is some basic example code and the generated spreadsheet (it affects all spreadsheets)

public Stream BuildExampleReport()
{
    using var workbook = new SXSSFWorkbook();
    var worksheet = workbook.CreateSheet("Sheet 1");
    for (var rowNum = 0; rowNum < 3; rowNum++)
    {
        var row = worksheet.CreateRow(rowNum);
        for (var colNum = 0; colNum < 3; colNum++)
        {
            var cell = row.CreateCell(colNum);
            cell.SetCellValue("Example");
        }
    }
    var memory = new MemoryStream();
    workbook.Write(memory, true);
    memory.Position = 0;
    return memory;
}

example.xlsx

The stray UTF8 BOM character is within the xl\worksheets\sheet1.xml file just after the opening <sheetData> tag.

As for importing into PowerBI, this is a process that our customers do with the generated spreadsheets and is not something we do ourselves; it has now been reported by multiple customers, each customer with their own use cases and processes.

We were previously using the non-streaming XSSF functions to generate the files, and these were generated without issue, it is only since switching to the streaming SXSSF functions that the issue has started.

[scousesheriff]

Hi.

This problem is not specific to PowerBI, it is creating a malformed document, it's just that Excel must work around it when opening the files. It seems to be a simple case of needing to remove this override to the encoding in the SheetDataWriter:

The vast majority of StreamWriters in the solution don't use the override, and rely on the the default encoding, which is UTF8NoBOM:

[transistor1]

This problem is not specific to PowerBI, it is creating a malformed document, it's just that Excel must work around it when opening the files. It seems to be a simple case of needing to remove this override to the encoding in the SheetDataWriter:

@tonyqus can you please make the simple change proposed by @scousesheriff and @msmith-work? The extra BOM also causes the same XmlNodeType error referenced in this issue when a user queries a document with Excel PowerQuery.

My kludge is to use reflection to set the sheet._writer._outputWriter.Encoding.emitUTF8Identifier value to false; because there seems to be no way to change the encoder directly. It would be great to get rid of this hack.

It does not seem like PRs are being reviewed, but I'll create one if you think it would help.