Exporting Data from dataGrid to Excel sheet

Try this, Hope this wiill solve ur problem,

 

Protected Sub Button4_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button4.Click

If FileUpload2.PostedFile.FileName <> "" Then

UploadBusinessProfile()

Else

Label7.Visible = True

Label7.Text = "Please Select A Excel File First...."

End If

End Sub

Sub UploadBusinessProfile()

Dim path As String = Server.MapPath("~/Admin/ImportData/")

FileUpload2.PostedFile.SaveAs(path & _

FileUpload2.FileName)

Label7.Visible = True

Dim prmpathexcelfile As String = path + FileUpload2.FileName

ImportBusinessProfile(prmpathexcelfile)

End Sub

Sub ImportBusinessProfile(ByVal PrmPathExcelFile As String)

Dim strpath = PrmPathExcelFile

Dim count As Int32 = 0

Dim loopcounter, i As Int32

Dim cmd As New SqlCommand

Dim query As String

Dim con As SqlConnection

con = New SqlConnection(ocon)

Dim PostName = "", Category = "", CompanyName = "", Companybriefdis = "", Turnover = "", ProjectHandle = "", ContactPerson = "", Address = "", State = "", City = ""

Dim PhoneNo = "", Fax = "", Weblink = ""

Dim econ As OleDbConnection = New System.Data.OleDb.OleDbConnection( _

"Provider=Microsoft.Jet.OLEDB.4.0;" _

& "Data Source=" & strpath & ";" _

& "Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""")

 

Try

econ.Open()

Dim objCmdSelect As New OleDbCommand("select * from [Sheet1$]", econ)

Dim objAdapter1 As New OleDbDataAdapter()

objAdapter1.SelectCommand = objCmdSelect

Dim ds As New DataSet()

'pb1.Minimum = 1

objAdapter1.Fill(ds, "Test")

If ds.Tables(0).Rows.Count > 0 Then

'GridView1.DataSource = Nothing

'GridView1.DataSource = ds.Tables(0)

'GridView1.DataBind()

count = ds.Tables(0).Rows.Count

'pb1.Maximum = count

For loopcounter = 0 To count

If Not (IsDBNull(ds.Tables(0).Rows.Item(i).Item("PostName"))) Then

PostName = Convert.ToString(ds.Tables(0).Rows.Item(i).Item("PostName"))

Else

PostName = ""

End If

If Not (IsDBNull(ds.Tables(0).Rows.Item(i).Item("Category"))) Then

Category = Convert.ToString(ds.Tables(0).Rows.Item(i).Item("Category"))

Else

Category = ""

End If

If Not (IsDBNull(ds.Tables(0).Rows.Item(i).Item("CompanyName"))) Then

CompanyName = Convert.ToString(ds.Tables(0).Rows.Item(i).Item("CompanyName"))

Else

CompanyName = ""

End If

If Not (IsDBNull(ds.Tables(0).Rows.Item(i).Item("Companybriefdis"))) Then

Companybriefdis = Convert.ToString(ds.Tables(0).Rows.Item(i).Item("Companybriefdis"))

Else

Companybriefdis = ""

End If

If Not (IsDBNull(ds.Tables(0).Rows.Item(i).Item("Turnover"))) Then

Turnover = Convert.ToString(ds.Tables(0).Rows.Item(i).Item("Turnover"))

Else

Turnover = ""

End If

If Not (IsDBNull(ds.Tables(0).Rows.Item(i).Item("ProjectHandle"))) Then

ProjectHandle = Convert.ToString(ds.Tables(0).Rows.Item(i).Item("ProjectHandle"))

Else

ProjectHandle = ""

End If

If Not (IsDBNull(ds.Tables(0).Rows.Item(i).Item("State"))) Then

State = Convert.ToString(ds.Tables(0).Rows.Item(i).Item("State"))

Else

State = ""

End If

If Not (IsDBNull(ds.Tables(0).Rows.Item(i).Item("City"))) Then

City = Convert.ToString(ds.Tables(0).Rows.Item(i).Item("City"))

Else

City = ""

End If

If Not (IsDBNull(ds.Tables(0).Rows.Item(i).Item("ContactPerson"))) Then

ContactPerson = Convert.ToString(ds.Tables(0).Rows.Item(i).Item("ContactPerson"))

Else

ContactPerson = ""

End If

If Not (IsDBNull(ds.Tables(0).Rows.Item(i).Item("Address"))) Then

Address = Convert.ToString(ds.Tables(0).Rows.Item(i).Item("Address"))

Else

Address = ""

End If

If Not (IsDBNull(ds.Tables(0).Rows.Item(i).Item("Fax"))) Then

Fax = Convert.ToString(ds.Tables(0).Rows.Item(i).Item("Fax"))

Else

Fax = ""

End If

If Not (IsDBNull(ds.Tables(0).Rows.Item(i).Item("PhoneNo"))) Then

PhoneNo = Convert.ToString(ds.Tables(0).Rows.Item(i).Item("PhoneNo"))

Else

PhoneNo = ""

End If

If Not (IsDBNull(ds.Tables(0).Rows.Item(i).Item("Weblink"))) Then

Weblink = Convert.ToString(ds.Tables(0).Rows.Item(i).Item("Weblink"))

Else

Weblink = ""

End If

query = "Insert into Agent(PostName,Category, CompanyName, Companybriefdis, Turnover,ProjectHandle, ContactPerson, Address, State, City, PhoneNo, Fax, Weblink) " _

& " values('" & PostName & "', '" & Category & "','" & CompanyName & "','" & Companybriefdis & "','" & Turnover & "','" & ProjectHandle & "','" & ContactPerson & "','" & Address & "','" & State & "','" & City & "','" & PhoneNo & "','" & Fax & "','" & Weblink & "')"

cmd.CommandText = query

cmd.Connection = con

con.Open()

cmd.ExecuteNonQuery()

If i = count - 1 Then

Exit For

End If

i = i + 1

'pb1.Value = i

Next

Label7.Text = "Business Profile Data Successfully Imported..........."

Label7.Visible = True

End If

con.Close()

Catch ex As Exception

Label7.Text = ex.ToString()

End Try

End Sub

Dear.
     try to do as following code.
  
           Response.Clear();
            Response.Buffer =  false;
            Response.Charset ="UTF-8";
            //filename is your define format  Response.AppendHeader("Content-Disposition","attachment;filename=Member("+DateTime.Now.ToShortDateString() +").xls");
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
           // Excel file ContentType = "application/vnd.ms-excel"
            Response.ContentType = "application/vnd.ms-excel";
            System.IO.StringWriter excelWriter = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter htriter = new          System.Web.UI.HtmlTextWriter(excelWriter);
           // dgMemberInfo is your DataGrid object
            if(dgMemberInfo.Items.Count > 0)
                this.dgMemberInfo.RenderControl(htriter);
            Response.Write(excelWriter.ToString());
            Response.End();

Hi,

do you want to export the data from dept(grid), Emp(grid) to two different excel sheets and link them based on the Dept ID?

Thanks -- Vj