上传Excel 表内容到数据库用GridView显示出来代码,Asp.Net代码库,德仔网

代码语言

代码分类

【Asp.Net】上传Excel 表内容到数据库用GridView显示出来

作者: / 发布于2014/10/21/ 457


	using System.IO;
	//判断是否上传文件，若上传，上传的文件是否存在、格式是否正确。fileupload1为FileUpload 控件ID
	 protected bool IsFileupload(FileUpload fileupload1, out string message, out string filepath)
	 {
	 if (fileupload1.HasFile) //判断该控件是否包含文件
	 {
	 string fullfilename = fileupload1.PostedFile.FileName;
	 string filename = fullfilename.Substring(fullfilename.LastIndexOf("\\") + 1);
	 string type = filename.Substring(filename.LastIndexOf('.') + 1); //截取文件名后缀
	 if (type.ToLower() == "xls")
	 {
	 string basepath = this.Server.MapPath("~\\Temp\\教师信息"); //保存到文件夹
	 string subpath = basepath + "\\" + DateTime.Now.ToString("yyyyMMddhhmmss") + filename; //修改上传文件名字，避免重复名称
	 if (!File.Exists(subpath))
	 {
	 if (!Directory.Exists(Server.MapPath("~\\Temp")))
	 Directory.CreateDirectory(basepath);
	 if (!Directory.Exists(basepath))
	 Directory.CreateDirectory(basepath);
	 filepath = subpath;
	 message = "";
	 return true;
	 }
	 else
	 {
	 message = "您上传的文件已存在，请重命名后再上传！";
	 filepath = "";
	 return false;
	 }
	 }
	 else
	 {
	 message = "您上传的文件格式不正确！";
	 filepath = "";
	 return false;
	 }
	 }
	 else
	 {
	 message = "您未上传文件或上传的文件为空！";
	 filepath = "";
	 return false;
	 }
	 }
	
	using NPOI.HSSF.UserModel;
	using NPOI.SS.UserModel;
	using System.Collections;
	//将Excel表中数据转换为DataSet 类型
	 public static DataSet ExcelToDatatable(string filepath)
	 {
	 HSSFWorkbook hssfworkbook = null;
	 using (FileStream file = new FileStream(filepath, FileMode.Open, FileAccess.Read))
	 {
	 hssfworkbook = new HSSFWorkbook(file);
	 }
	
	 NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
	 IEnumerator rows = sheet.GetRowEnumerator();
	 DataSet ds = new DataSet();
	 DataTable dt = new DataTable();
	 int rowIndex = 1;
	 while (rows.MoveNext())
	 {
	 NPOI.SS.UserModel.IRow row = (NPOI.HSSF.UserModel.HSSFRow)rows.Current;
	 DataRow dr = null;
	
	 if (rowIndex == 1)
	 {
	 for (int i = 0; i < row.LastCellNum; i++)
	 {
	 ICell cell = row.GetCell(i);
	 string value;
	 if (cell == null)
	 value = string.Empty;
	 else
	 value = cell.ToString();
	 dt.Columns.Add(value);
	 }
	 if (dr != null)
	 dt.Rows.Add(dr);
	 }
	 else
	 {
	 for (int i = 0; i < row.LastCellNum; i++)
	 {
	 ICell cell = row.GetCell(i);
	 string value;
	 if (cell == null)
	 value = string.Empty;
	 else
	 value = cell.ToString();
	 if(dr == null)
	 dr = dt.NewRow();
	 dr[i] = value;
	 }
	 if (dr != null)
	 dt.Rows.Add(dr);
	 }
	 rowIndex++;
	 }
	 ds.Tables.Add(dt);
	 return ds;
	 }
	
	//导入按钮事件
	 protected void btn_users_Click(object sender, EventArgs e)
	 {
	 string message = "";
	 string filepath = "";
	 if (IsFileupload(FU_users, out message, out filepath))
	 {
	 FU_users.PostedFile.SaveAs(filepath);
	 DataSet ds = NPOITool.ExcelToDatatable(filepath);
	 if (ds != null && ds.Tables.Count > 0)
	 {
	 try
	 {
	 // 数据有效性判断
	 string error = "";//姓名是否为空\是否重复
	 List<string> namelist = new List<string>();
	 for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
	 {
	 DataRow dr = ds.Tables[0].Rows[i];
	 string name = dr[0].ToString();
	 if (string.IsNullOrEmpty(name))
	 {
	 error += (i + 2).ToString() + ",";
	 }
	 if (namelist.Contains(name))
	 {
	 error += (i + 2).ToString() + ",";
	 }
	 else
	 namelist.Add(name);
	 }
	 if (error == "")
	 {
	 //把数据添加到数据库
	 }
	 else
	 {
	 error = "以下行数姓名为空或重复：" + error.TrimEnd(',') + "\\n";
	 ClientScript.RegisterStartupScript(this.GetType(), "alert", "<script language= 'javascript'>alert('" + error + "')</script>");
	 File.Delete(filepath);
	 }
	 }
	 catch
	 {
	 ClientScript.RegisterStartupScript(this.GetType(), "alert", "<script language= 'javascript'>alert('请按模板要求填写信息。')</script>");
	 File.Delete(filepath);
	 }
	 }
	 else
	 {
	 ClientScript.RegisterStartupScript(this.GetType(), "alert", "<script language= 'javascript'>alert('文件异常。')</script>");
	 File.Delete(filepath);
	 }
	 }
	 else
	 {
	 ClientScript.RegisterStartupScript(this.GetType(), "alert", "<script language= 'javascript'>alert('" + message + "')</script>");
	 }
	 }

评论列表

本站所提供的代码，版权归原作者所有，若有侵犯作者版权，请与我们联系，我们将立即删除或修改。谢谢!
本站所有代码发布及提供者。

试试其它关键字

　上传Excel　　GridView　

同语言下

可能有用的

贡献的其它代码Label