How to get excel sheet in C#
By FoxLearn 10/10/2024 6:52:55 AM 42
To read an Excel sheet in C# using OleDbConnection, you can follow these steps.
How to get excel sheet in C# using OleDbConnection?
You can create a GetSchema
method to get the excel sheet in c#.
public DataTable GetSchema(string filePath) { DataTable dt = new DataTable(); dt.Columns.Add("FullSheetName", typeof(string)); dt.Columns.Add("SheetName", typeof(string)); using (OleDbConnection oleConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + filePath + ";Extended Properties=" + Convert.ToChar(34).ToString() + "Excel 8.0;Imex=2;HDR=yes" + Convert.ToChar(34).ToString())) { oleConn.Open(); // Get the sheet names DataTable dtSchema = oleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); oleConn.Close(); if (dtSchema.Rows.Count != 0) { foreach (DataRow dr in dtSchema.Rows) { DataRow drNew = dt.NewRow(); drNew["FullSheetName"] = dr["TABLE_NAME"].ToString(); drNew["SheetName"] = drNew["FullSheetName"].ToString().Replace("$", string.Empty); dt.Rows.Add(drNew); } } } return dt; }
For .xls
, use Excel 8.0;
and for .xlsx
, use Excel 12.0 Xml;
.
The OleDbConnection
object is used to open a connection to the Excel file.
The GetOleDbSchemaTable
method retrieves the names of the sheets in the Excel file.
How to read excel file in C# using OleDbConnection?
To get content of excel sheet you can create a GetSheetContent
method as shown below.
public DataTable GetSheetContent(string filePath, string fullSheetName) { DataTable dt = new DataTable(); using (OleDbConnection oleConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + filePath + ";Extended Properties=" + Convert.ToChar(34).ToString() + "Excel 8.0;Imex=2;HDR=yes" + Convert.ToChar(34).ToString())) { OleDbDataAdapter oleDb = new OleDbDataAdapter("select * from [" + fullSheetName + "]", oleConn); oleDb.Fill(dt); } return dt; }
The statement suggests that you can retrieve all data from a specific sheet by using a SQL query.
The results are filled into a DataTable
using OleDbDataAdapter
.
Make sure your file path is correct and that the necessary drivers are installed.
- How to read an excel (.xls/.xlsx) file in C#
- How to Read Excel file in C# using OleDb
- How to Read Excel file in C#
- How to Read Excel file (*.xls, *.xlsx) in C#
- How to Open and Read Excel Files in C#
- How to Open and Read Excel Files in VB.NET
- How To Open and Read an Excel file into a ListView in C#
- How to Export DataTable to Excel without Interop in C#
Categories
Popular Posts
How to sign a powershell script
10/03/2024
How to get Credentials in PowerShell?
10/03/2024