Percentage Cell Format

Aspose.Cells Product Family
1

Howdy,

I am trying to export a percentage figure (e.g 12%) and insert it into an Excel table cell. The trouble is that isn't a percentage cell format (or one that I can find). The only suggestion I find is to use a custom style but the trouble with this is that it still places the green arrow in Excel, asking to convert it into a number. Is there a function that I am overlooking?

Cheers,

James

2

Hi James,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for considering Aspose.

You may use Style.Number = 9 for percentage display value. Please see the following sample code for your reference:

//Instantiating a Workbook object

Workbook workbook = new Workbook();

//Obtaining the reference of the newly added worksheet by passing its sheet index

Worksheet worksheet = workbook.Worksheets[0];

//Adding a numeric value to "A1" cell

worksheet.Cells["A1"].PutValue(0.12);

//Setting the display format of the value to number 9 to show value as percentage

worksheet.Cells["A1"].Style.Number = 9;

//Saving the Excel file

workbook.Save("D:\\Test_Temp\\book2.xls", FileFormatType.Default);

Please check the following documentation link for further details regarding display formats:

http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/setting-display-formats-of-numbers-dates.html

Thank You & Best Regards,

3

Hi Nausnerwan,

Thank you for pointing to the percentage format, can't believe I actually missed it!

I have an additional issue with the formats though. I have a tables that uses multiple decimal points, at least up to 9 points. While the format styles only apply to 0 and 2 decimal points, the only method I have seen on your website is to create a custom numeric string where you can specify. The trouble with this method is it doesn't support zeros at the end of decimal places (e.g "13.0" comes out as "13."). I've noticed though in Excel that the actual percentage format has a decimal place option which does support zero decimal places. Is there a way of using this option in Aspose.Cells?

Hope to hear from you soon. Cheers.

James

4

Hi,



Well, you may use Style.Custom attribute to specify your desired formatting for your need.



We appreciate if you could you create a sample file with dummy data in
MS Excel manually and specify your desired custom formatting you
require. Post the file here, we will check and tell you how to apply formatting
using Aspose.Cells APIs.



Thank you.

5

Uploaded a sample file, basically all it is the ability to give percentages and numbers up to nine decimal places which doesn’t use custom numeric formatting strings. The number of decimals is determined by the option in the format cells and it would enable zeros to display if the number requires a certain number of decimal places.

6

Hi,

Well, I am afraid, you have to use Style.Custom attribute to specify your desired formatting strings to cover all your desired percentage or decimal formatting (mentioned in your file).

e.g

//Instantiating a Workbook object
Workbook workbook = new Workbook();
//Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[0];
//Adding a numeric value to “A5” cell
worksheet.Cells[“A5”].PutValue(0.1201234);
worksheet.Cells[“A1”].Style.Custom = “0.00000%”;
//Saving the Excel file
workbook.Save(“e:\test\outBook.xls”, FileFormatType.Default);


Thank you.

7

hi,

We've following requirement. We have a field with format "$XX.XXXX". We need how to export the value in that format . Also when exported, the data type should be matched with one of the below excel formats.

Accounting, Decimal or Number and not "Text". I've tried setting the style as you suggested in this post like cells[0, col].GetStyle().Custom = "0.0000%"; We have below problems with this approach.

The data will be exported as "Text". And if there are 0s at the end, the values are getting removed.

Please let us know how Aspose APIs support this.

Thanks

Prasad

8

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Please download and use the latest version:
Aspose.Cells
for .NET v7.1.1.5

Please see the following code. I have also attached the output file generated by the code and the screenshot for a reference.

C#


//Create a workbook

Workbook workbook = new Workbook();


Cell a1 = workbook.Worksheets[0].Cells[“A1”];


a1.PutValue(34);


Style st = a1.GetStyle();

st.Custom = “"$"#,##0.0000”;

a1.SetStyle(st);


workbook.Save(@“F:\Shak-Data-RW\Downloads\output.xlsx”);


Screenshot: