.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<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;
}
}
}
上一篇: 简述Java图像倾斜方法及实例 原创
下一篇: C#中利用代理实现观察者设计模式详解