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!

Answer 1

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);
}

Answer 2

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,