一、方法一:
方法描述:直接独缺信息,然后处理
1.参考网址
(1)http://www.oschina.net/code/snippet_156249_4854
(2)http://www.csharpwin.com/dotnetspace/10575r6876.shtml
(3)http://www.cnblogs.com/youngerliu/p/3229357.html
2.My eg:
(1)前台:
<asp:FileUpload ID="FileImport" CssClass="input" runat="server" Width="200px" />
<asp:Button ID="btnImport" runat="server" Text="确定导入"/>
(2)后台:
记得引用:
using org.in2bits.MyXls;
using System.IO;
/// <summary>
/// 读取导入的数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
void btnImport_Click(object sender, EventArgs e)
{
try
{
//判断是否已经选择了文件
if (string.IsNullOrEmpty(FileImport.PostedFile.FileName))
{
throw new Exception("请选择一个文件!");
}
//判断是否是xls为后缀的Excel文件
string strExtName = System.IO.Path.GetExtension(FileImport.PostedFile.FileName).ToUpper();
if (strExtName != ".XLS")
{
throw new Exception("请选择一个扩展名为.xls的Excel文件!");
}
System.IO.FileInfo fiExcel = new System.IO.FileInfo(FileImport.PostedFile.FileName);
string fileName = Server.MapPath("../") + DateTime.Now.ToString("yyyyMMddHHmmss") + "_" + fiExcel.Name;
FileImport.PostedFile.SaveAs(fileName);
XlsDocument xls = new XlsDocument(fileName);//加载外部Excel
//获得Excel中的第一个工作页
Worksheet sheet = xls.Workbook.Worksheets[0];
//读取数据 循环每sheet工作页的每一行,不读取第一行
string strPracName;
string strCertNum;
int count = 0;
for (int i = 2; i < sheet.Rows.Count; i++)
{
//解释:获得指定工作页行集合的指定行的指定列的值
strPracName = sheet.Rows[ushort.Parse(i.ToString())].GetCell(1).Value.ToString();
strCertNum = sheet.Rows[ushort.Parse(i.ToString())].GetCell(2).Value.ToString();
Practitioner objPrac = BaseBLLFactory.CreateService<PractitionerService>().GetPractitionerByIdCertAndName(strCertNum, strPracName);
if (objPrac != null)
{
strReturnTrue += "'" + objPrac.IdCertNum + "',";
count++;
}
else
{
strReturnFalse += strCertNum + ",";
}
}
if (!string.IsNullOrEmpty(strReturnFalse))
{
ShowErrorMessage(strReturnFalse);
}
else
{
ShowSuccessMessage("成功导入" + count.ToString() + "条信息.");
PracEducationDataBind();
}
if (File.Exists(fileName))
{
File.Delete(fileName);
}
}
catch (Exception err)
{
ShowErrorMessage(err.Message);
}
}
3.Asp.Net 获取FileUpload控件的文件路径、文件名、扩展名
string fileNameNo = Path.GetFileName(FileUploadImg.PostedFile.FileName); //获取文件名和扩展名
string DirectoryName = Path.GetDirectoryName(FileUploadImg.PostedFile.FileName); //获取文件所在目录
string Extension = Path.GetExtension(FileUploadImg.PostedFile.FileName); //获取扩展名
string fileName = Path.GetFileNameWithoutExtension(FileUploadImg.PostedFile.FileName); //获取文件名(不包括扩展名)
string fullPath = Path.GetFullPath(FileUploadImg.PostedFile.FileName); //获取文件的绝对路径
string PathRoot = Path.GetPathRoot(FileUploadImg.PostedFile.FileName); //获取文件所在地分区
二、方法二:
方法描述:把Excel中的信息转化为DataTable,然后读取DataTable!
void btnImp_Click(object sender, EventArgs e)
{
try
{
if (fileUpLoad.HasFile)
{
string strFileName = fileUpLoad.FileName; //获取初始文件名
int index = strFileName.LastIndexOf("."); //取得文件名中最后一个"."的索引
string fileContentType = strFileName.Substring(index); //获取文件扩展名
if (fileContentType == ".xls" || fileContentType == ".xlsx")
{
string strFilePath = SysConfig.TestPeopleExcelPhysicalPath + Guid.NewGuid().ToString() + ".xls";
fileUpLoad.SaveAs(strFilePath);
System.Data.DataTable dt = ExecleDataTable(strFilePath);
for (int i = 0; i < dt.Rows.Count; i++)
{
TestPeople obj = new TestPeople();
obj.Birthday = Convert.ToDateTime(dt.Rows[i]["出生年月"]);
obj.CertificateNum = dt.Rows[i]["资格证号"].ToString();
obj.IdNum = dt.Rows[i]["身份证号"].ToString();
obj.Position = dt.Rows[i]["职责"].ToString();
if (dt.Rows[i]["性别"].ToString() == "女")
obj.Sex = 2;
else
obj.Sex = 1;
obj.StartTime = Convert.ToDateTime(dt.Rows[i]["开始时间"]);
obj.EndTime = Convert.ToDateTime(dt.Rows[i]["结束时间"]);
obj.TestPeopleName = dt.Rows[i]["检测员姓名"].ToString();
obj.TestStation = dt.Rows[i]["检测站"].ToString();
BaseBLLFactory.CreateService<TestPeopleService>().AddTestPeople(obj);
}
TestPeopleDataBind();
}
else
JavaScriptUtil.Alert(this, "请选择EXCEL文件!");
}
else
throw new Exception("请选择需要导入的文件!");
}
catch (Exception err)
{
JavaScriptUtil.Alert(this, err.Message);
}
}
/// <summary>
/// 把Excel中的信息转化为DataTable
/// </summary>
/// <param name="filenameurl"></param>
/// <param name="table"></param>
/// <returns></returns>
public System.Data.DataTable ExecleDataTable(string filenameurl)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filenameurl + ";Extended Properties=Excel 8.0";
OleDbConnection conn = new OleDbConnection(strConn);
OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn);
DataSet ds = new DataSet();
odda.Fill(ds, "table");
return ds.Tables[0];
}