Save Data from Dataset into Excel File‏

Response.ContentType = "application/vnd.ms-excel";
//remove the charset from the content-type header. Response.Charset = "";
System.Text.StringBuilder strOutput = new System.Text.StringBuilder();
strOutput.Append("<?xml version=\"1.0\"?><?mso-application progid=\"Excel.Sheet\"?>");
strOutput.Append("<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\">");
strOutput.Append("<ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">");
strOutput.Append("</ExcelWorkbook>");
strOutput.Append("<Styles>");
strOutput.Append("<Style ss:ID=\"Default\" ss:Name=\"Normal\">");
strOutput.Append("<Alignment ss:Vertical=\"Bottom\" />");
strOutput.Append("<Borders/>");
strOutput.Append("<Font/>");
strOutput.Append("<Interior/>");
strOutput.Append("<NumberFormat/>");
strOutput.Append("<Protection/>");
strOutput.Append("</Style>");
strOutput.Append("<Style ss:ID=\"m52377878\">");
strOutput.Append("<Alignment ss:Vertical=\"Top\" ss:WrapText=\"1\"/>");
strOutput.Append("<Borders>");
strOutput.Append("<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
strOutput.Append("<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
strOutput.Append("</Borders>");
strOutput.Append("<Font x:Family=\"Swiss\" ss:Bold=\"1\"/>");
strOutput.Append("<Interior ss:Color=\"#C0C0C0\" ss:Pattern=\"Solid\"/>");
strOutput.Append("</Style>");
strOutput.Append("<Style ss:ID=\"m52377888\">");
strOutput.Append("<Alignment ss:Vertical=\"Top\" ss:WrapText=\"1\"/>");
strOutput.Append("<Borders>");
strOutput.Append("<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
strOutput.Append("<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
strOutput.Append("<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
strOutput.Append("</Borders>");
strOutput.Append("</Style>");
strOutput.Append("<Style ss:ID=\"m52388202\">");
strOutput.Append("<Alignment ss:Vertical=\"Top\" ss:WrapText=\"1\"/>");
strOutput.Append("<Borders>");
strOutput.Append("<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
strOutput.Append("<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
strOutput.Append("</Borders>");
strOutput.Append("<Font x:Family=\"Swiss\" ss:Bold=\"1\"/>");
strOutput.Append("<Interior ss:Color=\"#C0C0C0\" ss:Pattern=\"Solid\"/>");
strOutput.Append("</Style>");
strOutput.Append("<Style ss:ID=\"m52388212\">");
strOutput.Append("<Alignment ss:Vertical=\"Top\" ss:WrapText=\"1\"/>");
strOutput.Append("<Borders>");
strOutput.Append("<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
strOutput.Append("<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
strOutput.Append("<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
strOutput.Append("</Borders>");
strOutput.Append("</Style>");
strOutput.Append("<Style ss:ID=\"s25\">");
strOutput.Append("<Alignment ss:Vertical=\"Top\" ss:WrapText=\"1\"/>");
strOutput.Append("<Borders>");
strOutput.Append("<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
strOutput.Append("</Borders>");
strOutput.Append("</Style>");
strOutput.Append("<Style ss:ID=\"s26\">");
strOutput.Append("<Borders/>");
strOutput.Append("</Style>");
strOutput.Append("<Style ss:ID=\"s27\">");
strOutput.Append("<Alignment ss:Vertical=\"Top\" ss:WrapText=\"1\"/>");
strOutput.Append("<Borders>");
strOutput.Append("<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
strOutput.Append("</Borders>");
strOutput.Append("</Style>");
strOutput.Append("<Style ss:ID=\"s34\">");
strOutput.Append("<Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Bottom\" ss:WrapText=\"1\"/>");
strOutput.Append("<Font x:Family=\"Swiss\" ss:Size=\"12\" ss:Bold=\"1\"/>");
strOutput.Append("</Style>");
strOutput.Append("<Style ss:ID=\"s41\">");
strOutput.Append("<Borders>");
strOutput.Append("<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
strOutput.Append("</Borders>");
strOutput.Append("<Font x:Family=\"Swiss\" ss:Bold=\"1\"/>");
strOutput.Append("<Interior ss:Color=\"#969696\" ss:Pattern=\"Solid\"/>");
strOutput.Append("</Style>");
strOutput.Append("<Style ss:ID=\"s42\">");
strOutput.Append("<Alignment ss:Vertical=\"Top\" ss:WrapText=\"1\"/>");
strOutput.Append("<Borders/>");
strOutput.Append("<Font x:Family=\"Swiss\" ss:Bold=\"1\"/>");
strOutput.Append("<Interior ss:Color=\"#969696\" ss:Pattern=\"Solid\"/>");
strOutput.Append("</Style>");
strOutput.Append("<Style ss:ID=\"s43\">");
strOutput.Append("<Alignment ss:Vertical=\"Top\" ss:WrapText=\"1\"/>");
strOutput.Append("<Borders>");
strOutput.Append("<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
strOutput.Append("</Borders>");
strOutput.Append("<Font x:Family=\"Swiss\" ss:Bold=\"1\"/>");
strOutput.Append("<Interior ss:Color=\"#969696\" ss:Pattern=\"Solid\"/>");
strOutput.Append("</Style>");
strOutput.Append("<Style ss:ID=\"s47\">");
strOutput.Append("<Alignment ss:Vertical=\"Top\" ss:WrapText=\"1\"/>");
strOutput.Append("<Borders>");
strOutput.Append("<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
strOutput.Append("</Borders>");
strOutput.Append("<Interior ss:Color=\"#C0C0C0\" ss:Pattern=\"Solid\"/>");
strOutput.Append("</Style>");
strOutput.Append("<Style ss:ID=\"s48\">");
strOutput.Append("<Alignment ss:Vertical=\"Top\" ss:WrapText=\"1\"/>");
strOutput.Append("<Borders/>");
strOutput.Append("<Interior ss:Color=\"#C0C0C0\" ss:Pattern=\"Solid\"/>");
strOutput.Append("</Style>");
strOutput.Append("<Style ss:ID=\"s49\">");
strOutput.Append("<Alignment ss:Vertical=\"Top\" ss:WrapText=\"1\"/>");
strOutput.Append("<Borders>");
strOutput.Append("<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
strOutput.Append("</Borders>");
strOutput.Append("<Interior ss:Color=\"#C0C0C0\" ss:Pattern=\"Solid\"/>");
strOutput.Append("</Style>");
strOutput.Append("<Style ss:ID=\"s50\">");
strOutput.Append("<Alignment ss:Vertical=\"Top\" ss:WrapText=\"1\"/>");
strOutput.Append("<Interior ss:Color=\"#C0C0C0\" ss:Pattern=\"Solid\"/>");
strOutput.Append("</Style>");
strOutput.Append("<Style ss:ID=\"s51\">");
strOutput.Append("<Alignment ss:Vertical=\"Top\" ss:WrapText=\"1\"/>");
strOutput.Append("<Borders>");
strOutput.Append("<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
strOutput.Append("<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
strOutput.Append("</Borders>");
strOutput.Append("<Font x:Family=\"Swiss\" ss:Bold=\"1\"/>");
strOutput.Append("<Interior ss:Color=\"#C0C0C0\" ss:Pattern=\"Solid\"/>");
strOutput.Append("</Style>");
strOutput.Append("</Styles>");
strOutput.Append("<Worksheet ss:Name=\"Standard Status Report\">");
strOutput.Append("<Table ss:ExpandedColumnCount=\"6\" x:FullColumns=\"1\" x:FullRows=\"1\">");
strOutput.Append("<Column ss:Width=\"108.75\"/>");
strOutput.Append("<Column ss:Width=\"339.75\"/>");
strOutput.Append("<Column ss:Width=\"54\"/>");
strOutput.Append("<Column ss:Width=\"131.25\"/>");
strOutput.Append("<Column ss:Width=\"76.5\"/>");
strOutput.Append("<Column ss:Width=\"71.25\"/>");
//report heading strOutput.Append("<Row ss:Height=\"15.75\">");
strOutput.Append("<Cell ss:MergeAcross=\"5\" ss:StyleID=\"s34\"><Data ss:Type=\"String\">Standard Status Report</Data></Cell>");
strOutput.Append("</Row>");
strOutput.Append("<Row ss:Height=\"13.5\">");
strOutput.Append("<Cell ss:MergeAccross=\"5\"><Data ss:Type=\"String\"></Data></Cell>");
strOutput.Append("</Row>");
foreach(DataRow drStandard in dsStandard.Tables["Standard"].Rows)
{
bool bAlternateRow = false;
//standard name strOutput.Append("<Row>");
//BOC - marefin - 20050929 - encode special chars in xml strOutput.Append("<Cell ss:MergeAcross=\"2\" ss:StyleID=\"s51\"><Data ss:Type=\"String\">Standard Name: " + Server.HtmlEncode(drStandard["StandardName"].ToString()) + (drStandard["assigned"].ToString() == "0" ? "" : "~") + "</Data></Cell>");
//EOC - marefin - 20050929 - encode special chars in xml strOutput.Append("<Cell ss:MergeAcross=\"2\" ss:StyleID=\"m52388202\"><Data ss:Type=\"String\">Standard Status: " + (drStandard["Active"].ToString() == "1" ? "Active" : "Needs Review") + "</Data></Cell>");
strOutput.Append("</Row>");
//column headers strOutput.Append("<Row>");
strOutput.Append("<Cell ss:StyleID=\"s41\"><Data ss:Type=\"String\">Standard Line Category</Data></Cell>");
strOutput.Append("<Cell ss:StyleID=\"s42\"><Data ss:Type=\"String\">Components</Data></Cell>");
strOutput.Append("<Cell ss:StyleID=\"s42\"><Data ss:Type=\"String\">Source</Data></Cell>");
strOutput.Append("<Cell ss:StyleID=\"s42\"><Data ss:Type=\"String\">Manufacturer</Data></Cell>");
strOutput.Append("<Cell ss:StyleID=\"s42\"><Data ss:Type=\"String\">Mfg. Part #</Data></Cell>");
strOutput.Append("<Cell ss:StyleID=\"s43\"><Data ss:Type=\"String\">Product Status</Data></Cell>");
strOutput.Append("</Row>");
foreach(DataRow drLineCategory in dsStandard.Tables["StandardLineCategory"].Select("StandardId = " + drStandard["StandardId"]))
{
bool bDisplayLineCategoryName = true;
//strOutput.Append("<Row>"); //strOutput.Append("<Cell><Data ss:Type=\"String\">" + drLineCategory["standardlinecategoryname"] + "</Data></Cell>"); //strOutput.Append("</Row>"); //int nLength = dsStandard.Tables["StandardLineCategoryComponents"].Select("StandardLineCategoryId = " + drLineCategory["StandardLineCategoryId"]).Length; foreach(DataRow drLineComponent in dsStandard.Tables["StandardLineCategoryComponents"].Select("StandardLineCategoryId = " + drLineCategory["StandardLineCategoryId"]))
{
//strOutput.Append("<Row" + (bAlternateRow ? " ss:StyleID=\"s50\"" : "")+ ">"); strOutput.Append("<Row>");
//BOC - marefin - 20050929 - encode special chars in xml strOutput.Append("<Cell ss:StyleID=\"s" + (bAlternateRow ? "47" : "25")+ "\"><Data ss:Type=\"String\">" + (bDisplayLineCategoryName ? Server.HtmlEncode(drLineCategory["standardlinecategoryname"].ToString()) : "") + "</Data></Cell>");
//EOC - marefin - 20050929 - encode special chars in xml bDisplayLineCategoryName = false;
//BOC - marefin - 20050929 - encode special chars in xml strOutput.Append("<Cell ss:StyleID=\"s" + (bAlternateRow ? "48" : "26")+ "\"><Data ss:Type=\"String\">" + Server.HtmlEncode(drLineComponent["descriptiontext"].ToString()) + "</Data></Cell>");
strOutput.Append("<Cell ss:StyleID=\"s" + (bAlternateRow ? "48" : "26")+ "\"><Data ss:Type=\"String\">" + Server.HtmlEncode(drLineComponent["catalogname"].ToString()) + "</Data></Cell>");
strOutput.Append("<Cell ss:StyleID=\"s" + (bAlternateRow ? "48" : "26")+ "\"><Data ss:Type=\"String\">" + Server.HtmlEncode(drLineComponent["mfgname"].ToString()) + "</Data></Cell>");
strOutput.Append("<Cell ss:StyleID=\"s" + (bAlternateRow ? "48" : "26")+ "\"><Data ss:Type=\"String\">" + Server.HtmlEncode(drLineComponent["mfgpartnumber"].ToString()) + "</Data></Cell>");
strOutput.Append("<Cell ss:StyleID=\"s" + (bAlternateRow ? "49" : "27")+ "\"><Data ss:Type=\"String\">" + Server.HtmlEncode(drLineComponent["statusdesc"].ToString()) + "</Data></Cell>");
//EOC - marefin - 20050929 - encode special chars in xml strOutput.Append("</Row>");
bAlternateRow = !bAlternateRow;
}
}
strOutput.Append("<Row ss:Height=\"13.5\">");
strOutput.Append("<Cell ss:MergeAcross=\"5\" ss:StyleID=\"m52388212\"><Data ss:Type=\"String\"></Data></Cell>");
strOutput.Append("</Row>");
strOutput.Append("<Row ss:Height=\"13.5\">");
strOutput.Append("<Cell ss:MergeAccross=\"5\"><Data ss:Type=\"String\"></Data></Cell>");
strOutput.Append("</Row>");
}

strOutput.Append("</Table>");
strOutput.Append("<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\"><Selected/><Panes><Pane><Number>1</Number><ActiveRow>1</ActiveRow><ActiveCol>1</ActiveCol></Pane></Panes><ProtectObjects>False</ProtectObjects><ProtectScenarios>False</ProtectScenarios></WorksheetOptions></Worksheet></Workbook>");
//write the HTML back to the browser. Response.Write(strOutput);
//end the response. Response.End();


2)   TO save your STring that contain Excel string into Excel file ,the code is given below,above code open the excel file into browser

string path;
path=System.AppDomain.CurrentDomain.BaseDirectory.ToString() + "Adeel.xls";
System.Xm.XmDocument doc =new System.Xml.XmlDocument();
System.IO.FileInfo fileName=new System.IO.FileInfo(path);
try {
if (!fileName.Exists)
{
System.IO.File.Delete(path);
System.IO.StreamWriter sw = new System.IO.StreamWriter(path, true);
sw.WriteLine(strOutput.ToString());
sw.Flush();
sw.Close();
}

}
catch { }