search

qax-os / excelize

Go language library for reading and writing Microsoft Excel™ (XLAM / XLSM / XLSX / XLTM / XLTX) spreadsheets
https://xuri.me/excelize
BSD 3-Clause "New" or "Revised" License
18.19k stars 1.71k forks source link

SetCellFormula - corruption on MS/Libre Office, google works #471

Closed Belgian93 closed 5 years ago

Belgian93 commented 5 years ago

Description I'm using excelize to export data from a postgresql db to excel, making it available to clients through our Go API.

When downloading the excel file from our API and opening it in LibreOffice the cell with the (rather complex) formula will throw Error: 508 In MS Office, a prompt will ask user to repair the xlsx file even before opening, repairing will remove the formula in said cell. Opening the file in google spreadsheets works fine, when exporting from google spreadsheets and opening the file in MS/Libre Office will make the file behave as expected.

Tried analyzing both files (file1: from api; file2: exported from gogole spreadsheets) after unzipping, but I know too little about the file structure of xlsx to find what causes this issue. Upgraded to v2, maybe this was a bug in older version, but no luck.

Below an example of code that will produce this bug, I omitted all other code that is irrelevant for this issue. Notice the complexity of the formula (we want clients to be able to generate valid json from their excel). This formula should be correct, it works on all platforms when pasted directly into a cell.

f := xls.NewFile()
f.SetSheetName("Sheet1", "metrics")

jsonFormula := `CONCATENATE("{ ""ID"": ""someID"", "; """Name"": """; A2 ;""", ""Description"": """; B2 ;""", ""Datatype"": """; C2 ;""", ""MetricGroupID"": """; D2 ;""", ""CollectorID"": """; E2 ;""", ""Settings"": { "; IF(ISBLANK(I2); ""; CONCATENATE(" """; $I$1 ; """: """; I2 ;""", ")) ;" "; IF(ISBLANK(F2); ""; CONCATENATE(" """; $F$1 ; """: """; F2 ;""", ")) ;" "; IF(ISBLANK(G2); ""; CONCATENATE(" """; $G$1 ; """: "; G2 ;", ")) ;" "; IF(ISBLANK(H2); ""; CONCATENATE(" """; $H$1 ; """: "; H2 ;"")) ;"  } },")`
f.SetCellFormula("metrics", "J2", jsonFormula)

f.SaveAs("./exports/metrics.xlsx")

Steps to reproduce the issue:

  1. Generate empty xlsx
  2. SetCellFormula to given formula
  3. Open saved file in MS/Libre Office

Describe the results you received: File is corrupted for MS Office, and Error 508 (Pair missing) for Libre Office

Describe the results you expected: Working formula in cell, as it does work on google spreadsheets, and copying the formula directly in the cell does too.

Expected formula in cell:

=CONCATENATE("{ ""ID"": ""someID"", "; """Name"": """; A2 ;""", ""Description"": """; B2 ;""", ""Datatype"": """; C2 ;""", ""MetricGroupID"": """; D2 ;""", ""CollectorID"": """; E2 ;""", ""Settings"": { "; IF(ISBLANK(I2); ""; CONCATENATE(" """; $I$1 ; """: """; I2 ;""", ")) ;" "; IF(ISBLANK(F2); ""; CONCATENATE(" """; $F$1 ; """: """; F2 ;""", ")) ;" "; IF(ISBLANK(G2); ""; CONCATENATE(" """; $G$1 ; """: "; G2 ;", ")) ;" "; IF(ISBLANK(H2); ""; CONCATENATE(" """; $H$1 ; """: "; H2 ;"")) ;"  } },")

Attachements: excelizeGenerated.xlsx googleExported.xlsx

Output of go version:

go version go1.12.7 linux/amd64

Excelize version or commit ID:

module api

go 1.12

require (
    github.com/360EntSecGroup-Skylar/excelize/v2 v2.0.1
    ...

Environment details (OS, Microsoft Excel™ version, physical, etc.):

Operating System:


                          ./+o+-       alexander@alexander
                  yyyyy- -yyyyyy+      OS: Ubuntu 18.04 bionic
               ://+//////-yyyyyyo      Kernel: x86_64 Linux 4.15.0-55-generic
           .++ .:/++++++/-.+sss/`      Uptime: 3h 24m
         .:++o:  /++++++++/:--:/-      Packages: 2094
        o:+o+:++.`..```.-/oo+++++/     Shell: zsh 5.4.2
       .:+o:+o/.          `+sssoo+/    Resolution: 3520x1080
  .++/+:+oo+o:`             /sssooo.   DE: GNOME 
 /+++//+:`oo+o               /::--:.   WM: GNOME Shell
 \+/+o+++`o++o               ++////.   WM Theme: Adwaita
  .++.o+++oo+:`             /dddhhh.   GTK Theme: Ambiance [GTK2/3]
       .+.o+oo:.          `oddhhhh+    Icon Theme: ubuntu-mono-dark
        \+.++o+o``-````.:ohdhhhhh+     Font: Ubuntu 11
         `:o+++ `ohhhhhhhhyo++os:      CPU: Intel Core i7-3540M @ 4x 3.7GHz [63.5°C]
           .o:`.syhhhhhhh/.oo++o`      GPU: intel
               /osyyyyyyo++ooo+++/     RAM: 4337MiB / 7850MiB
                   ````` +oo+++o\:    
                          `oo++.

LibreOffice Calc version: 

Version: 6.0.7.3
Build ID: 1:6.0.7-0ubuntu0.18.04.8

MS Excel for Mac version:

Version 15.26 (160910)
Belgian93 commented 5 years ago

Furthermore I'd like to add that both in google spreadsheets and in Libre Office, the formulas in the cells are identical, however one results in Err 508 and the other is functional

That's why I think the answer lies somewhere in the xml files, either in the header or in the bulk of the xml. I noticed the google spreadsheet sheet1.xml has all quotes in the formula translated to '"', whereas the excelize generated file has literal quotes.

Belgian93 commented 5 years ago

Formula incorrect after all, semicolons instead of commas to distinguish strings from cell references. Usage of ";" or "," as list seperator is regional, check your settings.