法一:
参照C:\Program Files\Microsoft Visual Studio .NET 2003\SDK\v1.1\Samples\Technologies\Interop\Applications\Office\Excel\cs
using System;
using System.Reflection; // For Missing.Value and BindingFlags
using System.Runtime.InteropServices; // For COMException
using Excel;
class AutoExcel {
public static int Main() {
Console.WriteLine ("Creating new Excel.Application");
Application app = new Application();
if (app == null) {
Console.WriteLine("ERROR: EXCEL couldn't be started!");
return 0;
}
Console.WriteLine ("Making application visible");
app.Visible = true;
Console.WriteLine ("Getting the workbooks collection");
Workbooks workbooks = app.Workbooks;
Console.WriteLine ("Adding a new workbook");
// The following line is the temporary workaround for the LCID problem
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Console.WriteLine ("Getting the worksheets collection");
Sheets sheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet) sheets.get_Item(1);
if (worksheet == null) {
Console.WriteLine ("ERROR: worksheet == null");
}
Console.WriteLine ("Setting the value for cell");
// This paragraph puts the value 5 to the cell G1
Range range1 = worksheet.get_Range("G1", Missing.Value);
if (range1 == null) {
Console.WriteLine ("ERROR: range == null");
}
const int nCells = 5;
range1.Value2 = nCells;
// This paragraph sends single dimension array to Excel
Range range2 = worksheet.get_Range("A1", "E1");
int[] array2 = new int [nCells];
for (int i=0; i < array2.GetLength(0); i++) {
array2[i] = i+1;
}
range2.Value2 = array2;
// This paragraph sends two dimension array to Excel
Range range3 = worksheet.get_Range("A2", "E3");
int[,] array3 = new int [2, nCells];
for (int i=0; i < array3.GetLength(0); i++) {
for (int j=0; j < array3.GetLength(1); j++) {
array3[i, j] = i*10 + j;
}
}
range3.Value2 = array3;
// This paragraph reads two dimension array from Excel
Range range4 = worksheet.get_Range("A2", "E3");
Object[,] array4;
array4 = (Object[,])range4.Value2;
for (int i=array4.GetLowerBound(0); i <= array4.GetUpperBound(0); i++) {
for (int j=array4.GetLowerBound(1); j <= array4.GetUpperBound(1); j++) {
if ((double)array4[i, j] != array3[i-1, j-1]) {
Console.WriteLine ("ERROR: Comparison FAILED!");
return 0;
}
}
}
//重命名sheet的名字
// m_objSheet.Name = "逄瑞锋";
//重命名sheet的名字
m_objSheet.Name = "逄瑞锋";
//新增加一个sheet
m_objBook.Worksheets.Add(miss, miss, miss, miss);
//Sheet之间切换
m_objSheet =(Excel .Worksheet) m_objBook.Worksheets.get_Item
// This paragraph fills two dimension array with points for two curves and sends it to Excel
Range range5 = worksheet.get_Range("A5", "J6");
double[,] array5 = new double[2, 10];
for (int j=0; j < array5.GetLength(1); j++) {
double arg = Math.PI/array5.GetLength(1) * j;
array5[0, j] = Math.Sin(arg);
array5[1, j] = Math.Cos(arg);
}
range5.Value2 = array5;
// The following code draws the chart
range5.Select();
ChartObjects chartobjects = (ChartObjects) worksheet.ChartObjects(Missing.Value);
ChartObject chartobject = (ChartObject) chartobjects.Add(10 /*Left*/, 100 /*Top*/, 450 /*Width*/, 250 /*Height*/);
_Chart chart = (_Chart) chartobject.Chart;
// Call to chart.ChartWizard() is shown using late binding technique solely for the demonstration purposes
Object[] args7 = new Object[11];
args7[0] = range5; // Source
args7[1] = XlChartType.xl3DColumn; // Gallery
args7[2] = Missing.Value; // Format
args7[3] = XlRowCol.xlRows; // PlotBy
args7[4] = 0; // CategoryLabels
args7[5] = 0; // SeriesLabels
args7[6] = true; // HasLegend
args7[7] = "Sample Chart"; // Title
args7[8] = "Sample Category Type"; // CategoryTitle
args7[9] = "Sample Value Type"; // ValueTitle
args7[10] = Missing.Value; // ExtraTitle
chart.GetType().InvokeMember("ChartWizard", BindingFlags.InvokeMethod, null, chart, args7);
/*
ChartWizard(Source, Gallery, Format, P1otBy, CategoryLabels,
SeriesLabels, HasLegend, Title, CategoryTitle, ValueTitle, ExtraTitle)
其中：
Source：包含新图表的源数据的区域。如省略，将修改活动图表工作表或活动工作表中处于选定状态的嵌人式图表。
Gallery：图表类型。其值可为下列常量之一：xlArea, x1Bar, xlColumn, xlLine, x1Pie, xlRadar,x1XYScatter, xlCombination, x13DArea, x13DBar、x13DColumn, x13DLine, x13DPie、x13 DSurface、xlDoughnut或xlDefaultAutoFormat。
Format：内置自动套用格式的编号。如省略，将选择默认值。
P1otBy：指定系列中的数据是来自行(xlRows)还是列(xlColumns)。
CategoryLabels：表示包含分类标志的源区域内行数或列数的整数。
SeriesLabels：表示包含系列标志的源区域内行数或列数的整数。
HasLegend：若指定True，则图表将具有图例。
Title：图表标题文字。
CategoryTitle：分类轴标题文字。
ValueTitle：数值轴标题文字。
ExtraTitle：三维图表的系列轴标题，或二维图表的第二数值轴标题
http://www.cnblogs.com/pincelee/archive/2006/05/09/394684.html
Excel中设置基本柱形图格式）
chart.Legend.Delete(); 删除系列名称
chart.ChartArea.Interior.ColorIndex = 1; 设置chartArea 颜色
chart.PlotArea.Interior.ColorIndex = 40; 设置 PlotArea颜色
Axis col= (Axis) chart.Axes(XlAxisType .xlValue, XlAxisGroup.xlPrimary );
col.TickLabels.Font.Size= 8; 改变坐标轴字体大小
Axis row = (Axis)chart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
row.TickLabels.Font.Size = 8;
*/
Console.WriteLine ("Press ENTER to finish the sample:");
Console.ReadLine();
try {
// If user interacted with Excel it will not close when the app object is destroyed, so we close it explicitely
workbook.Saved = true;
app.UserControl = false;
app.Quit();
} catch (COMException) {
Console.WriteLine ("User closed Excel manually, so we don't have to do that");
}
Console.WriteLine ("Sample successfully finished!");
return 100;
}
}
法二：像操作数据库一样操作
using System ;
using System.Drawing ;
using System.Collections ;
using System.ComponentModel ;
using System.Windows.Forms ;
using System.Data ;
using System.Data.OleDb ;
public class Form1 : Form
{
private Button button1 ;
private System.Data.DataSet myDataSet ;
private DataGrid DataGrid1 ;
private System.ComponentModel.Container components = null ;
public Form1 ( )
{
file://初始化窗体中的各个组件
InitializeComponent ( ) ;
file://打开数据链接，得到数据集
GetConnect ( ) ;
}
file://清除程序中使用过的资源
protected override void Dispose ( bool disposing )
{
if ( disposing )
{
if ( components != null )
{
components.Dispose ( ) ;
}
}
base.Dispose ( disposing ) ;
}
private void GetConnect ( )
{
file://创建一个数据链接
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = c:\\sample.xls;Extended Properties=Excel 8.0" ;
OleDbConnection myConn = new OleDbConnection ( strCon ) ;
string strCom = " SELECT * FROM [Sheet1$] " ;
myConn.Open ( ) ;
file://打开数据链接，得到一个数据集
OleDbDataAdapter myCommand = new OleDbDataAdapter ( strCom , myConn ) ;
file://创建一个 DataSet对象
myDataSet = new DataSet ( ) ;
file://得到自己的DataSet对象
myCommand.Fill ( myDataSet , "[Sheet1$]" ) ;
file://关闭此数据链接
myConn.Close ( ) ;
}
private void InitializeComponent ( )
{
DataGrid1 = new DataGrid ( ) ;
button1 = new Button ( ) ;
SuspendLayout ( ) ;
DataGrid1.Name = "DataGrid1";
DataGrid1.Size = new System.Drawing.Size ( 400 , 200 ) ;
button1.Location = new System.Drawing.Point ( 124 , 240 ) ;
button1.Name = "button1" ;
button1.TabIndex = 1 ;
button1.Text = "读取数据" ;
button1.Size = new System.Drawing.Size (84 , 24 ) ;
button1.Click += new System.EventHandler ( this.button1_Click ) ;
this.AutoScaleBaseSize = new System.Drawing.Size ( 6 , 14 ) ;
this.ClientSize = new System.Drawing.Size ( 400 , 280 ) ;
this.Controls.Add ( button1 ) ;
this.Controls.Add ( DataGrid1 ) ;
this.Name = "Form1" ;
this.Text = "读取Excle表格中的数据，并用DataGrid显示出来！" ;
this.ResumeLayout ( false ) ;
}
private void button1_Click ( object sender , System.EventArgs e )
{
DataGrid1.DataMember= "[Sheet1$]" ;
DataGrid1.DataSource = myDataSet ;
}
static void Main ( )
{
Application.Run ( new Form1 ( ) ) ;
}
}