I need to be able to generate an arbitrarily large Excel file, possible tens of thousands of rows and 100MB in size. This is crashing the process since the Workbook object wants to load all of this in memory at once. I need a DataReader like approach, where I can write to a stream rather than hold the entire doc in memory.
It looks like LightCellsDataProvider solves this, but there’s absolutely no examples of how to use it for .NET anywhere, and no documentation. My questions are:
1) Is LightCellsDataProvider the correct approach?
2) Does it exist in the .NET version of the component?
3) Can you point me to documentation, - or -
4) Better yet, some comprehensive sample code?
Thanks!
Andrew
Hi,
Yes, .Net version has supported LightCells APIs and we think it can fit your requirement. For the sample code, please see the demo codes(\samples\consoleDemo\Northwind\src\com\aspose\cells\demos\lightcells ) in our official release such as V7.2.0.
And another techinical tip for memory performance issue, if you are saving XLS files, please use SaveOptions.CachedFileFolder to specify a temporary place for saving temporary data generated in saving process. Otherwise lots of memory will be needed even using LightCells when the workbook to be created has large amount of cells and data, especially for XLS file because of its file structure.
I can’t find this demo - where is it?
Hi,
Please first download:
Aspose.Cells for Java 7.2.0
Then you will find the demo in the zip archive at the following location:
aspose-cells-7.2.0-java.zip\samples\consoleDemo\Northwind\src\com\aspose\cells\demos\lightcells
I have also attached the screenshot for your help.
Screenshot:
I’m using C#.NET - is there no demo for that?
Hi,
I have attached the light cells demo in C#.
You can also download the output file generated by the demo code.
Here is a code. I have also attached the code.
C#
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Aspose.Cells;
namespace LightOoxmlTest
{
class Program
{
static void Main(string[] args)
{
var workbook = new Workbook();
var ooxmlSaveOptions = new OoxmlSaveOptions();
ooxmlSaveOptions.LightCellsDataProvider = new TestDataProvider(workbook);
workbook.Save(“output.xlsx”, ooxmlSaveOptions);
}
}
class TestDataProvider : LightCellsDataProvider
{
private int _row = -1;
private int _column = -1;
private Workbook _workbook;
public TestDataProvider(Workbook workbook)
{
_workbook = workbook;
}
#region LightCellsDataProvider Members
public bool IsGatherString()
{
return false;
}
//Just generating a dummy 1000x30 matrix.
public int NextCell()
{
++_column;
if (_column < 30)
return _column;
else
{
_column = -1;
return -1;
}
}
public int NextRow()
{
++_row;
if (_row < 1000)
{
_column = -1;
return _row;
}
else
return -1;
}
public void StartCell(Cell cell)
{
cell.PutValue(_row + _column);
if (_row == 1)
{
}
else
{
cell.Formula = “=Rand() + A2”;
}
}
public void StartRow(Row row)
{
}
public bool StartSheet(int sheetIndex)
{
if (sheetIndex == 0)
{
return true;
}
else
return false;
}
#endregion
}
}
Please Suggest a workaround for MaxDatarow and MaxDataColumn values instead of fixed row and column count of 1000 and 30..
Thanks in Advance..
Hi,
Please see the modified code below (for LightCells APIs) for your requirements. It will create your desired matrix as you specify the matrix boundaries in the calling code (by passing it) to the interface.
Sample code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Aspose.Cells;
namespace LightOoxmlTest
{
class Program
{
static void Main(string[] args)
{
//Specify your desired matrix.
int rows = 2000;
int cols = 26;
var workbook = new Workbook();
var ooxmlSaveOptions = new OoxmlSaveOptions();
ooxmlSaveOptions.LightCellsDataProvider = new TestDataProvider(workbook, rows, cols);
workbook.Save(“e:\test2\LightCellsBook_testoutput.xlsx”, ooxmlSaveOptions);
}
}
class TestDataProvider : LightCellsDataProvider
{
private int _row;
private int _column;
private int maxrows;
private int maxcols;
private Workbook _workbook;
public TestDataProvider(Workbook workbook, int rowcount, int colcount)
{
_workbook = workbook;
maxrows = rowcount;
maxcols = colcount;
}
#region LightCellsDataProvider Members
public bool IsGatherString()
{
return false;
}
public int NextCell()
{
++_column;
if (_column < maxcols)
return _column;
else
{
_column = -1;
return -1;
}
}
public int NextRow()
{
++_row;
if (_row < maxrows)
{
_column = -1;
return _row;
}
else
return -1;
}
public void StartCell(Cell cell)
{
cell.PutValue(_row + _column);
if (_row == 1)
{
}
else
{
cell.Formula = “=Rand() + A2”;
}
}
public void StartRow(Row row)
{
}
public bool StartSheet(int sheetIndex)
{
if (sheetIndex == 0)
{
return true;
}
else
return false;
}
#endregion
}
}
Thank you.