ASP.NET - dynamically create a table based on import excel file using C#.net

Asked By venu yelle on 26-Sep-12 05:03 AM

I am trying to import excel file to SQL server using asp.net web application. I can upload the file on the server, my problem is I want to load the data in excel file dynamically; I will need to create a table in the server dynamically everytime I import an excel file.
Please help!!!
Thanks

manish postariya replied to venu yelle on 26-Sep-12 01:19 PM

public string DataTable2ExcelString(DataTable dt)
    {
        StringBuilder sbTop = new StringBuilder();
        sbTop.Append("<html xmlns:o="\"urn:schemas-microsoft-com:office:office\"" xmlns:x="\"urn:schemas-microsoft-com:office:excel\"" hold=" />        sbTop.Append(" xmlns="\"http://www.w3.org/TR/REC-html40\""><head><meta http-equiv="Content-Type" content="\"text/html;" charset="windows-1252\"">");
        sbTop.Append("<meta name="ProgId" content="Excel.Sheet"><meta name="Generator" content="\"Microsoft" excel="" mode="hold" />        string bottom = "</meta></meta></head></html>";
        StringBuilder sb = new StringBuilder();
        //Header
        sb.Append("");
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            sb.Append("" + dt.Columns[i].ColumnName + "");
        }
        sb.Append("");
 
        //Items
        for (int x = 0; x < dt.Rows.Count; x++)
        {
            sb.Append("");
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                sb.Append("" + dt.Rows[x][i] + "");
            }
            sb.Append("");
        }
 
        string SSxml = sbTop.ToString() + sb.ToString() + bottom;
 
        return SSxml;
    }
 

    protected void btndwn_Click1(object sender, EventArgs e)
    {
        try
        {
            string strBody = DataTable2ExcelString(dtDPS);
            Response.AppendHeader("Content-Type", "application/vnd.ms-excel");
            Response.AppendHeader("Content-disposition", "attachment; filename=MyBooking.xls");
            Response.Write(strBody);
        }
        catch (Exception _ex)
        {
            throw;
        }
    }