This project has moved. For the latest updates, please go here.

Insert Recordset to Existing Worksheet

May 30, 2014 at 2:17 PM
Let me preface this with the fact that I'm a SQL DBA and not a programmer, so my code may not be the most efficient.

With that said, I'm moving away from Interop.Excel for many reasons but it seems I'm having problems converting some of my scripts to use ClosedXML.

My issue is not having my query results in the spreadsheet. I pass the results into the script in a variable but don't get the expected result. Before I could simply do the following.
        objWorkBook = objExcel.Workbooks.Open(strReportFileLocation & strReportFileName)

        myRecordset = Dts.Variables("gvRecordSet").Value

        objWorkSheet = objWorkBook.Worksheets("SomeCharges")

        __objWorkSheet.Cells(3, 1).CopyFromRecordset(myRecordset)__
Now with ClosedXML I'm using the following code. I do not receive any errors but I have no data on my spreadsheet either.
  Public Sub Main()
    Dim FilePath As String
    Dim Filename As String
    Dim Recordset As Object
    Dim filespec As String

    FilePath = Dts.Variables("gvReportFileLocation").Value.ToString
    Filename = Dts.Variables("gvReportFileName").Value.ToString
    Recordset = CType(Dts.Variables("gvRecordSet").Value, String)
    filespec = (FilePath & Filename)

    Dim Workbook As New XLWorkbook(filespec)
    Dim Worksheet As IXLWorksheet

    Worksheet = Workbook.Worksheet("SomeFees")

    Dim CellForNewData As IXLCell

        CellForNewData = Worksheet.Cell(3, 1)

        CellForNewData.InsertData(Recordset)
Thanks in advance for any help.
Coordinator
May 30, 2014 at 4:33 PM
I need a way to reproduce the problem. Create a small function that creates a recordset, populates it with 2 rows and then inserts the recordset in the worksheet.

Thanks
May 30, 2014 at 7:36 PM
This should work for you and it's all that's happening when the process runs. the Recordset variable could have 0-80k rows. Since this script is being run as a task in SSIS I'm not sure how good of a test this will be. The behavior might be quite different.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports ClosedXML.Excel



Public Class ScriptMain
Public Sub Main()
    Dim FilePath As String
    Dim Filename As String
    Dim filespec As String

    Dim Recordset As Object

    Recordset = "Row1 Column1, Row1 Column2" & vbCrLf & "Row2 Column1, Row2 Column2"  'CType(Dts.Variables("gvRecordSet").Value, String)

    FilePath = "C:\Billing\"  'Dts.Variables("gvReportFileLocation").Value.ToString
    Filename = "Test_Excel.xlsx" 'Dts.Variables("gvReportFileName").Value.ToString

    filespec = (FilePath & Filename)

    Dim Workbook As New XLWorkbook(filespec)
    Dim Worksheet As IXLWorksheet

    Worksheet = Workbook.Worksheet("sheet1")

    Dim NumberOfLastRow As Integer
    Dim CellForNewData As IXLCell
    Try
        NumberOfLastRow = Worksheet.LastRowUsed().RowNumber
        CellForNewData = Worksheet.Cell(3, 1)

        CellForNewData.InsertData(Recordset.ToString)

    Catch ex As Exception
    End Try

End Sub
End Class
Coordinator
May 30, 2014 at 7:50 PM
You're passing a string to InsertData. You need to pass some kind of collection to it (an array, list, data table, linq query, or anything that implements IEnumerable). Take a look at: https://closedxml.codeplex.com/wikipage?title=Inserting%20Data&referringTitle=Documentation