How to get the data filled column index in spreadsheet?
This article explains how to get the data filled column index in a JavaScript spreadsheet. Using the getCell method, you can retrieve the cellModel with values loaded in the spreadsheet. By iterating the cells based on the used range values, you can get the data filled columns in the spreadsheet.
[HTML]
<button className="e-control e-btn" id="btn" role="button"> Get used Columns </button> <div id="spreadsheet"></div>
[TS]
//Initialize Spreadsheet component
let spreadsheet: Spreadsheet = new Spreadsheet({
sheets: [
{
name: 'Car Sales Report',
ranges: [{ dataSource: (dataSource as any).defaultData }],
columns: [
{ width: 180 },
{ width: 130 },
{ width: 130 },
{ width: 180 },
{ width: 130 },
{ width: 120 },
{ width: 130 },
],
},
],
created: (): void => {
//Applies cell and number formatting to specified range of the active sheet
spreadsheet.cellFormat(
{ fontWeight: 'bold', textAlign: 'center', verticalAlign: 'middle' },
'A1:G1'
);
spreadsheet.numberFormat('$#,##0.00', 'F2:F31');
},
});
document.getElementById("btn").onclick = () => {
var sheet = spreadsheet.getActiveSheet();
var lastColumn = sheet.usedRange.colIndex;
var lastRow = sheet.usedRange.rowIndex;
var activeColums = [];
var header_collection = spreadsheet.element.getElementsByClassName('e-header-cell');
for (let i = 0; i <= lastColumn; i++) {
for (let j = 0; j <= lastRow; j++) {
let cell = getCell(j, i, sheet);
if (cell && cell.value) {
activeColums.push(i + ',' + header_collection[i].innerText);
break;
}
}
}
console.log(activeColums);
};
//Render initialized Spreadsheet component
spreadsheet.appendTo('#spreadsheet');
Sample Link: https://stackblitz.com/edit/85bqtd?file=index.ts
Sample Output:
Documentation:
https://ej2.syncfusion.com/documentation/spreadsheet/editing/
https://ej2.syncfusion.com/documentation/api/spreadsheet/#cellformat
https://ej2.syncfusion.com/documentation/api/spreadsheet/#numberformat
Conclusion
We hope you enjoyed learning about how to get the data filled column index in spreadsheet.
You can refer to our JavaScript Spreadsheet page to learn about its other groundbreaking feature representations. You can also explore our JavaScript Spreadsheet Documentation to understand how to manipulate data.
For current customers, you can check out our components from the License and Downloads page. If you are new to Syncfusion, you can try our 30-day free trial to check out our other controls.
If you have any queries or require clarifications, please let us know in the comments section below. You can also contact us through our support forums, BoldDesk Support, or feedback portal. We are always happy to assist you!
