using System; using System.Collections.Generic; using System.Linq; using System.Text; using Microsoft.Win32; using System.IO; using System.Data.OleDb; using System.Data; namespace BeiJing.ISS.Common { /// <summary> /// <author>pjy</author> /// <date>2013/01/09</Date> /// <describe>Excel帮助类</describe> /// </summary> public class ExcelHelper { private string connStr; private OleDbConnection conn = null; private OleDbCommand comm = null; /// <summary> /// <author>pjy</author> /// <date>2013/01/09</Date> /// <describe>构造函数初始化，把Excel的数据封装到DataTable中，该构造函数在导入数据到数据库时使用。</describe> /// </summary> /// <param name="filePath">上传的Excel的存放路径</param> /// <param name="dt">Excel里要插入数据库中的数据结果集</param> public ExcelHelper(string filePath, out DataTable dt) { //动态构造连接字符串 //ExcelVersion03() ? "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'" : string fileExt = Path.GetExtension(filePath).ToLower(); if (fileExt == ".xls") { this.connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'"; } else { this.connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'"; } conn = new OleDbConnection(connStr); try { conn.Open(); } catch (OleDbException ex) { throw new ApplicationException("打开Excel访问连接时发生异常， 错误信息：" + ex.Message); } string sheetName = GetSheetName(); dt = new DataTable(sheetName); string sql = "select * from [" + sheetName + "]"; comm = new OleDbCommand(sql, conn); try { using (OleDbDataAdapter da = new OleDbDataAdapter(comm)) { da.Fill(dt); } } catch (OleDbException ex) { throw new ApplicationException("读取Excel中的数据时发生异常，错误信息：" + ex.Message); } finally { this.DisposeResource(); } } /// <summary> /// <author>龚传波</author> /// <date>2013/05/06</Date> /// 构造函数初始化，把Excel的数据封装到DataSet中，该构造函数在导入数据到数据库时使用。 /// </summary> /// <param name="filePath">上传的Excel的存放路径</param> /// <param name="sheetNames">表名称，多个用','隔开，不含$</param> /// <param name="ds">Excel里要插入数据库中的数据结果集</param> public ExcelHelper(string filePath, string sheetNames, out DataSet ds) { //动态构造连接字符串 //ExcelVersion03() ? "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'" : string fileExt = Path.GetExtension(filePath).ToLower(); if (fileExt == ".xls") { this.connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'"; } else { this.connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'"; } conn = new OleDbConnection(connStr); try { conn.Open(); } catch (OleDbException ex) { throw new ApplicationException("打开Excel访问连接时发生异常， 错误信息：" + ex.Message); } string[] sheetNameArray = sheetNames.Split(','); string sheetName = ""; ds = new DataSet(); try { int tablesCount=sheetNameArray.Length; for (int i = 0; i < tablesCount; i++) { sheetName = sheetNameArray[i] + "$"; DataTable dt = new DataTable(sheetName); string sql = "select * from [" + sheetName + "]"; comm = new OleDbCommand(sql, conn); try { using (OleDbDataAdapter da = new OleDbDataAdapter(comm)) { da.Fill(dt); ds.Tables.Add(dt);//添加DataTable到DataSet } } catch (OleDbException ex) { throw new ApplicationException("读取Excel中的数据时发生异常，错误信息：" + ex.Message); } } } catch (Exception e) { throw new ApplicationException("读取Excel中的数据时发生异常，错误信息：" + e.Message); } finally { this.DisposeResource(); } } public string GetSheetNames() { string sheetNames = ""; DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); for (int i = 0; i < dt.Rows.Count; i++) { string tbName = dt.Rows[i]["TABLE_NAME"].ToString().Trim(); sheetNames += tbName + " "; } return sheetNames; } public ExcelHelper() { } /// <summary> /// <author>pjy</author> /// <date>2013/01/09</Date> /// <describe>检查本机是否安装office2003</describe> /// </summary> /// <returns>如果安装了Office 2003，则返回true</returns> private bool ExcelVersion03() { bool ifused = false; RegistryKey rk = Registry.LocalMachine; RegistryKey akey = rk.OpenSubKey(@"SOFTWARE\Microsoft\Office\11.0\Word\InstallRoot\"); //检查本机是否安装office2003 if (akey != null) { string file03 = akey.GetValue("Path").ToString(); if (File.Exists(file03 + "Excel.exe")) { ifused = true; } } return ifused; } /// <summary> /// <author>pjy</author> /// <date>2013/01/09</Date> /// <describe>检查本机是否安装office2007</describe> /// </summary> /// <returns>如果安装了Office 2007，则返回true</returns> private bool ExcelVersion07() { bool ifused = false; RegistryKey rk = Registry.LocalMachine; RegistryKey akeytwo = rk.OpenSubKey(@"SOFTWARE\Microsoft\Office\12.0\Word\InstallRoot\"); //检查本机是否安装office2007 if (akeytwo != null) { string file07 = akeytwo.GetValue("Path").ToString(); if (File.Exists(file07 + "Excel.exe")) { ifused = true; } } return ifused; } /// <summary> /// <author>pjy</author> /// <date>2013/01/09</Date> /// <describe>判断可执行的Excel应用程序是否存在</describe> /// </summary> /// <returns>存在则返回true</returns> public bool IsExcellInstalled() { Type type = Type.GetTypeFromProgID("Excel.Application"); return type != null; } /// <summary> /// <author>pjy</author> /// <date>2013/01/09</Date> /// <describe>查询注册表某键值是否存在</describe> /// </summary> /// <returns></returns> public bool ExistsRegedit() { bool ifused = false; RegistryKey rk = Registry.LocalMachine; RegistryKey akey = rk.OpenSubKey(@"SOFTWARE\Microsoft\Office\11.0word\InstallRoot\"); RegistryKey akeytow = rk.OpenSubKey(@"SOFTWARE\Microsoft\Office\12.0word\InstallRoot\"); if (akeytow != null) { string file07 = akeytow.GetValue("Path").ToString(); if (File.Exists(file07 + "Excel.exe")) { ifused = true; } } else if (akey != null) { string file03 = akey.GetValue("Path").ToString(); if (File.Exists(file03 + "Excel.exe")) { ifused = true; } } return ifused; } /// <summary> /// <author>pjy</author> /// <date>2013/01/09</Date> /// <describe>获取工作簿名称, 只获取第一个工作簿名称</describe> /// </summary> /// <returns>第一个工作簿名称</returns> private string GetSheetName() { try { return conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }).Rows[0]["TABLE_NAME"].ToString().Trim(); } catch (OleDbException ex) { throw new ApplicationException("获取工作簿名称时发生异常，错误信息：" + ex.Message); } } /// <summary> /// <author>pjy</author> /// <date>2013/01/09</Date> /// <describe>释放资源</describe> /// </summary> private void DisposeResource() { if (comm != null) { comm.Dispose(); comm = null; } if (conn != null) { conn.Close(); conn.Dispose(); conn = null; } GC.Collect(); } } }