C# excel导入模板

1527493060(1).png

 try {
                string fileName = "";
                //获取Excel文件路径和名称  
                OpenFileDialog odXls = new OpenFileDialog();
                // 设置文件格式    
                odXls.Title = "请选择文件夹";
                odXls.Filter = "Excel files office2003(*.xls)|*.xls|Excel office2010(*.xlsx)|*.xlsx|All files (*.*)|*.*";
                odXls.FilterIndex = 2;
                odXls.RestoreDirectory = true;
                if (odXls.ShowDialog() == DialogResult.OK)
                {
                    fileName = odXls.FileName;
                }
                if (fileName != "")
                {
                    try
                    {
                        //office07及以上版本
                        string strCon = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source={0};" + "Extended Properties='Excel 8.0;HDR=YES;IMEX=1';", fileName);
                        if ((System.IO.Path.GetExtension(fileName)).ToLower() == ".xls")
                        {
                            //office07及以下版本
                            strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" + "data source=" + fileName + ";Extended Properties=Excel 5.0;Persist Security Info=False";
                        }
                        using (OleDbConnection conn = new OleDbConnection(strCon))
                        {
                            conn.Open();
                            DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); //得到所有sheet的名字
                            string firstSheetName = sheetsName.Rows[0][2].ToString(); //得到第一个sheet的名字
                            string sql = string.Format("SELECT * FROM [{0}]", firstSheetName); //查询字符串 
                            OleDbDataAdapter ada = new OleDbDataAdapter(sql, strCon);
                            DataSet odDs = new DataSet();
                            ada.Fill(odDs);
                            List<JobMode> jmos = new List<JobMode>();
                            foreach (DataRow dr in odDs.Tables[0].Rows)
                            {
                                JobMode jmo = new JobMode();
                                jmo.CmstId = this._cmstId;
                                jmo.Mode = dr["作业模式"].ToString();
                                jmo.HelpCode = dr["助记码"].ToString();
                                jmo.Rate = Convert.ToDecimal(dr["费率"]);
                                jmo.Remark = dr["备注"].ToString();
                                jmos.Add(jmo);
                            }
                            string jsonresult = ChargeManageProxy.ImportJmo(jmos);
                            FeedbackInfomation fi = JsonConvert.DeserializeObject<FeedbackInfomation>(jsonresult);
                            if (fi.ErrorStatus == STATUS_ADAPTER.IMPORT_SUCCESS)
                            {
                                MsgBox.ShowDialog(fi.FeedbackMessage, "信息提示", MsgBox.MyButtons.OK, true);
                                LoadJmo();
                                this.View.clearPanelEdit();//清除编辑区内容
                                this.View.clearDgvSelect();//清除dgv选中行
                                this.View.SetControlInInit();//初始化状态
                            }
                            else
                            {
                                MsgBox.ShowDialog(fi.FeedbackMessage, "信息提示", MsgBox.MyButtons.OK, true);
                                this.View.clearPanelEdit();//清除编辑区内容
                                this.View.clearDgvSelect();//清除dgv选中行
                                this.View.SetControlInInit();//初始化状态
                            }
                        }
                    }
                    catch (Exception ex1)
                    {
                        MsgBox.ShowDialog("模板格式不正确或缺少插件", "信息提示", MsgBox.MyButtons.OK, true);
                        if (ex1.ToString().Contains("未在本地计算机上注册“Microsoft.ACE.OLEDB.12.0”提供程序"))
                        {
                            if (DialogResult.OK == MsgBox.ShowDialog("检测到系统缺少插件，下载需一定时间，是否下载安装？", "导入插件下载", MsgBox.MyButtons.OKCancel, true))
                            {
                                WebClient wc = new WebClient();
                                string address = "http://download.microsoft.com/download/7/0/3/703ffbcb-dc0c-4e19-b0da-1463960fdcdb/AccessDatabaseEngine.exe";
                                string dir = Path.Combine(Application.StartupPath, "temdownload");
                                if (!Directory.Exists(dir))
                                {
                                    Directory.CreateDirectory(dir);
                                }
                                string downFileName = Path.Combine(dir, "AccessDatabaseEngine.exe");
                                wc.DownloadFile(address, downFileName);
                                ProcessStartInfo psi = new ProcessStartInfo();
                                psi.FileName = downFileName;
                                Process.Start(psi);
                            }
                        }
                    }