Export dataset to Excel while retaining sheet formatting

[Isaac Maddow-Zimet]

Hi all,

I was wondering if anyone had good solutions for exporting a dataset to an (already existing) Excel worksheet while retaining the existing cell formatting in that worksheet (similar to the behavior of -putexcel-). Normally I would just convert the dataset to a matrix using -mkmat-, and then use -putexcel- to export that matrix, but in this case, I want to export a dataset that has a mix of string and numeric variables, which -mkmat- does not allow. -export excel- is perfect for what I'd like to do, as it can write a dataset to a specific cell range in an existing spreadsheet, but it overwrites any formatting (italics, number formatting, borders, etc.) in the cell range it is writing to.

Any ideas? My apologies if I'm missing an obvious solution; for some reason, I cannot seem to figure this out (and didn't see any solutions in the archives, though I'm sure others have had similar problems).

Thanks so much,
Isaac

[Clyde Schechter]

If you use the -sheetmodify- option, some of these aspects of formatting will be retained. But not all. I am also unaware of any complete solution to this problem, and I would certainly welcome one as I am often asked by my collaborators to fill in prettily formatted Excel table shells with my results.

[Roger Chu]

I do not think there is a way with -export excel- that allows you to save all of the formatting in the worksheet.

What I have done in the past is, within one workbook, I have one sheet for displaying data (let's call it DISPLAY) and one sheet for holding data from Stata (let's call it RAW). When I use -export excel-, I export my data into RAW. My DISPLAY sheet then references cells within RAW and puts data where it needs to be. I usually also hide my RAW sheet to reduce clutter.

What this workaround allows you do to is completely format DISPLAY as you see fit, and then export data into RAW without worrying about the export eliminating any of your formatting in DISPLAY. It does take a little bit of time to set up your workbook, but it works like a charm afterwards.

Note: in DISPLAY, you can either reference specific cells within RAW, or you can use Excel functions like =vlookup() to reference cells depending on what values a variable has. I often use the =vlookup() method just in case I need to change the order of my output later, but it does require you export a reference variable.

[Isaac Maddow-Zimet]

Thanks Roger and Clyde for these suggestions (and the commiseration - glad I'm not overlooking something obvious). I've tried the sheetmodify option in the past, but I think it still writes over most formatting: if you run the code below, for example, you'll see that the formatting is retained for the first row of variable names, but number formatting, font and borders all get wiped out by writing in numbers. (Note that I'm just using putexcel here to set the initial styles so that I don't have to attach an Excel file to the post).

putexcel set example.xlsx, modify
putexcel (A1:B1), bold
putexcel (A2:B2), bold
putexcel (A3:B3), italic
putexcel (A3:B3), nformat("percent")
putexcel (A1:B1), border("bottom", "medium", "black")
putexcel (A2:B2), border("bottom", "medium", "black")
putexcel (A1:A3), border("right", "medium", "black")

input var1 var2
3.78 4.2
4.1 2
end

export excel using example.xlsx, cell(A1) sheetmodify firstrow(var)

Roger, I've actually been using a similar approach of exporting my data to a "staging area" and then bringing it into the formatted section of the sheet (or a separate sheet) with formulas. I was hoping to move away from using any formulas in Excel since they introduce another layer with potential for error, but I may have to keep doing this for now though; I agree that it's the best current approach.