Hi Everyone…
I have been exporting data from dataset to excel using nested loops, excel object, accessing each cell of excel sheet and each cell of dataset and so on and so forth until I find this……
Before writing about it a special thanks to Peter A. Bromberg for writing such a nice article which really made at least my life easy….:). In his article he wrote a very simple function to export data without using excel object and other messy stuff. XSL Transformation is applied to dataset and XML for excel is generated.
Below is the complete function which will write an Excel file to your local disk. You have to pass it the DataSet to export and path to where file should be generated.
public static void CreateWorkbook(DataSet ds, String path)
{
   XmlDataDocument xmlDataDoc = new XmlDataDocument(ds);
   XslTransform xt = new XslTransform();
   StreamReader reader =new StreamReader(typeof (WorkbookEngine).Assembly.GetManifestResourceStream(typeof (WorkbookEngine), “Excel.xsl”));
   XmlTextReader xRdr = new XmlTextReader(reader);
   xt.Load(xRdr, null, null);
   StringWriter sw = new StringWriter();
   xt.Transform(xmlDataDoc, null, sw, null);
   StreamWriter myWriter = new StreamWriter (path + “\\Report.xls“);
   myWriter.Write (sw.ToString());
   myWriter.Close ();
}
and thats it. Your DataSet has been exported to an Excel file which is saved at the path passed to this function.