在我们的.Net程序中,有时需要将Excel中的表格数据或CSV文件中的数据导出到DataSet然后再进行处理,下面的方法使用OleDB来实现这一功能:
Excel到DataSet:
string xlsPath = @"D:/documents/TestLoad2.xls"; // 绝对物理路径
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Extended Properties=/"Excel 8.0;IMEX=1/";" +
"data source=" + xlsPath;
// 查询语句
string sql = "SELECT * FROM [Sheet1$]";
DataSet ds = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter(sql, connStr);
da.Fill(ds); // 填充DataSet
CSV到DataSet:
string csvPath = @"D:/documents/TestLoad2.xls"; //
string fileFullName = Path.GetFileName(csvPath);
string folderPath = csvPath.Substring(0,csvPath.LastIndexOf('//') + 1);
string connStr = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='text;HDR=Yes;IMEX=1'", folderPath); //FMT=Delimited;
// select statement
string sql = string.Format(@"SELECT * FROM [{0}]", fileFullName); //select top 2 * from filename.csv
DataSet ds = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter(sql, connStr);
da.Fill(ds); // 填充DataSet