操作Excel

1. 读取Excel中数据到DataSet

 

static public DataSet ExcelToDataSet(string filename, DataSet ds)
        {
            //string strConn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + filename + ";Extended Properties=Excel 8.0";
            string strConn = " Provider = Microsoft.ACE.OLEDB.12.0 ; Data Source = " + filename + ";Extended Properties=Excel 12.0";
            OleDbConnection conn = new OleDbConnection(strConn);
            try
            {
                ds.Clear();
                conn.Open();
                //返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等　 
                DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });

                OleDbDataAdapter adapter = new OleDbDataAdapter();

                foreach (DataRow dr in schemaTable.Rows)
                {
                    string strSql = "Select * From [" + dr["TABLE_NAME"].ToString().Trim() + "]";
                    if (strSql.Contains("$"))
                    {
                        OleDbCommand cmd = new OleDbCommand(strSql, conn);
                        adapter.SelectCommand = cmd;
                        adapter.Fill(ds, dr["TABLE_NAME"].ToString().Trim());
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception("该Excel文件的工作表的名字不正确," + ex.Message);
            }
            finally
            {
                if (conn != null) conn.Close();
            }
            return ds;
        }

 

 

2.  通过读取模板文件保存Excel

 

  /// <summary>
        /// 通过读取模板文件保存Excel
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="template">模板文件路径名</param>
        /// <param name="filename">保存文件路径名</param>
        /// <param name="beginRow">开始行</param>
        /// <param name="beginColumn">开始列</param>
        public static void SaveExcelByDataSet(DataSet ds, string template, string filename, int beginRow, int beginColumn)
        {
            GC.Collect();

            //建立Excel对象 
            Excel.Application excel = new Excel.Application();
            excel.UserControl = true;
            excel.Visible = false;
            int index = 1;
            Excel.Workbook workbook = excel.Workbooks.Add(template);
            excel.DisplayAlerts = false; //如果想删除某个sheet页，首先要将此项设为fasle。

            foreach (DataTable dt in ds.Tables)
            {
                DataTableToExcel(workbook, dt,ref index, beginRow, beginColumn);             
            }
            workbook.SaveCopyAs(filename);
            excel.Visible = false;
            workbook.Close(false, null, null);
            excel.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
            ds = null;
            workbook = null;
            excel = null;
            GC.Collect();
            MessageBox.Show("保存成功！");
        }


        private static void DataTableToExcel(Excel.Workbook workbook, DataTable dt,ref int sheetIndex, int beginRow, int beginColumn)
        {
            Excel.Worksheet worksheetTemp = workbook.Worksheets[sheetIndex];
            worksheetTemp.Copy(Type.Missing, workbook.Worksheets[sheetIndex]);//复制模板
            //workbook.Worksheets.Add(worksheetTemp, Type.Missing, Type.Missing, Type.Missing);操作原来的模板
            sheetIndex++;
            Excel.Worksheet worksheet = workbook.Worksheets[sheetIndex];

            worksheet.Name = dt.TableName;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    DataColumn dc = dt.Columns[j];
                    if (dc.DataType == System.Type.GetType("System.DateTime"))
                    {
                        worksheet.Cells[i + beginRow, j + beginColumn] = (Convert.ToDateTime(dt.Rows[i][dc.ColumnName].ToString())).ToString("yyyy-MM-dd");
                    }
                    else
                        if (dc.DataType.IsEnum)
                        {
                            string temp = dt.Rows[i][dc.ColumnName].ToString();
                            string str = Enum.Parse(dc.DataType, temp).ToString();
                            worksheet.Cells[i + beginRow, j + beginColumn] = str;
                        }
                        else
                        {
                            string str = dt.Rows[i][dc.ColumnName].ToString();
                            worksheet.Cells[i + beginRow, j + beginColumn] = str;
                        }
                }
            }
            worksheetTemp.Delete();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheetTemp);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
            worksheetTemp = null;
            worksheet = null;
        }

 

 3.打开Excel(传入Excel文件的路径时注意加上“”文件夹名称有可能含有空格)   注册Excel公式

 

 public string Register = @"%windir%\Microsoft.NET\Framework\v4.0.30319\regasm /tlb /codebase "+path;
 public string Register64 = @"%windir%\Microsoft.NET\Framework64\v4.0.30319\regasm /tlb /codebase "+path;

 

         /// <summary>
         /// 执行
       /// </summary>
        /// <param name="dosCommand">dos命令</param>
        /// <param name="milliseconds">等待命令执行的时间（单位：毫秒），如果设定为0，则无限等待</param>
        /// <returns>返回输出，如果发生异常，返回空字符串</returns>
        public static string ExecuteCmd(string cmd,  int milliseconds)
        {
            string output = string.Empty;
            Process process = new Process();     //创建进程对象
            ProcessStartInfo startInfo = new ProcessStartInfo();
            startInfo.FileName = "cmd.exe";      //设定需要执行的命令
            //startInfo.Arguments = argums;   //设定参数，其中的“/C”表示执行完命令后马上退出
            startInfo.UseShellExecute = false;     //不使用系统外壳程序启动
            startInfo.RedirectStandardInput = true;   //不重定向输入
            startInfo.RedirectStandardOutput = true;   //重定向输出
            startInfo.RedirectStandardError = true;
            startInfo.CreateNoWindow = true;     //不创建窗口

            process.EnableRaisingEvents = true;
            process.Exited += new EventHandler(process_Exited);
            process.StartInfo = startInfo;
            try
            {
                if (process.Start())       //开始进程
                {
                    process.StandardInput.WriteLine(cmd);
                    process.StandardInput.WriteLine("exit");
                    output = process.StandardOutput.ReadToEnd();//读取进程的输出
                    process.WaitForExit();
                }
            }
            catch
            {
                output = "error";
            }
            finally
            {
                if (process != null)
                    process.Close();
            }
            return output;
        }

        static void process_Exited(object sender, EventArgs e)
        {
            //
        }

 

 Excel 公式编写

