DataGrid to Excel?

Question

I have a program created in WPF. It displays a DataGrid with data from a MySQL Database. What I want to do is to allow the user to export the contents of the DataGrid to an Excel file. Is this possible with WPF?

When I use the method shown here: https://www.outcoldman.ru/en/blog/show/201

The only row that gets imported to the xls file is the header.

Answer 1

I was also looking for something simillar to help export the data in the datagrid into excel, but found nothing which works. Atlast I just converted the content of the DataGrid into a 2D array of string and exported it using the interop dll.

The code looks something like this:

    Excel.Application xlApp;
    Excel.Workbook xlWorkBook;
    Excel.Worksheet xlWorkSheet;
    object misValue = System.Reflection.Missing.Value;
    Excel.Range rangeToHoldHyperlink;
    Excel.Range CellInstance;
    xlApp = new Excel.Application();
    xlWorkBook = xlApp.Workbooks.Add(misValue);

    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
    xlApp.DisplayAlerts = false;
    //Dummy initialisation to prevent errors.
    rangeToHoldHyperlink = xlWorkSheet.get_Range("A1", Type.Missing);
    CellInstance = xlWorkSheet.get_Range("A1", Type.Missing);

    for (int i = 0; i < NumberOfCols; i++)
    {
            for (int j = 0; j <= NumberOfRows; j++)
            {
                xlWorkSheet.Cells[j + 1, i + 1] = DataToWrite[j][i];
            }
     }

If you are looking for some formating, they are also supported in this. I wanted to add a hyperlink and the following code does that:

 CellInstance = xlWorkSheet.Cells[j + 1, i + 1];

                xlWorkSheet.Hyperlinks.Add(
                    CellInstance,
                    DataToWrite[j][i],
                    Type.Missing,
                    "Hover Text Comes Here",
                    "Text to be displayed");

If you want the first row to be the header, you can highlight them as follows:

Excel.Range Range1 = xlWorkSheet.get_Range("A1");
Range1.EntireRow.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
Range1.EntireRow.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightSkyBlue);
Range1.EntireRow.Font.Size = 14;
Range1.EntireRow.AutoFit();

Finally to Save the excel in a desired path:

xlWorkBook.SaveAs(@FilePath, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close();

The reference to the interop is added as follows:

Right Click on the Project name -> Click "Add reference" -> Goto "COM" tab -> Search for "Microsoft Excel Object Library" click "OK" to add the reference.

You must be using the following namespace :

using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;