IIS7 下上传读取Excel 表格数据，并添加到数据库
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<div>
<radU:RadProgressManager ID="RadProgressManager1" Width="100%" Height="37" runat="server" />
<radU:RadProgressArea ID="RadProgressArea1" runat="server">
</radU:RadProgressArea>
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="btnUpload" runat="server" Text="上 传" OnClientClick="javascript:return checkTime()"
OnClick="btnUpload_Click" />
<asp:Label ID="lblMessage" runat="server" Visible="False" Font-Bold="True" ForeColor="#009933"></asp:Label>
</div>
</ContentTemplate>
<Triggers>
<asp:PostBackTrigger ControlID="btnUpload" />
</Triggers>
</asp:UpdatePanel>
#region 连接Excel 读取Excel数据 并返回DataSet数据集合
/// <summary>
/// 连接Excel 读取Excel数据 并返回DataSet数据集合
/// </summary>
/// <param name="filepath">Excel服务器路径</param>
/// <param name="tableName">Excel表名称</param>
/// <returns></returns>
public static System.Data.DataSet ExcelSqlConnection(string filepath, string tableName, string isXls)
{
// string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel "
//+ " 8.0;HDR=YES;IMEX=1'";
string strCon = "";
if (isXls == ".xls")
{
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties="Excel "
+ "8.0;HDR=Yes;IMEX=2"";
}
else if (isXls == ".xlsx")
{
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties="Excel "
+ "12.0;HDR=Yes;IMEX=2"";
}
OleDbConnection ExcelConn = new OleDbConnection(strCon);
try
{
ExcelConn.Open();
DataTable dtExcelSchema = ExcelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
string SheetName = "";
for (int i = 0; i < dtExcelSchema.Rows.Count; i++)
{
SheetName = dtExcelSchema.Rows[i]["TABLE_NAME"].ToString();
}
//string strCom = string.Format("SELECT * FROM [Sheet1$]");
string strCom = string.Format("SELECT * FROM [" + SheetName + "]");
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, ExcelConn);
DataSet ds = new DataSet();
myCommand.Fill(ds, "[" + tableName + "$]");
ExcelConn.Close();
return ds;
}
catch
{
ExcelConn.Close();
return null;
}
}
#endregion
#region 导入的execl
protected void btnUpload_Click(object sender, EventArgs e)
{
foreach (UploadedFile file in RadUploadContext.Current.UploadedFiles)
{
string IsXls = System.IO.Path.GetExtension(file.FileName).ToString().ToLower();
if (IsXls != ".xls" && IsXls != ".xlsx")
{
Response.Write("<script>alert('只可以选择Excel文件')</script>");
return;//当选择的不是Excel文件时,返回
}
string fullname = file.FileName.ToString();//获得当前图片的名字
string typ2 = fullname.Substring(fullname.LastIndexOf(".") + 1);//获取图片的类型 后缀名字
string da = Guid.NewGuid().ToString();
string wjm = da + "." + typ2;// 图片的名字 DateRndName()得到的是上传的时间
string st = Request.PhysicalApplicationPath;
//保存
file.SaveAs(st + "UploadFiles\" + wjm, true);
DataSet ds = ExcelSqlConnection(st + "UploadFiles\" + wjm, fullname, IsXls); //调用自定义方法
DataRow[] dr = ds.Tables[0].Select(); //定义一个DataRow数组
int rowsnum = ds.Tables[0].Rows.Count;
if (rowsnum == 0)
{
Response.Write("<script>alert('Excel表为空表,无数据!')</script>"); //当Excel表为空时,对用户进行提示
}
else
{
try
{
for (int i = 2; i < dr.Length - 1; i++)
{
string fnbm = DBHelper.ToBj(dr[i][2].ToString());
string fnxm = dr[i][0].ToString();
string fnsfhm = DBHelper.ToBj(dr[i][1].ToString());
string sql_add = "insert into ceshi(fnbm,fnxm,fnsfhm)values(@fnbm,@fnxm,@fnsfhm)";
SqlParameter[] sp = new SqlParameter[3];
sp[0] = new SqlParameter("@fnbm", fnbm);
sp[1] = new SqlParameter("@fnxm", fnxm);
sp[2] = new SqlParameter("@fnsfhm", fnsfhm);
try
{
DBHelper.ExecuteNonQuery(CommandType.Text, sql_add, sp);
}
catch (MembershipCreateUserException ex)//捕捉异常
{
Response.Write("<script>alert('导入内容:" + ex.Message + "')</script>");
}
}
Response.Write("<script>alert('Excle表导入成功!')</script>");
}
catch (Exception ex)
{
Response.Write("<script>alert('表：" + ex.Message + "')</script>");
return;
}
}
}
}
#endregion
