//用户导入
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);
}
}
