From a web application, is there a simple way to export a list of LINQ objects to an Excel file? Are there any good libraries that can do this?
2
-
1possible duplicate of How to Export data to Excel using LINQ to Entity?Dave Swersky– Dave Swersky2011-01-19 15:50:13 +00:00Commented Jan 19, 2011 at 15:50
-
I would imagine you are going to have to use ado.net, Check out some similar questions stackoverflow.com/questions/3486278/c-export-to-excel, stackoverflow.com/questions/151005/…. Cheersmsarchet– msarchet2011-01-19 15:52:17 +00:00Commented Jan 19, 2011 at 15:52
Add a comment
|
3 Answers
This is the Excel Export I ended up with based on the link to the VB video above. It takes any List of Objects (It excludes navigation properties and collections on Entity Framework objects) and exports them to Excel. It exports about 35K records in ~4 seconds.
public void ExportToExcel<T>(List<T> list)
{
int columnCount = 0;
DateTime StartTime = DateTime.Now;
StringBuilder rowData = new StringBuilder();
PropertyInfo[] properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
rowData.Append("<Row ss:StyleID=\"s62\">");
foreach (PropertyInfo p in properties)
{
if (p.PropertyType.Name != "EntityCollection`1" && p.PropertyType.Name != "EntityReference`1" && p.PropertyType.Name != p.Name)
{
columnCount++;
rowData.Append("<Cell><Data ss:Type=\"String\">" + p.Name + "</Data></Cell>");
}
else
break;
}
rowData.Append("</Row>");
foreach (T item in list)
{
rowData.Append("<Row>");
for (int x = 0; x < columnCount; x++) //each (PropertyInfo p in properties)
{
object o = properties[x].GetValue(item, null);
string value = o == null ? "" : o.ToString();
rowData.Append("<Cell><Data ss:Type=\"String\">" + value + "</Data></Cell>");
}
rowData.Append("</Row>");
}
var sheet = @"<?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>MSADMIN</Author>
<LastAuthor>MSADMIN</LastAuthor>
<Created>2011-07-12T23:40:11Z</Created>
<Company>Microsoft</Company>
<Version>12.00</Version>
</DocumentProperties>
<ExcelWorkbook xmlns=""urn:schemas-microsoft-com:office:excel"">
<WindowHeight>6600</WindowHeight>
<WindowWidth>12255</WindowWidth>
<WindowTopX>0</WindowTopX>
<WindowTopY>60</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID=""Default"" ss:Name=""Normal"">
<Alignment ss:Vertical=""Bottom""/>
<Borders/>
<Font ss:FontName=""Calibri"" x:Family=""Swiss"" ss:Size=""11"" ss:Color=""#000000""/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID=""s62"">
<Font ss:FontName=""Calibri"" x:Family=""Swiss"" ss:Size=""11"" ss:Color=""#000000""
ss:Bold=""1""/>
</Style>
</Styles>
<Worksheet ss:Name=""Sheet1"">
<Table ss:ExpandedColumnCount=""" + (properties.Count() + 1) + @""" ss:ExpandedRowCount=""" + (list.Count() + 1) + @""" x:FullColumns=""1""
x:FullRows=""1"" ss:DefaultRowHeight=""15"">
" + rowData.ToString() +@"
</Table>
<WorksheetOptions xmlns=""urn:schemas-microsoft-com:office:excel"">
<PageSetup>
<Header x:Margin=""0.3""/>
<Footer x:Margin=""0.3""/>
<PageMargins x:Bottom=""0.75"" x:Left=""0.7"" x:Right=""0.7"" x:Top=""0.75""/>
</PageSetup>
<Print>
<ValidPrinterInfo/>
<HorizontalResolution>300</HorizontalResolution>
<VerticalResolution>300</VerticalResolution>
</Print>
<Selected/>
<Panes>
<Pane>
<Number>3</Number>
<ActiveCol>2</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name=""Sheet2"">
<Table ss:ExpandedColumnCount=""1"" ss:ExpandedRowCount=""1"" x:FullColumns=""1""
x:FullRows=""1"" ss:DefaultRowHeight=""15"">
</Table>
<WorksheetOptions xmlns=""urn:schemas-microsoft-com:office:excel"">
<PageSetup>
<Header x:Margin=""0.3""/>
<Footer x:Margin=""0.3""/>
<PageMargins x:Bottom=""0.75"" x:Left=""0.7"" x:Right=""0.7"" x:Top=""0.75""/>
</PageSetup>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name=""Sheet3"">
<Table ss:ExpandedColumnCount=""1"" ss:ExpandedRowCount=""1"" x:FullColumns=""1""
x:FullRows=""1"" ss:DefaultRowHeight=""15"">
</Table>
<WorksheetOptions xmlns=""urn:schemas-microsoft-com:office:excel"">
<PageSetup>
<Header x:Margin=""0.3""/>
<Footer x:Margin=""0.3""/>
<PageMargins x:Bottom=""0.75"" x:Left=""0.7"" x:Right=""0.7"" x:Top=""0.75""/>
</PageSetup>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>";
System.Diagnostics.Debug.Print(StartTime.ToString() + " - " + DateTime.Now);
System.Diagnostics.Debug.Print((DateTime.Now - StartTime).ToString());
string attachment = "attachment; filename=Report.xml";
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.AddHeader("content-disposition", attachment);
HttpContext.Current.Response.Write(sheet);
HttpContext.Current.Response.ContentType = "application/ms-excel";
HttpContext.Current.Response.End();
}
1 Comment
user1477388
I wish I could plus you more than 1 point for this code; great job. Thanks.
Check out the VB team's videos linked from the ASP.Net, Beth Massi actually does a very similar demo that might do what you want:
http://www.asp.net/linq/videos/how-do-i-create-excel-spreadsheets-using-linq-to-xml
You might find others in the same series useful, they are here about 4/5 down the page:
http://www.asp.net/web-forms/data
There is also a project called Linq-To-Excel which is here - http://code.google.com/p/linqtoexcel/
Or you could use the OpenXML libraries to do this sort of thing, here is one such example - http://msdn.microsoft.com/en-us/library/bb508943(v=office.12).aspx
Excel can also open XML files directly, so you could just create an XML output using XML serialisation or whatever other method, and open it in Excel.
Comments
So, in specifying Excel, you're committing to a row / column paradigm (as opposed to xml, for example). So you'll have to specify how the properties map to columns.
Beyond that you write to an Excel file using the Ole DB provider. Loop through your objects, generating an INSERT statement for each one using a stringbuilder, and execute that against your sheet. Easy as cake.
