MaxDisplayRange Error

Aspose.Cells Product Family
1

I’m getting following compilation error while merging 2 worksheets


Error 43 ‘Aspose.Cells.Cells’ does not contain a definition for ‘MaxDisplayRange’ and no extension method ‘MaxDisplayRange’ accepting a first argument of type ‘Aspose.Cells.Cells’ could be found (are you missing a using directive or an assembly reference?) C:\clients\slnSPMReportApplication\SPMReportApp\Export2007New.cs 735 68 SPMReportApp
2

Here are our initial issue. We have 350K rows with 100 Columns in it. With this we are getting System.OutOfMemeory excepetion. We feel that this is issue/limitation with Aspose dll as till 250K everything worked fine. Now to get the solution we are going to add 250K records in one Worksheet and 250K in another sheet and at the end we will merge both sheets into one. For that I am trying this example

And now getting error definition for ‘MaxDisplayRange’ and no extension

Please let us know if above solution will work for 500K+ records? or we will face any other issue?

Thanks for your reply.

3

Hi,


Please download and try our latest fix/version: Aspose.Cells for .NET v7.4.3.5 and let us know your feedback.

The product does contain the Cells.MaxDisplayRange attribute that returns the Range object.

Regarding your out of memory issue, there is no limitation for a sheet to fill data, it totally depends upon two things.

1) Your RAM and CPU – If you got good amount of RAM with higher configured processor (e.g x64 bit), you may accomplish the task easily and may input huge list of data into a single sheet. Sure, 350K rows * 100 cols are lots of data, so you got to have sufficient RAM for it.

2) You should use XLSX file format instead of XLS (if you are already using it).

By the way, we have LightCells API which is specifically used to insert long list of data and to create large Excel files dynamically, see the topic if you could use it:
http://www.aspose.com/docs/display/cellsnet/Using+LightCells+API


Thank you.

4

Amjad,


Thank you very much for your reply.

Regarding Out of Memery Issue.
1. RAM and CPU, Please see below configuration of our server where we are creating a report.
Intel Xeon E5345 @2.33GHz 32GM RAM, Windows Server 2003 R2 Enterprise Edition Service Pack 2.

2. We are using XLSX format.


Regarding Cells.MaxDisplayRange, I am getting error please see the attached screen shot.

Please suggest ASAP
5

Hi,


Please try our latest fix/version: v7.4.3.5 which shared the link in my previous post.

1) Please create a sample console application, zip it and post it here to reproduce the issue on our end. We will check it soon.

3) I suspect you might be using some older version which does not have MaxDisplayRange attribute. Please use our latest version/fix. You may also print the version number at the start of your application to confirm the version number, e.g
Console.WriteLine(CellsHelper.GetVersion());
If you still could not evaluate, please create a sample application, zip it and post it here with latest fix/version, we will check it soon.

Thank you.

6

Hi,


Well, we did suggest you to use LightCells API which is specifically used to insert long list of data and to create large Excel files dynamically, see the topic if you could use it:

We are afraid there is no solution currently for
creating such a huge Excel file with such kind of large dataset in the normal way.


Thank you.

7

Amjad,


Now I’m getting System.OutOFMemonry issue when trying to open a workbook with 200K * 100 col records in it.(total Size of file is ~75MB) . Can you please let us know Size limitation of your. This will help us to go and inform our customer the limitation and find other solution of it. Our simple requirement is to have around 350K records in one Worksheet( using aspose) with file size around ~150MB.

Please let us know is it possible ? if not also fine. But please give us correct limitation of Aspose dll. I have spend 20Hrs on getting this work and still there is no solution. I have to give status our client. This is getting bigger issue for us. Please Suggest ASAP.
8

Hi,


Well, there is no specific limitation put forth by Aspose. Cells regarding worksheet data or file size.
Well, if your file is quite large which you are opening/loading, so you would need sufficient resources to process the big task. It would demand at least 10 times or more RAM of the size of the file. It is not final but it can depend on the complexity of your workbook and its data or other objects, e.g. formulas, formattings, binary /drawing objects etc. So, I am afraid, as I said we have no good solution for your requirements. We have LightCells API but again you are not creating the workbook on the fly rather opening the bigger files which is not currently supported in LightCells APIs (We may support it in some future versions though). So, kindly make sure that you have sufficient resources to process your big files, probably a 64 bit machine with a good amount of RAM will process your file fine and in quick time without producing the out of memory error.



Thanks for your understanding!
9

As I mentioned that our Sever is having 64Bit with 32GB RAM is that not enough to process 100MB file?


Can you please give me example of LightCells API which created a xlsx file from DataTable? I do not not want sample that have on your site (which creates sample cell). That may help us? we have data into DataTable.
10

Hi,


I have written a sample .NET program for your requirement using the LightCells APIs, please refer to it and you may try to use this approach if it works fine for your needs. I have filled 1 million records to fill into the worksheet from the datatable (which I have already filled this long list of dataset in it using my sample code).

Sample code:

using System;
using System.Collections.Generic;
using System.Text;
using Aspose.Cells;
using System.Data;

