ExcelReader.cs 类,看懂了以后,只管在用户的操作窗体,进行实例化,调用即可!!
using System;
using System.Data;
using System.Data.OleDb;
namespace utilities
{
/// <summary>
/// Summary description for ExcelReader.
/// </summary>
public class ExcelReader : IDisposable
{
#region Variables
private int[] _PKCol;
private string _strExcelFilename;
private bool _blnMixedData=true;
private bool _blnHeaders=false;
private string _strSheetName;
private string _strSheetRange;
private bool _blnKeepConnectionOpen=false;
private OleDbConnection _oleConn;
private OleDbCommand _oleCmdSelect;
private OleDbCommand _oleCmdUpdate;
#endregion
#region properties
public int[] PKCols
{
get {return _PKCol;}
set {_PKCol=value;}
}
public string ColName(int intCol)
{
string sColName="";
if (intCol<26)
sColName= Convert.ToString(Convert.ToChar((Convert.ToByte((char) 'A')+intCol)) );
else
{
int intFirst = ((int) intCol / 26);
int intSecond = ((int) intCol % 26);
sColName= Convert.ToString(Convert.ToByte((char) 'A')+intFirst);
sColName += Convert.ToString(Convert.ToByte((char) 'A')+intSecond);
}
return sColName;
}
public int ColNumber(string strCol)
{
strCol = strCol.ToUpper();
int intColNumber=0;
if (strCol.Length>1)
{
intColNumber = Convert.ToInt16(Convert.ToByte(strCol[1])-65);
intColNumber += Convert.ToInt16(Convert.ToByte(strCol[1])-64)*26;
}
else
intColNumber = Convert.ToInt16(Convert.ToByte(strCol[0])-65);
return intColNumber;
}
public String[] GetExcelSheetNames()
{
System.Data.DataTable dt = null;
try
{
if (_oleConn ==null) Open();
// Get the data table containing the schema
dt = _oleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if(dt == null)
{
return null;
}
String[] excelSheets = new String[dt.Rows.Count];
int i = 0;
// Add the sheet name to the string array.
foreach(DataRow row in dt.Rows)
{
string strSheetTableName = row["TABLE_NAME"].ToString();
excelSheets[i] = strSheetTableName.Substring(0,strSheetTableName.Length-1);
i++;
}
return excelSheets;
}
catch(Exception)
{
return null;
}
finally
{
// Clean up.
if(this.KeepConnectionOpen==false)
{
this.Close();
}
if(dt != null)
{
dt.Dispose();
dt=null;
}
}
}
public string ExcelFilename
{
get { return _strExcelFilename;}
set { _strExcelFilename=value;}
}
public string SheetName
{
get { return _strSheetName;}
set { _strSheetName=value;}
}
public string SheetRange
{
get {return _strSheetRange;}
set
{
if (value.IndexOf(":")==-1) throw new Exception("Invalid range length");
_strSheetRange=value;}
}
public bool KeepConnectionOpen
{
get { return _blnKeepConnectionOpen;}
set {_blnKeepConnectionOpen=value;}
}
public bool Headers
{
get { return _blnHeaders;}
set { _blnHeaders=value;}
}
public bool MixedData
{
get {return _blnMixedData;}
set {_blnMixedData=value;}
}
#endregion
#region Methods
#region Excel Connection
private string ExcelConnectionOptions()
{
string strOpts="";
if (this.MixedData ==true)
strOpts += "Imex=2;";
if (this.Headers==true)
strOpts += "HDR=Yes;";
else
strOpts += "HDR=No;";
return strOpts;
}
private string ExcelConnection()
{
return
@"Provider=Microsoft.Jet.OLEDB.4.0;" +
@"Data Source=" + _strExcelFilename + ";" +
@"Extended Properties=" + Convert.ToChar(34).ToString() +
@"Excel 8.0;"+ ExcelConnectionOptions() + Convert.ToChar(34).ToString();
}
#endregion
#region Open / Close
public void Open()
{
try
{
if (_oleConn !=null)
{
if (_oleConn.State==ConnectionState.Open)
{
_oleConn.Close();
}
_oleConn=null;
}
if (System.IO.File.Exists(_strExcelFilename)==false)
{
throw new Exception("Excel file " + _strExcelFilename + "could not be found.");
}
_oleConn = new OleDbConnection(ExcelConnection());
_oleConn.Open();
}
catch (Exception ex)
{
throw ex;
}
}
public void Close()
{
if (_oleConn !=null)
{
if (_oleConn.State != ConnectionState.Closed)
_oleConn.Close();
_oleConn.Dispose();
_oleConn=null;
}
}
#endregion
ExcelReader.cs
最新推荐文章于 2024-07-25 22:42:19 发布