Excel Having issues with an ODS file created with SODS

Question

I'm creating an ODS spreadsheet using SODS (https://github.com/miachm/SODS). This is a single-sheet, non formatted file. LibreOffice is happy with it, as is Google Spreadsheets. Excel, on the other hand, claims it contains errors, and when it attempts to fix it, it damages the data.

How can I make Excel happy about this file*?

Code (This is a Play/Scala program, accidentsDatasetCols are column extractors that generate cell content):

val sheet = new Sheet("accidents")
    // add title row
    sheet.appendColumns(accidentsDatasetCols.size)
    sheet.appendRow()
    val titleRow = sheet.getRange(0,0,1,accidentsDatasetCols.size)
    accidentsDatasetCols.zipWithIndex.foreach( c => {
      titleRow.getCell(0,c._2).setValue(c._1.name)
    } )

    // add data rows
    for {
      workAccidents <- accidents.listAllAccidents()
    } yield {
      for ( acc <- workAccidents ) {
        sheet.appendRow()
        val row = sheet.getRange(sheet.getLastRow,0, 1, accidentsDatasetCols.size)
        accidentsDatasetCols.zipWithIndex.foreach( c => row.getCell(0,c._2).setValue(c._1(acc)) )
      }
      val sprd = new SpreadSheet()
      sprd.addSheet(sheet, 0)
      var bytes:Array[Byte]=null
      Using( new ByteArrayOutputStream() ){ bas =>
        sprd.save(bas)
        bas.flush()
        bytes = bas.toByteArray
      }
      Ok(bytes).as("application/vnd.oasis.opendocument.spreadsheet")
        .withHeaders("Content-Disposition"->"attachment; filename=\"work-accidents.ods\"")
    }

Thanks!

• Alternatively, how can I make MS fix Excel already?!

Answer 1

Author of the libray here!

At first glance, it seems Excel is expecting than the file defines default-style fields (default font family, default paragraph margin, etc...). This is not required by the standard and that's why it works perfectly in LibreOffice and Google Sheets.

This is quite odd but I am not suprised. It's not the first time Excel is causing problems by an awful implementation. For example, Excel declares tables of 1048573x16384 cells even though it's only using a few ones at the top. This is plain stupid and it doesn't make any sense: https://github.com/miachm/SODS/issues/12

Anyway, we can not change how Excel works. So I'll try to work around this new issue. It should be solved in the next version!

EDIT: The bug is solved now!