参照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 BindingFlagsusing System.Runtime.InteropServices; // For COMExceptionusing 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; } } }
// 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 ( ) ) ;}}
