How to Insert 0 Before Any Number in Excel using Aspose cells

Aspose.Cells Product Family
1

Hi,

I am using Aspose cells to generate a report. but i am facing a issue with number's which are prefixed with zero. for example if i set a number "000234" in a cell, and save it. When i open the excel i see just 234.

Is there a way to retain the 0 which is prefixed any number? can you please let me know if you have a solution to this issue ASAP.

Regards,

Mahesh

2

Hi Mahesh,

Thank you for considering Aspose.

Well, MS Excel does not allow to show / save a number with zeros prefixed, so I think the only option with you is to use the cell display type as “Text” to achieve your desired result. Please see the following sample code for your reference:

//Instantiating a Workbook object

Workbook workbook = new Workbook();


//Accessing the first worksheet in the Excel file

Worksheet worksheet = workbook.getWorksheets().getSheet(0);


Cells cells = worksheet.getCells();


Cell cell = cells.getCell("A1");


cell.setValue("00000234");


//Setting the display format of the cell

Style style = cell.getStyle();


style.setNumber(49);


cell.setStyle(style);


//Saving the modified Excel file in default format

workbook.save("C:\\output.xls",FileFormatType.DEFAULT);

Thank You & Best Regards,

3

Hi,

And, alternatively, you may also set custom number formats, see the sample code below:

Sample code:

//Instantiating a Workbook object
Workbook workbook = new Workbook();
//Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.getWorksheets().getSheet(0);
Cells cells = worksheet.getCells();
Cell cell = cells.getCell(“A1”);
cell.setValue(234);
//Setting the display format of the cell
Style style = cell.getStyle();
style.setCustom(“000000”);
cell.setStyle(style);
//Saving the modified Excel file in default format
workbook.save(“c:\output.xls”,FileFormatType.DEFAULT);


Thank you.