Export to Excel

[Arvind R]

how to ask saveas dialog before writing the data to the excel file?
right now im able to save in c drive or any other specified location only.

any solution will be a great help!

System.Text.StringBuilder sbrHTML=new System.Text.StringBuilder("");

//StringBuilder sbrHTML = new StringBuilder();


SqlConnection cn = new
SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["connectionString"]);

SqlCommand dc = new SqlCommand("Select * From wc_ingredient",cn);

SqlDataReader dr ;

int i=0;

cn.Open();

dr = dc.ExecuteReader();

//Making HTML

sbrHTML.Append("<TABLE Border=1 ID='Table1'>");

sbrHTML.Append("<TR><TD ColSpan=35><Font Size=5>Report</Font></TD><TR>");

sbrHTML.Append("<TR><TH>Id</TH><TH>Ingredient
Name</TH><TH>measure</TH><TH>Cost</TH><TH>Onhand Qty</TH><TH>Supplier
Code</TH><TH>Supplier Name</TH><TH>Supplier purchase unit</TH><TH>Supplier
Factor</TH><TH>Onhand Factor</TH><TH>Supplier Brand</TH><TH>Supplier
Case</TH><TH>Supplier Purchase unit</TH><TH>Supplier use
unit</TH><TH>Supplier cost unit</TH></TR>");

while(dr.Read())

{

i++;

sbrHTML.Append("<TR><TD>" + dr.GetValue(0).ToString() + "</TD><TD>"

+ dr.GetValue(1).ToString() + "</TD><TD>"

+ dr.GetValue(2).ToString() + "</TD><TD>"

+ dr.GetValue(3).ToString() + "</TD><TD>"

+ "</TR>" );

}

sbrHTML.Append("</TABLE>");

sbrHTML.Append("<BR><BR><B>Total Number of Ingredients are " + i.ToString()
+ "</B>");



string path="c:\\Ingredient_Report.xls";

StreamWriter swXLS =new StreamWriter("c:\\Report.xls");

swXLS.Write(sbrHTML.ToString());


swXLS.Close();

 

[Nicholas Paldino [.NET/C# MVP]]

Arvind,

You can't really save HTML content and just give it an extension of XLS
and expect Excel to open it correctly (although I would guess it probably
could).

What you should do is access the Excel object model, and then layout
your content appropriately, and then save it in an Excel format.

Or, what you could do is use the code you are using now, save the file
as HTML, open it in Excel through automation, and then save it as an Excel
file, and let Excel do the work.

Hope this helps.

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi?

how to ask saveas dialog before writing the data to the excel file?
right now im able to save in c drive or any other specified location only.

Why are you creating a HTML file if what you want is an excel file? , IT
does not makes sense.

Easiest solution is create a text file, a CSV to be more precise, excel will
understand it perfectly well.

The other solution is create a native excel file from code, look into MSDN
for examples of how to do this, it's very easy

 

[Vikashevskumar]

Have pasted the code for reference:-

Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=FileName.xls");
Response.Charset = "";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/vnd.ms-excel";
System.Text.StringBuilder sbrHTML = new System.Text.StringBuilder();
sbrHTML.Append("<table id=750 border=1 align=center cellpadding=4 cellspacing=1 bgcolor=#ffffff>");
sbrHTML.Append("<tr>");
sbrHTML.Append("<td align=center bgcolor=Lightyellow style=mso-number-format:\\@><B>000693965</B></td><td align=center bgcolor=Lightyellow><B>Column 7</B></td><td align=center bgcolor=Lightyellow><B>Column 8</B></td>");
sbrHTML.Append("</tr> ");
sbrHTML.Append("</table>");
HttpContext.Current.Response.Write(sbrHTML);
HttpContext.Current.Response.End();