[SOLVED] Script to freeze top row on all .xlsx files

trickydba · 01-31-2017, 08:41 AM

Is it possible through a script to freeze the top row on all .xlsx files in a folder?

michaelk · 01-31-2017, 08:51 AM

What do you mean by freeze?

trickydba · 01-31-2017, 08:54 AM

Like when I scroll down an excel file, the very top row that has the headers is still visible

Habitual · 01-31-2017, 09:01 AM

They're called columns, for starters.

Press F1 and search for column
"Freezing Rows or Columns as Headers".

via script? If you have to ask, it won't be easy for you.</opinion>
Done.

trickydba · 01-31-2017, 09:11 AM

@Habitual......well maybe I should have said 'column headers'. Hmmm. It's not easy for me, which is why I'm asking. Lol. I know how to freeze the top row in Excel, but I wanted to see if this is possible in a script in Linux.

michaelk · 01-31-2017, 09:27 AM

It can be done using a visual basic script from within Excel. Been awhile since I've really used VB. You should be able to expand the link below to open worksheets, freeze the 1st row, save and close it.

http://stackoverflow.com/questions/3...t-in-excel-200

trickydba · 01-31-2017, 09:32 AM

Yea I researched that page already. Was hoping that I can do this all in linux.

michaelk · 01-31-2017, 10:04 AM

Not as far as I know...

trickydba · 01-31-2017, 10:25 AM

Ok. Yea I researched alot and I couldn't find anything. Appreciate all the help as usual!

JZL240I-U · 02-01-2017, 12:55 AM

Umm, trickydba this thread is NOT solved. Other people searching for a solution will not find it here. Please remove the "solved" label.

Turbocapitalist · 02-01-2017, 04:00 AM

Quote:

Originally Posted by trickydba

Ok. Yea I researched alot and I couldn't find anything. Appreciate all the help as usual!

Then it's not solved yet and could be unmarked to avoid luring people here in expectation of a solution.

If you are still looking, you could check to see about LibreOffice macros. LibreOffice can be launched headless and macros can be written in python, javascript, or java. If it is possible to launch macros from a separate file, then that might be the way to go.

AnanthaP · 02-02-2017, 02:10 AM

Earlier versions of excel had a specific xl file in a startup under your login's settings) where the default excel.xls was available.

You changed windows --> Freezepanes, saved it under some other name, quit excel, renamed the template file and reloaded excel to see the desired default.

OK

ecos · 02-02-2017, 04:20 AM

There are several ways to modify .xlsx files in Linux.
First you have to select a framework to edit each file with. Then you loop through all the files and process them as described below.

You could use the Java-based Framework Apache POI and process the files as described in https://stackoverflow.com/questions/...ng-header-rows.

Or, since .xlsx files are in effect ZIP archives containing mostly XML files, you could do this small change directly. According to a short comparison:
in file xl/worksheets/sheet1.xml (and other worksheet files if present)
... two child elements were inserted below /worksheet/sheetViews[1]/sheetView[1]:

Quote:

Unzip the .xlsx file in a working directory
Change the file(s) using the XML tool of your choice
zip the file again