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

  • 3 Contributors
  • 3 Replies
  • 288 Views
  • 3 Years Discussion Span
  • Latest Post Latest Post by balasukumar

Dani AI

Generated

Quick, practical plan for (and anyone else picking this up later):

For a desktop C# app that lets the user pick one or more Excel files, reads column-wise data, runs calculations, and saves results, the most reliable pattern is: read each sheet into a DataTable, do the math in C#, then persist the results (write a new Excel workbook or bulk-insert into a database). Excel’s built‑in GUI links (as noted by ) are fine for manual work, but automation and scale are best handled programmatically.

Three common programmatic routes (tradeoffs in one line each):

  • Interop (Microsoft.Office.Interop.Excel) — full Excel fidelity but requires Excel installed and is fragile for batch/background work.
  • OLE DB / ODBC (ACE provider) — quick tabular access with SQL-like queries; needs the provider installed and can mis-detect column types.
  • OpenXML libraries (ClosedXML / EPPlus / NPOI) — safest for serverless, reliable read/write of xlsx/xls without Excel installed.

Typical workflow to implement

  1. Let the user pick files (OpenFileDialog with Multiselect = true).
  2. For each file: enumerate sheets, load sheet into DataTable, perform column-wise calculations (LINQ or plain loops), append results to a master DataTable.
  3. Persist results: either write a new Excel file with an OpenXML library or push the DataTable into your DB using SqlBulkCopy.

Example snippets (trimmed) — read a sheet via OLE DB, then bulk-insert or save with ClosedXML:

// read a sheet into a DataTable (OLE DB)
string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath +
                 ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;\"";
using(var conn = new OleDbConnection(connStr)) {
  conn.Open();
  var schema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
  foreach(DataRow r in schema.Rows) {
    string sheet = r["TABLE_NAME"].ToString();            // e.g. "Sheet1$"
    var da = new OleDbDataAdapter("SELECT * FROM ["+sheet+"]", conn);
    var dt = new DataTable(); da.Fill(dt);
    // perform column-wise math on dt
  }
}
// quick write to SQL Server
using(var bulk = new SqlBulkCopy(sqlConnString)) {
  bulk.DestinationTableName = "dbo.MyTarget";
  bulk.WriteToServer(resultDataTable);
}

Practical tips and gotchas

  • IMEX=1 helps with mixed-type columns but is not perfect. Test with representative files.
  • The ACE OLE DB driver must be installed for OLE DB access; platform (x86/x64) matters.
  • Avoid Interop in services or heavy batch jobs. Dispose COM objects if you use it.
  • For many or large files, process sequentially or use limited parallelism to avoid memory spikes and file locks.
  • Map columns explicitly when inserting to a DB (do not rely on inferred names/types).

This gives a repeatable, testable flow: let the user pick files, read into memory, compute in C#, then write or bulk-insert.

Member Avatar for ArtistScope

This forum is for Classic ASP.

Member Avatar for rjbrjb777

okay.. :(

Member Avatar for balasukumar

HAI Dear,
Databases are great for storing information, but sometimes you want to use or analyze its data in Excel. You can use the Data Connection Wizard to create a dynamic connection between a SQL Server database and your Excel workbook. You get to the Data Connection Wizard through the Data tab.

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.