asp.net(c#) 将dbf转换为xls或wps,并将数据的列名改成中文;并判断本机是否安装office2003,2007和wps2007,2010

using Microsoft.Office.Interop.Excel;//转换为excel时,需要引用此命名空间

using ET;//转换为wps时,需要引用此命名空间
using KSO;//转换为wps时,需要引用此命名空间
当转换为excel时,需要引入Microsoft.Office.Interop.Excel.dll;当转换为wps时,需要引入Interop.ET.dll,Interop.KSO.dll;

 

#region 查询注册表,判断本机是否安装office2003,2007和wps
        public int ExistsRegedit()
        {
            int ifused = 0;
            RegistryKey rk = Registry.LocalMachine;
            RegistryKey akey = rk.OpenSubKey(@"SOFTWARE\Microsoft\Office\11.0\Excel\InstallRoot\");//查询2003

    RegistryKey akey07 = rk.OpenSubKey(@"SOFTWARE\Microsoft\Office\12.0\Excel\InstallRoot\");//查询2007
            RegistryKey akeytwo = rk.OpenSubKey(@"SOFTWARE\Kingsoft\Office\6.0\common\");//查询wps
            //检查本机是否安装Office2003
            if (akey != null)
            {
                string file03 = akey.GetValue("Path").ToString();
                if (File.Exists(file03 + "Excel.exe"))
                {
                    ifused += 1;
                }
            }

     //检查本机是否安装Office2007

            if (akey07 != null)
            {
                string file07 = akey.GetValue("Path").ToString();
                if (File.Exists(file07 + "Excel.exe"))
                {
                    ifused += 2;
                }
            }

            //检查本机是否安装wps
            if (akeytwo != null)
            {
                string filewps = akeytwo.GetValue("InstallRoot").ToString();
                if (File.Exists(filewps + @"\office6\et.exe"))
                {
                    ifused += 4;
                }
            }
            return ifused;
        }
        #endregion

 

 

#region 将DBF文件放入DataSet
        private DataSet DbfToDs(string my_TablePath, string my_TableName)
        {
            //数据库连接定义
            OleDbConnection my_conn; //数据连接
            OleDbDataAdapter my_Adapter;//数据适配器
            DataSet my_Ds = null;
            ////数据库连接
            try
            {
                string path = my_TablePath.Substring(0, my_TablePath.LastIndexOf("\\")) + "\\";
                string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=dBASE IV;Persist Security Info=False;";
                string my_StrSelect = "SELECT * FROM " + my_TableName;
                my_conn = new OleDbConnection(connString);
                my_Adapter = new OleDbDataAdapter(my_StrSelect, my_conn);
                my_Ds = new DataSet();

                //填充数据集
                my_Adapter.Fill(my_Ds, my_TableName);
            }
            catch (Exception ex)
            {
                MessageBox.Show("选择文件格式不正确,请选择.dbf文件!", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            return my_Ds;
        }
        #endregions

 

#region 把数据文件导入到.xls文件
        public void ExportToExcel(DataSet ds)
        {
            if (ds.Tables.Count != 0)
            {
                //创建excel文件
                Microsoft.Office.Interop.Excel.Application myExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();

                //打开已有的文件
                Microsoft.Office.Interop.Excel.Workbook xBook = myExcel.Workbooks._Open(txtPath.Text , Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                //指定要操作的sheet
                Microsoft.Office.Interop.Excel.Worksheet xSheet = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets[1];

                #region 改成中文列名
                xSheet.Cells[1, 1] = "程序员";

                 .

      .

      .
                //改成中文列名时,可将中文列名放入string[]中,然后循环此数组,将第一行改成相应的中文列名             
                #endregion

                xBook.SaveAs(txtPath.Text, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                myExcel.Visible = true;

            }
            else
            {
                System.Windows.Forms.MessageBox.Show("No Data");
            }
        }
        #endregion

 

#region 把数据文件导出为wps
        public void ExportToWps(DataSet ds)
        {
            if (ds.Tables.Count > 0)
            {
                eTApp = new ET.Application();
                eTApp.Visible = false;
                eTWorkBook = eTApp.Workbooks.Open(txtPath.Text, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                eTWorkSheet = (ET.Worksheet)eTWorkBook.Worksheets[1];

                #region 改成中文列名
                eTWorkBook.ActiveSheet.Cells.set_Item(1, 1, "程序员")


                #endregion

                eTWorkBook.SaveAs(txtPath.Text, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, ETSaveAsAccessMode.etNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                eTApp.Visible = true;

            }

        }

        #endregion

当转换成xls或者wps时,一般都是循环dataset然后将数据写入每一个单元格,但是当数据量大时转换速度会很慢;所以将数据放入dataset后,可以使用file.copy(文件1,文件2);这样速度会快很多;

posted @   scgw  阅读(418)  评论(0编辑  收藏  举报
编辑推荐:
· .NET Core GC压缩(compact_phase)底层原理浅谈
· 现代计算机视觉入门之:什么是图片特征编码
· .NET 9 new features-C#13新的锁类型和语义
· Linux系统下SQL Server数据库镜像配置全流程详解
· 现代计算机视觉入门之:什么是视频
阅读排行:
· 【译】我们最喜欢的2024年的 Visual Studio 新功能
· 个人数据保全计划:从印象笔记迁移到joplin
· Vue3.5常用特性整理
· 重拾 SSH:从基础到安全加固
· 为什么UNIX使用init进程启动其他进程?
历史上的今天:
2007-09-19 [转]利用.NET中的反射机制实现IList到DataTable的转换
< 2011年9月 >
28 29 30 31 1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30 1
2 3 4 5 6 7 8
点击右上角即可分享
微信分享提示