Display grid with 16384 empty columns like in MS Excel

Aspose.Cells Product Family
1

Hi,


When importing an Excel file in the GridDesktop, is there a simple way to extend the number of row and columns with empty rows/columns and fill the background with them.
In other words to mimic the default behavior of excel concerning the number of empty lines/columns when opening an existing file.

I suppose I could append empty lines and columns, but then the size of the scroll-bars, proportional to the ratio displayed area/whole area will not seem right.

Thanks
2

Hi Daniel,


Thank you for contacting Aspose support.

What I understand from your post is that you wish to give the GridDesktop a view of Excel application in terms of rows & columns regardless of the fact how many cells contain any data. If this is true then please note that GridDesktop will display the maximum range of cells that contains data, means, if the spreadsheet to be loaded has the range of data from cell A1 to B2 then GridDesktop will display the same range while omitting any blank rows & columns. You can workaround this situation by inserting some value (empty string as well) at farthest cell to increase the display range before importing the spreadsheet in GridDesktop. For instance, please check the following piece of code that inserts an empty string at Cell[1000, 1600] to increase the display range as discussed above. Please also note that the scroll bars will adjust themselves according to the display range of cell.

C#

MemoryStream stream = new MemoryStream();
Aspose.Cells.Workbook book = new Workbook(“C:/temp/book1.xlsx”);
book.Worksheets[0].Cells[1000, 1600].PutValue("");
book.Save(stream, SaveFormat.Xlsx);
stream.Position = 0;
this.gridDesktop1.ImportExcelFile(stream);
3

Hi Babar,


Thanks for your help.
I tried something similar in my openfile method but it does not work:
GridDesktop grid = UCGrid.gridDesktop1;
grid.ImportExcelFile(xmlSSFilename);
// fill the grid with empty cells to make it “MS Excel like”
for (int i = 0; i < grid.Worksheets.Count; i++)
{
Aspose.Cells.GridDesktop.Worksheet ws = grid.Worksheets[i];
string wsname = ws.Name;
int maxrowsize = ((ws.RowsCount / 1000) + 1) * 500;
int maxcolsize = ((ws.ColumnsCount / 1600) + 1) * 500;
ws.Cells[maxrowsize, maxcolsize].PutValue("");
}
grid.Refresh();

The file I import to the grid (xmlSSFilename) has 4 named sheets and is displayed perfectly in the gridDesktop1 in my app (it is a spreadsheetML file). But in the code I included here, I try to access the sheets and I find only 2, “Sheet1” and “Evaluation Copyright Warning”. It is like I am accessing a different file or a new one.
What am I doing wrong? Do you have an idea what’s going on?

Thanks

4

Hi,


Thanks for providing us sample code and further details.

I tried to simulate your issue with a simple SpreadsheetML file and your code but I could not find any issue, it works fine and all sheets in the workbook are imported. I am using Aspose.Cells.GridDesktop v8.6.2. Could you provide us your template SpreadsheetML (.xml) file, so we could evaluate your issue properly on our end.

Thank you.
5

Hi,


I have done some testing withe the following code:
GridDesktop grid = UCGrid.gridDesktop1;

// create a temp copy of xml file and extend the cells
Aspose.Cells.Workbook book = new Workbook(xmlSSFilename);
string wsname = book.Worksheets[0].Name;
// fill the grid with empty cells to make it “MS Excel like”
for (int i = 0; i < book.Worksheets.Count; i++)
{
Aspose.Cells.Worksheet ws = book.Worksheets[i];
wsname = ws.Name;
int maxrowsize = ((ws.Cells.Rows.Count / 500) + 1) * 500;
int maxcolsize = ((ws.Cells.Columns.Count / 500) + 1) * 500;
maxrowsize = 500;
maxcolsize = 256;
ws.Cells[maxrowsize, maxcolsize].PutValue(" ");
}
string tmpFile = Path.Combine(Path.GetTempPath(), “XclDoc.xml”);
book.Save(tmpFile, SaveFormat.SpreadsheetML);

grid.ImportExcelFile(tmpFile);


Loading the file with Aspose Cells Workbook and saving in a temp file bypasses the problem of dealing with the worksheets of the grid itself. If I could do it without an intermediary temp file, it will be a relief.
However, the code I post in this comment works only if you change the maxcolsize to 255 !
This is a value loved by developers ! When it is 256, the grid displays only the cells loaded in the original file and ignores the runtime PutValue().

So now I have 2 issues to solve.

the file I use is attached!

6

Hi again,


