Tool materials:

Windows 7, Visual Studio 2010, Microsoft Office 2007

Create solution

Menu》New》Project》Windows Forms Application:

Add related components:

Add two DataGridView, one TextBox, and two buttons, as shown below:

Add Excel resource:

C#Create an Excel file. Here, a pre-created Excel file is actually extracted from the resource. The file extraction is successful. Finally, use the OleDb method to connect to Excel and write data to the Excel file.

First create a new Excel file in the folder, and set the column name in the first row of Sheet1:

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

#Add an existing file and select the Excel file just created

Extract Excel files from resources

            string excelPath = AppDomain.CurrentDomain.BaseDirectory + "Excel" + DateTime.Now.Ticks + ".xlsx";
            if (System.IO.File.Exists(excelPath))
            {
                textBox1.Text += ("文件已經(jīng)存在！");
                return;
            }

            try
            {
                //從資源中提取Excel文件
                System.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文件成功！" + "\r\n";
            }
            catch (System.Exception ex)
            {
                excelPath = string.Empty;
                textBox1.Text += ("提取Excel文件失?。?quot; + ex.Message);
                textBox1.Text += ("\r\n");
                Application.DoEvents();
                return;
            }

Define the connection string

//定義OleDB連接字符串
            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, if it is 1 or 2 , when executing the Insert Into statement, the error " operation must use an updateable query " will be reported.

Display the 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 the Excel table, you need to add "$ after the table name. " symbol, the Insert statement does not need to specify a column name

                OleDbCommand cmd = null;
                try
                {
                    //向"Sheet1"表中插入幾條數(shù)據(jù),訪問Excel的表的時候需要在表名后添加"$"符號,Insert語句可以不指定列名
                    cmd = new OleDbCommand("Insert Into [Sheet1$] Values('abc', 'bac', '0', '123456', 'test','測試','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 += ("插入數(shù)據(jù)失?。?quot; + ex.Message);
                    textBox1.Text += ("\r\n");
                }

Display the contents of the table "Sheet1" in dataGridView2. When accessing the Excel table, you need to 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 the contents of 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 += ("關(guān)閉Excel數(shù)據(jù)連接：" + ex.Message);
                        textBox1.Text += ("\r\n");
                    }
                }

Open the file directory

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

The above is the detailed content of Detailed explanation of sample code for creating Excel files and exporting data to Excel files in C# (picture). For more information, please follow other related articles on the PHP Chinese website!