C# 使用工作表作为数据源的 VSTO Excel 简单示例

Simple Example of VSTO Excel using a worksheet as a datasource

提问by Unknown Coder

I think I'm running into a case of "the easiest answers are the hardest ones to find" and I haven't come across any searches that give this to me in a straightforward way. This is for Excel 2010and VS 2010within an existing VSTO (C#) project.

我想我遇到了“最简单的答案是最难找到的答案”的情况，而且我还没有遇到任何以直接方式向我提供此信息的搜索。这适用于现有 VSTO (C#) 项目中的Excel 2010和VS 2010。

I have an Excel worksheet that contains 4 columns of data that I would like to use as a source for a DataGridView. Can someone please provide C# code snippets for (1) getting the data from a particular worksheet and populating a custom object with it? (2) binding the object (like an IEnumerable list) to a Datagridview and (3) some snippets for the update and delete functionality that would be inherent to the grid and feed back to the source worksheet.

我有一个 Excel 工作表，其中包含 4 列数据，我想将其用作 DataGridView 的源。有人可以提供用于 (1) 从特定工作表获取数据并用它填充自定义对象的 C# 代码片段吗？(2) 将对象（如 IEnumerable 列表）绑定到 Datagridview 和 (3) 一些用于更新和删除功能的片段，这些片段是网格固有的并反馈给源工作表。

I know I'm asking for a lot here, but so much of the VSTO information seems to be dis-jointed and not always easy to find. Thanks!

我知道我在这里要求很多，但如此多的 VSTO 信息似乎是脱节的，并不总是很容易找到。谢谢！

采纳答案by cremor

Edit:Great, I just noticed that I missed a big part of your question, getting updates and deletes back to the worksheet. I have absolutely no idea if that is possible but I think that makes my solution worthless. I'll leave it here anyway, maybe it can help in any way.

编辑：太好了，我只是注意到我错过了您问题的很大一部分，将更新和删除返回到工作表。我完全不知道这是否可能，但我认为这使我的解决方案毫无价值。无论如何我会把它留在这里，也许它可以以任何方式提供帮助。

---

---

Why do you need VSTO? As far as I know VSTO is used for Office Add-Ins. But since you want to show the data in a DataGridView I assume that you have a WinForms application that should just access a workbook. In this case you can simply open the workbook by using Office Interop. Just add a reference to Microsoft.Office.Interop.Excel to your project and add a using Microsoft.Office.Interop.Excel;statement.

为什么需要 VSTO？据我所知，VSTO 用于 Office 加载项。但是由于您想在 DataGridView 中显示数据，我假设您有一个应该只访问工作簿的 WinForms 应用程序。在这种情况下，您只需使用 Office Interop 打开工作簿即可。只需在您的项目中添加对 Microsoft.Office.Interop.Excel 的引用并添加一条using Microsoft.Office.Interop.Excel;语句即可。

MSDN reference documentation for Excel Interop can be found here: http://msdn.microsoft.com/en-us/library/ms262200%28v=office.14%29.aspx

可以在此处找到 Excel Interop 的 MSDN 参考文档：http: //msdn.microsoft.com/en-us/library/ms262200%28v=office.14%29.aspx

I'll give you the Excel part, maybe someone else can do the rest.

我会给你 Excel 部分，也许其他人可以做剩下的。

First, open Excel and the workbook:

首先，打开 Excel 和工作簿：

Application app = new Application();
// Optional, but recommended if the user shouldn't see Excel.
app.Visible = false;
app.ScreenUpdating = false;
// AddToMru parameter is optional, but recommended in automation scenarios.
Workbook workbook = app.Workbooks.Open(filepath, AddToMru: false);

Then somehow get the correct worksheet. You have a few possiblities:

然后以某种方式获得正确的工作表。你有几种可能：

// Active sheet (should be the one which was active the last time the workbook was saved).
Worksheet sheet = workbook.ActiveSheet;
// First sheet (notice that the first is actually 1 and not 0).
Worksheet sheet = workbook.Worksheets[1];
// Specific sheet.
// Caution: Default sheet names differ for different localized versions of Excel.
Worksheet sheet = workbook.Worksheets["Sheet1"];

Then get the correct range. You didn't specify how you know where the needed data is, so I'll assume it is in fixed columns.

然后得到正确的范围。您没有指定如何知道所需数据的位置，因此我假设它位于固定列中。

// If you also know the row count.
Range range = sheet.Range["A1", "D20"];
// If you want to get all rows until the last one that has some data.
Range lastUsedCell = sheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell);
string columnName = "D" + lastUsedCell.Row;
Range range = sheet.Range["A1", columnName];

Get the values:

获取值：

// Possible types of the return value:
// If a single cell is in the range: Different types depending on the cell content
// (string, DateTime, double, ...)
// If multiple cells are in the range: Two dimensional array that exactly represents
// the range from Excel and also has different types in its elements depending on the
// value of the Excel cell (should always be that one in your case)
object[,] values = range.Value;

That two dimensional object array can then be used as a data source for your DataGridView. I haven't used WinForms for years so I don't know if you can bind it directly or first need to get the data into some specific format.

然后可以将二维对象数组用作 DataGridView 的数据源。好多年没用WinForms了，不知道是可以直接绑定还是先把数据转成某种特定的格式。

Finally close Excel again:

最后再次关闭Excel：

workbook.Close(SaveChanges: false);
workbook = null;
app.Quit();
app = null;
// Yes, we really want to call those two methods twice to make sure all
// COM objects AND all RCWs are collected.
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();

Correctly closing Excel after using Interop is a task itself because you have to make sure that all references to COM objects have been released. The easiest way I have found to do this is to do all the work except opening and closing Excel and the workbook (so my first and last code block) in a seperate method. This ensures that all COM objects used in that method are out of scope when Quitis called.

在使用 Interop 后正确关闭 Excel 本身就是一项任务，因为您必须确保已释放对 COM 对象的所有引用。我发现最简单的方法是在单独的方法中完成所有工作，除了打开和关闭 Excel 和工作簿（所以我的第一个和最后一个代码块）。这可确保该方法中使用的所有 COM 对象在Quit调用时都超出范围。

回答by juanvan

So in the Sheet1_Startup event

所以在 Sheet1_Startup 事件中

Excel.Range range1 = this.Range["A1", missing];
var obj = range1.Value2.ToString();

You would need to move to the next cell then

然后你需要移动到下一个单元格

    range1 = this.Range["A2", missing];
    obj = New list(range1.Value2.ToString());

回答by Erdogan Kurtur

this is one of the most ugly codes I've written but it will work as a proof of concept :) I've created an example workbook like that

这是我写过的最丑陋的代码之一，但它可以作为概念证明:) 我已经创建了一个这样的示例工作簿

