CODEWITHSUNDEEP
pythonexcelopenpyxl
Jekson
Jekson

Reputation: 3262

Delete rows and cols from exist workbook sheet

I am trying to remove from the sheet all filled rows except the first three and all columns starting from the coordinates (U - AA). I have to remove all filled in information except for the headers in the end. How to remove the columns I did not understand, but when I ran the script to delete row the information is left, only the styles are removed. How to perform such operation correctly?

class DownloadRfiExcelFile(APIView):
    """
    Download rfi excel file to user
    """
    def get(self, request, format=None, **kwargs):
        file = default_storage.url('test.xlsx')
        wb = load_workbook(filename=file)

        response = HttpResponse(content_type='application/vnd.ms-excel')
        response['Content-Disposition'] = 'attachment; filename="test.xlsx"'
        sheet = wb["RT"]
        for rowNum in range(3, 1114):
                sheet.delete_rows(rowNum)
        for col in sheet.iter_cols():
            for cell in col:
                # delete from U to AA row
        wb.save(response)
        return response

Upvotes: 0

Views: 1539

Answers (2)

spinpwr
spinpwr

Reputation: 341

The openpyxl.utils has a function that to convert column strings to integers: column_index_from_string.

So using Dror Av.s answer can just import it instead of writing your own function:

from openpyxl.utils import column_index_from_string as col2num

# Setting initial values for deletion
...

The column_index_from_string uses openpyxls internal _COL_STRING_CACHE to lookup the column string. I don't know if it's faster, but in most cases the built-in will be fine I guess.

Upvotes: 0

Dror Av.
Dror Av.

Reputation: 1214

To delete rows and columns just use:

  • sheet.delete_rows({first_row}, {amount})
  • sheet.delete_cols({first_col}, {amount})

accordingly, read more about in the openpyxl documentation. Due notice that columns are represented as integers so A == 1, B ==2 And so on.

import string

def col2num(col):
    # Utility function to convert column letters to numbers

    num = 0
    for c in col:
        if c in string.ascii_letters:
            num = num * 26 + (ord(c.upper()) - ord('A')) + 1
    return num

# Setting initial values for deletion
starting_row = 4
starting_col = col2num('U')
last_col = col2num('AA')

# Deleting rows and columns
sheet.delete_rows(starting_row, sheet.max_row-starting_row)
sheet.delete_cols(starting_col, last_col-starting_col)

Upvotes: 2

Related Questions