title image


Smiley Re: Microsoft SQL Server: SQL Server 2005 Express Edition
geht auch mit einer Excel-Tabelle



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();

}



-----------------------------------------------------------------------------------





Zitat von unbekannter gegoogelter Quelle:





-----------------------------------------------------------------------------------





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



#region Command Select

private bool SetSheetQuerySelect()

{

try

{

if (_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)

{

// Deleting in Excel workbook is not possible

//So this command is not defined

try

{









if (_oleConn == null)

{

throw new Exception("Connection is unassigned or closed.");

}





if (_strSheetName.Length ==0)

throw new Exception("Sheetname was not assigned.");



if (PKCols == null)

throw new Exception("Cannot update excel sheet with no primarykey set.");

if (PKCols.Length<1)

throw new Exception("Cannot 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
{

strWhere = AddWithAnd(strWhere,dt.Columns[iPK].ColumnName + "=?");

}

strWhere =" Where "+strWhere;



for (int iCol=0;iCol
{

strInsert= AddWithComma(strInsert,dt.Columns[iCol].ColumnName);

strInsertPar= AddWithComma(strInsertPar,"?");

strUpdate= AddWithComma(strUpdate,dt.Columns[iCol].ColumnName)+"=?";

}



string strTable = "["+ 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
{

oleParIns = new OleDbParameter("?",dt.Columns[iCol].DataType.ToString());

oleParUpd = new OleDbParameter("?",dt.Columns[iCol].DataType.ToString());

oleParIns.SourceColumn =dt.Columns[iCol].ColumnName;

oleParUpd.SourceColumn =dt.Columns[iCol].ColumnName;

oleda.InsertCommand.Parameters.Add(oleParIns);

oleda.UpdateCommand.Parameters.Add(oleParUpd);

oleParIns=null;

oleParUpd=null;

}



for (int iPK=0;iPK
{

oleParUpd = new OleDbParameter("?",dt.Columns[iPK].DataType.ToString());

oleParUpd.SourceColumn =dt.Columns[iPK].ColumnName;

oleParUpd.SourceVersion = DataRowVersion.Original;

oleda.UpdateCommand.Parameters.Add(oleParUpd);

}

return oleda;

}

catch (Exception ex)

{

throw ex;

}



}



#region command Singe Value Update

private bool SetSheetQuerySingelValUpdate(object val)

{

string tmp = "";

tmp = val.ToString();

// if(val is string)

// {

// tmp = "'"+ val.ToString().Trim() + "'";

// }

// else

// {

// tmp = val.ToString();

// }

try

{

if (_oleConn == null)

{

throw new Exception("Connection is unassigned or closed.");

}



if (_strSheetName.Length ==0)

throw new Exception("Sheetname was not assigned.");



_oleCmdUpdate =new OleDbCommand(

@" Update ["

+ _strSheetName

+ "$" + _strSheetRange

+ "] set F1= " + tmp , _oleConn);

return true;

}

catch (Exception ex)

{

throw ex;

}





}

#endregion







public void SetPrimaryKey(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
{

dc[i] =dt.Columns[PKCols[i]];

}





dt.PrimaryKey = dc;



}

}

}

catch (Exception ex)

{

throw ex;

}

}



public DataTable GetTable(string strTableName)



{

try

{

//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 = 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 intCol = this.ColNumber(FirstCol);

for (int intI=0;intI
{

dt.Columns[intI].Caption =ColName(intCol+intI);

}

}

}

SetPrimaryKey(dt);

//Cannot delete rows in Excel workbook

dt.DefaultView.AllowDelete =false;



//Clean up

_oleCmdSelect.Dispose();

_oleCmdSelect=null;

oleAdapter.Dispose();

oleAdapter=null;

if (KeepConnectionOpen==false) Close();

return dt;



}

catch (Exception ex)

{

throw ex;

}

}





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 are no changes to be saved!");

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 strCell)

