0

Hi I am writing code to loop through all the excel spreadsheet in a workbook to pick up a certain range from each spreadsheet.

My code is as below:

from openpyxl import load_workbook
import pandas as pd
wb = load_workbook('D:\Temp\DD_Catalogues_6092020.xlsx')
sheetnames=wb.sheetnames'
for i in sheetnames:
    i = wb[i]
    row=i.max_row-3
    data_rows = []
    for row in i[1:row]:
        data_cols = []
        for cell in row:
            data_cols.append(cell.value)
        data_rows.append(data_cols)
df = pd.DataFrame(data_rows)    
print(df)

However, it doesn't seem loop through all the worksheet and only pick up the first worksheet. May I know how to resolve that? Thank you so much!

Hellen Lai
  • 43
  • 2
  • Does this answer your question? [how to concatenate multiple excel sheets from the same file?](https://stackoverflow.com/questions/46605910/how-to-concatenate-multiple-excel-sheets-from-the-same-file) – Tomerikoo Sep 11 '20 at 09:34
  • Not really as i am using openpyxl. how can i do the same using openpyxl. i think i just need some help to take a look at my code and see what went wrong – Hellen Lai Sep 11 '20 at 09:50
  • What went wrong is that `data_rows` is being reset every iteration so you create a dataframe of the last sheet only. It is not clear what you want to do so I can't help. Are you trying to merge all sheets to one? Do some kind of processing on each? Why are you mixing `pandas` and `openpyxl`? If you're planning on using a dataframe , why not just use `pandas`? – Tomerikoo Sep 11 '20 at 19:36
  • Thanks. What i am trying to do is 1. Take a specific range(from row 1 to the last third row, and from column A to column I) in the excel sheet. 2. iterate it through all sheet in the excel workbook 3. append them all into one data frame. – Hellen Lai Sep 11 '20 at 23:55

0 Answers0