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>";
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. 在提示您输入季度销售数据时，单击是。一个链接到季度数据的图表就会被添加到工作表中。