Microsoft Excel unable to read styled .ods generated files

[MinMatth-Magi]

Hi, junior developer here !

The Problem I have

Microsoft Excel seems to have issues reading .ods files generated with SODS.

When opening in LibreOffice, the data is correct, but in Excel, even though the columns widths are correctly formatted and the sheets have their names right, no data appears in the cells.

What I tried and what it resulted into

I digged for some answers online, and found this answer of yours on Stack Overflow. From what I understood from it, there was a bug that has been fixed a while ago already. But you also mentioned the following...

"it seems Excel is expecting than the file defines default-style fields"

I didn't find any way to set a default style on the whole spreadsheet, but did apply a style on each column of each sheet. It worked the same for the vertical alignment, but applying a fontSize or a dataStyle for example resulted in the Excel being unable to open the spreadsheet (still works fine with LibreOffice).

More details

• I am using the current latest version of SODS (1.6.4)
• To test if Microsoft Excel can open the file, I try on both the online version and desktop application
• The SODS library is used on a Kotlin api that I call, and the spreadhseet is streamed as an answer.

Don't hesitate to ask for more details I could provide, or if I need to try something out.

[miachm]

Hi!

The easiest way is having a minimal-reproducible-example and debugging the output.

You can pass me over a block of code which produces the file with the issue. If that's not possible, at least pass me over the file generated by SODS.

About the answer in Stack Overflow. It's not relevant. That issue is solved in the latest version and it's related to the internal XML structure, nothing from your side.

Greetings,
Miguel

[MinMatth-Magi]

Hi! Thanks for your answer.

Here are the files required to reproduce the issue :

My "not library-specific" data structure for tables and what could be in it:

class Sheet(
    val name: String,
    val rows: List<Row>
)
typealias Row = List<Cell>
typealias Cell = String

ExportController.kt

@Controller
class ExportController(
    val dataService: DataService,
    val exportNodeSearchDtoMapper: ExportNodeSearchDtoMapper,
    val odsSerializer: OdsSerializer,
    val nodeMapper: NodeMapper
) : ExportApi {

    override fun nodeSearchExport(
        accept: FileFormat,
        exportNodeSearchDto: ExportNodeSearchDto,
        locale: String?
    ): ResponseEntity<Resource> {
        if (accept === FileFormat.APPLICATION_SLASH_VND_PERIOD_OASIS_PERIOD_OPENDOCUMENT_PERIOD_SPREADSHEET) {
            val odsContentType = "application/vnd.oasis.opendocument.spreadsheet"

            // Fomat data to match the 
            val exportNodeSearchBean = exportNodeSearchDtoMapper.toExportNodeSearchBean(exportNodeSearchDto)
            val nodeSearchData = dataService.getNodeSearchData(exportNodeSearchBean, locale)
            val tableSheets = nodeMapper.nodeSearchDataToTableSheets(nodeSearchData, locale)

            // Call odsSerializer.createOds and stream the answer as the API response
            return ResponseEntity
                    .ok()
                    .header(HttpHeaders.CONTENT_TYPE, odsContentType)
                    .header(HttpHeaders.CONTENT_DISPOSITION, "inline; filename=\"${"export.ods"}\"")
                    .body(ByteArrayResource(odsSerializer.createOds(tableSheets).toByteArray()))
        } else {
            throw NotImplementedException("The requested format has not been implemented yet")
        }
    }
}

OdsSerializer.kt

@Component
class OdsSerializer {

    companion object {
        private val ROW_START = 0
        private val COLUMN_START = 0
        private val COLUMN_WIDTH = 45.0 // Equivalent to 4.5cm
    }

    fun createOds(sheets: Set<com.export.service.beans.data.Sheet>): ByteArrayOutputStream {
        ByteArrayOutputStream().use { outputStream ->
            try {
                val spreadsheet = formatOdsFile(sheets)
                spreadsheet.save(outputStream)
                return outputStream
            } catch (e: IOException) {
                e.printStackTrace()
            }
            return outputStream
        }
    }

    private fun formatOdsFile(sheets: Set<com.export.service.beans.data.Sheet>): SpreadSheet
    {
        if (sheets.isEmpty()) throw Exception("No sheet to format.")

        val defaultStyle = Style()

        // The following style has no impact on the file being openable on Microsoft Excel
        defaultStyle.verticalTextAligment = Style.VERTICAL_TEXT_ALIGMENT.Middle

        /* While any of the following make the file impossible to open on Microsoft Excel
        //defaultStyle.isWrap = true
        //defaultStyle.fontColor = Color(0,0,0)
        //defaultStyle.fontSize = 10
        //defaultStyle.dataStyle = "@"
        */

        return sheets.fold(SpreadSheet()) { acc, tableSheet ->
            val nRows = tableSheet.rows.size
            val nCols = tableSheet.rows.maxOfOrNull { it.size } ?: 0
            if (nRows == 0 || nCols == 0)
                //throw Exception("No rows to format for ${tableSheet.name} sheet.")

            val sheet = Sheet(
                tableSheet.name,
                ROW_START + nRows,
                COLUMN_START + nCols
            )

            var rowIndex = ROW_START
            tableSheet.rows.forEach { row ->
                sheet
                    .getRange(rowIndex, COLUMN_START, 1, nCols)
                    .setValues(*row.toTypedArray())
                rowIndex++
            }

            for (i in COLUMN_START until COLUMN_START+nCols)
                sheet.setDefaultColumnCellStyle(i, defaultStyle)
            sheet.getRange(ROW_START, COLUMN_START, 1, nCols).setFontBold(true)
            sheet.setColumnWidths(COLUMN_START, nCols, COLUMN_WIDTH)

            acc.appendSheet(sheet)
            return@fold acc
        }
    }

}

