In my Previous Post I had specified how we can communicate with word and excel using ComCreateObject function available in Siebel.

Though one important thing to know is that you can only use this API in Server Scripts it will not work in Browser Script.

But recently while browsing Siebel forum in ITToolbox I came across a interesting problem while a user was trying to work with excel using ComCreateObject.

Problem:

While trying to write values of an ID Field in excel file leading zeros were being erased from that value by excel for example

If we write 000077788666 in the excel file, when we open the file the value that we see will be 77788666. All the leading zeros were erased by excel.

Solution:

The only solution possible was to ask excel to treat it as text value not a number value. And users came up with two solutions to make excel treat is a Text Cell

1. Set the format of the cell with help of this line of code 

So the final code will look something like this
var ExcelApp = new ComCreateObject(”Excel.Application”);
if (ExcelApp) {
ExcelApp.Visible = true;
ExcelApp = ExcelApp.Workbooks.Add();
ExcelApp.ActiveSheet.Cells(3,3).Value.NumberFormat = “@”
ExcelApp.ActiveSheet.Cells(3,3).Value = “000077788666″;
}
2. Prefix a ‘ (apostropy) in front of the value which would result in the following code

var ExcelApp = new ComCreateObject(”Excel.Application”);
if (ExcelApp) {
ExcelApp.Visible = true;
ExcelApp = ExcelApp.Workbooks.Add();
ExcelApp.ActiveSheet.Cells(3,3).Value = “‘000077788666′”;
}

One of these should work for you if you were to face this kind of problem.

OkAvarageGoodVery GoodExcellent (No Ratings Yet)
Loading ... Loading ...