bool hasTitle = false;string path="D:\\Test.xlsx";string fileType = System.IO.Path.GetExtension(path);string strCon = string.Empty;if (fileType == ".xls"){strCon = string.Format("Provider=Microsoft.Jet.OLEDB.{0}.0;" +"Extended Properties=\"Excel {1}.0;HDR={2};IMEX=1;\";" +"Data Source={3};",(fileType == ".xls" ? 4 : 12), (fileType == ".xls" ? 8 : 12), (hasTitle ? "Yes" : "NO"), path);}else{strCon = string.Format("Provider=Microsoft.ACE.OLEDB.{0}.0;" +"Extended Properties=\"Excel {1}.0;HDR={2};IMEX=1;\";" +"Data Source={3};",(fileType == ".xls" ? 4 : 12), (fileType == ".xls" ? 8 : 12), (hasTitle ? "Yes" : "NO"), path);}
string sheetName = "sheet1";string strCom = " SELECT * FROM [" + sheetName + "$]";
OleDbConnection myConn = new OleDbConnection(strCon);
然后,创建一个sql语句的适配器:OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
执行并获取数据:System.Data.DataTable dt = new System.Data.DataTable();myConn.Open();myCommand.Fill(dt);
如果不出意外,我们成功获取了该数据:
String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source=D:/Test2.xls;" +"Extended Properties=Excel 8.0;";OleDbConnection cn = new OleDbConnection(sConnectionString);
string sqlCreate = "CREATE TABLE TestSheet ([姓?名?] VarChar,[成¨¦绩¡§] INTEGER)";
紧接着创建命令对象用来执行建表和插数据的命令。值得注意的是OLEDB中插叙数据和写入数据的命令执行器是不一样的,查询数据中我们使用的是OleDbDataAdapter对象执行查询命令,而在建表、插入数据时我们用的是OleDbCommand对象来执行命令。OleDbCommand cmd = new OleDbCommand(sqlCreate, cn);
然后创建文件,我们已经建立了连接cn,只要打开了这个连接,就自动创建了Excel文件:cn.Open();
执行创建sheet的语句:cmd.ExecuteNonQuery();
然后循环添加数据:for (int i = 1; i < dt.Rows.Count;i++ ){DataRow row = dt.Rows[i];cmd.CommandText = "INSERT INTO TestSheet VALUES('" + row["F1"] + "'," + row["F2"] + ")";cmd.ExecuteNonQuery();}
最后,关闭连接。 cn.Close();
如果不出意外,我们成功的将D://Test. xlsx文件中的内容写入到D://Test2. xls
本文完整源代码:
/*定义连接字符串*/ bool hasTitle = false; string path="D:\\Test.xlsx"; string fileType = System.IO.Path.GetExtension(path); string strCon = string.Empty; if (fileType == ".xls") { strCon = string.Format("Provider=Microsoft.Jet.OLEDB.{0}.0;" + "Extended Properties=\"Excel {1}.0;HDR={2};IMEX=1;\";" + "Data Source={3};", (fileType == ".xls" ? 4 : 12), (fileType == ".xls" ? 8 : 12), (hasTitle ? "Yes" : "NO"), path); } else { strCon = string.Format("Provider=Microsoft.ACE.OLEDB.{0}.0;" + "Extended Properties=\"Excel {1}.0;HDR={2};IMEX=1;\";" + "Data Source={3};", (fileType == ".xls" ? 4 : 12), (fileType == ".xls" ? 8 : 12), (hasTitle ? "Yes" : "NO"), path); } /*使用SQL语句读取数据*/ string sheetName = "sheet1"; string strCom = " SELECT * FROM [" + sheetName + "$]"; /*建立数据库连接*/ OleDbConnection myConn = new OleDbConnection(strCon); /*建立sql语句执行器*/ OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn); /*执行并读取数据*/ System.Data.DataTable dt = new System.Data.DataTable(); myConn.Open();//打开连接 myCommand.Fill(dt);//填充数据 myConn.Close();//关闭连接 /*将数据集里的数据写入到Excel*/ //1.创建连接字符串 String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=D:/Test2.xls;" + "Extended Properties=Excel 8.0;"; OleDbConnection cn = new OleDbConnection(sConnectionString); string sqlCreate = "CREATE TABLE TestSheet ([姓名] VarChar,[成绩] INTEGER)"; OleDbCommand cmd = new OleDbCommand(sqlCreate, cn); //创建Excel文件:D:/Test2.xls cn.Open(); //创建TestSheet工作表 cmd.ExecuteNonQuery(); //添加数据 for (int i = 1; i < dt.Rows.Count;i++ ) { DataRow row = dt.Rows[i]; cmd.CommandText = "INSERT INTO TestSheet VALUES('" + row["F1"] + "'," + row["F2"] + ")"; cmd.ExecuteNonQuery(); } //关闭连接 cn.Close();
联系客服