Leading zeros are getting removed while putting in the Excel using PutValue

Aspose.Cells Product Family
1

Hi,

We have a requirement to put number (as a text) with leading zeros. I am using .PutValue method, but somehow it is removing the leading zeros.

Any solution around this. Appreciate the help.

2

@sachingangarde,

Well, this is MS Excel’s behavior by default. For saving string value of zeros, you can set text format for the cell. See the sample code for your reference:
e.g
Sample code:

Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
Cell cell = worksheet.Cells["A1"];
cell.PutValue("000000");
// Set cell format as text
Style style = cell.GetStyle();
style.SetCustom("@" , false);
cell.SetStyle(style);
workbook.Save("TextFormatting.xlsx");

Hope, this helps a bit.

3

@Amjad_Sahi, thanks for your response, but this is not working. It is still removing the leading zeros.

Is there a way we can specify not to convert or keep as a string while using .putvalue method? I have other formats to put like double, datetime, int and don’t want them converted automatically.

4

@sachingangarde,

I have written a sample code on possible approaches which MS Excel supports to accomplish your task for your reference. I have also added comments so you could understand it precisely. Please note, there may not be any other way around except mentioned ways in MS Excel:
e.g
Sample code:

Workbook workbook = new Workbook();

            Cell cell1 = workbook.Worksheets[0].Cells["A1"];
            cell1.PutValue("'0000");//it will be inserted as string "0000"

            Cell cell2 = workbook.Worksheets[0].Cells["A2"];
            cell2.PutValue("0000");//it will be inserted as string "0000"

            Cell cell3 = workbook.Worksheets[0].Cells["A3"];
            cell3.PutValue(Convert.ToString(0000));//it will be inserted as string "0"

            Cell cell4 = workbook.Worksheets[0].Cells["A4"];
            cell4.PutValue(0000);//it will be inserted as number "0"

            Cell cell5 = workbook.Worksheets[0].Cells["A5"];
            cell5.PutValue(0000);//it will be inserted as number "0000"
            // Set cell format as text
            Style style1 = cell5.GetStyle();
            style1.Custom = "0000";
            cell5.SetStyle(style1);

            Cell cell6 = workbook.Worksheets[0].Cells["A6"];
            cell6.PutValue("0000"); //it will be inserted as string "0000"
            // Set cell format as text
            Style style = cell6.GetStyle();
            style.SetCustom("@", false);
            cell6.SetStyle(style);


            workbook.Save("e:\\test2\\out1.xlsx");

If you still could not implement your task or could not find your desired way by referring to above code segment, kindly provide a sample file with your desired data intact (according to your way). You may create the file in MS Excel manually and zip it to provide us here, we will check and help you on how to do it via Aspose.Cells APIs.

5

@Amjad_Sahi: Thanks for the your response. Basically, the second parameter in .putvalue() was causing the issue. After removing that, it started working fine.

6

@sachingangarde,

Good to know that your issue is sorted out now. And, yes, the second Boolean parameter will convert to relevant data type. In the event of further queries or issue, feel free to write us back, we will be happy to assist you soon.