首页 > 代码库 > 下载不含数据EXCEL的固定表头模版（标准EXCEL只含有列头），然后上传EXCEL.显示成功和上传失败的EXCEL连接
下载不含数据EXCEL的固定表头模版（标准EXCEL只含有列头），然后上传EXCEL.显示成功和上传失败的EXCEL连接
<div id="import" runat="server" visible="false"> Step1:<asp:HyperLink ID="HyperLink1" NavigateUrl="~/CommonTemplate/设备清单模版.xlsx" runat="server">下载模版</asp:HyperLink><br /> Step2: <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 /> </div>
/// <summary> /// 上传EXCEL /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnUpload_Click(object sender, EventArgs e) { if (FileUpload1.HasFile) { string path = Server.MapPath("UploadFile"); 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>jError(‘必须是Excel格式文件!‘, { TimeShown: 1000, VerticalPosition: ‘center‘ });;</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) { DataTable errorData = GetData("1"); DataTable successData = GetData("2"); for (int i = 0; i < dt.Rows.Count; i++) //循环导入的数据 { if (!string.IsNullOrEmpty(dt.Rows[i]["名称"].ToString().Trim()) && !string.IsNullOrEmpty(dt.Rows[i]["型号"].ToString().Trim()) && !string.IsNullOrEmpty(dt.Rows[i]["数量"].ToString().Trim()) && !string.IsNullOrEmpty(dt.Rows[i]["单价"].ToString().Trim())) { //检查盘点数量的数据格式 if (YzNum(dt.Rows[i]["数量"].ToString().Trim())) { if (!YzPrice(dt.Rows[i]["单价"].ToString().Trim())) { GetErrorData(dt, errorData, i, "单价必须为正数"); } else { GetErrorData(dt, successData, i, ""); } } else { GetErrorData(dt, errorData, i, "数量必须为正整数"); } } else //3个元素中有一个为NULL { GetErrorData(dt, errorData, i, "数据不全"); } } Session["ErrorData"] = errorData; //Session["SuccessData"] = successData; if (IPB.SaveIntegratedEquipmentList(successData, ViewState["pid"].ToString())) { ShowControl(); //BindGVData(); Page.RegisterClientScriptBlock("TS", "<script>jError(‘导入成功‘, { TimeShown: 1000, VerticalPosition: ‘center‘ });;</script>"); } else { Page.RegisterClientScriptBlock("TS", "<script>jError(‘导入失败!‘, { TimeShown: 1000, VerticalPosition: ‘center‘ });;</script>"); } lbsuccess.Text = "成功" + (dt.Rows.Count - errorData.Rows.Count) + "条"; if (errorData != null && errorData.Rows.Count > 0) { lbfail.Text = "数据错误：" + errorData.Rows.Count + "条"; } conn.Close(); } else { Page.RegisterClientScriptBlock("TS", "<script>jError(‘Exece表没有数据!‘, { TimeShown: 1000, VerticalPosition: ‘center‘ });;</script>"); conn.Close(); } } else { //提示 Page.RegisterClientScriptBlock("TS", "<script>alert(‘请选择Excel格式文件‘);</script>"); } }
/// <summary> /// 判断一个数是否为正整数 /// </summary> /// <param name="content"></param> /// <returns></returns> private bool YzNum(string content) { bool y = true; try { int.Parse(content); if (int.Parse(content) < 0) //非正数 { y = false; } if (content.IndexOf(‘.‘) >= 0) //是小数 { y = false; } } catch { y = false; } return y; } /// <summary> /// 验证价格 /// </summary> /// <param name="content"></param> /// <returns></returns> private bool YzPrice(string content) { bool y = true; try { float.Parse(content); if (float.Parse(content) < 0) //非正数 { y = false; } } catch { y = false; } return y; }
/// <summary> /// 返回错误表列 /// </summary> /// <returns></returns> private DataTable GetData(string bz) { #region 错误数据标题 DataTable dtError = new DataTable(); dtError.Columns.Add("名称"); dtError.Columns.Add("型号"); 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(); }
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。