.net 操作EXCEL

原创 2006年05月24日 16:32:00

法一:

参照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;
    }
   }
  }

  // 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);
    
  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 ( ) ) ;
}
}

版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

告别ASP.NET操作EXCEL的烦恼

ASP.NET操作EXCEL代码公元19XX年前,关于EXCEL的操作就如滔滔江水,连绵不绝,真正操作EXCEL我也是从去年下半年开始的,有些比较复杂的年度报表之类的,做起来也有点费力,不过还是都能画...

asp.net(C#)之NPOI"操作Excel

1.首先到网上下载"NPOI.DLL",引用。 2.新建一个操作类“ExcelHelper.cs”: using System.Collections.Generic; using System....

ASP.NET服务端不装Office 操作Excel NPOI 1-2

作者:Tony Qu NPOI QQ交流群:  群1:78142590 (满) 群2:124527967 群3: 116053476(新) 更新 2009.3.24  把npoi 1....

使用开源免费类库在.net中操作Excel

自从上次找到NPOI之后,根据园友提供的线索以及Google,又找到了一些开源免费的类库,所以都简单体验了一遍。 主要找到以下类库: MyXls(http://sourceforge.net/...

asp.net 操作excel

这几天做向Excel插入数据,其中有插入图片的需求,经试验,下面2种方法都可以插入图片,但各有不同的用处。现将这2种方法共享出来, 希望需要的朋友进行参考,代码中已经有详细注释了。 注意:使用之前需要...

.NET 中 操作excel 系列--导入与导出.

在 开发中很多时候都要用导入与导出EXCEL 的功能.这两天一直做一些有关这方面 的东西.其中用到很多的导入,导出功,在GOOGLE ,BAIDU 里面搜索了一下,DEMO还真不少.一抓一大把. 其...

ASP.NET操作EXCEL的总结篇

http://www.jb51.net/article/26273.htm 今年有个系统的部分EXCEL的操作也让我做,顺便结合之前操作EXCEL的经验作一下总结,可能也算不上什么,对于绝大多数来...

.net操作Excel

Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();...
  • zl983
  • zl983
  • 2015-03-20 16:38
  • 122

.NET 中 操作excel 系列--导入与导出.

.NET 中 操作excel 系列--导入与导出. 在 开发中很多时候都要用导入与导出EXCEL 的功能.这两天一直做一些有关这方面 的东西.其中用到很多的导入,导出功,在GOOGLE ,BAI...

告别ASP.NET操作EXCEL的烦恼(总结篇)

公元19XX年前,关于EXCEL的操作就如滔滔江水,连绵不绝,真正操作EXCEL我也是从去年下半年开始的,有些比较复杂的年度报表之类的,做起来也有点费力,不过还是都能画出来了,关于EXCEL的报表导出...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)