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

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 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:

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

String excelPath = AppDomain. currentDomain. baseDirectory + "Excel" + DateTime. now. ticks + ". xlsx "; if (System. IO. file. exists (excelPath) {textBox1.Text + = ("the file already Exists! "); Return;} try {// extract the Excel file System from the resource. IO. fileStream fs = new System. IO. fileStream (excelPath, FileMode. openOrCreate); fs. setLength (0); fs. write (Properties. resources. excel, 0, Properties. resources. excel. length); fs. close (); fs. dispose (); textBox1.Text = "Excel file extracted! "+" \ R \ n ";} catch (System. exception ex) {excelPath = string. empty; textBox1.Text + = ("An error occurred while extracting the Excel file:" + ex. message); textBox1.Text + = ("\ r \ n"); Application. doEvents (); return ;}

Define the connection string

// Define the OleDB 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 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

                DataTable oleDt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);                dataGridView1.DataSource = oleDt;                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.

OleDbCommand cmd = null; try {// insert several pieces of data into the "Sheet1" table. When accessing an Excel table, add the "$" symbol after the table name, the Insert statement does not specify the column name cmd = new OleDbCommand ("Insert Into [Sheet1 $] Values ('abc', 'bac ', '0', '123', 'test ', 'test', 'A') ", conn); // (A, B, C, D, E, F, G) cmd. executeNonQuery (); cmd. executeNonQuery (); cmd. executeNonQuery (); cmd. executeNonQuery (); cmd. executeNonQuery ();} catch (System. exception ex) {textBox1.Text + = ("failed to insert data:" + ex. message); textBox1.Text + = ("\ r \ n ");}

The content of the table "Sheet1" is displayed in dataGridView2. to access an Excel table, add the "$" symbol after the table name.

                cmd = new OleDbCommand("Select * From [Sheet1$]", conn);                OleDbDataAdapter adp = new OleDbDataAdapter(cmd);                DataSet ds = new DataSet();                adp.Fill(ds);                dataGridView2.DataSource = ds.Tables[0];

Traverse Schema content

                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 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 the file directory

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

Source code download: http://download.csdn.net/detail/testcs_dn/7328685