Cannot apply styleindex in Excel using OpenXml SDK

Question

Hi ,
I'm using a template xlsx file which contains some columns with Formats.
When I populate my new Excel file I can replace the values but I cannot accomplish the format of the cells
The workflow is as follows
a. I'm taking the excel template which contains the preformatted columns. I have for example a column with number format , a column with a datetime format etc.
b. I'm creating the new xlsx file according to my original template and then with code I'm replacing the values to each column. The values are replaces CORRECTLY so let's not stick to that.

The problem starts that If I have a value , a number let's say 34567 and in my excel I want to show it as Currency , in the generated excel I can see the number without the format.

Although I have applied the styleindex property.

Below is some sample code from the last actions that my app is doing.

The InsertCellInWorkSheet contains the styleindex which originally came from the template

private static Cell InsertCellInWorksheet(Celll celll, WorksheetPart worksheetPart, SingleValues value)
{
var worksheet = worksheetPart.Worksheet;
var sheetData = worksheet.GetFirstChild<SheetData>();

         var rowIndex = Convert.ToUInt32(RowReg.Match(celll.CellReference).Value);
         // If the worksheet does not contain a row with the specified row index, insert one.

         //this will be current row, next row or null
         var nextOrCurrentRow = sheetData
             .Elements<Row>() //take all rows
             .Where(r => r.RowIndex.Value >= rowIndex) //where index equal or greater of current
             .OrderBy(x => x.RowIndex.Value) //order by index
             .FirstOrDefault();

         Row row = null;
         //if it current row use it
         if (nextOrCurrentRow != null && nextOrCurrentRow.RowIndex.Value == rowIndex)
             row = nextOrCurrentRow;

         if (row == null)
         {
             row = new Row { RowIndex = new UInt32Value(rowIndex) };
             //if not null then this is next row
             if (nextOrCurrentRow != null)
                 sheetData.InsertBefore(row, nextOrCurrentRow);
             else
                 sheetData.Append(row);
         }

         // If there is not a cell with the specified column name, insert one.
         if (row.Elements<Cell>().Any(c => c.CellReference.Value == celll.CellReference))
         {
             return row.Elements<Cell>().First(c => c.CellReference.Value == celll.CellReference);

         }

         // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
         var newid = Common.Ab4ToInt(celll.CellReference.ToUpper());

         var refCell = row.Elements<Cell>()
             .FirstOrDefault(
                 cell => Common.Ab4ToInt(cell.CellReference.Value.ToUpper()) > newid);

         var newCell = new Cell { CellReference = celll.CellReference, StyleIndex = celll.StyleIndex  };

         row.InsertBefore(newCell, refCell);

         return newCell;

}

Everything is SharedString

private static void SetCellValue(OpenXmlPartContainer wbPart, WorksheetPart worksheetPart, Celll celll, SingleValues value)
{
var sheetCell = InsertCellInWorksheet(celll, worksheetPart,value);

        //return;
         var cellText = GetCellValue(sheetCell, wbPart);

         if (sheetCell.DataType == null)
         {
             //new cell?
             sheetCell.DataType = new EnumValue<CellValues>(CellValues.String);
             cellText = celll.Template;
         }
         var safeText = value.FirstOrDefault(x => x.Replaceable == celll.Label)?.Value?.Trim();
         cellText = ResultToValue(safeText);


         switch (sheetCell.DataType.Value)
         {
             case CellValues.SharedString:
                 var sharedStringId = InsertSharedStringItem(cellText, wbPart , sheetCell).ToString(CultureInfo.InvariantCulture);
                 sheetCell.CellValue = new CellValue(sharedStringId);
                 break;
             default:
                 var textNode = new Text(cellText);
                 sheetCell.CellValue = new CellValue(textNode.Text);
                 break;
         }

     }

private static int InsertSharedStringItem(string text, OpenXmlPartContainer wbPart , Cell cell=null)
{
var shareStringPart = GetOrCreateSharedStringTablePart(wbPart);
var i = 0;

         // Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
         foreach (var item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
         {
             if (item.InnerText == text)
                 return i;
             i++;
         }
         // The text does not exist in the part. Create the SharedStringItem and return its index.

         shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text(text)));
         shareStringPart.SharedStringTable.Save();

         return i;
     }

Can anyone assist me on this ?
I want to insert the data which is in string format and let Excel handle the formating which is already applied.
Appreciate any help you could give me

Answer 1

Thank you Tom