Integrating Mata in loop with putexcel to specify cell width

[Patrick Abi Nader]

Hello all,


I am trying to 1) export to excel, and 2) then format data using the below code and I can't seem to figure out how to integrate a mata to pre-specify width of columns.

My exportation code works perfectly and even the part where i use putexcel for formating. I know that putexcel cannot specify a cell width so i came across the folloing post which references this webpage but I have no idea which part is pertinent for me.

Is there anything in that webpage that might be useful for me to specifiy exactly the width of each exported column ? and where do I integrate that mata in my code (if i can)? I have never used mata so i am really not sure. If it's best to post this question in the mata forum i will modify it. Best wishes and thank you for any assistance.

* Variables to export
local vars Facteurs_de_risque IndicateurInformations Données_SLSJ Moyenne_Régionale


* Get climat values
levelsof climat, local(clims)



foreach c of local clims {

*------------------------------*
* File name = climat label
*------------------------------*
local clim_label : label (climat) `c'
local fname "`clim_label'"
local fname = subinstr("`fname'", " ", "_", .)
local fname = ustrnormalize("`fname'", "nfd")
local fname = ustrregexra("`fname'", "\p{M}", "")
local fname = subinstr("`fname'", "/", "-", .)

if "`fname'" == "" local fname "climat_`c'"

local outfile "export_`fname'.xlsx"
cap erase "`outfile'"

*------------------------------*
* Sous-composantes for this climat
*------------------------------*
levelsof souscompnum if climat == `c', local(subs)

foreach s of local subs {

*------------------------------*
* Sheet name = souscomp label
*------------------------------*
local sous_label : label (souscompnum) `s'
local sheet "`sous_label'"
local sheet = subinstr("`sheet'", " ", "_", .)
local sheet = ustrnormalize("`sheet'", "nfd")
local sheet = ustrregexra("`sheet'", "\p{M}", "")
local sheet = subinstr("`sheet'", "/", "-", .)

if "`sheet'" == "" local sheet "souscomp_`s'"

*------------------------------*
* EXPORT (CORRECT SYNTAX)
*------------------------------*
export excel `vars' ///
if climat == `c' & souscompnum == `s' ///
using "`outfile'", ///
sheet("`sheet'") sheetreplace ///
firstrow(varlabels)
*----------------------------------*
*----------------------------------*
* FORMATTING (wrap + left align)
*----------------------------------*

* Number of columns
local nvars : word count `vars'
local lastcol = char(64 + `nvars')

* Number of rows (+1 for header)
count if climat == `c' & souscompnum == `s'
local lastrow = r(N) + 1

* Ranges
local hdr "A1:`lastcol'1"
local body "A1:`lastcol'`lastrow'"
local last "A`lastrow':`lastcol'`lastrow'"

* Activate sheet
putexcel set "`outfile'", sheet("`sheet'") modify

* Header: bold, wrap, left aligned
putexcel `hdr', bold txtwrap left

* Body: wrap + left aligned
putexcel `body', txtwrap left

* Thick borders
putexcel `hdr', border(top, thick)
putexcel `hdr', border(bottom, thick)
putexcel `last', border(bottom, thick)

}
}
}

[Bert Lloyd]

Step 17 in the linked page looks right.

The end at the end of a mata block can cause problems in stata loops. I believe that strictly speaking the right thing to do is to write a proper mata function in a program (ado-file)and then call that program in your loop.

I have found this inconvenient in quick tasks and have not had any problems with two alternatives

mata one-liners

using braces to delimit mata blocks

However, these may be considered unwise.

Some helpful discussion here:

https://www.statalist.org/forums/for...73#post1459773

https://www.statalist.org/forums/for...-loop-and-mata

[Patrick Abi Nader]

Hello Bert,

Thank you very much for your response. When I run the following code my excel files remain unchanged what am I missing?

.
. local folder "C:/Users/abpa2175/OneDrive - Sante et Services sociaux/RapportVRAC/Tableau/DDR"
. local files : dir "`folder'" files "*.xlsx"
. mata:
------------------------------------------------- mata (type end to exit) ------------------------------------------------------
: void set_colwidth_one_sheet(string scalar filename, real scalar sheetnum)
> {
> class xl scalar b
>
> b = xl()
> b.load_book(filename)
> b.set_sheet(sheetnum)
> b.set_column_width(1, 1, 45) // Column A
> b.set_column_width(2, 2, 75) // Column B
> b.set_column_width(3, 4, 30) // Columns C-D
> b.close_book()
> }

: end
--------------------------------------------------------------------------------------------------------------------------------
. foreach f of local files {
2. di "Processing file: `f'"
3.
. forvalues s = 1/4 {
4. capture mata: set_colwidth_one_sheet("`folder'/`f'", `s')
5. }
6. }
Processing file: cons_chaleur_extreme_et_chaleur_estivale.xlsx
Processing file: cons_feux_de_foret.xlsx
Processing file: cons_glissement_de_terrain.xlsx
Processing file: cons_inondation.xlsx
Processing file: cons_pollens_et_allergenes.xlsx
Processing file: cons_precipitations_extremes_ou_atypiques.xlsx
Processing file: cons_qualite_de_l'air.xlsx
Processing file: cons_qualite_de_l'eau.xlsx
Processing file: cons_tempete.xlsx
Processing file: cons_zoonoses_et_vecteurs_de_maladies.xlsx
Processing file: vraisemblance_chaleur_extreme.xlsx
Processing file: vraisemblance_feux_de_foret.xlsx
Processing file: vraisemblance_glissements_de_terrain.xlsx
Processing file: vraisemblance_inondation.xlsx
Processing file: vraisemblance_pollen_et_allergene.xlsx
Processing file: vraisemblance_precipitation_extreme_ou_atypique.xl sx
Processing file: vraisemblance_qualite_de_l'air.xlsx
Processing file: vraisemblance_qualite_de_l'eau.xlsx
Processing file: vraisemblance_tempete.xlsx
Processing file: vraisemblance_vecteur_de_maladie_zoonose.xlsx

.
end of do-file

Best wishes,
Patrick

[Bert Lloyd]

Maybe try running it once while coding in the arguments directly rather than looping through the macros

mata: set_colwidth_one_sheet("[hardcode folder path]/cons_precipitations_extremes_ou_atypiques.xlsx", 1)

I'm not sure how mata oneliners handle stata macros, it may be that mata is not picking up the values of the macros

I would also put some display or printf statements into your code to be sure the arguments are getting through correctly, e.g.,

display(folder)
display(f)
display(s)