//用户导入 public void BatchImport() { bool isSuccess; string msg; DataTable dt = ExcelToDataTable.Convert(StringHelper.AddSafe(RequestHelper.GetForm <string>("file")), out isSuccess, out msg); if (!isSuccess) //return Json(new { flag = false, msg = msg }, JsonRequestBehavior.AllowGet); { Response.Write(Newtonsoft.Json.JsonConvert.SerializeObject(new { flag = false, msg = msg })); } else { List <object> dataList = new List <object>(); foreach (DataRow dr in dt.Rows) { string realname = dr[0] + ""; string username = dr[1] + ""; string mobile = dr[2] + ""; string birthday = dr[3] + ""; //string email = dr[2] + ""; //string tel = dr[3] + ""; //string qq = dr[4] + ""; if (string.IsNullOrEmpty(realname)) { Response.Write(Newtonsoft.Json.JsonConvert.SerializeObject(new { flag = false, msg = "姓名不能为空" })); Response.End(); } if (string.IsNullOrEmpty(username)) { Response.Write(Newtonsoft.Json.JsonConvert.SerializeObject(new { flag = false, msg = "用户名/微信昵称不能为空" })); Response.End(); } if (string.IsNullOrEmpty(mobile)) { Response.Write(Newtonsoft.Json.JsonConvert.SerializeObject(new { flag = false, msg = "手机号不能为空" })); Response.End(); } DateTime date = DateTime.Now; if (!DateTime.TryParse(birthday, out date)) { Response.Write(Newtonsoft.Json.JsonConvert.SerializeObject(new { flag = false, msg = "生日必须为日期格式(如1999-01-01)" })); Response.End(); } //if (string.IsNullOrEmpty(email)) //{ // Response.Write(Newtonsoft.Json.JsonConvert.SerializeObject(new { flag = false, msg = "Email不能为空" })); // Response.End(); //} //dataList.Add(new { row = dt.Rows.IndexOf(dr),RealName=realname, UserName = username, Mobile = mobile, Email = email, Tel = tel,QQ=qq }); dataList.Add(new { row = dt.Rows.IndexOf(dr), RealName = realname, UserName = username, Mobile = mobile, Birthday = birthday }); } Response.Write(Newtonsoft.Json.JsonConvert.SerializeObject(new { flag = true, msg = "", list = dataList })); } Response.End(); }
/// <summary> /// 根据excel文件路径执行生成操作,并返回生成的sql /// </summary> /// <param name="excelFilePath"></param> /// <returns></returns> public virtual String Build(String excelFilePath) { var excelHelper = new ExcelToDataTable(); excelHelper.Init(excelFilePath); using (var ds = excelHelper.GetDataTables()) { var sb = new StringBuilder(); for (var i = 0; i < ds.Tables.Count; i++) { sb.Append(BuildOne(ds.Tables[i])); } return(sb.ToString()); } }
private void skinButton1_Click(object sender, EventArgs e) { try { OpenFileDialog op = new OpenFileDialog(); op.Filter = "Excel(97-2003)文件|*.xls|所有文件|*.*"; op.Title = "打开文件夹"; string path = null; op.InitialDirectory = "d:\\"; //最初从D盘开始查找文件,测试文件放置于本文件夹。 op.FilterIndex = 1; if (op.ShowDialog() == DialogResult.OK) //判断路径是否正确 { path = op.FileName; } string name = GetExcelFile.GetFile(path); //获取Excel文件。 string Tsql = "SELECT * FROM [" + name + "]"; DataTable table = ExcelToDataTable.Redatatable(path, Tsql).Tables[0]; //将Excel转换为dataset类型并赋值于table Class1 class1 = new Class1(); Dictionary <string, string> dic = new Dictionary <string, string>(); dic.Add("姓名", "varchar(20)"); dic.Add("课程名", "varchar(20)"); dic.Add("辅导员", "varchar(50)"); string tablename = string.Format("{0}{1}{2}课程考勤表", skinWaterTextBox1.Text.ToString(), skinWaterTextBox2.Text.ToString(), skinWaterTextBox3.Text.ToString()); class1.CreateDataTable("优课堂", tablename, dic, "学号"); string cons = PubConstant.ConnectionString; SqlConnection con = new SqlConnection(cons); con.Open(); for (int i = 1; i < table.Rows.Count; i++) { string sql = string.Format("insert into {4}{5}{6}课程考勤表(学号,姓名,课程名,辅导员) values ('{0}','{1}','{2}','{3}')", table.Rows[i][0], table.Rows[i][1], table.Rows[i][2], table.Rows[i][3], skinWaterTextBox1.Text.ToString(), skinWaterTextBox2.Text.ToString(), skinWaterTextBox3.Text.ToString()); SqlCommand cmd1 = new SqlCommand(sql, con); cmd1.ExecuteNonQuery(); } con.Close(); skinDataGridView1.DataSource = selectData.table(tablename); } catch (Exception ex) { MessageBox.Show(ex.Message); } }