c# - 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.

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: