Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Writing DataRow into Excel spreadsheet

Posted on 2006-11-06
4
Medium Priority
?
1,449 Views
Last Modified: 2010-08-05
Im writing an entire datatable into an excel workbook. I want to copy everthing from the datatable into the second worksheet of an excel template. How do I write an entire row at once?

    Public Shared Function Fill_Sheet_2(ByVal dt As DataTable, ByVal template As String, ByVal sheetName As String)
        'create objects
        Dim app As New Excel.Application
        Dim wkbk As Workbook
        Dim wksheet As Excel.Worksheet
        Dim row As Integer = 0
        Dim column As String
        wkbk = app.Workbooks.Open(template)
        wksheet = wkbk.Worksheets.Item(sheetName)
        'with worksheet
        With wksheet
            For Each dr As DataRow In dt.rows
                '???write row into excel???
            Next
        End With

        Return True
    End Function
0
Comment
Question by:craigdev
  • 2
4 Comments
 
LVL 28

Accepted Solution

by:
iboutchkine earned 2000 total points
ID: 17881136
you can do it this way. Create array whicch you populate with data from datarow and then enter this array to excel

Imports Excel
Imports System.Runtime.InteropServices

Public Class ExportExcel
    Sub Export_To_Excel(ByVal dtSource As System.Data.DataTable, ByVal sFileName As String)

        Dim iRowCount As Integer = dtSource.Rows.Count
        Dim iColCount As Integer = dtSource.Columns.Count
        Dim oData(iRowCount, iColCount) As Object

        Dim iRow As Integer, iCol As Integer
        For iRow = 0 To iRowCount - 1
            For iCol = 0 To iColCount - 1
                oData(iRow, iCol) = dtSource.Rows(iRow).Item(iCol)
            Next
        Next

        ' Start Excel and get Application object
        Dim oExcel As Excel.Application = New Excel.Application()
        'oExcel.Visible = True  ' Make visible

        ' Get a new workbook
        Dim oBook As Excel._Workbook = CType(oExcel.Workbooks.Add(Missing.Value), Excel._Workbook)
        Dim oSheet As Excel._Worksheet = CType(oBook.ActiveSheet, Excel._Worksheet)

        Dim oRange As Excel.Range = oSheet.Range("A1")
        oRange = oRange.Resize(iRowCount, iColCount)
        oRange.Value = oData

        oSheet.SaveAs(sFileName)
        oExcel.Workbooks.Close()
        oExcel.Quit()

        oBook = Nothing
        oSheet = Nothing
        oExcel = Nothing
    End Sub
End Class
0
 
LVL 1

Author Comment

by:craigdev
ID: 17881223
Thanks!
0
 
LVL 35

Expert Comment

by:YZlat
ID: 17881229
try using this function:

