插入和删除Excel文件的行和列

介绍

无论是从头开始创建新工作表还是在现有工作表上操作，我们可能需要添加额外的行或列来容纳更多数据。反之，我们可能还需要从工作表中的指定位置删除行或列。 为了满足这些要求，Aspose.Cells for Python via .NET提供了一套非常简单的类和方法，下面进行了讨论。

管理行和列

Aspose.Cells for Python via .NET提供了一个类Workbook，代表Microsoft Excel文件。Workbook类包含一个worksheets集合，允许访问Excel文件中的每个工作表。工作表由Worksheet类表示。Worksheet类提供了一个cells集合，表示工作表中的所有单元格。

cells集合提供了几种管理工作表中行和列的方法，下面将讨论其中一些。

当添加行或列时，工作表中的内容会向下或向右移动，如果删除行或列，则内容会向上或向左移动。

插入行和列

如何插入行

通过调用cells集合的insert_row方法，在工作表中的任何位置插入一行。insert_row方法接受新行将被插入的行索引。

from aspose.cells import Workbook

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  The path to the documents directory.
dataDir = RunExamples.GetDataDir(".")
#  Creating a file stream containing the Excel file to be opened
fstream = open(dataDir + "book1.xls", "rb")
#  Instantiating a Workbook object
#  Opening the Excel file through the file stream
workbook = Workbook(fstream)
#  Accessing the first worksheet in the Excel file
worksheet = workbook.worksheets[0]
#  Inserting a row into the worksheet at 3rd position
worksheet.cells.insert_row(2)
#  Saving the modified Excel file
workbook.save(dataDir + "output.out.xls")
#  Closing the file stream to free all resources
fstream.close()

如何插入多行

要向工作表中插入多行，调用cells集合的insert_rows方法。insert_rows方法接受两个参数：

• 行索引，新行将从该行插入。
• 行数，需要插入的总行数。

from aspose.cells import Workbook

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  The path to the documents directory.
dataDir = RunExamples.GetDataDir(".")
#  Creating a file stream containing the Excel file to be opened
fstream = open(dataDir + "book1.xls", "rb")
#  Instantiating a Workbook object
#  Opening the Excel file through the file stream
workbook = Workbook(fstream)
#  Accessing the first worksheet in the Excel file
worksheet = workbook.worksheets[0]
#  Inserting 10 rows into the worksheet starting from 3rd row
worksheet.cells.insert_rows(2, 10)
#  Saving the modified Excel file
workbook.save(dataDir + "output.out.xls")
#  Closing the file stream to free all resources
fstream.close()

如何插入带有格式的行

要插入带有格式选项的行，使用接受InsertOptions参数的insert_rows重载。将InsertOptions类的copy_format_type属性与CopyFormatType枚举设置。CopyFormatType枚举有以下三个成员。

• SAME_AS_ABOVE: 与上一行格式相同。
• SAME_AS_BELOW: 与下一行格式相同。
• CLEAR: 清除格式。

from aspose.cells import CopyFormatType, InsertOptions, Workbook

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  The path to the documents directory.
dataDir = RunExamples.GetDataDir(".")
#  Creating a file stream containing the Excel file to be opened
fstream = open(dataDir + "book1.xls", "rb")
#  Instantiating a Workbook object
#  Opening the Excel file through the file stream
workbook = Workbook(fstream)
#  Accessing the first worksheet in the Excel file
worksheet = workbook.worksheets[0]
#  Setting Formatting options
insertOptions = InsertOptions()
insertOptions.copy_format_type = CopyFormatType.SAME_AS_ABOVE
#  Inserting a row into the worksheet at 3rd position
worksheet.cells.insert_rows(2, 1, insertOptions)
#  Saving the modified Excel file
workbook.save(dataDir + "InsertingARowWithFormatting.out.xls")
#  Closing the file stream to free all resources
fstream.close()

如何插入列

开发人员还可以通过调用cells集合的insert_column方法在工作表的任何位置插入列。insert_column方法接受将插入新列的列的索引。

from aspose.cells import Workbook

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  The path to the documents directory.
dataDir = RunExamples.GetDataDir(".")
#  Creating a file stream containing the Excel file to be opened
fstream = open(dataDir + "book1.xls", "rb")
#  Instantiating a Workbook object
#  Opening the Excel file through the file stream
workbook = Workbook(fstream)
#  Accessing the first worksheet in the Excel file
worksheet = workbook.worksheets[0]
#  Inserting a column into the worksheet at 2nd position
worksheet.cells.insert_column(1)
#  Saving the modified Excel file
workbook.save(dataDir + "output.out.xls")
#  Closing the file stream to free all resources
fstream.close()

删除行和列

如何删除多行

要从工作表删除多行，请调用cells集合的delete_rows方法。delete_rows方法接受两个参数：

• 行索引，要删除行的索引。
• 行数，需要删除的总行数。

from aspose.cells import Workbook

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  The path to the documents directory.
dataDir = RunExamples.GetDataDir(".")
#  Creating a file stream containing the Excel file to be opened
fstream = open(dataDir + "Book1.xlsx", "wb")
#  Instantiating a Workbook object
#  Opening the Excel file through the file stream
workbook = Workbook(fstream)
#  Accessing the first worksheet in the Excel file
worksheet = workbook.worksheets[0]
#  Deleting 10 rows from the worksheet starting from 3rd row
worksheet.cells.delete_rows(2, 10)
#  Saving the modified Excel file
workbook.save(dataDir + "output.xlsx")
#  Closing the file stream to free all resources
fstream.close()

如何删除列

要从工作表的任何位置删除列，请调用Cells集合的delete_column方法。delete_column方法接受要删除的列的索引。

from aspose.cells import Workbook

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  The path to the documents directory.
dataDir = RunExamples.GetDataDir(".")
#  Creating a file stream containing the Excel file to be opened
fstream = open(dataDir + "Book1.xlsx", "rb")
#  Instantiating a Workbook object
#  Opening the Excel file through the file stream
workbook = Workbook(fstream)
#  Accessing the first worksheet in the Excel file
worksheet = workbook.worksheets[0]
#  Deleting a column from the worksheet at 5th position
worksheet.cells.delete_column(4)
#  Saving the modified Excel file
workbook.save(dataDir + "output.xlsx")
#  Closing the file stream to free all resources
fstream.close()