Hi Team,
We came across a issue related to on previous ticket (Issue ID: CELLSNET-54534):
- We are using Aspose.Cells . NET version 25.7 with the FileCache Memory setting enabled for files larger than 150MB (as previously recommended).
- Despite these settings, we are encountering significant performance issues when appending data, even with smaller files. We are not getting any timeout issue but performance is significantly affected.
Our specific use case:
- We need to append new records to the very top (start) of an Excel worksheet.
- If there are 10 records at the top, and we want to append 10 more, we shift the existing 10 records down and insert the new 10 records at the top.
- This logic is applied regardless of the number of existing or new records, meaning every append requires shifting a large block of rows downward.
Performance examples:
- Appending 100,000 records to a file with 100,000 existing records (4MB) takes about 4 minutes.
- Appending 100,000 records to a file with 200,000 existing records (7.7MB) takes 14 minutes.
- Appending 100,000 records to a file with 500,000 existing records (26MB) takes over 20 minutes and does not complete.
Our current implementation uses:
- Cells.InsertRow(rowIndex) to insert new rows at the top and shift existing records.
- Cells[row, col]. PutValue(value, isConverted) to write values to each cell, all performed within nested loops.
We suspect this approach, involving repeated row insertions and cell updates in loops, is the main performance bottleneck as data volumes grow.
Request:
- Are there more efficient, bulk-oriented methods for appending large datasets specifically at the top of a worksheet in Aspose.Cells?
- Is there a recommended best practice or alternative pattern for this scenario to improve performance and scalability?
Thank you for your guidance and support.
@Chvenkatakalyan
Summary
You are seeing slow performance because inserting rows and writing cells one‑by‑one forces the worksheet to be re‑indexed and the internal engine to be invoked many times. The file‑cache setting helps with memory usage but does not eliminate the overhead of those repeated operations.
Why it happens
Worksheet.Cells.InsertRow (or a loop that calls it) adds rows individually, causing the sheet structure to be rebuilt for each call.Cell.PutValue is also executed per cell, which is expensive when performed hundreds of thousands of times.
What to try next
- Look for bulk‑row insertion APIs (e.g., an
InsertRows method that can add a block of rows in a single call) and use them instead of looping over InsertRow.
- Use bulk‑data import facilities such as
ImportDataTable, ImportArray, or similar methods that can write a whole range of values at once rather than calling PutValue for each cell.
- If the library provides a way to shift a range of existing rows in one operation (e.g., a
CopyRows or MoveRows method), use that instead of repeatedly inserting rows at the top.
- Keep the memory‑optimisation settings (file‑cache, streaming options) enabled for large workbooks, as they still help with overall resource usage.
- Review the Aspose.Cells .NET documentation and any performance‑related KB articles for the version you are using, and consider opening a support ticket with a minimal reproducible example if the bulk APIs do not resolve the slowdown.
Action checklist
- Replace any loop that calls
Cells.InsertRow with a single bulk‑row insertion call, if available.
- Replace nested
Cells[row, col].PutValue(...) loops with a bulk import method (DataTable, array, etc.).
- Use a single‑call row‑shifting operation instead of repeated inserts, when possible.
- Verify that your file‑cache and other memory‑optimisation settings remain enabled.
- Test the updated implementation with a representative data size and compare the execution time.
@Chvenkatakalyan
The MemoryPreference and FileCache mode are deigned mainly for users to access cells data sequentially with lower memory cost. Because cells data is maintained in compact format, modifying the data or accessing cells randomly may result in significantly poorer performance when comparing to the Normal mode.
For your situation, if there are many worksheets, you may set the mode as Normal for the one which you will modify. After all data in the worksheet has been set and not any more modification is required, you may set the memory mode of this sheet to FileCache. And then process next worksheet in the same way.
If there is only one worksheet in your workbook, please try to avoid the operation of inserting rows/cells into existing data set because such kind of operation casues all existing data be shifted and moved. We think you may prepare the inserted data in one new worksheet, after all data has been filled, copy the original data in original sheet to the new sheet(append to the tail of the new worksheet). Such kind of operation(without inserting) may help to improve the performance.