how can i change my Datagrid column header text and how can i print updated header in excel?

debugcn Published at Dev

Snack'Eyes

i dont want to change header in sqlite table,i just want to change it on datagrid and then want to print it in excel file....here is my XAML code

        <DataGrid.Columns>
            <DataGridTextColumn Header="A" Width="100" Binding="{Binding A}"/>

            <DataGridTextColumn Header="B" Width="100" Binding="{Binding B}"/>
            <DataGridTextColumn Header="rr" Width="100" Binding="{Binding C}"/>
            <DataGridTextColumn Header="D" Width="100" Binding="{Binding D}"/>
            <DataGridTextColumn Header="E" Width="100" Binding="{Binding E}"/>
            <DataGridTextColumn Header="F" Width="100" Binding="{Binding F}"/>
            <DataGridTextColumn Header="G" Width="100" Binding="{Binding G}"/>
            <DataGridTextColumn Header="H" Width="100" Binding="{Binding H}"/>
            <DataGridTextColumn Header="I" Width="100" Binding="{Binding I}"/>
            <DataGridTextColumn Header="J" Width="100" Binding="{Binding J}"/>
            <DataGridTextColumn Header="K" Width="100" Binding="{Binding K}"/>
            <DataGridTextColumn Header="L" Width="100" Binding="{Binding L}"/>
            <DataGridTextColumn Header="M" Width="100" Binding="{Binding M}"/>
            <DataGridTextColumn Header="N" Width="100" Binding="{Binding N}"/>
            <DataGridTextColumn Header="O" Width="100" Binding="{Binding O}"/>
            <DataGridTextColumn Header="P" Width="100" Binding="{Binding P}"/>
            <DataGridTextColumn Header="Q" Width="100" Binding="{Binding Q}"/>
            <DataGridTextColumn Header="R" Width="100" Binding="{Binding R}"/>
            <DataGridTextColumn Header="S" Width="100" Binding="{Binding S}"/>
            <DataGridTextColumn Header="T" Width="100" Binding="{Binding T}"/>
            <DataGridTextColumn Header="U" Width="100" Binding="{Binding U}"/>
            <DataGridTextColumn Header="V" Width="100" Binding="{Binding V}"/>
            <DataGridTextColumn Header="W" Width="100" Binding="{Binding W}"/>
            <DataGridTextColumn Header="X" Width="100" Binding="{Binding X}"/>
            <DataGridTextColumn Header="Y" Width="100" Binding="{Binding Y}"/>
            <DataGridTextColumn Header="Z" Width="100" Binding="{Binding Z}"/>

        </DataGrid.Columns>

and i want to change it by using textbox and button in cs file

Maro

private void Heder_A_Update_Click(object sender, RoutedEventArgs e)
    {
        var newHederText = MyTextBox.Text;

        var temp = new DataTemplate();
        temp.VisualTree = new FrameworkElementFactory(typeof(TextBlock));
        temp.VisualTree.SetValue(TextBlock.TextProperty, newHederText);
        dataGrid.Columns[0].HeaderTemplate = temp;
    }

Edit

1- Convert your datagrid to dataTable using the following function

public static DataTable GridToDataTable(DataGrid dataGrid)
{
    DataView dView = (DataView)dataGrid.ItemsSource)
    DataTable table= dView.Table.Clone();
    foreach (DataRowView dv in dView )
       dt.ImportRow(dView .Row);
    return table;
}

var table= ((DataView)dataGrid.ItemsSource).ToTable();

2- Create extension method to export datatable to excel

public static void ExportToExcel(this DataTable dataTable, string excelFilePath = null)
        {
            try
            {
                int columnsCount;

                if (dataTable == null || (columnsCount = dataTable.Columns.Count) == 0)
                    throw new Exception("ExportToExcel: Null or empty input table!\n");

                // load excel, and create a new workbook
                var excel = new Microsoft.Office.Interop.Excel.Application();
                excel.Workbooks.Add();

                // single worksheet
                Microsoft.Office.Interop.Excel._Worksheet worksheet = excel.ActiveSheet;

                var header = new object[columnsCount];

                // column headings               
                for (int i = 0; i < columnsCount; i++)
                    header[i] = dataTable.Columns[i].ColumnName;

                Microsoft.Office.Interop.Excel.Range headerRange = worksheet.Range[(Microsoft.Office.Interop.Excel.Range)(worksheet.Cells[1, 1]), (Microsoft.Office.Interop.Excel.Range)(worksheet.Cells[1, columnsCount])];
                headerRange.Value = header;
                headerRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightSkyBlue);
                headerRange.Font.Bold = true;

                // DataCells
                int rowsCount = dataTable.Rows.Count;
                var cells = new object[rowsCount, columnsCount];

                for (int j = 0; j < rowsCount; j++)
                    for (int i = 0; i < columnsCount; i++)
                        cells[j, i] = dataTable.Rows[j][i];

                worksheet.Range[(Microsoft.Office.Interop.Excel.Range)(worksheet.Cells[2, 1]), (Microsoft.Office.Interop.Excel.Range)(worksheet.Cells[rowsCount + 1, columnsCount])].Value = cells;

                try
                {
                    worksheet.SaveAs(excelFilePath);
                    excel.Quit();
                }
                catch (Exception ex)
                {
                    throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
                        + ex.Message);
                }
            }
            catch (Exception ex)
            {
                throw new Exception("ExportToExcel: \n" + ex.Message);
            }
        }

3- calling the method :

string filename = @"c:\temp\export.xlsx"; //
table.ExportToExcel(filename);

GridToDataTable(datagrid).ExportToExcel(filename);

Check if the headers are ok, otherwise add the following to Datatable extension create the header of excel

int index=1;
foreach (var colName in dataGrid.Columns.Select(cs => cs.Header).ToList())
{   
      excel.Cells[1, index] = colName.ToStrihng();
      index++; 
}

Collected from the Internet

Please contact [email protected] to delete if infringement.