使用oledbconnection读取excel数据
以excel为数据源进行数据的导入,导入要注意的三个问题:
1、数据文件的存在时要删除,同时多个用户同时导入相同文件名的文件时的文件的处理;
2、要读取该excel文件中选中的sheet的内容;
3、当系统长期使用后,不能造成大量的垃圾文件。
主要代码如下:
// add random number before filename (1)
string filename = new Random().Next() + fuExcel.FileName;
//save file to given directory
string path = System.AppDomain.CurrentDomain.BaseDirectory + @"uploadExcels/" + filename;
if(File.Exists(path)) // delete the file if it exists. (3)
File.Delete(path);
{
Request.Files[0].SaveAs(path);
}
catch (Exception ex)
{
PreExitExcel();
ShowMessageBox("上传文件失败,服务器没有开启写入权限!+" + ex.Message);
return;
}
// get the name of activesheet of the excel (2)
string sheetName;
Excel.Application excel = new Application();try
{
System.Reflection.Missing miss = System.Reflection.Missing.Value;
excel.Workbooks.Open(path, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss);
sheetName = ((Excel.Worksheet)excel.ActiveWorkbook.ActiveSheet).Name;
}
finally
{
excel.Workbooks.Close();
excel.Quit();
excel = null;
}
// open the excel file as data source.
DataSet ds = new DataSet();
string connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""", path);
OleDbConnection connection = new OleDbConnection(connectionString);
OleDbDataAdapter adapter = new OleDbDataAdapter("select * from [" + sheetName + "$]", connection);
adapter.Fill(ds);
// process the data source
......
// delete the file after usage. (3)
try
{
File.Delete(path);
}
catch
{}