上传导入和下载标准的EXCEL
HTML部分 下载上传标准的EXCEL功能。防止其它伪EXCEL导入失败的情况。 <asp:Button ID="btnDownLoad" runat="server" Text="点击下载..." OnClick="btnDownLoad_Click" /><br /> <asp:FileUpload ID="FileUpload1" ToolTip="选择文件" runat="server" /> <asp:Button ID="btnUpload" runat="server" Text="上传" OnClick="btnUpload_Click" /> <asp:Label ID="lbsuccess" runat="server"></asp:Label> <asp:LinkButton ID="lbfail" runat="server" OnClick="lbfail_Click" ForeColor="Red"></asp:LinkButton><br />
CS部分
/// <summary> /// 上传 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnUpload_Click(object sender, EventArgs e) { if (FileUpload1.HasFile) { string path = Server.MapPath("template"); string fileName = path + "\\" + Guid.NewGuid().ToString("N") + FileUpload1.FileName; string serverFilename = ""; if (FileUpload1.FileName.ToLower().IndexOf(".xlsx") > 0) { serverFilename = "xlsx"; } else { if (FileUpload1.FileName.ToLower().IndexOf(".xls") > 0 && FileUpload1.FileName.EndsWith("xls")) { serverFilename = "xls"; } else { //提示 Page.RegisterClientScriptBlock("TS", "<script>alert('必须是Excel格式文件');</script>"); return; } } FileUpload1.PostedFile.SaveAs(fileName); //上传文件到服务器端 string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + fileName + "';Extended Properties='Excel 8.0;HDR=YES;'"; System.Data.DataTable dt = new System.Data.DataTable(); if (serverFilename == "xlsx") { strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + fileName + "';Extended Properties='Excel 12.0;HDR=YES'"; } if (serverFilename == "xls") { strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + fileName + "';Extended Properties='Excel 8.0;HDR=YES;'"; } OleDbConnection conn = new OleDbConnection(strConn); if (conn.State.ToString() == "Closed") { conn.Open(); } OleDbDataAdapter adapter = new OleDbDataAdapter("select * from [sheet1$]", strConn); adapter.Fill(dt); if (dt != null && dt.Rows.Count > 0) { BasicDataBLL BDB = new BasicDataBLL(); DataTable errorData = GetData("1"); DataTable successData = GetData("2"); for (int i = 0; i < dt.Rows.Count; i++) //循环导入的数据 { if (!string.IsNullOrEmpty(dt.Rows[i]["XXX"].ToString().Trim())) { //检查盘点数量的数据格式 if (YzNum(dt.Rows[i]["XXXXXXX"].ToString().Trim())) { if (BDB.CheckoutExistParts(dt.Rows[i]["XXX"].ToString().Trim(), dt.Rows[i]["配件型号"].ToString().Trim())) //存在 { GetErrorData(dt, successData, i, ""); } else //不存在 { GetErrorData(dt, errorData, i, "XXXX匹配错误"); } } else { GetErrorData(dt, errorData, i, "XXXXX必须为正整数"); } } else //3个元素中有一个为NULL { GetErrorData(dt, errorData, i, "数据不全"); } } Session["ErrorData"] = errorData; Session["SuccessData"] = successData; if (errorData != null && errorData.Rows.Count > 0) { string str = "成功" + (dt.Rows.Count - errorData.Rows.Count) + "条，失败" + errorData.Rows.Count + "条"; lbsuccess.Text = "成功" + (dt.Rows.Count - errorData.Rows.Count) + "条"; lbfail.Text = "失败" + errorData.Rows.Count + "条"; Page.RegisterClientScriptBlock("TS", "<script>alert('" + str + "');</script>"); conn.Close(); } else { Page.RegisterClientScriptBlock("TS", "<script>alert('导入成功');</script>"); conn.Close(); } } else { Page.RegisterClientScriptBlock("TS", "<script>alert('Exece表没有数据');</script>"); conn.Close(); } } else { //提示 Page.RegisterClientScriptBlock("TS", "<script>alert('请选择Excel格式文件');</script>"); } }
下载文件
/// <summary> /// 下载文件 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnDownLoad_Click(object sender, EventArgs e) { BasicDataBLL BDB = new BasicDataBLL(); DataTable dt = BDB.GetPartsPM(); // 根据模板文件创建副本 ，此处的Inventory.xls是标准的EXCEL2003模版 string filePath = Server.MapPath("~/template/" + Guid.NewGuid().ToString() + ".xls"); File.Copy(Server.MapPath("~/template/Inventory.xls"), filePath); // 使用OleDb驱动程序连接到副本 OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0;"); using (conn) { conn.Open(); // 增加记录 for (int i = 0; i < dt.Rows.Count; i++) { OleDbCommand cmd = new OleDbCommand("INSERT INTO [Sheet1$](配件,配件型号) VALUES(@pid,@pmodelId)", conn); cmd.Parameters.AddWithValue("@pid", dt.Rows[i]["配件"].ToString()); cmd.Parameters.AddWithValue("@pmodelId", dt.Rows[i]["配件型号"].ToString()); cmd.ExecuteNonQuery(); } } // 输出副本的二进制字节流 Response.ContentType = "application/ms-excel"; Response.AppendHeader("Content-Disposition", "attachment;filename=Inventory.xls"); Response.BinaryWrite(File.ReadAllBytes(filePath)); // 删除副本 File.Delete(filePath); }
其它部分
/// <summary> /// 返回错误表列 /// </summary> /// <returns></returns> private DataTable GetData(string bz) { #region 错误数据标题 DataTable dtError = new DataTable(); dtError.Columns.Add("配件"); dtError.Columns.Add("配件型号"); dtError.Columns.Add("盘点数量"); if (bz == "1") { dtError.Columns.Add("错误信息"); } else { dtError.Columns.Add(""); } #endregion return dtError; } /// <summary> /// 将错误的信息添加到表中 /// </summary> /// <param name="dt">读入到系统中的数据</param> /// <param name="dtError">错误表数据</param> /// <param name="rowNum">当前行数</param> /// <param name="errorDesc">错误描述</param> /// <returns>返回错误表最新数据</returns> private DataTable GetErrorData(DataTable dt, DataTable dtError, int rowNum, string errorDesc) { DataRow dr = dtError.NewRow(); for (int m = 0; m < dt.Columns.Count; m++) { dr[m] = dt.Rows[rowNum][m].ToString(); } dr[dt.Columns.Count] = errorDesc; dtError.Rows.Add(dr); //把失败的行添加到新的数据表中 return dtError; } protected void lbfail_Click(object sender, EventArgs e) { if (Session["ErrorData"] != null) { DrawExcel((DataTable)Session["ErrorData"]); } } /// <summary> /// 导出HTML格式的数据 /// </summary> /// <param name="pDataTable"></param> private void DrawExcel(System.Data.DataTable pDataTable) { int tRowCount = pDataTable.Rows.Count; int tColumnCount = pDataTable.Columns.Count; Response.Expires = 0; Response.Clear(); Response.Buffer = true; Response.Charset = "utf-8"; Response.ContentEncoding = System.Text.Encoding.UTF8; Response.ContentType = "application/vnd.ms-excel"; Response.AddHeader("Content-Disposition", "attachment; filename=ErrorData.xls"); Response.Write("<meta http-equiv=Content-Type content=text/html;charset=utf-8>"); //Response.Write("<style type=\"text/css\">.format{mso-number-format:\'\@\';}<\style>"); Response.Write("<Table borderColor=black border=1>"); Response.Write("\n"); Response.Write("\n <TR>"); for (int i = 0; i < tColumnCount; i++) //画数据标题栏 { Response.Write("\n <TD bgcolor = #ffcd00 style=\"height: 30;font-weight:bold;\" align=\"center\" >"); Response.Write(pDataTable.Columns[i].ColumnName); Response.Write("\n </TD>"); } Response.Write("\n </TR>"); for (int j = 0; j < tRowCount; j++) { Response.Write("\n <TR>"); for (int k = 0; k < tColumnCount; k++) { Response.Write("\n <TD align=\"right\" style='vnd.ms-excel.numberformat:@'>"); Response.Write(pDataTable.Rows[j][k].ToString()); Response.Write("\n </TD>"); } Response.Write("\n </TR>"); } Response.Write("</Table>"); Response.End(); }
