Member Avatar for 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??

  • 2 Contributors
  • 2 Replies
  • 212 Views
  • 14 Hours Discussion Span
  • Latest Post Latest Post by rjbrjb777

Dani AI

Generated

Quick diagnosis and a safe fix for : the exception on the second row is almost always caused by forcing Value2 into a string. Range.Value2 returns a Variant/object that can be null, a string, a double (for numbers or dates) or even a 2‑D array for multi‑cell ranges. Treat it as an object, check for null, then convert by type (for OLE dates use DateTime.FromOADate). See Microsoft docs on Value2 and the Date conversion behavior. (learn.microsoft.com)

For reliability and performance, read the whole UsedRange once into a managed 2‑D array and iterate that instead of calling into Excel for every single cell. That both avoids many COM round trips and prevents many transient COM errors. A minimal pattern (adapt to your columns/expected types) looks like:

// read once (example)
var values = (object[,])xlWorkSheet.UsedRange.Value2;
int rows = values.GetLength(0);
int cols = values.GetLength(1);

for (int r = 1; r <= rows; r++)
{
    for (int c = 1; c <= cols; c++)
    {
        var v = values[r, c];   // v can be null, string, double, etc.
        if (v == null) continue;
        if (v is double d) { /* number or OLE date -> DateTime.FromOADate(d) if needed */ }
        else { var s = v.ToString(); /* text */ }
    }
}

Microsoft demonstrates using arrays for multi‑cell ranges; use GetLowerBound/GetUpperBound if you need culture/1‑based safety. (learn.microsoft.com)

About choice of API: is right that OLE DB works (ACE/JET) but watch the provider bitness and install requirements (Access Database Engine). For desktop tools that must run without Excel installed, consider modern libraries that avoid Interop entirely: EPPlus or ClosedXML (both actively maintained) or the Open XML SDK for low‑level control. Each has tradeoffs (licensing, .xlsx only, features). (microsoft.com)

A final note on cleanup: Excel COM lifetimes can be tricky. Minimize COM calls, close workbooks and quit the app as soon as possible, and prefer reading into arrays so you mostly avoid per‑cell RCWs. Use care with Marshal.ReleaseComObject (Microsoft warns it can be dangerous); prefer deterministic scoping and a single GC round if you need to force cleanup. (devblogs.microsoft.com)

References:

Recommended Answers

All 2 Replies

Member Avatar for kvprajapati

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

Member Avatar for rjbrjb777

okay..
i have done like these as a basic program..
in 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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.