Code Simplified – Viral Sarvaiya

Code Simplified – Viral Sarvaiya, Web Developer Friends, dot net Developer, Sql Server Developer

Posts Tagged ‘Excel’

Export GridView Data into CSVFile In Asp.net

Posted by Viral Sarvaiya on October 30, 2009


Steps.

1) Create Simple Web Application.

2) put the below control in to the page (.aspx)

2.1) GridView (id= GridView1)

2.2) Button (id=button1 and Text =Create CSV File)

3) assign data source to GridView

4) on the button_click event put the following code.


Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs)
Try
'Create CSV file
Dim objSw As New StreamWriter(Server.MapPath("~/demo.csv"))
'get table from GridView1
Dim objDt As DataTable = DirectCast(GridView1.DataSource, DataSet).Tables(0)
'Get No Of Column in GridView
Dim NoOfColumn As Integer = objDt.Columns.Count
'Create Header
For i As Integer = 0 To NoOfColumn - 1
objSw.Write(objDt.Columns(i))
'check not last column
If i < NoOfColumn - 1 Then
objSw.Write(",")
End If
Next
objSw.Write(objSw.NewLine)
'Create Data
For Each dr As DataRow In objDt.Rows
For i As Integer = 0 To NoOfColumn - 1
objSw.Write(dr(i).ToString())

If i < NoOfColumn - 1 Then
objSw.Write(",")
End If
Next
objSw.Write(objSw.NewLine)
Next
objSw.Close()
Catch ex As Exception
Response.Write("Can Not Generate CSV File")
End Try
End Sub

Enjoy Coding….

Advertisements

Posted in ASP.NET | Tagged: , , , , , | 2 Comments »

Get the record of the excel file in the dataset

Posted by Viral Sarvaiya on September 3, 2009


here i demonstrate that how to get the data of the excel file in to the dataset.

the function have the two arguments,
1) path of the excel file, from where the excel file is.
2) name of the excel file.


Public Function ReadXLS(ByVal path As String, ByVal filename As String) As DataSet

Dim ds As New DataSet
Dim strConn As String
//this is the connection string
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & filename & ";Extended Properties=Excel 8.0;"
//take the connection of the OLEDB
Dim con As New OleDbConnection(strConn)
Dim dt As DataTable
If con.State = ConnectionState.Open Then
con.Close()
End If
con.Open()
dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
If dt Is Nothing Then
Return Nothing
End If
//take the name of the all the sheet of the excel file to the erray
Dim excelSheets() As String = New [String](dt.Rows.Count - 1) {}
Dim i As Integer = 0
For Each row As DataRow In dt.Rows
excelSheets(i) = row("TABLE_NAME").ToString()
If con.State = ConnectionState.Open Then
Dim adpt As OleDbDataAdapter = New OleDbDataAdapter("select * from [" & excelSheets(i) & "]", con)
adpt.Fill(ds)
return ds
i += 1

end if
Next
ds.Dispose()
ds.Clear()
con.Close()
End Function

i put this code, that have some error in a particular situation, when u have a numeric and varchar datatype mix in a single column then this code give error, the particular datatype is converting NULL in the dataset.

the solution is change the connection string as below…


strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & filename & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;ImportMixedTypes=Text;TypeGuessRows=10"""

now the error has been solved.

to know other interesting problem click here

enjoy coding….

Posted in ASP.NET | Tagged: , , | 2 Comments »

 
%d bloggers like this: