C # create an Excel file and export the data to an Excel file,

Source: Internet
Author: User

C # create an Excel file and export the data to an Excel file,
Tool raw materials:

Windows 7, Visual Studio 2010, Microsoft Office 2007

Create a solution

Menu> new project> Windows Forms Application:

Add related components:

Add two dview datagri, one TextBox and two buttons, for example:

Add an Excel Resource:

C # create an Excel file. Here, a previously created Excel file is extracted from the resource. After the file is extracted successfully, use the OleDb method to connect to Excel and write data to the Excel file.

Create an Excel file in the folder and set the column name in the first row of the Sheet1 table:

Double-click the "Resources. resx" file to open the resource file view:

Add an existing file and select the created Excel File

Extract Excel files from resources [Csharp]View plaincopy
  1. String excelPath = AppDomain. CurrentDomain. BaseDirectory + "Excel" + DateTime. Now. Ticks + ". xlsx ";
  2. If (System. IO. File. Exists (excelPath ))
  3. {
  4. TextBox1.Text + = ("the file already exists! ");
  5. Return;
  6. }
  7. Try
  8. {
  9. // Extract an Excel file from the Resource
  10. System. IO. FileStream fs = new System. IO. FileStream (excelPath, FileMode. OpenOrCreate );
  11. Fs. SetLength (0 );
  12. Fs. Write (Properties. Resources. Excel, 0, Properties. Resources. Excel. Length );
  13. Fs. Close ();
  14. Fs. Dispose ();
  15. TextBox1.Text = "Excel file extracted! "+" \ R \ n ";
  16. }
  17. Catch (System. Exception ex)
  18. {
  19. ExcelPath = string. Empty;
  20. TextBox1.Text + = ("Excel file extraction failed:" + ex. Message );
  21. TextBox1.Text + = ("\ r \ n ");
  22. Application. DoEvents ();
  23. Return;
  24. }
Define the connection string [Csharp]View plaincopy
  1. // Define the OleDB connection string
  2. String strConn = "Provider = Microsoft. ace. oleDb.12.0; Persist Security Info = False; "+" data source = "+ @ excelPath +"; Extended Properties = 'excel 12.0; HDR = yes; IMEX = 10 '";
  3. OleDbConnection conn = new OleDbConnection ();
  4. Conn. ConnectionString = strConn;

Note: The IMEX value in the connection string uses 10. If it is 1 or 2, the"The operation must use an updatable query..

Display information of all tables in the Excel file in dataGridView1 [Csharp]View plaincopy
  1. DataTable oleDt = conn. GetOleDbSchemaTable (OleDbSchemaGuid. Tables, null );
  2. DataGridView1.DataSource = oleDt;
  3. DataGridView1.Show ();
Insert several pieces of data into the "Sheet1" table. When accessing an Excel table, add the "$" symbol after the table name. The Insert statement can not specify the column name. [Csharp]View plaincopy
  1. OleDbCommand cmd = null;
  2. Try
  3. {
  4. // Insert several pieces of data into the "Sheet1" table. to access an Excel table, add the "$" symbol after the table name. The Insert statement can not specify the column name.
  5. Cmd = new OleDbCommand ("Insert Into [Sheet1 $] Values ('abc', 'bac ', '0', '123', 'test', 'test ', 'A') ", conn); // (A, B, C, D, E, F, G)
  6. Cmd. ExecuteNonQuery ();
  7. Cmd. ExecuteNonQuery ();
  8. Cmd. ExecuteNonQuery ();
  9. Cmd. ExecuteNonQuery ();
  10. Cmd. ExecuteNonQuery ();
  11. }
  12. Catch (System. Exception ex)
  13. {
  14. TextBox1.Text + = ("failed to insert data:" + ex. Message );
  15. TextBox1.Text + = ("\ r \ n ");
  16. }
The content of the table "Sheet1" is displayed in dataGridView2. to access an Excel table, add the "$" symbol after the table name. [Csharp]View plaincopy
  1. Cmd = new OleDbCommand ("Select * From [Sheet1 $]", conn );
  2. OleDbDataAdapter adp = new OleDbDataAdapter (cmd );
  3. DataSet ds = new DataSet ();
  4. Adp. Fill (ds );
  5. DataGridView2.DataSource = ds. Tables [0];
Traverse Schema content [Csharp]View plaincopy
  1. DataTable dt = conn. GetSchema ();
  2. For (int I = 0; I <dt. Columns. Count; I ++)
  3. {
  4. TextBox1.Text + = dt. Columns [I]. Caption;
  5. If (I + 1 <dt. Columns. Count)
  6. {
  7. TextBox1.Text + = ",";
  8. }
  9. }
  10. For (int j = 0; j <dt. Rows. Count; j ++)
  11. {
  12. For (int I = 0; I <dt. Columns. Count; I ++)
  13. {
  14. If (dt. Rows [j] [dt. Columns [I]! = Null)
  15. {
  16. TextBox1.Text + = dt. Rows [j] [dt. Columns [I]. ToString ();
  17. }
  18. Else
  19. {
  20. TextBox1.Text + = "null ";
  21. }
  22. If (I + 1 <dt. Columns. Count)
  23. {
  24. TextBox1.Text + = ",";
  25. }
  26. }
  27. TextBox1.Text + = ("\ r \ n ");
  28. }
Close Excel Data Connection [Csharp]View plaincopy
  1. If (conn. State! = ConnectionState. Closed)
  2. {
  3. Try
  4. {
  5. Conn. Close ();
  6. }
  7. Catch (System. Exception ex)
  8. {
  9. TextBox1.Text + = ("Close Excel Data Connection:" + ex. Message );
  10. TextBox1.Text + = ("\ r \ n ");
  11. }
  12. }
Open the file directory [Csharp]View plaincopy
    1. System. Diagnostics. Process. Start ("assumer.exe", AppDomain. CurrentDomain. BaseDirectory );

In the C language, what is the symbol (->) and how to use it?

This is a symbol in the struct pointer. Write a program to explain it, for example:
# Include <stdio. h>
Struct STU // define a struct
{
Int num;
} Stu;
Int main ()
{
Struct STU * p; // defines a struct pointer.
P = stu; // p points to the struct variable stu.
Stu. num = 100; // attaches an initial value to the struct member num.
Printf ("% d", p-> num); // output the num value in stu
Return;
}
As you can see, the-> method is to reference the variable in the struct !!
Format: p-> struct member (such as p-> num)
The function is equivalent to stu. num or (* p). num.
I don't know. You don't understand, and don't understand call me. O (∩ _ ∩) O ~
Hope to adopt it.

In the C language, what is the symbol (->) and how to use it?

This is a symbol in the struct pointer. Write a program to explain it, for example:
# Include <stdio. h>
Struct STU // define a struct
{
Int num;
} Stu;
Int main ()
{
Struct STU * p; // defines a struct pointer.
P = stu; // p points to the struct variable stu.
Stu. num = 100; // attaches an initial value to the struct member num.
Printf ("% d", p-> num); // output the num value in stu
Return;
}
As you can see, the-> method is to reference the variable in the struct !!
Format: p-> struct member (such as p-> num)
The function is equivalent to stu. num or (* p). num.
I don't know. You don't understand, and don't understand call me. O (∩ _ ∩) O ~
Hope to adopt it.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.