Improve company productivity with a Business Account.Sign Up

x
?
Solved

Writing DataRow into Excel spreadsheet

Posted on 2006-11-06
4
Medium Priority
?
1,460 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
When you have multiple client accounts to manage, it often feels like there aren’t enough hours in the day. With too many applications to juggle, you can’t focus on your clients, much less your growing to-do list. But that doesn’t have to be the cas…
Watch the working video to know how to import Outlook PST/OST files to Amazon WorkMail. Kernel released this tool which is very easy to use and migrate single or multiple PST and OST files to Amazon WorkMail. To know more about Kernel Import PST to …

584 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