I am currently running Aspose.Cells.GridWeb.dll with a File Version of 2.7.14.2004 and Product Version of 2013.01.11. When attempting to create a grid with approximately 366 columns, I encounter the error:
[ArgumentOutOfRangeException: Specified argument was out of the range of valid values.]
Based on other posts in this forum, it looks like I may be trying to create more columns that the GridWeb supports. Is this true? What is the maximum number of columns? Is there an updated release that expands this limit? If not, is there a plan to increase this limit?
Well, for an XLS file format, you can have only 65536 rows and 256 columns in a worksheet, for an XLSX file format, there is no such limit. Also, the limitation is set by MS Excel for different file formats. Could you try our latest version Aspose.Cells.GridWeb v5.0.1.xxx, you may get the version after installing Aspose.Cells for .NET v7.7.1 package in the Downloads module.
If you still find the issue, kindly create a sample project (runnable) with it and provide us here to reproduce the issue on our end, we will check your issue soon.
I am not loading a spreadsheet. I am creating a GridWeb based on data I pull from a SQL database, creating the columns and rows as needed and then populating the GridWeb with the data.
As I told you that there is no limit from Aspose.Cells.GridWeb, it totally depends upon your MS Excel file format that your using (e.g XLS, XLSX etc.) and resources (e.g RAM) that you are using for the bigger matrix or dataset, etc. Well, I have tested your scenario/ case a bit by creating my dynamic datatable (which contains a table with more than 366 columns) and then importing to GridWeb, it works fine, see the attached screen shot for your reference. I am using the following sample code (runnable) in a web form (in a project) with Aspose.Cells.GridWeb v5.0.1.xxxx:
e.g
Sample code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Aspose.Cells;
using AGW = Aspose.Cells.GridWeb;
using AGWD = Aspose.Cells.GridWeb.Data;
using [System.IO](http://System.IO);
using System.Data;
namespace WebApplication1
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
SetAsposeLicense();
//The datatable.
DataTable dt = new DataTable(“Products”);
for (int i = 1; i <= 370; i++)
{
dt.Columns.Add(“Col_ID” + i.ToString(), typeof(Int32));
}
for (int x = 0; x <= 2; x++)
{
DataRow dr = dt.NewRow();
for (int y = 0; y < 370; y++)
{
dr[y] = y +10;
}
dt.Rows.Add(dr);
}
gridWebCalculationArea.WebWorksheets.ImportDataView(dt.DefaultView, null, null,gridWebCalculationArea.WebWorksheets[0].Name,0,0);
}
}
private void SetAsposeLicense()
{
//Set the license file [Aspose.Cells.lic]
string asposeLicenseFile = System.IO.Path.Combine(this.Server.MapPath("~"), “Aspose.Cells.lic”);
Aspose.Cells.License cellsLicense = new Aspose.Cells.License();
cellsLicense.SetLicense(asposeLicenseFile);
Aspose.Cells.GridWeb.License gridWebLicense = new Aspose.Cells.GridWeb.License();
gridWebLicense.SetLicense(asposeLicenseFile);
}
}
}
Please try our latest version v5.0.1.xxx (GridWeb). If you still find the issue, kindly provide us a sample project (runnable - you may create a dynamic datatable for your data), zip it and post it here to reproduce the issue on our end, we will check your issue soon.
Sorry it took a while to reply, but I needed to update my license in order to take the latest release. The good news is that the latest release does indeed appear to support my required number of columns.
The bad news, though, is that performance is horrible. Using Firefox, it takes upwards of 3 minutes to fully load the page. IE8 and IE9 never seem to fully load.
I extracted out the main processing of my application into a standalone project. You can find that project in the attached zip file. Note that it is currently configured for just 15 columns. At line 43 of the Default.aspx.cs file is a definitions of EntryCols which controls the number of columns. As indicated in the comment attaches to that line, it needs to support 368 columns and perform. You can begin to see the performance issues when EntryCols is increased to 200 and beyond.
I am also including a screen shot of the resulting screen (from the sample project). It is color coded for the user. The pale yellow area is where they perform their data entry. Those cells and the ones in green are the only cells that are editable. The top row contains formulas to provide column totals. The far right column contains formulas to provide row totals. The first two columns and the top 3 rows are frozen.
I welcome any input you can provide as to why the performance of this grid is so poor. Limiting the number of columns is not possible as a number of my customers require 1 per day of the year plus some label columns (the first 2 in the provided project).
After an initial test, I observed the performance issue as you mentioned by using your project when loading large formatted data (having columns >= 200) into GridWeb, it takes long time to finally load the data into GridWeb. Obviously it would take more time as you increase the number of columns to your matrix. Anyways, we need to evaluate your issue thoroughly. I have logged a ticket with an id “CELLSNET-42436” for your issue. We will look into your issue soon.
Once we have any update on it, we will let you know here.
Please try our latest version Aspose.Cells.GridWeb v6.0.0.2xxx with latest acwmain.js file from acw_client folder at your installation directory. You may get the latest version of Aspose.Cells.GridWeb after installing our latest Aspose.Cells for .NET v7.7.2 package from the Downloads module. Also, please make sure to set needInitAlignmentAdjust=false in the “acwmain.js” file (by default, it is set as false).
I have tested with Aspose.Cells.GridWeb v6.0.0.2000 and its relevant latest scripting and other files in acw_client folder and it takes very little time to load the long data list (matrix) into GridWeb, see the screen shot attached here.
Thank you very much. I downloaded and installed the full Cells 7.7.2 package and the GridWeb does indeed load quicker (especially in Firefox). However it is still unusable for data entry. For this test, I set the EntryCols variable to 200 (still not the full 368 I need to support).
Please try entering a number in one of the cells, press Tab, and then try to enter another number. In Firefox the second cell does not receive proper focus, so I cannot simply enter another number. Also, the amount of time it takes to enter that number and tab is way too long.
In IE entering a number and tabbing does result in proper focus for entry in the second cell, but the performance is HORRIBLE.
This performance has to be improved for the grid to be usable for data entry.
I could not spot the issue (regarding data entry into the cells) as you have mentioned. The tab key works fine although it takes a little time for the first time to navigate the focus on the second cell but all other navigations are ok I think as I tested. Also, you may use arrow keys to do that for your needs.
Regarding IE browser type’s issue, I think you may use IE8 only as compatibility mode, I tested and it works good here.
""
We will also check and try to enhance the performance more.
* In Firefox it fails. I enter a “1” and hit Tab. While the cell shading moves to the next cell, the focus does not. The focus is outside the grid and hitting Tab more times I can see the focus moving about the browser’s controls. If I instead use the right arrow key, it works correctly. So, there is an issue with the Tab key in Firefox.
The performance when the grid is configured for 368+ cells is a real problem and makes the component unusable for our customers that require a daily spreadsheet.
After further evaluation of your issue. I found after entering data into a cell and hitting the Tab, the focus does not shift to the next cell very first time. The focus is outside the grid and hitting Tab more times I can see it fine.
I have reopened your issue again and we will surely look into the Tab key issue and performance issue if we can enhance it more.
Once we have any update on it, we will let you know here.
We have evaluated your issue further. We could not find any significant performance issue when we set the columns’ count to more columns. A very little pause is inevitable when you have a huge list of columns to be rendered into the GridWeb but it is acceptable as we think. We suggest you to kindly try our latest “acwmain.js” file (attached) into acw_client folder path.
Let us know your feedback.
Regarding tab key issue, we are already working over it and will provide the fix soon.
I have tried the attached acwmain.js file. I set the number of columns to 368 (what my customers need for a daily spreadsheet) and performed tests.
* Chrome (as expected) is the fastest and there is on average less than 3 seconds between cells when entering values and tabbing to the next.
* Firefox was a little slower, with times nearing 10 seconds between cells when entering values and using the right arrow key
* IE8 (as expected) is pretty unusable with times near 30 seconds between cells when entering values and tabbing to the next.
I will integrate the latest Aspose.Cells.GridWeb and acw_client folder into our build process and try the resulting application using more recent versions of IE to see if performance improves.
A resolution to the tab key issue in Firefox is needed before I could release this to my customers.
Thank you very much for all your help. I eagerly await an update for the tab issue.
Sorry for the delay, but I didn’t correctly read your reply about the Firefox tab issue. it indeed appears to be fixed in the provided acwmain.js file.
Good to know that it fixes your issue regarding Tab key navigation. 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.