上传Excel文件到Controller后调取B层读取文件方法
listUser = userbll.ExcelToDataTable(strSavaPath, strPaperId, tablename);BLL:
public List<t_user> ExcelToDataTable(string strSavaPath, string strPaperId, string tablename) { string strConn; string strFileType = System.IO.Path.GetExtension(strSavaPath); if (string.IsNullOrEmpty(strFileType)) return null; if (strFileType == ".xls") { strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strSavaPath + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\""; } else { strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + strSavaPath + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\""; } OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); OleDbDataAdapter adpter = new OleDbDataAdapter("select * from [" + strPaperId + "$]", strConn); DataSet myDataset = new DataSet(); try { adpter.Fill(myDataset, tablename); } catch (Exception) { throw new Exception("配置文件的sheet名称配置错误"); } finally { if (conn.State == ConnectionState.Open) { conn.Close(); adpter.Dispose(); conn.Dispose(); } } System.Data.DataTable table = myDataset.Tables[tablename].DefaultView.ToTable(); //Excel列头的汉字转为字段名 table.Columns["账号"].ColumnName = "userID"; table.Columns["密码"].ColumnName = "pwd"; table.Columns["姓名"].ColumnName = "userName"; table.Columns["电话"].ColumnName = "tel"; table.Columns["邮箱"].ColumnName = "mail"; table.Columns["备注"].ColumnName = "remark"; //拿到数据集合 System.Data.DataTable newtable = table.Copy(); //转泛型 List<t_user> listUser = new List<t_user>(); listUser = DAL.DatatableToList.ConvertToList<t_user>(newtable); return listUser; }