C # Create an Excel file and export the data to an Excel file

Tool Material:

Windows 7,visual Studio, Microsoft Office 2007

Create a solution

Menu new Project Windows Forms application:

Add Related components:

Add two DataGridView, a TextBox, two buttons, such as:

To add an Excel resource:

C # creates an Excel file, where you actually extract a pre-created Excel file from the resource, and after the file extraction succeeds, you use the OLE DB method to connect to Excel and write the data to the Excel file.

Create a new 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, select the Excel file you just created

Extracting an Excel file from a resource [CSharp]View Plaincopy

2. String Excelpath = AppDomain.CurrentDomain.BaseDirectory + "Excel" + DateTime.Now.Ticks + ". xlsx";
4. if (System.IO.File.Exists (Excelpath))
6. {
8. TextBox1.Text + = ("File already exists!  ");
10. return;
12. }
16. Try
18. {
20. //Extract the Excel file from the resource
22. System.IO.FileStream fs = new System.IO.FileStream (Excelpath, FileMode.OpenOrCreate);
24. Fs. SetLength (0);
26. Fs. Write (Properties.Resources.Excel, 0, Properties.Resources.Excel.Length);
28. Fs. Close ();
30. Fs. Dispose ();
32. TextBox1.Text = "Extract Excel file successfully!"  "+ " \ r \ n ";
34. }
36. catch (System.Exception ex)
38. {
40. Excelpath = string.  Empty;
42. TextBox1.Text + = ("Extract Excel file failed:" + ex.)  Message);
44. TextBox1.Text + = ("\ r \ n");
46. Application.doevents ();
48. return;
50. }

Defining a connection string [CSharp]View Plaincopy

1. //define OLE DB connection string   
2.             string  Strconn =  "provider=microsoft.ace.oledb.12.0; persist security info=false; "  +  "data source="  +  @excelPath  + 
3.              Oledbconnection conn = new oledbconnection ();   
4.             conn. connectionstring = strconn;  

Note: The value of Imex in the connection string is 10, and if it is 1 or 2, an error " operation must use an updatable query " is reported when an INSERT INTO statement is executed.

Display information for all tables in an Excel file in DataGridView1 [CSharp]View Plaincopy

1. DataTable Oledt = conn.  GetOleDbSchemaTable (OleDbSchemaGuid.Tables, null);
2. Datagridview1.datasource = Oledt;
3. Datagridview1.show ();

Insert a few data into the "Sheet1" table, you need to add a "$" symbol after the table name to access the Excel table, the INSERT statement can not specify the column name [CSharp]View Plaincopy

2. OleDbCommand cmd = null;
4. Try
6. {
8. ///To insert a few data into the "Sheet1" table, you need to add a "$" symbol after the table name to access the Excel table, the INSERT statement can not specify the column name
10. cmd = new OleDbCommand ("Insert into [sheet1$] Values (' abc ', ' Bac ', ' 0 ', ' 123456 ', ' Test ', ' testing ', ' AA ')", conn); (a,b,c,d,e,f,g)
12. Cmd. ExecuteNonQuery ();
14. Cmd. ExecuteNonQuery ();
16. Cmd. ExecuteNonQuery ();
18. Cmd. ExecuteNonQuery ();
20. Cmd. ExecuteNonQuery ();
22. }
24. catch (System.Exception ex)
26. {
28. TextBox1.Text + = ("Insert data failed:" + ex.)  Message);
30. TextBox1.Text + = ("\ r \ n");
32. }

Display the contents of the table "Sheet1" in dataGridView2, you need to add the "$" symbol after the table name when accessing the Excel table [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];

Traversing the contents of a schema [CSharp]View Plaincopy

2. DataTable DT = conn. GetSchema ();
4. for (int i = 0; i < dt. Columns.count; i++)
6. {
8. TextBox1.Text + = dt. Columns[i]. Caption;
10. if (i + 1 < dt. Columns.count)
12. {
14. TextBox1.Text + = ",";
16. }
18. }
22. for (int j = 0; j < dt. Rows.Count; J + +)
24. {
26. For (int i = 0; i < dt. Columns.count; i++)
28. {
30. if (dt. Rows[j][dt. Columns[i]]! = null)
32. {
34. TextBox1.Text + = dt. Rows[j][dt. Columns[i]]. ToString ();
36. }
38. Else
40. {
42. TextBox1.Text + = "null";
44. }
48. if (i + 1 < dt. Columns.count)
50. {
52. TextBox1.Text + = ",";
54. }
56. }
58. TextBox1.Text + = ("\ r \ n");
60. }

Close the Excel data connection [CSharp]View Plaincopy

2. IF (Conn. State = connectionstate.closed)
4. {
6. Try
8. {
10. Conn. Close ();
12. }
14. catch (System.Exception ex)
16. {
18. TextBox1.Text + = ("Close Excel Data connection:" + ex.)  Message);
20. TextBox1.Text + = ("\ r \ n");
22. }
24. }

Open File Directory [CSharp]View Plaincopy

1. System.Diagnostics.Process.Start ("Explorer.exe", AppDomain.CurrentDomain.BaseDirectory);
   

C # Create an Excel file and export the data to an Excel file