读取excel或csv文件中的数据暂时保存在DataTable中, 代码如下:
public static DataTable ReadDataFromFile(string file, string sheet){string strConn = "";string extension = Path.GetExtension(file);string sqlStr = string.Empty;if (extension == ".csv"){strConn = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='text;HDR=Yes;FMT=Delimited';Data Source={0}", Path.GetDirectoryName(file));sqlStr = string.Format("select * from {0}", Path.GetFileName(file));}else if (extension == ".xls"){strConn = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES;'", file);sqlStr = string.Format("SELECT * FROM [{0}$]", sheet);}else if (extension == ".xlsx"){strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;", file);sqlStr = string.Format("SELECT * FROM [{0}$]", sheet);}else{throw new Exception(string.Format("不支持的导入扩展名为{0}的文件!", extension));}OleDbConnection conn = new OleDbConnection(strConn);OleDbDataAdapter myCommand = new OleDbDataAdapter(sqlStr, strConn);DataTable dt = new DataTable();try{myCommand.Fill(dt);}catch (Exception ex){throw ex;}finally{conn.Close();}return dt;}