Page 1 of 1
How to update an exsting cell in an .xlsm file?
Posted: Wed Sep 29, 2021 2:13 pm
by NicoWickersheim
Hi, I want to update an existing cell in an Excel file (test.xlsm).
So I don't want to create a new cell in this file.
I want to update cell D4.
Here's my code:
Code: Select all
D book s like(SSWorkbook)
D sheet s like(SSSheet)
D row s like(SSRow)
D cell s like(SSCell)
D TempStr s like(jString)
D StrVal s 8000C
D ccsid(13488)
book = ss_open(path);
sheet = ss_getSheet(book: 0);
row = SSSheet_getRow(sheet: 3);
cell = SSRow_getCell(row: 3);
StrVal = 'testcell';
TempStr = new_String(%trim(StrVal));
SSCell_setCellValueStr(cell: TempStr);
SS_save(book:path);
But after saving the file, the content of the cell is still blank. I set the cell type in the file to "String".
Am I missing something that I still have to set or where can be the error here?
Thanks for your help!
Re: How to update an exsting cell in an .xlsm file?
Posted: Wed Sep 29, 2021 2:23 pm
by Scott Klement
It has been more than 10 years since I've worked with this stuff, I don't use it at my current job. But, my guess is that the spreadsheet isn't saving empty cells, or perhaps isn't saving the cell types. So when you go to retrieve the existing cell, it's not there.
Please try forcing it to create the cell if it's not there. Also, force the cell type to string just to be sure its set properly.
Code: Select all
book = ss_open(path);
sheet = ss_getSheet(book: 0);
row = SSSheet_getRow(sheet: 3);
cell = SSRow_getCell(row: 3);
if (cell = *null);
cell = SSRow_createCell(row: 3);
endif;
SSCell_setCellType(cell: CELL_TYPE_STRING);
StrVal = 'testcell';
TempStr = new_String(%trim(StrVal));
SSCell_setCellValueStr(cell: TempStr);
SS_save(book:path);
Re: How to update an exsting cell in an .xlsm file?
Posted: Wed Sep 29, 2021 3:13 pm
by NicoWickersheim
Thanks Scott, but the result also with your advices is the same.
My cell variable is filled with an Integer, so I think the procedure found the cell.
But when I open the file after saving it, this cell is empty.
Do you have any other suggestions as to what it could be?
Should I use a .xlsx instead of .xlsm? Or should I use the hssf-procedure instead of the ss-procedures?
I am thankful for any hint.
Re: How to update an exsting cell in an .xlsm file?
Posted: Wed Sep 29, 2021 9:33 pm
by Scott Klement
NicoWickersheim wrote: ↑Wed Sep 29, 2021 3:13 pm
Do you have any other suggestions as to what it could be?
Should I use a .xlsx instead of .xlsm? Or should I use the hssf-procedure instead of the ss-procedures?
If .xlsx meets your requirements, you should definitely use that! .xlsm is prone to malware, so you should always avoid it if you don't need the macro support it offers. I would not use the HSSF procedures anymore, that is for old 1990's style Excel documents.
I don't know why the example code isn't working. I suggest looking at the error messages to see what's going wrong.
Re: How to update an exsting cell in an .xlsm file?
Posted: Thu Sep 30, 2021 8:33 am
by NicoWickersheim
Hi Scott, thanks for your help!
I found my mistake, the cell format was wrong. Thanks again!