David, Alex and me just needed to get a load of data from some Excel sheets and work with the data. What's better than to load the data into a DataSet using OleDB and process it using LINQ to DataSets?
Make sure you know what the format of your columns is and that the first row in your sheet holds the name of the column. Then below would be what you need for code.
string filename = @"C:\myfile.xls";
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + filename + ";" +
"Extended Properties=Excel 8.0;";
OleDbDataAdapter dataAdapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
DataSet myDataSet = new DataSet();
dataAdapter.Fill(myDataSet, "ExcelInfo");
DataTable dataTable = myDataSet.Tables["ExcelInfo"];
var query = from r in dataTable.AsEnumerable()
select new
{
RelationNr = r.Field<double>("RelationNumber"),
ClientName = r.Field<string>("ClientName"),
};
foreach (var item in query)
Console.WriteLine(item.ClientName);
}
Als de data al in de DataTable zit dan zie ik eigenlijk niet echt de meerwaarde van LINQ niet.
Maar dat kan aan mij liggen.
foreach DataRow dbrow in dataTable.Rows
Console.WriteLine (dbrow("ClientName"));
of iets dergelijks.
Great example, but I think you have a small error in this line:
I think "strConn" should be "connectionString".
Thanks again,
pt
How to get data the other way around? I mean from SQL Server using Linq to Excel. I have tried using a web page to publish the result of the Linq query, and then import using Excel's import from web page. It works but the performance is poor. Is there a way to "convert" the Linq query to a ODBC connection to avoid all the ASCII conversions?
@Niels : Could you be a little more specific, because I don't really understand what you're doing.
If you want to exchange OLEDB for ODBC, I wouldn't recommend that, because normally (I said : normally :) OLEDB is always faster.
But I don't understand from where to where you're importing and exporting stuff. Are you getting info from Excel into a dataset or vice-versa?
Excellent, been looking for an example of this!
This example was very helpful. Here is my VB.NET version:
' Imports System.Data.OleDb
Dim filename As String = "C:\myfile.xls"
Dim connectionString As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;", filename)
Dim dataAdapter As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString)
Dim myDataSet As DataSet = New DataSet()
dataAdapter.Fill(myDataSet, "ExcelInfo")
Dim dataTable As DataTable = myDataSet.Tables("ExcelInfo")
Dim query = From r In dataTable.AsEnumerable() _
Select New With _
{ _
.RelationNr = r.Field(Of Double)("RelationNumber"), _
.ClientName = r.Field(Of String)("ClientName") _
For Each item In query
Console.WriteLine(item.ClientName)
Next
Thanks Andy, that's awesome!