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

介绍

无论是从头开始创建新工作表还是处理现有工作表，我们都可能需要添加额外的行或列以容纳更多数据。反之，我们可能还需要删除工作表中指定位置的行或列。 为满足这些要求，Aspose.Cells 提供了一组非常简单的类和方法，如下所述。

管理行和列

Aspose.Cells提供类工作簿，代表一个 Microsoft Excel 文件。这工作簿类包含一个工作表允许访问 Excel 文件中每个工作表的集合。工作表由工作表班级。这工作表类提供了Cells代表工作表中所有单元格的集合。

这Cellscollection 提供了多种方法来管理工作表中的行和列。下面讨论其中一些。

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

插入行和列

插入一行

通过调用插入行的方法Cells收藏。这插入行方法采用将插入新行的行的索引。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Creating a file stream containing the Excel file to be opened
FileStream fstream = new FileStream(dataDir + "book1.xls", FileMode.Open);

// Instantiating a Workbook object
// Opening the Excel file through the file stream
Workbook workbook = new Workbook(fstream);

// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];

// Inserting a row into the worksheet at 3rd position
worksheet.Cells.InsertRow(2);

// Saving the modified Excel file
workbook.Save(dataDir + "output.out.xls");

// Closing the file stream to free all resources
fstream.Close();

插入多行

要将多行插入到工作表中，请调用插入行的方法Cells收藏。这插入行方法有两个参数：

• 行索引，将插入新行的行的索引。
• Number of rows，需要插入的总行数。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Creating a file stream containing the Excel file to be opened
FileStream fstream = new FileStream(dataDir + "book1.xls", FileMode.Open);

// Instantiating a Workbook object
// Opening the Excel file through the file stream
Workbook workbook = new Workbook(fstream);

// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];

// Inserting 10 rows into the worksheet starting from 3rd row
worksheet.Cells.InsertRows(2, 10);


// Saving the modified Excel file
workbook.Save(dataDir + "output.out.xls");

// Closing the file stream to free all resources
fstream.Close();

插入带格式的行

要插入带有格式选项的行，请使用插入行超载需要插入选项作为参数。设置复制格式类型的财产插入选项类复制格式类型枚举。这复制格式类型枚举具有三个成员，如下所列。

• SameAsAbove：格式化与上一行相同的行。
• SameAsBelow：格式化与下一行相同的行。
• 清除：清除格式。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Creating a file stream containing the Excel file to be opened
FileStream fstream = new FileStream(dataDir + "book1.xls", FileMode.Open);

// Instantiating a Workbook object
// Opening the Excel file through the file stream
Workbook workbook = new Workbook(fstream);

// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];

// Setting Formatting options
InsertOptions insertOptions = new InsertOptions();
insertOptions.CopyFormatType = CopyFormatType.SameAsAbove;

// Inserting a row into the worksheet at 3rd position
worksheet.Cells.InsertRows(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收藏。这插入列方法采用将插入新列的列的索引。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Creating a file stream containing the Excel file to be opened
FileStream fstream = new FileStream(dataDir + "book1.xls", FileMode.Open);

// Instantiating a Workbook object
// Opening the Excel file through the file stream
Workbook workbook = new Workbook(fstream);

// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];

// Inserting a column into the worksheet at 2nd position
worksheet.Cells.InsertColumn(1);

// Saving the modified Excel file
workbook.Save(dataDir + "output.out.xls");

// Closing the file stream to free all resources
fstream.Close();

删除行和列

删除多行

要从工作表中删除多行，请调用删除行的方法Cells收藏。这删除行方法有两个参数：

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

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Creating a file stream containing the Excel file to be opened
FileStream fstream = new FileStream(dataDir + "Book1.xlsx", FileMode.OpenOrCreate);

// Instantiating a Workbook object
// Opening the Excel file through the file stream
Workbook workbook = new Workbook(fstream);

// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];

// Deleting 10 rows from the worksheet starting from 3rd row
worksheet.Cells.DeleteRows(2, 10);

// Saving the modified Excel file
workbook.Save(dataDir + "output.xlsx");

// Closing the file stream to free all resources
fstream.Close();

删除列

要从工作表的任何位置删除列，请调用删除列的方法Cells收藏。这删除列方法采用要删除的列的索引。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Creating a file stream containing the Excel file to be opened
FileStream fstream = new FileStream(dataDir + "Book1.xlsx", FileMode.Open);

// Instantiating a Workbook object
// Opening the Excel file through the file stream
Workbook workbook = new Workbook(fstream);

// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];

// Deleting a column from the worksheet at 5th position
worksheet.Cells.DeleteColumn(4);

// Saving the modified Excel file
workbook.Save(dataDir + "output.xlsx");

// Closing the file stream to free all resources
fstream.Close();