Write Excel XML Worksheet with LINQ

2 posts, 0 answers
  1. Richard
    Richard avatar
    8 posts
    Member since:
    Sep 2008

    Posted 13 Dec 2010 Link to this post

    I tried the to create an excel xml worksheet and got the following error:
    "Execution of 'System.Xml.Linq.XName:Get(String,String)' on the database server side currently not implemented."

    Did I something wrong or is this error by design?
    Any help appreciated.

    The environment:
    VS2010
    VB.NET
    OpenAccess 2010 Q3

    My class:
    Imports PC
    Imports Telerik.OpenAccess
      
    Imports <xmlns="urn:schemas-microsoft-com:office:spreadsheet">
    Imports <xmlns:o="urn:schemas-microsoft-com:office:office">
    Imports <xmlns:x="urn:x=schemas-microsoft-com:office:excel">
    Imports <xmlns:ss="urn:ss=schemas-microsoft-com:office:spreadsheet">
      
    Public Class ExcelExportViaLINQ
      
        Public Sub MyExport(ByVal pScope As IObjectScope)
      
            Dim _vorschäumers = (From v As Vorschaeumer In pScope.Extent(Of Vorschaeumer)() _
                                 Select <Row>
                                            <Cell><Data ss:Type="String"><%= v.VorsID %></Data></Cell>
                                            <Cell><Data ss:Type="String"><%= v.VorsDateTime %></Data></Cell>
                                            <Cell><Data ss:Type="String"><%= v.VorsMatCode %></Data></Cell>
                                            <Cell><Data ss:Type="Number"><%= v.VorsFillWeight %></Data></Cell>
                                        </Row>
                               )
      
            Dim excelSheet = <?xml version="1.0"?>
                             <?mso-application progid="Excel.Sheet"?>
                             <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
                                 xmlns:o="urn:schemas-microsoft-com:office:office"
                                 xmlns:x="urn:schemas-microsoft-com:office:excel"
                                 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
                                 xmlns:html="http://www.w3.org/TR/REC-html40">
                                 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
                                     <Author>Richard Kling</Author>
                                     <LastAuthor>Richard Kling</LastAuthor>
                                     <Created>2010-12-13T13:55:43Z</Created>
                                     <Company>AdvanTech GmbH</Company>
                                     <Version>11.9999</Version>
                                 </DocumentProperties>
                                 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
                                     <WindowHeight>9360</WindowHeight>
                                     <WindowWidth>12780</WindowWidth>
                                     <WindowTopX>240</WindowTopX>
                                     <WindowTopY>90</WindowTopY>
                                     <ProtectStructure>False</ProtectStructure>
                                     <ProtectWindows>False</ProtectWindows>
                                 </ExcelWorkbook>
                                 <Styles>
                                     <Style ss:ID="Default" ss:Name="Normal">
                                         <Alignment ss:Vertical="Bottom"/>
                                         <Borders/>
                                         <Font/>
                                         <Interior/>
                                         <NumberFormat/>
                                         <Protection/>
                                     </Style>
                                 </Styles>
                                 <Worksheet ss:Name="Tabelle1">
                                     <Table ss:ExpandedColumnCount="4" ss:ExpandedRowCount=<%= _vorschäumers.Count + 1 %> x:FullColumns="1"
                                         x:FullRows="1" ss:DefaultColumnWidth="60">
                                         <Row>
                                             <Cell><Data ss:Type="String">Name</Data></Cell>
                                             <Cell><Data ss:Type="String">Ort</Data></Cell>
                                             <Cell><Data ss:Type="String">Land</Data></Cell>
                                             <Cell><Data ss:Type="String">Alter</Data></Cell>
                                         </Row>
                                         <%= _vorschäumers %>
                                     </Table>
                                     <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
                                         <PageSetup>
                                             <Header x:Margin="0.4921259845"/>
                                             <Footer x:Margin="0.4921259845"/>
                                             <PageMargins x:Bottom="0.984251969" x:Left="0.78740157499999996"
                                                 x:Right="0.78740157499999996" x:Top="0.984251969"/>
                                         </PageSetup>
                                         <Selected/>
                                         <Panes>
                                             <Pane>
                                                 <Number>3</Number>
                                                 <ActiveRow>3</ActiveRow>
                                                 <ActiveCol>3</ActiveCol>
                                             </Pane>
                                         </Panes>
                                         <ProtectObjects>False</ProtectObjects>
                                         <ProtectScenarios>False</ProtectScenarios>
                                     </WorksheetOptions>
                                 </Worksheet>
                                 <Worksheet ss:Name="Tabelle2">
                                     <Table ss:ExpandedColumnCount="0" ss:ExpandedRowCount="0" x:FullColumns="1"
                                         x:FullRows="1" ss:DefaultColumnWidth="60"/>
                                     <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
                                         <PageSetup>
                                             <Header x:Margin="0.4921259845"/>
                                             <Footer x:Margin="0.4921259845"/>
                                             <PageMargins x:Bottom="0.984251969" x:Left="0.78740157499999996"
                                                 x:Right="0.78740157499999996" x:Top="0.984251969"/>
                                         </PageSetup>
                                         <ProtectObjects>False</ProtectObjects>
                                         <ProtectScenarios>False</ProtectScenarios>
                                     </WorksheetOptions>
                                 </Worksheet>
                                 <Worksheet ss:Name="Tabelle3">
                                     <Table ss:ExpandedColumnCount="0" ss:ExpandedRowCount="0" x:FullColumns="1"
                                         x:FullRows="1" ss:DefaultColumnWidth="60"/>
                                     <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
                                         <PageSetup>
                                             <Header x:Margin="0.4921259845"/>
                                             <Footer x:Margin="0.4921259845"/>
                                             <PageMargins x:Bottom="0.984251969" x:Left="0.78740157499999996"
                                                 x:Right="0.78740157499999996" x:Top="0.984251969"/>
                                         </PageSetup>
                                         <ProtectObjects>False</ProtectObjects>
                                         <ProtectScenarios>False</ProtectScenarios>
                                     </WorksheetOptions>
                                 </Worksheet>
                             </Workbook>
      
            excelsheet.save("C:\Temp\Sheet.xml")
            Process.Start("C:\Temp\Sheet.xml")
      
      
        End Sub
      
    End Class
  2. Damyan Bogoev
    Admin
    Damyan Bogoev avatar
    581 posts

    Posted 15 Dec 2010 Link to this post

    Hello Richard,

    I am afraid that Telerik OpenAccess ORM does not support executing queries on the server which use xml declaration in the select statement.
    You could retrieve the data from the database and later on use Linq to Objects to accomplish this goal:

    Dim _vorschaumers = (
    From v As Vorschaeumer In pScope.Extent(Of Vorschaeumer)().ToList()
    _
                         Select <Row>
                                    <Cell><Data ss:Type="String"><%= v.VorsID %></Data></Cell>
                                    <Cell><Data ss:Type="String"><%= v.VorsDateTime %></Data></Cell>
                                    <Cell><Data ss:Type="String"><%= v.VorsMatCode %></Data></Cell>
                                    <Cell><Data ss:Type="Number"><%= v.VorsFillWeight %></Data></Cell>
                                </Row>
                        )

    The ToList method will execute the query on the server.

    Hope that helps.

    All the best,
    Damyan Bogoev
    the Telerik team
    Accelerate your learning with industry's first Telerik OpenAccess ORM SDK. Download today.
  3. DevCraft banner
Back to Top