I want to export datagrid rows in to an Excel file. Some information are in English and some others are in Perisan. I have searched a lot and tested two different ways. The first way uses ApplicationCommands.Copy.Execute to copy data. The problem in this way is that characters in Persian changes to ??? (question mark), even if I use utf-8 encoding or any other Encoding. I also used Microsoft.Office.Interop.Excel.Application to copy the rows. The problem with this way is that just 7 top rows are copied but the Encoding problem is solved in this way. The first way (https://www.codeproject.com/Questions/354557/WPF-DataGrid-to-Excel) is as below:

     DgReport.SelectAllCells();
        DgReport.ClipboardCopyMode = DataGridClipboardCopyMode.IncludeHeader;
        ApplicationCommands.Copy.Execute(null, DgReport);
        String resultat = (string)Clipboard.GetData(DataFormats.CommaSeparatedValue);
        String result = (string)Clipboard.GetData(DataFormats.Text);
        DgReport.UnselectAllCells();
        System.IO.StreamWriter file1 = new System.IO.StreamWriter(@"C:\Users\test.csv", false, Encoding.UTF8);
        file1.WriteLine(result.Replace(',', ' '));
        file1.Close();

The second way (https://www.codeproject.com/Questions/678267/Data-Export-from-DataGrid-to-Excel-is-not-working) is as below. This way just copy 7 top rows but no problem with Encoding.

    int i1 = 0;
        int k1 = 1, h = 1;
        string strFullFilePathNoExt = @"C:\Users\TestExcel4.xls";

        var rows = GetDataGridRows(DgReport);
        Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel._Workbook ExcelBook;
        Microsoft.Office.Interop.Excel._Worksheet ExcelSheet;
        ExcelBook = (Microsoft.Office.Interop.Excel._Workbook)ExcelApp.Workbooks.Add(1);
        ExcelSheet = (Microsoft.Office.Interop.Excel._Worksheet)ExcelBook.ActiveSheet;
        for (i1 = 1; i1 <= DgReport.Columns.Count; i1++)
        {
            ExcelSheet.Cells[1, i1] = DgReport.Columns[i1 - 1].Header.ToString();
        }
        foreach (DataGridRow r in rows)
        {
            DataRowView rv = (DataRowView)r.Item;
            foreach (DataGridColumn column in DgReport.Columns)
            {
                if (column.GetCellContent(r) is TextBlock)
                {
                    TextBlock cellContent = column.GetCellContent(r) as TextBlock;
                    ExcelSheet.Cells[h + 1, k1] = cellContent.Text.Trim();
                    k1++;
                }

            }
            k1 = 1;
            h++;
        }
        ExcelApp.Visible = false;
        ExcelBook.SaveAs(strFullFilePathNoExt, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, Missing.Value, Missing.Value, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
        Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, true,
        Missing.Value, Missing.Value, Missing.Value);
        ExcelBook.Close(strFullFilePathNoExt, Missing.Value, Missing.Value);
        ExcelSheet = null;
        ExcelBook = null;
        ExcelApp = null;
    }
     public IEnumerable<DataGridRow> GetDataGridRows(DataGrid grid)
    {
        var itemsSource = grid.ItemsSource as IEnumerable;
        if (null == itemsSource) yield return null;
        foreach (var item in itemsSource)
        {
            var row = grid.ItemContainerGenerator.ContainerFromItem(item) as DataGridRow;
            if (null != row) yield return row;
        }

    }

The way I define my datagrid is as below:

       <DataGrid Name="DgReport" IsReadOnly="True"  HeadersVisibility="Column" ItemsSource="{Binding}" ScrollViewer.HorizontalScrollBarVisibility="Visible"  AutoGenerateColumns="False" SelectionChanged="DgReport_SelectionChanged" FlowDirection="RightToLeft" ></DataGrid>

The way I fill my datagrid is that I send data from database to a datatable named: Reportdt and then

     DgReport.DataContext = Reportdt;
     DataGridTextColumn c3 = new DataGridTextColumn();
     Binding b3 = new Binding("FromNumber");
      c3.Binding = b3;        
      c3.CanUserResize = false;
      DgReport.Columns.Add(c3);

Any idea?

1 Answers

0
user3750985 On

I solved the problem in this way:

 string strFullFilePathNoExt = @"MyFile_" + DateTime.Now.Year + DateTime.Now.Month + DateTime.Now.Day + DateTime.Now.Hour + DateTime.Now.Minute + ".xls";

                var rows = GetDataGridRows(DgReport);
                Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
                Microsoft.Office.Interop.Excel._Workbook ExcelBook;
                Microsoft.Office.Interop.Excel._Worksheet ExcelSheet2;
                ExcelBook = (Microsoft.Office.Interop.Excel._Workbook)ExcelApp.Workbooks.Add(1);
                ExcelSheet2 = (Microsoft.Office.Interop.Excel._Worksheet)ExcelBook.ActiveSheet;

                ExcelSheet2.Cells[1, 6] = "Col6";
                ExcelSheet2.Cells[1, 5] = "Col5";
                ExcelSheet2.Cells[1, 1].Value = " Col1";
                ExcelSheet2.Cells[1, 3].Value = "Col3";
                ExcelSheet2.Cells[1, 2].Value = "Col2";
                ExcelSheet2.Cells[1, 4].Value = "Col4";

                for (int Idx = 0; Idx < Reportdt.Rows.Count; Idx++)
                {

                    //   ws.Range["A2"].Offset[Idx].Resize[1, dt.Columns.Count].Value = dt.Rows[Idx].ItemArray[14];
                    ExcelSheet2.Cells[Idx + 2, 6].Value = Reportdt.Rows[Idx].ItemArray[5];
                    ExcelSheet2.Cells[Idx + 2, 5].Value = Reportdt.Rows[Idx].ItemArray[13];
                    ExcelSheet2.Cells[Idx + 2, 1].Value = Reportdt.Rows[Idx].ItemArray[14];
                    ExcelSheet2.Cells[Idx + 2, 1].NumberFormat = "############";
                    ExcelSheet2.Cells[Idx + 2, 3].Value = Reportdt.Rows[Idx].ItemArray[16];
                    ExcelSheet2.Cells[Idx + 2, 2].Value = Reportdt.Rows[Idx].ItemArray[18];
                    //ExcelSheet2.Cells[Idx + 1, 6].Value = Reportdt.Rows[Idx].ItemArray[18];
                    ExcelSheet2.Cells[Idx + 2, 4].Value = Reportdt.Rows[Idx].ItemArray[17];
                }

                ExcelApp.Visible = false;
                ExcelBook.SaveAs(strFullFilePathNoExt, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, Missing.Value, Missing.Value, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
                Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, true,
                Missing.Value, Missing.Value, Missing.Value);
                ExcelBook.Close(strFullFilePathNoExt, Missing.Value, Missing.Value);
                ExcelSheet2 = null;
                ExcelBook = null;
                ExcelApp = null;