I'm currently working on a small program that takes a worksheet with a massive number of rows (hundreds of thousands), filters out for specific values in a single column, then outputs the filtered view as a new worksheet with just the filtered view entries. I am unsure if there is a more efficient way to approach this (And I would like to be more efficient as I'm dealing with large amounts of rows).
So far, I've managed to copy the rows that have the specified value, but I am losing all formatting applied to the original worksheet.
I'm using openpyxl, but am unsure of how to keep formatting.
import openpyxl as opxl
book = opxl.load_workbook('Test.xlsx', read_only=True)
book2 = opxl.load_workbook('new_test.xlsx')
sheet = book.worksheets[2]
sheet2 = book2.worksheets[0]
for row in sheet.iter_rows():
if row[4].value == 'TESTY':
sheet2.append((cell.value for cell in row))
elif row[4].value == 'TESTX':
sheet2.append((cell.value for cell in row))
book2.save("Test(edited).xlsx")
CodePudding user response:
Something like this should do what your question asks:
import openpyxl as opxl
book = opxl.load_workbook('Test.xlsx', read_only=True)
book2 = opxl.load_workbook('new_test.xlsx')
sheet = book.worksheets[2]
sheet2 = book2.worksheets[0]
for row in sheet.iter_rows():
if row[4].value in ('TESTY', 'TESTX'):
sheet2.append((cell.value for cell in row))
for column, cell in enumerate(row):
dst = sheet2.cell(sheet2.max_row, column 1)
dst.font = cell.font
if cell.fill != None: dst.fill = cell.fill
dst.border = cell.border
dst.alignment = cell.alignment
if cell.number_format != None: dst.number_format = cell.number_format
if cell.value != None: dst.protection = cell.protection
book2.save("Test(edited).xlsx")