怎么能在我的c#程序中调用excel的宏呢？

Question

http://www.epoint.cn/Article_Show.asp?ArticleID=399

Answer 1

按下面步骤录制一个宏：1、随便选择几个单元格；
2、选择菜单“工具”→“宏”→“录制新宏”，输入宏的名称，就用默认的“宏1”吧，点确定；
3、选择菜单“格式”→“单元格”，在对话框中选择“边框”，将内边框和外边框均选中，按确定；
4、此时，刚才选择的单元格就有了边框，再点工具栏中的“停止录制宏”按钮 来结束宏录制。刚才的操作目的是录制宏而不是加边框，因此，我们按“Ctrl+Z”来撤消刚才的操作，通过按Alt+F8来调出宏，选择“宏1”，选择编辑，看到的代码应该如下： Sub 宏1()
' 宏1 Macro
' xx 记录的宏 2003-5-1    Selection.Borders(xlDiagonalDown).LineStyle = xlNone    Selection.Borders(xlDiagonalUp).LineStyle = xlNone    With Selection.Borders(xlEdgeLeft)        .LineStyle = xlContinuous        .Weight = xlThin        .ColorIndex = xlAutomatic    End With    With Selection.Borders(xlEdgeTop)        .LineStyle = xlContinuous        .Weight = xlThin        .ColorIndex = xlAutomatic    End With    With Selection.Borders(xlEdgeBottom)        .LineStyle = xlContinuous        .Weight = xlThin        .ColorIndex = xlAutomatic    End With    With Selection.Borders(xlEdgeRight)        .LineStyle = xlContinuous        .Weight = xlThin        .ColorIndex = xlAutomatic    End With    With Selection.Borders(xlInsideVertical)        .LineStyle = xlContinuous        .Weight = xlThin        .ColorIndex = xlAutomatic    End With    With Selection.Borders(xlInsideHorizontal)        .LineStyle = xlContinuous        .Weight = xlThin        .ColorIndex = xlAutomatic    End WithEnd Sub图中表的数据都是供排版参考用的，结束前将实际内容删除掉，即只留下排好版的格式，包括标题、列标题等，将实际内容去掉。将文件保存到一个地方，如D:\Normal.xls，当然，实际开发时，可以放到执行文件所在目录下，为了防止用户随便修改，可以将文件名改为normal.rpt之类。 有了上面的准备，我们就可以在C#中使用了，添加“高级报表”按钮的响应代码。下面是全部代码清单。using System;using System.Drawing;using System.Collections;using System.ComponentModel;using System.Windows.Forms;using System.Data; using System.IO;using System.Reflection; namespace MyExcel{     /// <summary>     /// Form1 的摘要说明。     /// </summary>     public class Form1 : System.Windows.Forms.Form     {         private System.Windows.Forms.Button btnNormal;         private System.Windows.Forms.Button btnAdvace;         /// <summary>         /// 必需的设计器变量。         /// </summary>         private System.ComponentModel.Container components = null;          public Form1()         {              //              // Windows 窗体设计器支持所必需的              //              InitializeComponent();               //              // TODO: 在 InitializeComponent 调用后添加任何构造函数代码              //         }          /// <summary>         /// 清理所有正在使用的资源。         /// </summary>         protected override void Dispose( bool disposing )
         {              if( disposing )              {                   if (components != null)                    {                       components.Dispose();                   }              }              base.Dispose( disposing );         }          #region Windows Form Designer generated code         /// <summary>         /// 设计器支持所需的方法 - 不要使用代码编辑器修改         /// 此方法的内容。         /// </summary>         private void InitializeComponent()         {              this.btnNormal = new System.Windows.Forms.Button();              this.btnAdvace = new System.Windows.Forms.Button();              this.SuspendLayout();              //               // btnNormal              //               this.btnNormal.Location = new System.Drawing.Point(49, 55);              this.btnNormal.Name = "btnNormal";              this.btnNormal.TabIndex = 0;              this.btnNormal.Text = "普通报表";              this.btnNormal.Click += new System.EventHandler(this.btnNormal_Click);              //               // btnAdvace              //               this.btnAdvace.Location = new System.Drawing.Point(169, 55);              this.btnAdvace.Name = "btnAdvace";              this.btnAdvace.TabIndex = 1;              this.btnAdvace.Text = "高级报表";              this.btnAdvace.Click += new System.EventHandler(this.btnAdvace_Click);              //               // Form1              // 

Answer 2

