Problem in import and export to excel

[santosh1234]

Hi,

I have a problem in importing from an excel file. I am using the connection string for oledb to convert the data from the excel file into a datatable and proceed thereafter. Code I am using is as given below:

excelConn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & fileName & ";Extended Properties=""Excel 8.0;HDR=Yes;""")

excelConn.Open()

Dim dbSchema As DataTable = excelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tabl es, Nothing)

If dbSchema Is Nothing OrElse dbSchema.Rows.Count < 1 Then

Throw New Exception("Error: Could not determine the name of the first worksheet.")

End If

Dim firstSheetName As String = dbSchema.Rows(0)("TABLE_NAME").ToString

Dim dbCommand As OleDbCommand = New OleDbCommand("SELECT * FROM [" + firstSheetName + "]", excelConn)

'excelConn.Open()

oledbDA = New OleDbDataAdapter("SELECT * FROM [" + firstSheetName + "]", excelConn)

oledbDA.Fill(fcstImportDS)



It is able to read some of the excel files correctly. But in the cases when i am renedring a datagrid to excel file then it is unable to read those excel files and giving exception having no name. The i am using for downloading datagrid to excel is as given below:

dgExportToExcel.DataSource = userAllGenericStrategiesDT

dgExportToExcel.DataBind()

Response.Clear()

Response.AddHeader("Content-Disposition", "attachment;filename=" & fileName)

Response.Charset = ""

Response.ContentType = "application/vnd.ms-excel"

Dim controlOutputBuffer As System.IO.StringWriter

Dim controlOutput As HtmlTextWriter

controlOutputBuffer = New System.IO.StringWriter()

controlOutput = New HtmlTextWriter(controlOutputBuffer)

''Render the datagrid control.

dgExportToExcel.RenderControl(controlOutput)

''Write the contents of the control output to the response stream

Response.Write(controlOutputBuffer.ToString())

Response.Flush()

Response.End()



Can any body help me on this, why it is not able to read from the downloaded excel files?(If i open the same downloaded file and again save it as an excel file, it is able to read correctly. One thing i can see is when i open the downloaded excel file and click "Save AS" button, it shows me option for type as Web Page)



Thanks,

Santosh.