I wasn’t able to find a neat solution anywhere online to this: I have multiple tables on the same worksheet and I want to be able to interact with each of these tables from Visual Studio in C#.
By putting together ideas from various places I came up with my own solution, which I hope you find to be elegant.
As I intended to use the Access Database Engine, I had to download it from here. Although I’m running an x64 OS, I have an x32 office instance running and therefore I was forced to install the x32 Access Database Engine.
1) Define names for your tables in Excel. Select only the table (header row included, title row excluded), and in the Formulas tab click on Define Names. Give it a name (you will be using this as the table name later).
Also make sure none of your column names have spaces in them.
2) In your Visual Studio project, go to Project->Add Reference and add System.Data.Linq
I would recommend that you use a connection string in the following manner:
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + fileName + ";Extended Properties=Excel 12.0 Xml"
This piece of code will read the values from the Excel file:
using System;
using System.Data;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Linq;
using System.Data.OleDb;
namespace ExcelInteractionProgram
{
[Table(Name = "Student")]
class Student
{
private double _Id;
[Column(Storage = "_Id")]
public double Id
{
get
{
return this._Id;
}
set
{
_Id = value;
}
}
private string _Name;
[Column(Storage="_Name")]
public string Name
{
get
{
return this._Name;
}
set
{
_Name = value;
}
}
private DateTime _DateOfJoining;
[Column(Storage = "_DateOfJoining")]
public DateTime DateOfJoining
{
get
{
return this._DateOfJoining;
}
set
{
_DateOfJoining = value;
}
}
}
[Table(Name = "ExamScore")]
class ExamScore
{
private double _StudentId;
[Column(Storage = "_StudentId")]
public double StudentId
{
get
{
return this._StudentId;
}
set
{
_StudentId = value;
}
}
private double _Score;
[Column(Storage = "_Score")]
public double Score
{
get
{
return this._Score;
}
set
{
_Score = value;
}
}
}
class Program
{
static void Main(string[] args)
{
string fileName = @"C:\Student.xlsx";
DataContext db = new DataContext(new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + fileName + ";Extended Properties=Excel 12.0"));
Table<Student> students = db.GetTable<Student>();
Table<ExamScore> examScores = db.GetTable<ExamScore>();
// Attach the log to show generated SQL.
db.Log = Console.Out;
var dbQuery =
from student in students
join score in examScores
on student.Id equals score.StudentId
select new { student.Id, student.Name, student.DateOfJoining, score.Score };
foreach (var row in dbQuery)
{
Console.WriteLine("Student Id = {0}; Name={1}; Date of Joining={2}; Score={3}",
row.Id, row.Name, row.DateOfJoining.ToShortDateString(), row.Score);
}
Console.ReadKey();
}
}
}
If you’re getting errors, these pointers might help:
1) I got: Specified cast is not valid.
when my Student Id property was defined as an integer:
private int _Id;
[Column(Storage = "_Id")]
public int Id
{
get
{
return this._Id;
}
set
{
_Id = value;
}
}
In Excel, all numbers are stored as double type by default so you must use double for the property as well.
2) I got: No value given for one or more required parameters.
when I named the property different from the name of the column.
As an example I have called the property ‘Score1′ instead of ‘Score’:
private double _Score;
[Column(Storage = "_Score")]
public double Score1
{
get
{
return this._Score;
}
set
{
_Score = value;
}
}
3) I got: The Microsoft Access database engine could not find the object ‘Student’. Make sure the object exists and that you spell its name and the path name correctly. If ‘Student’ is not a local object, check your network connection or contact the server administrator.
when I misspelled my file name in the connection string.
Eg: Student1.xlsx instead of Student.xlsx. Note that the error is misleading because you would think you made a mistake in naming the Student table within the Excel worksheet but in my case, it was the file name which had the typo.
