Excel 2 Win!

Many times in life one has to Excel 2 Win!

This tutorial presents a simple sample demonstrating how to get access to data copied from a Microsoft Excel Sheet and present the same in a DataGrid control within a Windows Form Application.

This is increasingly becoming quite a popular request amongst users because many have traditionally used Microsoft Excel as their primitive data store. Any automation effort will face requests from users to optionally allow them to copy the Excel data directly into the application using the neat old copy/paste sequence that they are so used to!

The sample is intended to serve as a good starting point to customize it to one's requirements.

To jump straight into action, here is a list of the typical sequence of action that needs to be carried out within the Windows Application, assuming the copied Excel data is being held in the Clipboard:

  • Access & extract the Clipboard data
  • Format the data into a DataGrid control bindable form
  • Bind the data to the DataGrid control

Figure 1: Sample Data in an Excel Sheet

Simple, right?

Well, there is still some work that needs to be done, but the good part is the effort is not more than 15 minutes of your time!

First, here is some information on the important .NET Framework Classes that we would befriend during the course of this sample:

  • Clipboard: Nothing can be easier that this. .NET provides access to the Clipboard by using a Class, not surprisingly, called Clipboard. We would use the Clipboard Class to extract the data contained within it.
  • IDataObject: This interface provides a format-independent mechanism for transferring data. We would use this interface to extract and store the Clipboard Excel data.
  • DataFormats: This one provides light in the dark. The Class is composed of certain pre-defined format names of data that the Clipboard can hold, such as Comma Separated Values, Html, Bitmap, and so forth. We would use the Class to identify Excel data extracted in an IDataObject.

With friendly Classes in the pocket, let us start looking at the meaty part now.

Figure 2: The Sample Application User Interface

Access & Extract the Clipboard Data

Firstly, we need to peek into the Clipboard and extract its content into a container object. For this, we use the GetDataObject method of the Clipboard Class and contain it as an IDataObject.

Dim objPresumablyExcel As IDataObject = Clipboard.GetDataObject()

Next, we need to make a quick check whether the data is indeed Excel data. Excel data is represented as Comma Seperated Values in the Clipboard, with line breaks between two rows of data. Hence, we make use of the GetDataPresent method of the IDataObject by passing it the predefined format name CommaSeperatedValue of the DataFormats Class to do the verification.

If (objPresumablyExcel.GetDataPresent(DataFormats. _
   CommaSeparatedValue)) Then
   ...
   ...
   ...
End If

Format the Data into a DataGrid Control Bindable Form

The data is raw and we need to format it into a form or object that can be bound to a DataGrid control. The sample uses the DataTable object to bind to the DataGrid control.

Dim tblExcel2WinData As New DataTable

Now, there are many ways in which you can treat and format the data. For the purpose of the sample, we would first convert and cast the contents of the IDataObject into a Stream of data that will be contained in an object of the StreamReader Class.

Dim srReadExcel As New StreamReader(CType(objPresumablyExcel. _
    GetData(DataFormats.CommaSeparatedValue), Stream))

The next task would be to read this stream of data one line at a time. A loop is the order of the day.

While (srReadExcel.Peek() > 0)
   ...
   ...
   ...
End While

Within the loop, we need to start replicating the contents of the StreamReader object in a DataTable object. To achieve this, we first extract a row of data in the Stream Reader object into a String variable.

Dim sFormattedData As String

sFormattedData = srReadExcel.ReadLine()

The String variable will now contain the column values for a row of Excel data separated by comma. We split this String variable into an array.

Dim arrSplitData As Array

arrSplitData = sFormattedData.Split("")

The number of Array items is equivalent to the number of columns of data copied from the Excel Sheet. We prepare the DataTable to reflect the same number of columns.

If tblExcel2WinData.Columns.Count <= 0 Then
   For iLoopCounter = 0 To arrSplitData.GetUpperBound(0)
      tblExcel2WinData.Columns.Add()
   Next
End If

Finally, we loop the array to add the contents of individual columns into a DataTable Row and add the Row to the DataTable.

Dim iLoopCounter As Integer
Dim rowNew As DataRow

rowNew = tblExcel2WinData.NewRow()

For iLoopCounter = 0 To arrSplitData.GetUpperBound(0)
   rowNew(iLoopCounter) = arrSplitData.GetValue(iLoopCounter)
Next

tblExcel2WinData.Rows.Add(rowNew)

Bind the Data to the DataGrid Control

The copied Excel Sheet data is successfully replicated and contained in the DataTable object. All that remains to be done now is to bind the DataTable object to the DataGrid control.

dgrExcelContents.DataSource = tblExcel2WinData.DefaultView()

Figure 3: The Excel Sheet data in the DataGrid Control

And hey, presto, the Excel Sheet data magically appears in the DataGrid control!



About the Author

Siddharth Upadhya

Sids is the new kid on the block at Tata Consultancy Services (TCS). Sids is engaged in doing some fireworks with the TCS .NET Centre of Excellence which has a firecracker of a team in place.

While not working, Sids is completing his MBA (MMM-Marketing) from NMIMS, Mumbai.

And while he is not doing his MBA you can find Sids hanging around with his friends or trekking the mountains all panting and puffing!

U can reach Sids at siddharth.u@tcs.com

Downloads

Comments

  • KonxP hlR QSVN

    Posted by pnOFTOGbKx on 11/14/2012 08:34pm

    order soma online carisoprodol tablets uses - carisoprodol 350 mg contraindications

    Reply
  • Alternative

    Posted by BarbaMariolino on 07/09/2009 08:11am

    Hi,
    
    nice article Sids but there is better and easier way to deal with Excel from VB then Excel Automation. You should use some 3rd party component like GemBox Excel component for VB. Here you can see why GemBox VB component is better then Excel Automation.
    
    Filip
    GemBox Software - Easy and fast read/write Excel files for .NET

    Reply
  • I liked ur work .

    Posted by arshi_aks on 02/07/2006 02:01am

    It is really appreciable. But how to copy contents of DataTable (tblExcel2WinData) to a new dataset

    Reply
  • I like this!

    Posted by kaifong78 on 10/06/2004 02:09am

    Hi,
    
    May I know after I copy my data into DataGrid, can I have ADD, DELETE, UPDATE, SAVE the data records? 
    
    If convenient can provide me a sample code? Thanks

    Reply
  • problem with sample code

    Posted by xcfreak on 07/20/2004 11:46am

    I am trying to open the sample source code and I am getting the follwing error: "The item 'Excel2Win.resx' does not exist in the project directory." Am I missing a file?

    • convert string to stream

      Posted by basembakr on 06/12/2005 09:06am

      when I run the project has exception (unable to cast object from string to IO.stream

      Reply
    Reply
  • Good work Sid

    Posted by das_avijit on 03/26/2004 11:39am

    Good work Sid. Seems you are exploring winforms now. It really has lots and lots of features.

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • The explosion in mobile devices and applications has generated a great deal of interest in APIs. Today's businesses are under increased pressure to make it easy to build apps, supply tools to help developers work more quickly, and deploy operational analytics so they can track users, developers, application performance, and more. Apigee Edge provides comprehensive API delivery tools and both operational and business-level analytics in an integrated platform. It is available as on-premise software or through …

  • The Red Hat® Standard Operating Environment SOE helps you define, deploy, and maintain Red Hat Enterprise Linux® and third-party applications as an SOE. The SOE is fully aligned with your requirements as an effective and managed process, and fully integrated with your IT environment and processes. Benefits of an SOE: SOE is a specification for a tested, standard selection of computer hardware, software, and their configuration for use on computers within an organization. The modular nature of the Red …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds