OLEDB connection string to excel in C#. net framework

Question

I just wanted to ask if there is any suitable connection string for OLEDB to solve my problem ....

So my problem is that I'm using this provider Microsoft.ACE.OLEDB.12.0 and I wanted to connect to an excel file but this file is on a server and I can't find the right connection string in order to make the connection. So is there any help to solve this please

I've tried this : Provider=Microsoft.ACE.OLEDB.12.0;Data Source =" +DataSource + ";Extended Properties='Excel 12.0;

The DataSource is like this :

ServerIP/home/FileName.xlsx

Answer 1

Here's how you could do it in C#.

using System;
using System.Data;
using System.Data.OleDb;
using System.IO;

class Program
{
    static void Main()
    {
        string filePath = @"\\ServerIP\home\FileName.xlsx";
        string connectionString = $@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={filePath};Extended Properties='Excel 12.0 Xml;HDR=YES;'";

        if (!File.Exists(filePath))
        {
            Console.WriteLine("Error: File does not exist.");
            return;
        }

        using (OleDbConnection connection = new OleDbConnection(connectionString))
        {
            try
            {
                connection.Open();
                Console.WriteLine("Connection successful!");

                // Example query to select data from the first sheet
                string query = "SELECT * FROM [Sheet1$]";
                OleDbDataAdapter adapter = new OleDbDataAdapter(query, connection);
                DataTable dataTable = new DataTable();
                adapter.Fill(dataTable);

                // Display data
                foreach (DataRow row in dataTable.Rows)
                {
                    foreach (var item in row.ItemArray)
                    {
                        Console.Write(item + "\t");
                    }
                    Console.WriteLine();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
        }
    }
}