Purpose of the code:
Let's say I have 3 Excel files to try.
Those files have already been filtered or hidden because the content is very large. And I only need part of the content for each Excel file.
I do not want to open all files and worksheets to copy the desired range.
I want to write a code to combine each filtered or hidden cell and put those cells in a new workbook.
1.Combine all filtered or hidden cells of all worksheets for each Excel file.
2. Create a new workbook from an empty worksheet.
3. Place all cells in Step 1 in Step 2.
1. Run too slow. Maybe there are too many
import openpyxl import os import glob path = os.getcwd() data=() x=input('name：') + '.xlsx' target_xls = os.path.join(path,x) for file in glob.glob(path+'*.*'): if file.endswith((".xlsx")): wb = openpyxl.load_workbook(file, data_only=True) for sheet in wb.worksheets: for j in range(2, sheet.max_row+1 ): for i in range(1,sheet.max_column+1): ihidden = sheet.row_dimensions(j).hidden # Row Visibility True / False svalue = sheet.cell(column=i,row=j).value if ihidden == True: shidden = "HIDDEN" else: shidden = "VISIBLE" data.append(svalue) WP= openpyxl.Workbook() ws = WP.active ws.title = "Sheet1" x=sheet.max_column new_list = (data(i:i+x) for i in range(0, len(data), x)) for elem in new_list: ws.append(elem) WP.save(target_xls)