在 C# 用户控件中嵌入 Excel 工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/757023/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Embed excel sheet in C# user control
提问by
I need to embed an excel sheet portion (not the whole sheet) in a user control in C# language. And I want to manipulate the data on it and save it in a collection.
我需要在 C# 语言的用户控件中嵌入一个 excel 工作表部分（而不是整个工作表）。我想操作它上面的数据并将其保存在一个集合中。
What is the best way to go about it?
最好的方法是什么？
采纳答案by epotter
I would open the spreadsheet with the Excel COM Library. If you add a reference to the Microsoft Excel Object Library, you can get to the Com interface.
我会用 Excel COM 库打开电子表格。如果添加对 Microsoft Excel 对象库的引用，则可以访问 Com 界面。
Add these using statements:
添加这些 using 语句：
using Microsoft.Office.Interop;
using Microsoft.Office.Interop.Excel;
Then you can read from the spreadsheet by doing something like this:
然后，您可以通过执行以下操作从电子表格中读取：
private void GetData(string fileName, string tabName)
{
Workbook theWorkbook;
Application ExcelObj = null;
ExcelObj = new Application();
theWorkbook = ExcelObj.Workbooks.Open(fileName,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
Sheets sheets = theWorkbook.Worksheets;
Worksheet worksheet = (Worksheet)sheets[tabName];
Range range = worksheet.get_Range("A1:A1", Type.Missing);
string data = range.Text as string;
//
// TODO : store the data
//
theWorkbook.Close(false, fileName, null);
}
This code would read the contents of the A1 cell into a string.
此代码将 A1 单元格的内容读入字符串。
One of the quirks of working with the Excel COM interface is that you have to access data in a Range, even if you just want one cell. You can set the range to be a group of cells, then you can iterate over the collection that it returns to get the contents of each cell.
使用 Excel COM 接口的怪癖之一是您必须访问范围中的数据，即使您只需要一个单元格。您可以将范围设置为一组单元格，然后您可以遍历它返回的集合以获取每个单元格的内容。
You would also want to add some error checking/handling on the file name and tab name.
您还需要在文件名和选项卡名称上添加一些错误检查/处理。
There is also a way to use ODBC to read from Excel, but the spreadsheet has to be formatted in a certain way. There has to be a header data in row 1. I've found it easier to use the COM interface.
还有一种方法可以使用 ODBC 从 Excel 中读取数据，但是电子表格必须以某种方式进行格式化。第 1 行必须有标题数据。我发现使用 COM 接口更容易。
Once you have acquired the data you need, you could put it into a typed DataSet. Then you could bind that dataset to a DataGridView if you're using WinForms or a ListBox in WPF. If you just want to save the data in an XML format, you could use the DataSet WriteXml function to store the data to a file.
一旦你获得了你需要的数据，你就可以把它放入一个类型化的 DataSet 中。然后，如果您在 WPF 中使用 WinForms 或 ListBox，则可以将该数据集绑定到 DataGridView。如果您只想以 XML 格式保存数据，您可以使用 DataSet WriteXml 函数将数据存储到文件中。
回答by kiswa
Why not use a DataGridViewpopulated with the data you want to alter?
为什么不使用填充了要更改的数据的DataGridView？
Once any alterations are complete, save the data from the DataGridView over the portion of the Excel file you pulled it from to begin with.
完成任何更改后，将 DataGridView 中的数据保存在您从中提取数据的 Excel 文件部分上。
回答by norlando
Look into the Microsoft.Office.Interop.Excel library. There are a lot of excel controls in there to work with excel files and operations.
查看 Microsoft.Office.Interop.Excel 库。那里有很多 excel 控件可以处理 excel 文件和操作。