namespace LightOoxmlTest
{
class Program
{
public static void Main(string[] args)
{
//Specify your desired matrix
int rowsCount = 1000000;
int colsCount = 3;

var workbook = new Workbook();
var ooxmlSaveOptions = new OoxmlSaveOptions();

ooxmlSaveOptions.LightCellsDataProvider = new TestDataProvider(workbook, rowsCount, colsCount);

workbook.Save(“e:\test2\myoutput_test1.xlsx”, ooxmlSaveOptions);
}
}

class TestDataProvider : LightCellsDataProvider
{
DataTable dt;
public void FillTable()
{
dt = new DataTable();
dt.Columns.Add(“Column1”, typeof(int));
dt.Columns.Add(“Column2”, typeof(string));
dt.Columns.Add(“Column3”, typeof(int));
for (int i = 0; i < maxRows; i++)
{
dt.Rows.Add(i / 10 + 1, String.Format(“Item {0}”, i), i);
}
}
private int _row = -1;
private int _column = -1;

private int maxRows;
private int maxColumns;

private Workbook _workbook;
public TestDataProvider(Workbook workbook, int maxRows, int maxColumns)
{
this._workbook = workbook;
this.maxRows = maxRows;
this.maxColumns = maxColumns;
FillTable();
}

#region LightCellsDataProvider Members

public bool IsGatherString()
{
return false;
}

public int NextCell()
{
++_column;
if (_column < this.maxColumns)
return _column;
else
{
_column = -1;
return -1;
}
}
public int NextRow()
{
++_row;
if (_row < this.maxRows)
{
_column = -1;
return _row;
}
else
return -1;
}

public void StartCell(Cell cell)
{
cell.PutValue(dt.Rows[_row][_column]);//_row + _column);
}

public void StartRow(Row row)
{
}

public bool StartSheet(int sheetIndex)
{
if (sheetIndex == 0)
{
return true;
}
else
return false;
}

#endregion
}
}



Thanks for your understanding!
11

Amjad,


Thanks for great help. I’m trying above example with very small DataTable just to understand and then i will try 350K records.

Now with above same i just modified to pass database and worked fine. Only Problem is I am not able to get Column headers into Excel. Can you Please help how to add header from dataTable to excel?

Once again thanks for your all support.
12

Please update on how to get header from datatable to excel. Thanks

13

Also is it possible to do a Cell formatting in LightCell API? Like below we are doing for other


w.PageSetup.PaperSize = PaperSizeType.PaperLetter;

//Format header
for (byte i = 0; i <= w.Cells.MaxColumn; i++)
w.Cells[0, i].SetStyle(e.Styles[“Header”]);

for (int m = 1; m <= w.Cells.MaxRow; m++)
{

w.Cells[m, 36].SetStyle(e.Styles[“Date”]);
w.Cells[m, 38].SetStyle(e.Styles[“Date”]);
w.Cells[m, 40].SetStyle(e.Styles[“Date”]);
}
14

Hi,


1) To get column names or headers, you have to use your own code, e.g. you may use
dataTable.Columns[index].ColumnName and dataTable.Columns[index].Caption attributes


2) Please see the document/article for reference:
http://www.aspose.com/docs/display/cellsjava/Using+LightCells+API
Although the article is for JAVA, but you may create or convert it to .NET accordingly. Please refer to it and you may use this approach to write your own code for your needs by yourself. I think you may define Style objects in the constructor of the class which implements “LightCellsDataProvider” interface. You can apply the styles in the public void StartCell(Cell cell) method accordingly.

Thank you.

15
Hi,

Thanks for your posting and using Aspose.Cells for Java.

From your code, it seems you want to set cells in one row or column to the same format. If so it will be much better for you to apply the style to the Row/Column object before filling cells data for performance consideration:

Row.ApplyStyle(Style, StyleFlag)/Column.ApplyStyle(Style, StyleFlag)
16

Dear Amjad,


Greeting !

This is regarding Lightcell interface. Currently I’m using Lightcell of Single worksheet using following code. Now I would like to display data on 5 worksheets using datatable. Can you please suggest how to do this in Light Cell. PLEASE HELP urgent ,…

Here is my existing construction for a single sheet. Now I have dataset with 5 datatable. Let me know if you need any more information .

ooxmlSaveOptions.LightCellsDataProvider = new ReportLightCells(workbook, dt.Rows.Count + 1, dt.Columns.Count, dt, bShowListDetails, ReportTypeDesc);

Here is my few function into LightCell

public void StartCell(Cell cell)
{
if (_row == 0)
{
cell.PutValue(ds.Tables[0].Columns[_column].ColumnName);
cell.SetStyle(HeaderStyle);
}
else
{
cell.PutValue(ds.Tables[0].Rows[_row - 1][_column]);
cell.SetStyle(rowStyle);
}
}


public bool StartSheet(int sheetIndex)
{
if (sheetIndex == 0)
{
return true;
}
else
return false;
}

17

Hi Prashman,


Well, it is easy to do it. I have attached a sample project (VB.NET → you may convert it to C# for your needs) here to accomplish your task. I have created three data tables using three methods in the class that implements LightCelllsDataProvider interface and filled data into them. Now I used StartSheet() method to call different data table for different sheet accordingly. Please refer to it and create your own sample codes accordingly.

Note: To save my time, I decrease the matrix to 100000 * 3 records per each sheet (total 3 sheets).

Thank you.

18

Dear Amjad,


Thanks for your quick reply. That sample program helped a lot… Have a great Day !!
19

Hi Prashman,


Good to know that you have sorted it out accordingly. Feel free to contact us any time if you need further help or have some other issue or queries. We will be happy to assist you soon.

Thank you.