locked
Import Data From an Excel File RRS feed

  • Question

  • I'm looking for the most efficient way to import data from an Excel file into a DataSet at runtime using managed C++. 

    Do I use OLE or ADO.NET or ODBC or are they related?  I'm a bit confused as to how simple or difficult this should be.  I believe I should be able to just link to the xls file with just a few lines of code. Any ideas?

    Thank you very much, George
    Friday, September 9, 2005 2:22 PM

Answers

All replies

  • Thank you, but most of the examples linked here show iterating thru a csv or xls file, etc and building the dataset "manually".  Can you tell me if I should be able to essentially read an entire xls sheet into a dataset simply by calling a bind API?  So I'm hoping to have code that does: one line to connect to the xls file and one line to read the file into the DataSet (preferably in MC++).  Also, I dont need anything to do with SQL for this.

    Thanks again so much, George
    Friday, September 9, 2005 2:52 PM
  • essentially you can use the 2 step process (not nec. 2 lines though)
    1. oldb connection object to connect to the xl source 
    2. then using a dataadpter "Fill" the dataset
    Friday, September 9, 2005 4:44 PM
  • Re: Import xls file into dataset
    http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=85514


    The is a recent almost identical issue to the one discussed on the Visual C# General forum.

    James M. Sigler, II
    Dallas, TX
    jmsigler2@hotmail.com

    Saturday, September 10, 2005 3:44 PM
  • Hi,

    using Excel Interop or OleDb providers for Excel import / export is not efficient nor secure. Maybe you should take a look at this Excel C# / VB.NET library.

    It is a fully managed library that doesn't use Excel and allows you to easily import Excel to DataSet.

    Here is a sample Excel C# code (sorry but I am not familiar with Managed C++):

    var ef = new ExcelFile();
    
    ef.LoadXls("Data.xls");
    
    var dataSet = new DataSet();
    
    foreach (ExcelWorksheet ws in ef.Worksheets)
    {
     var dataTable = dataSet.Tables.Add(ws.Name);
    
     // Create columns from first row cells.
     dataTable.Columns.AddRange(ws.Rows[0].AllocatedCells.Cast<ExcelCell>().Select(cell => new DataColumn((string)cell.Value)).ToArray());
    
     // If excel cell value is not string, call ToString() on it.
     ws.ExtractDataEvent += (sender, e) =>
     {
     if (e.ErrorID == ExtractDataError.WrongType)
     {
      e.DataTableValue = e.ExcelValue != null ? e.ExcelValue.ToString() : string.Empty;
      e.Action = ExtractDataEventAction.Continue;
     }
     };
    
     ws.ExtractToDataTable(dataTable, ws.GetUsedCellRange().Height, ExtractDataOptions.None, ws.Rows[1], ws.Columns[0]);
    }
    

    Monday, May 2, 2011 9:13 AM