Accessing a table in an excel spreadsheet using C#

Question

I'm writing a program (in C#, using Microsoft.Office.Interop.Excel) that reads in an Excel spreadsheet and applies filters, etc. to the data within. However, I'm struggling with a few issues, namely how to "get" a table object representing the table of data I am working with. I'd like to be able to access columns by their headers as well, so I assumed that I would require the DataTable namespace. I can't seem to figure out what to do, however.

Here is the rough framework of my code:

    private void Process(Workbook workBook)
    {
        try
        {
            Worksheet sheet = workBook.Sheets["x"];
            Range range = sheet.UsedRange;

            // what goes here in order to access columns by name?

        }
        catch (Exception ex)
        {

        }
    }

I'm really not sure of how to go about this, so any help, as well as any suggestions for useful articles about how to use Microsoft.Office.Excel.Interop, is really welcome. I really haven't been able to find many resources that fit my needs. Thank you very much!

Okay. Keep in mind that if you ever want to move it into ASP.NET that the Interop libraries [don't work](http://support.microsoft.com/kb/257757). You'd need to use an alternative library such as [EPPlus](http://epplus.codeplex.com/). Most of these libraries have the advantage of being easier to work with, and they avoid some issues that come from running a "hidden" Office window (and don't require Office to be installed). — mason, Jul 14 '14 at 17:07
Have you looked at OLEDB for Excel? http://stackoverflow.com/questions/5814898/oledb-connection-string-for-excel-files — tgolisch, Jul 14 '14 at 17:09

score 0 · Answer 1 · edited May 23 '17 at 11:44

I use EPPLUS all the time, you can get it via NuGet. Use EPPLUS instead of Microsoft.Office.Excel.Interop

Best thing since sliced bread.

Here is a stackoverflow link to show how to export to Excel

Here is how to import excel into a datatable

var pck = new OfficeOpenXml.ExcelPackage();
pck.Load(new System.IO.FileInfo(path).OpenRead());
var ws = pck.Workbook.Worksheets["Worksheet1"];
DataTable tbl = new DataTable();
var hasHeader = true;
foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column]){
  tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}",         firstRowCell.Start.Column));
}
var startRow = hasHeader ? 2 : 1;
for (var rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++){
 var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
 var row = tbl.NewRow();
 foreach (var cell in wsRow){
       row[cell.Start.Column - 1] = cell.Text;
 }
 tbl.Rows.Add(row);
}

Alexander Bell · Accepted Answer · 2014-07-14T17:20:12.133

Following code snippet demonstrates the technique of exporting data from .NET DataTable to Excel Workbook using C# and Microsoft.Office.Interop.Excel:

 internal static bool Export2Excel(DataTable dataTable, bool Interactive)
    {
        object misValue = System.Reflection.Missing.Value;

        // Note: don't put Microsoft.Office.Interop.Excel in 'using' section:
        // it may cause ambiguity w/System.Data because both have DataTable obj
        // use in-place reference as shown below
        Microsoft.Office.Interop.Excel.Application _appExcel = null;
        Microsoft.Office.Interop.Excel.Workbook _excelWorkbook = null;
        Microsoft.Office.Interop.Excel.Worksheet _excelWorksheet = null;
        try
        {
            // excel app object
            _appExcel = new Microsoft.Office.Interop.Excel.Application();

            // excel workbook object added to app
            _excelWorkbook = _appExcel.Workbooks.Add(misValue);

            _excelWorksheet = _appExcel.ActiveWorkbook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;

            // column names row (range obj)
            Microsoft.Office.Interop.Excel.Range _columnsNameRange;
            _columnsNameRange = _excelWorksheet.get_Range("A1", misValue).get_Resize(1, dataTable.Columns.Count);

            // column names array to be assigned to _columnNameRange
            string[] _arrColumnNames = new string[dataTable.Columns.Count];

            // assign array to column headers range, make 'em bold
            _columnsNameRange.set_Value(misValue, _arrColumnNames);
            _columnsNameRange.Font.Bold = true;

            // populate data content row by row
            for (int Idx = 0; Idx < dataTable.Rows.Count; Idx++)
            {
                _excelWorksheet.Range["A2"].Offset[Idx].Resize[1, dataTable.Columns.Count].Value =
                dataTable.Rows[Idx].ItemArray;
            }

            // Autofit all Columns in the range
            _columnsNameRange.Columns.EntireColumn.AutoFit();


            // make visible and bring to the front (note: warning due to .Activate() ambiguity)
            if (Interactive)  { _appExcel.Visible = Interactive; _excelWorkbook.Activate(); }
            // // save and close if Interactive flag not set
            else
            {
                // Excel 2010 - "14.0"
                // Excel 2007 - "12.0"
                string _ver = _appExcel.Version;
                double _dblVer = double.Parse(_ver);

                string _fileName ="TableExported_" + 
                    DateTime.Today.ToString("yyyy_MM_dd") + "-" +
                    DateTime.Now.ToString("hh_mm_ss");

                // check version and select file extension
                if (_dblVer >= 12) { _fileName += ".xlsx"; }
                else { _fileName += ".xls"; }

                // save and close Excel workbook in Document Dir
                string _subDir = System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), _expDir);

                // create if target directory doesn't exists
                if (!System.IO.Directory.Exists(_subDir)) { System.IO.Directory.CreateDirectory(_subDir); }

                // format the full File path
                string _pathFile = System.IO.Path.Combine(_subDir, _fileName);

                // save file and close Excel workbook
                _excelWorkbook.Close(true, _pathFile, misValue);
            }

            // quit excel app process
            if (_appExcel != null)
            {
                _appExcel.UserControl = false;
                _appExcel.Quit();
            }
            return true;
        }
        catch (Exception ex) 
         {  
            // alternatively you can show Error message
            throw; 
          }
        finally
        {
            // release ref vars
            if (_appExcel != null)
            {
                _excelWorksheet = null;
                _excelWorkbook = null;
                _appExcel = null;
                misValue = null;
            }
        }
    }

Hope this will help. Regards,

Accessing a table in an excel spreadsheet using C#

2 Answers2

Linked