Hi,
I'm having a problem with the SetAutoFilter method and I can't figure this out.
Our company has made a template with several pivot tables and these pivot tables use two named ranges in the workbook. Each range has data on a seperate sheet and both sheet are completely independent of each other. In one sheet, our clients see all the information of the dossiers they created in our program, in the other sheet, they see all the information of the merges (automated generated Word-files as lettres) they did in our program.
We noticed that some of our clients have a huge database that is put in one those sheets and Excel can't handle all that information when updating the pivot tables.
We have implemented a filter in which our clients can select a number of years they want to have in the Excel file, this could limit the data in the generated Excel file and will make the pivot tables work. But the generated Excel file serves as an audit, so sometimes they want to see all the dossier information.
In our program, we check which named ranges are known in the Excel file, only the existing ranges will be used to update the client information.
If we use our template with both ranges, everything works great. If we remove one range in our template, everything works flawless as well. (We do have to remove the pivot tables that use the deleted range, but this has nothing to do with our problem)
We do want our clients to be able to make changes in their generated Excel files by removing some pivot tables or adding pivot tables of their own. In order to preserve these changes, we check for the named ranges in their Excel file and update the according sheet(s).
If we have a generated Excel file with both named ranges in them, we can make changes and have our program update both sheets without a problem. If we have a generated Excel file with only one of the two named ranges, we get an error though!
The error is caused by the function xls.SetAutoFilter(rowIndex, colIndex1, colIndex2). We tried moving this function up and downward in our procedure and it always fails on this function. Strangely enough, this function doesn't fail when we use it with our templates or with the generated Excel file that has both ranges. It only fails when we use a generated Excel file that has only one of both ranges.
The error we get is the following:
EAccessViolation: Vcl.ComCtrls.pas (line 33711): Access violation at address 0040A94A in module 'Smw.exe'. Read of address 0000000C.
SVN Revision (no svn build)
[Smw.exe] System.TObject.Free (Vcl.ComCtrls.pas, line 33711)
[ntdll.dll]
[Smw.exe] System.__linkp.Free (Vcl.ComCtrls.pas, line 33711)
[ntdll.dll]
[ntdll.dll]
[Smw.exe] _Uxlsescher.Tdrawinggroup.TDrawingGroup.EnsureDwgGroup
[Smw.exe] _Uxlsescher.Tdrawing.TDrawing.AddAutoFilter
[Smw.exe] _Uxlssheet.Tsheet.TSheet.SetAutoFilter
[Smw.exe] _Uxlsadapter.Xlsfile.TXlsFile.SetAutoFilter
[Smw.exe] Frmaudit.TAuditForm.FillFileDossiers (frmAudit.pas, line 708)
[Smw.exe] Frmaudit.TAuditForm.CreateFile (frmAudit.pas, line 520)
[Smw.exe] Frmaudit.TAuditForm.btnLastUsedFileClick (frmAudit.pas, line 285)
[Smw.exe] Vcl.Controls.TControl.Click (Vcl.Controls.pas, line 7357)
[Smw.exe] Vcl.Controls.TWinControl.WndProc (Vcl.Controls.pas, line 10084)
[Smw.exe] Vcl.Stdctrls.TButtonControl.WndProc
[Smw.exe] Vcl.Controls.DoControlMsg (Vcl.Controls.pas, line 10153)
[Smw.exe] Vcl.Controls.TWinControl.WndProc (Vcl.Controls.pas, line 10084)
[Smw.exe] Vcl.Forms.TCustomForm.WndProc
[Smw.exe] Dcsforms.TDcsForm.WndProc (DcsForms.pas, line 1497)
[Smw.exe] System.Classes.StdWndProc
[USER32.dll]
[USER32.dll]
[USER32.dll]
[USER32.dll]
[USER32.dll]
[USER32.dll]
[USER32.dll]
[USER32.dll]
[USER32.dll]
[USER32.dll]
[USER32.dll]
[USER32.dll]
[Smw.exe] Vcl.Controls.TWinControl.DefaultHandler (Vcl.Controls.pas, line 10125)
[Smw.exe] Vcl.Controls.TWinControl.WndProc (Vcl.Controls.pas, line 10084)
[Smw.exe] Vcl.Stdctrls.TButtonControl.WndProc
[Smw.exe] System.Classes.StdWndProc
[USER32.dll]
[USER32.dll]
[USER32.dll]
[USER32.dll]
[Smw.exe] Vcl.Forms.TApplication.ProcessMessage
Could you help us with this please?
When we put this function in commentary, our program works flawless with these generated files containing only one range, but then our data doesn't have a auto filter.
Thanks in advance!
Sincerely,
Tim Smet
Hi,
Thanks for the report, I'll take a look. Seems like a double free somewhere.
But to make things faster (and also to ensure I am fixing the problem you have), can you email me a file with problems to adrian@tmssoftware.com ?
I mean, the excel file to which you try to set the autofilter and get the error. Or some simple app that shows the problem. If it is simple to reproduce and you can send me the file, it will make things simpler. Anyway I'll try to see what could be going on.
Regards,
Adrian.
Hi Adrian,
I've send you the mail.
It's really strange that if we use a generated file with both range, that we can use the SetAutoFilter twice without a problem and that we can't use it when we use a generated file that only has one range.
both sheets and ranges are seperate from each other and somehow this fails when there is only 1 range present in the generated file. In our template, this doesn't seem to be a problem, then we can have only one range and the SetAutoFilter works flawless.
Hi
Thanks, I got the files and I am taking a look right now. I'll let you know when it is ready.
A fast answer for a question in the mail:
>We also noticed that after Flexcel is finished we have a file of 10Mb for example and when we open >this and close it, we always get a save prompt and then the size is almost doubled…
There are 2 issues here:
1)Xlsx files are zip files, and this makes size comparisons virtually impossible because of two reasons:
1a) A single bit of difference on the files inside the zip can lead to completely different zip files.
1b) There are many zip compression levels: From 0 (fastest, maximum zip size) to 9 (slowest, smallest files).
From the zLib headers:
#define Z_NO_COMPRESSION 0
#define Z_BEST_SPEED 1
#define Z_BEST_COMPRESSION 9
#define Z_DEFAULT_COMPRESSION (-1)
FlexCel uses DefaultCompression, which is supposed to be 6 and gives a good compromise between speed/file size. Excel uses a fastest compression level, which means that almost always xlsx files generated by FlexCel will be smaller than the same file generated by Excel.
When choosing the compression level, we tried to see what was best, and ind the default level seems to be a sweetspot. Lower level create much larger files, higher levels create almost the same size files, but slower. Of course this means a problem if someone benchmarks us against other solution (or excel itself) which use faster compression levels, but we think the right thing to do is to offer as much compression as possible if the speed difference isn't huge, even if we lose in some synthetic benchmark.
2) In this particular case, FlexCel won't calculate the pivot tables, and this is probably the main reason for the size difference. Once you open the file in Excel, it will calculate the pivots, create caches, and save all of those caches in the file (this is why it asks to save). This should count for most of the size difference.
Hi Adrian,
Thanks for the quick response.
You're explanation seems logical. I just wanted to check this hoping that this wouldn't be the problem.
I hope you'll figure this error out, it's really confusing and we want to keep this feature.
Sincerely,
Tim Smet
Hi,
I think I've found the problem and it should be fixed now. We've released a fix as 6.1.1 in our registered website.
Please retest with 6.1.1 (which also includes the codename fixes) and let me know.
Hi Adrian,
Thanks for the fix. We tested our program again with your changes and now everything works (both the fix with the SetAutoFilter and the codename fixes).
Sincerely,
Tim Smet