-
Nov 1st, 2017, 04:10 PM
#1
Thread Starter
Junior Member
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
-
Nov 2nd, 2017, 08:06 AM
#2
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
-
Nov 2nd, 2017, 11:35 AM
#3
Thread Starter
Junior Member
Re: 20171101 Write DataSet DataTable to Excel Spreadsheet
Originally Posted by KGComputers
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|