How to update an exsting cell in an .xlsm file?

Scott Klement's open source interface to the POI HSSF/XSSF Spreadsheet Project for RPG Programmers. http://www.scottklement.com/poi/
Post Reply
NicoWickersheim
Posts: 3
Joined: Wed Sep 29, 2021 1:57 pm

How to update an exsting cell in an .xlsm file?

Post 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); 


Image

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!
Scott Klement
Site Admin
Posts: 702
Joined: Sun Jul 04, 2021 5:12 am

Re: How to update an exsting cell in an .xlsm file?

Post 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); 
NicoWickersheim
Posts: 3
Joined: Wed Sep 29, 2021 1:57 pm

Re: How to update an exsting cell in an .xlsm file?

Post 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.
Scott Klement
Site Admin
Posts: 702
Joined: Sun Jul 04, 2021 5:12 am

Re: How to update an exsting cell in an .xlsm file?

Post 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.
NicoWickersheim
Posts: 3
Joined: Wed Sep 29, 2021 1:57 pm

Re: How to update an exsting cell in an .xlsm file?

Post by NicoWickersheim »

Hi Scott, thanks for your help!

I found my mistake, the cell format was wrong. Thanks again!
Post Reply