Example code in C # Creating an Excel file and exporting 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

 string Excelpath = AppDomain.CurrentDomain.BaseDirectory + "Excel" + Date            Time.Now.Ticks + ". xlsx"; if (System.IO.File.Exists (Excelpath)) {TextBox1.Text + = ("File already exists!                ");            Return try {//extract Excel file from resource System.IO.FileStream fs = new System.IO.FileSt                Ream (Excelpath, FileMode.OpenOrCreate); Fs.                SetLength (0); Fs.                Write (Properties.Resources.Excel, 0, Properties.Resources.Excel.Length); Fs.                Close (); Fs.                Dispose (); TextBox1.Text = "Extract Excel file successfully!"            "+" \ r \ n "; } catch (System.Exception ex) {Excelpath = string.                Empty; TextBox1.Text + = ("Extract Excel file failed:" + ex.)                Message);                TextBox1.Text + = ("\ r \ n");                Application.doevents ();            Return }

Defining a connection string

Defines the OLE DB connection string            strconn = "provider=microsoft.ace.oledb.12.0; Persist Security info=false; "+" Data source= "+ @excelPath +";            Extended properties= ' Excel 12.0; Hdr=yes; Imex=10 ' ";            OleDbConnection conn = new OleDbConnection ();            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

                DataTable Oledt = conn. GetOleDbSchemaTable (OleDbSchemaGuid.Tables, null);                Datagridview1.datasource = Oledt;                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

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

Display the contents of the table "Sheet1" in dataGridView2, you need to add the "$" symbol after the table name when accessing the Excel table

                cmd = new OleDbCommand ("SELECT * from [sheet1$]", conn);                OleDbDataAdapter ADP = new OleDbDataAdapter (cmd);                DataSet ds = new DataSet ();                Adp. Fill (DS);                Datagridview2.datasource = ds. Tables[0];

Traversing the contents of a schema

                DataTable DT = conn.                GetSchema (); for (int i = 0; i < dt. Columns.count; i++) {TextBox1.Text + = dt. Columns[i].                    Caption; if (i + 1 < dt.                    Columns.count) {TextBox1.Text + = ","; }} for (int j = 0; j < dt. Rows.Count; J + +) {for (int i = 0; i < dt. Columns.count; i++) {if (dt). Rows[j][dt. Columns[i]]! = NULL) {TextBox1.Text + = dt. Rows[j][dt. Columns[i]].                        ToString ();                        } else {TextBox1.Text + = "NULL"; } if (i + 1 < dt.                        Columns.count) {TextBox1.Text + = ",";                   } } TextBox1.Text + = ("\ r \ n"); }

Close the Excel data connection

                IF (Conn. State = connectionstate.closed)                {                    try                    {                        Conn. Close ();                    }                    catch (System.Exception ex)                    {                        TextBox1.Text + = ("Close Excel Data connection:" + ex.) Message);                        TextBox1.Text + = ("\ r \ n");}                }

Open File Directory

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