Write excel with an integer as reference

lawrence

I had the below .xls file:

SNo Scenario Result
a xyz Pass

I had the code: db.update(<dq>update [Sheet1$] set Scenario=<sq>updatedText<sq> where SNo=<sq>a<sq><dq>);

and the .xls file was updated as below:

SNo Scenario Result
1 updated Text Pass

Then I changed the setup like below:

SNo Scenario Result
1 xyz Pass

I had the code: db.update(<dq>update [Sheet1$] set Scenario=<sq>updatedText<sq> where SNo=<sq>1<sq><dq>);

The .xls file was not updated. Any clue?

Sumitra

Hi lawrence,

Can you try this:

db.update("update [Sheet1$] set Scenario='updatedText' where SNo='1'");

Check if this solves your issue.

For more details about working with Excel sheet:

http://sahi.co.in/w/working-with-excel-sheets

If you still are not able to update the xls file then let me know what is the error message you are getting.

Regards
Sumitra

lawrence

Hi Sumitra,

I tried - db.update("update [Sheet1$] set Scenario=updatedText where SNo='1'"); and it did not work for me.

I had referred:
http://sahi.co.in/w/working-with-excel-sheets
which do not support to solve my issue.

I do not get any error message while using:
db.update(<dq>update [Sheet1$] set Scenario=<sq>updatedText<sq> where SNo=<sq>1<sq><dq>);
but the file is not updated.

However the file gets updated while using:
db.update(<dq>update [Sheet1$] set Scenario=<sq>updatedText<sq> where SNo=<sq>a<sq><dq>);
which is not my requirement. Any more clue?

Sumitra

Hi lawrence,

Use the following code:

var db = _getDB("sun.jdbc.odbc.JdbcOdbcDriver","jdbc:odbc:Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=D:\\myfolder\\sample.xlsx;readOnly=false","","");
var $rs = db.select("select * from [Sheet1$]");
db.update("update [Sheet1$] set Scenario='UpdatedText' where SNo=1");
var $rs = db.select("select * from [Sheet1$]");
_assertEqual("UpdatedText", $rs[0]["Scenario"]);

Create an excel file on \\myfolder\\sample.xlsx with the contents:

SNo Scenario Result
1 abc Pass

Now, if you run the script you can see that the Excel sheet will be updated.

Let me know if this solves your issue.

Regards
Sumitra

lawrence

Hi Sumitra,

db.update("update [Sheet1$] set Scenario='UpdatedText' where SNo=1");

works fine.