Windows 7, Visual Studio 2010, Microsoft Office 2007
Menu》New》Project》Windows Forms Application:
Add two DataGridView, one TextBox, and two buttons, as shown below:
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
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; }
//定义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.
DataTable oleDt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); dataGridView1.DataSource = oleDt; dataGridView1.Show();
OleDbCommand cmd = null; try { //向"Sheet1"表中插入几条数据,访问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 += ("插入数据失败:" + ex.Message); textBox1.Text += ("\r\n"); }
cmd = new OleDbCommand("Select * From [Sheet1$]", conn); OleDbDataAdapter adp = new OleDbDataAdapter(cmd); DataSet ds = new DataSet(); adp.Fill(ds); dataGridView2.DataSource = ds.Tables[0];
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"); }
if (conn.State != ConnectionState.Closed) { try { conn.Close(); } catch (System.Exception ex) { textBox1.Text += ("关闭Excel数据连接:" + ex.Message); textBox1.Text += ("\r\n"); } }
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!