After running the code below, the text in Excel is not displaying as bold or properly centered (although this formatting is indicated on the ribbon). hcenter and bold can be swapped and either can be removed from the last line and the problem persists. In Excel, removing the text wrap causes the bold and/or centering to be displayed correctly.
The problem does not occur if the range in the third line is reduced to A1:A16376 or any other range of 16,376 cells or less. The problem will occur even if the formatting of the large range is broken into several commands and even if a large set of smaller ranges are formatted across multiple sheets (in the same workbook/Excel file). Attempting to do the same formatting of the large range with the Mata xl() class results in the same problem (I assume putexcel is calling the xl() methods). Finally, an even larger formatting range (e.g., 100,000+) results in Excel needing to repair the file's font and formatting XML (this hasn't occurred in the main code I'm working on so it's less of an issue for me).
I'm running Stata 14.2 (with 16 Mar 2017 updates), and the problem occurs opening the file in both the current and older (Office 2010) versions of Excel.
So far, there are two workarounds that I've found, but neither are particularly helpful for the reasons indicated below:
1. Switch the third and last lines in the code above. In what I'm trying to ultimately accomplish, I'm writing to several sheets sequentially, and in this case, it's the large set of many smaller-range formatting commands that seems to be an issue. I'm not sure that there's a way to order my formatting commands in that context to avoid this issue (at least one hasn't occurred to me).
2. Remove the text wrap in Excel, save the file and close, reopen and reapply the text wrap. I am trying to create multiple Excel files in an automated fashion. Having to perform these manual edits unfortunately defeats the purpose of the program I'm writing.
Am I doing something wrong? If not, are there thoughts for other workarounds?
The problem does not occur if the range in the third line is reduced to A1:A16376 or any other range of 16,376 cells or less. The problem will occur even if the formatting of the large range is broken into several commands and even if a large set of smaller ranges are formatted across multiple sheets (in the same workbook/Excel file). Attempting to do the same formatting of the large range with the Mata xl() class results in the same problem (I assume putexcel is calling the xl() methods). Finally, an even larger formatting range (e.g., 100,000+) results in Excel needing to repair the file's font and formatting XML (this hasn't occurred in the main code I'm working on so it's less of an issue for me).
I'm running Stata 14.2 (with 16 Mar 2017 updates), and the problem occurs opening the file in both the current and older (Office 2010) versions of Excel.
Code:
putexcel set "debug.xlsx", sheet("Test Sheet") replace
putexcel A1 = "text text text text"
putexcel A1:A16377, hcenter
putexcel A1, txtwrap bold
1. Switch the third and last lines in the code above. In what I'm trying to ultimately accomplish, I'm writing to several sheets sequentially, and in this case, it's the large set of many smaller-range formatting commands that seems to be an issue. I'm not sure that there's a way to order my formatting commands in that context to avoid this issue (at least one hasn't occurred to me).
2. Remove the text wrap in Excel, save the file and close, reopen and reapply the text wrap. I am trying to create multiple Excel files in an automated fashion. Having to perform these manual edits unfortunately defeats the purpose of the program I'm writing.
Am I doing something wrong? If not, are there thoughts for other workarounds?
Comment