C#操作excel sheet

 C#操作excel sheet
这里有个VB.NET利用数组的例子，自己改改好了
.NET下 从 DataView  DataSet  DataTable 导出数据至Excel
   Public Sub Exports2Excel(ByVal Dtg As DataGrid)
        If Dtg.VisibleRowCount > 0 Then
            Try
                Me.Cursor = Cursors.WaitCursor
                Dim datav As New DataView
                If TypeOf Dtg.DataSource Is DataView Then
                    datav = CType(Dtg.DataSource, DataView)
                ElseIf TypeOf Dtg.DataSource Is DataSet Then
                    datav = CType(Dtg.DataSource, DataSet).Tables(0).DefaultView
                ElseIf TypeOf Dtg.DataSource Is DataTable Then
                    datav = CType(Dtg.DataSource, DataTable).DefaultView
                End If
                Dim i, j As Integer
                Dim rows As Integer = datav.Table.Rows.Count
                Dim cols As Integer = datav.Table.Columns.Count
                Dim DataArray(rows - 1, cols - 1) As String
                Dim myExcel As Excel.Application = New Excel.Application
                For i = 0 To rows - 1
                    For j = 0 To cols - 1
                        DataArray(i, j) = datav.Table.Rows(i).Item(j)
                    Next
                Next
                myExcel.Application.Workbooks.Add(True)
                myExcel.Visible = True
                For j = 0 To cols - 1
                    myExcel.Cells(1, j + 1) = datav.Table.Columns(j).ColumnName
                Next
                myExcel.Range("A2").Resize(rows, cols).Value = DataArray
            Catch exp As Exception
                MessageBox.Show("数据导出失败!请查看是否已经安装了Excel", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Warning)
            Finally
                Me.Cursor = Cursors.Default
            End Try
        Else
            MessageBox.Show("没有数据!", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If
    End Sub
--------------------------------------------------------------------------------
帮顶！
--------------------------------------------------------------------------------
谢谢longdr(龙卷风)!这是我在ASP.NET下写的一个程序想转成WINFROM,偶对这个不熟,现找到一段源码:Excel.Application excel ;
excel = new Excel.ApplicationClass();
Excel.WorkbookClass oWB;
Excel.Worksheet oSheet;
oWB = (Excel.WorkbookClass)(excel.Workbooks.Add(true));
oSheet = (Excel.Worksheet)oWB.ActiveSheet;
oWB.Close(false,path,null); 
excel.Workbooks.Close();  
excel.Quit(); 


System.Runtime.InteropServices.Marshal.ReleaseComObject (excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oWB);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oSheet);
oSheet = null;
oWB= null;
excel = null;
运行说缺using指令,请问需加载什么指令空间,并:这段代码能实现吗?
--------------------------------------------------------------------------------
Response.Clear();
Response.Buffer= true;
Response.Charset="GB2312";   
Response.AppendHeader("Content-Disposition","attachment;filename=FileName.xls");
Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
this.EnableViewState = false;   
System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN",true);
System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
this.DataGrid1.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
Response.End();
--------------------------------------------------------------------------------
工程需引用MS-EXCEL
--------------------------------------------------------------------------------
asxulong(假如再有约会) ,这是ASP.NET的代码吧,偶想要在WINFORM下的,谢了!!
--------------------------------------------------------------------------------
longdr(龙卷风),怎么写?using MS-EXCEL?出错,提示"-"处应输入";".
--------------------------------------------------------------------------------
HttpResponse res = HttpContext.Current.Response;
res.Clear();
res.ContentType = "application/vnd.ms-excel";
res.Charset = "";
res.ContentEncoding= System.Text.Encoding.UTF8;
System.IO.StringWriter tw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
this.mytd2.RenderControl(hw);
string httpHeader="attachment;filename=report.Xls";
res.AppendHeader("Content-Disposition", httpHeader);
res.Write(tw.ToString());
res.End();
--------------------------------------------------------------------------------
呵呵，首先在工程引用微软的Excel库，如9.0或者10.0等
如果运行报错的话，可以考虑在源程序上方书写
using Excel=Microsoft.Office.Excel;
试一试，刚才那个导出程序在9.0是能跑的。
--------------------------------------------------------------------------------
longdr(龙卷风),现在可以运行了,但点击Button后没有生成EXCEL文档,没什么反应,以下是源码,看看是哪出错了,非常感谢!
private void Form1_Load(object sender, System.EventArgs e)
{
string oleinfo="Provider=Microsoft.Jet.OleDb.4.0; data source=D:\\项目备份 \\ok\\database\\ok.xls;Extended Properties=Excel 8.0;";
string strsel="SELECT 经理,[1$].站号,脂肪,蛋白,干物质,酸度,煮后酸度,温度,细菌,搀假,搀假备注,感官,感官备注,酒精试验,酒精试验备注,煮沸试验,判定,收奶,收奶量 FROM [1$] inner join [2$] on [1$].站号 like '%'+[2$].站号+'%' where 收奶='拒收'order by 经理";
//string strsel="SELECT * FROM [1$]";
OleDbConnection Myconn=new OleDbConnection(oleinfo);
Myconn.Open();
OleDbDataAdapter Mycomm = new OleDbDataAdapter (strsel,Myconn) ;
DataSet ds=new DataSet();
Mycomm.Fill(ds);
Myconn.Close();
dataGrid1.DataMember= "[1$]" ;
dataGrid1.DataSource =ds;
}
private void button1_Click(object sender, System.EventArgs e)
{
Excel.Application excel ;
excel = new Excel.ApplicationClass();
Excel.WorkbookClass oWB;
Excel.Worksheet oSheet;
oWB = (Excel.WorkbookClass)(excel.Workbooks.Add(true));
oSheet = (Excel.Worksheet)oWB.ActiveSheet;
oWB.Close(false,"ok",null); 
excel.Workbooks.Close();  
excel.Quit(); 


System.Runtime.InteropServices.Marshal.ReleaseComObject (excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oWB);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oSheet);
oSheet = null;
oWB= null;
excel = null;
}
--------------------------------------------------------------------------------
oWB.Close(false,"ok",null);  是什么意思?
找到的源码是oWB.Close(false,path,null);我看path处该是表名,所以改了一下,对吗?
--------------------------------------------------------------------------------
高手快来救命啊!!自己顶!!
--------------------------------------------------------------------------------
呵呵，你都没有把数据导出到Excel去，
那又怎么会有反应呢？
--------------------------------------------------------------------------------
//datagrid内容到excel
private void DatagridToExcel(DataGrid grid, Excel.Worksheet sheet)
{
DataTable table = (DataTable)grid.DataSource;
int sheetRow = 1;
//导出列头
for(int col = 0; col < table.Columns.Count; col++)
{
sheet.Cells[sheetRow, col + 1] = table.Columns[col].ColumnName;
}
sheetRow++;
//导出内容
for(int row = 0; row < table.Rows.Count; row++)
{
for(int col = 0; col < table.Columns.Count; col++)
{
sheet.Cells[sheetRow, col + 1] = table.Rows[row][col];
}
sheetRow++;
}
}

