.NET does not use OLE automation to convert Excel to DataSet [control]

#region "excel" private 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; #ENDREGON

#Region Properties

Public int [] pkcols {get {return _pkcol;} set {_pkcol = value;}}

Public string colname (int INTCOL) {string scolname = ""; if (intcol <26) scolname = convert.toCHRING (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.dataable dt = null;

Try {if (_oleconn == null) open (); // get the data Table contact The schema dt = _oleconn.getoledbschematable (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;} catren 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 == ") {} else {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

#Region Command Select Private Bool SetsheetQuerySelect () {try {i (_oleconn == null) {throw new exception ("Connection is unassigned or closed.");}

IF (_STRSHEETNAME.LENGTH == 0) Throw new Exception ("SheetName Was Not Assigned.");

_OLECMDSELECT = New OLEDBCommand (@ "select * from [" _STRSHEETNAME "$" _STRSHEETRANGE "]", _ OLECONN); Return True;} catch (exception ex) {throw EX;}

} #Endregion

#REGION Simple Utilities Private String ADDWITHCOMMA (STRING STRSOURCE, STRING stradd) {if (strsource! = "") strsource = strsource = ","; return strsource stradd;}

Private string addwithand (string strsource, string stradd) {if (strsource! = ") strsource = strsource =" and "; return strsource stradd;} #ENDREGION

Private oledbdataadapter setsheetQueryadapter (DataTable DT) {// deleding in Excel Workbook Is Not Possible // So this Command is not defined try {

IF (_oleconn == null) {Throw new exception (""} if (_STRSHEETNAME.LENGTH == 0) Throw new Exception ("SheetName Was Not Assigned."); if (pkcols == null) throw new Exception ( "Can not update excel sheet with no primarykey set."); if (PKCols.Length <1) throw new Exception ( "Can not update excel sheet with no primarykey set."); OleDbDataAdapter oleda = new OleDbDataAdapter ( _oleCmdSelect); string strUpdate = ""; string strInsertPar = ""; string strInsert = ""; string strWhere = ""; for (int iPK = 0; iPK

for (int iCol = 0; iCol

String strable = "[" this.sheetname "$" this.sheetRange "]"; strinsert = "INSERT INTO" STRTABLE "(" Strinsert ") VALUES (" StrinsertPar ")" strUpdate = "Update" strTable "Set" strUpdate strWhere; oleda.InsertCommand = new OleDbCommand (strInsert, _oleConn); oleda.UpdateCommand = new OleDbCommand (strUpdate, _oleConn); OleDbParameter oleParIns = null; OleDbParameter oleParUpd = null; For (int icol = 0; icol

#region command Singe Value Update private bool SetSheetQuerySingelValUpdate (string strVal) {try {if (_oleConn == null) {throw new Exception ( "Connection is unassigned or closed.");} if (_strSheetName.Length == 0) throw new "" SheetName Was Not Assigned. ");

_OLECMDUPDATE = New OLEDBCOMMAND (@ "Update [" _STRSHEETNAME "$" _STRSHETRANGE "] set f1 =" strval, _oleconn); return true;} catch (exception ex) {throw ex;}

} #Endregion

Public void setprimarykeyKey (int INTCOL) {_pkcol = new int [1] {intcol};

Public DataTable getTable () {Return getTable ("excelTable");

Private Void SetPrimaryKey (DataTable DT) {Try {if (pkcols! = null) {// set the primary key if (pkcols.length> 0) {Datacolumn [] DC; DC = New Datacolumn [PKCOLS.LENGTH]; for ( INT i = 0; i

}}}} Catch (exception ex) {throw ex;}} public datatable gettable (string strablename) {try {// open and query if (_oleconn == null) open (); if (_oleconn.State! = ConnectionsTate.Open Throw new Exception ("Connection Cannot Open Error."); If (setsheetqueryselect () == false

// Fill table OleDbDataAdapter oleAdapter = new OleDbDataAdapter (); oleAdapter.SelectCommand = _oleCmdSelect; DataTable dt = new DataTable (strTableName); oleAdapter.FillSchema (dt, SchemaType.Source); oleAdapter.Fill (dt); if (this.Headers == false) {if (_STRSHEETRANGE.INDEXOF (":")> 0) {string firstcol = _STRSHEETRANGE.SUBSTRING (0, _STRSHEETRANGE.INDEXOF ("::") - 1); int rtcol = this.colnumber (firstcol); For (int I = 0; inti

private void CheckPKExists (DataTable dt) {if (dt.PrimaryKey.Length == 0) if (this.PKCols = null!) {SetPrimaryKey (dt);} else throw new Exception ( "Provide an primary key to the datatable") PUBLIC DATATABLE SETTABLE (DATATABLE DT) {Try {DataTable DTChanges = Dt.getChanges (); if (DTChanges == NULL) Throw new Exception ("there is are no change!"); Checkpkexists (dt); / / Open and query if (_oleconn == null) open (); if (_oleconn.state! = ConnectionsTate.Open) throw new exception ("Connection Cannot Open Error); if (setsheetQueryselect () == false Return Null ;

// Fill table OleDbDataAdapter oleAdapter = SetSheetQueryAdapter (dtChanges); oleAdapter.Update (dtChanges); // Clean up _oleCmdSelect.Dispose (); _oleCmdSelect = null; oleAdapter.Dispose (); oleAdapter = null; if (KeepConnectionOpen == false) Close (); return dt;} catch (exception ex) {throw ex;}} #region get / set single value

Public void setsinglecellRange (string strchool) {_strsheetrange = strchool ":" strCell;}

public object GetValue (string strCell) {SetSingleCellRange (strCell); object objValue = null; // Open and query if (_oleConn == null) Open (); if (! _oleConn.State = ConnectionState.Open) throw new Exception ( " Connection is not open error. ");

IF (setsheetQueryselect () == false) Return null; objvalue = _olecmdselect.executescalar ();

_olecmdselect.dispose (); _olecmdselect = null; if (KeepConnectionOpen == false) close (); Return ObjValue;}

Public void setValue (string strchool, object objvalue) {try {

SetSingleCellRange (strCell); // Open and query if (_oleConn == null) Open (); if (! _OleConn.State = ConnectionState.Open) throw new Exception ( "Connection is not open error."); If (SetSheetQuerySingelValUpdate ( Objvalue.toString ()) == false) Return; objvalue = _olecmdupdate.executenonury ();

_oleCmdUpdate.Dispose (); _oleCmdUpdate = null; if (KeepConnectionOpen == false) Close ();} catch (Exception ex) {throw ex;} finally {if (_oleCmdUpdate = null!) {_oleCmdUpdate.Dispose (); _oleCmdUpdate = null;}}} # endregion # endregion

public

#Region Dispose / Destructor Void Dispose () {io (_oleconn! = null) {_oleconn.dispose (); _oleconn = null;} f (_olecmdselect! = null) {_olecmdselect! = null (_olecmdselect = null;} // Dispose of remaining objects.} #Endregion #region ctor public excelreader () {// // Todo: add constructor logic here //} #ENDREGON}

#ndregion

private ExcelReader _exr = null; #region properties private string _strExcelFilename = ""; [Category ( "ExcelR"), Description ( "Excel file path")] public string ExcelPath {get {return _strExcelFilename;} set {this._strExcelFilename = Value;

}

Private bool _boolheaders = true;

[Category ("excelr"), Description ("EXCEL table contains a header")] public bool headers {get {return_boolheaders;} set {_boolheaders = value;}}

Private bool _boolmixeddata = true;

[Category ("excelr"), Description ("EXCEL table mixed data")] public bool mixeddata {get {return_boolmixeddata;} set {_boolmixeddata = value;}}

Private string _strsheetname = "";

[Category ("Excelr"), Description ("Excel Work Table Name"] Public String SheetName {Get {Return_STRSHETNAME;} set {_strsheetname = Value;}}

Private string _STRSHEETRANGE = ""; [Category ("ExcelR"), Description ("Excel Worksheet Area")] Public String SheetRsheetRange;} set {_strsheetRange = Value;}}

[Category ("excelr"), Description ("EXCEL worksheet")] public int sheetcount {get {ix (this._exr == null) {this._exr = new excelreader ();} this._exr.keepConnectionOpen = true; this._exr.ExcelFilename = _strExcelFilename; this._exr.Headers = this._boolHeaders; this._exr.MixedData = this._boolMixedData; _exr.Open (); int i = 0;. i = _exr.GetExcelSheetNames () Length _Exr.close (); returni}

}

#ndregion

#Region event Export SheetHandler_on Export Sheet; [Category ("Excelr"), Description ("Trigger each Exporting an Excel Work Table")] Public Event Export SheetHandler ON Export Sheet {Add {_on Export Sheet = Value;

} Remove {_on exported sheet = null;}

}

#ndregion

Public system.data.dataset exportDataSet () {system.data.dataset ds = new system.data.dataset ();

if (! system.io.file.exists (_STREXCELFILENAME)) {throw new system.exception ();} else {// Todo:

if (this._exr == null) {this._exr = new ExcelReader ();} this._exr.KeepConnectionOpen = true; this._exr.ExcelFilename = _strExcelFilename; this._exr.Headers = this._boolHeaders; this._exr. MixedData = this._boolmixeddata; _exr.open (); if (this._strsheetname.trim () == "") {string [] sheetnames = this._exr.geTexcelsheetNames (); ifetnames.length <1) {throw New system.exception (there is no work table in Excel ");

Foreach (String Str in Sheetnames) {_exr.sheetname = Str; DS.TABLES.ADD (_exr.gettable (str)); this._on exports sheet (STR);}} else {_exr.sheetname = this._strsheetname; if (_EXR.SHEETNAME.TRIM () == "") {throw new system.exception ("First of all, you need to specify a worksheet");} _exr.sheetrange = this.SheetRange; ds.tables.add (_exr.gettable) Sheetname); this._on exports sheet (_exr.sheetname); }_exr.close ();} Return DS;}}} ---------------- Use control samples ( Winform) -------------------

Using system.drawing; using system.componentmodel; using system.windows.form; using system.data ;. using system.data ;. USING SYSTEM.DATA;

Summary description of Namespace WindowsApplication3 {///