Apply text style and update contents of cell without iterating rows and cells in .NET

Aspose.Cells Product Family
1

Hi Dear,

I am using Aspose.Cells version 8.6.1.6.

I am able to apply custom style as text in one of our excel column. The problem is that the content inside the cell is long number (15 digit e.g 784198757391852) which is not converter to text. It still show it like “7.842E+14”. How can i convert it to normal string without iterating the columns and rows in the excel?

Below is the code:

        Workbook wb = new Workbook("D:\\Excel\\abc.xls");
        wb.Settings.CalcMode = CalcModeType.Manual;
        Worksheet sheet = wb.Worksheets[0];
        var columns = sheet.Cells.Columns;
        var nationalIDColumn = columns[CellsHelper.ColumnNameToIndex("C")];
        var tstyle = wb.CreateStyle();
        tstyle.Custom = "@";
        nationalIDColumn.ApplyStyle(tstyle, new StyleFlag() { NumberFormat = true });
        wb.Save("D:\\Excel\\output.xlsx");

Kind Regards
Naveed Anjum

2

@naveedanjum88,

Thanks for your query.

This is the behavior of MS Excel by default (it converts long numbers into scientific notations), you may test your scenario/ case by entering the 15 digits number directly into MS Excel manually.

For your requirements, you may simply change the line of code:
i.e.,

tstyle.Custom = “@”;

to:

tstyle.Custom = “0”;

it will work for your needs.

I hope, this helps a bit.

3

Dear,

Thanks for quick response.

i tried this before and it convert the format to number. The problem is that we have one legacy system that accepts only the column to be formatted as text. Is there any workaround for this?

Thanks

4

@naveedanjum88,
I have tried to perform this task using Excel and found only two options to perform this task. First one was to convert the cell’s content to text using =TEXT() function. Second option was to place (') single quotation mark before such large numbers. No other option is found to display large numbers in text column without the scientific notation except the two mentioned above. You may please try to achieve this using Excel by any other means as per your requirement and share the steps with us. We will try to provide you assistance to perform same task using Aspose.Cells.

You may also have look at the following link.