Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel Automation

Status
Not open for further replies.
born2program

born2program

Technical User
Sep 18, 2006
85
US
I am trying to export data from a dataset in some vb script behind my asp page. When I run the code I get an error that says "Specified Cast Not Valid". This is the code I have so far. Didn't know whether to post this in the vb forum or the asp. Any help is appreciated. Thanks.

Private Sub btnExportXLS_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExportXLS.Click
Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet

Dim iRow As Integer
Dim iCol As Integer

Dim a As Integer
Dim Folder As String
Dim TopCellAddr As String
Dim ColumnLetter As String

Dim objDataHandler As New DataHandler
Dim objDeptDataSet As New DataSet
Dim objDeptDataView As New DataView

Dim dt As DataTable
Dim dr As DataRow

objDeptDataSet = objDataHandler.getChecksToBePrinted
objDeptDataView = objDeptDataSet.Tables.Item(0).DefaultView


oExcel = CreateObject("Excel.Application")
oExcel.Visible = True
End Sub
 
Sort by date Sort by votes
It throws the error when it trys to create the excel object. The line of code is:

oExcel = CreateObject("Excel.Application")

Thanks.
 
Upvote 0 Downvote
A quick search in this form gave me these 2 helpful threads:
thread855-1345870
thread855-1333083
 
Upvote 0 Downvote

I finally did it with the following code. I just need to know how to save it without any formating (plain text, no background color, etc).

Code: 
Private Sub btnExportXLS_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExportXLS.Click

        Dim stringWrite As New System.IO.StringWriter
        Dim htmlWrite As New HtmlTextWriter(stringWrite)

        Response.Clear()
        Response.AddHeader("content-disposition", "attachment;filename=FileName.xls")
        Response.Charset = ""
        Response.Cache.SetCacheability(HttpCacheability.NoCache)
        Response.ContentType = "application/vnd.xls"
        dgOverpay.RenderControl(htmlWrite)
        Response.Write(stringWrite.ToString())
        Response.End()


    End Sub
 
Upvote 0 Downvote
I have the following code, which saves a datagrid as an excel spreadsheet. The only problem I have is that I need it to just be plain text in the spreadsheet (no column headings, no formatting of anykind). Anyone know how to do this. Your help is appreciated. Thanks

Code: 
Private Sub btnExportXLS_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExportXLS.Click

        Dim stringWrite As New System.IO.StringWriter
        Dim htmlWrite As New HtmlTextWriter(stringWrite)

        Response.Clear()
        Response.ClearHeaders()
        Response.AddHeader("content-disposition", "attachment;filename=FileName.xls")
        Response.Charset = ""
        Response.Cache.SetCacheability(HttpCacheability.NoCache)
        Response.ContentType = "application/vnd.xls"
        dgOverpay.RenderControl(htmlWrite)
        Response.Write(stringWrite.ToString())
        Response.End()


    End Sub
 
Upvote 0 Downvote
It will be exported with any formatting that you have on your datagrid. Remove any formatting you have there and it will be exported in the same format.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.
 
Upvote 0 Downvote
I am using very similar code trying to export data to Excel have tried this code but I am getting an error on the Render line...


I get a error of "Control 'Gridview1' of type 'GridView' must be placed inside a form tag with runat=server. "

on the line
Gridview1.RenderControl(htw)
(line 32 of the code behind file)

The Gridview itself displays fine. I get the error when I click on the 'Export to Excel button'

Code: 
.apsx file


<%@ Page Language="VB" AutoEventWireup="false" CodeFile="testExcel.aspx.vb" Debug="true" Inherits="testExcel" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "[URL unfurl="true"]http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">[/URL]

<html xmlns="[URL unfurl="true"]http://www.w3.org/1999/xhtml"[/URL] >
<head runat="server">
    <title>Untitled Page</title>
</head>

<body>
    <form id="form1" runat="server">
    <div>
   <asp:Button ID="Btn_Export" runat="server" Text="Export to Excel" OnClick="Btn_Export_Click" /> 
    <asp:gridview ID="Gridview1" runat="server"></asp:gridview>
    
    </div>
    </form>
</body>
</html>


.aspx.vb file

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO

Partial Class testExcel
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        GetData()
    End Sub


    Public Function GetData()
        Dim myConnection As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("IncidentConnectionString").ToString)
        Const strSQL As String = "usp_incidentstatus_SELECT" 'Using a Stored Procedure
        Dim MyDataAdapter As SqlDataAdapter = New SqlDataAdapter(strSQL, myConnection)
        Dim MyDataSet As DataSet = New DataSet
        MyDataAdapter.Fill(MyDataSet)         Gridview1.DataSource = MyDataSet
        Gridview1.DataBind()
    End Function

    Protected Sub Btn_Export_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Btn_Export.Click
        Response.ClearContent()
        Response.AddHeader("content-disposition", "attachment; filename=MyExcelFile.xls")
        Response.ContentType = "application/excel"

        Dim sw As StringWriter = New StringWriter
        Dim htw As HtmlTextWriter = New HtmlTextWriter(sw)

        Gridview1.RenderControl(htw)
        Response.Write(sw.ToString())
        Response.End()
    End Sub
End Class

Line 32 of the aspx.vb file is the one that generates the error... Does anyone have any Ideas as to what is causing this error?

Thanks in advance for any assistance.

Mark Buckley
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

G12Consult
  • Locked
  • Question
Importing Excel to SQL Server using EPPlus
Replies
3
Views
227
jbenson001
jbenson001
wbodger
  • Locked
  • Question
vb.net to Excel data coming from a web service
Replies
2
Views
94
wbodger
wbodger
primagic
  • Locked
  • Question
Create Excel file.
Replies
1
Views
36
primagic
primagic
primagic
  • Locked
  • Question
Reading from Excel file
Replies
1
Views
35
jbenson001
jbenson001
primagic
  • Locked
  • Question
Uploading Excel to SQL Server
Replies
5
Views
68
gk53
gk53

Part and Inventory Search

Sponsor

Back
Top