OpenXML - изменить формат ячейки Excel (дата и число) при экспорте из Datagridview

Кажется, есть много ответов на этот тип вопросов, которые приводят к тому, что Excel просит починить. Обычно я рекомендую людям использовать ClosedXML, но если OpenXML является обязательным, то приведенный здесь ответ: https://stackoverflow.com/a/31829959/994679 действительно работает.

Вот этот ответ с некоторыми дополнительными строками для даты, включая ячейки времени, числовые ячейки и строковые ячейки.

    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;
    }

Я решил вышеуказанную проблему. Должен сказать, что работа с OpenXML немного утомительна, но я доволен конечным результатом.

Я решил, основываясь на многих темах OpenXML, расширить ответ, предоставив полный пригодный для использования код, а не только примеры, которые я обычно встречал на многих сайтах.

Мое основное требование состояло в том, чтобы экспортировать данные Datagridview в файл Excel с правильным форматированием ячеек и более высокой скоростью экспорта, чем текущее решение Interop, которое мы используем. Код ниже можно использовать с Datatable или Dataset, с небольшой модификацией. Я также добавил некоторые другие функции, которые, по моему мнению, должны быть задокументированы, поскольку это то, что нужно большинству программистов в Excel, но, к сожалению, это не так.

Я не буду вдаваться в подробности, так как у меня уже были головные боли от всего этого, так что давайте перейдем к делу. Результатом полного кода ниже является файл Excel с экспортированными данными из Datagridview и:

• имена столбцов такие же, как заголовки Datagridview, и выделены жирным шрифтом;
• изменен шрифт по умолчанию «Calibri» на «Arial»;
• форматирование ячеек на основе фактических данных из таблицы данных (даты, числа и строки) с желаемым форматом;
• диалоговое окно «Сохранить файл»;
• автоподгонка для столбцов;

Как отмечали многие другие, порядок в OpenXML очень важен. Это применимо практически ко всему — когда вы создаете документ или стиль. Итак, все, что вы видите здесь, отлично работает для меня в Office 2016, но если вы смешиваете строки, вы очень быстро получаете какие-то странные ошибки в Excel… Как и обещал, вот мой полный код:

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;
        }

Метод, в моем случае из .dll можно легко вызвать так:

Export_to_Excel(my_dgv, »test_file«)

Краткое объяснение некоторых вещей в коде:

1.) Стили: есть много вариантов, как я мог это сделать, но это был самый простой для меня способ. Когда вам понадобится что-то посложнее, постарайтесь не забывать, что порядок здесь тоже имеет значение. И добавление шрифтов, заливок и границ необходимо.

2.) Автоподгонка: Я не могу поверить, почему это еще не задокументировано, и я считаю, что OpenXML должен иметь какой-то метод для этого по умолчанию. Во всяком случае, я решил это с помощью LINQ и с помощью здесь. Надеюсь, автор не против, но кто-то должен сказать это вслух :)

А теперь, напоследок, результаты моих тестов и преимущества/недостатки по сравнению с Interop. Я тестировал Excel 2016 с 200 тыс. строк данных:

взаимодействие

• Экспорт данных почти за 3 минуты;

Преимущества:

• более простое кодирование (на мой взгляд) с множеством встроенных функций, таких как (конечно) Autofit;
• вы действительно можете создать файл Excel (объект), который еще не сохранен на диске;

Недостатки:

• медленный по сравнению с любыми другими библиотеками, такими как OpenXML, хотя я, вероятно, мог бы сократить 3 минуты, может быть, до 2;
• Я также заметил огромную утечку памяти при больших объемах данных, хотя мой код Interop достаточно оптимизирован;

OpenXML

• Экспорт данных (с функцией автоподбора и всеми стилями) за 20 секунд;

Преимущества:

• намного быстрее, чем Interop, и я думаю, что мой «мусорный» код можно оптимизировать (вы можете помочь с этим, если вам небезразлично);

Недостатки:

• кодирование, не очевидно? :)
• утечка памяти выше, чем в Interop, хотя OpenXML предлагает два подхода, также известных как SAX или метод DOM. SAX даже быстрее, чем предоставленный код, почти без потери памяти. Если вы записываете свои данные в Excel непосредственно из DataReader, но кодирование заняло у меня много времени;

Я надеюсь, что никто не будет злиться, потому что на самом деле я собирал кусочки со многих сайтов во что-то действительно полезное, вместо того, чтобы писать сложные примеры, которые никто не понимает. И если кто-то хочет улучшить что-то выше, я был бы очень признателен. Я не идеален, и большее количество голов вместе, как правило, в конце концов формирует лучшее решение для всех :)