从现有工作簿表中删除行和列 [英] Delete rows and cols from exist workbook sheet

问题描述

我正在尝试从 sheet 中删除所有填充行，除了前三列和所有从坐标(U-AA)开始的列.除了最后的标题外，我必须删除所有填写的信息.如何删除我不了解的列，但是当我运行脚本删除行时，只剩下信息了，只删除了样式.如何正确执行此类操作?

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

推荐答案

要删除行和列，只需使用:

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

请在 openpyxl文档中详细了解.请注意，列用整数表示，因此 A == 1，B == 2 依此类推.

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 culomn 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)

这篇关于从现有工作簿表中删除行和列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！