OpenXML - change Excel cell format (Date and Number) when exporting from Datagridview

Question

I using OpenXML to export Datagridview to Excel. If I export cells with CellValues.String evertyhing works fine without any errors in Excel file, but what I need is to properly convert all Date and Number data into corresponding cell format. I've tried to use built-in formats (not custom ones) to change format of cells, but then my Excel got corrupted.

Here is what I tried so far:

  public void Export_to_Excel(DataGridView dgv, string path)
    {
        using (var workbook = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook))
        {
            var workbookPart = workbook.AddWorkbookPart();

            workbook.WorkbookPart.Workbook = new Workbook();
            workbook.WorkbookPart.Workbook.Sheets = new Sheets();

            var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
            var sheetData = new SheetData();
            sheetPart.Worksheet = new Worksheet(sheetData);

            Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<Sheets>();
            string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);

            uint sheetId = 1;
            if (sheets.Elements<Sheet>().Count() > 0)
            {
                sheetId =
                    sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
            }

            Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = "List "+ sheetId};
            sheets.Append(sheet);

            Row headerRow = new Row();

            // Construct column names 
            List<String> columns = new List<string>();
            foreach (DataGridViewColumn column in dgv.Columns)
            {
                columns.Add(column.Name);

                Cell cell = new Cell
                {
                    DataType = CellValues.String,
                    CellValue = new CellValue(column.HeaderText)
                };
                headerRow.AppendChild(cell);
            }

            // Add the row values to the excel sheet 
            sheetData.AppendChild(headerRow);

            foreach (DataGridViewRow dsrow in dgv.Rows)
            {
                Row newRow = new Row();
                foreach (String col in columns)
                {

                    CellValues cell_type = new CellValues();
                    string cell_value = "";
                    UInt32 style_index;
                    if (dsrow.Cells[col].ValueType == typeof(decimal)) //numbers
                    {
                        cell_type = CellValues.Number;
                        cell_value = ((decimal)dsrow.Cells[col].Value).ToString();
                        style_index = 4; //should be #,##0.00
                    }
                    else if (dsrow.Cells[col].ValueType == typeof(DateTime)) //dates
                    {
                        cell_type = CellValues.String;
                        cell_value = ((DateTime)dsrow.Cells[col].Value).ToString("dd.mm.yyyy");
                        style_index =0; //should be General
                    }
                    else
                    {
                        cell_type = CellValues.String;
                        cell_value = dsrow.Cells[col].Value.ToString();
                        index_stila = 0; //should be General
                    }

                    Cell cell = new Cell();
                    cell.DataType = new EnumValue<CellValues>(cell_type);
                    cell.CellValue = new CellValue(cell_value);
                    cell.StyleIndex = style_index;
                    newRow.AppendChild(cell);
                }

                sheetData.AppendChild(newRow);
            }
        }
    }

So basically, what I would like is to have this cells formatted correctly. In above code I tried only for Number format, but I need same for Date format too. Here is also a link to built-in styles for OpenXML.

Integrand

A similar question was asked here - #6033876. The OpenXML implementation looks incredibly messy as usual. Like the others in that thread I strongly suggest using ClosedXML instead. – Dewittdewlap

@VBDidNothingWrong, I heard about ClosedXML and will give It a try too, although a lot of programmers told that It's eating memory and has issues with large data, and that is exactly what I need, we are talking about 500k-1 million rows. – Integrand

I see, I'll post an OpenXML answer now. Note, I tried a couple of the answers in the linked thread there, but none of them worked. – Dewittdewlap

@VBDidNothingWrong, looking forward to see your solution. I almost solved problem myself, but I can't get Number format working correctly (Date format solved). I will post my solution soon too, a complete code for further annoying questions regarding OpenXML. It's really a messy solution, but I've tried ClosedXML too and got a lot of bumps there also. – Integrand

Answer 1

I solved above problem. I must say that working with OpenXML is a bit frustrating but I'm happy with end results.

