- //1.读取excel文件
- if (openFileDialog1.ShowDialog() == DialogResult.OK)
- {
- String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
- "Data Source="+openFileDialog1.FileName+";"+
- "Extended Properties='Excel 8.0;HDR=Yes;IMEX=2'";
- //实例化一个Oledbconnection类(实现了IDisposable,要using)
- using (OleDbConnection ole_conn = new OleDbConnection(sConnectionString))
- {
- ole_conn.Open();
- using (OleDbCommand ole_cmd = ole_conn.CreateCommand())
- {
- //类似SQL的查询语句这个[Sheet1$对应Excel文件中的一个工作表]
- ole_cmd.CommandText = "select * from [Sheet1$]";
- OleDbDataAdapter adapter = new OleDbDataAdapter(ole_cmd);
- DataSet ds = new DataSet();
- adapter.Fill(ds, "Sheet1");
- for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
- {
- MessageBox.Show(ds.Tables[0].Rows[i]["商家名称"].ToString());
- }
- }
- }
- }
- //2.获取工作簿中所有的工作表
- if (openFileDialog1.ShowDialog() == DialogResult.OK)
- {
- String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
- "Data Source="+openFileDialog1.FileName+";"+
- "Extended Properties='Excel 8.0;HDR=Yes;IMEX=2'";
- //实例化一个Oledbconnection类(实现了IDisposable,要using)
- using (OleDbConnection ole_conn = new OleDbConnection(sConnectionString))
- {
- ole_conn.Open();
- using (OleDbCommand ole_cmd = ole_conn.CreateCommand())
- {
- DataTable tb = ole_conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
- foreach (DataRow row in tb.Rows)
- {
- MessageBox.Show(row["TABLE_NAME"].ToString());
- }
- }
- }
- }
- //3.写入数据到Excel表
- if (openFileDialog1.ShowDialog() == DialogResult.OK)
- {
- String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
- "Data Source="+openFileDialog1.FileName+";"+
- "Extended Properties=Excel 8.0;";
- //实例化一个Oledbconnection类(实现了IDisposable,要using)
- using (OleDbConnection ole_conn = new OleDbConnection(sConnectionString))
- {
- ole_conn.Open();
- using (OleDbCommand ole_cmd = ole_conn.CreateCommand())
- {
- ole_cmd.CommandText = "insert into [Sheet1$](商户ID,商家名称)values('DJ001','点击科技')";
- ole_cmd.ExecuteNonQuery();
- MessageBox.Show("数据插入成功......");
- }
- }
- }
- //4.创建Excel文件并写入数据
- String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
- "Data Source=d:\\\\excel1.xls;" +
- "Extended Properties=Excel 8.0;";
- //实例化一个Oledbconnection类(实现了IDisposable,要using)
- using (OleDbConnection ole_conn = new OleDbConnection(sConnectionString))
- {
- ole_conn.Open();
- using (OleDbCommand ole_cmd = ole_conn.CreateCommand())
- {
- ole_cmd.CommandText = "CREATE TABLE CustomerInfo ([CustomerID] VarChar,[Customer] VarChar)";
- ole_cmd.ExecuteNonQuery();
- ole_cmd.CommandText = "insert into CustomerInfo(CustomerID,Customer)values('CS001','代码片段')";
- ole_cmd.ExecuteNonQuery();
- MessageBox.Show("生成Excel文件成功并写入一条数据......");
- }
- }
- //该片段来自于http://www.codesnippet.cn/detail/030720134411.html
来源: http://www.codesnippet.cn/detail/030720134411.html