Introduction
In the following article, a brief description has been provided for a client which is used to interface with Microsoft Excel documents. Interfacing to Excel documents (reading, inserting, updating etc.) is a basic task under ADO.NET, and every developer must have a sense of interfacing to Excel documents. The MicrosoftExcelCient
is a simple class any developer can use in their code to interface with Excel documents. It has been created in C# and uses OLEDB.
Inside MicrosoftExcelClient
The MicrosoftExcelClient
has been created in C#, and in essence is a class which provides an easy to use simple interface to access Excel workbooks in the .NET Framework. OLEDB components are used within C# to access Excel sheets like databases.
OleDbConnection m_ConnectionToExcelBook;
OleDbDataAdapter m_AdapterForExcelBook;
Shown below are code snippets from some of the basic functions used in the MicrosoftExcelClient assembly...
Open Connection To An Excel Workbook
this.m_ConnectionToExcelBook =
new OleDbConnection("Provider=Microsoft.Jet" +
".OLEDB.4.0;Data Source=" +
this.m_SourceFileName +
";Extended Properties=Excel 8.0;");
this.m_ConnectionToExcelBook.Open();
Read Data From An Excel Sheet
DataTable returnDataObject = new DataTable();
OleDbCommand selectCommand =
new OleDbCommand("select * from [" + iSheetName + "$]");
selectCommand.Connection = this.m_ConnectionToExcelBook;
this.m_AdapterForExcelBook = new OleDbDataAdapter();
this.m_AdapterForExcelBook.SelectCommand = selectCommand;
this.m_AdapterForExcelBook.Fill(returnDataObject);
Insert Or Update Format
OleDbCommand nonQueryCommand = new OleDbCommand(iQuery);
nonQueryCommand.Connection = this.m_ConnectionToExcelBook;
nonQueryCommand.CommandText = iQuery;
nonQueryCommand.ExecuteNonQuery();
How To Use The MicrosoftExcelClient
As mentioned previously, the provided client is quite simple to use for budding developers, and should be a mere cake walk for experienced pros. Shown below is a brief snippet of how simple it is to use this assembly to extract and feed data from / into an Excel sheet…
MicrosoftExcelClient sampleObject =
new MicrosoftExcelClient("SampleData.xls");
sampleObject.openConnection();
this.dataGrid1.DataSource = sampleObject.readEntireSheet("sheet1");
this.dataGrid2.DataSource =
sampleObject.readForSpecificQuery("select data1 , " +
"data2 ,data3 from [sheet1$]");
sampleObject.runNonQuery("insert into [sheet1$]" +
" values('1','2','3','4','5','6','7') ");
sampleObject.runNonQuery("update [sheet1$] set data1 = '9'");
Conclusion
To sum it all up, the MicrosoftExcelClient
is very simple to use, and is open source and not copyrighted. You may use it in parts or as a whole without any restrictions. The demo project is bug free, and any and all suggestions are welcome. You may face problems deleteing records from an Excel database when using the NonQuery
function, however this is a drawback of OLEDB, and is not a mistake in the code.
Siddharth Batra is a first year graduate student, pursuing his Master's of Science in Computer Science at Stanford University. His research interests are in the domains of computer vision, digital image processing, visualizations and applied computer graphics.
He also posseses a keen interest in the .NET Framework and the C# language
You can contact Siddharth Batra at
http://www.siddharthbatra.info/