Solved

datagrid column to excel column

Posted on 2004-10-29
202 Views
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?
0
Question by:gotzcomputer
    2 Comments
     

    Author Comment

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

    Accepted Solution

    by:
    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

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

                frmGrabowskiReports.propDataSet = ds
                frmGrabowskiReports.ShowDialog()

                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
                    Next
                    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()
                            objGrabowski.GetGrabowskiDetail()
                        Next
                    Next
                End If
                ' AutoFit columns A.
                oRng = oSheet.Range("A23", "W23")
                oRng.EntireColumn.AutoFit()

                ' 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
            Catch
            End Try
    0

    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