Wednesday, March 24, 2010

Create Excel file also use various formulas with in it

In one project has requirement that need to create excel file through coding and need to use various excel formals for it.Below is code for it.


FileStream fs = new FileStream(@"D:\Test\TestExcel.xls", FileMode.Create);
        StreamWriter sr = new StreamWriter(fs);
        Response.ContentType = "application/ms-excel";
        StringBuilder sb = new StringBuilder();
        sb.Append("<table>");
        sb.Append("<tr><td><font face='Arial' color='black' size='2'><b>Name</b></font></td>");
        sb.Append("<td><font face='Arial' color='black' size='2'><b>Amount</b></font></td></tr>");
        for (int i = 0; i < 10; i++)
        {
            sb.Append("<tr>");
            sb.Append("<td align='center' vertical-align='middle'>" + "<font face='Arial' color='black' size='2'>Name12</font></td>");
            sb.Append("<td align='center' vertical-align='middle'>" + "<font face='Arial' color='black' size='2'>" + i.ToString() + "</font></td>");
            sb.Append("</tr>");

        }
        sb.Append("<tr>");
        sb.Append("<td>Total</td>");
        sb.Append("<td>=SUM(B1:B11)</td></tr>");
        sb.Append("</table>");
        sr.WriteLine(sb.ToString());
        sr.Close();
        fs.Dispose();

Response.Clear();
Response.AddHeader("content-disposition", @"attachment; filename=Test.xls");
Response.Write(sb.ToString());


Happy coding ..

No comments: