导入excel

 

  protected void Button1_Click(object sender, EventArgs e)
        {
            //获取文件路径
            HttpPostedFile upfiles =file1.PostedFile ;
            string filePath = "";
            if (upfiles.ContentLength > 0)
            {
                filePath = upfiles.FileName;
            }

            if (filePath != "")
            {
                if (filePath.Contains("xls"))//判断文件是否存在
                {
                    InputExcel(filePath);
                }
                else
                {
                    labMsg.Text = "请检查您选择的文件是否为Excel文件！谢谢！";
                }
            }
            else
            {
                labMsg.Text = "请先选择导入文件后，再执行导入！谢谢！";
            }
        }
        private void BindRight()
        {
            base.CheckRight("mall");
            if (!base.Permission.Create)
            {
                base.SetErrorHint(this.labMsg, "您无导入商品的操作权限！");
                this.Button1.Enabled = false;
            }
        }

        //protected void ExcelConvertToHtml(string xlsPath, string htmlPath)
        //{
        //    try
        //    {
        //        Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
        //        app.Visible = false;
        //        Object o = Missing.Value;
        //        _Workbook xls = app.Workbooks.Open(xlsPath, o, o, o, o, o, o, o, o, o, o, o, o, o, o);
        //        object fileName = Server.MapPath("../../../" + htmlPath.ToString());
        //        object format = Microsoft.Office.Interop.Excel.XlFileFormat.xlHtml;

        //        xls.SaveAs(fileName, format, o, o, o, o, XlSaveAsAccessMode.xlExclusive, o, o, o, o, o);
        //        object t = true;
        //        app.Quit();

        //        Process[] myProcesses = Process.GetProcessesByName("EXCEL");
        //        foreach (Process myProcess in myProcesses)
        //        {
        //            myProcess.Kill();
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        System.Console.Write(ex.Message);
        //    }
        //}

        private void InputExcel(string pPath)
        {
            OleDbConnection objConn = null;
            System.Data.DataTable dt = null;
            try
            {
                string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" + pPath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
                objConn = new OleDbConnection(connString);
                objConn.Open();
                dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (dt != null)
                {
                    string[] sheetName = new string[dt.Rows.Count];
                    int i = 0;
                    foreach (DataRow row in dt.Rows)
                    {
                        sheetName[i] = row["TABLE_NAME"].ToString();
                        string a = sheetName[i];
                        string Sql = "select 品牌,编号,名称,规格,价格,简介 from [" + a + "]";
                        OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, objConn);
                        DataSet ds = new DataSet();
                        mycommand.Fill(ds, a);
                        objConn.Close();
                        int count = ds.Tables[a].Rows.Count;
                       
                        for (int j = 0; j < count; j++)
                        {
                            int BrandId;
                            string productNo,productName, standard, standardPrice, Description;
                            //string filename = file1.FileName.Substring(0, file1.FileName.IndexOf('.'));
                            //DirectoryInfo dinfo = new DirectoryInfo(Server.MapPath("../../../upfiles/images/" + filename + ".files"));
                            //FileInfo[] files = dinfo.GetFiles("*.jpg");  //读取文件夹下所有文件名
                          
                            //ImageUrl = "/upfiles/images/jiancai.files/" + files[j].Name;
                            //ImageUrl = "/upfiles/images/" + filename + ".files/" + files[k].Name;//ds.Tables[0].Rows[j]["图片"].ToString().Trim();
                            BrandId = GetBrandId(ds.Tables[0].Rows[j]["品牌"].ToString().Trim());
                            productNo = ds.Tables[0].Rows[j]["编号"].ToString().Trim();
                            productName = ds.Tables[0].Rows[j]["名称"].ToString().Trim();
                            standard = ds.Tables[0].Rows[j]["规格"].ToString().Trim().Replace("\n", ",");
                            standardPrice = ds.Tables[0].Rows[j]["价格"].ToString().Trim();
                            Description = ds.Tables[0].Rows[j]["简介"].ToString().Trim();
                            string excelsql = "insert into mall_Product(brandId,ProductNo, productName,standard,standardPrice,Description) values (" + BrandId + ",'" + productNo + "','" + productName + "','" + standard + "','" + standardPrice + "','" + Description + "')";
                            ProductProvider.GetDefaultDataAccess().ExecuteNonQuery(excelsql);
                        }
                        i++;
                    }
                }
            }
            catch (Exception e1)
            {
                throw e1;
            }
        }