export excel and formatting

[Haris Fawad]

In an ideal world export excel has an option to retain whatever formatting that is specified on each exported variable. There's already an option rendering date formats using datestring, so why not expand this functionality to other types?

I'm aware of putexcel and the xl class in Mata that can be used to achieve the desired formatting. Granted my limitted experience in exporting data, the workflow then often involves opening/reopening the output excel file and formatting iteratively until I'm satisfied with the presentation. By then, it would've have been more efficient to just set the formatting using excel itself.

I've found that there's at least one other user that would've benefitted from an option in export excel to retain formatting: https://www.statalist.org/forums/for...using-putexcel.

What do you think? Should there be such an option in export excel, or is it redundant/inappropriate? Any advice you have to offer is also appreciated.

[William Lisowski]

When you write "it would've have been more efficient to just set the formatting using excel itself" regarding output using putexcel, it seems to me you are not aware that by default putexcel will leave cell formatting unchanged if you are modifying an existing worksheet, and thus formatting can be set using Excel itself.

[Haris Fawad]

Thank you for your answer, William Lisowski. You're right, I wasn't aware that putexcel leaves the formatting unchanged.

However, I'm hoping for a solution where there's no need to open Excel at all. That is, a solution where the data retains whatever display format it has in Stata when exported.

[Nick Cox]

I don't see really precise and workable proposals here, but my sense is that this is a highly problematic idea.

As a matter of principle, data aren't defined by their display formats. Those formats just control what you see, not what is stored. (It's a struggle to get many users to see this, even though it's the definition!)

As the display format doesn't tie down the data, how is the display format to be exported as extra information?

What do you do when display formats aren't intelligible to importing software (e.g. which other software knows what %9.0g means).?

Consider some examples.

A numeric variable with fractional parts is held as double but as a matter of taste and practicality I usually don't want to see anything more than three decimal places, so I currently assign %9.3f. What gets exported? Recall that most decimals that are multiples of 0.001 don't have exact binary equivalents.

Similarly, what about a date-time with a format %tcD_N_Y (i.e. time of day information suppressed in display)? What gets exported?

All that said, this bites with numeric variables, and you always have the option of e.g. exporting as string(whatever, "%9.3f")

EDIT: Corrected %td to %tcD_N_Y in example above.

[William Lisowski]

To me the key is that Stata numeric formats can be translated into Excel numeric formats, at least to some extent. For example, a Stata numeric format of ending in .2fc is well approximated by an Excel format of #,##0.00 understanding that Excel has no interest in specifying a maximum number of characters in the displayed value. And we see from help putexcel that Stata has the ability to pass formatting instructions - specified as Excel formats - into Excel. So while I'll let Haris Fawad speak for himself, it seems to me that in theory export excel could do at least a reasonable job of (optionally, of course) sending Excel formats for the columns that receive the Stata variables. As to what gets exported, that would of course be what is stored in Stata, not controlled by the format, as is now the case, and this avoids the loss of precision that exporting formatted strings would entail.

Now, I'm not voting for this as my most desired feature, but it does seem possible. It's even possible that someone could use

putexcel cellrange, overwritefmt format_options

to apply suitable formatting after export excel having first derived the necessary Excel formats from the Stata formats.

[Andrew Mupere]

when I use export excel command, the data output in excel comes with decimal points, yet the original file didnt have them. How do I avoid this?