Excel sheet connectivity..

[rjbrjb777]

helo friends.
i need to deal with more than 10 excel sheets..

so i want to make an desktop application in Visual Studio(c#),
which performs as followings..:

1) first user can choose on which file he wants to work on.
2) then retrieving column wise data, and performing many mathematical calculation
on that data.
3) and all the result should be stored in another new excel sheet..

so can anybody tell me how can i connect all the excel sheets to my database??

[kvprajapati]

@rjbrjb777, you can use System.Data.OleDB provider to connect to the excel and perform read and write on spread sheets.

[rjbrjb777]

okay..
i have done like these as a basic program..
in form1.cs i have got a button..
and on clicking button file will be connected... and column wise data will be shown in message box..

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Microsoft.Office.Interop.Excel.Application xlApp;
            Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
            Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
            Microsoft.Office.Interop.Excel.Range range;

            string str;
            int rCnt = 0;
            int cCnt = 0;

            xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            xlWorkBook = xlApp.Workbooks.Open("A:\\GUI_sheets\\Chilika_AMS16_1_9mar11.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            range = xlWorkSheet.UsedRange;
            MessageBox.Show("fiel has been successfully opened");
            
            
            for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
            {
                for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
                {
                    str = (string)(range.Cells[rCnt, cCnt] as Microsoft.Office.Interop.Excel.Range).Value2;
                    MessageBox.Show(str);
                }
            }

            xlWorkBook.Close(true, null, null);
            xlApp.Quit();

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);

        }

        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Unable to release the Object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        } 


    }
}

now problem is..
my sheet is like following.. so first row is being read..
but for second row it gives me error..
:( can u help me..??

Sr. No. Station ID Station Name Date Time(GMT) PERIPHERAL STATUS
1 AFA4102C AGROMET 16 1-Mar-11 0:00:00 0
2 AFA4102C AGROMET 16 1-Mar-11 0:30:00 0
3 AFA4102C AGROMET 16 1-Mar-11 1:00:00 0
4 AFA4102C AGROMET 16 1-Mar-11 1:30:00 0
5 AFA4102C AGROMET 16 1-Mar-11 2:00:00 0
6 AFA4102C AGROMET 16 1-Mar-11 2:30:00 0
7 AFA4102C AGROMET 16 1-Mar-11 3:00:00 0