--------------------------------------------------------------------------------
我也写了一个，自己试过了可以的
在mytable类中写
public void Binding(string sql,DataGrid dg)
{
DataSet ds = new DataSet();
//OleDbConnection MyConnection = new OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =" + System.Web.HttpContext.Current.Server.MapPath(".")+"/test.mdb");
OleDbConnection MyConnection = new OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =" + "VB.NET (F:):\et1\\lianxi\\test.mdb");
OleDbDataAdapter MyCommand = new OleDbDataAdapter(sql,MyConnection);
MyCommand.Fill(ds,"ta");
dg.DataSource = ds.Tables["ta"].DefaultView;
dg.DataBind();
}
然后private void Page_Load(object sender, System.EventArgs e)
{
if(!IsPostBack)
{
MkTable mt = new MkTable();
mt.Binding("Select * from login",MyDataGrid);
}
// ページを初期化するユーザー コードをここに挿入します。
}
private void button1_Click(object sender, System.EventArgs e)
{
MyDataGrid.AllowPaging = false;
MkTable mt = new MkTable();
mt.Binding("Select * from login",MyDataGrid);
//      MyDataGrid.SelectedItemStyle.BackColor=Color.white
//      MyDataGrid.AlternatingItemStyle.BackColor=Color.white
//      MyDataGrid.ItemStyle.BackColor=Color.white
//  MyDataGrid.HeaderStyle.BackColor=Color.white
//  MyDataGrid.HeaderStyle.ForeColor=Color.red

HttpResponse resp = Page.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
//Me.EnableViewState = false;
System.IO.StringWriter tw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
MyDataGrid.RenderControl(hw);
Response.Write(tw.ToString());
Response.End();
MyDataGrid.AllowPaging = true;
mt.Binding("Select * from login",MyDataGrid);
}
OK了