[Guid("54BD7DE7-3259-40AF-A263-EAF8395C8CCC")]
[ClassInterface(ClassInterfaceType.AutoDual)]
[ComVisible(true)]

 

COM Related 注册与卸载 函数

 [ComRegisterFunction]
        public static void RegisterFunction(Type type)
        {
            Microsoft.Win32.Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type, "Programmable"));
            Microsoft.Win32.RegistryKey registryKey = Microsoft.Win32.Registry.ClassesRoot.OpenSubKey(GetSubKeyName(type, "InprocServer32"), true);
            registryKey.SetValue("", Environment.SystemDirectory + @"\mscoree.dll", Microsoft.Win32.RegistryValueKind.String);
        }

        [ComUnregisterFunction]
        public static void UnregisterFunction(Type type)
        {
            Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(type, "Programmable"), false);
        }

        private static string GetSubKeyName(Type type, string subKeyName)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"CLSID\{");
            sb.Append(type.GUID.ToString().ToUpper());
            sb.Append(@"}\");
            sb.Append(subKeyName);
            return sb.ToString();
        }

 

 类加载初始化注册设置App.Config

 private static void RegSettings()
        {
            bool change = false;
            Assembly assembly;
            assembly = Assembly.GetCallingAssembly();
            ExeConfigurationFileMap configFileMap = new ExeConfigurationFileMap();
            Uri uri = new Uri(Path.GetDirectoryName(assembly.CodeBase));
            configFileMap.ExeConfigFilename = Path.Combine(uri.LocalPath, assembly.GetName().Name + ".dll.config"); ;
            var config = ConfigurationManager.OpenMappedExeConfiguration(configFileMap, ConfigurationUserLevel.None);
            var curConfig = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);

            BindingsSection servicesSection = config.GetSection("system.serviceModel/bindings") as BindingsSection;
            BindingsSection curServicesSection = curConfig.GetSection("system.serviceModel/bindings") as BindingsSection;
            if (curServicesSection == null)
            {
                curConfig.Sections.Add(null, servicesSection);
            }
            else
            {
                foreach (NetNamedPipeBindingElement bind in servicesSection.NetNamedPipeBinding.Bindings)
                {
                    if (!curServicesSection.NetNamedPipeBinding.Bindings.ContainsKey(bind.Name))
                    {
                        curServicesSection.NetNamedPipeBinding.Bindings.Add(bind);
                        change = true;
                    }
                }
                foreach (NetTcpBindingElement bind in servicesSection.NetTcpBinding.Bindings)
                {
                    if (!curServicesSection.NetTcpBinding.Bindings.ContainsKey(bind.Name))
                    {
                        curServicesSection.NetTcpBinding.Bindings.Add(bind);
                        change = true;
                    }
                }
                foreach (BasicHttpBindingElement bind in servicesSection.BasicHttpBinding.Bindings)
                {
                    if (!curServicesSection.BasicHttpBinding.Bindings.ContainsKey(bind.Name))
                    {
                        curServicesSection.BasicHttpBinding.Bindings.Add(bind);
                        change = true;
                    }
                }
                foreach (WSHttpBindingElement bind in servicesSection.WSHttpBinding.Bindings)
                {
                    if (!curServicesSection.WSHttpBinding.Bindings.ContainsKey(bind.Name))
                    {
                        curServicesSection.WSHttpBinding.Bindings.Add(bind);
                        change = true;
                    }
                }
            }


            ClientSection clientSection = config.GetSection("system.serviceModel/client") as ClientSection;
            ClientSection curClientSection = curConfig.GetSection("system.serviceModel/client") as ClientSection;
            if (curClientSection == null)
            {
                curConfig.Sections.Add(null, servicesSection);
            }
            else
            {
                foreach (ChannelEndpointElement bind in clientSection.Endpoints)
                {
                    if (change)
                    {
                        curClientSection.Endpoints.Add(bind);
                    }
                }
            }


            if (change == true)
            {
                curConfig.Save();
                ConfigurationManager.RefreshSection("system.serviceModel/bindings");
                ConfigurationManager.RefreshSection("system.serviceModel/client");
            }
            //foreach (ConfigurationSectionGroup item in config.SectionGroups["system.serviceModel"])
            {

                //Debug.WriteLine(config.SectionGroups["system.serviceModel"].SectionGroups["bindings"].SectionGroups["netNamedPipeBinding"].Sections["NetNamedPipeBinding_IHisDataRpt"]);
                //ConfigurationSectionGroup obj = config.SectionGroups["system.serviceModel"];
                //foreach (ConfigurationSection se in obj.Sections)
                //{
                //    Debug.WriteLine(obj.ToString());
                //}
            }
            //foreach (ConfigurationSectionGroup item in config.SectionGroups["system.serviceModel"])
            //{
            //    //ConfigurationManager.AppSettings.Add(item.Key, item.Value);
            //    Debug.WriteLine(item.Name);
            //    ConfigurationManager.AppSettings["system.serviceModel"].
            //}
            //foreach (ConnectionStringSettings item in config.ConnectionStrings.ConnectionStrings)
            //{
            //    ConfigurationManager.ConnectionStrings.Add(item);
            //}
        }