none
ADO.NET 3.5: Write DataSet/Table to Excel 2007 .xlsx spreadsheet (ping Paul Clement) RRS feed

  • Question

  • (ping Paul Clement)

    I am attempting to write the entire contents of an ADO.NET DataSet to an Excel 2007 spreadsheet and, while having partial success, I am unable to dump the contents of the DataSet into the .xlsx file.  "Partial success" meaning that I can generate the spreadsheet(s) ok, but cannot get the DataAdapter.Update to work.

    Using these as guides:

    http://social.msdn.microsoft.com/forums/en-us/vbgeneral/thread/019E7886-B681-4EB2-9DD4-65FACC236435

    http://support.microsoft.com/kb/316934

    So, what I have so far:

    1. CREATE Table statement(s) working to generate a series of .xlsx files.  no problems.

    2. "SELECT * FROM xlsx" to Fill a DataAdapter (Table1).  At this point, they are emply .xlsx files that were just created.

    3. Secondary Connection retrieves a DataTable from another db to be used to populate the empty xlsx files.  This is a DataTable that is used to merge with the emply DataTable in Step 2 above.

    4. Update and Insert Commands instantiated on the DataAdapter with (?) parameters.

    5. DataAdapter.Update(MergedDataTable) is issued.  This runs without error, but nothing is inserted into the .xlsx spreadsheets.

    Please have a look at the code below and see if you can spot any issues --- I am at a standstill after trying so many different ways that I need a new set of eyes on this one.

    Thanks in advance!

    Edit: code is located in post below because I attempted to edit it and the formating was poor.

    James Crandall ~ http://javitechnologies.com Spatial Database Solutions

    • Edited by jamesfreddyc Tuesday, September 20, 2011 12:20 PM
    Monday, September 19, 2011 7:53 PM

Answers

