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!!!

Answer 1

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