Translation of a file into a format for the Excel application

vitaminchik 466 Reputation points
2023-06-01T20:32:35.2733333+00:00

I have a Grid where there is a TextBox, DataGrid. Can I convert the file to a format for the Excel application?

Windows Presentation Foundation
Windows Presentation Foundation
A part of the .NET Framework that provides a unified programming model for building line-of-business desktop applications on Windows.
2,708 questions
Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,669 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,602 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Hui Liu-MSFT 47,256 Reputation points Microsoft Vendor
    2023-06-02T09:35:36.8333333+00:00

    Hi,@vitaminchik.For converting the data of DataGrid and TextBox in Grid to Excel, you could refer to the following code.

    Please install the nuget-package DocumentFormat.OpenXml first of all and you could try the following code.

    Xaml:

    <Grid x:Name="grid">
            <Grid.RowDefinitions>
                <RowDefinition Height="Auto"/>
                <RowDefinition Height="*"/>
                <RowDefinition Height="50"/>
            </Grid.RowDefinitions>
       
                <TextBox Grid.Row="0" Name="txtName" HorizontalAlignment="Left" Width="200" Height="30" Text="John Doe"/>
                <TextBox Grid.Row="0" Name="txtAge" Margin="10" Width="200" Height="30" Text="30"/>
            
           
    
            <DataGrid Grid.Row="1" Name="dataGrid"  Margin="10">
               
            </DataGrid>
    
            <Button Grid.Row="2" Content="Export to Excel" Width="120" Height="30" Margin="10"
                    Click="ExportToExcel_Click"/>
        </Grid>
    
    
    
    

    Codebedhind:

     public partial class MainWindow : Window
        {
            DataSet dataSet;
            public MainWindow()
            {
                InitializeComponent();
                dataSet = CreateDataSet();
    
                DataTable dataTable = dataSet.Tables[0];
                dataGrid.ItemsSource = dataTable.DefaultView;
            }
    
            private DataSet CreateDataSet()
            {
                // Create a new DataSet
                DataSet dataSet = new DataSet();
    
                // Create a new DataTable
                DataTable dataTable = new DataTable("MyTable");
    
                // Add columns to the DataTable
                dataTable.Columns.Add("ID", typeof(int));
                dataTable.Columns.Add("Name", typeof(string));
                dataTable.Columns.Add("Age", typeof(int));
    
                // Add rows to the DataTable
                dataTable.Rows.Add(1, "John", 25);
                dataTable.Rows.Add(2, "Jane", 30);
                dataTable.Rows.Add(3, "Mike", 35);
    
                // Add the DataTable to the DataSet
                dataSet.Tables.Add(dataTable);
    
                return dataSet;
            }
         
    
           
            private void ExportToExcel_Click(object sender, RoutedEventArgs e)
            {
    
                var filePath = "C:\\Users\\Administrator\\Desktop\\DataExport.xlsx";
                ExportDataSet(dataSet,grid, filePath);
                MessageBox.Show("Data exported to Excel successfully!");
             
            }
            private static void ExportDataSet(DataSet ds, Grid grid,string destination)
            {
                using (var workbook = SpreadsheetDocument.Create(destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
                {
                    var workbookPart = workbook.AddWorkbookPart();
    
                    workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
    
                    workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();
    
                    foreach (System.Data.DataTable table in ds.Tables)
                    {
    
                        var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
                        var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
                        sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);
    
                        DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();
                        string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
    
                        uint sheetId = 1;
                        if (sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
                        {
                            sheetId =
                                sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                        }
    
                        DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName };
                        sheets.Append(sheet);
    
                        DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
    
                        List<String> columns = new List<string>();
                        foreach (System.Data.DataColumn column in table.Columns)
                        {
                            columns.Add(column.ColumnName);
    
                            DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                            cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                            cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
                            headerRow.AppendChild(cell);
                        }
    
    
                        sheetData.AppendChild(headerRow);
    
                        foreach (System.Data.DataRow dsrow in table.Rows)
                        {
                            DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
                            foreach (String col in columns)
                            {
                                DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                                cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                                cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
                                newRow.AppendChild(cell);
                            }
    
    
                            sheetData.AppendChild(newRow);
    
    
                        
    
    
    
                        }
    
    
    
                        uint rowIndex = 1;
                        var textBoxes = grid.Children.OfType<System.Windows.Controls.TextBox>();
                        foreach (var textBox in textBoxes)
                        {
                            DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
                            string text = textBox.Text;
                            DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
    
                            cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(text); //
    
                            rowIndex++;
                            newRow.AppendChild(cell);
                            sheetData.AppendChild(newRow);
                        }
                       
    
                    }
                }
            }
    
    
        }
    
    
    

    The result:

    enter image description here


    If the response is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.