OpenXML SDK - Excel Change Cell Format - C#

Question

I have a web app in C#. And am using an xlsx template to create documents. The formatting of xlsx is fairly involved and hence I chose the template route instead of creating from scratch. In a cell x2, most data coming in as a percent, but in some cases it would be an amount. But I cannot seem to change the format of that cell when it is amount to percent. The template was saved with percent format in that cell. The code to set the cell value is as below and works for other cells, which were saved with currency format. I tried to set the cell style index to 103U based on that code in another cell with the same format.

How do I change the format of the cell x2? ANy help would be greatly appreciated. Thanks in advance.`

public void setCellValueNum(WorksheetPart ws, int row, int col, Double newVal)
    {
        Cell cl = getCell(ws.Worksheet, getColLetter(col), row);
        cl.CellValue = new CellValue(newVal.ToString());
        cl.CellReference = getColLetter(col) + row.ToString();
        cl.DataType =
            new EnumValue<CellValues>(CellValues.Number);

        //cl.StyleIndex = (UInt32Value)103U;

       }

Xavier · Answer 1 · 2018-03-23T16:57:21.557

3

See numbering format its an style. Add style to Excel in openxml.

According to Reading dates from OpenXml Excel files, format with ID value less than 164 are built in. You can find a list of formats there.

Here is an example: Applying % number format to a cell value using OpenXMl . Just change the format to something like

nf2decimal.FormatCode = StringValue.FromString("0.0");

You need to add a style that references the cell. Here is an example: https://blogs.msdn.microsoft.com/chrisquon/2009/11/30/stylizing-your-excel-worksheets-with-open-xml-2-0/

The openXML installs a tool to generate c# code to create a selected xlsx (https://tech.trailmax.info/2014/04/open-xml-sdk-tool-to-analyse-documents-and-generated-c-code/).

edited Mar 23 '18 at 16:57

answered Mar 22 '18 at 22:28

Xavier

106
4

Thanks Xavier, but this does not tell me the code to change from percent to currency. By using a template, I took a short-cut and did not create the whole style logic. Maybe a bad idea in hindsight, but did not have time to figure out the details. I have the productivity tool, but it only shows me an unsigned int as a code and normally I have been using that to make changes where necessary. Maybe because I am trying to change the number format and not the style!!! – user3150378 Mar 23 '18 at 14:09

score 1 · Accepted Answer · answered Mar 26 '18 at 16:51

1

I used the following line of code to change the format from percent to currency. THis works since the basic number formats are already added to the file by default.

cell.StyleIndex = 103U

answered Mar 26 '18 at 16:51

user3150378

335
1
5
14

OpenXML SDK - Excel Change Cell Format - C#

2 Answers2