设置OWC/axSpreadsheet控件数据源

最新推荐文章于 2020-03-26 09:35:23 发布

weixin_33847182

最新推荐文章于 2020-03-26 09:35:23 发布

阅读量173

点赞数

原文链接：http://www.cnblogs.com/yycxy/archive/2011/03/03/1969858.html

版权

1：添加组件到控件工具箱

2：添加控件到窗体

3：添加C#代码

   1: using System;

   2: using System.Collections.Generic;

   3: using System.ComponentModel;

   4: using System.Data;

   5: using System.Drawing;

   6: using System.Linq;

   7: using System.Text;

   8: using System.Windows.Forms;

9:

  10: namespace WindowsFormsApplication11

  11: {

  12:     public partial class Form1 : Form

  13:     {

  14:         public Form1()

  15:         {

  16:             InitializeComponent();

  17:         }

  18:         DataTable dt = null;

  19:         private void buttonX1_Click(object sender, EventArgs e)

  20:         {

  21:             ADODB.Connection conn = new ADODB.Connection();

  22:             ADODB.Recordset rs = new ADODB.Recordset();

  23:             string str = @"driver={sql server};server=TEL-236A\ZTXSQL2000;uid=sa;pwd=330443137; database=tbmrp";

  24:             conn.Open(str, "", "", -1);

  25:             rs.ActiveConnection = conn;

  26:             rs.CursorType = ADODB.CursorTypeEnum.adOpenStatic;

  27:             rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient;

28:

  29:             string sql = "select * from pncolor";

  30:             rs.Open(sql, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, 1);

31:

32:

  33:             dt = new DataTable();

  34:            // dt.Columns.Add("a");

  35:            // dt.Rows.Add(dt.NewRow());

  36:             ((Microsoft.Office.Interop.Owc11.Worksheet)this.axSpreadsheet1.Worksheets["Sheet1"]).Range["A1:z65536"].CopyFromRecordset(rs);

  37:            // ((Microsoft.Office.Interop.Owc11.Worksheet)this.axSpreadsheet1.Worksheets[0]).CommandText = "select * from pncolor";

  38:             //((Microsoft.Office.Interop.Owc11.Worksheet)this.axSpreadsheet1.Worksheets[0]).Refresh();

  39:             this.axPivotTable1.DataSource = (msdatasrc.DataSource)rs;

40:

  41:             ((Microsoft.Office.Interop.Owc11.Worksheet)this.axSpreadsheet1.Worksheets["Sheet1"]).Range["A2"].Select();

  42:             this.axSpreadsheet1.ActiveWindow.FreezePanes = true;

  43:         }

44:

  45:         private void buttonX3_Click(object sender, EventArgs e)

  46:         {

  47:             ADODB.Connection conn = new ADODB.Connection();

  48:             ADODB.Recordset rs = new ADODB.Recordset();

  49:             string str = @"driver={sql server};server=TEL-236A\ZTXSQL2000;uid=sa;pwd=330443137; database=tbmrp";

  50:             conn.Open(str, "", "", -1);

  51:             rs.ActiveConnection = conn;

  52:             rs.CursorType = ADODB.CursorTypeEnum.adOpenStatic;

  53:             rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient;

54:

  55:             string sql = "select toP 200 'a' as [一],* from PNCOPTC";

  56:             rs.Open(sql, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, 1);

57:

58:

  59:              dt = new DataTable();

  60:             // dt.Columns.Add("a");

  61:             // dt.Rows.Add(dt.NewRow());

  62:             ((Microsoft.Office.Interop.Owc11.Worksheet)this.axSpreadsheet1.Worksheets["Sheet1"]).Range["A1:z65536"].CopyFromRecordset(rs);

  63:             // ((Microsoft.Office.Interop.Owc11.Worksheet)this.axSpreadsheet1.Worksheets[0]).CommandText = "select * from pncolor";

  64:             //((Microsoft.Office.Interop.Owc11.Worksheet)this.axSpreadsheet1.Worksheets[0]).Refresh();

  65:             this.axPivotTable1.DataSource = (msdatasrc.DataSource)rs;

  66:         }

67:

68:

  69:     }

  70: }

4：测试程序

注：一直没有找到给axSpreadsheet设置数据源的方法，今天做EXCEL VBA开发时起到了CopyFromRecordset方法，一试居然就行了。axPivotTable数据透视表的功能相当强大，只是界面单调了些，还有就是没有直接打印的方法。

转载于:https://www.cnblogs.com/yycxy/archive/2011/03/03/1969858.html

关注