Autofilter disappearing on sort

[jimbo1999]

Hello,

I've recently upgraded to XLSReadWriteII 5.20.36 from version 4, and I'm having some problems with autofilters. I'm producing an xlsx file with a single tab, and have a header row where I'm applying an autofilter using the code below. When the file is opened in Excel 2013 it displays fine and the autofilters work, but if I select a data cell then sort, the autofilter disappears. It;s not a massive issue but one that's pretty annoying for customers who have to constantly turn the autofilters back on. Any ideas on what I can do to debug what's happening?

Code: Select all

oXLS[0].Autofilter.Add(nStartCol, nRow, nEndCol, nRow);

The file itself can be downloaded from http://www.pellcomp.co.uk/users/Occupancy000.xlsx

Thanks,
James

[jimbo1999]

Hello,

I've now done some further investigation on this as we have customers refusing to use the new version, and I've found a solution, but I'm unsure how (or indeed, if) I can fix it in code using XLSReadWrite.

I opened newly created XLSX file in Excel and turned off the Autofilter, then turned it back on, then saved the document. I opened the fixed XLSX file in Winzip and looked at workbook.xml and compared it with the original produced using XLSReadWrite. One section which has been added by Excel is the "definedNames" section which contains a definedName for the autofilter as per the code below. I applied this section to the original file and resaved, and on opening the spreadsheet the filter worked fine.

Code: Select all

<definedNames>
        <definedName name="_xlnm._FilterDatabase" localSheetId="0" hidden="1">Occupancy!$A$8:$BL$140</definedName>
</definedNames>

Is there anyway I can add this defined name using the code? Or should it be done automatically when adding the Autofilter? I can see that there is an object called TXc12DefinedNames but I'm really struggling to see how I can use this to add the section shown above.

Can anyone help?

Thanks,
James

[larsa]

Hello

This will be fixed in the next update, ready by the end of this week.

[jimbo1999]

Thanks Lars, I'll keep an eye out for it.

[larsa]

Hello

The update is ready now, 5.20.41

[jimbo1999]

Thanks Lars,

The version on the registered download site is still 5.20.39. Is there another page I should be looking at?

Many thanks,
James

[larsa]

Hello

The text on the download page was not updated, but the component is.

[jimbo1999]

Thanks Lars,

Unfortunately the fix hasn't quite worked, and in fact made the problem worse. When the spreadsheet is opened in Excel, I now get a message saying "We found a problem with some content in <filename>. Do you want to recover as much as we can? If you trust the source of this workbook, click Yes.". If you click yes then the spreadsheet opens but the same problem as before still happens if you try to sort the data.

I've had a look at the XML and it seems that the definedNames section is being added, but is missing the hidden=1 attribute and the name of the tab (see below for XML as produced by the code). If I go in and manually add these to the XML then save and reopen it works fine. Is there something I'm doing wrong when adding the Autofilter?

Code: Select all

<definedNames>
        <definedName name="_xlnm._FilterDatabase" localSheetId="0">$A$8:$BL$8</definedName>
</definedNames>

Thanks,
James

[jimbo1999]

Lars,

I'm afraid this one won't go away. To make things work and avoid going back to version 4, I've modified the library code in a few places. These changes are probably not 100% correct and are workarounds, but seem to fix the problems we've had.

What seemed to be happening was that a defined name was being created when adding an autofilter and its value (FContent) was set correctly, but then when this name was being written to the sheet in OnWriteDefinedName, the line "Name.Content := N.Content;" was calling a get property on N (TXLSName.GetContent) which was ignoring the value stored in FContent and calculating a different value based on the area, which wasn't set.

Basically I've had to manually set the SheetName and dimensions of the FArea record of the TXc12DefinedName when adding an Autofilter, then force the name to be an area when running Compile.

In TXLSName.Compile, add the following if statement to the line setting FSimpleName:

Code: Select all

  if FBuiltIn = bnFilterDatabase then
	FSimpleName := xsntArea
  else
    FSimpleName := IsSimpleName(FParent.FManager,FPtgs,FPtgsSz,@FArea);

This is probably something that IsSimpleName should be doing but this was the quick fix.

In TXLSAutoFilter.Add add the following code to set the FArea values:

Code: Select all

  N.SheetIndex := FXc12Sheet.Index;
  N.Col1 := AC1;
  N.Col2 := AC2;
  N.Row1 := AR1;
  N.Row2 := AR2;

In TXc12DefinedName, make the properties writeable:

Code: Select all

property SheetIndex: integer read FArea.SheetIndex write FArea.SheetIndex;
property Col1: integer read GetCol1 write FArea.Col1;
property Row1: integer read GetRow1 write FArea.Row1;
property Col2: integer read GetCol2 write FArea.Col2;
property Row2: integer read GetRow2 write FArea.Row2;

This code forces the defined name to use the correct sheet name and area values for its value, even on multiple tabbed workbooks.

Does any of the above look correct, or am I missing something else?

Thanks,
James