Best IT Recipes

How to write an Excel spreadsheet using Linq?

I am writing an application where I need to get some rows from a database and upload them to an Excel spreadsheet. I use Linq to extract these lines.

Is it possible to discard these lines directly in my instances on an Excel worksheet (where one cell in Excel corresponds to one cell from the database)?

+9
c # linq excel interop
06 Oct '09 at 20:03
source share
7 answers

There is no direct way to connect these two. It looks like you want LINQ to SQL to handle query generation, but not display O / R mapping (since Excel did not know what to do with objects coming out of LINQ, they no longer look like data rows). You can do this first part by calling (datacontext) .GetCommand (yourLinqQueryHere) and then running it as a CommandText in SqlCommand. Call ExecuteReader (), then GetSchemaTable () to find out the order of the columns. Then (assuming you automate Excel) pass the result (DbDataReader). GetValues ​​() to Excel (Worksheet) .Row [x] .Values ​​and it will display the results. You may need to reorder the material. If you do not automate Excel, you need to reset the values ​​using the OLEDB Jet provider for Excel using OleDbConnection or use a third-party component to create the spreadsheet.

+5
Oct 06 '09 at 20:27
source share

I personally am not a big fan of using libraries for things like I always think that this is a limitation at some point later ...

I used reflection to create column headers and get the cell values ​​of each row. And if you are using the .NET framework 3.5, you can use extension methods to export any IEnumerable<object> to an excel XDocument file.

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 also created another extension method to facilitate the return of an XDocument in a web script:

 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 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!

+17
09 Oct '09 at 14:32
source share

You can:

+1
Oct 06 '09 at 20:13
source share

Take a look at the Excel Data Object Provider . I personally did not use the writing functions, and I adapted the reading support to be able to specify ordinal (as well as named) column identifiers, but this could be a step in the right direction. Keep in mind that you cannot write or read XLSX files if Excel 2003+ is not installed on the target machine; standard XLS files will work in any Windows window.

My adapted version with ordinal columns is here . It may be necessary / useful for you to implement this in the current version (from the link above) if you decide to use this code. My version is a hybrid of functions from versions 2.0 and 2.5 - it has all the reading functions (with some 2.5 updates), but no letter. Oh, and unlike version 2.0 or 2.5, my version does not require the first sheet in an Excel document to be called "Sheet1".

Hope this helps!

+1
Oct 06 '09 at 20:23
source share

Writing to Excel XML with LINQ to XML

Surprisingly simple, does not require external libraries and uses reflection to turn any IEnumerable into a worksheet!

http://scottstjohn.wordpress.com/2011/04/02/write-to-excel-xml-with-linq-to-xml/

As described by the author, Scott St. John (I guess this is at his URL - he does not have bio information on his site):

I found a really good extension class to write any IEnumerable in Excel XML. See C # in Excel using Linq by bounav. I made a couple of modifications because I wanted to be able to add new IEnumerables as worksheets. See Example and Source Code below.

I pretty much copied / pasted my code into MyExtensions in LINQPad and used it right away.

+1
Jun 26 '14 at 0:44
source share

The quickest solution is to create a csv file:

 col1, colb, colc col1, colb, colc

Excel works great with csv files.

0
Oct 06 '09 at 20:08
source share

The fact that you are retrieving data using LINQ does not matter. What you really need is a good library for writing Excel. Once you get this, you can simply iterate over the results to create rows in an Excel worksheet.

As for the library, I used NPOI , and that was great.

0
Oct 06 '09 at 20:24
source share


More articles:


All Articles