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

 

/// 
        /// 通过读取模板文件保存Excel        /// 
        ///         /// 模板文件路径名        /// 保存文件路径名        /// 开始行        /// 开始列        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;

 

/// 
         /// 执行       /// 
        /// dos命令        /// 等待命令执行的时间（单位：毫秒），如果设定为0，则无限等待        /// 
      
       返回输出，如果发生异常，返回空字符串
              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);            //}        }