使用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屋!