I decided – based on many OpenXML topics - to extend answer with providing a full useable code, not just examples as I ussually encountered on many sites.

My basic requirement was to export Datagridview data into Excel file, with correct cell formatting and faster export speed than current Interop solution we use. Code below can be used with Datatable or Dataset also, with just a slight modification. I've also added some other functionalities which in my opinion should be documented as that Is what most programmers need in Excel, but unfortunally they're not.

I won't go in depths for everything since I allready had some headaches doing all of that, so let's cut to the chase. Result of complete code below is Excel file with exported data from Datagridview and :

• column names same as Datagridview headers & in bold font;
• changed default font »Calibri« to »Arial«;
• cell formating based on actual data from Datatable(dates,numbers & string) with desired format;
• Save File dialog prompt;
• autofit for columns;

As many others stated, order in OpenXML is very important. That applies for pretty much everything – when you create document or style It. So everything you see here works fine for me in Office 2016, but If you do some line mixing you end up very fast with some kind of weird errors in Excel… As promised, here is my full code:

public void Export_to_Excel(DataGridView dgv, string file_name)
{
  String file_path= Environment.GetFolderPath(Environment.SpecialFolder.Desktop).ToString() + "\\" +file_name + ".xlsx";

  SaveFileDialog saveFileDialog = new SaveFileDialog();
  saveFileDialog.InitialDirectory = Convert.ToString(Environment.SpecialFolder.Desktop);
  saveFileDialog.Filter = "Excel Workbook |*.xlsx";
  saveFileDialog.Title = "Save as";
  saveFileDialog.FileName = file_name;
  if (saveFileDialog.ShowDialog() == DialogResult.OK)
  {
    file_path = saveFileDialog.FileName;                  
  }
  else
  {
    return;
  }

 using (var workbook = SpreadsheetDocument.Create(file_path, SpreadsheetDocumentType.Workbook))
 {
    var workbookPart = workbook.AddWorkbookPart();
    workbook.WorkbookPart.Workbook = new Workbook();
    workbook.WorkbookPart.Workbook.Sheets = new Sheets();

    var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
    var sheetData = new SheetData();

     //Autofit comes first – we calculate width of columns based on data
     sheetPart.Worksheet = new Worksheet();
     sheetPart.Worksheet.Append(AutoFit_Columns(dgv));
     sheetPart.Worksheet.Append(sheetData);

     //Adding styles to worksheet
     Worksheet_Style(workbook);

     Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<Sheets>();
     string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);

     uint sheetId = 1;
     if (sheets.Elements<Sheet>().Count() > 0)
     {
       sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
     }

     Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = "List " + sheetId };
      sheets.Append(sheet);

      Row headerRow = new Row(); //Adding column headers

      for (int col = 0; col < dgv.ColumnCount; col++)
      {
         Cell cell = new Cell
         {
             DataType = CellValues.String,
             CellValue = new CellValue(dgv.Columns[col].HeaderText),
             StyleIndex = 1// bold font
         };
         headerRow.AppendChild(cell);
       }

       // Add the row values to the excel sheet 
       sheetData.AppendChild(headerRow);

       for (int row = 0; row < dgv.RowCount; row++)
       {
          Row newRow = new Row();

          for (int col = 0; col < dgv.ColumnCount; col++)
          {
              Cell cell = new Cell();

              //Checking types of data
              // I had problems here with Number format, I just can't set It to a
              // Datatype=CellValues.Number. If someone knows answer please let me know. However, Date format strangely works fine with Number datatype ?
              // Also important – whatever format you define in creating stylesheets, you have to insert value of same kind in string here – for CellValues !
              // I used cell formating as I needed, for something else just change Worksheet_Style method to your needs
              if (dgv.Columns[col].ValueType == typeof(decimal)) //numbers
              {
                 cell.DataType = new EnumValue<CellValues>(CellValues.String);
                 cell.CellValue = new CellValue(((decimal)dgv.Rows[row].Cells[col].Value).ToString("#,##0.00"));
                  cell.StyleIndex = 3;
               }
               else if (dgv.Columns[col].ValueType == typeof(DateTime)) //dates
               {
                  cell.DataType = new EnumValue<CellValues>(CellValues.Number);
                  cell.CellValue = new CellValue(((DateTime)dgv.Rows[row].Cells[col].Value).ToOADate().ToString(CultureInfo.InvariantCulture));
                  cell.StyleIndex = 2;
                }
                Else // strings
                {
                  cell.DataType = new EnumValue<CellValues>(CellValues.String);
                  cell.CellValue = new CellValue(dgv.Rows[row].Cells[col].Value.ToString());
                  cell.StyleIndex = 0;
          }
                 newRow.AppendChild(cell);
                }
                    sheetData.AppendChild(newRow);
                }
            }

 }

        private static WorkbookStylesPart Worksheet_Style (SpreadsheetDocument document)
        {
            WorkbookStylesPart create_style = document.WorkbookPart.AddNewPart<WorkbookStylesPart>();
            Stylesheet workbookstylesheet = new Stylesheet();

            DocumentFormat.OpenXml.Spreadsheet.Font font0 = new DocumentFormat.OpenXml.Spreadsheet.Font(); // Default font
            FontName arial = new FontName() { Val = "Arial" };
            FontSize size = new FontSize() { Val = 10 };
            font0.Append(arial);
            font0.Append(size);


            DocumentFormat.OpenXml.Spreadsheet.Font font1 = new DocumentFormat.OpenXml.Spreadsheet.Font(); // Bold font
            Bold bold = new Bold();
            font1.Append(bold);

            // Append both fonts
            Fonts fonts = new Fonts();     
            fonts.Append(font0);
            fonts.Append(font1);

            //Append fills - a must, in my case just default
            Fill fill0 = new Fill();        
            Fills fills = new Fills();      
            fills.Append(fill0);

            // Append borders - a must, in my case just default
            Border border0 = new Border();     // Default border
            Borders borders = new Borders();    
            borders.Append(border0);

            // CellFormats
            CellFormats cellformats = new CellFormats();

            CellFormat cellformat0 = new CellFormat() { FontId = 0, FillId = 0, BorderId = 0 }; // Default style : Mandatory | Style ID =0
            CellFormat bolded_format = new CellFormat() { FontId = 1 };  // Style with Bold text ; Style ID = 1
            CellFormat date_format = new CellFormat() { BorderId = 0, FillId = 0, FontId = 0, NumberFormatId = 14, FormatId = 0, ApplyNumberFormat = true };
            CellFormat number_format = new CellFormat() { BorderId = 0, FillId = 0, FontId = 0, NumberFormatId = 4, FormatId = 0, ApplyNumberFormat = true }; // format like "#,##0.00"

            cellformats.Append(cellformat0);
            cellformats.Append(bolded_format);
            cellformats.Append(date_format);
            cellformats.Append(number_format);

            // Append everyting to stylesheet  - Preserve the ORDER !
            workbookstylesheet.Append(fonts);
            workbookstylesheet.Append(fills);
            workbookstylesheet.Append(borders);
            workbookstylesheet.Append(cellformats);

            //Save style for finish
            create_style.Stylesheet = workbookstylesheet;
            create_style.Stylesheet.Save();

            return create_style;
        }


        private Columns AutoFit_Columns(DataGridView dgv)
        {
            Columns cols = new Columns();
            int Excel_column=0;

            DataTable dt = new DataTable();
            dt = (DataTable)dgv.DataSource;

            for (int col = 0; col < dgv.ColumnCount; col++)
            {
                double max_width = 14.5f; // something like default Excel width, I'm not sure about this

                //We search for longest string in each column and convert that into double to get desired width 
                string longest_string = dt.AsEnumerable()
                     .Select(row => row[col].ToString())
                     .OrderByDescending(st => st.Length).FirstOrDefault();

                double cell_width = GetWidth(new System.Drawing.Font("Arial", 10), longest_string);

                if (cell_width > max_width)
                {
                    max_width = cell_width;
                }

                if (col == 0) //first column of Datagridview is index 0, but there is no 0 index of column in Excel, careful with that !!!
                {
                    Excel_column = 1;
                }

                //now append column to worksheet, calculations done
                Column c = new Column() { Min = Convert.ToUInt32(Excel_column), Max = Convert.ToUInt32(Excel_column), Width = max_width, CustomWidth = true };
                cols.Append(c);

                Excel_column++;
            }
            return cols;
        }

        private static double GetWidth(System.Drawing.Font stringFont, string text)
        {
            // This formula calculates width. For better desired outputs try to change 0.5M to something else

            Size textSize = TextRenderer.MeasureText(text, stringFont);
            double width = (double)(((textSize.Width / (double)7) * 256) - (128 / 7)) / 256;
            width = (double)decimal.Round((decimal)width + 0.5M, 2);

            return width;
        }