All replies

  • Sorry -- I never remember to NOT edit any code that you add to a post because it totally messes up the formatting.  Please let me know if you want me to repost the code.

     

    Private Sub GenerateExcelFiles(ByVal SEUBasinNum As String)
            
            Dim m_sConn1 As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & AppPath &"\" & SEUBasinNum & ".xlsx;;Extended Properties=""Excel 12.0 Xml;HDR=Yes"""
    
            If Dir(AppPath & "\" & SEUBasinNum & ".xlsx") <> "" Then _
                  Kill(AppPath & "\" & SEUBasinNum & ".xlsx")
    
            Dim ExcelConnection As New System.Data.OleDb.OleDbConnection(m_sConn1)
            ExcelConnection.Open()
    
            Dim SQLDDLCommand As String = _
            "CREATE TABLE " & SEUBasinNum & _
            "(TaxYear INTEGER, " & _
            "SpecialDistrictCode TEXT(20), " & _
            "ParcelIDNumber TEXT(16), " & _
            "Assessment NUMERIC(18, 2), " & _
            "Rate NUMERIC(18, 2), " & _
            "Units NUMERIC(18, 2))"
    
            Dim ExcelCommand As New System.Data.OleDb.OleDbCommand(SQLDDLCommand, ExcelConnection)
            ExcelCommand.ExecuteNonQuery()
            ExcelConnection.Close()
    
    End Sub
    
    Public Sub BuildExportFile_cipAreas_Excel(ByVal SEUBasinNum As String)
    
            Try
    
                GenerateExcelFiles(SEUBasinNum)
    
    	    'Get the datatable to be used to populate the .xlsx spreadsheets
    	    Dim TaxCollDALC As stormsDAL.TaxCollExportDALC = New stormsDAL.TaxCollExportDALC
                Dim retDT As DataTable = TaxCollDALC.Insert_TaxCollExport_cipAreas(Me.AcctDescr) 'add the datatable to be used to merge/populate the .xlsx file
                TaxCollDALC.Dispose()
    
    	    Dim m_sConn2 As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & AppPath & "\" & SEUBasinNum & ".xlsx;;Extended Properties=""Excel 12.0 Xml;HDR=Yes"""
                Dim ExcelConnection2 As New System.Data.OleDb.OleDbConnection(m_sConn2)
                Dim da As New OleDbDataAdapter("SELECT * From [" & SEUBasinNum & "$]", ExcelConnection2)
                ExcelConnection2.Open()
    
                Dim ds As DataSet = New DataSet()
                da.Fill(ds, SEUBasinNum) 'add the datatable from the blank .xlsx file
    
    	    ds.Tables(tabName).Merge(retDT.Copy, False, MissingSchemaAction.Add) 'merge the populated datatable w the empty datatable
    
                'Verify that the rows were merged
    	    rowCount = ds.Tables(tabName).Rows.Count 
                For i = 0 To 10
                    txyr = ds.Tables(tabName)(i)("TaxYear")
                    specd = ds.Tables(tabName)(i)("SpecialDistrictCode")
                    pid = ds.Tables(tabName)(i)("ParcelIDNumber")
                    assmt = ds.Tables(tabName)(i)("Assessment")
                    rate = ds.Tables(tabName)(i)("Rate")
                    unit = ds.Tables(tabName)(i)("Units")
                Next i
                'all there looks good!
    
    
    	    'Generate the UpdateCommand and add the parameters for the command
                da.UpdateCommand = New OleDbCommand("UPDATE [" & SEUBasinNum & "$] SET " & _
                "TaxYear = ?, " & _
                "SpecialdistrictCode = ?, " & _
                "ParcelIDNumber = ?, " & _
                "Assessment = ?, " & _
                "Rate = ?, " & _
                "Units = ? " & _
                "WHERE ParcelIDNumber = ?", ExcelConnection2)
    
                da.UpdateCommand.Parameters.Add("@TaxYear", OleDbType.Integer).SourceColumn = "TaxYear"
                da.UpdateCommand.Parameters.Add("@SpecialDistrictCode", OleDbType.Char, 20).SourceColumn = "SpecialDistrictCode"
                da.UpdateCommand.Parameters.Add("@ParcelIDNumber", OleDbType.Char, 16).SourceColumn = "ParcelIDNumber"
                da.UpdateCommand.Parameters.Add("@Assessment", OleDbType.Decimal).SourceColumn = "Assessment"
                da.UpdateCommand.Parameters.Add("@Rate", OleDbType.Decimal).SourceColumn = "Rate"
                da.UpdateCommand.Parameters.Add("@Units", OleDbType.Decimal).SourceColumn = "Units"
    
                '' Generate the InsertCommand and add the parameters for the command.
                da.InsertCommand = New OleDbCommand("INSERT INTO [" & SEUBasinNum & "$] (TaxYear, SpecialDistrictCode, ParcelIDNumber, Assessment, Rate, Units) VALUES (?, ?, ?, ?, ?, ?)", ExcelConnection2)
                da.InsertCommand.Parameters.Add("@TaxYear", OleDbType.Char, 10, "TaxYear")
                da.InsertCommand.Parameters.Add("@SpecialDistrictCode", OleDbType.Char, 20).SourceColumn = "SpecialDistrictCode"
                da.InsertCommand.Parameters.Add("@ParcelIDNumber", OleDbType.Char, 16).SourceColumn = "ParcelIDNumber"
                da.InsertCommand.Parameters.Add("@Assessment", OleDbType.Decimal).SourceColumn = "Assessment"
                da.InsertCommand.Parameters.Add("@Rate", OleDbType.Decimal).SourceColumn = "Rate"
                da.InsertCommand.Parameters.Add("@Units", OleDbType.Decimal).SourceColumn = "Units"
    
                ' Apply the dataset changes to the actual data source (the workbook).
                da.Update(ds.Tables(tabName)) '(ds.Tables("TmpCipRptExp")) '(ds.Tables(tabName))
                ExcelConnection2.Close()
    
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
        End Sub
    

     


    James Crandall ~ http://javitechnologies.com Spatial Database Solutions

    Monday, September 19, 2011 7:57 PM
  • Bump To The Top
    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    Tuesday, September 20, 2011 12:21 PM
  • If the code executes without exceptions I would suspect that the RowState value of the Rows is "Unchanged". See the below article on how to use Merge with a Dataset:

    http://www.knowdotnet.com/articles/datasetmerge.html

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by jamesfreddyc Tuesday, September 20, 2011 3:45 PM
    Tuesday, September 20, 2011 1:46 PM
  • Guess I should get into the habit of testing for HasChanges on the Dataset, huh?

    Thanks a bunch, Paul.  I'll make some modifications and report back to this thread.

     

    j


    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    Tuesday, September 20, 2011 1:50 PM
  • Paul,

    Thanks again for the input --- the second set of eyes on this really has helped. 

    Results:

    I tried to issue the AcceptChangesDuringFill on the DataAdapter, but it didn't seem to have resolved the issue -- no errors, but I got the same result: nothing inserted into the spreadsheets.  Perhaps this is a placement of the code issue?  Not sure, I added this line here:

     Dim ExcelConnection2 As New System.Data.OleDb.OleDbConnection(m_sConn2)
    Dim da As New OleDbDataAdapter("SELECT * From [" & SEUBasinNum & "$]", ExcelConnection2)
    ExcelConnection2.Open()
    
    Dim ds As DataSet = New DataSet()
    Dim tabName As String = SEUBasinNum.ToString
    da.Fill(ds, tabName)
    da.AcceptChangesDuringFill = False 'does not do anything
    ds.Tables.Add(retDT.Copy)
    

     

    It had no effect actually.  This however does work and the rows are inserted into the spreadsheets just fine, but I am not sure if looping thru all of these rows in each DataTable and setting the RowState values to SetAdded() is the most efficient/best approach:

     

    ds.Tables(tabName).Merge(retDT.Copy)
    
     For Each dr As DataRow In ds.Tables(tabName).Rows
         dr.SetAdded()
      Next
    

     


    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    Tuesday, September 20, 2011 3:35 PM
  • I'd be thinking that you probably want to call AcceptChangesDuringFill before calling the Fill method.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Tuesday, September 20, 2011 4:06 PM
  • I'd be thinking that you probably want to call AcceptChangesDuringFill before calling the Fill method.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Yeah, I see what you mean from the example I posted.  However, moving it above the  Fill method still produces the same result.  Again, manually setting the rowstate to SetAdded() marks them as added and the insert works then, but just not sure why the former doesn't work and if this is inefficient.  (I cannot compare using just the  AcceptChangesDuringFill property to manually invoking SetAdded).

    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    Tuesday, September 20, 2011 5:32 PM
  • Hello,

    you can also easily export DataSet to Excel file with this C# / VB.NET Excel component.

    Here is a sample VB.NET code:

    ' Create new Excel file.
    Dim excelFile = New ExcelFile()
    
    ' Create new worksheet for each DataTable and insert DataTable's data into it.
    For Each dataTable As DataTable In dataSet.Tables
    	excelFile.Worksheets.Add(dataTable.TableName).InsertDataTable(dataTable, "A1", True)
    Next
    
    ' Save Excel file to XLSX format.
    excelFile.SaveXlsx(dataSet.DataSetName & ".xlsx")

    Monday, May 7, 2012 11:25 AM