Convert an Excel Table to a Range of Data

Using Microsoft Excel

Use the Convert to Range feature to quickly convert a table to a range without losing formatting. In Microsoft Excel 2007/2010:

  1. Click anywhere in the table to make sure that the active cell is in a table column.

todo:image_alt_text

  1. On the Design tab, in the Tools group, click Convert to Range.

todo:image_alt_text

Using Aspose.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(ConvertTableToRange.class) + "tables/";
// Open an existing file that contains a table/list object in it
Workbook wb = new Workbook(dataDir + "book1.xlsx");
// Convert the first table/list object (from the first worksheet) to normal range
wb.getWorksheets().get(0).getListObjects().get(0).convertToRange();
// Save the file
wb.save(dataDir + "ConvertTableToRange_out.xlsx");

Convert Table to Range with Options

Aspose.Cells provides additional options while converting Table to Range through the TableToRangeOptions class. The TableToRangeOptions class provides LastRow property which allows you to set the last index of the table row. The table formatting will be retained up to the specified row index and the rest of the formatting will be removed.

The sample code given below demonstrates the use of TableToRangeOptions class.

// 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(ConvertTableToRangeWithOptions.class) + "Tables/";
// Open an existing file that contains a table/list object in it
Workbook workbook = new Workbook(dataDir + "book1.xlsx");
TableToRangeOptions options = new TableToRangeOptions();
options.setLastRow(5);
// Convert the first table/list object (from the first worksheet) to normal range
workbook.getWorksheets().get(0).getListObjects().get(0).convertToRange(options);
// Save the file
workbook.save(dataDir + "ConvertTableToRangeWithOptions_out.xlsx");