How To Export Rows To Excel From Data Table By selecting different columns?

Shahab a 241 Reputation points
2022-10-06T05:55:52.173+00:00

Hi all
I Created a Dataset and a Data Table which I Added two Rows by this codes...
Please note that my Data table has three columns (ID, Name and Address)
I have two problems
1- Now I want to send only the records of the name column to Excel (Export To Excel)
2- I want ID to be placed in column A of Excel and Address to be placed in column B of Excel

How do I do this?

    Dim Ds As New DataSet  
        Dim Dt As DataTable  
        Dim idCoulumn As DataColumn  
        Dim NameCoulumn1 As DataColumn  
        Dim NameCoulumn2 As DataColumn  
        Dim I As Integer  
        Dim Dr As DataRow  
  
        Dt = New DataTable()  
  
        idCoulumn = New DataColumn("ID", Type.GetType("System.Int32"))  
        NameCoulumn1 = New DataColumn("Name", Type.GetType("System.String"))  
        NameCoulumn2 = New DataColumn("Address", Type.GetType("System.String"))  
  
  
  
  
  
        Dt.Columns.Add(idCoulumn)  
        Dt.Columns.Add(NameCoulumn1)  
        Dt.Columns.Add(NameCoulumn2)  
  
  
  
  
  
        Dr = Dt.NewRow()  
        Dr("ID") = 1  
        Dr("Name") = "Name1"  
        Dr("Address") = "Address1"  
        Dt.Rows.Add(Dr)  
  
        Dr = Dt.NewRow()  
        Dr("ID") = 2  
        Dr("Name") = "Name2"  
        Dr("Address") = "Address2"  
        Dt.Rows.Add(Dr)  
  
        Ds.Tables.Add(Dt)  
VB
VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,600 questions
0 comments No comments
{count} votes

Accepted answer
  1. Jiachen Li-MSFT 27,001 Reputation points Microsoft Vendor
    2022-10-06T08:53:13.073+00:00

    Hi @Shahab a ,
    You can refer to the following code to output the data of a specific column to excel by IO stream.

    248042-code.txt
    You can refer to the following code to output the data of column ID and Address by using Microsoft Excel 16.0 Object Library.
    248440-code2.txt
    Best Regards.
    Jiachen Li

    ----------

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Shahab a 241 Reputation points
    2022-10-07T15:25:54.453+00:00

    Thanks @Jiachen Li-MSFT

    I Change Your Codes...
    See it And if it is good, confirm it

         Dim dtb As New DataTable  
      
            dtb = Dt.Copy  
            dtb.Columns.Remove("Name")  
      
                Dim oExcel As New Excel.Application  
      
                Dim obook As Excel.Workbook  
      
                Dim oSheet As Excel.Worksheet  
      
                Try  
      
                    obook = oExcel.Workbooks.Add '.Open(file)  
      
                    oSheet = obook.Worksheets(1)  
      
                    oSheet.Name = "SheetExcel"  
      
                    Dim r As Integer, c As Integer  
      
                    Dim rCount As Integer  
      
                    Dim cCount As Integer  
      
                    '  Dim str As String  
      
                    rCount = dtb.Rows.Count  
      
                    cCount = dtb.Columns.Count()  
      
                    For c = 1 To cCount  
      
                    oSheet.Cells(1, c) = dtb.Columns(c - 1).Caption '设置列标题     
      
                Next  
      
                    c = 0 : r = 0  
      
                    For r = 1 To rCount  
      
                        For c = 1 To cCount  
      
                            oSheet.Cells(r + 1, c) = CStr(dtb.Rows(r - 1)(c - 1).ToString)  
      
                        Next  
      
                    Next  
      
                    oExcel.Visible = True  
      
                Catch ex As Exception  
      
                    MsgBox(ex.Message)  
      
                End Try  
      
                oExcel = Nothing  
      
                obook = Nothing  
      
                oSheet = Nothing