我为什么能写入使用LINQ Excel电子表格？ [英] How can I write to an Excel spreadsheet using Linq?

问题描述

我正在写一个应用程序，我需要从数据库中检索某些行并转储到Excel电子表格。我使用LINQ检索这些行。

I'm writing an app where I need to retrieve some rows from a DB and dump them into an Excel spreadsheet. I'm using Linq to retrieve these rows.

时，可以直接倾倒这些行到他们在Excel工作表中的同行（如在Excel中一个单元对应一个单元从DB）？

Is it possible to dump these rows directly into their counterparts in the Excel sheet (where one cell in Excel corresponds to one cell from the DB)?

推荐答案

有连接这两个没有直接的方法。这听起来像你想的LINQ to SQL来处理查询生成，而不是O / R映射（因为Excel不知道如何处理出来LINQ-他们不喜欢看数据行更多的对象做）。你可以通过调用（DataContext的）.GetCommand（yourLinqQueryHere），然后运行做到这一点首先一部分作为CommandText在一个SqlCommand。调用ExecuteReaer（），然后的GetSchemaTable（）来计算出列的顺序。然后，（假设你自动化Excel）合格（DbDataReader）.GetValues​​（）的结果Excel的（表）.Row [X] .Values​​，它会在图示的结果，你可能需要重新排序的东西。如果你没有自动化Excel，你需要倾倒在使用Excel的喷气OLEDB提供程序与一个OleDbConnection的值，或者使用第三方组件生成电子表格。

There's no direct way to connect these two. It sounds like you want LINQ to SQL to handle the query generation, but not the O/R mapping (because Excel wouldn't know what to do with the objects that come out of LINQ- they don't look like data rows any more). You can do that first part by calling (datacontext).GetCommand(yourLinqQueryHere), then running that as the CommandText in a SqlCommand. Call ExecuteReader(), then GetSchemaTable() to figure out the order of the columns. Then (assuming you're automating Excel) pass the result of (DbDataReader).GetValues() to Excel's (Worksheet).Row[x].Values and it'll splat the results in. You might need to reorder stuff. If you're not automating Excel, you'd need to dump the values in using the Jet OLEDB provider for Excel with an OleDbConnection, or use a 3rd party component to generate the spreadsheet.

这篇关于我为什么能写入使用LINQ Excel电子表格？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！