插入和删除行和列
介绍
无论是从头开始创建新工作表还是在现有工作表上操作,我们可能需要添加额外的行或列来容纳更多数据。反之,我们可能还需要从工作表中的指定位置删除行或列。
为了满足这些需求,Aspose.Cells提供了一组非常简单的类和方法,下面将讨论其中一些。
如何管理行/列
Aspose.Cells提供了一个Workbook类,表示Microsoft Excel文件。Workbook类包含一个WorksheetCollection,允许访问Excel文件中的每个工作表。工作表由Worksheet类表示。Worksheet类提供了一个表示工作表中所有单元格的Cells集合。
Cells集合提供了几种用于在工作表中管理行和列的方法。其中一些方法如下所述。
如何插入行
通过调用Cells集合的insertRows方法在任何位置插入一行。insertRows方法将插入新行的行索引作为第一个参数,需要插入的行数作为第二个参数。
// 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集合的insertRows方法。insertRows方法接受两个参数:
- 行索引:新行将插入的行的索引。
- 行数:需要插入的总行数。
// 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重载。设置InsertOptions类的CopyFormatType属性。 CopyFormatType枚举有三个成员,如下所列。
// 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"); |
如何删除行
要在任何位置删除行,请调用deleteRows方法的Cells集合。 deleteRows方法需要两个参数:
- 行索引:要删除行的起始行的索引。
- 行数:需要删除的总行数。
// 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"); |
如何删除多行
要从工作表中删除多行,请调用deleteRows方法的Cells集合。 deleteRows方法需要两个参数:
- 行索引:要删除行的起始行的索引。
- 行数:需要删除的总行数。
// 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"); |
如何插入一个或多个列
开发人员还可以通过调用insertColumns方法向工作表的任何位置插入列至集合Cells。 insertColumns方法需要两个参数:
- 列索引,需要插入列的索引
- 列数,需要插入的总列数。
// 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"); |
如何删除列
要在工作表的任何位置删除列,请调用deleteColumns方法的Cells集合。 deleteColumns方法需要以下参数:
- 列索引:要删除列的起始列的索引。
- 列数:需要删除的总列数。
- 更新引用:布尔参数,指示是否在其他工作表中更新引用。
// 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"); |