1

I have c# code that programmatically inserts cells in spreadsheet. I am not able to set percetnage format for those cells:

        Cell cell = OpenXmlHelper.GetCell(ws, address);            
        if (cell == null)
        {
            cell = InsertCellInWorksheet(ws, rowIndex, column);
        }

        cell.DataType = CellValues.Number; 


        cell.CellValue = new CellValue(value.ToString());

Instead of cell.DataType = CellValues.Number; I need some code that sets Percentage. Can you give me some clues or tutorial on formatting percentage for excel cells in openxml?

Compared to Open Xml Excel set cell format to be Percentage I have number 0.08 and I need to show it as 8%.

Community
  • 1
  • 1
renathy
  • 5,125
  • 20
  • 85
  • 149

1 Answers1

-2

Try cell.StyleIndex = (UInt32Value)1U;

Edit in response to renathy's comment

This code was adapted from the code provided by the Open XML SDK 2.0 Productivity Tool. When viewing a percentage formatted cell in this tool the code it generates is:

// Creates an Cell instance and adds its children.
public Cell GenerateCell()
{
    Cell cell1 = new Cell(){ CellReference = "A1", StyleIndex = (UInt32Value)1U };
    CellValue cellValue1 = new CellValue();
    cellValue1.Text = "0.2";

    cell1.Append(cellValue1);
    return cell1;
}

As to what the 1U is, it appears to be the magic number that Microsoft decided to use to represent styles (the U part just means that the number is unsigned).

Aquila Sands
  • 1,471
  • 1
  • 20
  • 28
  • This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. – Rahul Sharma Apr 17 '14 at 11:12
  • @RahulSharma setting that property should format the cell as a percentage how is that not an answer? – Aquila Sands Apr 17 '14 at 12:21
  • You could add the above point as a comment as it simply add one liner reply. My intentions with the comment were not to disapprove your reply as an answer rather to add it as a comment to original question. – Rahul Sharma Apr 17 '14 at 12:35
  • @RahulSharma I have to disagree, it is an answer even though it is a one liner and it should be posted as such so that the question can be marked as answered if it solves the problem. – Aquila Sands Apr 17 '14 at 12:48
  • 1
    This doesn't work. It doesn't change format of the cell. And even if it would be the correct answer, it would be good to see some comments - what is 1U for example. – renathy May 07 '14 at 13:49