Read Excel Sheet Data into DataTable - CodeProject
http://www.codeproject.com/Questions/445400/Read-Excel-Sheet-Data...
9,890,640 members (52,431 online)
manuu
2K
Sign out
home
articles
quick answers
discussions
features
community
help
read excel spreadsheet in c# and display into datatable
Ask a Question
All Questions All Unanswered Mine
FAQ
Next
Read Excel Sheet Data into DataTable
See more:
Hi everyone.. I have an Excel Sheet ,in that work sheet names are Sheet1,Sheet2,Sheet3... I want to load Sheet3 data in to my DataTable. How to do this can any one help me to do this .
ASP.NET C#4.0 Vote:
Your status enables you to edit this question. Alternatively, if the question is incomplete or simply isn't a question, then please report it.
Your Filters
Interested Ignored Save Filters
Thank's in advance
Posted 23 Aug '12 - 0:55 pranathis012 Improve question Permalink
Add a Solution
Have a Question or Comment?
5 solutions Solution 1
follow this process..
Top Rated Most Recent
Vote:
Top Experts in 24hrs
0 Sergey Alexandrovich Kryukov 1 Arun Vasu 2 OriginalGriff
Collapse | Copy Code
443 381 345 191 190
3 Maciej Los 4 Aarti Meswania
public static DataTable exceldata(string filePath) { DataTable dtexcel = new DataTable(); bool hasHeaders = false; string HDR = hasHeaders ? "Yes" : "No"; string strConn; if (filePath.Substring(filePath.LastIndexOf('.')).ToLower() == ".xlsx") strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\""; else strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\""; OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); //Looping Total Sheet of Xl File /*foreach (DataRow schemaRow in schemaTable.Rows) { }*/ //Looping a first Sheet of Xl File DataRow schemaRow = schemaTable.Rows[0]; string sheet = schemaRow["TABLE_NAME"].ToString(); if (!sheet.EndsWith("_")) { string query = "SELECT * FROM [" + sheet3 + "]"; OleDbDataAdapter daexcel = new OleDbDataAdapter(query, conn); dtexcel.Locale = CultureInfo.CurrentCulture; daexcel.Fill(dtexcel); }
Top Experts this month
0 Sergey Alexandrovich Kryukov 1 OriginalGriff 2 CPallini 3 Rohan Leuva 4 Maciej Los 9,755 7,559 4,018 3,362 2,951
1 of 4
25/05/2013 01:10
Read Excel Sheet Data into DataTable - CodeProject
http://www.codeproject.com/Questions/445400/Read-Excel-Sheet-Data...
conn.Close(); return dtexcel; }
Posted 23 Aug '12 - 0:58 Kamalkant(kk) 1.4K
Edited 23 Aug '12 - 1:04 v2 Uma Shankar Patel 25.6K
Improve solution
Permalink
Have a Question or Comment?
Solution 2
It works same as in case of database. like
Vote:
Collapse | Copy Code
OleDbConnection cnn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath(@"~\data\cocustomerdetails.xlsx") + "; Extended Properties=Excel 12.0;"); OleDbCommand oconn = new OleDbCommand("select * from [Sheet1$]", cnn); cnn.Open(); OleDbDataAdapter adp = new OleDbDataAdapter(oconn); DataTable dt = new DataTable(); adp.Fill(dt);
Posted 23 Aug '12 - 1:01 Uma Shankar Patel 25.6K
Improve solution
Permalink
Have a Question or Comment?
Solution 3
refer this link Read Excel in ASP.NET[^] and just replace sheet1 to sheet3 may be this will help u thank you @ChetanV@
Vote:
Posted 23 Aug '12 - 2:34 chetan virkar 2.8K
Improve solution
Permalink
Have a Question or Comment?
Solution 4
Vote:
Hello, It is very easy to export excel data into datatable using [Commercial Spam Link Removed]. Take a look at this C# example:
Collapse | Copy Code
ExcelWorkbook Wbook = ExcelWorkbook.ReadXLS(@"c:\test.xls"); ExcelWorksheet Wsheet = Wbook.Worksheets["Sheet3"]; DataTable dt = new DataTable(); dt = Wsheet.WriteToDataTable();
2 of 4
25/05/2013 01:10
Read Excel Sheet Data into DataTable - CodeProject
http://www.codeproject.com/Questions/445400/Read-Excel-Sheet-Data...
Posted 24 Feb '13 - 23:27 Eric Goodman 261
Edited 25 Feb '13 - 10:05 v2 asreighawleiybhghwlerisg 109K
Improve solution
Permalink
Have a Question or Comment?
Solution 5
You can use below code:
Vote:
Collapse | Copy Code
namespace DataFromWorkbookToDB { class Program { static void Main(string[] args) { Workbook workbook = new Workbook(); workbook.LoadFromFile(@"..\..\Sample.xlsx"); string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;data Source=sample.accdb; Persist Security Info=False;"; DataTable dt = workbook.Worksheets[0].ExportDataTable(); DataTable dt2 = workbook.Worksheets[1].ExportDataTable(); using (OleDbConnection conn = new OleDbConnection(connStr)) { conn.Open(); OleDbCommand command = new OleDbCommand(); command.Connection = conn; command.CommandText = "CREATE TABLE report(Name VARCHAR(225), Capital VARCHAR(225) ,Continent VARCHAR(225),Area VARCHAR(225),Population VARCHAR(225))"; command.ExecuteNonQuery(); command.CommandText = "CREATE TABLE lists(PartNo VARCHAR(225), VendorNo VARCHAR(225) ,Description VARCHAR(225),OnHand VARCHAR(225),OnOrder VARCHAR(225))"; command.ExecuteNonQuery(); for (int i = 0; i < dt.Rows.Count; i++) { DataRow row = dt.Rows[i]; string commd = "insert into [report] (Name,Capital,Continent,Area,Population) values('" + row[0].ToString() + "','" + row[1].ToString() + " ','" + row[2].ToString() + "','" + row[3].ToString() +"','"+ row[4].ToString() + "')"; command.CommandText = commd; command.ExecuteNonQuery(); } for (int i = 0; i < dt2.Rows.Count; i++) { DataRow row = dt2.Rows[i]; string commd = "insert into [lists] (PartNo,VendorNo,Description,OnHand,OnOrder) values('" + row[0].ToString() + "','" + row[1].ToString() + " ','" + row[2].ToString() + "','" + row[3].ToString() + "','" + row[4].ToString() + "')"; command.CommandText = commd; command.ExecuteNonQuery(); } } } } }
please note that above code needs this excel library Spire.XLS for .NET, you can give it a try,
Posted 25 Feb '13 - 16:55 Lacy00 1.4K
Improve solution
Permalink
Have a Question or Comment?
Add your solution here
B
small
BIG code var
<
>
&
link [^] encode untab indent outdent
Preview
3 of 4
25/05/2013 01:10
Read Excel Sheet Data into DataTable - CodeProject
http://www.codeproject.com/Questions/445400/Read-Excel-Sheet-Data...
Submit your solution!
When answering a question please: 1. Read the question carefully. 2. Understand that English isn't everyone's first language so be lenient of bad spelling and grammar. 3. If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome. Let's work to help developers, not make them feel stupid.
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)
Advertise | Privacy | Mobile Web01 | 2.6.130523.1 | Last Updated 25 Feb 2013
Layout: fixed | fluid
Copyright CodeProject, 1999-2013 All Rights Reserved. Terms of Use
4 of 4
25/05/2013 01:10