CODEWITHSUNDEEP
pythonxlsxwriter
Dhyerwolf
Dhyerwolf

Reputation: 71

How to lock scrolling in Xlsxwriter?

I am looking to stop excel from scrolling past a certain row/column. I have a table that I'm putting into excel, and everywhere outside of the table range needs to have an unlined background. However, by placing an unlined background, this includes the unlined portion in the scroll bar. As such, I would like to prevent scrolling past the limits of the table.

I've tried freeze panes, but the table expands beyond the scope of screen (approximately 50 rows and columns), and freezing the pane will not allow for scrolling across the whole table.

Upvotes: 1

Views: 676

Answers (1)

jmcnamara
jmcnamara

Reputation: 41614

It depends on how you would do it in Excel.

One way would be to hide all the unused rows and columns. In order to hide a large number of rows you need to use an Excel optimization to hide rows without setting each one (of approximately 1 million rows). To do this in XlsxWriter you can use the set_default_row() method.

Columns don’t require this optimization and can be hidden using set_column().

Here is an example:

import xlsxwriter

workbook = xlsxwriter.Workbook('hide_row_col.xlsx')
worksheet = workbook.add_worksheet()

# Write some data.
worksheet.write('D1', 'Some hidden columns.')
worksheet.write('A8', 'Some hidden rows.')

# Hide all rows without data.
worksheet.set_default_row(hide_unused_rows=True)

# Set the height of empty rows that we do want to display even
# if it is the default height.
for row in range(1, 7):
    worksheet.set_row(row, 15)

# Columns can be hidden explicitly. This doesn't increase the file size.
worksheet.set_column('G:XFD', None, None, {'hidden': True})

workbook.close()

Output:

enter image description here

Upvotes: 2

Related Questions