does openXML(C#) support generating number fields that are formatted (like 1,000,000)

Question

i have existing code that generates a spreadsheet from excel using OpenXML. It works fine but i always have to go into the spreadsheet afterwards and add formatting.

Is there anyway in my C# code I can specify that i want a column formatted with a certain number formatting) commas, decimals, etc?

any links to examples would be great.

here is how i am saving data now:

 public void SetCell<T>(string column, uint rowIndex, T value)
    {
        SheetData sheetData = _positionSheet;
        Cell cell = GetCell(sheetData, column, rowIndex);
        string stringValue = value == null ? "" : value.ToString();
        cell.CellValue = new CellValue(stringValue);
        switch (typeof(T).Name)
        {
            case "Date":
            case "DateTime": 
                cell.DataType = CellValues.Date;
                break;
            case "Int32":
                cell.DataType = CellValues.Number;
                break;
            default:
                cell.DataType = CellValues.String;
                break;
        }
    }

below is the GetCell() method (even though i guess thats not really relevant for the question:

 static private Cell GetCell(SheetData sheet, string column, uint rowIndex)
    {
        Cell cell;
        Row row = GetRow(sheet, rowIndex);
        if (row.Elements<Cell>().Where(c => c.CellReference.Value == column + rowIndex).Any())
        {
            cell = row.Elements<Cell>().Where(c => c.CellReference.Value == column + rowIndex).First();
        }
        else
        {
            Cell refCell = null;
            var comparer = new CellComparer();
            foreach (Cell existingCell in row.Elements<Cell>())
            {
                if (comparer.Compare(existingCell.CellReference.Value, column + rowIndex) > 0)
                {
                    refCell = existingCell;
                    break;
                }
            }

            cell = new Cell { CellReference = column + rowIndex };
            row.InsertBefore(cell, refCell);
        }
        return cell;
    }

Answer 1

You can use ExtremeML, it's based on OpenXML. If use it, you can define a template, and generate excel file by template.

book1.xlsx is template, book2.xlsx is finally file. tabb1 is a table in excel 2007.

            FileStream fs = new FileStream ( @"d:\book1.xlsx" , FileMode.Open );
        FileStream msm = new FileStream ( @"d:\book2.xlsx" , FileMode.CreateNew );

        using ( var package = SpreadsheetDocumentWrapper.Open ( fs , msm ) ) {
            var table = package.WorkbookPart.GetTablePart ( "tabb1" ).Table;
            var data = new List<object[]> ( );

            for ( var i = 0 ; i < 10 ; i++ ) {
                data.Add ( new object[] { "1" , "2" , "3" , "4" } );
            }

            table.Fill ( data.ToArray ( ) );
        }

        fs.Close ( );
        msm.Close ( );

Answer 2

Yes it's possible, however it's all buried deep into styles. Consider using ClosedXML it have object model similar to old good Excel Object Model and uses OpenXML under the hood. Everything is much simplier with it.