Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
c# reading excel-files by using OleDb
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
namespace ReadExcel
{
class Program
{
static void Main(string[] args)
{
string file = @"D:\tmp\Store 29-09-15.xlsx";
var dataSet = GetDataSetFromExcelFile(file);
Console.WriteLine(string.Format("reading file: {0}", file));
Console.WriteLine(string.Format("coloums: {0}", dataSet.Tables[0].Columns.Count));
Console.WriteLine(string.Format("rows: {0}", dataSet.Tables[0].Rows.Count));
Console.ReadKey();
}
private static string GetConnectionString(string file)
{
Dictionary<string, string> props = new Dictionary<string, string>();
string extension = file.Split('.').Last();
if (extension == "xls")
{
//Excel 2003 and Older
props["Provider"] = "Microsoft.Jet.OLEDB.4.0";
props["Extended Properties"] = "Excel 8.0";
}
else if (extension == "xlsx")
{
//Excel 2007, 2010, 2012, 2013
props["Provider"] = "Microsoft.ACE.OLEDB.12.0;";
props["Extended Properties"] = "Excel 12.0 XML";
}
else
throw new Exception(string.Format("error file: {0}", file));
props["Data Source"] = file;
StringBuilder sb = new StringBuilder();
foreach (KeyValuePair<string, string> prop in props)
{
sb.Append(prop.Key);
sb.Append('=');
sb.Append(prop.Value);
sb.Append(';');
}
return sb.ToString();
}
private static DataSet GetDataSetFromExcelFile(string file)
{
DataSet ds = new DataSet();
string connectionString = GetConnectionString(file);
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
// Get all Sheets in Excel File
DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
// Loop through all Sheets to get data
foreach (DataRow dr in dtSheet.Rows)
{
string sheetName = dr["TABLE_NAME"].ToString();
if (!sheetName.EndsWith("$"))
continue;
// Get all rows from the Sheet
cmd.CommandText = "SELECT * FROM [" + sheetName + "]";
DataTable dt = new DataTable();
dt.TableName = sheetName;
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(dt);
ds.Tables.Add(dt);
}
cmd = null;
conn.Close();
}
return ds;
}
}
}
@askhorizons
Copy link

askhorizons commented Dec 14, 2018

Thanks a lot. A great coding.

@sajjad27
Copy link

sajjad27 commented Mar 26, 2019

what a great code!
thank you very much <3

@adiaby1
Copy link

adiaby1 commented Dec 17, 2019

Thank you.

@murphy1995721
Copy link

murphy1995721 commented Oct 22, 2020

Thanks you

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment