datagrid column to excel column

Posted on 2004-10-29
Last Modified: 2011-09-20
i have my data going into a datagrid.  now i need to get the data from the datagrid to an excel worksheet.  how do i get the data column to an excel column?
Question by:gotzcomputer

    Author Comment

    I guess what I really want is how to copy my dataset over to an excel worksheet
    LVL 4

    Accepted Solution

    try this:
           Dim oResizeRange As Excel.Range
            Dim oXL As Excel.Application
            Dim oWB As Excel.Workbook
            Dim oSheet As Excel.Worksheet
            Dim oRng As Excel.Range

            'Instantiate Jobs Object
            Dim objGrabowski As New ExcelGrabowskiAttempt()
            objGrabowski.JobID = iJobID
            objGrabowski.ClientPK = iClientPK

                Dim ds As DataSet = objGrabowski.GetGrabowski
                Dim frmGrabowskiReports As New frmGrabowskiReports()

                frmGrabowskiReports.propDataSet = ds

                If frmGrabowskiReports.propGenerate Then

                    Dim dt As DataTable
                    dt = frmGrabowskiReports.propDataSet.Tables("Grabowski")

                    'For iIndex = 0 To dt.Rows.Count - 1

                    '*******************Start of Excel Sheet
                    ' Start Excel and get Application object.
                    oXL = CType(CreateObject("Excel.Application"), Excel.Application)
                    oXL.Visible = True

                    ' Get a new workbook.
                    oWB = oXL.Workbooks.Add
                    oSheet = CType(oWB.ActiveSheet, Excel.Worksheet)

                    With oSheet.Range("A1", "W1")
                        .Font.Bold = True
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With
                    Dim mrow As DataRow
                    Dim colindex As Integer
                    Dim rowindex As Integer
                    Dim col As DataColumn

                    For Each col In dt.Columns
                        colindex += 1
                        oXL.Cells(1, colindex) = col.ColumnName
                    rowindex = 1

                    For Each mrow In dt.Rows
                        rowindex += 1
                        colindex = 0
                        For Each col In dt.Columns
                            colindex += 1
                            oXL.Cells(rowindex, colindex) = mrow(col.ColumnName).ToString()
                End If
                ' AutoFit columns A.
                oRng = oSheet.Range("A23", "W23")

                ' Make sure Excel is visible and give the user control
                ' of Excel's lifetime.
                oXL.Visible = True
                oXL.UserControl = True

                ' Make sure that you release object references.
                oRng = Nothing
                oSheet = Nothing
                oWB = Nothing
                oXL = Nothing

                Exit Sub
            End Try

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT Security CISA, CISSP & CISM Certification

    Master the advanced techniques required to protect network resources from external threats with the IT Cyber Security bundle. Built around industry best-practice guidelines, the IT Cyber Security bundle consists of three in-depth courses.

    Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
    Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    933 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now