Translation of a file into a format for the Excel application

Question

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

Answer 1

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:

---

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.