使用C#访问Excel电子表格中的表 [英] Accessing a table in an excel spreadsheet using C#

问题描述

我正在编写一个程序(使用C#，使用Microsoft.Office.Interop.Excel)，该程序可以读取Excel电子表格，并对其中的数据应用过滤器等.但是，我在一些问题上苦苦挣扎，即如何获取"代表我正在使用的数据表的表对象.我也希望能够通过其标题访问列，因此我假设我将需要DataTable命名空间.但是，我似乎不知道该怎么办.

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)
        {

        }
    }

我真的不确定该如何去做，因此，真的很欢迎任何帮助以及有关如何使用Microsoft.Office.Excel.Interop的有用文章的任何建议.我确实找不到很多适合我需求的资源.非常感谢你！

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!

推荐答案

以下代码段演示了使用C#和Microsoft.Office.Interop.Excel将数据从.NET DataTable导出到Excel工作簿的技术:

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,

这篇关于使用C#访问Excel电子表格中的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！