Export to excel using vb.net codebehind

[born2program]

I have an asp.net application that I need to export data from a Oracle table to excel. I am writing data to an existing excel template. I was able to do this using OLEDB, however, when I write the data to the spreadsheet it inserts all data as text. How can I do this and keep each fields datatype? I have tried excel automation and ADODB, both of which I had issues when I added the refereces needed. I have also tried to set IMEX=0 and IMEX=2 in my connection string below. Thanks in advance for any help.

here is my current code.

Private Sub ExportGL()

        Dim objDataHandler As New DataHandler
        Dim objGLData As New DataSet
        Dim objGLDataView As New DataView

        objGLData = objDataHandler.getGLData
        objGLDataView = objGLData.Tables.Item(0).DefaultView

        Dim count As Integer                'Count of data rows from the database table
        Dim a As Integer                    'For loop counter
        Dim BalanceTotal As Double          'Variable to hold total of amount (will be inserted as final row so that the GL Sheet will balance.
        Dim Bnumb As String                 'Holds B-number to be used in last row totals
        Dim offset As String                'Holds the GL account description that is in the last row totals (will be used in column "DESC-1")
        Dim FinDesc2 As String = ""
        Dim Comp As String
        Dim BalTranCode As String = "60"
        Dim GLAccount As String
        Dim GLCenter As String

        Dim TDate As Date                   'holds the date of batch to be used in last line totals
        Dim conn1 As New OleDbConnection    'Connection for Excel
        Dim cmd As New OleDbCommand         'Command to hold and process INSERT SQL statement


        'Connection string to connect to the Excel Spreadsheet
        Dim XLConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\test\GL_Template1.xls;" & _
        "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2"""

        
    'Makes a copy of the template file for the data to be written to.
        File.Copy("C:\test\GL_Template.xls", "C:\test\GL_Template1.xls")

    'Open the connection to Excel template if the connection is closed
        If conn1.State = ConnectionState.Closed Then
            conn1.ConnectionString = XLConnection
            conn1.Open()
        End If

        cmd.Connection = conn1

        count = objGLDataView.Count     'assign count of rows from database to count variable

        cmd.Parameters.Add("@COMPANY", OleDbType.VarChar)
        cmd.Parameters.Add("@TRANSCODE", OleDbType.VarChar)
        cmd.Parameters.Add("@GLACCOUNT", OleDbType.VarChar)
        cmd.Parameters.Add("@CENTER", OleDbType.VarChar)
        cmd.Parameters.Add("@EFFECTIVEDATE", OleDbType.DBDate)
        cmd.Parameters.Add("@AMOUNT", OleDbType.Numeric)
        cmd.Parameters.Add("@DESC1", OleDbType.VarChar)
        cmd.Parameters.Add("@DESC2", OleDbType.VarChar)
        cmd.Parameters.Add("@DESC3", OleDbType.VarChar)


    'loop through records, inserting each record into an excel cell.
        For a = 0 To count - 1
            With objGLDataView.Table.Rows(a)

                cmd.Parameters.Item("@COMPANY").Value = .Item("COMPANY")
                cmd.Parameters.Item("@TRANSCODE").Value = .Item("TRANCODE")
                cmd.Parameters.Item("@GLACCOUNT").Value = .Item("GLACCOUNT")
                cmd.Parameters.Item("@CENTER").Value = .Item("CENTER")
                cmd.Parameters.Item("@EFFECTIVEDATE").Value = .Item("EFFECTIVEDATE")
                cmd.Parameters.Item("@AMOUNT").Value = .Item("AMOUNT")
                cmd.Parameters.Item("@DESC1").Value = .Item("DESC1")
                cmd.Parameters.Item("@DESC2").Value = .Item("DESC2")
                cmd.Parameters.Item("@DESC3").Value = .Item("DESC3")


                cmd.CommandText = "INSERT INTO [Logsheet$] ([Company], [Trans Code], [Account], [Center], [Eff Date], [Amount], [Desc-1], [Desc-2], [Desc-3]) 		" & _
                  "VALUES (@COMPANY, @TRANSCODE, @GLACCOUNT, @CENTER, @EFFECTIVEDATE, @AMOUNT, @DESC1, @DESC2, @DESC3)"

                Comp = .Item("COMPANY")
                BalanceTotal = BalanceTotal + .Item("AMOUNT")       'keep a total of the amounts to be included in last row
                Bnumb = .Item("DESC3")
                TDate = .Item("EFFECTIVEDATE")

                cmd.ExecuteNonQuery()
            End With
        Next

          
        conn1.Close()

    End Sub

 

[jmeckley]

since your are coping a template.
1. is the template columns formatted?
2. is this an excel template, or a workbook used as a template?

if the columns are not formatted try formatting them in the source/template. If that doesn't work, try option 2. I haven't worked with template files, so i'm not aware of how exactly to use them.

i like to use

http://www.carlosag.net/Tools/ExcelXmlWriter/Default.aspx

when creating excel workbooks. it's very light weight and doesn't require excel. neither option above works, carlos' tool.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[born2program]

1. all of the columns are formatted prior to and after I write to the spreadsheet. It just inserts the data as text instead of number, etc.
2.This is a Workbook used as a template with 2 worksheets in it.

I'll look at the link you gave.

 

[born2program]

I used the ExcelXmlWriter tool at the link above, but I get the following error when I add the reference to my project.

Any thoughts?

An error occured saving the project file 'PrintChecks.vbproj'.  Access is denied.

 

[jmeckley]

not sure, i have never had a problem referencing the assembly in my projects.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[AlexCuse]

This is kind of a shot in the dark, but I've gotten similar error messages trying to use VS2005 on vista, if I did not select the "run as administrator" option.

If thats' not it, I think you'll need to provide more info.



[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[born2program]

I think I actually found a fix this morning. I added the ASPNET account to my local host (C:\Inetpub\

http://wwwroot).

I also unchecked a the read only box set on the .vbproj file. Thanks for your help.