下载不含数据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" />&nbsp;
                            <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();
        }