svyUtils Excel - numberCellStyle

Questions
1

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:

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

2

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:

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')
3

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).

4

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!

5

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()

workbook.setFormatForColumn(1, "#");

You can also use it to set date formats:

workbook.setFormatForColumn(1, "dd/MM/yyyy");

Hope this helps!

Bob