Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Openpyxl and Hidden/Unhidden Excel Worksheets

I have the following code that reads data from a tab-delimited text file and then writes it to a specified worksheet within an existing Excel workbook. The variables "workbook", "write_sheet", and "text_file" are input by the user

tab_reader = csv.reader(text_file, delimiter='\t')
xls_book = openpyxl.load_workbook(filename=workbook)
sheet_names = xls_book.get_sheet_names()
xls_sheet = xls_book.get_sheet_by_name(write_sheet)
for row_index, row in enumerate(tab_reader):
        number = 0
        col_number = first_col
        while number < num_cols:
                cell_tmp = xls_sheet.cell(row = row_index, column = col_number)
                cell_tmp.value = row[number]
                number += 1
                col_number += 1
xls_book.save(workbook)

However when I run this code on a preexisting "workbook" in which "worksheet" is a hidden tab, the output unhides the tab. I think the reason is because openpyxl is not modifying the file but creating a new file entirely. Is there an easy way to tell python to check if the worksheet is hidden and then output a hidden or unhidden sheet based on whether or not the condition is satisfied?

Thanks!

like image 791
user3543052 Avatar asked Apr 18 '14 15:04

user3543052


People also ask

How do you hide a sheet in Excel using Python?

The worksheet object's hide() method makes the worksheet disappear till it is unhidden through Excel menu. In the following worksheet, there are three sheets, of which sheet2 is hidden.

How do I find hidden Excel workbooks?

In Excel, click the View tab, then click Unhide in the Window group. See screenshot: 2. If there is only one hidden workbook, after clicking the Unhide command, the hidden workbook will show up.


1 Answers

We currently don't support hiding worksheets in openpyxl so this is just ignored when reading the file and, therefore, lost when saving it. I don't think it should be too hard to add it. Please submit a feature request on Bitbucket.

[UPDATE]

The feature is now available:

ws.sheet_state = 'hidden'

Or actually xls_sheet.sheet_state = 'hidden' in your particular case.

like image 128
Charlie Clark Avatar answered Sep 19 '22 04:09

Charlie Clark