【下载文档:  .Net读取Excel 返回DataTable实例代码.txt 】

---

.Net读取Excel 返回DataTable实例代码
复制代码 代码如下: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 noInput = new List(); 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", ""); // return; //} } else { Page.ClientScript.RegisterStartupScript(Page.ClientScript.GetType(), "myscript", ""); 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", ""); } } } else { Page.ClientScript.RegisterStartupScript(Page.ClientScript.GetType(), "myscript", ""); return; } }); } protected void BtnClose_Click(object sender, EventArgs e) { Page.ClientScript.RegisterStartupScript(Page.ClientScript.GetType(), "myscript", ""); } ///

/// 连接到Excel ///

/// 文件路径 /// sheet名字 /// 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; } ///

/// 获得Excel中的所有sheetname ///

/// 文件路径 /// 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; } }}