this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);              this.ClientSize = new System.Drawing.Size(292, 133);              this.Controls.AddRange(new System.Windows.Forms.Control[] {
                    this.btnAdvace,this.btnNormal});              this.Name = "Form1";              this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;              this.Text = "Form1";              this.ResumeLayout(false);          }         #endregion          /// <summary>         /// 应用程序的主入口点。         /// </summary>         [STAThread]         static void Main()          {              Application.Run(new Form1());         }          private string [,] myData=          {              {"车牌号","类型","品 牌","型 号","颜 色","附加费证号","车架号"},              {"浙KA3676","危险品","货车","铁风SZG9220YY","白","1110708900","022836"},              {"浙KA4109","危险品","货车","解放CA4110P1K2","白","223132","010898"},              {"浙KA0001A","危险品","货车","南明LSY9190WS","白","1110205458","0474636"},              {"浙KA0493","上普货","货车","解放LSY9190WS","白","1110255971","0094327"},              {"浙KA1045","普货","货车","解放LSY9171WCD","蓝","1110391226","0516003"},              {"浙KA1313","普货","货车","解放9190WCD","蓝","1110315027","0538701"},              {"浙KA1322","普货","货车","解放LSY9190WS","蓝","24323332","0538716"},              {"浙KA1575","普货","货车","解放LSY9181WCD","蓝","1110314149","0113018"},              {"浙KA1925","普货","货车","解放LSY9220WCD","蓝","1110390626","00268729"},              {"浙KA2258","普货","货车","解放LSY9220WSP","蓝","111048152","00320"}         };           //普通报表，即单纯的文件导出功能         private void btnNormal_Click(object sender, System.EventArgs e)         {              //创建一个Excel文件              Excel.Application myExcel = new Excel.Application ( ) ;              myExcel.Application.Workbooks.Add ( true ) ;              //让Excel文件可见              myExcel.Visible=true;              //第一行为报表名称              myExcel.Cells[1,4]="普通报表";              //逐行写入数据，              for(int i=0;i<11;i++)
              {
                   for(int j=0;j<7;j++)
                   {
                       //以单引号开头，表示该单元格为纯文本
                       myExcel.Cells[2+i,1+j]="'"+myData[i,j];
                   }
              }
         }         //高级报表，根据模板生成的报表         private void btnAdvace_Click(object sender, System.EventArgs e)
         {              string filename="";              //将模板文件复制到一个新文件中              SaveFileDialog mySave=new SaveFileDialog();              mySave.Filter="Excel文件(*.XLS)|*.xls|所有文件(*.*)|*.*";              if(mySave.ShowDialog()!=DialogResult.OK)              {                   return;              }              else              {                   filename=mySave.FileName;                   //将模板文件copy到新位置，建议实际开发时用相对路径，如
                   //Application.StartupPath.Trim()+"\\report\\normal.xls"                                      string filenameold=mySave.FileName;                   FileInfo mode=new FileInfo("d:\\normal.xls");                   try                   {                       mode.CopyTo(filename,true);                   }                   catch(Exception ee)                   {                       MessageBox.Show(ee.Message);                       return;                   }               }               //打开复制后的文件              object missing=Missing.Value;              Excel.Application myExcel=new Excel.Application ( );              //打开新文件              myExcel.Application.Workbooks.Open(filename,missing,missing,missing,missing,
                missing,missing,missing,missing,missing,missing, missing,missing);               //将Excel显示出来              myExcel.Visible=true;
              //逐行写入数据，数组中第一行我列标题，忽略              for(int i=1;i<11;i++)
              {
                   for(int j=0;j<7;j++)
                   {
                       //以单引号开头，表示该单元格为纯文本                       myExcel.Cells[4+i,1+j]="'"+myData[i,j];                   }              }              //将列标题和实际内容选中              Excel.Workbook myBook=myExcel.Workbooks[1];              Excel.Worksheet mySheet=(Excel.Worksheet)myBook.Worksheets[1];              Excel.Range r=mySheet.get_Range(mySheet.Cells[3,1],mySheet.Cells[14,7]);              r.Select();              //=====通过执行宏来格表格加边框=======//              try              {                   myExcel.Run("宏1",missing,missing,                         missing,missing,missing,missing,missing,missing,missing,                         missing,missing,missing,missing,missing,missing,missing,                         missing,missing,missing,missing,missing,missing,missing,                         missing,missing,missing,missing,missing,missing,missing);               }              catch              {              }              //保存修改              myBook.Save();
         }     }//end of form
}在上述代码中，我们指定了选定范围：Excel.Range r=mySheet.get_Range(mySheet.Cells[3,1],mySheet.Cells[14,7]);具体开发时，我们可以根据数据库中的实际数据来计算范围，我们的列标题是从.Cells[3,1]开始的，在程序中定死了，为灵活使用，我们完全可以在模板的Cells[1,1]或者其他单元格填入一些基本信息，如实际数据起始位置等等，操作时，从该单元格读入数据，然后将该单元格内容替换成需要的内容。还有个问题，我们往单元格中写内容时假设某列应该放什么内容，为灵活起见，我们在得到了列标题起始位置后，读入该单元格内容（即该列应该是什么字段），再从数据库中找到相应的字段来填充该列，可以保证所填内容与设计的报表对应起来，还可以忽略数据库中无用的字段，也就是说同一个数据库表可以有许多种报表，只要有相应的模板就可以了，读入某单元格内容的代码如下：      Excel.Range r;
     r=mySheet.get_Range(mySheet.Cells[2,1],mySheet.Cells[2,1]);  //取得值存放的区域
     string strValue=r.Value.ToString();一次只能读一个单元格，否则得不到相应的数据，即=mySheet.get_Range(mySheet.Cells[2,1],mySheet.Cells[2,1])中两个参数都必须是同一个单元格，本例中为mySheet.Cells[2,1]。

Answer 3

同意 lijun84(李俊俊)的说法。