创建C＃的Excel VBA代码和编程按钮 [英] Create Excel VBA code and button programmatically from C#

问题描述

我在简单的方法的中间，即节省了我的DataGridView到Excel文件（仅限1张），也增加了VBA代码和一个按钮来运行VBA代码。

 公共无效SAVEFILE（字符串文件路径）
 {
 
 Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp =新Microsoft.Office.Interop .Excel.ApplicationClass（）; 
 ExcelApp.Application.Workbooks.Add（Type.Missing）; 
 
 //改变工作簿的属性。 
 ExcelApp.Columns.ColumnWidth = 20; 
 
 //存储在Excel中头部分。 
的for（int i = 1; I< gridData.Columns.Count + 1;我++）
 {
 ExcelApp.Cells [1，I] = gridData.Columns [我 -  1] .HeaderText; 
} 
 
 //保存每一行和每一列值的Excel工作表
为（INT行= 0;&行LT; gridData.Rows.Count;排++）
 {
 gridData.Rows [行] .Cells [0] .value的=万客隆; 
为（INT列= 0;&列LT; gridData.Columns.Count;柱++）
 {
 ExcelApp.Cells [行+ 2，+列1] = gridData.Rows [行] .Cells [专栏] .Value.ToString（）; 
} 
} 
 
 ExcelApp.ActiveWorkbook.SaveCopyAs（文件路径）; 
 ExcelApp.ActiveWorkbook.Saved = TRUE; 
 ExcelApp.Quit（）; 
} 
  

我只实现DataGridView的出口。

编辑：由于乔尔我可以，在适当的话，再次为解决搜索。我认为，这可能是有益的 。你愿意指正或者给有关应该是什么我的提示或两个。

解决方案

我刚写了一个小例子< STRONG>添加了一个新按钮以现有工作簿，之后添加按钮被点击

<时将被调用宏pre> 使用Excel =的Microsoft.Office.Interop.Excel;使用VBIDE = Microsoft.Vbe.Interop
;
：

私人静态无效excelAddButtonWithVBA（）
{
Excel.Application xlApp =新Excel.Application（）;
Excel.Workbook xlBook = xlApp.Workbooks.Open（@PATH_TO_EXCEL_FILE）;
Excel.Worksheet wrkSheet = xlBook.Worksheets [1];
Excel.Range范围;

试
{
//设置范围插入细胞
范围= wrkSheet.get_Range（A1：A1）;

//插入下拉到单元格
Excel.Buttons xlButtons = wrkSheet.Buttons（）;
Excel.Button xlButton = xlButtons.Add（（双）range.Left，（双）range.Top，（双）range.Width，（双）range.Height）;

//设置新键
xlButton.Name =btnDoSomething的名称;
xlButton.Text =点击我！;
xlButton.OnAction =btnDoSomething_Click;

buttonMacro（xlButton.Name，xlApp，xlBook，wrkSheet）;
}
赶上（异常前）
{
的Debug.WriteLine（ex.Message）;
}
xlApp.Visible = TRUE;
}


在这里，我们得到了 buttonMacro（..） 法

 私有静态无效buttonMacro（字符串BUTTONNAME，Excel.Application xlApp，Excel.Workbook wrkBook， Excel.Worksheet wrkSheet）
 {
 StringBuilder的某人; 
 VBIDE.VBComponent xlModule; 
 VBIDE.VBProject PRJ; 
 
 PRJ = wrkBook.VBProject; 
某人=新的StringBuilder（）; 
 
 //建立字符串模块代码
 sb.Append（子+ BUTTONNAME +_Click（）+\\\
）; 
 sb.Append（\t+的msgbox \+ BUTTONNAME +\\\\
）; //添加自定义的VBA代码这里
 sb.Append（结束子）; 
 
 //设置对象为新模块创建
 xlModule = wrkBook.VBProject.VBComponents.Add（VBIDE.vbext_ComponentType.vbext_ct_StdModule）; 
 
 //将宏添加到电子表格
 xlModule.CodeModule.AddFromString（sb.ToString（））; 
} 
  

找到一个KB文章的 如何通过从Visual C＃.NET中使用自动化创建Excel宏

I am in the middle of simple method, that saves my DataGridView into an Excel document (1 sheet only) and also adds VBA code and a button to run the VBA code.

public void SaveFile(string filePath)
    {

        Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
        ExcelApp.Application.Workbooks.Add(Type.Missing);

        //Change  Workbook-properties.
        ExcelApp.Columns.ColumnWidth = 20;

        // Storing header part in Excel.
        for (int i = 1; i < gridData.Columns.Count + 1; i++)
        {
            ExcelApp.Cells[1, i] = gridData.Columns[i - 1].HeaderText;
        }

        //Storing Each row and column value to excel sheet
        for (int row = 0; row < gridData.Rows.Count; row++)
        {
            gridData.Rows[row].Cells[0].Value = "Makro";
            for (int column = 0; column < gridData.Columns.Count; column++)
            {
                ExcelApp.Cells[row + 2, column + 1] = gridData.Rows[row].Cells[column].Value.ToString();
            }
        }

        ExcelApp.ActiveWorkbook.SaveCopyAs(filePath);
        ExcelApp.ActiveWorkbook.Saved = true;
        ExcelApp.Quit();
    }

I only implemented DataGridView export.

EDIT: Thanks to Joel I could, with proper words, search again for the solution. I think that this may be helpful. Would you correct me or give a tip or two about what I should look for.

解决方案

I just wrote a small example which adds a new button to an existing workbook and afterwards add a macro which will be called when the button is clicked.

using Excel = Microsoft.Office.Interop.Excel;
using VBIDE = Microsoft.Vbe.Interop;
...

private static void excelAddButtonWithVBA()
{
    Excel.Application xlApp = new Excel.Application();
    Excel.Workbook xlBook = xlApp.Workbooks.Open(@"PATH_TO_EXCEL_FILE");
    Excel.Worksheet wrkSheet = xlBook.Worksheets[1];
    Excel.Range range;

    try
    {
        //set range for insert cell
        range = wrkSheet.get_Range("A1:A1");

        //insert the dropdown into the cell
        Excel.Buttons xlButtons = wrkSheet.Buttons();
        Excel.Button xlButton = xlButtons.Add((double)range.Left, (double)range.Top, (double)range.Width, (double)range.Height);

        //set the name of the new button
        xlButton.Name = "btnDoSomething";
        xlButton.Text = "Click me!";
        xlButton.OnAction = "btnDoSomething_Click";

        buttonMacro(xlButton.Name, xlApp, xlBook, wrkSheet);
    }
    catch (Exception ex)
    {
        Debug.WriteLine(ex.Message);
    }
    xlApp.Visible = true;
}

And here we got the buttonMacro(..) method

private static void buttonMacro(string buttonName, Excel.Application xlApp, Excel.Workbook wrkBook, Excel.Worksheet wrkSheet)
{
    StringBuilder sb;
    VBIDE.VBComponent xlModule;
    VBIDE.VBProject prj;

    prj = wrkBook.VBProject;
    sb = new StringBuilder();

    // build string with module code
    sb.Append("Sub " + buttonName + "_Click()" + "\n");
    sb.Append("\t" + "msgbox \"" + buttonName + "\"\n"); // add your custom vba code here
    sb.Append("End Sub");

    // set an object for the new module to create
    xlModule = wrkBook.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule);

    // add the macro to the spreadsheet
    xlModule.CodeModule.AddFromString(sb.ToString());
}

Found this information within an KB article How To Create an Excel Macro by Using Automation from Visual C# .NET

这篇关于创建C＃的Excel VBA代码和编程按钮的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！