{

_strSheetRange = strCell + ":" + strCell;

}

public void SetSingleCellName(string name)

{

_strSheetRange = name;

}

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 strCell,string text)

{



try

{



SetSingleCellRange(strCell);

if (_oleConn ==null) Open();

if (_oleConn.State != ConnectionState.Open)

throw new Exception("Connection is not open error.");



if (SetSheetQuerySingelValUpdate(text)==false) return;

int result = _oleCmdUpdate.ExecuteNonQuery();



_oleCmdUpdate.Dispose();

_oleCmdUpdate=null;

if (KeepConnectionOpen==false) Close();

}

catch (Exception ex)

{

throw ex;

}

finally

{

if (_oleCmdUpdate != null)

{

_oleCmdUpdate.Dispose();

_oleCmdUpdate=null;

}

}



}



public void SetValue(int[] data,string spalte, int zeile)

{

int d = 0;

int result = 0;

if (_oleConn ==null) Open();



for(int i = 1;i
{

d = data[i];

try

{



SetSingleCellRange(spalte+zeile.ToString());



if (_oleConn.State != ConnectionState.Open)

{

throw new Exception("Connection is not open error.");

}



if (SetSheetQuerySingelValUpdate(d)==false) return;

if(data[i]>0)

{

try

{

result = _oleCmdUpdate.ExecuteNonQuery();

}

catch

{

result = -1;

}

}

zeile++;



}

catch (Exception ex)

{

throw ex;

}

}

_oleCmdUpdate.Dispose();

_oleCmdUpdate=null;

if (KeepConnectionOpen==false) Close();



}

public void SetValue(string strCell,int pages)

{



try

{



SetSingleCellRange(strCell);

//SetSingleCellName(strCell);

//Open and query

if (_oleConn ==null) Open();

if (_oleConn.State != ConnectionState.Open)

throw new Exception("Connection is not open error.");



if (SetSheetQuerySingelValUpdate(pages)==false) return;

int retval = _oleCmdUpdate.ExecuteNonQuery();



_oleCmdUpdate.Dispose();

_oleCmdUpdate=null;

if (KeepConnectionOpen==false) Close();

}

catch (Exception ex)

{

throw ex;

}

finally

{

if (_oleCmdUpdate != null)

{

_oleCmdUpdate.Dispose();

_oleCmdUpdate=null;

}

}



}

public void SetValueOnName(string cellName,int pages)

{



try

{



SetSingleCellName(cellName);

//Open and query

if (_oleConn ==null) Open();

if (_oleConn.State != ConnectionState.Open)

throw new Exception("Connection is not open error.");



if (SetSheetQuerySingelValUpdate(pages)==false) return;

int retval = _oleCmdUpdate.ExecuteNonQuery();



_oleCmdUpdate.Dispose();

_oleCmdUpdate=null;

if (KeepConnectionOpen==false) Close();

}

catch (Exception ex)

{

throw ex;

}

finally

{

if (_oleCmdUpdate != null)

{

_oleCmdUpdate.Dispose();

_oleCmdUpdate=null;

}

}



}

#endregion





#endregion



#region Dispose / Destructor

public void Dispose()

{

if (_oleConn !=null)

{

_oleConn.Dispose();

_oleConn=null;

}

if (_oleCmdSelect!=null)

{

_oleCmdSelect.Dispose();

_oleCmdSelect=null;

}

// Dispose of remaining objects.

}

#endregion



#region CTOR

public ExcelReader()

{

//

// TODO: Add constructor logic here

//

}

#endregion

}

public class clExcelData

{

public string cell = "";

public int number = 0;

public clExcelData(string Cell, int Number)

{

number = Number;

cell = Cell;

}

}





geschrieben von

Login

E-Mail:
  

Passwort:
  

Beitrag anfügen

Symbol:
 
 
 
 
 
 
 
 
 
 
 
 
 

Überschrift: