|
||||||||||||||||||||||||
|
||||||||||||||||||||||||
|
Announcements
Chapters
Services
Feature Zones
|
IntroductionThis project contains an However, due to restrictions in the Excel driver, it is not possible to delete rows from a table. Updating a empty range is also not an option. It's possible to read an range and updating or inserting an existing range. Excel has his own way of datatatyping the column. DaberElay made a response according to my article with:
If the OLEDB solution does not fit your needs, you may buy a component for it. There are some components to read or wrrite the Excel files without MS Excel and are able to edit. Here are some ideas. BackgroundFor a project, I needed to read and write MS Excel-files on a web server. The MS Excel file would be uploaded and be read on the server into a SQL Server database. Normally, I would have used the XML grammar Microsoft has published for the web. Unfortunately, this is a grammar supported by MS Excel 2002 or higher. It makes it easier to make a component to modify and read Excel workbooks. Only in my projects, the clients always use older software like MS Excel '97 that do not support this XML. I also like to choose a solution which uses just one version of Excel 2002 with a programmed converter class. Only this process will run on a web server where MS Office and its components are not scalable and not allowed. Also read what Microsoft says about it. So, I started a class that uses the OLEDB driver that can do some primary tasks with the uploaded Excel file. Using the codeThe demo form uses the following code to initialize the exr = new ExcelReader();
_dt = new DataTable("par");
exr.KeepConnectionOpen =true;
exr.ExcelFilename = _strExcelFilename;
exr.Headers =false;
exr.MixedData =true;
exr.SheetName = this.txtSheet.Text;
exr.SheetRange = this.txtRange.Text;
exr.SetPrimaryKey(0);
_dt = exr.GetTable();
First, create a new instance of this class. Also declare a The primary key is needed to be able to update the Excel sheet. It now supports just one primary key, but the class can be extended. If the table has no primary key, the How it's doneFirst, set the connection: 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
Open the connection: _oleConn = new OleDbConnection(ExcelConnection());
_oleConn.Open();
And just make a _oleCmdSelect =new OleDbCommand(
@"SELECT * FROM ["
+ _strSheetName
+ "$" + _strSheetRange
+ "]", _oleConn);
Fill the table with the OleDbDataAdapter oleAdapter = new OleDbDataAdapter();
oleAdapter.SelectCommand = _oleCmdSelect;
DataTable dt = new DataTable(strTableName);
oleAdapter.FillSchema(dt,SchemaType.Source);
oleAdapter.Fill(dt);
Updating the table: First set the primary key(s). Then call the update method to update the excel table. Try in the demo to update an empty range! An error will occur.if (this._intPKCol>-1)
{
int[] intPKCols = new int[] { _intPKCol};
_exr.PKCols = intPKCols;
}
_exr.SetTable(_dt);
History
|
|||||||||||||||||||||||
Joke 
Rant 
Admin