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 + "\r\n";
}
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.0\word\\InstallRoot\\");
RegistryKey akeytow = rk.OpenSubKey(@"SOFTWARE\\Microsoft\\Office\\12.0\word\\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();
}
}
}
浙公网安备 33010602011771号