Method, in my case from a .dll can be called easily like:

Export_to_Excel(my_dgv, »test_file«)

Short explanation of some stuff in code:

1.) Styles: there are many options of how I could do It, but that was the easiest way for me. When you will need something harder, try not to forget that order counts here too. And appending Fonts,Fills and Borders is neccesary.

2.) Autofit: I can't believe why that isn't documented allready, and my opinion is that OpenXML should have some method for that by default. Anyway, I solved that by using LINQ and with a help from here. I hope author doesn't mind, but someone should say that out loud :)

And now, for the end, my test results & advantages/disadvantages comparing with Interop. I tested on Excel 2016 with 200k rows of data:

Interop

• Exported data in almost 3 minutes;

Advantages:

• easier coding (in my opinion) with lots of built-in features, such as (ofcourse) Autofit;
• you can actually create Excel file (object) that isn't saved to disk allready;

Disadvantages:

• slow comparing to any other libraries such as OpenXML, though I could probably cut down 3 minutes to maybe 2;
• I've also noticed huge memory drain on large data, even though I have my Interop code quite optimized;

OpenXML

• Exported data (with autofit feature and all styles) in 20 seconds;

Advantages:

• much faster than Interop, and I think that my »rubbish« code can be more optimized (you can help with that If you care);

Disandvantages:

