使用c#访问excel电子表格中的表

本文关键字：电子表格 excel 访问 使用 | 更新日期: 2023-09-27 18:12:46

我正在编写一个程序(用c#，使用Microsoft.Office.Interop.Excel)，它读取Excel电子表格并对其中的数据应用过滤器等。然而，我正在努力解决几个问题，即如何"获取"一个表对象，表示我正在使用的数据表。我还希望能够通过标题访问列，因此我假设需要DataTable名称空间。然而，我似乎不知道该怎么办。

这是我的代码的大致框架:

    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的有用文章的任何建议。互操作，是非常受欢迎的。我真的没能找到很多符合我需要的资源。非常感谢!

使用c#访问excel电子表格中的表

我一直在使用EPPLUS，你可以通过NuGet获得它。使用EPPLUS代替Microsoft.Office.Excel.Interop

自切片面包以来最好的东西。

这里是一个stackoverflow链接，展示如何导出到Excel

下面是如何导入excel到数据表

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

下面的代码片段演示了使用c#和Microsoft.Office.Interop.Excel从。net DataTable导出数据到Excel Workbook的技术:

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

希望这将帮助。问候,

相关文章：