如何正确删除Excel工作表指定行与列？Django代码问题排查

Hey there! Let's tackle your Excel manipulation problems one by one. You want to keep the first 3 header rows, delete all other filled rows, and remove columns U to AA. Let's break down what was going wrong and fix it:

1. The Problem with Your Current Row Deletion #

Your current loop for rowNum in range(3, 1114) tries to delete rows starting from row 3, but here's the catch: when you delete row 3, the row that was originally 4 becomes the new row 3. This means your loop skips the next row you intended to delete, leaving leftover data. Plus, hardcoding 1114 isn't flexible if your sheet's row count changes.

Fix: Delete rows starting from the bottom and moving upward. This way, deleting a row doesn't affect the index of the rows you haven't processed yet.

2. How to Delete Columns U to AA #

In openpyxl, columns are referenced by numbers: U is column 21, AA is column 27. Just like rows, you need to delete columns from right to left (starting with AA then moving to U). If you delete left to right, deleting column U would shift columns V-AA left by one, making you delete the wrong columns next.

Modified Working Code #

Here's the updated version of your DownloadRfiExcelFile class with fixes for both issues:

from openpyxl import load_workbook
from django.http import HttpResponse
from django.core.files.storage import default_storage
from rest_framework.views import APIView

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"]

        # Delete all rows after the first 3 (headers) - start from bottom to top
        if sheet.max_row > 3:
            for row_num in range(sheet.max_row, 3, -1):
                sheet.delete_rows(row_num)

        # Delete columns U (21) to AA (27) - start from right to left
        for col_num in range(27, 20, -1):
            sheet.delete_cols(col_num)

        wb.save(response)
        return response

Key Changes Explained #

• Row Deletion: We use sheet.max_row to get the last filled row dynamically (no more hardcoding row numbers). Then we loop from that number down to 4 (since we want to keep rows 1-3), deleting each row. This ensures every row after the headers gets removed properly.
• Column Deletion: We loop from column 27 (AA) down to 21 (U), deleting each column. This avoids index shifting issues that would break the deletion process if we went left to right.

Quick Testing Tip #

After running this, open the downloaded Excel file to verify:

• Only the first 3 header rows remain
• Columns U through AA are completely removed
• No leftover data or styles from the deleted rows/columns

内容的提问来源于stack exchange，提问作者Jekson