插入和删除行和列

介绍

无论是从头开始创建新工作表还是在现有工作表上操作，我们可能需要添加额外的行或列来容纳更多数据。反之，我们可能还需要从工作表中的指定位置删除行或列。

为了满足这些需求，Aspose.Cells提供了一组非常简单的类和方法，下面将讨论其中一些。

如何管理行/列

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

Cells集合提供了几种用于在工作表中管理行和列的方法。其中一些方法如下所述。

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

如何插入行

通过调用 Cells 方法在任意位置插入一行。该方法的第一个参数为插入位置的索引，第二个参数为要插入的行数。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
String dataDir = Utils.getSharedDataDir(InsertingARow.class) + "rows_cloumns/";
// Instantiating a Workbook object
Workbook workbook = new Workbook(dataDir + "book1.xls");

// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.getWorksheets().get(0);

// Inserting a row into the worksheet at 3rd position
worksheet.getCells().insertRows(2, 1);

// Saving the modified Excel file in default (that is Excel 2000) format
workbook.save(dataDir + "InsertingARow_out.xls");

如何插入多行

要在工作表中插入多行，请调用 Cells 方法。该方法接受两个参数：

• 行索引：新行将插入的行的索引。
• 行数：需要插入的总行数。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
String dataDir = Utils.getSharedDataDir(InsertingMultipleRows.class) + "rows_cloumns/";
// Instantiating a Workbook object
Workbook workbook = new Workbook(dataDir + "book1.xls");

// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.getWorksheets().get(0);

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

// Saving the modified Excel file in default (that is Excel 2000) format
workbook.save(dataDir + "InsertingMultipleRows_out.xls");

如何插入带有格式的行

要插入带格式的行，请使用带有 InsertOptions 参数的 insertRows 重载方法。可以通过设置 CopyFormatType 属性来指定格式复制类型。该枚举有以下三种成员：

• SAME_AS_ABOVE： 格式与上方行相同。
• SAME_AS_BELOW： 格式与下方行相同。
• 清除: 清除格式。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
String dataDir = Utils.getSharedDataDir(InsertingARowWithFormatting.class) + "RowsAndColumns/";
// Instantiating a Workbook object
Workbook workbook = new Workbook(dataDir + "Book1.xlsx");

// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.getWorksheets().get(0);

// Setting Formatting options
InsertOptions insertOptions = new InsertOptions();
insertOptions.setCopyFormatType(CopyFormatType.SAME_AS_ABOVE);
      
// Inserting a row into the worksheet at 3rd position
worksheet.getCells().insertRows(2, 1, insertOptions);

// Saving the modified Excel file
workbook.save(dataDir + "InsertingARowWithFormatting_out.xlsx");

如何删除行

要在任意位置删除一行，调用 Cells 方法。该方法接受两个参数：

• 行索引：要删除行的起始行的索引。
• 行数：需要删除的总行数。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(DeleteARow.class) + "rows_cloumns/";
// Instantiating a Workbook object
Workbook workbook = new Workbook(dataDir + "book1.xls");

// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.getWorksheets().get(0);

// Deleting 3rd row from the worksheet
worksheet.getCells().deleteRows(2, 1, true);

// Saving the modified Excel file in default (that is Excel 2000) format
workbook.save(dataDir + "DeleteARow_out.xls");

如何删除多行

要删除工作表中的多行，可以调用 Cells 方法。该方法接受两个参数：

• 行索引：要删除行的起始行的索引。
• 行数：需要删除的总行数。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(DeleteMultipleRows.class) + "rows_cloumns/";
// Instantiating a Workbook object
Workbook workbook = new Workbook(dataDir + "Book1.xlsx");

// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.getWorksheets().get(0);

// Deleting 10 rows from the worksheet starting from 3rd row
worksheet.getCells().deleteRows(2, 10, true);

// Saving the modified Excel file in default (that is Excel 2000) format
workbook.save(dataDir + "DeleteMultipleRows_out.xls");

如何插入一个或多个列

开发者还可以通过调用 Cells 方法在任意位置插入列。该方法接受两个参数：

• 列索引，需要插入列的索引
• 列数，需要插入的总列数。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
String dataDir = Utils.getSharedDataDir(InsertingAColumn.class) + "rows_cloumns/";
// Instantiating a Workbook object
Workbook workbook = new Workbook(dataDir + "book1.xls");

// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.getWorksheets().get(0);

// Inserting a column into the worksheet at 2nd position
worksheet.getCells().insertColumns(1, 1);

// Saving the modified Excel file in default (that is Excel 2000) format
workbook.save(dataDir + "InsertingAColumn_out.xls");

如何删除列

要在工作表中删除任意列，调用 Cells 方法。该方法接受以下参数：

• 列索引：要删除列的起始列的索引。
• 列数：需要删除的总列数。
• 更新引用：布尔参数，指示是否在其他工作表中更新引用。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(DeleteAColumn.class) + "rows_cloumns/";
// Instantiating a Workbook object
Workbook workbook = new Workbook(dataDir + "Book1.xlsx");

// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.getWorksheets().get(0);

// Deleting a column from the worksheet at 2nd position
worksheet.getCells().deleteColumns(1, 1, true);

// Saving the modified Excel file in default (that is Excel 2000) format
workbook.save(dataDir + "DeleteAColumn_out.xls");