svyUtils Excel - numberCellStyle

Questions and answers on designing your Servoy solutions, database modelling and other 'how do I do this' that don't fit in any of the other categories

svyUtils Excel - numberCellStyle

Postby bcusick » Tue Apr 20, 2021 7:10 pm

Hey Guys,

First of all - AWESOME JOB on the "update" webinars! Exciting stuff!!

I'm having an issue with setting the numberCellStyle using the svyUtils...

My output puts a "." after ALL number cells (e.g. 0. , 5. , etc.). I had a look through svyExcelUtils.js and tried this code:

Code: Select all
numberCellStyle = workbook.createCellStyle();
numberCellStyle.cloneStyleFrom(workbook.rowStyle);
numberCellStyle.setAlignment(scopes.svyExcelUtils.ALIGNMENT.RIGHT);
numberCellStyle.setDataFormat("#,###,###");
workbook.numberCellStyle = numberCellStyle;


but no luck in removing the "." in the number cells. I'm SURE it's a stupid user error - but I would appreciate any point in the right direction... :D

Bob
Bob Cusick
bcusick
 
Posts: 1255
Joined: Wed Apr 23, 2003 11:27 pm
Location: Thousand Oaks, CA USA

Re: svyUtils Excel - numberCellStyle

Postby patrick » Wed Apr 21, 2021 2:31 pm

Not exactly sure what you do with that style and why it is cloned from what etc., but this works for me and might help you:

Code: Select all
var wb = scopes.svyExcelUtils.createWorkbook(scopes.svyExcelUtils.FILE_FORMAT.XLSX);
var cf = wb.createCellStyle();
cf.setDataFormat('#,###,###');
var sheet = wb.createSheet('Test');
for (var i = 0; i < 20; i++) {
   var row = sheet.createRow(i + 1);
   var cell = row.createCell(1);
   cell.setCellValue(Math.random() * 1000000, cf);
}
   
wb.writeToFile('D:\\test.xlsx')
Patrick Ruhsert
Servoy DACH
patrick
 
Posts: 3703
Joined: Wed Jun 11, 2003 10:33 am
Location: Munich, Germany

Re: svyUtils Excel - numberCellStyle

Postby patrick » Wed Apr 21, 2021 2:33 pm

Ah, I see. You are doing workbook.numberCellStyle = xx. That numberCellStyle property does not exist. You have to assign styles to cell (see my example).
Patrick Ruhsert
Servoy DACH
patrick
 
Posts: 3703
Joined: Wed Jun 11, 2003 10:33 am
Location: Munich, Germany

Re: svyUtils Excel - numberCellStyle

Postby bcusick » Wed Apr 21, 2021 4:46 pm

Ah! Thanks Patrick - I really appreciate your help!

I was using the dataset workbook creator - hoping I didn't have to go through and create each cell...

Thanks again!
Bob Cusick
bcusick
 
Posts: 1255
Joined: Wed Apr 23, 2003 11:27 pm
Location: Thousand Oaks, CA USA

Re: svyUtils Excel - numberCellStyle

Postby bcusick » Wed Apr 21, 2021 5:15 pm

Found an easier way - just documenting here in case someone else has the same issue.

You don't have to loop through the cells - you can just use setFormatForColumn()

Code: Select all
workbook.setFormatForColumn(1, "#");


You can also use it to set date formats:

Code: Select all
workbook.setFormatForColumn(1, "dd/MM/yyyy");


Hope this helps!

Bob
Bob Cusick
bcusick
 
Posts: 1255
Joined: Wed Apr 23, 2003 11:27 pm
Location: Thousand Oaks, CA USA


Return to Programming with Servoy

Who is online

Users browsing this forum: No registered users and 10 guests