Alternative to export excel using putexcel

[Tim Evans]

Hi, I am using Stata 14.1 and exporting data sets in Stata to excel using -export-. However, I'm having trouble retaining the formatting I have in Stata when exported to Excel - for instance I cant retain the comma separation on 1,000 when exported to Excel. The only way I can do this is convert to a string value and export to excel. The problem with this is that when it is exported to Excel, it appears as a string rather than a number. If I had one Excel file, I could just 'convert to number' however this is not feasible on 60 tabs.

Is it possible to use putexcel - which appears to be able to set cell formats in Excel to export a dataset? Having read a number of tutorials on how to export with putexcel, the only ones I have come across relate to either exporting charts or exporting stored estimation results.

This is my current set up for exporting Stata files and then also exporting related chart.

clear

capture: log close export_to_excel
log using "${logdir}export_to_excel.log", replace text name(export_to_excel)


clear
filelist, dir("$outdir") pattern("*.png") norecursive

generate group1 = 1 if regexm(filename, "^incOfSTIA")
replace group1  = 2 if regexm(filename, "^incOfTIA")
replace group1  = 3 if regexm(filename, "^incOfstroke")
replace group1  = 4 if regexm(filename, "^prOfSTIA")
replace group1  = 5 if regexm(filename, "^prOfTIA")
replace group1  = 6 if regexm(filename, "^prOfstroke")
    
label define Group 1 "inc_STIA" 2 "inc_TIA" 3 "inc_stroke" 4 "pr_STIA" 5 "pr_TIA" 6 "pr_stroke"
label values group1 Group
drop if group1==.
decode group1, gen(group2)

save "${outputs}png_excel_export.dta", replace

use "${outputs}png_excel_export.dta", clear

    local obs = _N
    gen obs2 = _n
      forvalues i=1(1)`obs' {
     preserve
     keep if obs2 ==`i'
     local group = group2
     local name = filename
     di "`name'"
     local tabname = regexr("`name'","\.png","")
     di "`tabname'"
     putexcel set "`group'", sheet("`tabname'") modify
      export excel using "`group'.xlsx", sheet("`tabname'") sheetmodify firstrow(variables)
     putexcel (I1)=picture("`name'")
      restore
     }
  
  
  

clear
filelist, dir("$outdir") pattern("*.dta") norecursive
generate group1 = 1 if regexm(filename, "^incOfSTIA")
replace group1  = 2 if regexm(filename, "^incOfTIA")
replace group1  = 3 if regexm(filename, "^incOfstroke")
replace group1  = 4 if regexm(filename, "^prOfSTIA")
replace group1  = 5 if regexm(filename, "^prOfTIA")
replace group1  = 6 if regexm(filename, "^prOfstroke")

label define Group 1 "inc_STIA" 2 "inc_TIA" 3 "inc_stroke" 4 "pr_STIA" 5 "pr_TIA" 6 "pr_stroke"
label values group1 Group
drop if group1==.
decode group1, gen(group2)

save "${outputs}dta_excel_export.dta", replace

 use "${outputs}dta_excel_export.dta", clear
    local obs = _N
    gen obs2 = _n
      forvalues i=1(1)`obs' {
     preserve
     keep if obs2 ==`i'
     local group = group2
     local name = filename
     use "`name'", replace
     local tabname = regexr("`name'","\.dta","")
     di "`tabname'"
     export excel using "`group'.xlsx", sheet("`tabname'") sheetmodify firstrow(variables)
      restore
     }

Thanks

Tim

[Tim Evans]

To answer my own question: I can change formatting with putexcel using the following code

     putexcel A2:I50, nformat(number_sep)
     putexcel A1:H1, bold border(bottom)
      restore

In this code:

clear
filelist, dir("$outdir") pattern("*.dta") norecursive
generate group1 = 1 if regexm(filename, "^incOfSTIA")
replace group1  = 2 if regexm(filename, "^incOfTIA")
replace group1  = 3 if regexm(filename, "^incOfstroke")
replace group1  = 4 if regexm(filename, "^prOfSTIA")
replace group1  = 5 if regexm(filename, "^prOfTIA")
replace group1  = 6 if regexm(filename, "^prOfstroke")

label define Group 1 "inc_STIA" 2 "inc_TIA" 3 "inc_stroke" 4 "pr_STIA" 5 "pr_TIA" 6 "pr_stroke"
label values group1 Group
drop if group1==.
decode group1, gen(group2)

save "${outputs}dta_excel_export.dta", replace

 use "${outputs}dta_excel_export.dta", clear
    local obs = _N
    gen obs2 = _n
      forvalues i=1(1)`obs' {
     preserve
     keep if obs2 ==`i'
     local group = group2
     local name = filename
     use "`name'", replace
     local tabname = regexr("`name'","\.dta","")
     di "`tabname'"
     export excel using "`group'.xlsx", sheet("`tabname'") sheetmodify firstrow(variables)
     putexcel set "`group'", sheet("`tabname'") modify
     *putexcel (I1)=picture("`name'")
     putexcel A2:I50, nformat(number_sep)
     putexcel A1:H1, bold border(bottom)
      restore

[Clyde Schechter]

Tim, that's great. I wasn't aware that -putexcel- could apply formatting like that without also being used to put data in a cell. Applying it after -export excel- to format data that's already there makes life a lot simpler. Thanks for posting this.