Tables and Ranges with JavaScript via C++
Contents
[
Hide
]
Introduction
Sometimes you create a table in Microsoft Excel and do not want to keep working with the table functionality that it comes with. Instead, you want something that looks like a table. To keep data in a table without losing formatting, convert the table to a regular range of data.
Aspose.Cells does support this feature of Microsoft Excel for tables and list objects.
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:
- Click anywhere in the table to make sure that the active cell is in a table column.
- On the Design tab, in the Tools group, click Convert to Range.
Using Aspose.Cells
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example - Convert Table to Range</title>
</head>
<body>
<h1>Convert Table to Range Example</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
const fileInput = document.getElementById('fileInput');
if (!fileInput.files.length) {
document.getElementById('result').innerHTML = '<p style="color: red;">Please select an Excel file.</p>';
return;
}
const file = fileInput.files[0];
const arrayBuffer = await file.arrayBuffer();
// Instantiate workbook from uploaded file
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Access the first worksheet and convert the first table/list object to a normal range
const worksheet = workbook.worksheets.get(0);
const listObject = worksheet.listObjects.get(0);
listObject.convertToRange();
// Save the modified workbook and provide a download link
const outputData = workbook.save(SaveFormat.Xlsx);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'output.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Modified Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Table converted to range successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
The table features are no longer available after the table has been converted to a range. For example, row headers no longer include the sort and filter arrows, and structured references (references that use table names) that were used in formulas turn into regular cell references.
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.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Table to Range Example</title>
</head>
<body>
<h1>Convert Table to Range Example</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat, TableToRangeOptions, Utils } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
const fileInput = document.getElementById('fileInput');
const resultDiv = document.getElementById('result');
const downloadLink = document.getElementById('downloadLink');
if (!fileInput.files.length) {
resultDiv.innerHTML = '<p style="color: red;">Please select an Excel file.</p>';
return;
}
const file = fileInput.files[0];
const arrayBuffer = await file.arrayBuffer();
// Instantiating a Workbook object from the uploaded file
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Create TableToRangeOptions and set lastRow property
const options = new TableToRangeOptions();
options.lastRow = 5;
// Convert the first table/list object (from the first worksheet) to normal range
workbook.worksheets.get(0).listObjects.get(0).convertToRange(options);
// Saving the modified Excel file
const outputData = workbook.save(SaveFormat.Xlsx);
const blob = new Blob([outputData]);
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'output.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Modified Excel File';
resultDiv.innerHTML = '<p style="color: green;">Table converted to range successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>