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