Freeze panes in Excel file produced in Stata

[Sergiy Radyakin]

Dear All,

is it possible to freeze the panes in an Excel file produced using Stata's putexcel or Mata's xl() commands? If yes, how?
Or if there is a user-written command for that purpose, kindly let me know.

Thank you, Sergiy Radyakin

[Leonardo Guizzetti]

You should start submitting these lists to the wishlist thread. Seems like another candidate for a request.

You might consider moving some manipulations over to Python, specifically with Openpyxl. A quick google search brought this forward. It's often a tool underlying automated excel exports generated in the clinical world.

[Sergiy Radyakin]

You should start submitting these lists to the wishlist thread.

Dear Leonardo,

Before submitting as a wish, I need to make sure there is no current solution or workaround (because there are so many things in Stata, and also so many recently added features, that it's hard to keep up). Otherwise it will just contaminate the wish list thread.

The Python code solves it.

Thank you and have a good weekend! Sergiy

[Santosh Dash]

Leonardo Guizzetti It (Freezing panes the Stata spreadsheet) would be a great addition to Stata 18, especially when we are viewing large datasets.

[Raymond Guiteras]

For the benefit of future Stata users who find this thread when searching: as of Stata 19, there is a set_split option for putexcel and a set_split() function in Mata that freeze panes.

putexcel example:

https://www.stata.com/new-in-stata/n...res/#excel_doc

Mata set_split() example (from my own code, caveat lector):

  loc sheet_name "Unmatched observations"
  export excel ///
    using "output/data/xlsx/spotChecks-StudyVillages-unMatchedVillage.xlsx" ///
    , firstrow(variables) sheet("`sheet_name'", replace)
    
  mata: 
    B = xl()  
    B.load_book("output/data/xlsx/spotChecks-StudyVillages-unMatchedVillage.xlsx")
    sheet = st_local("sheet_name")
    B.set_sheet(sheet)
    
    // freeze below the first row and to the right of pc11_id
    B.set_split(1,st_varindex("pc11_id"))
    

    B.close_book()

  end

[Fahad Mirza]

For the benefit of future Stata users who find this thread when searching: as of Stata 19, there is a set_split option for putexcel and a set_split() function in Mata that freeze panes.

putexcel example:

https://www.stata.com/new-in-stata/n...res/#excel_doc

Mata set_split() example (from my own code, caveat lector):

loc sheet_name "Unmatched observations"
export excel ///
using "output/data/xlsx/spotChecks-StudyVillages-unMatchedVillage.xlsx" ///
, firstrow(variables) sheet("`sheet_name'", replace)

mata:
B = xl()
B.load_book("output/data/xlsx/spotChecks-StudyVillages-unMatchedVillage.xlsx")
sheet = st_local("sheet_name")
B.set_sheet(sheet)

// freeze below the first row and to the right of pc11_id
B.set_split(1,st_varindex("pc11_id"))


B.close_book()

end

This is really nice! thanks for sharing, did not realise this feature was there. Also, is there an option to add filters?