Column1     Column2     Column3     Column4
------------------------------------------------------
Data-1-1    Data-2-1    Data-3-1    Data-4-1
Data-1-2    Data-2-2    Data-3-2    Data-4-2
....

Excel file contains exactly 50 lines, this explains the hard-coded range selectors. After writing that part of code rest is easy, just create a form, add a dataviewgrid, create a data source for MyExcelData, create an instance of MyExcelDatalike var data = new MyExcelData(pathToExcelFile);and bind it to grid.

Excel 文件正好包含 50 行，这解释了硬编码的范围选择器。写完那部分代码就很简单了，只需创建一个表单，添加一个dataviewgrid，创建一个数据源MyExcelData，创建一个MyExcelDatalike实例var data = new MyExcelData(pathToExcelFile);并将其绑定到grid。

Code is ugly, and has many assumptions but it implements your requirements. If you open excel and program you can see updates on grid are reflected on excel after cell edited. deleted row is also removed from excel. since I did not know whether you have primary keys of your excel or not, I used row index as ID.

代码很丑，有很多假设，但它实现了你的要求。如果您打开 excel 和程序，您可以看到网格上的更新在单元格编辑后反映在 excel 上。删除的行也会从 excel 中删除。因为我不知道你是否有你的 excel 的主键，我用行索引作为 ID。

