Hi,
1 of our user file had bunch of pivot tables (in multiple worksheets) in this workbook and some standard dataset in worksheets and formula referring to other worksheets. I tried to import this excel file using
Workbook wbExceldata = Workbook.Load(fileName);
and it takes 3 to 4 min for executing the Load command for loading empty workbook with 1 blank sheet (I deleted all other worksheets).
Even after removing all worksheets still it is 600 KB. it seems to holding it in memory i guess.It works for other excel files and am guessing something weird in this workbook.
Can you please help me to figure out why this file takes long time to load.
Thanks,
Valliappan
Can you post the workbook which has no worksheets? I'm guessing that it still has worksheets, but they are just hidden.
Hi Dour,
Since the file is 500 KB even after winzip, i can't upload the file as 200 KB is the max upload limit. Can you please let me know hoe can i send the file?
There are no hidden sheets and otehr info i would like to share is after excel file was loaded , i did look at the excel object in teh debugger and it had named references count =72 even though i removed all pivots and worksheet references in the workbook and had a empty workbook.
Hi Danko,
Thanks for that tip on upload. Please find the blank excel workbook attached with this mail.
Hello Valliappan,
I have found the reason why this file has this size. After unzipping it, the size gets up to 613KB.
After that if I remove all of the named references it becoms 308KB. I have further investigated that file and it contains kind of "styes" which are actually about 7MB in size, but packed in the excel file they gets about 300KB. If I remove them as well the file goes about 7KB.
Could you please let me know if you need those "styles" or you would like to have just the named references kept inside the file?
Thank you for using Infragistics.
Cool. I did also find that "Named references" in the debugger ( Count as 71) after the file was loaded. I was not sure why it was still retained in memory even after deleting those worksheet references and pivot tables. hoe can i remove this from the excel ?
Reg "Style" property, where is this embedded in the worksheet? how can i view this.?Assuming, If we r removing both these properties and have some data in the workbook , is import faster?
Can u please go ahead and remove those and try with some records?
I have tested the original file in my environment and it takes me about 92 seconds to load the workbook.
We will probably be able to improve the performance so I will log this behavior in our internal system.
After removing the NamedReferences and the styles from the mentioned file, it takes less than 1 second to load the workbook.
If you would like to remove the NamedReferences you could go to "Formulas" tab, and under the "Defined Names" Group there is "Name Manager". Once you open it, you would be able to delete all of the references there.
I am attaching the file to this forum thread, so you could test it with the desired number of records, and let me know if you still need any other assistance with this matter.
Thanks Danko. So, if we have excel workbook with pivot tables and styles, it will take more time for loading. is that true? .i tested with the file u sent , loading is faster. Is there any other way where i can programtically remove these references and styles before trying to import data ( The worksheet i want to import does not hv any references) from this file?
Hello Villiappan,
This issue has been fixed in the latest service release for NetAdvantage for Windows Forms 2011 vol1 and 2011 vol2.
If you have any other questions please feel free to let us know.
I havn't got a chance to test in the latest release. We are planning to upgrade from v10.2 to the latest release .can you please let me know which is the latest release and is this fix included in th latest release?
In our latest service release we have improved the performance when you are having similar scenarios like the mentioned one.
Please feel free to let us know if you have any other questions with this matter.
Just to let you know that I have created the following case for you : CAS-79799-RMQVXT
The cases will be linked to an internal work item for improving the load time when there are any NamedReferences in the file.
There is a way to remove the NamedReferences and the styles in your file programatically, but you will have to wait the intial loading time and once the workbook is loaded you could do something like the following to remove them (in your case there are 35901 styles and 71 named references):
Workbook wb = Workbook.Load("..\\..\\SomeExcelFile.xlsx");wb.NamedReferences.Clear();wb.Styles.Clear();
If you have any other questions please feel free to let me know.