locked
System.Data.OleDb.OleDbException: External table is not in the expected format. RRS feed

  • Question

  • User-707830680 posted

    I am doing page reponse output of content type "application/vnd.ms-excel" as below

    StringBuilder

    sb = new StringBuilder();

    try

    {

    DataSet dsGetKeyValues = (new clsSQLDBGet()).GeteKeys(objUser.UserId);

    if (dsGetKeyValues != null && dsGetKeyValues.Tables.Count > 0 && dsGetKeyValues.Tables[0].Rows.Count > 0)

    {

    DataTable dtGetKeyValues = dsGetKeyValues.Tables[0];

    sb.Append(

    "<table cellSpacing='0' cellPadding='1' width='500pt' border='1'>");

    sb.Append(

    "<tr>");

    for (int colHeaderIdx = 0; colHeaderIdx < dtGetKeyValues.Columns.Count; colHeaderIdx++)

    {

    //sb.Append("<td bgcolor='#F2DDDC' style=\"font-size: 11px;font-weight: bold;font-family: Verdana;text-align: center;\">" + dtGetKeyValues.Columns[colHeaderIdx].ColumnName + "</td>");

    if (colHeaderIdx == 0)

    {

    sb.Append(

    "<td bgcolor='#F2DDDC' style=\"visibility:hidden;font-size: 11px;font-weight: bold;font-family: Verdana;text-align: center;\">" + dtGetKeyValues.Columns[colHeaderIdx].ColumnName + "</td>");

    }

    if (colHeaderIdx == 1 || colHeaderIdx == 2 || colHeaderIdx == 3 || colHeaderIdx == 4)

    {

    sb.Append(

    "<td bgcolor='#F2DDDC' style=\"font-size: 11px;font-weight: bold;font-family: Verdana;text-align: center;\">" + dtGetKeyValues.Columns[colHeaderIdx].ColumnName + "</td>");

    }

    if (colHeaderIdx == 5 || colHeaderIdx == 6 || colHeaderIdx == 7)

    {

    sb.Append(

    "<td bgcolor='#D99792' style=\"font-size: 11px;font-weight: bold;font-family: Verdana;text-align: center;\">" + dtGetKeyValues.Columns[colHeaderIdx].ColumnName + "</td>");

    }

    }

    sb.Append(

    "</tr>");

    for (int dataRowIdx = 0; dataRowIdx < dtGetKeyValues.Rows.Count; dataRowIdx++)

    {

    sb.Append(

    "<tr>");

    for (int colIdx = 0; colIdx < dtGetKeyValues.Columns.Count; colIdx++)

    {

    //sb.Append("<td bgcolor='#F2DDDC'>" + dtGetKeyValues.Rows[dataRowIdx][colIdx] + "</td>");

    if (colIdx == 0)

    {

    sb.Append(

    "<td bgcolor='#F2DDDC' style=\"visibility:hidden;\">" + dtGetKeyValues.Rows[dataRowIdx][colIdx] + "</td>");

    }

    if (colIdx == 1 || colIdx == 2 || colIdx == 3 || colIdx == 4)

    {

    sb.Append(

    "<td bgcolor='#F2DDDC'>" + dtGetKeyValues.Rows[dataRowIdx][colIdx] + "</td>");

    }

    if (colIdx == 5 || colIdx == 6 || colIdx == 7)

    {

    sb.Append(

    "<td bgcolor='#D99792'>" + dtGetKeyValues.Rows[dataRowIdx][colIdx] + "</td>");

    }

    }

    sb.Append(

    "</tr>");

    }

    sb.Append(

    "</table>");

    Response.ContentType =

    "application/vnd.ms-excel";

    Response.AppendHeader(

    "content-disposition", "attachment; filename=" + fileName);

    Response.Write(sb.ToString());

    Response.Flush();

    Response.Close();

     

    Then I dowloaded the excel file and saved it. I am again trying to read this saved file using the connection string.

    excelConnStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;\"";.

    I am now getting the error "System.Data.OleDb.OleDbException: External table is not in the expected format.".

    What is wrong with the connection string? I checked all types of excel connections, but its not working. 

    What excel connection string should I use to response object content type  "application/vnd.ms-excel"?

    Wednesday, July 27, 2011 1:09 AM

Answers