Excel、记事本数据导入到数据库的实现方法

<div class="tab-content detail" id="divSecond" runat="server" visible="false">

            <fieldset >

             <p>

                <label>

                  选择文件</label>

                  <asp:FileUpload ID="FileUpload1" runat="server"  /><asp:RequiredFieldValidator

                      ID="RequiredFieldValidator1" runat="server" ErrorMessage="请选择要提交的Excel文件" ControlToValidate="FileUpload1"></asp:RequiredFieldValidator>

              </p>

              <p>

              <label>文件示例</label><img src="../images/ex_excel.jpg" />

              </p>

            </fieldset>

            <div class="clear"></div>

           <div class="clear"></div>

            <div style="margin-left:200px">

                <asp:Button ID="btnExcel" runat="server" Text="提 交" CssClass="button" 

                    onclick="btnExcel_Click"  />

                  &nbsp;&nbsp;&nbsp;

                <input class="button" type="button" value="返 回" onclick="javascript:window.location.href='CartList.aspx'" />

            </div>

          </div>

          <div class="tab-content detail" id="divThird" runat="server" visible="false">

            <fieldset >

              <p>

                <label>

                  选择文件</label>

                  <asp:FileUpload ID="FileUpload2" runat="server" /> <asp:RequiredFieldValidator

                      ID="RequiredFieldValidator2" runat="server" ErrorMessage="请选择要提交的tTXT文件"

                      ControlToValidate="FileUpload2"></asp:RequiredFieldValidator>

              </p>

              <p>

              <label>文件示例</label><img src="../images/ex_txt.jpg" />

              </p>

            </fieldset>

            <div class="clear"></div>

           <div class="clear"></div>

            <div style="margin-left:200px">

                <asp:Button ID="btnNotepad" runat="server" Text="提 交" CssClass="button"

                    onclick="btnNotepad_Click"  />

                  &nbsp;&nbsp;&nbsp;

                <input class="button" type="button" value="返 回" onclick="javascript:window.location.href='CartList.aspx'" />

            </div>

          </div>

//Excel上传

        protected void btnExcel_Click(object sender, EventArgs e)

        {         

              string backStr= UploadFile(FileUpload1, "Excel",1);

              if (backStr == "-1")

              {

                   //这里是放返回消息的，改为对应放消息的方式就行了，Js这个类就不上传了

                  Js.ShowSureMsgBox(this.Page,"请选择要提交的Excel文件");

                  return;

              }

              else if (backStr == "-2")

              {

                  Js.ShowSureMsgBox(this.Page, "请选择.xls或.xlsx类型文件");

                  return;

              }

              else

              {

                  string url = backStr;   //绝对路径

                  DataTable dt = ExcelToDataSet(url);

                  if (dt.Rows.Count > 0)

                  {

                     for (int i = 0; i < dt.Rows.Count; i++)

                     {

                          //读取每行数据         

                           string phoneNum= dt.Rows[i][0].ToString();            

                     }           

                  }

                  else

                  {

                      Js.ShowSureMsgBox(this.Page, "文件内容为空");

                  }

                  File.Delete(url);  //删除上传的文件

              }

        }

        //记事本上传

        protected void btnNotepad_Click(object sender, EventArgs e)

        {

            string backStr = UploadFile(FileUpload2, "TXT",2);

            if (backStr == "-1")

            {

                Js.ShowSureMsgBox(this.Page, "请选择要提交的TXT文件");

                return;

            }

            else if (backStr == "-2")

            {

                Js.ShowSureMsgBox(this.Page, "请选择.txt类型文件");

                return;

            }

            else

            {

                string url = backStr;   //绝对路径

                DataTable dt = ReadTXT(url);

                if (dt.Rows.Count > 0)

                {

                     for (int i = 0; i < dt.Rows.Count; i++)

                     {

                          //读取每行数据         

                           string phoneNum= dt.Rows[i][0].ToString();            

                     }                

                }

                else

                {

                    Js.ShowSureMsgBox(this.Page, "文件内容为空");

                }

                File.Delete(url);  //删除上传的文件

            }

        }

        //上传文件

        public string UploadFile(FileUpload FileUploadName, string varfilename,int type)

        {

                if (FileUploadName.HasFile)//判断是否有上传文件

                {

                    string fileExtension = System.IO.Path.GetExtension(FileUploadName.FileName).ToLower();//获取文件的后缀名

                    if (type == 1)

                    {

                        if (fileExtension != ".xls" && fileExtension != ".xlsx")

                        {

                            return "-2";

                        }

                    }

                    if (type == 2)

                    {

                        if (fileExtension != ".txt")

                        {

                            return "-2";

                        }

                    }                 

                    string fpath = System.Web.HttpContext.Current.Server.MapPath("/Manager/Uploadfiles/" + varfilename + "/");//图片存储文件夹路径，需要按照不同的需要进行相应的修改

                    if (!Directory.Exists(fpath))//查看存储路径的文件是否存在

                    {

                        Directory.CreateDirectory(fpath);   //创建文件夹，并上传文件

                    }

                    string time = DateTime.Now.ToString("yyyyMMddhhmmssfff");//使用时间定义上传图片的名字

                    string picturename = time + fileExtension;

                    string newFilePath = fpath + picturename; //文件保存路径

                    FileUploadName.SaveAs(newFilePath);

                    return newFilePath;   //绝对路径

                }

                else

                {

                    return "-1";   //没有文件

                }           

        }

        //读取Excel数据

         public DataTable ExcelToDataSet(string filename)

        {

            string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + filename + ";Extended Properties=Excel 8.0";

            OleDbConnection conn = new OleDbConnection(strCon);

            conn.Open();

            //返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等　

            DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });

            //包含excel中表名的字符串数组

            string[] strTableNames = new string[dtSheetName.Rows.Count];

            for (int k = 0; k < dtSheetName.Rows.Count; k++)

            {

                strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();

            }

            OleDbDataAdapter myCommand = null;

            DataTable dt = new DataTable();

            //从指定的表明查询数据,可先把所有表明列出来供用户选择

            string strExcel = "select * from [" + strTableNames[0] + "]";

            myCommand = new OleDbDataAdapter(strExcel, strCon);

            myCommand.Fill(dt);

            conn.Close();

            return dt;
        }
        //读取记事本数据
         public DataTable ReadTXT(string dirTXT)
         {
             StreamReader objReader = new StreamReader(dirTXT);
             System.Data.DataTable dt = new System.Data.DataTable();
             dt.Columns.Add("DN", System.Type.GetType("System.String"));
             string sLine = "";
             while (sLine != null)
             {
                 sLine = objReader.ReadLine();
                 if (sLine != null && !sLine.Equals(""))
                 {
                     DataRow dr = dt.NewRow();
                     dr[0] = sLine;
                     dt.Rows.Add(dr);
                 }
             }
             objReader.Close();
             return dt;
         }