Formatting Column Width when using -putexcel -

[James Voss]

Hello all,

Question:
Is there a way to format column width in an excel file that is produced using -putexcel -?

This is my first week using -putexcel- and it's been a great learning experience.
Per the advice on posting, I've already searched the online help for Stata, used Stata's searched command, read through the manuals, and the bulletin and journal. I've also recently attended the very helpful stata webinar on Reproducible Reporting done by Gabriela.

I am worried that what I'm hoping for does not yet exist.

Here's a sample of the data, which has been saved as "SampleData01.dta."

* Example generated by -dataex-. For more info, type help dataex
clear
input str30 EMPLOYERNAME long AUN double startsal int year str14 PhoneNumber str47 Website str14 COUNTYNAME str30 IU str40 Category
"IU 01 INTERMEDIATE"         101000000 43000 2026 "" "" "Washington" "Intermediate Unit 1"       "Intermediate Unit"
"IU 02 PITTSBURGH-MT OLIVER" 102000000 48579 2024 "" "" "Allegheny"  "Pittsburgh-Mt Oliver IU 2" "Intermediate Unit"
"IU 03 ALLEGHENY"            103000000 47000 2026 "" "" "Allegheny"  "Allegheny IU 3"            "Intermediate Unit"
"IU 04 MIDWESTERN"           104000000 59898 2026 "" "" "Mercer"     "Midwestern IU 4"           "Intermediate Unit"
"IU 05 NORTHWEST TRI-COUNTY" 105000000 63830 2026 "" "" "Erie"       "Northwest Tri-County IU 5" "Intermediate Unit"
end

Missing (as far as I can tell) are the variable labels which have even more characters in them because the intent of this file is to be public-facing and so end-user friendly.
Those variable labels are: Employer, AUN, Starting Salary, Year (Spring Semester), and County.

Here's my code so far:

****************

save "SampleData01.dta", replace
export excel using "SampleData01.xlsx", firstrow(varlabels) nolabel replace
putexcel set "SampleData01.xlsx", modify
putexcel A1:e700, font("Aptos", 11) hcenter
putexcel A1:e1, fpattern(solid,lightgray) hcenter border(all, medium) bold
putexcel C2:C700, nformat(#,###)

*****************

The file created by - putexcel - has columns that aren't wide enough for:
-- the column headers in columns: A, C, and D
-- the text data in columns A and E
-- the numeric data in column B.

Is there a way to automatically or manually set the excel file's column widths so that they are wide enough?

I have tried - txtwrap - and that is not the solution because the columns are so narrow the - txtwrap - causes individual words to be wrapped over two lines.


Thanks for your consideration of this puzzle.

[James Voss]

Reposting so I can put the code within "code delimiters." Sorry about the oversight.
Question: Is there a way to format column width in an excel file that is produced using -putexcel -?

Here's a sample of the data, which has been saved as "SampleData01.dta."

* Example generated by -dataex-. For more info, type help dataex
clear
input str30 EMPLOYERNAME long AUN double startsal int year str14 PhoneNumber str47 Website str14 COUNTYNAME str30 IU str40 Category
"IU 01 INTERMEDIATE" 101000000 43000 2026 "" "" "Washington" "Intermediate Unit 1" "Intermediate Unit"
"IU 02 PITTSBURGH-MT OLIVER" 102000000 48579 2024 "" "" "Allegheny" "Pittsburgh-Mt Oliver IU 2" "Intermediate Unit"
"IU 03 ALLEGHENY" 103000000 47000 2026 "" "" "Allegheny" "Allegheny IU 3" "Intermediate Unit"
"IU 04 MIDWESTERN" 104000000 59898 2026 "" "" "Mercer" "Midwestern IU 4" "Intermediate Unit"
"IU 05 NORTHWEST TRI-COUNTY" 105000000 63830 2026 "" "" "Erie" "Northwest Tri-County IU 5" "Intermediate Unit"
end

Here's my code so far:

save "SampleData01.dta", replace
export excel using "SampleData01.xlsx", firstrow(varlabels) nolabel replace
putexcel set "SampleData01.xlsx", modify
putexcel A1:e700, font("Aptos", 11) hcenter
putexcel A1:e1, fpattern(solid,lightgray) hcenter border(all, medium) bold
putexcel C2:C700, nformat(#,###)

The file created by - putexcel - has columns that aren't wide enough for:
-- the column headers in columns: A, C, and D
-- the text data in columns A and E
-- the numeric data in column B.

Is there a way to automatically or manually set the excel file's column widths so that they are wide enough?

Thanks for your consideration of this puzzle.

[Andrew Musau]

There is no option to change the column width using putexcel. You can only force the text to fill Excel's default column width.

shrinkfit and noshrinkfit specify whether or not the text is to be shrunk to fit in the cell width of a cell or in each cell of a range of cells. The default is no shrinking. noshrinkfit has an effect only if the cell or cells were previously formatted to shrink text to fit.

Alternatively, you can wrap text.

txtwrap and notxtwrap specify whether or not the text is to be wrapped in a cell or within each cell in a range of cells. The default is no wrapping. notxtwrap has an effect only if the cell or cells were previously formatted to wrap.

If you know the specific column numbers that need to be widened, you can use Mata to adjust them, either individually or for a group of columns.

* Example generated by -dataex-. For more info, type help dataex
clear
input str30 EMPLOYERNAME long AUN double startsal int year str14 PhoneNumber str47 Website str14 COUNTYNAME str30 IU str40 Category
"IU 01 INTERMEDIATE" 101000000 43000 2026 "" "" "Washington" "Intermediate Unit 1" "Intermediate Unit"
"IU 02 PITTSBURGH-MT OLIVER" 102000000 48579 2024 "" "" "Allegheny" "Pittsburgh-Mt Oliver IU 2" "Intermediate Unit"
"IU 03 ALLEGHENY" 103000000 47000 2026 "" "" "Allegheny" "Allegheny IU 3" "Intermediate Unit"
"IU 04 MIDWESTERN" 104000000 59898 2026 "" "" "Mercer" "Midwestern IU 4" "Intermediate Unit"
"IU 05 NORTHWEST TRI-COUNTY" 105000000 63830 2026 "" "" "Erie" "Northwest Tri-County IU 5" "Intermediate Unit"
end

export excel using "SampleData01.xlsx", firstrow(varlabels) nolabel replace keepcellfmt
putexcel set "SampleData01.xlsx", modify
putexcel A1:e700, font("Aptos", 11) hcenter
putexcel A1:e1, fpattern(solid,lightgray) hcenter border(all, medium) bold
putexcel C2:C700, nformat(#,###)
mata: xl = xl()
mata: xl.load_book("SampleData01.xlsx")
mata: xl.set_sheet("Sheet1")
*WIDEN COLUMNS 1, 5, 7, 8, 9
foreach col of numlist 1 5  7/9{
    mata: xl.set_column_width(`col',`col',29)
}
mata: xl.close_book()

Res.:

[James Voss]

Thank you Andrew Musau, the mata adjustments work like a charm.
Thanks for thinking outside of the - putexcel - box. It's a relief to know I was not missing something within putexcel's capabilities.