Convert Excel range to ADO.NET DataSet or DataTable etc.

Question

I have an Excel spreadsheet that will be hosted on a network drive. It should access my Winforms C # 3.0 application (many users can use the application and click on this table at the same time). There is a lot of data on one sheet. This data is divided into areas that I called ranges. I need to have access to these ranges individually, return each range as a dataset, and then snap it to the grid.

I found examples that use OLE and made them work. However, I saw some warnings about using this method, plus at work we used Microsoft.Office.Interop.Excel as a standard so far. I really do not want to deviate from it, unless I have to. As far as I know, our users will use Office 2003.

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 path has been enjoyable since it took my first row and turned it into columns. My ranges (12 in total) for the most part differ from each other in the number of columns. I did not know if this information would affect any recommendations.

Is it possible to use Interop and return the returned range back to the dataset?

Answer 1

, . :

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

, . excel, http://www.freeimagehosting.net/image.php?f8d4ef4173.png, .

    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

NPOI, / Excel Excel XLS. NPOI - .

, DataRows DataTable.

StackOverflow :

Excel (.XLS .XLSX) #

Answer 4

This method does not work when the same column in an Excel spreadsheet contains both text and numbers. For example, if Range("A3")=Helloand Range("A7")=5, then it reads only Hello, and the value for Range("A7")-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;