Click here to Skip to main content
14,741,024 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
Hi
How to Export Data from WPF(Window based) Datagrid to Excel in same format becasue i m using multiple header in Datagrid. please help me out.

Thnx
Posted
Updated 29-Nov-20 6:47am
Comments
mukeshdepani8681 27-Mar-12 7:56am
   
simply it is done but i have more than one header i mean there are multiple header for eg. I have summarize data with grouping so there should be multiple header so i can't do it from given code.

dgDisplay is a DataGrid Name.Hope the below code will solve your problem.

private void ExportToExcelAndCsv()
       {
           dgDisplay.SelectAllCells();
           dgDisplay.ClipboardCopyMode = DataGridClipboardCopyMode.IncludeHeader;
           ApplicationCommands.Copy.Execute(null, dgDisplay);
           String resultat = (string)Clipboard.GetData(DataFormats.CommaSeparatedValue);
           String result = (string)Clipboard.GetData(DataFormats.Text);
           dgDisplay.UnselectAllCells();
           System.IO.StreamWriter file1 = new System.IO.StreamWriter(@"C:\Users\test.xls");
           file1.WriteLine(result.Replace(',', ' '));
           file1.Close();

           MessageBox.Show(" Exporting DataGrid data to Excel file created.xls");
       }
   
Comments
Member 10269928 29-Apr-15 5:03am
   
Code is working Thanks
Leonardo Lynx 31-Aug-15 20:34pm
   
Funciona! Muchas gracias!
iato 22-May-18 14:23pm
   
Works in 2018 !
Member 14204059 1-Apr-19 14:06pm
   
This code is not working for me.. Excel sheet still blank .. Can you please tell me the solution? Thanks
It is going to have to be a manual process. Sample of what I have done to export Excel:
private static void Excel(string fileName, List<IDirectoryInventoryDataCollector> list)
{
    try
    {
        var xlApp = new Excel.Application();
        var xlWorkBook = xlApp.Workbooks.Add();
        var xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
        ExcelTitleRow(list[0], 1, xlWorkSheet);

        int row = 2;
        foreach (var item in list)
        {
            ExcelFillRow(item, row++, xlWorkSheet);
        }

        for (int i = 1; i < list[0].MaxLevel - 1; i++)
        {
            ((Range)xlWorkSheet.Columns[i]).ColumnWidth = 2;
        }
        ((Range)xlWorkSheet.Columns[list[0].MaxLevel - 1]).ColumnWidth = 30;
        ((Range)xlWorkSheet.Rows[1]).WrapText = true;
        ((Range)xlWorkSheet.Rows[1]).HorizontalAlignment = HorizontalAlignment.Center;
        ((Range)xlWorkSheet.Cells[1, 1]).WrapText = false;

        xlWorkBook.SaveAs(fileName);
        xlWorkBook.Close();
        xlApp.Quit();
    }
    catch (AccessViolationException)
    {
        System.Windows.Forms.MessageBox.Show(
             "Have encountered access violation. This could be issue with Excel 2000 if that is only version installed on computer",
             "Access Violation");
    }
    catch (Exception)
    {
        System.Windows.Forms.MessageBox.Show("Unknown error",
             "Unknown error");
    }
}

private static void ExcelFillRow(IDirectoryInventoryDataCollector item, int row, Excel.Worksheet sheet)
{
    sheet.Cells[row, item.Level] = item.Name;
    int column = item.MaxLevel;
    foreach (var property in item.GetProperties())
    {
        sheet.Cells[row, column++] = property;
    }
}

private static void ExcelTitleRow(IDirectoryInventoryDataCollector item, int row, Excel.Worksheet sheet)
{
    sheet.Cells[row, 1] = "Name";
    int column = item.MaxLevel;
    foreach (var property in item.GetPropertyNames())
    {
        sheet.Cells[row, column++] = property;
    }
}

The namespace you need are
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop.Excel;
   
Comments
Member 13688494 6-Apr-18 5:49am
   
Hello,
List<idirectoryinventorydatacollector> doesn't work for me. Are there using for this specific List ?
Check this code for WPF DataGrid to Excel

dataGrid is name of DataGrid.

DataGrid dg = dataGrid;
dg.SelectAllCells();
dg.ClipboardCopyMode = DataGridClipboardCopyMode.IncludeHeader;
ApplicationCommands.Copy.Execute(null, dg);
dg.UnselectAllCells();
String Clipboardresult =(string)Clipboard.GetData(DataFormats.CommaSeparatedValue);
StreamWriter swObj = new StreamWriter("exportToExcel.csv");
swObj.WriteLine(Clipboardresult);
swObj.Close();
Process.Start("exportToExcel.csv");


Above code not required any dll to create the excel file.
   
Comments
Pavasorn Noiphan 26-Jul-15 18:29pm
   
From solution 3, is it possible to change font of the texts or column's size?
Member 12059989 20-Oct-15 11:59am
   
Data from Date column is exporting #### only. How to deal with this problem ?
Member 14204059 1-Apr-19 14:06pm
   
This code is not working for me.. Excel sheet still blank .. Can you please tell me the solution? Thanks
Hi, I write a extension method for WPF Datagrid Export to Excel(CSV):

if "MyDatagrid" is the name of your datagrid, use one line code to call on own user control.
MyDatagrid.ExportToExcel(this);

and add this method to your extension static class
#region DataGrid Extentions

public static void ExportToExcel(this DataGrid dg, UserControl owner, string filename = "")
        {
            try
            {
                dg.SelectionMode = DataGridSelectionMode.Extended;
                dg.SelectAllCells();

                Clipboard.Clear();
                ApplicationCommands.Copy.Execute(null, dg);

                var saveFileDialog = new SaveFileDialog
                {
                    FileName = filename != "" ? filename : "gpmfca-exportedDocument",
                    DefaultExt = ".csv", 
                    Filter = "Common Seprated Documents (.csv)|*.csv"
                };

                if (saveFileDialog.ShowDialog() == true)
                {
                    var clip2 = Clipboard.GetText();
                    File.WriteAllText(saveFileDialog.FileName, clip2.Replace('\t', ','), Encoding.UTF8);
                    Process.Start(saveFileDialog.FileName);
                }

                dg.UnselectAllCells();
                dg.SelectionMode = DataGridSelectionMode.Single;
            }
            catch (Exception ex)
            {
                owner.ShowMessageBox(ex.Message);
                Clipboard.Clear();
            }
        }
#endregion

finally don't forget to
using Microsoft.Win32;
in extension class
and set
ClipboardCopyMode="IncludeHeader"
for your datagrid.
   
v4

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900