BTW, I'm really bad when it comes to VSTO. so if you know a better way open/edit/save please notify me.

顺便说一句，谈到 VSTO，我真的很糟糕。因此，如果您知道更好的打开/编辑/保存方法，请通知我。

public class MyExcelDataObject
{
    private readonly MyExcelData owner;
    private readonly object[,] realData;
    private int RealId;
    public MyExcelDataObject(MyExcelData owner, int index, object[,] realData)
    {
        this.owner = owner;
        this.realData = realData;
        ID = index;
        RealId = index;
    }

    public int ID { get; set; }

    public void DecrementRealId()
    {
        RealId--;
    }

    public string Column1
    {
        get { return (string)realData[RealId, 1]; }
        set
        {
            realData[ID, 1] = value;
            owner.Update(ID);
        }
    }
    public string Column2
    {
        get { return (string)realData[RealId, 2]; }
        set
        {
            realData[ID, 2] = value;
            owner.Update(ID);
        }
    }
    public string Column3
    {
        get { return (string)realData[RealId, 3]; }
        set
        {
            realData[ID, 3] = value;
            owner.Update(ID);
        }
    }
    public string Column4
    {
        get { return (string)realData[RealId, 4]; }
        set
        {
            realData[ID, 4] = value;
            owner.Update(ID);
        }
    }
}

public class MyExcelData : BindingList<MyExcelDataObject>
{
    private Application excel;
    private Workbook wb;
    private Worksheet ws;

    private object[,] values;

    public MyExcelData(string excelFile)
    {
        excel = new ApplicationClass();
        excel.Visible = true;
        wb = excel.Workbooks.Open(excelFile);
        ws = (Worksheet)wb.Sheets[1];

        var range = ws.Range["A2", "D51"];
        values = (object[,])range.Value;

        AllowEdit = true;
        AllowRemove = true;
        AllowEdit = true;

        for (var index = 0; index < 50; index++)
        {
            Add(new MyExcelDataObject(this, index + 1, values));
        }
    }

    public void Update(int index)
    {
        var item = this[index - 1];

        var range = ws.Range["A" + (2 + index - 1), "D" + (2 + index - 1)];
        range.Value = new object[,]
            {
                {item.Column1, item.Column2, item.Column3, item.Column4}
            };
    }

    protected override void RemoveItem(int index)
    {
        var range = ws.Range[string.Format("A{0}:D{0}", (2 + index)), Type.Missing];
        range.Select();
        range.Delete();
        base.RemoveItem(index);

        for (int n = index; n < Count; n++)
        {
            this[n].DecrementRealId();
        }
    }
}

PS: I'd like to use lightweight objects but it adds unnecessary complications.

PS：我想使用轻量级对象，但它增加了不必要的复杂性。

回答by Sangram Nandkhile

UPDATE:

更新：

I replaced my previous method with newer code for faster approach. System.Arrayis quite efficient and faster way to read and bind data to the excel. You can download the demo from this link.

我用更新的代码替换了我以前的方法以获得更快的方法。System.Array是读取数据并将数据绑定到 excel 的非常有效和更快的方法。您可以从此链接下载演示。

---

---

I have developed VSTO application in Excel 2003 Workbook. There is no big differences in terms of syntax,so you can use it in 2007 / 2010 with no efforts.

我在 Excel 2003 Workbook 中开发了 VSTO 应用程序。语法上没有太大差异，因此您可以在 2007 / 2010 中毫不费力地使用它。

I didn't know which event you will be using to open the window showing data so i am assuming that you will be using.

我不知道您将使用哪个事件来打开显示数据的窗口，因此我假设您将使用。

SheetFollowHyperlink

I am going to use Static workbook object declared in Showdata.cs. Here's the code for your Thisworkbook.cs

我将使用在 Showdata.cs 中声明的静态工作簿对象。这是您的代码Thisworkbook.cs

 private void ThisWorkbook_Startup(object sender, System.EventArgs e)
        {
            ShowData._WORKBOOK = this;
        }
