none
Using Visual Studio to manipulate excel data

    Question

  • I'm creating an application with a button click event to create a procedure that looks at two excel files to manipulate their data and copy it into a new excel workbook.  Whats the best way for me to connect to Excel to be able to use its functions through Visual Studio using Visual Basic.  I've made a reference to the Excel Object library...but I'm not exactly sure what to do next?  Should I save the data in 2 different data sets and then manipulate it... or is there an easier way to open both sheets and input a formula into the new sheet to do the calculations?  Any help will be greatly appreciated!!! Smile
    Sunday, July 08, 2007 5:00 AM

Answers

  • Hi, HELPNewToVB,

     

    There would be two ways to manipulate data in Excel,

    one is OLEDB object, and the other is to refer to Microsoft Excel Object Library.

    As you choose to use the second one,

    I wrote a small sample to illustrate how to read and save Excel File.

    Don't foget to add Microsoft Excel Object Library as reference.

     For VC#

    Code Snippet

    using System;

    using System.Collections.Generic;

    using System.ComponentModel;

    using System.Data;

    using System.Drawing;

    using System.Text;

    using System.Windows.Forms;

    using Microsoft.Office.Interop.Excel;

     

    namespace excel

    {

        public partial class Form1 : Form

        {

            public Form1()

            {

                InitializeComponent();

               

            }

     

            private void button1_Click(object sender, EventArgs e)

            {

                object MissValue = System.Reflection.Missing.Value;

     

                string data1 = "";

                string data2 = "";

                ApplicationClass app = new ApplicationClass();

                Workbook workbook1 = app.Workbooks.Open(@"E:\1.xlsx", 0, false, 5, MissValue, MissValue, true, XlPlatform.xlWindows, @"\t", true, false, 0, true, 1, 0);

                Workbook workbook2 = app.Workbooks.Open(@"E:\2.xlsx", 0, false, 5, MissValue, MissValue, true, XlPlatform.xlWindows, @"\t", true, false, 0, true, 1, 0);

                Worksheet worksheet1 = (Worksheet)workbook1.Sheets[1];

                Worksheet worksheet2 = (Worksheet)workbook2.Sheets[1];

               

     

                try

                {

                    for (int i = 2; i < 4; i++)

                    {

                        data1 += ((Range)worksheet1.Cells[i, 2]).Value2.ToString() + "\n";

                        data2 += ((Range)worksheet2.Cells[i, 2]).Value2.ToString() + "\n";

     

                    }

     

                    label1.Text = data1;

                    label2.Text = data2;

                }

                catch

                {

                    app.Quit();

                }

     

                Workbooks newworkbooks = app.Workbooks;

                Workbook newworkbook = newworkbooks.Add(MissValue);

     

                Sheets newsheets = newworkbook.Worksheets;

                Worksheet newworksheet = (Worksheet)newsheets.get_Item(1);

                try

                {

                    for (int i = 1; i < 4; i++)

                    {

                        for (int j = 1; j < 3; j++)

                        {

                            newworksheet.Cells[i, j] = ((Range)worksheet1.Cells[i, j]).Value2.ToString();

                        }

                    }

                    for (int i = 2; i < 4; i++)

                    {

                        for (int j = 1; j < 3; j++)

                        {

                            newworksheet.Cells[i + 2, j] = ((Range)worksheet2.Cells[i, j]).Value2.ToString();

                        }

                    }

                    newworksheet.SaveAs(@"E:\3.xlsx", MissValue, MissValue, MissValue, MissValue, MissValue, MissValue, MissValue, MissValue, MissValue);

                }

                catch

                {

                    app.Quit();

                }

                app.Quit();

            }

        }

    }

     

    for VB.net

    Code Snippet

    Imports System

    Imports System.Collections.Generic

    Imports System.ComponentModel

    Imports System.Data

    Imports System.Drawing

    Imports System.Text

    Imports System.Windows.Forms

    Imports Microsoft.Office.Interop.Excel

    Public Class Form2

     

        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

            Dim MissValue As Object = System.Reflection.Missing.Value

            Dim data1 As String = ""

            Dim data2 As String = ""

            Dim app As New ApplicationClass()

            Dim workbook1 As Workbook = app.Workbooks.Open("E:\1.xlsx", 0, False, 5, MissValue, MissValue, _

             True, XlPlatform.xlWindows, "\t", True, False, 0, _

             True, 1, 0)

            Dim workbook2 As Workbook = app.Workbooks.Open("E:\2.xlsx", 0, False, 5, MissValue, MissValue, _

             True, XlPlatform.xlWindows, "\t", True, False, 0, _

             True, 1, 0)

            Dim worksheet1 As Worksheet = DirectCast(workbook1.Sheets(1), Worksheet)

            Dim worksheet2 As Worksheet = DirectCast(workbook2.Sheets(1), Worksheet)

            Try

                For i As Integer = 2 To 3

                    data1 += DirectCast(worksheet1.Cells(i, 2), Range).Value2.ToString() + "" & Chr(10) & ""

                    data2 += DirectCast(worksheet2.Cells(i, 2), Range).Value2.ToString() + "" & Chr(10) & ""

                Next

                Label1.Text = data1

                Label2.Text = data2

            Catch

                app.Quit()

            End Try

     

            Dim newworkbooks As Workbooks = app.Workbooks

            Dim newworkbook As Workbook = newworkbooks.Add(MissValue)

            Dim newsheets As Sheets = newworkbook.Worksheets

            Dim newworksheet As Worksheet = DirectCast(newsheets.Item(1), Worksheet)

            Try

                For i As Integer = 1 To 3

                    For j As Integer = 1 To 2

                        newworksheet.Cells(i, j) = DirectCast(worksheet1.Cells(i, j), Range).Value2.ToString()

                    Next

                Next

                For i As Integer = 2 To 3

                    For j As Integer = 1 To 2

                        newworksheet.Cells(i + 2, j) = DirectCast(worksheet2.Cells(i, j), Range).Value2.ToString()

                    Next

                Next

                newworksheet.SaveAs("E:\3.xlsx", MissValue, MissValue, MissValue, MissValue, MissValue, _

                 MissValue, MissValue, MissValue, MissValue)

            Catch

                app.Quit()

            End Try

            app.Quit()

        End Sub

    End Class

     

     

    Excel1

    id name
    1 Tom
    2 John

    Excel2

    id name
    3 Jacky
    4 John

     

    Hopes this helps,

    Regards

    Tuesday, July 10, 2007 2:41 AM