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.
Code steps:
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.
Code Problem:
1. Run too slow. Maybe there are too many for loops?
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)
