dimanche 29 mars 2015

c# How to update 1st empty column in excel file using oledb connection

I would like to know if there is any way how to update/insert data into Excel file using oledb connection. Only restriction / problem I have are dependent cells.


Let's assume following excel table template:



| A | B | C
1 |Name | Salary | Taxes
2 |John | 20 000 | = [Salary] * .25
3 | | | = [Salary] * .25
4 | | | = [Salary] * .25
5 | | | = [Salary] * .25
6 | | |


The thing is that I am using excel file template that can be changed by user anytime. And I want to insert only values into columns A and B and valuce C should be calculated automatically (I know this cant be done via OleDb connection)


The main problem I am having is how to specify, that I want to update/insert data at row 3.


I tried using following command:



oleDbCommand.CommandText = string.Format("INSERT INTO {0} ({1}, {2}) VALUES ({3}, {4})", tableName, nameColumn, salaryColumn, nameValue, salaryValue)


but as a resault i've got this:



| A | B | C
1 | Name | Salary | Taxes
2 | John | 20 000 | = [Salary] * .25
3 | | | = [Salary] * .25
4 | | | = [Salary] * .25
5 | | | = [Salary] * .25
6 | Anna | 20 000 |


It inserted values at first emty row in sheet. But i am trying to achieve to insert it on first row:



WHERE (([Name] IS NULL OR [Name] = '') AND
([Salary] IS NULL or [Salary] = '')


I also tried using something like this:



oleDbCommand.CommandText = string.Format("Update [{0}] SET [{1}] = '{2}' WHERE [{3}] = '';", tableName, nameRow, nameValue, nameRow)


I expected this command to update all rows 3 to 5 but in fact it didnt change anything.


Any ideas, how to achieve update/insert on first row, that meets some kind of criteria?


Aucun commentaire:

Enregistrer un commentaire