This is a migrated thread and some comments may be shown as answers.

Write Excel XML Worksheet with LINQ

1 Answer 184 Views

LINQ (LINQ specific questions)

This is a migrated thread and some comments may be shown as answers.

This question is locked. New answers and comments are not allowed.

Richard asked on 13 Dec 2010, 04:21 PM

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

1 Answer, 1 is accepted

answered on 15 Dec 2010, 03:02 PM

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.