Xlsxwriter: add a formula with a different sheet to it

Question

I am trying to create an xlsx file with the xlsxwriter python plugin. In this XLSX, I have 2 sheets:

• Analysis : contains a table with information
• Stat . Contains some information and 2 formulas

These two formulas are:

=NBVAL(Analyse!C:C)-1 =NB.SI(Analyse!D:D;"To change") 

My problem is that when I open the generated file, I have an error. And the formulas do not work. If I edit the formula and just press Enter, it will work.

My code is:

 shInfo = self.__workbook.add_worksheet("Stat") shInfo.activate() information = self.__workbook.add_format({'bg_color': '#BFBFBF', 'font_name': 'Courier New'}) shInfo.write('G3','=NBVAL(Analyse!C:C)-1',information) shInfo.write('G5','=NB.SI(Analyse!D:D;"To change")',information) 

When I open the XML error report. I have it:

 <?xml version="1.0" encoding="UTF-8" standalone="true"?> <recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <logFileName>error056160_04.xml</logFileName> <summary>Des erreurs ont été détectées dans le fichier « L:\UNMS\InputBEB\Output\UNMSViewer\public_html\Data\XLSX\todo\A6S54300.xlsx »</summary> <removedRecords summary="Liste des enregistrements supprimés ci-dessous :"> <removedRecord>Enregistrements supprimés: Formule dans la partie /xl/worksheets/sheet2.xml</removedRecord> </removedRecords> </recoveryLog> 

Answer 1

The problem is probably related to the function names of the formula in French, but Excel expects it to be saved / written in English . At least in the files written by XlsxWriter.

Try this instead:

 shInfo.write('G3','=COUNTA(Analyse!C:C)-1',information) shInfo.write('G5','=COUNTIF(Analyse!D:D,"To change")',information) 

If you send me a small sample file saved using the French version of Excel, I will see if it is possible to set a flag in files written by XlsxWriter to indicate the language of formulas.

Refresh . The COUNTIF() formula should also use the US style comma operator instead ; . Update 2 . Based on the sample file provided by @gatchan, the file does not have a language identifier. The formula is translated into English and American style by comma, Excel when it is saved.

Answer 2

Instead of the write() method, you should use write_formula () :

 shInfo.write_formula('G3','=NBVAL(Analyse!C:C)-1',information) shInfo.write_formula('G5','=NB.SI(Analyse!D:D;"To change")',information)