I have three columns in Excel sheet such as id, name, family.
I am using LINQ and i need to import data from Excel to database with coding instruction, i have 6500 records in Excel sheet
Add a comment
|
3 Answers
You can use below code to get all the data and then you can convert form DataTable to List. for below example to work you have Microsoft Access Database Engine 2010 Redistributable should be installed
public static DataTable ReadExcelWithoutOffice(string filePath)
{
var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR=YES;TypeGuessRows=0;FirstRowHasNames=true;ImportMixedTypes=Text\""; ;
using (var conn = new OleDbConnection(connectionString))
{
conn.Open();
var sheets = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT * FROM [" + sheets.Rows[0]["TABLE_NAME"].ToString() + "] ";
var adapter = new OleDbDataAdapter(cmd);
var ds = new DataSet();
adapter.Fill(ds);
return ds.Tables[0];
}
}
}
1 Comment
Mustafa_K
Thanks for your answer. But it gives me an error. "There is no row at position 0." in line 11. In the meantime,should I change the amount "TABLE"on line 8??
Thank you all for your answer.
I found my problem. Here is the code :
string pach = @"D:\C# Projects\ex.xlsx";
var excelData = new ExcelQueryFactory(pach);
var data = from x in excelData.Worksheet<xlsdata>("MySheet")
select x;
DataClassesDataContext db = new DataClassesDataContext();
foreach (var d in data)
{
db.tbl_infos.InsertOnSubmit(new tbl_info
{
id = d.id,
name = d.name,
family = d.family
});
}
db.SubmitChanges();
Comments
You will need to import and reference OpenXML, open the sheets, sheet, worksheet, IIRC - then parse through your columns into strings.
Then create a SQL Data Adapter and all of that, to use either a ConnectionString or SQLConnection, fire up a parameterized query, and it's in the database.
