复制代码 代码如下: using System; using Microsoft.SharePoint; using Microsoft.SharePoint.WebControls; using System.Data; using System.IO; using System.Linq; using System.Web; using System.Collections; using System.Data.OleDb; using NuctechProject.DTO.Bll; using System.Collections.Generic; namespace NuctechProject.Layouts.Project { public partial class IntroductionPlan : LayoutsPageBase { string url = Common.rootUrl; private string _strConn; //导入excel时的连接 string pmurl = Common.proUrl; private UserBLL bll = new UserBLL(); protected void Page_Load(object sender, EventArgs e) { hidProid.Value = Request.QueryString["proid"]; } protected void BtnOK_Click(object sender, EventArgs e) { DataTable excelTable = null;
SPSecurity.RunWithElevatedPrivileges(delegate { if (BaseInfoTemplateFile.HasFile) { List<string> noInput = new List<string>(); string strLoginName = HttpContext.Current.User.Identity.Name; //获取用户名 string folderTemp = strLoginName.Substring(strLoginName.LastIndexOf('\\') + 1); try { string extension = Path.GetExtension(BaseInfoTemplateFile.FileName); //获取文件的后缀 if (extension != null) { string fileException = extension.ToLower(); if (fileException == ".xlsx" || fileException == ".xls") { #region 读取Excel string fileFolder = Server.MapPath("~/_layouts/15/images/" + folderTemp + "Upfile/"); if (!Directory.Exists(fileFolder)) //根目录 { Directory.CreateDirectory(fileFolder); //判断上传目录是否存在 自动创建 } BaseInfoTemplateFile.SaveAs(Server.MapPath("~/_layouts/15/images/" + folderTemp + "Upfile/" + BaseInfoTemplateFile.FileName)); string strFilepathNmae = Server.MapPath("~/_layouts/15/images/" + folderTemp + "Upfile/" + BaseInfoTemplateFile.FileName); string strExcel = ExcelSheetName(strFilepathNmae)[0].ToString(); excelTable = ExcelDataSource(strFilepathNmae, strExcel).Tables[0]; #endregion //data是excel的数据 DataTable data = ExcelDataSource(strFilepathNmae, strExcel).Tables[0]; //try //{ if (data != null) {
foreach (DataRow row in data.Rows) { //读取 }
} //} //catch (Exception) //{ // Page.ClientScript.RegisterStartupScript(Page.ClientScript.GetType(), "myscript", "<script type='text/javascript'>$.ligerDialog.closeWaitting();alert('Excel表列名与系统不符合,请检查Excel表列名!');</script>"); // return; //} } else { Page.ClientScript.RegisterStartupScript(Page.ClientScript.GetType(), "myscript", "<script type='text/javascript'>$.ligerDialog.closeWaitting();alert('您选择的文件不是Excel格式!');</script>"); return; } } } finally //最终要把临时存储的文件删除 { string strFileFolder = Server.MapPath("~/_layouts/15/images/" + folderTemp + "Upfile/"); if (Directory.Exists(strFileFolder)) //根目录 { //Directory.CreateDirectory(strFileFolder);//判断上传目录是否存在 自动创建 Directory.Delete(strFileFolder, true); } else { Page.ClientScript.RegisterStartupScript(Page.ClientScript.GetType(), "myscript", "<script type='text/javascript'>ReturnPageValue();</script>"); } } } else { Page.ClientScript.RegisterStartupScript(Page.ClientScript.GetType(), "myscript", "<script type='text/javascript'>$.ligerDialog.closeWaitting();alert('请选择导入文件!');</script>"); return; } }); } protected void BtnClose_Click(object sender, EventArgs e) { Page.ClientScript.RegisterStartupScript(Page.ClientScript.GetType(), "myscript", "<script type='text/javascript'>ReturnPageValue();</script>"); } /// <summary> /// 连接到Excel /// </summary> /// <param name="filepath">文件路径</param> /// <param name="sheetname">sheet名字</param> /// <returns></returns> public DataSet ExcelDataSource(string filepath, string sheetname) { _strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties='Excel 12.0;HDR=YES'"; new OleDbConnection(_strConn); var oada = new OleDbDataAdapter("select * from [" + sheetname + "]", _strConn); var ds = new DataSet(); oada.Fill(ds); return ds; } /// <summary> /// 获得Excel中的所有sheetname /// </summary> /// <param name="filepath">文件路径</param> /// <returns></returns> public ArrayList ExcelSheetName(string filepath) { _strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties='Excel 12.0;HDR=YES'"; var al = new ArrayList(); var conn = new OleDbConnection(_strConn); conn.Open(); DataTable sheetNames = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); conn.Close(); if (sheetNames != null) foreach (DataRow dr in sheetNames.Rows) { al.Add(dr[2]); } return al; } } }
|