Converting an Excel Spreadsheet to a DataSet, DataTable and Multi-Dimensional Array

A way to easily manipulate data from an excel spreadsheet programmatically

I work in an office environment which is dominated by Microsoft Excel spreadsheets, and so often, people want to use them in programs.  I am a SQL junky myself, but Excel is appropriate in a lot of cases, but integrating them into programs can be a pain.  More specifically, getting the data from the Excel worksheet to a form where it is workable can be a pain. 

 

With this example, you can see how to take in an Excel Spreadsheet to get the data into commonly manipulatable data types in c#; then you can use/change it however you want to.  This demo shows first how to put the data into a dataset, then datatable (almost the same thing), then takes the same data in puts it into a multi-dimensional array (I prefer to work it arrays).  Since this is a linear progression, the demo only outputs the information from the array into an html table to show that it worked. 

 

The demo uses the very first row as the column names (as it is used in datatable) and assumes that the Spreadsheet is in default format with Sheet1 as it's first sheet.  The demo only processes Sheet1.  This can be easily customized if you want to process more. 

 

With this, you can take/change/display the excel data however you want to using asp.net controls like ListView or GridView or just simple c#.  The code is pretty well commented, so you should be able to pull the parts that you may need.  *NOTE: you need full trust enabled for this to work properly.



Comments (11) -

  • Thank you,
    This piece of code was really very helpful.

  • Great!  Glad it helped someone else out... I ended up using it again last week - my boss had a 44,000 entry xls file and want certain data  pulled from it, he thought it would take days; I used this and wrote a quick program - it took about 15 minutes Smile
  • Hello,

    First of all, I would like to thank you.
    I've been using your code and it was very easy to use and very helpful.
    When putting my application on another server i got the following exception: "The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine."

    How can I solve it? If i have to install something on the second server it might be a problem.
    Is there a way to add a dll to my code?
    What should I do to prevent that?

    Thanks again,
    Dana
  • Jay
    Great article. It works for me to open excel files, however, it has conflict when I try to add Ajax extender to the page (for other controls).

    As soon as I opened an excel file from the page, I got this error: Extender controls may not be registered before PreRender/

    anyone has any idea. Thanks
  • Are you adding the controls in the aspx or in the code-behind?  Sounds like you are adding them to early in the page life-cycle?
  • Jay
    I add the scriptmanager in my master page

    <body>
        <form id="form1" runat="server">
        <asp:ScriptManager ID="ScriptManager1" runat="server"/>
       <asp:ContentPlaceHolder ID="ContentPlaceHolder1" runat="server"/>


    and then I use this

    private DataSet GetExcel(string fileName)

    function in the aspx page. finally I output the results to a gridview.

    DataSet ds = GetExcel(filePath);
    previewExcelGrd.DataSource = ds;
    previewExcelGrd.DataBind();
                    
    The error comes out whenever I load the excel file and open it. I didn't even use any ajax control at all except putting scriptmanager in the masterpage.

  • Hello There.... Terrific article... I have a question... In my particular case, the excel file I work with is compossed of multiple sets of infomation all contained in a single sheet.... is there a way to load a certain chunk of info instead of an entire sheet? Moreover, is there a way to tell the process which row you want to be used as the column names? THANKS A TON
  • somedude,
      There is certainly a way to load chunks of info, you would just have to modify the for loops to decide which chunks to publish - if you were more specific, I could probably help you more.

      Also, you could specify a row to use for column names by editing this:

    //gets column names
    for (int j = 0; j < number_of_columns; j++)
         columnNames[j] = dt.Columns[j].ToString();

    into something like this:

    for (int j = 0; j < number_of_columns; j++)
        columnNames[j] = dt.Rows[ROW_TO_USE][j].ToString();

    Cheers!
  • Hey buddy,

    Thanks for your help... I also found a couple more ways of doing what I wanted... In run time, you can add a header row that contains the column names you want to use for your collection. Alternativelly, you can define "Names" for a collection of rows that contains you data... this is better explained in the following article: http://support.microsoft.com/kb/311731

    THANKS A LOT!
  • Great piece of code example for working with Excel. Exactly what I needed to load excel data.
Comments are closed