And here's a file I generated like that :
testExport.ods

Once opened in LibreOffice it looks like this :

And in Microsoft Excel, like this (formatted columns, names sheets, but no data in cells):

I also generated a file with defaultStyle.dataStyle = "@", which cannot be open on Microsoft Excel :
5802392f-3b46-4be3-b238-f617433551e7.ods

Thanks for your help on this topic,
Matthieu

[miachm]

Sorry, I don't have access to a Windows machine. So this is going to be tricky to debug. I can confirm it's working fine in LibreOffice and Google Sheets.

I want to discard options. Have you tried to remove all the symbols in your file? (&, ->...). That includes parenthesis in the sheet names.

[MinMatth-Magi]

You might be onto something! I just tried a simple generation based on your SaveOds.java example, and there was no issue once opened in Excel. I will try more tests to really identify the issue and follow-up with my results as soon as my schedule allows it.

If it really is caused by some symbols/special characters, are you thinking of a fix ? I think even an error, a warning, or still writing the cells with no problematic characters in it could be a better behaviour than the actual one.

[miachm]

Sure, I am trying to maintain compatibility with Excel. Even if it's a pain in the ass :)

[MinMatth-Magi]

Hey again, I tried many new cases, and sadly, there might be more to it.

With the following code, there was no issue

  val sheet = Sheet("A", 3, 3)
  sheet.dataRange.setValues(1, 2, 3, 4, 5, 6, 7, 8, 9)

witnessTest.ods

note: The following tests have been realized with only this part of the code being edited

But I was surprised to see that making the values strings made the file impossible to open in Excel:

  val sheet = Sheet("A", 3, 3)
  sheet.dataRange.setValues("1", "2", "3", "4", "5", "6", "7", "8", "9")

stringTest.ods

Same for a sheet name with a different name

  val sheet = Sheet("AB", 3, 3)
  sheet.dataRange.setValues(1, 2, 3, 4, 5, 6, 7, 8, 9)

ABtest.ods

To get closer to what I did, I also tried with data spreading, which worked fine even in Excel

val sheet = Sheet("A", 3, 3)
val data = arrayOf(1, 2, 3, 4, 5, 6, 7, 8, 9)
sheet.dataRange.setValues(*data)

dataSpreadingTest.ods

This gets us further from the initial issue that was files that could be opened, correct sheet names, but with empty cells.
I can't understand how changing the sheet name to "AB" leads to the file being impossible to read by Excel.

Sorry for adding to "the pain in the ass"... 😆 Tell me if you need something more to figure out the issue.

[miachm]

@MinMatth-Magi It seems like a regresion bug from:
#65

So confusing.

I am generating the new version 1.6.5. Tell me if that solves the problem.

[MinMatth-Magi]

Sadly, it doesn't...

I tried with simple spreadsheets but only the exact saveOds example sheet seems to be working and can be opened in Excel.

        val sheet = Sheet("A", 3, 3)
        val data = arrayOf(1, 2, 3, 4, 5, 6, 7, 8, 9)
        sheet.dataRange.setValues(*data)
        sheet.getRange(2,2).setFontUnderline(true)
        sheet.getRange(0,0,2,2).setFontBold(true)

If I change the sheet name to "AB", or comment any of the styling lines, it will result in the following :

So the issue seems to not only be the empty cells. Most cases seem to lead to a file that cannot be opened at all.
All of the files I tried can be opened in LibreOffice though.

[MinMatth-Magi]

I did some more digging, the generated spreadsheets can be opened when saved directly in a file, instead of the output stream !

I will look a bit more into it and get back to you (maybe not today).

[miachm]

That doesn't makes sense. There is something else we are not considering.

[MinMatth-Magi]

So, with the following :

   spreadsheet.save(File("Out.ods"));
   spreadsheet.save(outputStream)

Out.ods can be opened in LibreOffice, and in Excel (some '0' appear in some cells in Excel while they are empty in LibreOffice)
The file I get from the outputStream can be opened in LibreOffice, but not in Excel (or when I was able to, it was with the empty cells)

Is there a difference in the way you write the spreadsheet in a file and in an outputStream ?

[miachm]

Which kind of OutputStream are you using?

[MinMatth-Magi]

OdsSerializer.kt

   fun createOds(sheets: Set<com.export.service.beans.data.Sheet>): ByteArrayOutputStream {
       ByteArrayOutputStream().use { outputStream ->
           try {
              val spreadsheet = formatOdsFile(sheets)
               spreadsheet.save(outputStream)
               return outputStream
           } catch (e: IOException) {
               e.printStackTrace()
           }
           return outputStream
       }
   }

I am using a ByteArrayOutputStream as it is what I previously had to use for some .pdf files generation. I currently am looking into the differences between those two OuputStream, and if I can use FileOutputStream instead.

[miachm]

Maybe is a flush problem?

[miachm]

I don't have Excel in Linux. Could you:

• Clone the repo.
• Add a flush() call before the line 45 in OdsWritter.java
• Test the code. For example, edit one of the examples of the repo with a ByteOutputStream()

[MinMatth-Magi]

Hey ! It works ! 😃

I added the following method in the Compressor.java file:

    public void flush() throws IOException {
        out.flush();
    }

And on the line 45 of OdsWritter.java, the call to this flush method

        out.flush();

I then built and published the project in my local maven to test it out, and it worked very well ! 😉 I had to use the -DskipTests flag when doing the install though.

Do you want me to open a pull request with the changes, or do you want to make sure there is no issue with the tests first ?

[miachm]

Nice!

Yes, you could open a PR so I can merge into SODS :)