I tested a simple spreadsheet with 3 sheets and it worked, except for the limit of 255 columns.
There is something about my more complex file I attached in my previous comment your grid does not like!
7
Hi,

Thank you for the sample spreadsheet.

danielastertech:
Loading the file with Aspose Cells Workbook and saving in a temp file bypasses the problem of dealing with the worksheets of the grid itself. If I could do it without an intermediary temp file, it will be a relief.

You do not need to save the intermediate file on disk. You can simply store the updated results in an instance of MemoryStream and then load it with Aspose.Cells.GridDesktop component. I have already shared sample code demonstrating this usage scenario here.

danielastertech:
Hi,
However, the code I post in this comment works only if you change the maxcolsize to 255 !
This is a value loved by developers ! When it is 256, the grid displays only the cells loaded in the original file and ignores the runtime PutValue().

I believe you are hitting the column limitation for your specified spreadsheet format. Please try by saving the updated spreadsheet in XLSX format using the following piece of code and you will be able to by-pass this problem as well.

C#

using (var stream = new MemoryStream())
{
book.Save(stream, SaveFormat.Xlsx);
stream.Position = 0;
gridDesktop1.ImportExcelFile(stream);
}
8

Hi Babar,


Working with a stream, Cells.Workbook instead the worksheets object of gridDesktop, and with Xlsx in memory instead of spreadsheetML format solves the problem.

Using the gridDesktop1.Worksheets object with the spreadsheetML file I gave you does not work even if it is displayed correctly in the grid. Can you tell me why or does it mean I should always work with Cells.Workbook and an intermediary stream instead ?

Thank you for your help, your responsiveness and your efficiency.

9

Hi Daniel,


Please note, Aspose.Cells.GridDesktop should ideally work exactly as Aspose.Cells APIs, however, in this case the results are different. Please allow me some time to look further into this matter and log appropriate ticket for product team’s review. In the meanwhile, you have the solution that should get you started with the Aspose.Cells.GridDesktop component.
10

Hi again,


This is to update you that I have logged a investigative ticket (CELLSNET-44101) for the product team to review the scenario shared here. The GridDesktop component should extend the spreadsheet’s viewing area by inserting an empty string in the farthest cell of the GridDesktop’s worksheet directly rather than using Aspose.Cells.Workbook to manipulate the spreadsheet and then load it in the GridDesktop. Please spare us little time to investigate the matter, and get back to you with updates in this regard.

11

Hi Daniel,


Thank you for your patience with us.

This is to update you that we have investigated the issue logged earlier as CELLSNET-44101. You can extend the view area of GridDesktop by using the Aspose.Cells.GridDesktop.Worksheet.RowsCount & ColumnsCount properties. Please note, as import facility of the GridDesktop is asynchronized so if you wish to extend the view area after importing a spreadsheet then you should set the aforementioned properties within the FinishLoadFile event such as demonstrated below.

C#

private void gridDesktop1_FinishLoadFile(object sender, Aspose.Cells.GridDesktop.WorkBookEvents args)
{
this.gridDesktop1.Worksheets[0].ColumnsCount = 1000;
this.gridDesktop1.Worksheets[0].RowsCount = 1000;
this.gridDesktop1.RefreshControl();
this.gridDesktop1.Refresh();
}
12

Hi Babar,


Fantastic. It works.
So I understand I should not access the gridDesktop.Worksheets object before the event FinishLoadFile is fired. If I access it immediately after the importExcelFile() which in my case is called by my MFC MDI OnOpenDocument() event method, I get the workbook of a new document.

By the way, if it interests someone, I have developed a bridge dll who is called by a Native C++ MFC MDI unmanaged application (with the document/view MS architecture) which calls a .NET User Control C# dll with Aspose gridDesktop.
I have been told a few times it was not feasible and I have it.

One issue is behind us.
Thanks Babar

Daniel
13
danielastertech:
Fantastic. It works.
So I understand I should not access the gridDesktop.Worksheets object before the event FinishLoadFile is fired. If I access it immediately after the importExcelFile() which in my case is called by my MFC MDI OnOpenDocument() event method, I get the workbook of a new document.

Thank you for the confirmation. It is good to know that you are up & running again.

danielastertech:

By the way, if it interests someone, I have developed a bridge dll who is called by a Native C++ MFC MDI unmanaged application (with the document/view MS architecture) which calls a .NET User Control C# dll with Aspose gridDesktop.
I have been told a few times it was not feasible and I have it.

It would be of great help for Aspose community if it is possible for you to share an application demonstrating the usage of Aspose.Cells.GridDesktop control in C++.