--------------------------------------------------------------------------------
http://www.mscenter.edu.cn/blog/dragon1982/archive/2005/01/25/766.aspx
--------------------------------------------------------------------------------
生成表格,然后将其保存了Excle文件
//生成报表
string contstr1="<html><head><meta http-equiv=Content-Type content=text/html; charset=utf-8><title>定期统计报告</title></head><body& gt;";
contstr1 += "<p align=center><strong>定 期 统 计 报 告</strong></p>";
string contstr="<table width=600 border=1 align=center cellspacing=0 bordercolor=#000000>";
contstr += "<tr>";
contstr += "<td>工号</td>";
contstr += "<td>姓名</td>";
contstr += "<td>部门</td>";
contstr += "<td>"+getyymmddfang(enddatestr)+"日均</td>";
if (bjj=="1")
{
contstr += "<td>"+getyymmddfang(enddatestr2)+"日均</td>";
contstr += "<td>日均差</td>";
}
contstr += "</tr>";
string filename="temp.htm";//
string strInsert7 = "select * from gonghaobao";
if(pxxx=="1")
{
if(bjj=="0")
{
strInsert7 +=" order by first_money desc,bm asc";
}
else
{
strInsert7 +=" order by zengzhang_money desc,first_money desc,bm asc";
}
}
else if(pxxx=="3")
{
if(bjj=="0")
{
strInsert7 +=" order by bm desc,first_money desc";
}
else
{
strInsert7 +=" order by bm desc,zengzhang_money desc";
}
}
else
{
strInsert7 +=" order by gonghao asc";
}
myConn.Open ( ) ;

