C# 如何使用 Linq 写入 Excel 电子表格？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1527790/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I write to an Excel spreadsheet using Linq?
提问by
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 电子表格中。我正在使用 Linq 来检索这些行。
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)?
是否可以将这些行直接转储到 Excel 工作表中的对应行中（其中 Excel 中的一个单元格对应于 DB 中的一个单元格）？
采纳答案by nitzmahone
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 to SQL 来处理查询生成，而不是 O/R 映射（因为 Excel 不知道如何处理来自 LINQ 的对象——它们看起来不再像数据行了）。您可以通过调用 (datacontext).GetCommand(yourLinqQueryHere) 来完成第一部分，然后将其作为 SqlCommand 中的 CommandText 运行。调用 ExecuteReader()，然后调用 GetSchemaTable() 以确定列的顺序。然后（假设您正在自动化 Excel）将 (DbDataReader).GetValues() 的结果传递给 Excel 的 (Worksheet).Row[x].Values，它会将结果放入。您可能需要重新排序。如果您不自动化 Excel，则需要使用 OleDbConnection 转储 Excel 的 Jet OLEDB 提供程序中的值，
回答by Amirshk
The quickest solution will be to create a csv file:
最快的解决方案是创建一个 csv 文件：
col1, colb, colc
col1, colb, colc
Excel works very well with csv files.
Excel 可以很好地处理 csv 文件。
回答by Nasir
You can:
你可以：
- Create a CSV file, which is associated with Excel on most systems.
- Create Excel Worksheet object and populate it manually. Google Search
- Using an XLS/XLSX generating Control. I have use Spire.XLS from http://www.e-iceblue.com/xls/xlsintro.htm
- 创建一个 CSV 文件，该文件在大多数系统上与 Excel 相关联。
- 创建 Excel Worksheet 对象并手动填充它。谷歌搜索
- 使用 XLS/XLSX 生成控件。我使用了来自http://www.e-iceblue.com/xls/xlsintro.htm 的Spire.XLS
回答by Nathan Taylor
Take a look at this Excel Data Object Provider. I haven't personally used the writing functionality of it and I adapted the reading support to allow for ordinal (as well as named) column identifiers, but it may be a step in the right direction. Keep in mind that you cannot write or read from XLSX files unless Excel 2003+ is installed on the target machine; standard XLS files will work on any Windows box though.
看看这个Excel 数据对象提供程序。我个人没有使用过它的写入功能，我调整了读取支持以允许顺序（以及命名）列标识符，但这可能是朝着正确方向迈出的一步。请记住，除非目标计算机上安装了 Excel 2003+，否则您无法写入或读取 XLSX 文件；不过，标准 XLS 文件将适用于任何 Windows 机器。
My adapted version with ordinal columns can be found here. You may find it necessary/helpful to implement that in the current version (at the above link) if you decide to use the code. My version is a hybrid of features from the version 2.0and 2.5- it has all of the reading functionality (with some 2.5 upgrades), but no writing. Oh- and unlike either version 2.0 or 2.5, my version doesn't require that the first sheet in the Excel document be named "Sheet1".
我的带有序号列的改编版本可以在这里找到。如果您决定使用代码，您可能会发现在当前版本（在上面的链接中）中实现它是必要的/有帮助的。我的版本是2.0 版和 2.5版功能的混合 -它具有所有读取功能（有一些 2.5 升级），但没有写入功能。哦，与 2.0 或 2.5 版本不同，我的版本不要求将 Excel 文档中的第一个工作表命名为“Sheet1”。
Hope that helps!
希望有帮助！
回答by Larsenal
The fact that you're retrieving your data with LINQ is sort of irrelevant. What you're really after is a good library to write Excel. Once you've got that, you can simply iterate through your results to create rows in your Excel worksheet.
您使用 LINQ 检索数据这一事实与此无关。您真正想要的是编写 Excel 的好库。完成后，您可以简单地遍历结果以在 Excel 工作表中创建行。
As far as a library I've used NPOI, and it's been great.
至于我使用过的图书馆NPOI，它很棒。
回答by bounav
I am personally not a big fan of using libraries to such things as I always find it limiting at some point later...
我个人不是很喜欢使用库来做这样的事情，因为我总是发现它在某些时候会受到限制......
I used reflection in order to generate the column headers and get the cell values of each row. And if you are using .NET framework 3.5 your can take advantage of extensions methods so that you can export any IEnumerable<object>to an excel XDocument file.
我使用反射来生成列标题并获取每行的单元格值。如果您使用 .NET framework 3.5，您可以利用扩展方法，以便您可以将任何扩展方法导出IEnumerable<object>到 Excel XDocument 文件。
Here is how I did it:
这是我如何做到的：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Xml.Linq;
namespace YourNameSpace
{
public static class ExcelExportExtensions
{
public static XDocument ToExcelXml(this IEnumerable<object> rows)
{
return rows.ToExcelXml("Sheet1");
}
public static XDocument ToExcelXml(this IEnumerable<object> rows, string sheetName)
{
sheetName = sheetName.Replace("/", "-");
sheetName = sheetName.Replace("\", "-");
XNamespace mainNamespace = "urn:schemas-microsoft-com:office:spreadsheet";
XNamespace o = "urn:schemas-microsoft-com:office:office";
XNamespace x = "urn:schemas-microsoft-com:office:excel";
XNamespace ss = "urn:schemas-microsoft-com:office:spreadsheet";
XNamespace html = "http://www.w3.org/TR/REC-html40";
XDocument xdoc = new XDocument(new XDeclaration("1.0", "utf-8", "yes"));
var headerRow = from p in rows.First().GetType().GetProperties()
select new XElement(mainNamespace + "Cell",
new XElement(mainNamespace + "Data",
new XAttribute(ss + "Type", "String"), p.Name)); //Generate header using reflection
XElement workbook = new XElement(mainNamespace + "Workbook",
new XAttribute(XNamespace.Xmlns + "html", html),
new XAttribute(XName.Get("ss", "http://www.w3.org/2000/xmlns/"), ss),
new XAttribute(XName.Get("o", "http://www.w3.org/2000/xmlns/"), o),
new XAttribute(XName.Get("x", "http://www.w3.org/2000/xmlns/"), x),
new XAttribute(XName.Get("xmlns", ""), mainNamespace),
new XElement(o + "DocumentProperties",
new XAttribute(XName.Get("xmlns", ""), o),
new XElement(o + "Author", "Smartdesk Systems Ltd"),
new XElement(o + "LastAuthor", "Smartdesk Systems Ltd"),
new XElement(o + "Created", DateTime.Now.ToString())
), //end document properties
new XElement(x + "ExcelWorkbook",
new XAttribute(XName.Get("xmlns", ""), x),
new XElement(x + "WindowHeight", 12750),
new XElement(x + "WindowWidth", 24855),
new XElement(x + "WindowTopX", 240),
new XElement(x + "WindowTopY", 75),
new XElement(x + "ProtectStructure", "False"),
new XElement(x + "ProtectWindows", "False")
), //end ExcelWorkbook
new XElement(mainNamespace + "Styles",
new XElement(mainNamespace + "Style",
new XAttribute(ss + "ID", "Default"),
new XAttribute(ss + "Name", "Normal"),
new XElement(mainNamespace + "Alignment",
new XAttribute(ss + "Vertical", "Bottom")
),
new XElement(mainNamespace + "Borders"),
new XElement(mainNamespace + "Font",
new XAttribute(ss + "FontName", "Calibri"),
new XAttribute(x + "Family", "Swiss"),
new XAttribute(ss + "Size", "11"),
new XAttribute(ss + "Color", "#000000")
),
new XElement(mainNamespace + "Interior"),
new XElement(mainNamespace + "NumberFormat"),
new XElement(mainNamespace + "Protection")
),
new XElement(mainNamespace + "Style",
new XAttribute(ss + "ID", "Header"),
new XElement(mainNamespace + "Font",
new XAttribute(ss + "FontName", "Calibri"),
new XAttribute(x + "Family", "Swiss"),
new XAttribute(ss + "Size", "11"),
new XAttribute(ss + "Color", "#000000"),
new XAttribute(ss + "Bold", "1")
)
)
), // close styles
new XElement(mainNamespace + "Worksheet",
new XAttribute(ss + "Name", sheetName /* Sheet name */),
new XElement(mainNamespace + "Table",
new XAttribute(ss + "ExpandedColumnCount", headerRow.Count()),
new XAttribute(ss + "ExpandedRowCount", rows.Count() + 1),
new XAttribute(x + "FullColumns", 1),
new XAttribute(x + "FullRows", 1),
new XAttribute(ss + "DefaultRowHeight", 15),
new XElement(mainNamespace + "Column",
new XAttribute(ss + "Width", 81)
),
new XElement(mainNamespace + "Row", new XAttribute(ss + "StyleID", "Header"), headerRow),
from contentRow in rows
select new XElement(mainNamespace + "Row",
new XAttribute(ss + "StyleID", "Default"),
from p in contentRow.GetType().GetProperties()
select new XElement(mainNamespace + "Cell",
new XElement(mainNamespace + "Data", new XAttribute(ss + "Type", "String"), p.GetValue(contentRow, null))) /* Build cells using reflection */ )
), //close table
new XElement(x + "WorksheetOptions",
new XAttribute(XName.Get("xmlns", ""), x),
new XElement(x + "PageSetup",
new XElement(x + "Header",
new XAttribute(x + "Margin", "0.3")
),
new XElement(x + "Footer",
new XAttribute(x + "Margin", "0.3")
),
new XElement(x + "PageMargins",
new XAttribute(x + "Bottom", "0.75"),
new XAttribute(x + "Left", "0.7"),
new XAttribute(x + "Right", "0.7"),
new XAttribute(x + "Top", "0.75")
)
),
new XElement(x + "Print",
new XElement(x + "ValidPrinterInfo"),
new XElement(x + "HorizontalResolution", 600),
new XElement(x + "VerticalResolution", 600)
),
new XElement(x + "Selected"),
new XElement(x + "Panes",
new XElement(x + "Pane",
new XElement(x + "Number", 3),
new XElement(x + "ActiveRow", 1),
new XElement(x + "ActiveCol", 0)
)
),
new XElement(x + "ProtectObjects", "False"),
new XElement(x + "ProtectScenarios", "False")
) // close worksheet options
) // close Worksheet
);
xdoc.Add(workbook);
return xdoc;
}
}
}
I have also created another extention method to ease return the XDocument in web scenarios:
我还创建了另一种扩展方法来简化 Web 场景中的 XDocument 返回：
public static DownloadableFile ToDownloadableXmlFileForExcel2003(this System.Xml.Linq.XDocument file, string fileName)
{
MemoryStream ms = new MemoryStream();
XmlWriterSettings xmlWriterSettings = new XmlWriterSettings() { Encoding = Encoding.UTF8 };
XmlWriter xmlWriter = XmlWriter.Create(ms, xmlWriterSettings);
file.Save(xmlWriter); //.Save() adds the <xml /> header tag!
xmlWriter.Close(); //Must close the writer to dump it's content its output (the memory stream)
DownloadableFile dbf =
new DownloadableFile
{
FileName = String.Format("{0}.xls", fileName.Replace(" ", "")),
Content = ms.ToArray(),
MimeType = "application/vnd.ms-excel"
};
ms.Close();
ms.Dispose();
return dbf;
}
Hope this helps!
希望这可以帮助！
