本文主要介绍用C# Builder通过ODBC访问数据。并将数据导出到Excel,下面以C# Builder Enterprise+Microsoft Access 2000+Microsoft Excel 2000为例。
1.建立数据库mydb,内建表:联系人
联系人ID 名字 姓氏 地址 城市 省份
[ 相关贴图 ]
2.建立ODBC(mydb)
3.编写程序
点击菜单 File - New - C# Application,输入应用程序名称
[ 相关贴图 ]
[ 相关贴图 ]
如果还没有安装ODBC组件,你还需要装上它们。点击菜单Component - Installed .Net components,在Installed .Net components窗口中确定ODBC组件已经选上。确定后,看tool Palette上是否有ODBC几个组件在上面。图示
[ 相关贴图 ]
[ 相关贴图 ]
加上一个OdbcConnection和一个OdbcCommand
[ 相关贴图 ]
选中odbcConnection1,在ConnectionString输入:DSN=mydb;Uid=admin;Pwd=; 选中odbcCommand1,Connection选择odbcConnection1,CommandText输入:select * from 联系人 WinForm窗口加上两个Button和ListBox,其中ListBox的Dock设为Bottom 双击按钮,输入代码:
listBox1.Items.Clear(); odbcConnection1.Open(); OdbcDataReader myreader=odbcCommand1.ExecuteReader(); try{ while (myreader.Read()) { listBox1.Items.Add(myreader.GetString(0)+","+myreader.GetString(1)+" "+myreader.GetString(2)); }
} finally{ myreader.Close(); odbcConnection1.Close(); }
对于.NET对ODBC的一些使用方法,可以查看帮助。上面写得很详细。
[ 相关贴图 ]
#p#通过Com组件来完成数据导出Excel:
为了在C#中使用Excel,我们要先做一点准备工作,在你的计算机中找到TlbImp和Excel9.olb,将他们复制到一个文件夹中,在DOS窗口中执行 TlbImp Excel9.olb,这时会产生以下三个文件:Excel.dll、Office.dll和VBIDE.dll。
通过菜单 project->Add reference ,弹出的对话框中选择COM imports,点击Browser按钮,选中前面生成的三个DLL文件,OK
导出代码如下: //创建一个Excel文件 int i; Excel.Application myExcel = new Excel.Application ( )
myExcel.Application.Workbooks.Add ( true )
//让Excel文件可见
myExcel.Visible=true;
//第一行为报表名称
myExcel.Cells[1,4]="联系人"; myExcel.Cells[2,1]="联系人ID"; myExcel.Cells[2,2]="名字"; myExcel.Cells[2,3]="姓氏"; myExcel.Cells[2,4]="地址"; myExcel.Cells[2,5]="城市"; myExcel.Cells[2,6]="省份";
//逐行写入数据,
listBox1.Items.Clear(); odbcConnection1.Open(); OdbcDataReader myreader=odbcCommand1.ExecuteReader(); try{ i=2; while (myreader.Read()) { i=i+1; myExcel.Cells[i,1]=myreader.GetString(0); myExcel.Cells[i,2]=myreader.GetString(1); myExcel.Cells[i,3]=myreader.GetString(2); myExcel.Cells[i,4]=myreader.GetString(3); myExcel.Cells[i,5]=myreader.GetString(4); myExcel.Cells[i,6]=myreader.GetString(5); }
} finally{ myreader.Close(); odbcConnection1.Close(); }
程序完整的代码如下:
using System; using System.Drawing; using System.Collections; using System.ComponentModel; using System.Windows.Forms; using System.Data; using System.Data.Odbc; using System.IO; using System.Reflection;
namespace DBApp { /// <summary> /// Summary description for WinForm. /// </summary> public class WinForm : System.Windows.Forms.Form { /// <summary> /// Required designer variable. /// </summary> private System.ComponentModel.Container components = null; private System.Data.Odbc.OdbcConnection odbcConnection1; private System.Data.Odbc.OdbcCommand odbcCommand1; private System.Windows.Forms.ListBox listBox1; private System.Windows.Forms.Button button1; private System.Windows.Forms.Button button2;
public WinForm() { // // Required for Windows Form Designer support // InitializeComponent();
// // TODO: Add any constructor code after InitializeComponent call // }
/// <summary> /// Clean up any resources being used. /// </summary> protected override void Dispose (bool disposing) { if (disposing) { if (components != null) { components.Dispose(); } } base.Dispose(disposing); }
#region Windows Form Designer generated code /// <summary> /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary> private void InitializeComponent() { this.odbcConnection1 = new System.Data.Odbc.OdbcConnection(); this.listBox1 = new System.Windows.Forms.ListBox(); this.button1 = new System.Windows.Forms.Button(); this.odbcCommand1 = new System.Data.Odbc.OdbcCommand(); this.button2 = new System.Windows.Forms.Button(); this.SuspendLayout(); // // odbcConnection1 // this.odbcConnection1.ConnectionString = "DSN=mydb;Uid=admin;Pwd=;"; // // listBox1 // this.listBox1.Dock = System.Windows.Forms.DockStyle.Bottom; this.listBox1.ItemHeight = 12; this.listBox1.Location = new System.Drawing.Point(0, 53); this.listBox1.Name = "listBox1"; this.listBox1.Size = new System.Drawing.Size(368, 184); this.listBox1.TabIndex = 0; // // button1 // this.button1.Location = new System.Drawing.Point(16, 16); this.button1.Name = "button1"; this.button1.TabIndex = 1; this.button1.Text = "查询"; this.button1.Click += new System.EventHandler(this.button1_Click); // // odbcCommand1 // this.odbcCommand1.CommandText = "select * from 联系人 "; this.odbcCommand1.Connection = this.odbcConnection1; // // button2 // this.button2.Location = new System.Drawing.Point(245, 14); this.button2.Name = "button2"; this.button2.TabIndex = 2; this.button2.Text = "导出"; this.button2.Click += new System.EventHandler(this.button2_Click); // // WinForm // this.AutoScaleBaseSize = new System.Drawing.Size(6, 14); this.ClientSize = new System.Drawing.Size(368, 237); this.Controls.Add(this.button2); this.Controls.Add(this.button1); this.Controls.Add(this.listBox1); this.Name = "WinForm"; this.Text = "WinForm"; this.Load += new System.EventHandler(this.WinForm_Load); this.ResumeLayout(false); } #endregion
/// <summary> /// The main entry point for the application. /// </summary> [STAThread] static void Main() { Application.Run(new WinForm()); }
private void WinForm_Load(object sender, System.EventArgs e) {
}
private void button1_Click(object sender, System.EventArgs e) { listBox1.Items.Clear(); odbcConnection1.Open(); OdbcDataReader myreader=odbcCommand1.ExecuteReader(); try{ while (myreader.Read()) { listBox1.Items.Add(myreader.GetString(0)+","+myreader.GetString(1)+" "+myreader.GetString(2)); }
} finally{ myreader.Close(); odbcConnection1.Close(); }
}
private void button2_Click(object sender, System.EventArgs e) { //创建一个Excel文件 int i; Excel.Application myExcel = new Excel.Application ( )
myExcel.Application.Workbooks.Add ( true )
//让Excel文件可见
myExcel.Visible=true;
//第一行为报表名称
myExcel.Cells[1,4]="联系人"; myExcel.Cells[2,1]="联系人ID"; myExcel.Cells[2,2]="名字"; myExcel.Cells[2,3]="姓氏"; myExcel.Cells[2,4]="地址"; myExcel.Cells[2,5]="城市"; myExcel.Cells[2,6]="省份";
//逐行写入数据,
listBox1.Items.Clear(); odbcConnection1.Open(); OdbcDataReader myreader=odbcCommand1.ExecuteReader(); try{ i=2; while (myreader.Read()) { i=i+1; myExcel.Cells[i,1]=myreader.GetString(0); myExcel.Cells[i,2]=myreader.GetString(1); myExcel.Cells[i,3]=myreader.GetString(2); myExcel.Cells[i,4]=myreader.GetString(3); myExcel.Cells[i,5]=myreader.GetString(4); myExcel.Cells[i,6]=myreader.GetString(5); }
} finally{ myreader.Close(); odbcConnection1.Close(); }
} } }
#p#4.运行程序
按F9运行程序
[ 相关贴图 ]
[ 相关贴图 ]
|