Click here to Skip to main content
12,240,822 members (64,490 online)
Click here to Skip to main content
Add your own
alternative version

Stats

138.1K views
2.9K downloads
58 bookmarked
Posted

Client For Microsoft Excel in C#

, 8 Dec 2005
Rate this:
Please Sign up or sign in to vote.
MicrosoftExcelClient is a small assembly coded in C#.NET which is used to interface with, i.e., read from and write into an Excel sheet in the .NET Framework.

Sample Image - MicrosoftExcelClient.jpg

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.

/// <summary>
/// Connects to the source excel workbook
/// </summary>

OleDbConnection m_ConnectionToExcelBook;

/// <summary>
/// Reads the data from the document to a System.Data object
/// </summary>

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…

Sample screenshot

//Declare the sample object & set source data path
MicrosoftExcelClient sampleObject = 
     new MicrosoftExcelClient("SampleData.xls");

//Open connection to the excel work book
sampleObject.openConnection();

//Load the entire excel sheet into Datagrid1 
//( Sheet name is passed as a parameter )
this.dataGrid1.DataSource = sampleObject.readEntireSheet("sheet1");

//Load the result of a specific query on the excel sheet into Datagrid2 
//Query pased as a parameter

this.dataGrid2.DataSource = 
   sampleObject.readForSpecificQuery("select data1 , " + 
   "data2 ,data3 from [sheet1$]");

//******NON RESULT ORIENTED QUERIES*******
//Inserts a new record into the excel sheet
sampleObject.runNonQuery("insert into [sheet1$]" + 
         " values('1','2','3','4','5','6','7') ");

//Update the given excel sheet
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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Siddhartha Batra
Web Developer
India India
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/

You may also be interested in...

Comments and Discussions

 
GeneralMy vote of 1 Pin
debbie613-Apr-12 7:09
memberdebbie613-Apr-12 7:09 
GeneralMy vote of 5 Pin
manoj kumar choubey7-Feb-12 3:56
membermanoj kumar choubey7-Feb-12 3:56 
QuestionAlternative solution Pin
JamesHoward97228-Dec-11 6:57
memberJamesHoward97228-Dec-11 6:57 
GeneralERROR: the first row don't display! Pin
wadeliu6-Oct-08 21:26
memberwadeliu6-Oct-08 21:26 
GeneralRe: ERROR: the first row don't display! Pin
Deathspike22-Oct-08 3:22
memberDeathspike22-Oct-08 3:22 
QuestionExcel Probelm Pin
ghumman638-May-08 19:42
memberghumman638-May-08 19:42 
AnswerRe: Excel Probelm Pin
gg423713-Nov-09 2:06
membergg423713-Nov-09 2:06 
QuestionCan I read Opened Excel file continuosly? Pin
Member 458580414-Jan-08 8:47
memberMember 458580414-Jan-08 8:47 
GeneralProblem with create table. Pin
Tomasz_S14-Jan-08 3:51
memberTomasz_S14-Jan-08 3:51 
QuestionExtended Functions Pin
new devy4-Nov-07 10:25
membernew devy4-Nov-07 10:25 
AnswerRe: Extended Functions Pin
Siddhartha Batra4-Nov-07 10:48
memberSiddhartha Batra4-Nov-07 10:48 
QuestionRe: Extended Functions Pin
new devy6-Nov-07 9:23
membernew devy6-Nov-07 9:23 
GeneralLong Strings Pin
stephen66630-Nov-06 3:41
memberstephen66630-Nov-06 3:41 
GeneralDates Pin
stephen66624-Nov-06 1:20
memberstephen66624-Nov-06 1:20 
GeneralRe: Dates Pin
Siddhartha Batra24-Nov-06 1:25
memberSiddhartha Batra24-Nov-06 1:25 
GeneralRe: Dates Pin
stephen66624-Nov-06 1:45
memberstephen66624-Nov-06 1:45 
GeneralRe: Dates Pin
stephen66630-Nov-06 3:38
memberstephen66630-Nov-06 3:38 
GeneralRe: Dates Pin
Shailendrasinh14-Nov-07 11:47
memberShailendrasinh14-Nov-07 11:47 
QuestionUpdate Fails Pin
George.Will30-Oct-06 10:33
memberGeorge.Will30-Oct-06 10:33 
AnswerRe: Update Fails Pin
ricpue2-Nov-06 11:09
memberricpue2-Nov-06 11:09 
GeneralI CAN NOT download source Pin
lbustio30-Oct-06 4:03
memberlbustio30-Oct-06 4:03 
GeneralRe: I CAN NOT download source Pin
Siddhartha Batra30-Oct-06 7:34
memberSiddhartha Batra30-Oct-06 7:34 
GeneralThank you for sharing! Pin
David7776-Sep-06 2:59
memberDavid7776-Sep-06 2:59 
QuestionDeleting rows Pin
Cezar Gradinariu4-Apr-06 3:03
memberCezar Gradinariu4-Apr-06 3:03 
GeneralRe: Deleting rows Pin
Siddhartha Batra4-Apr-06 3:06
memberSiddhartha Batra4-Apr-06 3:06 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160426.1 | Last Updated 8 Dec 2005
Article Copyright 2005 by Siddhartha Batra
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid