java上传xls文件
2024-09-21 23:15:23
using System; using System.Collections.Generic; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using PublicProject.ENTITY; using System.Data; using System.IO; using System.Data.OleDb; using PublicProject.BLL; public partial class 使用Excel读取数据 : System.Web.UI.Page { //集合 private List<School> schoolList = new List<School>(); private DataSet dsall = new DataSet(); private int count = 0; private SchoolBll schoolBll = new SchoolBll(); protected void Page_Load(object sender, EventArgs e) { } //public void ReaderXls() //{ // string xlsFilePath = @"F:\项目代码\公用架构\PublicProjectSolution\PublicProjectWeb\xls\DateReader.xls"; // Excel //} /// <summary> /// 单击导入按钮的事件 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void BtnImport_Click1(object sender, EventArgs e) { string fileName = string.Empty; try { fileName= UpLoadXls(FileExcel); ImportXlsToData(fileName);//将XLS文件的数据导入数据库 if (fileName != string.Empty && System.IO.File.Exists(fileName)) { System.IO.File.Delete(fileName);//删除上传的XLS文件 } LblMessage.Text = "数据导入成功!"; this.gvimport.DataSource = schoolList; this.gvimport.DataBind(); this.lbimport.Text = schoolList.Count.ToString(); int all = Convert.ToInt32(this.lballcount.Text.ToString()); int import = Convert.ToInt32(this.lbimport.Text.ToString()); int unimport = all - import; if (unimport > count) { this.lbunimport.Text = "," + unimport.ToString() + "条,由于传输原因未导入到数据库!"; } } catch (Exception ex) { LblMessage.Text=ex.Message; } } /// <summary> /// 上传Excel文件 /// </summary> /// <param name="inputfile">上传的控件名</param> /// <returns></returns> private string UpLoadXls(System.Web.UI.HtmlControls.HtmlInputFile inputfile) { string orifilename = string.Empty; string uploadfilepath = string.Empty; string modifyfilename = string.Empty; string fileExtend = "";//文件扩展名 int fileSize = 0;//文件大小 try { if (inputfile.Value != string.Empty) { //得到文件的大小 fileSize = inputfile.PostedFile.ContentLength; if (fileSize == 0) { throw new Exception("导入的Excel文件大小为0,请检查是否正确!"); } //得到扩展名 fileExtend = inputfile.Value.Substring(inputfile.Value.LastIndexOf(".") + 1); if (fileExtend.ToLower() != "xls") { throw new Exception("你选择的文件格式不正确,只能导入EXCEL文件!"); } //路径 uploadfilepath = Server.MapPath("~/xls/XlsUpLodes"); //新文件名 modifyfilename = System.Guid.NewGuid().ToString(); modifyfilename += "." + inputfile.Value.Substring(inputfile.Value.LastIndexOf(".") + 1); //判断是否有该目录 System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(uploadfilepath); if (!dir.Exists) { dir.Create(); } orifilename = uploadfilepath + "\\" + modifyfilename; //如果存在,删除文件 if (File.Exists(orifilename)) { File.Delete(orifilename); } // 上传文件 inputfile.PostedFile.SaveAs(orifilename); } else { throw new Exception("请选择要导入的Excel文件!"); } } catch (Exception ex) { throw ex; } return orifilename; } /// <summary> /// 将上传文件中的数据读取到数据库中 /// </summary> /// <param name="fileName">上传的文件的地址</param> private void ImportXlsToData(string fileName) { try { if (fileName == string.Empty) { throw new ArgumentNullException("Excel文件上传失败!"); } string oleDBConnString = String.Empty; oleDBConnString = "Provider=Microsoft.Jet.OLEDB.4.0;"; oleDBConnString += "Data Source="; oleDBConnString += fileName; oleDBConnString += ";Extended Properties=Excel 8.0;"; OleDbConnection oleDBConn = null; OleDbDataAdapter oleAdMaster = null; DataTable m_tableName = new DataTable(); DataSet ds = new DataSet(); oleDBConn = new OleDbConnection(oleDBConnString); oleDBConn.Open(); m_tableName = oleDBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (m_tableName != null && m_tableName.Rows.Count > 0) { m_tableName.TableName = m_tableName.Rows[0]["TABLE_NAME"].ToString(); } string sqlMaster; sqlMaster = " SELECT * FROM [" + m_tableName.TableName + "]"; oleAdMaster = new OleDbDataAdapter(sqlMaster, oleDBConn); oleAdMaster.Fill(ds, "m_tableName"); oleAdMaster.Dispose(); oleDBConn.Close(); oleDBConn.Dispose(); AddDatasetToSQL(ds, 8); } catch (Exception ex) { throw ex; } } /// <summary> /// 将Dataset的数据导入数据库 /// </summary> /// <param name="pds">数据集</param> /// <param name="Cols">数据集列数</param> /// <returns></returns> private bool AddDatasetToSQL(DataSet pds, int Cols) { int ic, ir; ic = pds.Tables[0].Columns.Count; if (pds.Tables[0].Columns.Count < Cols) { throw new Exception("导入Excel格式错误!Excel只有" + ic.ToString() + "列"); } ir = pds.Tables[0].Rows.Count; if (pds != null && pds.Tables[0].Rows.Count > 0) { for (int i = 0; i < pds.Tables[0].Rows.Count; i++) { School model = new School(); string schooolid = pds.Tables[0].Rows[i][3].ToString(); model.Email = pds.Tables[0].Rows[i][0].ToString(); model.Pass= pds.Tables[0].Rows[i][1].ToString(); model.RealName = pds.Tables[0].Rows[i][2].ToString(); model.School1 = pds.Tables[0].Rows[i][3].ToString(); model.ColSchool = pds.Tables[0].Rows[i][4].ToString(); model.SubName = pds.Tables[0].Rows[i][5].ToString(); model.GradeName = pds.Tables[0].Rows[i][6].ToString(); model.ClassName = pds.Tables[0].Rows[i][7].ToString(); //excel的格式:必须是英文列头 //EMAIL 密码 姓名 学校 学院 专业 年级 班级 //email PASS REAL_NAME SCHOOL COL_NAME SUB_NAME GRADE CLASS // 0 1 2 3 4 5 6 7 schoolBll.Add(model); schoolList.Add(model); } //全部信息 this.gvgetall.DataSource = schoolList; this.gvgetall.DataBind(); dsall = pds; this.lballcount.Text = pds.Tables[0].Rows.Count.ToString(); } else { throw new Exception("导入数据为空!"); } return true; } /// <summary> /// 插入数据到数据库 /// </summary> //public void Add(ccwu.Model.T_RESUME_INFO model) //{ // string sql = "select * from T_STUDENT_INFO where email='" + model.EMAIL.ToString() + "'";//根据一个号去查询 // DataSet ds = ccwu.DBUtility.DbHelperSQL.Query(sql.ToString()); // // int count = 0; // if (ds.Tables[0].Rows.Count == 0) // { // //insert into PersonRecord // ccwu.DAL.T_STUDENT_INFO dalstudent = new ccwu.DAL.T_STUDENT_INFO(); // ccwu.DAL.T_RESUME_INFO dalresume = new ccwu.DAL.T_RESUME_INFO(); // ccwu.Model.T_STUDENT_INFO m = new ccwu.Model.T_STUDENT_INFO(); // m.EMAIL = model.EMAIL.ToString(); // m.PASS = model.pass.ToString(); // m.SCHOOL_INFO_ID = Convert.ToInt32(model.SCHOOL_INFO_ID.ToString()); // //向学生注册表里 // int lastid = dalstudent.Add(m); // model.STUDENT_INFO_ID = lastid; // //向简历表里添加信息 // dalresume.AddForAll(model); // list.Add(model); // } // if (ds.Tables[0].Rows.Count > 0) // { // count = count + 1; // } //} protected void gvgetall_PageIndexChanging(object sender, GridViewPageEventArgs e) { } protected void gvimport_PageIndexChanging(object sender, GridViewPageEventArgs e) { } }
最新文章
- 基于浏览器的HTML5地理定位
- 酷炫放大镜canvas实现
- HBase性能优化方法总结(转)
- 字符串匹配之KMP算法
- Android 中Webview 自适应屏幕
- Adb shell 常用命令
- Flume-ng源码解析之Source组件
- JSP入门 分页
- Velocity(7)——velocity进阶用法
- Docker仓库
- Shell中read的常用方式
- 玩转DWZ (一)---项目中怎么使用dwz
- 【翻译】七个习惯提高Python程序的性能
- Java常用API——时间类
- pom.xml文件最详细的讲解
- Docker学习笔记之docker-save vs docker-export vs docker-commit
- LINUX下PHP网页生成快照(截屏)(xvfb and wkhtmltoimage)
- WebApi的过滤器
- Hadoop 命令 &;&; Web UI
- cf 295 div 2 B (bfs)
热门文章
- edgesForExtendedLayout,automaticallyAdjustsScrollViewInsets, extendedLayoutIncludesOpaqueBars的影响
- Hadoop Pipeline详解[摘抄]
- MYCAT 配置(转)
- Ninject使用介绍
- Stream 和 byte[] 之间的转换
- Android数据库的运用
- fsn文件解析(C#)
- MySQL服务 - 客户端工具mysql及mysqladmin使用介绍
- IE8、IE9浏览器下报:JSON未定义 解决方法
- 使用git新建分支以及管理分支