Setting value into a cell with percentage format

I need to set a value to a cell that has the percentage format.

I am using the following API:
xlsFile.ActiveSheet = ...;
int XF = xlsFile.GetCellFormat(...);
xlsFile.SetCellValue(..., value, XF);

The problem is that the value is not used as a percentage but as a normal value.

How can I fix this?

What I mean is that when I set value = 80, it's used in the Flexcel calculation engine as 8000.

Hi,

When you set a value of 80, it is 8000%, as you would expect, but not 8000.  80% is 0.8
This is the way Excel works too: If you write 80 in a cell, then change the format to percent, you will see it changes into 8000%:



Now, what you might be thinking of is when you write "80%" in Excel: In this case, Excel will enter 0.8 into the cell, format is as "%" and so it will display 80%.

But SetCellValue is a little more "dumber" than this: It will enter whatever you tell it to enter, and it won't look at the format. if you enter 80 then it will enter 80 into the cell no matter if the cell has a percent format, a date format or whatever. 

If you want a behavior similar to Excel, you can use "SetCellFromString". In the following code, both A1 and A2 will be set to 0.8 and formatted as %:

e[CODE]

using FlexCel.Core;

using FlexCel.XlsAdapter;

using System;


namespace TestPercent

{

    class Program

    {

        static void Main(string[] args)

        {

            XlsFile xlsFile = new XlsFile(1, true);

            int XF = xlsFile.GetCellFormat(1, 1);

            TFlxFormat fmt = xlsFile.GetFormat(XF);

            fmt.Format = "0%";

            XF = xlsFile.AddFormat(fmt);


            xlsFile.SetCellValue(1, 1, 0.8, XF);


            xlsFile.SetCellFromString(2, 1, "80%");


            xlsFile.Save(@"r:\test.xlsx");

        }

    }

}


[/CODE]

But I personally prefer to use SetCellValue when possible. It makes no sense to convert the number to a string so you can use SetCellFromString, and also SetCellFromString is doing "guess" work, trying to guess what you really meant and entering that into the cell. As said SetCellValue is dumber, and it does exactly what you tell it to do. It is a little more work to know what to tell it to do, but you can be sure it won't guess the wrong thing.

Thanks for the great answer!


My case is slighter different since I already have the Excel cell formatted as a percentage. So, as a API consumer I'd expect SetCellValue to be a little smarter (especially if I set the XF using to the GetCellFormat - which for percentage returned 668) and behave as Excel - i.e. in Excel when entering a value in a cell that already has the percentage format, the value is used in the context of the format, as percentage: 80 -> 80%. I do realise that technically/mathematically 80% is in fact 0.8 but in the context of a cell with percentage format 80 is actually 80%.

I think this can be a nice feature for later releases of the Flexcel. :)

Thanks again!