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\");//查询2003RegistryKey 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[]中，然后循环此数组，将第一行改成相应的中文列名 #endregionxBook.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, "程序员") #endregioneTWorkBook.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);这样速度会快很多；