• coding, not obvious? :)
• memory drain higher than in Interop, though OpenXML offers two approaches a.k.a. SAX or DOM method. SAX is even faster than provided code, with almost no memory drain If you write your data to Excel directly from DataReader, but coding took me a lot of time;

I hope nobody will be mad as what I actually did was to put bits and pieces from many sites into something that is actually useful, instead of writing complicated examples that nobody understands. And If anybody cares to improve anything above I would appreciate that a lot. I'm not perfect and more heads together ussually forms a better solution for everybody in the end :)

Integrand

Best example yet, with some minor tune up it work great – Tranche

Answer 2

There seems to be a lot of answers to this type of question that result in an excel that asks to be repaired. I'd normally recommend people use ClosedXML, but if OpenXML is a must then the answer given here: https://mcmap.net/q/373306/-openxml-writing-a-date-into-excel-spreadsheet-results-in-unreadable-content does work.

Here's that answer with some extra lines for Date including time cells, number cells, and string cells.

    private static void TestExcel()
    {
        using (var Spreadsheet = SpreadsheetDocument.Create("C:\\Example.xlsx", SpreadsheetDocumentType.Workbook))
        {
            // Create workbook.
            var WorkbookPart = Spreadsheet.AddWorkbookPart();
            var Workbook = WorkbookPart.Workbook = new Workbook();

            // Add Stylesheet.
            var WorkbookStylesPart = WorkbookPart.AddNewPart<WorkbookStylesPart>();
            WorkbookStylesPart.Stylesheet = GetStylesheet();
            WorkbookStylesPart.Stylesheet.Save();

            // Create worksheet.
            var WorksheetPart = Spreadsheet.WorkbookPart.AddNewPart<WorksheetPart>();
            var Worksheet = WorksheetPart.Worksheet = new Worksheet();

            // Add data to worksheet.
            var SheetData = Worksheet.AppendChild(new SheetData());
            SheetData.AppendChild(new Row(
                //Date example. Will show as dd/MM/yyyy. 
                new Cell() { CellValue = new CellValue(DateTime.Today.ToOADate().ToString(CultureInfo.InvariantCulture)), StyleIndex = 1 },

                //Date Time example. Will show as dd/MM/yyyy HH:mm
                new Cell() { CellValue = new CellValue(DateTime.Now.ToOADate().ToString(CultureInfo.InvariantCulture)), StyleIndex = 2 },

                //Number example
                new Cell() { CellValue = new CellValue(123.23d.ToString(CultureInfo.InvariantCulture)), StyleIndex = 0 },

                //String example
                new Cell() { CellValue = new CellValue("Test string"), DataType = CellValues.String }

            ));

            // Link worksheet to workbook.
            var Sheets = Workbook.AppendChild(new Sheets());
            Sheets.AppendChild(new Sheet()
            {
                Id = WorkbookPart.GetIdOfPart(WorksheetPart),
                SheetId = (uint)(Sheets.Count() + 1),
                Name = "Example"
            });

            Workbook.Save();
        }
    }

    private static Stylesheet GetStylesheet()
    {
        var StyleSheet = new Stylesheet();

        // Create "fonts" node.
        var Fonts = new Fonts();
        Fonts.Append(new Font()
        {
            FontName = new FontName() { Val = "Calibri" },
            FontSize = new FontSize() { Val = 11 },
            FontFamilyNumbering = new FontFamilyNumbering() { Val = 2 },
        });

        Fonts.Count = (uint)Fonts.ChildElements.Count;

        // Create "fills" node.
        var Fills = new Fills();
        Fills.Append(new Fill()
        {
            PatternFill = new PatternFill() { PatternType = PatternValues.None }
        });
        Fills.Append(new Fill()
        {
            PatternFill = new PatternFill() { PatternType = PatternValues.Gray125 }
        });

        Fills.Count = (uint)Fills.ChildElements.Count;

        // Create "borders" node.
        var Borders = new Borders();
        Borders.Append(new Border()
        {
            LeftBorder = new LeftBorder(),
            RightBorder = new RightBorder(),
            TopBorder = new TopBorder(),
            BottomBorder = new BottomBorder(),
            DiagonalBorder = new DiagonalBorder()
        });

        Borders.Count = (uint)Borders.ChildElements.Count;

        // Create "cellStyleXfs" node.
        var CellStyleFormats = new CellStyleFormats();
        CellStyleFormats.Append(new CellFormat()
        {
            NumberFormatId = 0,
            FontId = 0,
            FillId = 0,
            BorderId = 0
        });

        CellStyleFormats.Count = (uint)CellStyleFormats.ChildElements.Count;

        // Create "cellXfs" node.
        var CellFormats = new CellFormats();

        // StyleIndex = 0, A default style that works for most things (But not strings? )
        CellFormats.Append(new CellFormat()
        {
            BorderId = 0,
            FillId = 0,
            FontId = 0,
            NumberFormatId = 0,
            FormatId = 0,
            ApplyNumberFormat = true
        });

        // StyleIndex = 1, A style that works for DateTime (just the date)
        CellFormats.Append(new CellFormat()
        {
            BorderId = 0,
            FillId = 0,
            FontId = 0,
            NumberFormatId = 14, //Date
            FormatId = 0,
            ApplyNumberFormat = true
        });

        // StyleIndex = 2, A style that works for DateTime (Date and Time)
        CellFormats.Append(new CellFormat()
        {
            BorderId = 0,
            FillId = 0,
            FontId = 0,
            NumberFormatId = 22, //Date Time
            FormatId = 0,
            ApplyNumberFormat = true
        });

        CellFormats.Count = (uint)CellFormats.ChildElements.Count;

        // Create "cellStyles" node.
        var CellStyles = new CellStyles();
        CellStyles.Append(new CellStyle()
        {
            Name = "Normal",
            FormatId = 0,
            BuiltinId = 0
        });
        CellStyles.Count = (uint)CellStyles.ChildElements.Count;

        // Append all nodes in order.
        StyleSheet.Append(Fonts);
        StyleSheet.Append(Fills);
        StyleSheet.Append(Borders);
        StyleSheet.Append(CellStyleFormats);
        StyleSheet.Append(CellFormats);
        StyleSheet.Append(CellStyles);

        return StyleSheet;
    }

