Problem:
When creating an Excel file from within COBOL.Net the formatting on numeric cells is lost, see example :-
1) Tthe value 123.40 is in a cell, but when opened the file it shows 123.4
2) Also the same with 00100, it shows 100
Resolution:
Set the number format on ths cells you require. The "NumberFormat" property on a Range object lets you do this. See the Microsoft doucmentation on this property for full details on what can be set.
Some example code for this is:-
$set sourceformat(variable)
program-id. MSExcel.
environment division.
configuration section.
repository.
class cExcel as "Microsoft.Office.Interop.Excel.ApplicationClass"
class cWorkbook as "Microsoft.Office.Interop.Excel.WorkbookClass"
class IRange as "Microsoft.Office.Interop.Excel.Range"
class cType as "System.Type"
class cMarshal as "System.Runtime.InteropServices.Marshal"
class cObject as "System.Object"
class cXlSaveAsAccessMode as "Microsoft.Office.Interop.Excel.XlSaveAsAccessMode"
class cEnvironment as "System.Environment"
class cString as "System.String"
.
data division.
working-storage section.
01 ExcelObject object reference cExcel.
01 WorkBook object reference CWORKBOOK.
01 Cell object reference IRANGE.
01 LoopCount pic xx comp-5.
01 theValue decimal.
01 saveStr string.
01 currentdir string.
01 STATCODE PIC X(2) COMP-5.
procedure division.
*> Create a new instance of Microsoft Excel
invoke cExcel "new" returning ExcelObject
*> Make Excel visible
set ExcelObject::"Visible" to true
*> Add a new Workbook
invoke ExcelObject::"Workbooks"::"Add"(cType::"Missing") returning WorkBook.
*> Now loop, filling in the cells A1, B1 and C1 with
*> the numbers 1, 2 and 3 respectively
perform varying LoopCount from 1 by 1
until LoopCount > 3
set cell to ExcelObject::"Cells"::"Item"(1,LoopCount) as IRange
set Cell::"Value2" to LoopCount
invoke cMarshal::"ReleaseComObject"(Cell)
end-perform
perform varying LoopCount from 1 by 1
until LoopCount > 3
set cell to ExcelObject::"Cells"::"Item"(2,LoopCount) as IRange
set theValue to LoopCount * 100.25
*>multiply LoopCount by 100.33 giving thevalue
set Cell::"Value2" to thevalue
invoke cMarshal::"ReleaseComObject"(Cell)
end-perform
set cell to ExcelObject::"Range"("A2","C2")
set cell::"NumberFormat" to "00000.00"
set currentdir to cEnvironment::"CurrentDirectory"()
CALL "CBL_DELETE_FILE" USING "MyExcelSheet.xls " RETURNING STATCODE
set savestr to cString::"Concat"(currentdir,"\\MyExcelSheet.xls")
*>set saveStr to "MyExcelSheet.xls"
invoke WorkBook::"SaveAs"(saveStr
,cType::"Missing"
,cType::"Missing"
,cType::"Missing"
,cType::"Missing"
,cType::"Missing"
,cXlSaveAsAccessMode::"xlNoChange"
,cType::"Missing"
,cType::"Missing"
,cType::"Missing"
,cType::"Missing"
,cType::"Missing")
*> Close the WorkBook, discarding the contents
invoke WorkBook::"Close"(false,cType::"Missing",cType::"Missing")
*> Exit Excel
invoke ExcelObject "Quit"
stop run.
goback.
