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"?
