xlsxwriter: add formula with other sheet in it

问题:

I try to create a XLSX file with xlsxwriter python plugin. In this XLSX, I have 2 sheets:

• Analyse: Contain a table with informations
• Stat: Contain some informations and 2 formulas

This 2 formulas are:

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

My problem is when I open the generated file, I have a error. And the formulas don't work. If I edit the formula and just press Enter, it work.

My code:

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 this:

<?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>

回答1:

The issue is probably that the formula function names are in French but Excel expects it to be stored/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 a French version of Excel I'll have a look and see if it is possible to set a flag in the files written by XlsxWriter to indicate the language of the formulas.

Update: The COUNTIF() formula also needs to use the US style comma operator instead of ;. Update 2: Based on the sample file provided by @gatchan there is no language identifier in the file. The formula is translated to English and US style comma operator, by Excel, when it is saved .

回答2:

Instead of 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)