Csharp 如何使用C#Interop EXCEL创建度量衡

VB.NET

我很快就用VB.net写了这段代码.它的工作方式与预期一致.当天晚些时候，如果我有时间，我会用C #代码更新帖子，如果你仍然卡住了…

Before:个

Code:个

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim xlApp As New Excel.Application
        Dim xlWb As Excel.Workbook
        Dim Mdl As Excel.Model
        Dim tbl As Excel.ModelTable

        With xlApp
            .Visible = True

            '~~> Open workbook
            xlWb = .Workbooks.Open("C:\Users\routs\Downloads\Test.xlsm")

            Mdl = xlWb.Model
            tbl = Mdl.ModelTables("Range")

            '~~> Delete the measure if it already exists
            Try
                Mdl.ModelMeasures("NewMeasure").Delete
            Catch ex As Exception
            End Try

            Try
                Mdl.ModelMeasures.Add("NewMeasure", tbl, "1+2", Mdl.ModelFormatDecimalNumber(False, 2))
            Catch ex As Exception
                MessageBox.Show(ex.Message, "System Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End With

        '~~> Save and Close workbook and quit Excel
        xlWb.Close(True)
        xlApp.Quit()

        '~~> Flush the toilet
        Kawoosh(tbl)
        Kawoosh(xlWb)
        Kawoosh(Mdl)
        Kawoosh(xlApp)
    End Sub

    Private Sub Kawoosh(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Class

Output个

C #

在VB.Net中，默认情况下关闭Option Strict，因此它允许隐式数据类型转换.如果您将我上面给出的代码直接转换为C #，它将在如下所示的行上出错.我相信这就是你得到的错误...

在VB.net中，当您在项目上单击鼠标右键并 Select "属性"时，您会看到Compile标签(如下所示)，您可以在其中调整Option Strict属性.

C #中缺少此选项卡(如下所示).

C #中不存在此设置，因为就像我上面提到的那样，C #中不允许隐式数据类型转换.

那么，我们该如何解决这个问题呢？我们使用关键字dynamic.该关键字提供了与C #等价的Option Struction功能.

试试这个代码.我已经测试过了.它起作用了.

using System;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

namespace WindowsFormsApp1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Excel.Application xlApp = new Excel.Application();
            Excel.Workbook xlWb;
            // Excel.Model Mdl;
            Excel.ModelTable tbl;

            {
                xlApp.Visible = true;

                // ~~> Open workbook
                xlWb = xlApp.Workbooks.Open(@"C:\Users\routs\Downloads\Test.xlsm");

                dynamic Mdl = xlWb.Model;

                tbl = Mdl.ModelTables["Range"];

                try
                {
                    Mdl.ModelMeasures["NewMeasure"].Delete();
                }
                catch (Exception ex)
                {
                }

                try
                {
                    Mdl.ModelMeasures.Add("NewMeasure", tbl, "1+2", Mdl.ModelFormatDecimalNumber(false, 2));
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "System Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }

            // ~~> Close workbook and quit Excel
            xlWb.Close(true);
            xlApp.Quit();

            // ~~> Flush the toilet
            Kawoosh(tbl);
            Kawoosh(xlWb);
            Kawoosh(xlApp);
        }
        private void Kawoosh(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
            }
            finally
            {
                GC.Collect();
            }
        }
    }
}