插入和删除Excel文件的行和列
介绍
无论是从头开始创建新工作表还是在现有工作表上操作,我们可能需要添加额外的行或列来容纳更多数据。反之,我们可能还需要从工作表中的指定位置删除行或列。 为了满足这些需求,Aspose.Cells提供了一组非常简单的类和方法,下面将讨论其中一些。
管理行和列
Aspose.Cells提供一个类Workbook,代表一个Microsoft Excel文件。Workbook类包含一个Worksheets集合,允许访问Excel文件中的每个工作表。工作表由Worksheet类表示。Worksheet类提供了一个Cells集合,表示工作表中的所有单元格。
Cells集合提供了几种管理工作表中行和列的方法,下面将讨论其中一些。
插入行和列
如何插入行
通过调用Cells集合的InsertRow方法,在工作表中的任何位置插入一行。InsertRow方法接受新行将被插入的行索引。
// 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集合的InsertRows方法。InsertRows方法接受两个参数:
- 行索引,新行将从该行插入。
- 行数,需要插入的总行数。
// 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(); |
如何插入带有格式的行
要插入带有格式选项的行,使用接受InsertOptions参数的InsertRows重载。将InsertOptions类的CopyFormatType属性与CopyFormatType枚举设置。CopyFormatType枚举有以下三个成员。
- SameAsAbove: 格式与上方行相同。
- SameAsBelow: 格式与下方行相同。
- Clear: 清除格式。
// 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集合的InsertColumn方法在工作表的任何位置插入列。InsertColumn方法接受将插入新列的列的索引。
// 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集合的DeleteRows方法。DeleteRows方法接受两个参数:
- 行索引,要删除行的索引。
- 行数,需要删除的总行数。
// 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集合的DeleteColumn方法。DeleteColumn方法接受要删除的列的索引。
// 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(); |