if(myConn.State==System.Data.ConnectionState.Open)//判断myConn是否打开
{
//int gonghaoint;
OleDbCommand  mycmd998=new OleDbCommand (strInsert7,myConn);
OleDbDataReader sdr5566=mycmd998.ExecuteReader();
//第一层循环,按照工号进行读取
while(sdr5566.Read())
{
//gonghaostr=sdr["gonghao"].ToString();
contstr += "<tr>";
contstr += "<td>"+sdr5566["gonghao"].ToString()+"</td>";
contstr += "<td>"+sdr5566["zgname"].ToString()+"</td>";
contstr += "<td>"+sdr5566["bm"].ToString()+"</td>";
contstr += "<td>"+sdr5566["first_money"].ToString()+"</td>";
if (bjj=="1")
{
contstr += "<td>"+sdr5566["end_money"].ToString()+"</td>";
contstr += "<td>"+sdr5566["zengzhang_money"].ToString()+"</td>";
}
contstr += "</tr>";
}
//统计
//查询语句 
mycmd998.Dispose();
mycmd998=null;
}
contstr1=contstr1 + contstr;
contstr1 += "</table>";
if(bjj=="1")
{
contstr1 += "<p align=center>备注:统计是:从"+getyymmddfang(stardatestr)+" 起"+getyymmddfang(enddatestr)+"与"+getyymmddfang(enddatestr2)+"的之间的日均</p>";
}
else
{
contstr1 += "<p align=center>备注:统计是:从"+getyymmddfang(stardatestr)+"起计算"+getyymmddfang(enddatestr)+"的日均</p>";
}
contstr1 += "<div align=center><SCRIPT LANGUAGE=JavaScript>";
contstr1 += "if (window.print) {document.write('<form>'+ '<input type=button name=print value=打印 '";
contstr1 +="+ 'onClick=javascript:window.print()></form>');}</script></div>";
contstr1 += "</body></html>";
makehtm(contstr,"temp.xls");//生成Excel
其中makehtm函数为:
private void makehtm(string contenstr,string filename)
{
string fisrtstr=Application.StartupPath+"//temp//";
using (StreamWriter sw = new StreamWriter(fisrtstr+filename))
{
// Add some text to the file.
sw.Write(contenstr);
}
}
这种方法是对简单的报表就合适.
--------------------------------------------------------------------------------
lldwolf(铁背苍狼),你的程序我大概看懂了，只是在调用时
private void DatagridToExcel(DataGrid grid, Excel.Worksheet sheet)
第二个参数怎么写，我想新开一个ＥＸＣＥＬ文档。
--------------------------------------------------------------------------------
using System;
using System.Data;
using Excel;
using System.Reflection;
using System.Runtime.InteropServices;
using System.IO;
using System.Web;
using System.Diagnostics;
namespace HLDXS.NET.Comm
{
/// <summary>
/// excel_down 的摘要说明。
/// </summary>
public class excel_down:System.IDisposable
{
DataSet Excel_DS;
public excel_down(DataSet Excel_DS)
{
this.Excel_DS=Excel_DS;
}
public void Export_Excel(System.Web.UI.Page excel)
{
Excel.Application MyApp;
if(this.Excel_DS.Tables.Count<1)
{
return;
}
int table_count=this.Excel_DS.Tables.Count;
object oMissiong = System.Reflection.Missing.Value;
MyApp=new Excel.ApplicationClass();
Workbooks workbooks = MyApp.Workbooks;
_Workbook MyBook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Excel.Sheets mysheet=null;
mysheet=MyApp.Worksheets;
for(int i=0;i<table_count;i++)
{
Excel._Worksheet MySheet1=new Excel.WorksheetClass();
mysheet.Add(oMissiong,oMissiong,1,oMissiong);
}
int Columns=1;
Excel._Worksheet MySheets=null;
for(int i=0;i<table_count;i++)
{
MySheets=(Excel._Worksheet)MyBook.Worksheets.get_Item(i+1);
MySheets.Name=this.Excel_DS.Tables[i].TableName;
MySheets.Cells[1,Columns]=this.Excel_DS.Tables[i].TableName;
for(int k=0;k<this.Excel_DS.Tables[i].Columns.Count;k++)
{
MySheets.Cells[2,Columns+k]=this.Excel_DS.Tables[i].Columns[k].Caption;
for(int j=0;j<this.Excel_DS.Tables[i].Rows.Count;j++)
{
MySheets.Cells[j+3,Columns+k]=this.Excel_DS.Tables[i].Rows[j][k];
}            
}
MySheets.get_Range(MySheets.Cells[1,Columns],MySheets.Cells[1,Columns+Excel_DS.Tables[i].Columns.Count-1]).Merge(0);
MySheets.get_Range(MySheets.Cells[1,Columns],MySheets.Cells[1,Columns+Excel_DS.Tables[i].Columns.Count-1]).Font.Size=15;
MySheets.get_Range(MySheets.Cells[1,Columns],MySheets.Cells[1,Columns+Excel_DS.Tables[i].Columns.Count-1]).Font.Bold=true;
MySheets.get_Range(MySheets.Cells[1,Columns],MySheets.Cells[1,Columns+Excel_DS.Tables[i].Columns.Count-1]).RowHeight=24;
MySheets.get_Range(MySheets.Cells[1,Columns],MySheets.Cells[1,Columns+Excel_DS.Tables[i].Columns.Count]).Borders.Weight=(OWC.LineWeightEnum.owcLineWeightThin);
MySheets.get_Range(MySheets.Cells[2,Columns],MySheets.Cells[2,Columns+Excel_DS.Tables[i].Columns.Count]).ColumnWidth=10;
}
//删除第一个sheet
int tt=MyApp.Worksheets.Count;
MySheets=new Excel.WorksheetClass();
MySheets=(Excel._Worksheet)MyBook.Worksheets.get_Item(tt);
MySheets.Delete();
MySheets=null;
//
string filename=excel.MapPath("")+"/"+this.Excel_DS.DataSetName+".xls";
MySheets=new Excel.WorksheetClass();
MySheets=(Excel._Worksheet)MyBook.Worksheets.get_Item(1);
MyApp.Visible=false;
MySheets.SaveAs(filename,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong);
MyApp.Workbooks.Close();
MyApp.Quit();
MyApp=null;
GC.Collect();
HttpResponse resp;
resp =excel.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
string DownFileName=this.Excel_DS.DataSetName+".xls";
resp.AppendHeader("Content-Disposition", "attachment;filename=" +DownFileName);
FileInfo MyFileInfo;
long StartPos = 0, FileSize;
MyFileInfo = new FileInfo(filename);
FileSize = MyFileInfo.Length;
resp.WriteFile(filename, StartPos, FileSize);
resp.Flush();
MyFileInfo.Delete();
excel.Response.End();
}
public void Dispose()
{
this.Excel_DS=null;
}
}
}
完整的一个 dataset 导出一个excel 有几个表就导出几个sheet
--------------------------------------------------------------------------------
我是这样调用的，点Ｂutton后报错：＂指定转换失败，错误行：
DataTable table = (DataTable)grid.DataSource;
这样调用对吗？该怎么改？
>>>>>>>>>>>>>>>>>>private void button1_Click(object sender, System.EventArgs e)
{
Excel.Application excel ;
excel = new Excel.ApplicationClass();
Excel.WorkbookClass oWB;
Excel.Worksheet oSheet;
oWB = (Excel.WorkbookClass)(excel.Workbooks.Add(true));
oSheet = (Excel.Worksheet)oWB.ActiveSheet;
oWB.Close(false,"D:\\项目备份 \\ok\\database\\ok.xls",null ); 
excel.Workbooks.Close();  
excel.Quit(); 


System.Runtime.InteropServices.Marshal.ReleaseComObject (excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oWB);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oSheet);
oSheet = null;
oWB= null;
excel = null;
DatagridToExcel(dataGrid1,oSheet);
}
private void DatagridToExcel(DataGrid grid, Excel.Worksheet sheet)
{
DataTable table = (DataTable)grid.DataSource;
int sheetRow = 1;
//导出列头
for(int col = 0; col < table.Columns.Count; col++)
{
sheet.Cells[sheetRow, col + 1] = table.Columns[col].ColumnName;
}
sheetRow++;
//导出内容
for(int row = 0; row < table.Rows.Count; row++)
{
for(int col = 0; col < table.Columns.Count; col++)
{
sheet.Cells[sheetRow, col + 1] = table.Rows[row][col];
}
sheetRow++;
}
}
--------------------------------------------------------------------------------
忘了说两句上面的最好用于web
--------------------------------------------------------------------------------
忘了说两句上面的最好用于web
--------------------------------------------------------------------------------
slon3dmax(slon3dmax)， hky5_com(绿源人) ，非常感谢，但很不幸，我在ＷＥＢ下以实现，这个正是ＷＥＢ换ＷＩＮＦＯＲＭ！唉～～
 xjaifly(tiantian) ，非常感谢你提供的资料，已收藏，只是没找到这个要用的．


 从excel中导出数据
 C# Code
  ---------------------------------------------
  <%@ Page Language="C#" %>
  <%@ Import Namespace="System.Data" %>
  <%@ Import Namespace="System.Data.ADO" %>
  <script language="C#" runat="server">
  protected void Page_Load(Object Src, EventArgs E)
  {
  string strConn;
  strConn = "Provider=Microsoft.Jet.OLEDB.4.0;"
  +"Data Source=C:\\exceltest.xls;"
  +"Extended Properties=Excel 8.0;";
  ADODataSetCommand myCommand = new ADODataSetCommand("SELECT * FROM [Sheet1$]", strConn);
  DataSet myDataSet = new DataSet();
  myCommand.FillDataSet(myDataSet, "ExcelInfo");
  DataGrid1.DataSource = myDataSet.Tables["ExcelInfo"].DefaultView;
  DataGrid1.DataBind();
  }
  </script>
  <p><asp:Label id=Label1 runat="server">Excel表格内容:</asp:Label></p>
  <asp:DataGrid id=DataGrid1 runat="server"/>

