Detailed explanation of sample code for creating Excel files and exporting data to Excel files in C# (picture)-C#.Net Tutorial-php.cn

Detailed explanation of sample code for creating Excel files and exporting data to Excel files in C# (picture)

黄舟

Release： 2017-03-13 17:47:16

Original

2338 people have browsed it

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 += ("文件已经存在！");
                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文件失败：" + ex.Message);
                textBox1.Text += ("\r\n");
                Application.DoEvents();
                return;
            }

Copy after login

Define the connection string

//定义OleDB连接字符串
            string strConn = "Provider=Microsoft.Ace.OleDb.12.0;Persist Security Info=False;" + "data source=" + @excelPath + ";
            Extended Properties=&#39;Excel 12.0; HDR=yes; IMEX=10&#39;";
            OleDbConnection conn = new OleDbConnection();
            conn.ConnectionString = strConn;

Copy after login

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();

Copy after login

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"表中插入几条数据,访问Excel的表的时候需要在表名后添加"$"符号,Insert语句可以不指定列名
                    cmd = new OleDbCommand("Insert Into [Sheet1$] Values(&#39;abc&#39;, &#39;bac&#39;, &#39;0&#39;, &#39;123456&#39;, &#39;test&#39;,&#39;测试&#39;,&#39;aa&#39;)", conn);//(A,B,C,D,E,F,G) 
                    cmd.ExecuteNonQuery();
                    cmd.ExecuteNonQuery();
                    cmd.ExecuteNonQuery();
                    cmd.ExecuteNonQuery();
                    cmd.ExecuteNonQuery();
                }
                catch (System.Exception ex)
                {
                    textBox1.Text += ("插入数据失败：" + ex.Message);
                    textBox1.Text += ("\r\n");
                }

Copy after login

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];

Copy after login

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");
                }

Copy after login

Close the Excel data connection

                if (conn.State != ConnectionState.Closed)
                {
                    try
                    {
                        conn.Close();
                    }
                    catch (System.Exception ex)
                    {
                        textBox1.Text += ("关闭Excel数据连接：" + ex.Message);
                        textBox1.Text += ("\r\n");
                    }
                }

Copy after login

Open the file directory

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

Copy after login

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!