<body><script type="text/javascript"> function setAttributeOnload(object, attribute, val) { if(window.addEventListener) { window.addEventListener('load', function(){ object[attribute] = val; }, false); } else { window.attachEvent('onload', function(){ object[attribute] = val; }); } } </script> <div id="navbar-iframe-container"></div> <script type="text/javascript" src="https://apis.google.com/js/plusone.js"></script> <script type="text/javascript"> gapi.load("gapi.iframes:gapi.iframes.style.bubble", function() { if (gapi.iframes && gapi.iframes.getContext) { gapi.iframes.getContext().openChild({ url: 'https://www.blogger.com/navbar.g?targetBlogID\x3d8211560\x26blogName\x3dTech+Tips,+Tricks+%26+Trivia\x26publishMode\x3dPUBLISH_MODE_BLOGSPOT\x26navbarType\x3dBLUE\x26layoutType\x3dCLASSIC\x26searchRoot\x3dhttp://mvark.blogspot.com/search\x26blogLocale\x3den\x26v\x3d2\x26homepageUrl\x3dhttp://mvark.blogspot.com/\x26vt\x3d-5147029996388199615', where: document.getElementById("navbar-iframe-container"), id: "navbar-iframe" }); } }); </script>

Tech Tips, Tricks & Trivia

by 'Anil' Radhakrishna
An architect's notes, experiments, discoveries and annotated bookmarks.

Search from over a hundred HOW TO articles, Tips and Tricks


"Export to Excel" without using components in a WinForms app

Way back in 2004, I wrote a program to generate a Word file dynamically without using any components in a Web application. The technique I used makes use of the fact that documents can be converted from Word/Excel to HTML (File->Save As) and vice versa.  When you manually save an Office document as a Web page, you can see from its source that it renders some interesting HTML, CSS & Office XML. By understanding and emulating this conversion technique programmatically, we can generate Word or Excel documents through web pages.

This snippet shows how you can generate a report in Excel format in a web application after fetching data from a data source using the Office XML technique -

System.Data.DataTable workTable = new System.Data.DataTable();

//The tablename specified here will be set as the worksheet name of the generated Excel file.
workTable.TableName = "Customers";
workTable.Columns.Add("Id");
workTable.Columns.Add("Name");
System.Data.DataRow workRow;

for (int i = 0; i <= 9; i++)
{
workRow = workTable.NewRow();
workRow[0] = i;
workRow[1] = "CustName" + i.ToString();
workTable.Rows.Add(workRow);
}

string strBody = DataTable2ExcelString(workTable);

Response.AppendHeader("Content-Type", "application/vnd.ms-excel");
Response.AppendHeader("Content-disposition", "attachment; filename=my.xls");
Response.Write(strBody);


A complete working sample & a detailed explanation of the code are available.

A developer wanted to know how this method of converting a datatable to Excel can be adapted for a Windows Forms application.

The trick to generating the Excel file in a web application involves creating a HTML page containing some special CSS & Office XML and triggering a file download with the help of the following Response.AppendHeader statements -
Response.AppendHeader("Content-Type", "application/vnd.ms-excel");
Response.AppendHeader("Content-disposition", "attachment; filename=my.xls");


To use this technique in a Winforms application, the string content representing the HTML page (containing the special CSS & Office XML) needs to be saved as a text file and the file should be given an extension of ".xls". The last 3 lines in the snippet above which are irrelevant in a WinForms app  can be replaced with this line -
System.IO.File.WriteAllText(@"C:\Report.xls", strBody);

Also see: HOW TO send an email with a Word or Excel file attachment built on the fly60

Labels:

Tweet this | Google+ it | Share on FB

« Home | Next »
| Next »
| Next »
| Next »
| Next »
| Next »
| Next »
| Next »
| Next »
| Next »

»

Post a Comment