freeze_panes working properly under Google Drive *.xlsx spreadsheet but not when viewed by MS-Excel or Libre Office Calc

51 views
Skip to first unread message

Paulo Góes

unread,
Aug 21, 2022, 3:15:04 PM8/21/22
to openpyxl-users
Hi everyone

The following code snipet makes a *.xlsx file properly frozen at C2 cell. I can see that after running it and upload the the *.xlsx file and finally open it in Google Drive. It looks like the freeze_panes is ok (the C2 cell is correctly frozen), but when I open the same file on my system using MS-Excel or Libre Office Calc the A1 column isn't shown. Has anyone come accross something like that?

def freeze_cell(filename, row_to_freeze):
    work_book = xl.load_workbook(filename=filename)
    sheet = work_book.active
    sheet.freeze_panes = row_to_freeze
    work_book.save('reviews-sample-frozen.xlsx')

freeze_cell('reviews-sample.xlsx', row_to_freeze='C2')


Thanks in advance

Charlie Clark

unread,
Aug 22, 2022, 6:27:54 AM8/22/22
to openpyxl-users

On 21 Aug 2022, at 21:15, Paulo Góes wrote:

Hi everyone

The following code snipet makes a *.xlsx file properly frozen at C2 cell. I

can see that after running it and upload the the *.xlsx file and finally

open it in Google Drive. It looks like the freeze_panes is ok (*the C2 cell

is correctly frozen*), but when I open the same file on my system using

MS-Excel or Libre Office Calc the A1 column isn't shown. Has anyone come

accross something like that?

def freeze_cell(filename, row_to_freeze):

work_book = xl.load_workbook(filename=filename)

sheet = work_book.active

sheet.freeze_panes = row_to_freeze

work_book.save('reviews-sample-frozen.xlsx')

freeze_cell('reviews-sample.xlsx', row_to_freeze='C2')

I've never worked with this directly but I think it's best to work with the full OOXML API. In this worksheets have multiple views and freezing is set for panes within a view. This is more verbose but makes it easier to compare what you see in the XML of a worksheet. freeze_panes is more convenient but older and really ought to be deprecated.

Get the view:

view = ws.sheet_view # == ws.sheetView[0]
pane = view.pane

From the specification.

"""
18.3.1.66 pane (View Pane)

Worksheet view pane

  • activePane (Active Pane)

The pane that is active. The possible values for this attribute are defined by the ST_Pane simple type (§18.18.52).

  • state (Split State)

Indicates whether the pane has horizontal / vertical splits, and whether those splits are frozen. The possible values for this attribute are defined by the ST_PaneState simple type (§18.18.53).

  • topLeftCell (Top Left Visible Cell)

Location of the top left visible cell in the bottom right pane (when in Left-To-Right mode). The possible values for this attribute are defined by the ST_CellRef simple type (§18.18.7).

  • xSplit (Horizontal Split Position)

Horizontal position of the split, in 1/20th of a point; 0 (zero) if none. If the pane is frozen, this value indicates the number of columns visible in the top pane. The possible values for this attribute are defined by the W3C XML Schema double datatype.

  • ySplit (Vertical Split Position)

Vertical position of the split, in 1/20th of a point; 0 (zero) if none. If the pane is frozen, this value indicates the number of rows visible in the left pane. The possible values for this attribute are defined by the W3C XML Schema double datatype.
"""

--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Sengelsweg 34
Düsseldorf
D- 40489
Tel: +49-203-3925-0390
Mobile: +49-178-782-6226

Reply all
Reply to author
Forward
0 new messages