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

Question

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();
        }
    }
}

Have you made sure, that the document created actually contains something and something meaningful as well? It looks like you are trying to create an HTML document, not an Excel document. — Dirk Vollmar, May 27 '10 at 10:45

score 0 · Answer 1 · answered May 27 '10 at 10:55

0

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.

answered May 27 '10 at 10:55

Hans Olsson

54,199
15
94
116

I had created the file using csv as well. But I use the data from graph to generate a dynamic graph. So no point in using csv. – Srikanth V M May 27 '10 at 11:07
It opens normally in Microsoft Excel Sheet. But while saving the file it asks to convert to a specific format. – Srikanth V M May 27 '10 at 11:07

score -1 · Answer 2 · edited May 23 '17 at 10:34

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;

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

2 Answers2

Linked