Convert Excel Range to ADO.NET DataSet or DataTabl

Question

I have an Excel spreadsheet that will sit out on a network share drive. It needs to be accessed by my Winforms C# 3.0 application (many users could be using the app and hitting this spreadsheet at the same time). There is a lot of data on one worksheet. This data is broken out into areas that I have named as ranges. I need to be able to access these ranges individually, return each range as a dataset, and then bind it to a grid.

I have found examples that use OLE and have got these to work. However, I have seen some warnings about using this method, plus at work we have been using Microsoft.Office.Interop.Excel as the standard thus far. I don't really want to stray from this unless I have to. Our users will be using Office 2003 on up as far as I know.

I can get the range I need with the following code:

MyDataRange = (Microsoft.Office.Interop.Excel.Range)
    MyWorkSheet.get_Range("MyExcelRange", Type.Missing);

The OLE way was nice as it would take my first row and turn those into columns. My ranges (12 total) are for the most part different from each other in number of columns. Didn't know if this info would affect any recommendations.

Is there any way to use Interop and get the returned range back into a dataset?

Answer 1

I don't know about a built-in function, but it shouldn't be difficult to write it yourself. Pseudocode:

DataTable MakeTableFromRange(Range range)
{
   table = new DataTable
   for every column in range
   {
      add new column to table
   }
   for every row in range
   {
      add new datarow to table
      for every column in range
      {
         table.cells[column, row].value = range[column, row].value
      }
   }
   return table
}

Answer 2

I don't know what type of data you have.But for an excel data like shown in this link http://www.freeimagehosting.net/image.php?f8d4ef4173.png, you can use the following code to load into data table.

    private void Form1_Load(object sender, EventArgs e)
    {   
       try
       {        
            DataTable sheetTable = loadSingleSheet(@"C:\excelFile.xls", "Sheet1$");
            dataGridView1.DataSource = sheetTable;
       }
       catch (Exception Ex)
       {
            MessageBox.Show(Ex.Message, "");
       }  
    }        

    private OleDbConnection returnConnection(string fileName)
    {
        return new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + "; Jet OLEDB:Engine Type=5;Extended Properties=\"Excel 8.0;\"");
    }

    private DataTable loadSingleSheet(string fileName, string sheetName)
    {           
        DataTable sheetData = new DataTable();
        using (OleDbConnection conn = this.returnConnection(fileName))
        {
           conn.Open();
           // retrieve the data using data adapter
           OleDbDataAdapter sheetAdapter = new OleDbDataAdapter("select * from [" + sheetName + "]", conn);
            sheetAdapter.Fill(sheetData);
        }                        
        return sheetData;
    }

Answer 3

This method does not work well when the same column in the excel spread sheet contains both text and numbers. For instance, if Range("A3")=Hello and Range("A7")=5 then it reads only Hello and the value for Range("A7") is DBNULL

private void Form1_Load(object sender, EventArgs e)
{   
   try
   {        
        DataTable sheetTable = loadSingleSheet(@"C:\excelFile.xls", "Sheet1$");
        dataGridView1.DataSource = sheetTable;
   }
   catch (Exception Ex)
   {
        MessageBox.Show(Ex.Message, "");
   }  
}        

private OleDbConnection returnConnection(string fileName)
{
    return new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + "; Jet OLEDB:Engine Type=5;Extended Properties=\"Excel 8.0;\"");
}

private DataTable loadSingleSheet(string fileName, string sheetName)
{           
    DataTable sheetData = new DataTable();
    using (OleDbConnection conn = this.returnConnection(fileName))
    {
       conn.Open();
       // retrieve the data using data adapter
       OleDbDataAdapter sheetAdapter = new OleDbDataAdapter("select * from [" + sheetName + "]", conn);
        sheetAdapter.Fill(sheetData);
    }                        
    return sheetData;

Answer 4

It's worth to take a look at NPOI when it comes to read/write Excel 2003 XLS files. NPOI is a life saver.

I think you'll have to iterate your range and create DataRows to put in your DataTable.

This question on StackOverflow provides more resources:

Create Excel (.XLS and .XLSX) file from C#