上传文件:
/// <summary>
/// 上传Excel文件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnFileLoad_Click(object sender, EventArgs e)
{
if (this.FileUpload_Excel.HasFile)
{
try
{
//获取文件名
string FileName = this.FileUpload_Excel.FileName.ToString();
//获取文件后缀名
string[] FileArray = FileName.Split('.');
string FileType = FileArray[FileArray.Length - 1].ToString();
//判断上传的文件是否为Excel格式
if (FileType != "xls")
{
JavaScript.Alert("请您上传Excel文件!", this.Page);
return;
}
this.FileUpload_Excel.SaveAs(Server.MapPath("~") + "/ExcelFile/" + "LevovoExcel" + "." + FileType);
this.Label_Excel.Text = "恭喜您!文件上传成功";
}
catch (Exception ex)
{
Response.Write(ex.Message.ToString());
}
}
else
{
this.Label_Excel.Text = "对不起!文件没有上传成功,请重试!";
}
}
导入文件:
/// <summary>
/// 将Excel中的文件导入到数据库
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnInsertData_Click(object sender, EventArgs e)
{
//先将EXCEL导入到数据库,一:先把EXCEL导入dateView,二:然后将dateView里的数据导入到数据库里面
//EXCEL连接
string conn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=WWW/ExcelFile/LevovoExcel.xls;" + "Extended Properties=Excel 8.0;";
OleDbConnection thisconnection = new OleDbConnection(conn);
thisconnection.Open();
string Sql = "select * from [Sheet1$]";
OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, thisconnection);
DataSet ds = new DataSet();
mycommand.Fill(ds, "[Sheet1]");
thisconnection.Close();
//Sql连接
SqlConnection thisconnection1 = new SqlConnection(SqlHelper.conn_Default);
thisconnection1.Open();
int count = ds.Tables["[Sheet1]"].Rows.Count;
for (int i = 0; i < count; i++)
{
string DataNum1 = ds.Tables["[Sheet1]"].Rows[i]["编号"].ToString();
string DataNum2 = ds.Tables["[Sheet1]"].Rows[i]["批号"].ToString();
string DataNum3 = ds.Tables["[Sheet1]"].Rows[i]["报名表类别"].ToString();
string DataNum4 = ds.Tables["[Sheet1]"].Rows[i]["姓名"].ToString();
string DataNum5 = ds.Tables["[Sheet1]"].Rows[i]["性别"].ToString();
string DataNum6 = ds.Tables["[Sheet1]"].Rows[i]["有效证件号码"].ToString();
string DataNum7 = ds.Tables["[Sheet1]"].Rows[i]["出生日期"].ToString();
string DataNum8 = ds.Tables["[Sheet1]"].Rows[i]["年龄"].ToString();
string DataNum9 = ds.Tables["[Sheet1]"].Rows[i]["职业"].ToString();
string DataNum10 = ds.Tables["[Sheet1]"].Rows[i]["国籍"].ToString();
string DataNum11 = ds.Tables["[Sheet1]"].Rows[i]["省份/直辖市"].ToString();
string DataNum12 = ds.Tables["[Sheet1]"].Rows[i]["城市"].ToString();
string DataNum13 = ds.Tables["[Sheet1]"].Rows[i]["地址"].ToString();
string DataNum14 = ds.Tables["[Sheet1]"].Rows[i]["邮政编码"].ToString();
string DataNum15 = ds.Tables["[Sheet1]"].Rows[i]["Email"].ToString();
string DataNum16 = ds.Tables["[Sheet1]"].Rows[i]["区号"].ToString();
string DataNum17 = ds.Tables["[Sheet1]"].Rows[i]["住宅电话"].ToString();
string DataNum18 = ds.Tables["[Sheet1]"].Rows[i]["移动电话"].ToString();
string DataNum19 = ds.Tables["[Sheet1]"].Rows[i]["办公电话"].ToString();
string excelsql = "INSERT INTO [cnSheet_Lenovo] ([编号] ,[批号] ,[报名表类别] ,[姓名] ,[性别] ,[有效证件号码] ,[出生日期] ,[年龄] ,[职业] ,[国籍] ,[省份/直辖市] ,[城市] ,[地址] ,[邮政编码] ,[Email] ,[区号] ,[住宅电话] ,[移动电话] ,[办公电话] ) VALUES ('" + DataNum1 + "','" + DataNum2 + "','" + DataNum3 + "','" + DataNum4 + "','" + DataNum5 + "','" + DataNum6 + "','" + DataNum7 + "','" + DataNum8 + "','" + DataNum9 + "','" + DataNum10 + "','" + DataNum11 + "','" + DataNum12 + "','" + DataNum13 + "','" + DataNum14 + "','" + DataNum15 + "','" + DataNum16 + "','" + DataNum17 + "','" + DataNum18 + "','" + DataNum19 + "')";
SqlCommand mycommand1 = new SqlCommand(excelsql, thisconnection1);
mycommand1.ExecuteNonQuery();
}
Response.Write("更新成功");
thisconnection1.Close();
}