private void 将系统数据导出到ExcelToolStripMenuItem_Click(object sender, EventArgs e)
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "xls文件|*.xls";
DialogResult result = saveFileDialog.ShowDialog();
string localFilePath = "";
if (result == DialogResult.OK)
{
//获得文件路径
localFilePath = saveFileDialog.FileName.ToString();
int state = 0;
DataTable dt = config.querySql("select rid,name,tel,qq,mail,school,major,leve,rstate,consult,source,date,record,backlog,visa,tuition,total,frontfee,frontdate,endfee,visafee,visadate,endtime from main", ref state);
dt.Columns["rid"].ColumnName = "资源号";
dt.Columns["name"].ColumnName = "姓名";
dt.Columns["qq"].ColumnName = "QQ/微信";
dt.Columns["tel"].ColumnName = "电话";
dt.Columns["mail"].ColumnName = "邮箱";
dt.Columns["school"].ColumnName = "在读学校";
dt.Columns["major"].ColumnName = "在读专业";
dt.Columns["leve"].ColumnName = "级别";
dt.Columns["rstate"].ColumnName = "资源状态";
dt.Columns["consult"].ColumnName = "顾问";
dt.Columns["source"].ColumnName = "资源来源";
dt.Columns["date"].ColumnName = "签约日期";
dt.Columns["record"].ColumnName = "回访记录";
dt.Columns["backlog"].ColumnName = "服务费其他说明";
dt.Columns["visa"].ColumnName = "签证费缴纳情况";
dt.Columns["tuition"].ColumnName = "学费押金缴纳情况";
dt.Columns["total"].ColumnName = "总服务费用";
dt.Columns["frontfee"].ColumnName = "服务费定金";
dt.Columns["frontdate"].ColumnName = "定金缴费日期";
dt.Columns["endfee"].ColumnName = "录取后费用";
dt.Columns["visafee"].ColumnName = "录取后缴费日期";
dt.Columns["visadate"].ColumnName = "签证后费用";
dt.Columns["endtime"].ColumnName = "签证后缴费日期";
//rid id school course senddate account enterinfor yasi language ldate getdate result
DataTable dt2 = config.querySql("select rid,school,course,senddate,account,enterinfor,yasi,language,ldate,getdate,result from minor", ref state);
dt2.Columns["rid"].ColumnName = "资源号";
dt2.Columns["school"].ColumnName = "申请学校";
dt2.Columns["course"].ColumnName = "申请课程";
dt2.Columns["senddate"].ColumnName = "递交日期";
dt2.Columns["account"].ColumnName = "网申账号/密码";
dt2.Columns["enterinfor"].ColumnName = "录取条件/结果";
dt2.Columns["yasi"].ColumnName = "雅思考试时间或不出国备注";
dt2.Columns["language"].ColumnName = "配语言学校";
dt2.Columns["ldate"].ColumnName = "配语言日期";
dt2.Columns["getdate"].ColumnName = "递签日期";
dt2.Columns["result"].ColumnName = "签证结果";
ExcelUtility.DataTableToExcel(dt, dt2, localFilePath);
MessageBox.Show("导出成功！", "提示");
}
}
private void 将Excel模板内容导入系统ToolStripMenuItem_Click(object sender, EventArgs e)
{
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.InitialDirectory = "";
openFileDialog.Filter = "Excel文件|*.xls;*.xlsx";
openFileDialog.RestoreDirectory = true;
openFileDialog.FilterIndex = 1;
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
int dt = 0;
int tss = 0;
SqlConnection con = new SqlConnection(config.str_con);
con.Open();
SqlTransaction tran = con.BeginTransaction();//先实例SqlTransaction类，使用这个事务使用的是con 这个连接，使用BeginTransaction这个方法来开始执行这个事
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.Transaction = tran;
try
{
DataTable ts = null;
DataTable s = ExcelUtility.ExcelToDataTable(openFileDialog.FileName, ref ts, true);
int count = s.Rows.Count;
for (int i = 0; i < count; i++)//得到总行数并在之内循环
{
dt = i;
string rid = s.Rows[i]["资源号"].ToString();
string name = s.Rows[i]["姓名"].ToString();
string qq = s.Rows[i]["QQ/微信"].ToString();
string tel = s.Rows[i]["电话"].ToString();
string mail = s.Rows[i]["邮箱"].ToString();
string school = s.Rows[i]["在读学校"].ToString();
string major = s.Rows[i]["在读专业"].ToString();
string leve = s.Rows[i]["级别"].ToString();
string rstate = s.Rows[i]["资源状态"].ToString();
string consult = s.Rows[i]["顾问"].ToString();
string source = s.Rows[i]["资源来源"].ToString();
string date = s.Rows[i]["签约日期"].ToString();
string record = s.Rows[i]["回访记录"].ToString();
string backlog = s.Rows[i]["服务费其他说明"].ToString();
string visa = s.Rows[i]["签证费缴纳情况"].ToString();
string tuition = s.Rows[i]["学费押金缴纳情况"].ToString();
string total = s.Rows[i]["总服务费用"].ToString();
string frontfee = s.Rows[i]["服务费定金"].ToString();
string frontdate = s.Rows[i]["定金缴费日期"].ToString();
string endfee = s.Rows[i]["录取后费用"].ToString();
string visafee = s.Rows[i]["录取后缴费日期"].ToString();
string visadate = s.Rows[i]["签证后费用"].ToString();
string endtime = s.Rows[i]["签证后缴费日期"].ToString();
cmd.CommandText = "insert into main(rid,name,qq,tel,mail,school,major,leve,rstate,consult,source,date,record,backlog,visa,tuition,total,frontfee,frontdate,endfee,visafee,visadate,endtime) values('" + rid + "','" + name + "','" + qq + "','" + tel + "','" + mail + "','" + school + "','" + major + "','" + leve + "','" + rstate + "','" + consult + "','" + source + "','" + date + "','" + record + "','" + backlog + "','" + visa + "','" + tuition + "','" + total + "','" + frontfee + "','" + frontdate + "','" + endfee + "','" + visafee + "','" + visadate + "','" + endtime + "')";
cmd.ExecuteNonQuery();
}
tss = 1;
count = ts.Rows.Count;
for (int i = 0; i < count; i++)//得到总行数并在之内循环
{
dt = i;
string rid = ts.Rows[i]["资源号"].ToString();
string school = ts.Rows[i]["申请学校"].ToString();
string course = ts.Rows[i]["申请课程"].ToString();
string senddate = ts.Rows[i]["递交日期"].ToString();
string account = ts.Rows[i]["网申账号/密码"].ToString();
string enterinfor = ts.Rows[i]["录取条件/结果"].ToString();
string yasi = ts.Rows[i]["雅思考试时间或不出国备注"].ToString();
string language = ts.Rows[i]["配语言学校"].ToString();
string ldate = ts.Rows[i]["配语言日期"].ToString();
string getdate = ts.Rows[i]["递签日期"].ToString();
string result = ts.Rows[i]["签证结果"].ToString();
cmd.CommandText = "insert into minor(rid,school,course,senddate,account,enterinfor,yasi,language,ldate,getdate,result) values('" + rid + "','" + school + "','" + course + "','" + senddate + "','" + account + "','" + enterinfor + "','" + yasi + "','" + language + "','" + ldate + "','" + getdate + "','" + result + "')";
cmd.ExecuteNonQuery();
}
tran.Commit();//如果两个sql命令都执行成功，则执行commit这个方法，执行这些操作
MessageBox.Show("导入成功", "提示");
refresh();
}
catch
{
if (tss == 1)
{
MessageBox.Show("导入失败，请检查附表第" + (dt + 2).ToString() + "行数据格式且被导入文件不能处于打开状态！", "提示");
}
else
{
MessageBox.Show("导入失败，请检查主表第" + (dt + 2).ToString() + "行数据格式且被导入文件不能处于打开状态！", "提示");
}
tran.Rollback();//如何执行不成功，发生异常，则执行rollback方法，回滚到事务操作开始之前；
}
}
}