"could not write italic() format to file" error message

[Sergiy Radyakin]

While executing my code I have encountered an occasion when the following message was produced in the output with execution aborted:

There were two different lines of code that could have been executed at the time:
1.

putexcel B`i' = `"`allcomments'"' , italic

or
2.

putexcel B`i' = "`lev'", fpattern(solid, "238 236 225") font("Calibri", 14) bold italic

both of which output some well-formed text to the Excel file being staged. Macros:
* i is the counter of lines, and due to amount of output can get into thousands, but otherwise nothing special about it.
* lev is 2012 in this case.
* allcomments is in general some text, but should be empty for the specific loop iteration here.

The error only occurred once, and didn't reoccur when the same code was re-run (hence I couldn't debug it to see which line of code failed and for which values.)

After searching the internet I didn't find a single mentioning of the exact message "could not write italic() format to file" , but the closest relevant result was the message from Sarah Eliason here, which cited "could not write merge format to file" under some circumstances.

I am seeking any guidance (in Stata's documentation or in 3-rd party resources) on the conditions that may provoke this specific error, as well as hopefully any explanation as to why it may occur only some times, and not being reproducible.

Thank you, Sergiy

Version information: Stata 17.0(170130) on Windows.

[Kevin Crow (StataCorp)]

The error "could not write italic() format to file" is issued from the Mata xl() class. This error happens with .xlsx file I/O (usually antivirus related) or when you try and write too many Excel cell formats to the file in question.

[Sergiy Radyakin]

Dear Kevin,

thank you very much for your explanation. From the lack of reproducibility (even with identical inputs and code) I tend to believe that this may be antivirus-related issue. Still, since you've mentioned "too many Excel cell formats" - it would be good to know what specific limit does apply to cell formats? and is this a Stata's own limit or a limit inherited from the Excel file standard/specification?

Thank you, Sergiy

[Kevin Crow (StataCorp)]

An Excel workbook has a limit to the number of cell formats it can store/parse. I'm not sure what the exact limit is for each version of Excel, but using the putexcel option overwritefmt combined with using cell ranges with your cell formats helps with this problem.

[Sergiy Radyakin]

Dear Kevin Crow (StataCorp) ,

thank you very much for clarifying.

I find the limit value is mentioned at this page and is

Unique cell formats/cell styles

65,490

(Excel limits may be version dependent.)

I will experiment with the option overwritefmt you've recommended, which was not part of my code so far.

Thank you, Sergiy

[Sergiy Radyakin]

To update this thread,

I've managed to reliably reproduce the conditions, under which the error message was appearing, (which turned out dependent on the input files I was supplying for processing, resulting in varying amount of output generated and saved to Excel).

I have then added the overwritefmt option to all the putexcel statements in my code writing data to the Excel output file and the problem was gone.

One thing that escapes me is actually whether there is any way to monitor this value and how close to the actual limit I am? In particular, when I am appending data to an existing file, I may lack any intuition about the previously used quota towards this limit. Also, is there any penalty (file size, performance, etc) or other consequence of using this option? If not, should it not be the default then?

Thank you, Sergiy Radyakin