This project has moved. For the latest updates, please go here.

Autofilter on multiple sheets : unreadable content

Dec 21, 2012 at 2:07 PM

Hi,

I started using this great component to export data to excel and I'm having a small issue when trying to set autofilters on multiple sheets in the same workbook. 

I get an "unreadable content" warning when opening the file in excel, yet the filters work just fine.  The problem seems to be in the definedNames section of the xl/workbook.xml.

Here's the bad xml : 

<workbook xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <bookViews>
    <workbookView />
  </bookViews>
  <sheets>
    <sheet name="Code magasins doublons" sheetId="1" r:id="R1472518796f14431" />
    <sheet name="Quantité d'équipements " sheetId="2" r:id="Rb8ad4bd3047f4ffe" />
    <sheet name="Portrait du parc d'équipements" sheetId="3" r:id="R38fbefa2ba02414a" />
  </sheets>
  <definedNames>
    <definedName name="_xlnm._FilterDatabase" localSheetId="0" hidden="1">'Code magasins doublons'!$A$7:$F$21</definedName>
    <definedName name="_xlnm._FilterDatabase" localSheetId="1" hidden="1">'Quantité d'équipements '!$A$7:$I$443</definedName>
    <definedName name="_xlnm._FilterDatabase" localSheetId="2" hidden="1">'Portrait du parc d'équipements'!$A$7:$B$12</definedName>
  </definedNames>
</workbook>
And the "repaired by excel" version :
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
  <fileVersion appName="xl" lastEdited="5" lowestEdited="5" rupBuild="9302"/>
  <workbookPr defaultThemeVersion="124226"/>
  <bookViews>
    <workbookView xWindow="330" yWindow="450" windowWidth="28200" windowHeight="12195"/>
  </bookViews>
  <sheets>
    <sheet name="Code magasins doublons" sheetId="1" r:id="rId1"/>
    <sheet name="Quantité d'équipements " sheetId="2" r:id="rId2"/>
    <sheet name="Portrait du parc d'équipements" sheetId="3" r:id="rId3"/>
  </sheets>
  <definedNames>
    <definedName name="_xlnm._FilterDatabase" localSheetId="0" hidden="1">'Code magasins doublons'!$A$7:$F$21</definedName>
  </definedNames>
  <calcPr calcId="0"/>
  <fileRecoveryPr repairLoad="1"/>
</workbook>
Anf finally the VB code that produces the file :
 Public Function export(ByVal firstRow As Integer, ByVal lastColMin As Integer, ByVal logo As FileInfo, ByVal logoSize As Integer) As String

        Dim fi As New FileInfo(Me.dir & "\" & Me.fileName)

        Dim lastCol As Integer

        Dim pck As New ExcelPackage(fi)

        Dim i As Integer = firstRow
        Dim k As Integer = 1

        Dim r As rapport = DirectCast(Me.source, rapport)

        r.loadOnglets()

        Dim dt As DataTable
        Dim ws As ExcelWorksheet

        For Each o As ongletRapport In r.onglets

            ws = pck.Workbook.Worksheets.Add(HttpContext.GetGlobalResourceObject(Me.resourcePrefix, o.TITRE & "ABG"))

            ws.Drawings.AddPicture(o.TITRE & "_logo", logo)
            ws.Drawings(o.TITRE & "_logo").SetPosition(0, 0)
            ws.Drawings(o.TITRE & "_logo").SetSize(logoSize)

            k = 1
            i = firstRow + 2

            dt = o.getData(r.parametres)

            For Each c As DataColumn In dt.Columns

                ws.Cells(i, k).Value = HttpContext.GetGlobalResourceObject(Me.resourcePrefix, c.ColumnName)
                ws.Cells(i, k).Style.Font.Bold = True

                lastCol = k

                k += 1

            Next c

            i += 1

            For Each row As DataRow In dt.Rows

                k = 1

                For Each c As DataColumn In dt.Columns

                    ws.Cells(i, k).Value = row(c.ColumnName)

                    k += 1

                Next c

                i += 1

            Next row

            ws.Cells(getColIndex(1) & (firstRow + 2).ToString() & ":" & getColIndex(lastCol) & (i - 1).ToString()).AutoFilter = True

            If lastCol < lastColMin Then

                lastCol = lastColMin

            End If

            For j As Integer = 1 To lastCol

                ws.Column(j).AutoFit()

            Next

            ws.View.FreezePanes(firstRow + 3, lastCol + 1)

        Next o

        pck.Save()

        pck.Dispose()

        Return pck.File.FullName

    End Function
Is this a bug or am I doing something wrong ? Any help would be greatly appreciated !