Public Sub WriteToExcelSpreadsheet(ByVal fileName As String, ByVal dt As System.Data.DataTable)
        Dim iCol, iRow, iColVal As Integer
        Dim missing As Object = System.Reflection.Missing.Value
        Dim bNew As Boolean
        Dim i As Integer
        ' Open the document that was chosen by the dialog
        Dim aBook As Excel.Workbook
        Try
            ''re-initialize excel app
            ExlApp = New Excel.Application

            If ExlApp Is Nothing Then
                ''throw an exception
                Throw (New Exception("Unable to Start Microsoft Excel"))
            Else
                ''supresses overwrite warnings
                ExlApp.DisplayAlerts = False
             
                'aBook = New Excel.Workbook
                ''check if file exists
                If File.Exists(fileName) Then
                    aBook = ExlApp.Workbooks.Open(fileName)
                Else
                    aBook = ExlApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet)
                End If
                With ExlApp
                    .SheetsInNewWorkbook = 1
                    '.Workbooks.Add()
                    .Worksheets(1).Select()
                    'For displaying the column name in the the excel file.
                    For iCol = 0 To dt.Columns.Count - 1
                        ''clear column name before setting a new value
                        .Cells(1, iCol + 1).Value = ""
                        .Cells(1, iCol + 1).Value = dt.Columns(iCol).ColumnName.ToString
                    Next
                    'For displaying the column value row-by-row in the the excel file.
                    For iRow = 0 To dt.Rows.Count - 1
                        For iColVal = 0 To dt.Columns.Count - 1
                            .Cells(iRow + 2, iColVal + 1).Value = Trim(dt.Rows(iRow).ItemArray(iColVal).ToString)
                        Next
                    Next
                    If File.Exists(fileName) Then
                        .ActiveWorkbook().Save() 'fileName)
                    Else
                        .ActiveWorkbook().SaveAs(fileName, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing)
                    End If
                    .ActiveWorkbook.Close()
                End With
                Console.Write("File exported sucessfully")
            End If
        Catch ex As Runtime.InteropServices.COMException
            err.WriteError("C:\Inetpub\wwwroot\errorlogs\", ex.Message, ex.StackTrace)
            ''err.WriteError(System.Reflection.Assembly.GetAssembly, ex.Message, ex.StackTrace)
            Console.Write("ERROR: " & ex.Message)
        Catch ex As Exception
            err.WriteError("C:\Inetpub\wwwroot\errorlogs\", ex.Message, ex.StackTrace)
            'err.WriteError(GetExecutingAssembly.Location.ToString() & "errorlog.txt", ex.Message, ex.StackTrace)
            Console.Write("ERROR: " & ex.Message)
        Finally
            ExlApp.Quit()
            System.Runtime.InteropServices.Marshal.ReleaseComObject(ExlApp)
            aBook = Nothing
            ExlApp = Nothing
           
         
        End Try
    End Sub
0
 
LVL 35

Expert Comment

by:YZlat
ID: 17881237
sorry, try this:

Public Sub WriteToExcelSpreadsheet(ByVal fileName As String, ByVal dt As System.Data.DataTable)
        Dim iCol, iRow, iColVal As Integer
        Dim missing As Object = System.Reflection.Missing.Value
        Dim bNew As Boolean
        Dim i As Integer
        ' Open the document that was chosen by the dialog
        Dim aBook As Excel.Workbook
        Try
            ''re-initialize excel app
            ExlApp = New Excel.Application

            If ExlApp Is Nothing Then
                ''throw an exception
                Throw (New Exception("Unable to Start Microsoft Excel"))
            Else
                ''supresses overwrite warnings
                ExlApp.DisplayAlerts = False
             
                'aBook = New Excel.Workbook
                ''check if file exists
                If File.Exists(fileName) Then
                    aBook = ExlApp.Workbooks.Open(fileName)
                Else
                    aBook = ExlApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet)
                End If
                With ExlApp
                    .SheetsInNewWorkbook = 1
                    '.Workbooks.Add()
                    .Worksheets(2).Select()
                    'For displaying the column name in the the excel file.
                    For iCol = 0 To dt.Columns.Count - 1
                        ''clear column name before setting a new value
                        .Cells(1, iCol + 1).Value = ""
                        .Cells(1, iCol + 1).Value = dt.Columns(iCol).ColumnName.ToString
                    Next
                    'For displaying the column value row-by-row in the the excel file.
                    For iRow = 0 To dt.Rows.Count - 1
                        For iColVal = 0 To dt.Columns.Count - 1
                            .Cells(iRow + 2, iColVal + 1).Value = Trim(dt.Rows(iRow).ItemArray(iColVal).ToString)
                        Next
                    Next
                    If File.Exists(fileName) Then
                        .ActiveWorkbook().Save() 'fileName)
                    Else
                        .ActiveWorkbook().SaveAs(fileName, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing)
                    End If
                    .ActiveWorkbook.Close()
                End With
                Console.Write("File exported sucessfully")
            End If
        Catch ex As Runtime.InteropServices.COMException
           
            Console.Write("ERROR: " & ex.Message)
        Catch ex As Exception
         
            Console.Write("ERROR: " & ex.Message)
        Finally
            ExlApp.Quit()
            System.Runtime.InteropServices.Marshal.ReleaseComObject(ExlApp)
            aBook = Nothing
            ExlApp = Nothing
           
         
        End Try
    End Sub
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Integration Management Part 2
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…

926 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