Author: Xu Changyou home page: http://yousoft.hi.com.cn
This document describes how to use C # Builder to access data through ODBC. And export the data to Excel,
The following uses C # Builder Enterprise + Microsoft Access 2000 + Microsoft Excel 2000 as an example.1. Create a database mydb with a table: Contact
Contact ID
Name
Last name
Address
City
Province [Related textures]
2. Establish ODBC (mydb) 3. Write a program
Click File-New-C # Application in the menu and enter the Application name.
[Related textures]
[Related textures]
If you have not installed ODBC components, you need to install them. Click Component-Installed. Net components in the menu. In the Installed. Net components window, confirm that the ODBC Component has been selected. After confirming, check whether there are ODBC components on tool Palette. Illustration
[Related textures]
[Related textures]
Add an OdbcConnection and an OdbcCommand
[Related textures]
Select odbcConnection1 and input: DSN = mydb; Uid = admin; Pwd =;
Select odbccommand1, connection select odbcconnection1, commandtext input: Select * From contact
Two buttons and ListBox are added to the winform window, And the Dock of ListBox is set to bottom.
Double-click the button and enter the code: 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 ();
}
For how to use. NET for ODBC, you can view help. The above is very detailed.
[Related textures]
Use the Com component to export data to Excel:
To use Excel in C #, we need to make some preparations First, find TlbImp and Excel9.olb in your computer, copy them to a folder, and execute TlbImp Excel9.olb in the DOS window, the following three files are generated: Excel. dll, Office. dll and VBIDE. dll.
Choose "project"> "Add reference" from the menu. In the pop-up dialog box, select "COM imports", click "Browser", and select the three generated DLL files. OK The Export Code is as follows: // Create an Excel file
Int I;
Excel. Application myExcel = new Excel. Application () MyExcel. Application. Workbooks. Add (true) // Make the Excel file visible MyExcel. Visible = true; // Name of the first behavior report MyExcel. Cells [1, 4] = "Contact ";
MyExcel. Cells [2, 1] = "Contact ID ";
MyExcel. Cells [2, 2] = "name ";
MyExcel. Cells [2, 3] = "last name ";
MyExcel. Cells [2, 4] = "Address ";
MyExcel. Cells [2, 5] = "city ";
MyExcel. Cells [2, 6] = "Province "; // Write data row by row, 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 ();
} The complete code of the program is as follows: 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 = "query ";
This. button1.Click + = new System. EventHandler (this. button#click );
//
// OdbcCommand1
//
This. odbcCommand1.CommandText = "select * from contact ";
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 = "Export ";
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 button#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)
{
// Create an Excel file
Int I;
Excel. Application myexcel = new excel. Application () Myexcel. application. workbooks. Add (true) // Make the Excel file visible Myexcel. Visible = true; // Name of the first behavior report Myexcel. cells [1, 4] = "Contact ";
Myexcel. cells [2, 1] = "Contact ID ";
Myexcel. cells [2, 2] = "name ";
Myexcel. cells [2, 3] = "last name ";
Myexcel. cells [2, 4] = "Address ";
Myexcel. cells [2, 5] = "city ";
Myexcel. cells [2, 6] = "Province "; // Write data row by row, 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 ();
} }
}
} 4. Run the program
Run programs by F9
[Related textures]
[Related textures]
Sample Code: [Click to download]
|