posted @ 2006-05-18 10:21 破茧化蝶 阅读(8) | 评论 (0) | 编辑 收藏

如何用c＃代码操作excel
通过自动化过程，使用诸如 Visual C# .NET 这样的语言编写的应用程序就可以用编程方式来控制其他应用程序。利用 Excel 的自动化功能，您可以执行诸如新建工作簿、向工作簿添加数据或创建图表等操作。对于 Excel 和其他 Microsoft Office 应用程序，几乎所有可以通过用户界面手动执行的操作也都可以通过使用“自动化”功能以编程方式来执行。

Excel 通过一种对象模型来公开这一程序功能。该对象模型是一些类和方法的集合，这些类和方法充当 Excel 的逻辑组件。例如，有 Application 对象、Workbook 对象和 Worksheet 对象，其中每一种对象都包含 Excel 中那些组件的功能。要从 Visual C# .NET 访问该对象模型，可以设置对类型库的项目引用。

本文将阐述如何为 Visual C# .NET 设置对 Excel 类型库的适当项目引用，并提供使 Excel 自动运行的代码示例。
为 Microsoft Excel 创建自动化客户端
1. 启动 Microsoft Visual Studio .NET。
2. 在文件菜单上，单击新建，然后单击项目。从 Visual C# 项目类型中选择 Windows 应用程序。Form1 是默认创建的窗体。
3. 添加对 Microsoft Excel 对象库的引用。为此，请按照下列步骤操作： a.  在项目菜单上，单击添加引用。
b.  在 COM 选项卡上，找到 Microsoft Excel 对象库，然后单击选择。

