Can't read excel file after creating it using File.WriteAllText() function

I have created an excel sheet from datatable using function. I want to read the excel sheet programatically using the below connectionstring. This string works fine for all other excel sheets but not for the one i created using the function. I guess it is because of excel version problem.

   OleDbConnection conn= new OleDbConnection("Data Source='" + path +"';provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;";);  

Can anyone suggest a way by which i can create an excel sheet such that it is readable again using above query. I cannot use Microsoft InterOp library as it is not supported by my host. I have even changed different encoding formats. Still it doesnt work

 public void ExportDataSetToExcel(DataTable dt)
{
    HttpResponse response = HttpContext.Current.Response;        
    response.Clear();
    response.Charset = "utf-8";
    response.ContentEncoding = Encoding.GetEncoding("utf-8"); 
    response.ContentType = "application/vnd.ms-excel";
    Random Rand = new Random(); int iNum = Rand.Next(10000, 99999);
    string extension = ".xls";
    string filenamepath = AppDomain.CurrentDomain.BaseDirectory + "graphs\\" + iNum + ".xls";        
    string file_path = "graphs/" + iNum + extension;

    response.AddHeader("Content-Disposition", "attachment;filename=\"" + iNum + "\"");
    string query = "insert into graphtable(graphtitle,graphpath,creategraph,year) VALUES('" + iNum.ToString() + "','" + file_path + "','" + true + "','" + DateTime.Now.Year.ToString() + "')";
    try
    {
        int n = connect.UpdateDb(query);
        if (n > 0)
        {
            resultLabel.Text = "Merge Successfull";
        }
        else
        {
            resultLabel.Text = " Merge Failed";
        }
        resultLabel.Visible = true;
    }
    catch { }    
    using (StringWriter sw = new StringWriter()开发者_开发问答)
    {
        using (HtmlTextWriter htw = new HtmlTextWriter(sw))
        {
            // instantiate a datagrid
            DataGrid dg = new DataGrid();
            dg.DataSource = dt; //ds.Tables[0];
            dg.DataBind();                
            dg.RenderControl(htw);
            File.WriteAllText(filenamepath, sw.ToString());    // File.WriteAllText(filenamepath, sw.ToString(), Encoding.UTF8);
            response.Write(sw.ToString());
            response.End();
        }
    }
}

You seem to be writing a dataset as HtmlText and then trying to tell it that it's an Excel file. Unless it's something I'm missing that's unlikely to work since Excel files are a specific format and so needs to be written in that format. If you take the file you've created and tries to open it in Excel, what happens?

One way around it would be to write your data as a CSV file which can be read both with Excel or with an OleDBConnection.

Folowed the link: C# Excel file OLEDB read HTML IMPORT

Use the Extended Properties=\"HTML Import;HDR=No;IMEX=1 the select * from [tablename]

tablename is returned from GetOleDbSchemaTable.

Note: This would not load the normal excel...for that use Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\ where table name will be with $ sign.

string full = "C:\\Temp.xls"
            DataTable datatable = null;
            string conString = "";
            OleDbConnection objConn = null;

            try
            {
                //create the "database" connection string 
                connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + full + ";Extended Properties=\"HTML Import;HDR=No;IMEX=1\"";

                objConn = new OleDbConnection(connString);
                // Open connection with the database.
                objConn.Open();
                // Get the data table containg the schema guid.

                dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            }
            catch
            {
              throw exception
            }

            //no worksheets
            if (dt == null)
            {
                DataCaptured = null;
                return;
            }

            List<string> Sheets = new List<string>();

            // Add the sheet name to the string array.

            foreach (DataRow row in dt.Rows)
            {
                string name = row["TABLE_NAME"].ToString();

                if (string.IsNullOrEmpty(name) == false)
                {
                    Sheets.Add(name);
                }
            }

            //no worksheets
            if (excelSheets.Count == 0)
            {
                return;
            }

            Dataset dataSet = new DataSet();

            int sheetCount = excelSheets.Count;

            for (int i = 0; i < sheetCount; i++)
            {
                string sheetName = excelSheets[i];

                OleDbDataAdapter ad = new OleDbDataAdapter("SELECT * FROM [" + sheetName + "]", connString);

                DataTable data = new DataTable();
                try
                {
                    ad.Fill(data);
                }
                catch
                {
                    throw exception
                }

                data.TableName = sheetName;
                dataSet.Tables.Add(data);

                adapter.Dispose();
            }

            objConn.Close();

        return dataSet;