I have a excel file which I opened in openpyxl and cleaned it and closed the work book, with the example code below:

#filename is the name of the excel file used 
wb = load_workbook(filename)
sheet = wb.active

#I did some cleaning on the sheet.
#I closed the workbook

wb.close()

As a part of the unit test of this function I want to read the content of the sheet of this workbook so that I can write a assert statement for the unit test.

I tried to read with:

sheet.read()

But it only prints and read the sheet name like

Workbook <Sheet 1>

I know we can iterate over rows of the sheet and read the contents of each row or cell but it would be too complicated to store for a unit tests and then assert it with a expected value.

How can I simply read the whole content of the sheet.

Thanks

CodePudding user response：

Excel files are tricky. On what basis should two cell values be considered equal? What if they contain the same values, but one is bold? Or if they have the same formula, but different computed values?

There are lots of ways that cells can differ, but I think you're looking for Worksheet's values property:

Produces all cell values in the worksheet, by row

Type: generator

For example, you can generate a list of tuples by doing

list(sheet.values)

For a worksheet containing

this yields

[('A1', 'B1', 'C1'),
 ('A2', 'B2', 'C2'),
 ('A3', 'B3', 'C3'),
 ('=SUM(1, 2, 3)', None, None)]