注意：Microsoft Office 2003 包含主 Interop 程序集 (PIA)。Microsoft Office XP 不包含 PIA，但您可以下载 PIA。 有关 Office XP PIA 的更多信息，请单击下面的文章编号，以查看 Microsoft 知识库中相应的文章：
328912 ( http://support.microsoft.com/kb/328912/ ) Microsoft Office XP 主 interop 程序集 (PIA) 可供下载 
c.  在添加引用对话框中单击确定以接受您的选择。如果系统提示您为选定的库生成包装，请单击是。

4. 在视图菜单上，选择工具箱以显示工具箱，然后向 Form1 添加一个按钮。
5. 双击 Button1。出现该窗体的代码窗口。
6. 在代码窗口中，将以下代码
private void button1_Click(object sender, System.EventArgs e)
            {
            }

替换为：
private void button1_Click(object sender, System.EventArgs e)
            {
            Excel.Application oXL;
            Excel._Workbook oWB;
            Excel._Worksheet oSheet;
            Excel.Range oRng;
            try
            {
            //Start Excel and get Application object.
            oXL = new Excel.Application();
            oXL.Visible = true;
            //Get a new workbook.
            oWB = (Excel._Workbook)(oXL.Workbooks.Add( Missing.Value ));
            oSheet = (Excel._Worksheet)oWB.ActiveSheet;
            //Add table headers going cell by cell.
            oSheet.Cells[1, 1] = "First Name";
            oSheet.Cells[1, 2] = "Last Name";
            oSheet.Cells[1, 3] = "Full Name";
            oSheet.Cells[1, 4] = "Salary";
            //Format A1:D1 as bold, vertical alignment = center.
            oSheet.get_Range("A1", "D1").Font.Bold = true;
            oSheet.get_Range("A1", "D1").VerticalAlignment =
            Excel.XlVAlign.xlVAlignCenter;
            // Create an array to multiple values at once.
            string[,] saNames = new string[5,2];
            saNames[ 0, 0] = "John";
            saNames[ 0, 1] = "Smith";
            saNames[ 1, 0] = "Tom";
            saNames[ 1, 1] = "Brown";
            saNames[ 2, 0] = "Sue";
            saNames[ 2, 1] = "Thomas";
            saNames[ 3, 0] = "Jane";
            saNames[ 3, 1] = "Jones";
            saNames[ 4, 0] = "Adam";
            saNames[ 4, 1] = "Johnson";
            //Fill A2:B6 with an array of values (First and Last Names).
            oSheet.get_Range("A2", "B6").Value2 = saNames;
            //Fill C2:C6 with a relative formula (=A2 & " " & B2).
            oRng = oSheet.get_Range("C2", "C6");
            oRng.Formula = "=A2 & \" \" & B2";
            //Fill D2:D6 with a formula(=RAND()*100000) and apply format.
            oRng = oSheet.get_Range("D2", "D6");
            oRng.Formula = "=RAND()*100000";
            oRng.NumberFormat = "$0.00";
            //AutoFit columns A:D.
            oRng = oSheet.get_Range("A1", "D1");
            oRng.EntireColumn.AutoFit();
            //Manipulate a variable number of columns for Quarterly Sales Data.
            DisplayQuarterlySales(oSheet);
            //Make sure Excel is visible and give the user control
            //of Microsoft Excel's lifetime.
            oXL.Visible = true;
            oXL.UserControl = true;
            }
            catch( Exception theException )
            {
            String errorMessage;
            errorMessage = "Error: ";
            errorMessage = String.Concat( errorMessage, theException.Message );
            errorMessage = String.Concat( errorMessage, " Line: " );
            errorMessage = String.Concat( errorMessage, theException.Source );
            MessageBox.Show( errorMessage, "Error" );
            }
            }
            private void DisplayQuarterlySales(Excel._Worksheet oWS)
            {
            Excel._Workbook oWB;
            Excel.Series oSeries;
            Excel.Range oResizeRange;
            Excel._Chart oChart;
            String sMsg;
            int iNumQtrs;
            //Determine how many quarters to display data for.
            for( iNumQtrs = 4; iNumQtrs >= 2; iNumQtrs--)
            {
            sMsg = "Enter sales data for ";
            sMsg = String.Concat( sMsg, iNumQtrs );
            sMsg = String.Concat( sMsg, " quarter(s)?");
            DialogResult iRet = MessageBox.Show( sMsg, "Quarterly Sales?",
            MessageBoxButtons.YesNo );
            if (iRet == DialogResult.Yes)
            break;
            }
            sMsg = "Displaying data for ";
            sMsg = String.Concat( sMsg, iNumQtrs );
            sMsg = String.Concat( sMsg, " quarter(s)." );
            MessageBox.Show( sMsg, "Quarterly Sales" );
            //Starting at E1, fill headers for the number of columns selected.
            oResizeRange = oWS.get_Range("E1", "E1").get_Resize( Missing.Value, iNumQtrs);
            oResizeRange.Formula = "=\"Q\" & COLUMN()-4 & CHAR(10) & \"Sales\"";
            //Change the Orientation and WrapText properties for the headers.
            oResizeRange.Orientation = 38;
            oResizeRange.WrapText = true;
            //Fill the interior color of the headers.
            oResizeRange.Interior.ColorIndex = 36;
            //Fill the columns with a formula and apply a number format.
            oResizeRange = oWS.get_Range("E2", "E6").get_Resize( Missing.Value, iNumQtrs);
            oResizeRange.Formula = "=RAND()*100";
            oResizeRange.NumberFormat = "$0.00";
            //Apply borders to the Sales data and headers.
            oResizeRange = oWS.get_Range("E1", "E6").get_Resize( Missing.Value, iNumQtrs);
            oResizeRange.Borders.Weight = Excel.XlBorderWeight.xlThin;
            //Add a Totals formula for the sales data and apply a border.
            oResizeRange = oWS.get_Range("E8", "E8").get_Resize( Missing.Value, iNumQtrs);
            oResizeRange.Formula = "=SUM(E2:E6)";
            oResizeRange.Borders.get_Item( Excel.XlBordersIndex.xlEdgeBottom ).LineStyle
            = Excel.XlLineStyle.xlDouble;
            oResizeRange.Borders.get_Item( Excel.XlBordersIndex.xlEdgeBottom ).Weight
            = Excel.XlBorderWeight.xlThick;
            //Add a Chart for the selected data.
            oWB = (Excel._Workbook)oWS.Parent;
            oChart = (Excel._Chart)oWB.Charts.Add( Missing.Value, Missing.Value,
            Missing.Value, Missing.Value );
            //Use the ChartWizard to create a new chart from the selected data.
            oResizeRange = oWS.get_Range("E2:E6", Missing.Value ).get_Resize(
            Missing.Value, iNumQtrs);
            oChart.ChartWizard( oResizeRange, Excel.XlChartType.xl3DColumn, Missing.Value,
            Excel.XlRowCol.xlColumns, Missing.Value, Missing.Value, Missing.Value,
            Missing.Value, Missing.Value, Missing.Value, Missing.Value );
            oSeries = (Excel.Series)oChart.SeriesCollection(1);
            oSeries.XValues = oWS.get_Range("A2", "A6");
            for( int iRet = 1; iRet <= iNumQtrs; iRet++)
            {
            oSeries = (Excel.Series)oChart.SeriesCollection(iRet);
            String seriesName;
            seriesName = "=\"Q";
            seriesName = String.Concat( seriesName, iRet );
            seriesName = String.Concat( seriesName, "\"" );
            oSeries.Name = seriesName;
            }
            oChart.Location( Excel.XlChartLocation.xlLocationAsObject, oWS.Name );
            //Move the chart so as not to cover your data.
            oResizeRange = (Excel.Range)oWS.Rows.get_Item(10, Missing.Value );
            oWS.Shapes.Item("Chart 1").Top = (float)(double)oResizeRange.Top;
            oResizeRange = (Excel.Range)oWS.Columns.get_Item(2, Missing.Value );
            oWS.Shapes.Item("Chart 1").Left = (float)(double)oResizeRange.Left;
            }


7. 滚动到代码窗口的顶部。将下面的代码行添加到 using 指令列表的末尾：
using Excel = Microsoft.Office.Interop.Excel;
            using System.Reflection;



对自动化客户端进行测试
1. 按 F5 生成并运行该程序。
2. 在窗体上，单击 Button1。该程序将启动 Excel 并将数据填充到一个新的工作表中。
3. 在提示您输入季度销售数据时，单击是。一个链接到季度数据的图表就会被添加到工作表中。