Using LightCells API with JavaScript via C++
Event‑Driven Architecture
Aspose.Cells provides the LightCells API, mainly designed to manipulate cell data one by one without building a complete data model block (using the Cell collection etc.) into memory. It works in an event‑driven mode.
To save workbooks, provide the cell content cell by cell when saving, and the component saves it to the output file directly.
When reading template files, the component parses every cell and provides its value one by one.
In both procedures, one Cell object is processed and then discarded; the Workbook object does not hold the collection. In this mode, therefore, memory is saved when importing and exporting Microsoft Excel files that have a large data set which would otherwise use a lot of memory.
Even though the LightCells API processes the cells in the same way for XLSX and XLS files (it does not actually load all cells in memory but processes one cell and then discards it), it saves memory more effectively for XLSX files than XLS files because of the different data models and structures of the two formats.
However, for XLS files, to save more memory, developers can specify a temporary location for saving temporary data generated during the Save process. Commonly, using the LightCells API to save XLSX files may save 50 % or more memory than using the conventional method; saving XLS may save about 20‑40 % memory.
Writing a Large Excel File
Aspose.Cells provides an interface, LightCellsDataProvider, that needs to be implemented in your program. The interface represents the data provider for saving large spreadsheet files in a lightweight mode.
When saving a workbook in this mode, StartSheet(int) is checked for every worksheet in the workbook. For a sheet, if StartSheet(int) returns true, then all the data and properties of rows and cells of this sheet to be saved are supplied by this implementation. First, NextRow() is called to obtain the next row index to be saved. If a valid row index is returned (the row index must be in ascending order for the rows to be saved), then a Row object representing this row is provided to the implementation so that its properties can be set via StartRow(Row).
For a row, NextCell() is checked first. If a valid column index is returned (the column index must be in ascending order for all cells of one row to be saved), then a Cell object representing that cell is provided to the implementation so that its data and properties can be set via StartCell(Cell). After the cell’s data is set, the cell is saved directly to the generated spreadsheet file and the next cell is checked and processed.
Writing a Large Excel File: Example
Please see the following sample code to see the working of the LightCells API. Add, remove, or update the code segments according to your needs.
The program creates a huge file with 10,000 (10000 × 30 matrix) records in a worksheet and fills them with dummy data. You can specify your own matrix by changing the rowsCount and colsCount variables in the script.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Aspose.Cells 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, OoxmlSaveOptions } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
class TestDataProvider {
constructor(workbook, maxRows, maxColumns) {
this._workbook = workbook;
this.maxRows = maxRows;
this.maxColumns = maxColumns;
this._row = -1;
this._column = -1;
}
isGatherString() {
return false;
}
nextCell() {
this._column++;
if (this._column < this.maxColumns) {
return this._column;
} else {
this._column = -1;
return -1;
}
}
nextRow() {
this._row++;
if (this._row < this.maxRows) {
this._column = -1;
return this._row;
} else {
return -1;
}
}
startCell(cell) {
cell.value = this._row + this._column;
if (this._row !== 1) {
cell.formula = "=Rand() + A2";
}
}
startRow(row) {
}
startSheet(sheetIndex) {
return sheetIndex === 0;
}
}
document.getElementById('runExample').addEventListener('click', async () => {
const fileInput = document.getElementById('fileInput');
// The example does not require an input file; file input is optional.
const rowsCount = 10000;
const colsCount = 30;
const workbook = new Workbook();
const ooxmlSaveOptions = new OoxmlSaveOptions();
ooxmlSaveOptions.lightCellsDataProvider = new TestDataProvider(workbook, rowsCount, colsCount);
const outputData = workbook.save(SaveFormat.Xlsx, ooxmlSaveOptions);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'output.out.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Operation completed successfully! Click the download link to get the generated file.</p>';
});
</script>
</html>
Reading Large Excel Files
Aspose.Cells provides an interface, LightCellsDataHandler, that needs to be implemented in your program. The interface represents the data handler for reading large spreadsheet files in a lightweight mode.
When reading a workbook in this mode, StartSheet is checked for each worksheet in the workbook. For a sheet, if StartSheet returns true, then all the data and properties of the cells in rows and columns of the sheet are examined and processed by the implementation of this interface. For each row, StartRow is called to determine whether it needs to be processed. If a row needs processing, its properties are read first, and the developer can access them with ProcessRow. If the row’s cells also need to be processed, ProcessRow should return true, after which StartCell is called for every existing cell in the row to determine whether a particular cell should be processed. If a cell needs processing, ProcessCell is called to handle the cell in the implementation of this interface.
Reading Large Excel Files: Example
Please see the following sample code to see the working of the LightCells API. Add, remove, or update the code segments according to your needs.
The program reads a huge file with millions of records in a worksheet. It takes a short time to read each sheet in the workbook. The sample code reads the file and retrieves the total number of cells, the string count, and the formula count in each worksheet.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>LightCells Data Handler 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, LoadOptions, CellValueType, Utils } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
class LightCellsDataHandlerVisitCells {
constructor() {
this.cellCount = 0;
this.formulaCount = 0;
this.stringCount = 0;
}
get CellCount() {
return this.cellCount;
}
get FormulaCount() {
return this.formulaCount;
}
get StringCount() {
return this.stringCount;
}
StartSheet(sheet) {
console.log("Processing sheet[" + sheet.name + "]");
return true;
}
StartRow(rowIndex) {
return true;
}
ProcessRow(row) {
return true;
}
StartCell(column) {
return true;
}
ProcessCell(cell) {
this.cellCount++;
if (cell.isFormula()) {
this.formulaCount++;
} else if (cell.type === CellValueType.IsString) {
this.stringCount++;
}
return false;
}
}
document.getElementById('runExample').addEventListener('click', async () => {
const fileInput = document.getElementById('fileInput');
const resultDiv = document.getElementById('result');
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();
const opts = new LoadOptions();
const v = new LightCellsDataHandlerVisitCells();
opts.lightCellsDataHandler = v;
const wb = new Workbook(new Uint8Array(arrayBuffer), opts);
const sheetCount = wb.worksheets.count;
resultDiv.innerHTML = '<p style="color: green;">Total sheets: ' + sheetCount + ', cells: ' + v.CellCount
+ ', strings: ' + v.StringCount + ', formulas: ' + v.FormulaCount + '</p>';
});
</script>
</html>