export csv to excel template

[h1dden]

Hi,

I have One Form with and Import Button and Excel Spreadsheet in it. When user click on import button it will ask for the csv file then will export the csv file into my excel template and display it in the spreadsheet control already in my form.

Can you provide me a code on how to do this.

Another problem I had was that my vb.net is saying that excel.application in

Dim xcl as Excel.application

declaration is not declared when infact I had already added a reference to Microsoft Excel 11.0 object library and i also added a reference to Microsoft Office 11.0 object library.

Please help mo on this.

Using VB 2003/1.1 and Office 2003

THanks,
Ian

[h1dden]

anybody who could help me on this?

[jmcilhinney]

Read the CSV using ADO.NET. It works the very same way as reading from Access. See www.connectionstrings.com for details of how to tailor your connection string and SQL code. Alternatively you could read the data in a loop using a StreamReader. Once you have the data it's up to you how you get it into your spreadsheet as we don't know anything about the structure.

As for your second problem, adding a reference to a library gives you access to all the types it contains. You still need to specify those types by name though. You either need to qualify your types with the namespace they are members of or else import that namespace, either project-wide in the properties or else for the current file at the top of the code. You would qualify the class name like this:

Dim xcl As Microsoft.Office.Interop.Excel.Application

Doing that every time gets very laborious though, so it is a good idea to import the namespace. With Office apps it is regular to do so with an alias:

Imports Excel = Microsoft.Office.Interop.Excel

Once you've added that at the top of your code "Excel" becomes an alias for "Microsoft.Office.Interop.Excel". The alternative would be to just import the Interop namespace:

Imports Microsoft.Office.Interop

Your code would then look the same but using "Excel" in your code would have a subtly different meaning. The net result is the same though so whichever you like.

[RobDog888]

You will only get the .Interop class if you have installed the .NET Primary Interop Assemblies. Checkout the link in my signature for more info and installation procedures.

[RobDog888]

Just noticed that your running 2003 so I wrote a quick example for you.

Option Explicit On 
Option Strict On
'Add a reference to MS Excel xx.0 Object Library
Imports Microsoft.Office.Interop
 
Public Class Form1
 
    Inherits System.Windows.Forms.Form
 
 
    Private moApp As Excel.Application
 
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            '<INITIALIZE EXCEL>
            moApp = DirectCast(GetObject(, "Excel.Application"), Excel.Application)
        Catch ex As Exception
            '<CREATE EXCEL SINCE NOT RUNNING>
            If TypeName(moApp) = "Nothing" Then
                moApp = DirectCast(CreateObject("Excel.Application"), Excel.Application)
            Else
                MessageBox.Show(ex.Message, "VB/Office Guru™ Excel Demo™ .NET", _
                MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            End If
        End Try
    End Sub
 
    Private Sub Form1_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
        If Not moApp Is Nothing Then
            moApp.Quit()
        End If
        moApp = Nothing
        System.Runtime.InteropServices.Marshal.ReleaseComObject(moApp)
    End Sub
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim oWB As Excel.Workbook = moApp.Workbooks.Add
        Dim oSht As Excel.Worksheet = DirectCast(oWB.Sheets("Sheet1"), Excel.Worksheet)
        oSht.Cells(1, 1) = "2"
        oSht.Cells(1, 2) = "2"
        oSht.Cells(1, 3) = "=SUM(A1, B1)"
        moApp.Visible = True
        MessageBox.Show("Before Calculate()")
        oSht.Cells(1, 1) = "3"
        oSht.Calculate()
        MessageBox.Show("After Calculate()")
        oWB.Close(True, "C:\Test.xls")
        oSht = Nothing
        oWB = Nothing
    End Sub

[h1dden]

Hi,

THanks for the reply. I am attaching my VB.net form for clarity.

Here is what i want to do.

1. At form load the excel spreadsheet in my form will load a blank template which contains formatting(cells that are protected).
2. Click on Import button, the program will ask for the csv file and then populate the content of it to the excel template.
3. Save it as CSV file, but there will be an option for the user to save/export only modified rows.

Hope you could help.

Thanks,
Ian

[h1dden]

I have also attached my csv file.

When i open my csv file in the spreadsheet control using

cd.Filter = "CSV File(*.CSV)|*.CSV;*.csv | All files (*.*)|*.*"
        cd.FilterIndex = 0
        cd.InitialDirectory = Application.ExecutablePath
        cd.ShowDialog(Me)
 
        AxSpreadsheet1.CSVURL = cd.FileName

the values like 000,1101110010005 and the likes where not displayed exactly as the csv file. 000 is replaced by 0 and 1101110010005 is replaced by 1.10111E+12. I want all values to just appear exactly as it is in the csv file. anybody can help me on this?

[RobDog888]

Format the excel columns as text instead of numbers.

[h1dden]

any idea about my other questions?