Using Python: Filter an Excel Sheet and Save Filtered View as New Sheet

user_12376869 0
Viewed 411

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")
openpyxl excel python
1 Answers

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")
user_181354540
Related