private void ThisWorkbook_SheetFollowHyperlink(object Sh, Microsoft.Office.Interop.Excel.Hyperlink Target)
        {
            System.Data.DataTable dTable =  GenerateDatatable();
            showData sh = new showData(dTable);
            sh.Show(); // You can also use ShowDialog()
        }

I have added a Link on the current sheet and it will pop up the window with a datagridview.

我在当前工作表上添加了一个链接，它将弹出带有 datagridview 的窗口。

         private System.Data.DataTable GenerateDatatable()
    {
        Range oRng = null;
        // It takes the current activesheet from the workbook. You can always pass any sheet as an argument

        Worksheet ws = this.ActiveSheet as Worksheet;

        // set this value using your own function to read last used column, There are simple function to find last used column
        int col = 4;
        // set this value using your own function to read last used row, There are simple function to find last used rows
        int row = 5;

//lets assume its 4 and 5 returned by method string strRange = "A1"; string andRange = "D5";

//假设它的 4 和 5 由方法 string strRange = "A1"; 返回；string andRange = "D5";

        System.Array arr = (System.Array)ws.get_Range(strRange, andRange).get_Value(Type.Missing);
        System.Data.DataTable dt = new System.Data.DataTable();
        for (int cnt = 1;
            cnt <= col; cnt++)
            dt.Columns.Add(cnt.Chr(), typeof(string));
        for (int i = 1; i <= row; i++)
        {
            DataRow dr = dt.NewRow();
            for (int j = 1; j <= col; j++)
            {
                dr[j - 1] = arr.GetValue(i, j).ToString();
            }
            dt.Rows.Add(dr);
        }
        return dt;
    }

Here's the form which will allow user to display and edit values. I have added extension methods and Chr() to convert numerical into respective alphabets which will come handy.

这是允许用户显示和编辑值的表单。我添加了扩展方法s 和 Chr() 以将数字转换为相应的字母，这将派上用场。

public partial class ShowData : Form
    {
        //use static workbook object to access Worksheets
        public static ThisWorkbook _WORKBOOK;

        public ShowData(System.Data.DataTable dt)
        {
            InitializeComponent();
            // binding value to datagrid
            this.dataGridView1.DataSource = dt;
        }

        private void RefreshExcel_Click(object sender, EventArgs e)
        {
            Worksheet ws = ShowData._WORKBOOK.ActiveSheet as Worksheet;
            System.Data.DataTable dTable = dataGridView1.DataSource as System.Data.DataTable;

            // Write values back to Excel sheet
            // you can pass any worksheet of your choice in ws
            WriteToExcel(dTable,ws);
        }

       private void WriteToExcel(System.Data.DataTable dTable,Worksheet ws)
    {
        int col = dTable.Columns.Count; ; 
        int row = dTable.Rows.Count;

        string strRange = "A1";
        string andRange = "D5";

        System.Array arr = Array.CreateInstance(typeof(object),5,4);
        for (int i = 0; i < row; i++)
        {
            for (int j = 0; j < col; j++)
            {
                try
                {
                    arr.SetValue(dTable.Rows[i][j].ToString(), i, j);
                }
                catch { }
            }

        }
        ws.get_Range(strRange, andRange).Value2 = arr;
        this.Close();
    }
    public static class ExtensionMethods
    {
        static string alphabets = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
        public static string Chr(this int p_intByte)
        {

            if (p_intByte > 0 && p_intByte <= 26)
            {
                return alphabets[p_intByte - 1].ToString();
            }
            else if (p_intByte > 26 && p_intByte <= 700)
            {
                int firstChrIndx = Convert.ToInt32(Math.Floor((p_intByte - 1) / 26.0));
                int scndIndx = p_intByte % 26;
                if (scndIndx == 0) scndIndx = 26;
                return alphabets[firstChrIndx - 1].ToString() + alphabets[scndIndx - 1].ToString();
            }

            return "NA";
        }

    }