前台代码:
<table width="900" style="text-align: center; vertical-align: middle;" align="center"
border="0" cellpadding="0" cellspacing="0">
<tr>
<td style="width: 90">
店铺:
</td>
<td style="text-align: left; width: 180px">
<asp:DropDownList ID="ddlSeller" runat="server">
<asp:ListItem Selected="True" Value="111">123</asp:ListItem>
<asp:ListItem Value="222">222</asp:ListItem>
<asp:ListItem Value="333">333</asp:ListItem>
<asp:ListItem Value="444">444</asp:ListItem>
<asp:ListItem Value="555">555</asp:ListItem>
<asp:ListItem Value="666">666</asp:ListItem>
</asp:DropDownList>
</td>
<td style="width: 140;text-align: right">
工作表名称:
</td>
<td style="text-align: left; width: 180px">
<asp:TextBox ID="txtSheet" runat="server" Width="120px" ForeColor="Blue"></asp:TextBox>
</td>
<td style="text-align: left; width: 180px">
<asp:TextBox runat="server" ID="txtIemport" Visible="false" />
<asp:FileUpload ID="FileUpload1" runat="server" />
</td>
<td style="text-align: left">
<asp:Button runat="server" ID="btnImport" Text="导入" OnClick="btnImport_Click" />
</td>
</tr>
<tr>
<td colspan="6">
<asp:GridView ID="gvImport" runat="server" Width="900">
</asp:GridView>
</td>
</tr>
</table>
后台代码:
protected void btnImport_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
string fileName = Server.HtmlEncode(FileUpload1.FileName);
string extension = System.IO.Path.GetExtension(fileName);
if ((extension == ".xlsx") || (extension == ".xls"))
{
ExalToSql();
}
}
}
private void ExalToSql()
{
string sheet = "";
string path = Server.MapPath(FileUpload1.FileName);
FileUpload1.PostedFile.SaveAs(path);//保存文件
//string connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = " + path + ";Extended Properties =Excel 12.0;HDR=YES;IMEX=1;";
string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";Extended Properties='Excel 8.0;'";
//建立EXCEL的连接
OleDbConnection objConn = new OleDbConnection(sConnectionString);
int count = 0;//用来记录出错的条数
try
{
if (string.IsNullOrWhiteSpace(txtSheet.Text))
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "startup", "<script>alert('请认真填写工作表名称,谢谢!!!');window.location='ImportSellerExamine.aspx'</script>");
}
else
{
sheet = txtSheet.Text.Trim();
}
string strCom = " SELECT * FROM [" + sheet + "$A1:E100] ";
objConn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, objConn);
DataSet ds = new DataSet();
myCommand.Fill(ds, "[" + sheet + "$]");
DataTable dt = ds.Tables[0];
string query = "select count(*) from CustomerServiceMonth where seller_id='" + ddlSeller.SelectedItem.Value + "'";
int customercount = int.Parse(SQLDBHelper.GetSingle(query).ToString());
if (customercount > 0)
{
string delete = "delete from Customer where seller_id='" + ddlSeller.SelectedItem.Value + "' and monthOfDate=" + DateTime.Now.Month;
SQLDBHelper.ExecuteSql(delete);
}
foreach (DataRow myDrv in dt.Rows)
{
count++;
if (!string.IsNullOrWhiteSpace(myDrv[1].ToString().Trim()))
{
string sql = @"INSERT INTO Customer
([ww_nick]
,[ww_successRate]
,[seller_id]
,[ww_unitPrice]
,[ww_sale]
,[monthOfDate]
,[modified]
,[salrate])
VALUES
('" + myDrv[0].ToString().Trim()
+ "','" + myDrv[1].ToString().Trim()
+ "','" + ddlSeller.SelectedItem.Value
+ "','" + myDrv[2].ToString().Trim()
+ "','" + myDrv[3].ToString().Trim()
+ "'," + DateTime.Now.Month
+ ",'" + DateTime.Now
+ "','" + myDrv[4].ToString().Trim() + "')";
SQLDBHelper.ExecuteSql(sql);
}
}
gvImport.DataSource = ds;
gvImport.DataBind();
}
catch
{
Page.Response.Write("alert('第" + count.ToString() + "条数据出错!');");
}
finally
{
objConn.Close();//关闭EXCEL的连接
}
}