Dewittdewlap

interesting, that is same example that I was working on and solved Date format correctly. However, notice about Number format, It doesn't except "CellValues.Number" datatype. – Integrand

The number example worked for me, though strings didn't, and I added a line in for that. Tested with Excel 2013 – Dewittdewlap

It doesn't work with numbers. It does write a value into Excel without any errors, but IF you check in Excel you'll notice that cell is formatted as General and not number. At least that is case in Office 2016. However I solved everything now and will be posting complete all-in-one code with other useful things that I was struggling with over past week. – Integrand

Answer 3

I would like to enhance the great answer of Nancy82. I've change it to convert a DataTable to Excel, and modernize it a little bit.

 public class ExcelConverter
    {
        public static void DataTableToExcel(DataTable dataTable, string outputFilename)
        {

            using (var workbook = SpreadsheetDocument.Create(outputFilename, SpreadsheetDocumentType.Workbook))
            {
                workbook.AddWorkbookPart();
                workbook.WorkbookPart.Workbook = new Workbook();
                workbook.WorkbookPart.Workbook.Sheets = new Sheets();

                var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
                var sheetData = new SheetData();

                //Autofit comes first – we calculate width of columns based on data
                sheetPart.Worksheet = new Worksheet();
                sheetPart.Worksheet.Append(AutoFit_Columns(dataTable));
                sheetPart.Worksheet.Append(sheetData);

                //Adding styles to worksheet
                Worksheet_Style(workbook);

                var sheets = workbook.WorkbookPart.Workbook.GetFirstChild<Sheets>();
                var relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);

                uint sheetId = 1;
                if (sheets.Elements<Sheet>().Any())
                {
                    sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                }

                var sheet = new Sheet { Id = relationshipId, SheetId = sheetId, Name = "List " + sheetId };
                sheets.Append(sheet);

                var headerRow = new Row(); //Adding column headers

                for (var col = 0; col < dataTable.Columns.Count; col++)
                {
                    var cell = new Cell
                    {
                        DataType = CellValues.String,
                        CellValue = new CellValue(dataTable.Columns[col].Caption),
                        StyleIndex = 1// bold font
                    };
                    headerRow.AppendChild(cell);
                }

                // Add the row values to the excel sheet 
                sheetData.AppendChild(headerRow);

                for (var row = 0; row < dataTable.Rows.Count; row++)
                {
                    var newRow = new Row();

                    for (var col = 0; col < dataTable.Columns.Count; col++)
                    {
                        var cell = new Cell();

                        if (dataTable.Columns[col].DataType == typeof(int))
                        {
                            cell.DataType = new EnumValue<CellValues>(CellValues.Number);
                            cell.CellValue = new CellValue(((int)dataTable.Rows[row][col]));
                            cell.StyleIndex = 4;
                        }
                        else if (dataTable.Columns[col].DataType == typeof(decimal)) //numbers
                        {
                            cell.DataType = new EnumValue<CellValues>(CellValues.Number);
                            cell.CellValue = new CellValue((decimal)dataTable.Rows[row][col]);
                            cell.StyleIndex = 3;
                        }
                        else if (dataTable.Columns[col].DataType == typeof(DateTime)) //dates
                        {
                            cell.DataType = new EnumValue<CellValues>(CellValues.Number);
                            var value = dataTable.Rows[row][col].ToString();

                            if (DateTime.TryParse(value, out var date))
                                cell.CellValue = new CellValue(date.ToOADate().ToString(CultureInfo.InvariantCulture));
                            else
                                cell.CellValue = new CellValue("");

                            cell.StyleIndex = 2;
                        }
                        else // strings
                        {
                            cell.DataType = new EnumValue<CellValues>(CellValues.String);
                            cell.CellValue = new CellValue(dataTable.Rows[row][col].ToString());
                            cell.StyleIndex = 0;
                        }
                        newRow.AppendChild(cell);
                    }
                    sheetData.AppendChild(newRow);
                }
            }

        }

        private static void Worksheet_Style(SpreadsheetDocument document)
        {
            var createStyle = document.WorkbookPart.AddNewPart<WorkbookStylesPart>();
            var workbookStyleSheet = new Stylesheet();

            var font0 = new Font(); // Default font
            var arial = new FontName { Val = "Calibri" };
            var size = new FontSize { Val = 11 };
            font0.Append(arial);
            font0.Append(size);


            var font1 = new Font(); // Bold font
            var bold = new Bold();
            font1.Append(bold);

            // Append both fonts
            var fonts = new Fonts();
            fonts.Append(font0);
            fonts.Append(font1);

            //Append fills - a must, in my case just default
            var fill0 = new Fill();
            var fills = new Fills();
            fills.Append(fill0);

            // Append borders - a must, in my case just default
            var border0 = new Border();     // Default border
            var borders = new Borders();
            borders.Append(border0);

            // CellFormats
            var cellFormats = new CellFormats();

            var cellFormat = new CellFormat { FontId = 0, FillId = 0, BorderId = 0 }; // Default style : Mandatory | Style ID =0
            var boldFormat = new CellFormat { FontId = 1 };  // Style with Bold text ; Style ID = 1
            var dateFormat = new CellFormat { BorderId = 0, FillId = 0, FontId = 0, NumberFormatId = 14, FormatId = 0, ApplyNumberFormat = true };
            var moneyFormat = new CellFormat { BorderId = 0, FillId = 0, FontId = 0, NumberFormatId = 4, FormatId = 0, ApplyNumberFormat = true }; // format like "#,##0.00"
            var integerFormat = new CellFormat { BorderId = 0, FillId = 0, FontId = 0, NumberFormatId = 1, FormatId = 0, ApplyNumberFormat = true }; 

            cellFormats.Append(cellFormat);
            cellFormats.Append(boldFormat);
            cellFormats.Append(dateFormat);
            cellFormats.Append(moneyFormat);
            cellFormats.Append(integerFormat);

            // Append everything to stylesheet  - Preserve the ORDER !
            workbookStyleSheet.Append(fonts);
            workbookStyleSheet.Append(fills);
            workbookStyleSheet.Append(borders);
            workbookStyleSheet.Append(cellFormats);

            //Save style for finish
            createStyle.Stylesheet = workbookStyleSheet;
            createStyle.Stylesheet.Save();
        }

        private static Columns AutoFit_Columns(DataTable dt)
        {
            var cols = new Columns();
            var excelColumn = 0;

            for (var col = 0; col < dt.Columns.Count; col++)
            {
                if (col == 0) 
                {
                    excelColumn = 1;
                }

                //now append column to worksheet, calculations done
                var c = new Column { Min = Convert.ToUInt32(excelColumn), Max = Convert.ToUInt32(excelColumn), Width = 14.5f, CustomWidth = true };
                cols.Append(c);
                excelColumn++;
            }
            return cols;
        }

        //private static double GetWidth(System.Drawing.Font stringFont, string text)
        //{
        //     //TextRenderer.MeasureText !?!? where that came from ??
        //    // This formula calculates width. For better desired outputs try to change 0.5M to something else
        //    Size textSize = TextRenderer.MeasureText(text, stringFont);
        //    double width = (double)(((textSize.Width / (double)7) * 256) - (128 / 7)) / 256;
        //    width = (double)decimal.Round((decimal)width + 0.5M, 2);
        //    return width;
        //}
    }

I hope that this could help someone else one of these day. :-)

Tranche