将excel表嵌入到C＃用户控件中 [英] Embed excel sheet in C# user control

问题描述

我需要在C＃语言的用户控件中嵌入Excel表单部分（而不是整个工作表）。我想操纵它上的数据并将其保存在一个集合中。

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.

最好的方法是什么？

推荐答案

我将使用Excel COM Library打开电子表格。如果您添加对Microsoft Excel对象库的引用，您可以访问Com界面。

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.

添加以下使用语句：

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

此代码将将A1单元格的内容读入字符串。

This code would read the contents of the A1 cell into a string.

使用Excel COM界面的奇怪之处在于，您必须访问范围中的数据，即使您只想要一个单元格。您可以将范围设置为一组单元格，然后可以迭代返回的集合以获取每个单元格的内容。

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.

您还需要在文件名和选项卡名称上添加一些错误检查/处理。

You would also want to add some error checking/handling on the file name and tab name.

还有一种使用ODBC从Excel读取的方法，但电子表格必须格式化为一定的方式在第1行必须有一个标题数据。我发现使用COM界面更容易。

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.

一旦你获得了你需要的数据，你可以把它成为一个类型化的DataSet。那么如果您在WPF中使用WinForms或ListBox，那么可以将该数据集绑定到DataGridView。如果您只想以XML格式保存数据，则可以使用DataSet WriteXml函数将数据存储到文件中。

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.

这篇关于将excel表嵌入到C＃用户控件中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！