AutoFilter generated XML error

[Jérémie]

Hello all. I have some problem with the autoFilter function. It seems to me that the generated XML code is not complete, and makes Excel crash.

Here is how I specify the filter:

Code: Select all

CTAutoFilter filter = new CTAutoFilter();
filter.setRef("A8:L8");
worksheet.setAutoFilter(filter);

When I open the .docx file in Excel (2010), I see the filter marks. But if I try to sort ascending or descending, Excel crashes.

If I manually remove the filter, then recreate it on the same cells, it is now ok.
Here is the XML code added by Excel on workbook.xml, when I do it (after <sheets/> part)

Code: Select all

<definedNames>
    <definedName name="_xlnm._FilterDatabase" localSheetId="0" hidden="1">'Sheet1'!$A$8:$L$8</definedName>
</definedNames>


It looks like Excel needs a specific name for the filter.

I can create a new one by myself, for example like this:

Code: Select all

CTDefinedName ctDefinedName = new CTDefinedName();
ctDefinedName.setName("_xlnm._FilterDatabase");
ctDefinedName.setLocalSheetId(sheetNumber);
ctDefinedName.setHidden(true);
ctDefinedName.setValue("'" + sheetName + "'" + filter.getRef());

But how can I assign the CTDefinedName to my sheet or workbook?

Thanks in advance (and thanks for your great job).


EDIT:
I managed to assign the name to the worksheet. Here is how:

Code: Select all

SpreadsheetMLPackage spreadsheetPkg = SpreadsheetMLPackage.createPackage();
...
// populate XLSX document
worksheetPart = spreadsheetpkg.createWorksheetPart(new PartName(...));
...
// Assign an autofilter
CTAutoFilter filter = new CTAutoFilter();
filter.setRef("A8:L8");
Worksheet worksheet = worksheetPart.getJaxbElement();
worksheet.setAutoFilter(filter);

// Define a name for the autofilter
CTDefinedName ctDefinedName = new CTDefinedName();
ctDefinedName.setName("_xlnm._FilterDatabase");
ctDefinedName.setLocalSheetId(0);
ctDefinedName.setHidden(true);
ctDefinedName.setValue("'" + sheetName + "'" + filter.getRef());

// Assign the name to the workbook
Workbook workbook = spreadsheetpkg.getWorkbookPart().getJaxbElement();
DefinedNames definedNames = workbook.getDefinedNames();
if (definedNames == null)    // Is-it the right method? (I saw an ObjectFactory object. Better using it?)
   definedNames = new DefinedNames();
definedNames.getDefinedName().add(ctDefinedName);
workbook.setDefinedNames(definedNames);

I think it could be easier to include all these stuffs directly in the xlsx4j code, in the Worksheet.setAutoFilter method()… we always need to define a DefinedName " _xlnm._FilterDatabase" for it.