Results 1 to 3 of 3

Thread: 20171101 Write DataSet DataTable to Excel Spreadsheet

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Dec 2010
    Posts
    28

    20171101 Write DataSet DataTable to Excel Spreadsheet

    Using ADO with VB.Net I have been able to create a Form and write the GridView data to a file that can be saved as an Excel Spreadsheet. There are Save Dialog annoyances like that the file extension is displayed as '.xls[1]' and when I remove the '[1]' and save the file in the desired location, I'm hit with "The file is in a different format than specified by the file extension. Do you want to open the file now?" Annoyances aside, I get the full Gridview, however many pages, 'Select' column and all and when "Saved As" 'xlsx' is forever after an Excel Spreadsheet. I'd like to correct those difficulties - particularly the 'xls[1]' - and would appreciate any help offered, but my real issue is trying to save the entire SQL database query selection, all 32 columns and not just the Gridview 9 columns.

    I've not been able to translate my SQL Query into an Excel Spreadsheet. When I use WriteXml I see that I do have all of the data but it is rendered as an 'Xml' document - useless to me. I have been looking at this for a long time and have gotten many Errors including my latest "80040154 Class not registered". All advice is appreciated.

    Display Code:
    Code:
    <br />
    <asp:Button id="btn1" Text="Excel" onclick="doExcel" Runat="server" />
    <br />
    Code Behind:
    Code:
    Imports Excel = Microsoft.Office.Interop.Excel
    Imports Microsoft.Office.Interop
    Imports System.Data.SqlClient
    Imports System.Configuration
    Imports System.Data
    Imports System.IO
    
    Partial Class _03Eligible
        Inherits System.Web.UI.Page
    
        Protected Sub doExcel(Source As Object, E As EventArgs)
    
            'Create new SqlDataAdapter
            Dim daEligible As New SqlDataAdapter
            'Create new DataTable
            Dim dtEligible As New DataTable
            'Create new StringWriter
            Dim stEligible As New StringWriter
            'Create a Connection object
            Dim connObject As New SqlConnection
            'Create a Select command to execute
            Dim selCmmd As New SqlCommand
    
            'Set the Connection object connection string to access the WebConfig connection string
            connObject.ConnectionString = ConfigurationManager.ConnectionStrings("connString").ConnectionString
    
            'Set the Select command and Connection object to access the database
            selCmmd.Connection = connObject
    
            'Set the Select command parameters
            selCmmd.CommandText = "SELECT * FROM [Pensions] WHERE (([TYCMS] >= 20) AND ([TYSC] >= 15) AND ([SCLAST] >= 10) AND ([DISCHARGE] <> 'Dishonorable') AND ([DECEASED] IS NULL) AND (DATEDIFF(day, [DOB], GETDATE()) >= 21915)) ORDER BY [NameL], [NameF]"
    
            'Set DataAdapter Connection and Select parameters
            daEligible.SelectCommand = selCmmd
    
            'Open the connection to the database
            connObject.Open()
    
            'Populate the new DataTable
            daEligible.Fill(dtEligible)
    
            'Name the new DataTable
            dtEligible.TableName = "Pensions"
    
            'Close the connection to the database
            connObject.Close()
    
            'Define output data type
            Response.ContentType = "application/ms-excel"
            Response.AddHeader("content-disposition", "attachment;filename=" & ".xls")
            Response.Charset = ""
            EnableViewState = False
    
            'VVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVV
            '   error: 80040154 Class not registered
            'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
            Dim wbEligible As New Excel.Workbook
            wbEligible.Worksheets.Add(dtEligible, "Pensions")
            'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
    
            Response.Write(wbEligible)
            Response.Flush()
            Response.End()
    
        End Sub
    
    End Class

  2. #2
    Frenzied Member KGComputers's Avatar
    Join Date
    Dec 2005
    Location
    Cebu, PH
    Posts
    2,020

    Re: 20171101 Write DataSet DataTable to Excel Spreadsheet

    but my real issue is trying to save the entire SQL database query selection, all 32 columns and not just the Gridview 9 columns.
    When exporting datatables to excel, a solution is to loop through those rows and columns and then assign the value(s) to an excel worksheet cell.
    Code:
    For i = 0 To ds.Tables(0).Rows.Count - 1
    	For j = 0 To ds.Tables(0).Columns.Count - 1
    		xlWorkSheet.Cells(i + 1, j + 1) =  _
    		ds.Tables(0).Rows(i).Item(j)
    	Next
    Next
    Reference: Export Database to Excel
    CodeBank: VB.NET & C#.NET | ASP.NET
    Programming: C# | VB.NET
    Blogs: Personal | Programming
    Projects: GitHub | jsFiddle
    ___________________________________________________________________________________

    Rating someone's post is a way of saying Thanks...

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Dec 2010
    Posts
    28

    Re: 20171101 Write DataSet DataTable to Excel Spreadsheet

    Quote Originally Posted by KGComputers View Post
    This did not work for me as the Workbook / Worksheet reference kept giving me 'error: 80040154 Class not registered'. The solution for that seems to be 'ClosedXml' but I kept getting the following error:

    Install-Package : Could not install package 'ClosedXML 0.90.0'. You are trying to install this package into a project that targets '.NETFramework,Version=v4.5', but the package does not contain any assembly references or content files that are compatible with that framework. For more information, contact the package author.

    I got this error message for every 'NETFramework,Version' that I tried from 2.0 through 3.5 to 4 and 4.5

    So, I'm tired of messing with this and am going to go with Loganix77 from ASP.Net and create another hidden Gridview. I have got that technology working.

    Thanks for the interest and help but I just could not get around 'error: 80040154 Class not registered' and ClosedXml not wanting to install and not being able to 'Imports ClosedXML.Excel'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width