Read Excel Data using C#

I am assuming that you already have an Excel file with one “Sheet1” data table in it. Microsoft ACE driver is already installed;

Standard syntax to query Excel table is “SELECT * FROM [Sheet1$]”. Here is the code that does the magic.

// Connect EXCEL sheet with OLEDB using connection string
 using (OleDbConnection conn = new OleDbConnection(connectionString))
    {
        conn.Open();
        OleDbDataAdapter objDA = new System.Data.OleDb.OleDbDataAdapter
        ("select * from [Sheet1$]", conn);
        DataSet excelDataSet = new DataSet();
        objDA.Fill(excelDataSet);
        dataGridView1.DataSource = excelDataSet.Tables[0];
    }
			
	//In above code '[Sheet1$]' is the first sheet name with '$' as default selector,
        // with the help of data adaptor we can load records in dataset		
	
	//write data in EXCEL sheet (Insert data)
 using (OleDbConnection conn = new OleDbConnection(connectionString))
    {
        try
        {
            conn.Open();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            cmd.CommandText = @"Insert into [Sheet1$] (month,mango,apple,orange) 
            VALUES ('DEC','40','60','80');";
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            //exception here
        }
        finally
        {
             conn.Close();
             conn.Dispose();
        }
    }
			
//update data in EXCEL sheet (update data)
using (OleDbConnection conn = new OleDbConnection(connectionString))
	{
        try
        {
            conn.Open();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            cmd.CommandText = "UPDATE [Sheet1$] SET month = 'DEC' WHERE apple = 74;";
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            //exception here
        }
        finally
        {
            conn.Close();
            conn.Dispose();
        }
    }

$ in select statement means that table already exists in Excel File. If we are going to create a new worksheet then we will not use $ sign. OLEDB does not support DELETE query.

Resource;

https://www.codingame.com/playgrounds/9014/read-write-excel-file-with-oledb-in-c-without-interop

All Excel – Programmatically Reading/Writing

ERR: External table is not in the expected format;

The error might be the result of wrong extended properties in connection string. Check code examples below;

Following code will help you to open *.xls files;

string connString = string.Format("Provider = Microsoft.ACE.OLEDB.12.0; Data Source ={0}; Extended Properties = 'EXCEL 8.0 XML;HDR=YES;IMEX=1'", fullPathToExcel);
using (OleDbConnection conn = new OleDbConnection(connString))
{
   conn.Open();
  //...any other instruction
}

Following code will help you to open *.xlsx files;

string connString = string.Format("Provider = Microsoft.ACE.OLEDB.12.0; Data Source ={0}; Extended Properties = 'EXCEL 12.0 XML;HDR=YES;IMEX=1'", fullPathToExcel);
using (OleDbConnection conn = new OleDbConnection(connString))
{
   conn.Open();
  //...any other instruction
}

Excel ADO.NET properties for different excel version are;

Excel 97-2003 Workbook (.XLS) –> “Excel 80”

Excel 2007-2010 Workbook (.XLSX) –> “Excel 12.0 XML”

Excel 2007-2010 Macro-enabled Workbook (.XLSM) –> “Excel 12.0 MACRO”

This is how you can run a select query;

"SELECT * FROM [StoreSheet$A13:I]"

If you are not able to solve External table is not in the expected format error, consider changing driver. try NPOI or ExcelDataReader.

ExcelDataReader Library

This library doesn’t work with .NET core.

EPPlus Library

EPPlus library works with .NET core.

Working with Excel Files with the help of Script Task

Integration Services provides the Excel connection manager, Excel source, and Excel destination for working with data stored in spreadsheets in the Microsoft Excel file format. The techniques described in this topic use the Script task to obtain information about available Excel databases (workbook files) and tables (worksheets and named ranges).

You can read more here.

How to unprotect the Excel sheet

https://techcommunity.microsoft.com/t5/excel/how-to-unprotect-the-excel-sheet-if-forgot-the-password/m-p/1574559

Common variables and Namespaces used to read Excel files

Variables. Open the Variables window and define the following variables:
1- ExcelFile, of type String. Enter the complete path and filename to an existing Excel workbook.
2 -ExcelTable, of type String. Enter the name of an existing worksheet or named range in the workbook
named in the value of the ExcelFile variable. This value is case-sensitive.
3 – ExcelFileExists, of type Boolean.
4 – ExcelTableExists, of type Boolean.
5 – ExcelFolder, of type String. Enter the complete path of a folder that contains at least one Excel workbook.
6 – ExcelFiles, of type Object.
7 – ExcelTables, of type Object.

Imports statements
You will need following .NET Framework namespaces;
1 – System.IO, for file system operations.
2- System.Data.OleDb, to open Excel files as data sources.

References.
The code that read schema information from Excel files require an additional reference in the script project to the System.Xml namespace.