Greetings!
Me again! 
I have been testing your product extensively and have come up with some more desired features.
The main reason for the designer file MemoryStream support from our perspective is so we can send a stream from one server to another and have the second server create the excel spreadsheet from that stream.
This would be great, be we are finding that there is a substantial CPU cost to actually instantiating a large Excel object.
Basically, here, we will be having designer worksheets stored on the hard drive and when a user visits our web site and selects a report, the web server loads the designer, fills in the requested data, and sends it to the client.
Works great, and I assume plenty of people will use Aspose.Excel for this purpose. In our scenario, however, the designer files are quite large, in the order of 12 megs. We are finding that there is an almost 5 second, 100% cpu utilization, creation time for loading these designer files. It would be great if I could load up all the designers into the web server’s Application cache and simply get a copy from memory each time a user requests the report.
With this in mind, I tried doing so via the web pages cache methods, however, updating the Excel object in memory also updates it in the cache which won’t work for what I need.
What all of this is leading up to is the need for an Excel.Clone method. I would like to be able to take an excel object from the server cache and clone it to a new Excel object, similar to the way some of the native objects in .Net work. This would allow me to get a fresh object each time a user requests a report without the cost of having to load it from a file. Of course, the clone method would have to be efficient, or nothing will be gained.
Also, I would like the following abilities,
- Access an element of the Cells collection via a numeric index. I would like to be able to get the last element of the collection without having to enumerate through them all.
- A property with the MaxColumn used value.
- A property with the MaxRow used value.
I am really liking the turn around and effort your company is providing us. I have been authorized to purchase several licenses of your product, and someone from my group shall be doing so shortly.
Keep up the good work!
Tim Trahan
Senior Programmer Analyst
Hilton Hotels Corp.
Dear Tim,
Good suggestion! Your requirements are also helpful to our product.
As I understand, your requirements are:
1. Add a clone method in Excel object, so after you read a huge designer file, you can deep-copy the data to other Excel objects. And the all the modification to these excel objects do not interfere with each other.
2. Add numeric index to Cells collection
3. A property with the MaxColumn used value to Cells collection, so you can know the max column number which contains numberic, string or formula data.
4. A property with the MaxRow used value to Cells collection, so you can know the max row number which contains numberic, string or formula data.
My plan is:
1. A fix with VBA designer support and memory stream support will be delivered in the end of this week.
2. A new version with Pivot Chart/Table designer support will be delivered in the end of this month.
3. A fix with your new requirements will be delivered at the beginning of next month.
Is that OK for you? And could you give me one of your huge designer file, so I can test the efficency of the Clone method?
Thanks.
Dear Tim,
Please download Hot Fix 1.5.9.
I added a new method, Excel.Copy, to allow you to deep-copy data from other Excel objects.
For example,
Excel excel1 = new Excel();
Excel excel2 = new Excel();
excel1.Open(“d:\pRpt09.xls”);
excel2.Copy(excel1);
…
I tested it with your huge designer file. It only takes about 1.5 seconds to deep-copy the Excel objects.
MaxRow and MaxColumn properties are added to Cells collection.
I must warn you that the numeric index is not added to Cells collection…let me explain. The Cells collection arranges Cell objects, sorting first by row index and then by column index; the same as MS Excel native format. So Cells collection is more like a SortedList than an ArrayList. If you use the numeric indexer to access the last element, the element returned by the indexer may not the element you needed.
Cells object can be created from a designer file or by API, so simply supplying the numeric indexer will cause confusion.
To keep Aspose.Excel robust, I decided not add the numeric indexer.
Sorry for the inconvenience. I think you can record the row & column index or name of the Cell object to access the last element you added to the Cells collection.