Home

resources: Welcome - The complete WPF tutorial

# Create WPF Project

dotnet new wpf -o ExcelToXML

ExceToXML.csproj

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>WinExe</OutputType>
    <TargetFramework>net8.0-windows</TargetFramework>
    <Nullable>enable</Nullable>
    <ImplicitUsings>enable</ImplicitUsings>
    <UseWPF>true</UseWPF>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="AvalonEdit" Version="6.3.0.90" />
    <PackageReference Include="ExcelDataReader" Version="3.6.0" />
    <PackageReference Include="ExcelDataReader.DataSet" Version="3.6.0" />
  </ItemGroup>
</Project>

# XAML Layout

MainWindow.xaml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
<Window x:Class="ExcelToXML.MainWindow"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
    xmlns:local="clr-namespace:ExcelToXML"
    xmlns:avalonedit="http://icsharpcode.net/sharpdevelop/avalonedit"
    mc:Ignorable="d" 
    Title="ExcelToXML" Height="600" Width="800">

    <Window.Resources>
        <Style TargetType="Label">
            <Setter Property="Foreground" Value="Gray" />
            <Setter Property="FontWeight" Value="Bold" />
        </Style>

        <Style x:Key="HeaderStyle" TargetType="TextBlock">
            <Setter Property="TextBlock.FontWeight" Value="Bold" />
            <Setter Property="TextBlock.Foreground" Value="Blue" />
        </Style>
    </Window.Resources>

    <Window.CommandBindings>
        <CommandBinding Command="ApplicationCommands.Open"  Executed="OpenCommand_Executed" />
        <CommandBinding Command="ApplicationCommands.Save" CanExecute="SaveCommand_CanExecute" Executed="SaveCommand_Executed" />
    </Window.CommandBindings>

    <Grid Margin="10">
        <Grid.RowDefinitions>
            <RowDefinition Height="Auto" />
            <RowDefinition Height="*" />
            <RowDefinition Height="Auto" />
            <RowDefinition Height="Auto" />
            <RowDefinition Height="*" />
            <RowDefinition Height="Auto" />
        </Grid.RowDefinitions>

        <Menu Grid.Row="0"  Margin="0 10" Padding="0 6">
            <MenuItem Header="_File">
                <MenuItem Header="_Open" Command="ApplicationCommands.Open" />
                <MenuItem Header="_Save" Command="ApplicationCommands.Save" />
            </MenuItem>
        </Menu>

        <DataGrid Grid.Row="1" Name="DataGridExcel" Margin="0 10" ColumnWidth="*"></DataGrid>
        
        <GroupBox Grid.Row="2" Margin="0 10" Padding="10">
            <GroupBox.Header>
                <StackPanel Orientation="Horizontal">
                    <TextBlock Style="{StaticResource HeaderStyle}" Text="XML Setting">
                        <!-- <TextBlock.Style>
                            <Style>
                                <Setter Property="TextBlock.FontWeight" Value="Bold" />
                                <Setter Property="TextBlock.Foreground" Value="Blue" />
                            </Style>
                        </TextBlock.Style> -->
                    </TextBlock>
                </StackPanel>
            </GroupBox.Header>

            <Grid>
                <Grid.ColumnDefinitions>
                    <ColumnDefinition Width="2*" />
                    <ColumnDefinition Width="*" />
                    <ColumnDefinition Width="*" />
                </Grid.ColumnDefinitions>

                <Grid>
                    <Grid.ColumnDefinitions>
                        <ColumnDefinition Width="Auto" />
                        <ColumnDefinition Width="*" />
                    </Grid.ColumnDefinitions>
                    <Grid.RowDefinitions>
                        <RowDefinition Height="Auto" />
                        <RowDefinition Height="Auto" />
                    </Grid.RowDefinitions>
                
                    <Label>Root Tag: </Label>
                    <TextBox Name="TextBoxRootTag" Grid.Column="1" Padding="6 4" Margin="0 0 10 10">records</TextBox>
                    <Label Grid.Row="1">Record Tag: </Label>
                    <TextBox Name="TextBoxRecordTag" Grid.Row="1" Grid.Column="1" Padding="6 4" Margin="0 0 10 10">record</TextBox>
                </Grid>

                <StackPanel Grid.Column="1" Margin="20 0 0 0">
                    <Label>Tag Case</Label>
                    <RadioButton Name="RadioTagCaseDefault" GroupName="TagCase" IsChecked="True">Default</RadioButton>
                    <RadioButton Name="RadioTagCaseUpper" GroupName="TagCase">Upper</RadioButton>
                    <RadioButton Name="RadioTagCaseLower" GroupName="TagCase">Lower</RadioButton>
                </StackPanel>

                <CheckBox Name="CheckBoxTrimData" Grid.Column="2">Trim Data</CheckBox>
            </Grid>
        </GroupBox>
        
        <Button Grid.Row="3" Click="ButtonToXML_Click" IsEnabled="{Binding ElementName=DataGridExcel, Path=HasItems}" Padding="10 6">Transfer to XML</Button>

        <avalonedit:TextEditor Grid.Row="4"  SyntaxHighlighting="XML" x:Name="TextEditorXML" Margin="0 10">
             <avalonedit:TextEditor.ContextMenu>
                <ContextMenu>
                    <MenuItem Header="Save XML" Command="ApplicationCommands.Save"/>
                </ContextMenu>
            </avalonedit:TextEditor.ContextMenu>
        </avalonedit:TextEditor>

        <StatusBar Grid.Row="5" >
            <StatusBar.ItemsPanel>
                <ItemsPanelTemplate>
                    <Grid>
                        <Grid.ColumnDefinitions>
                            <ColumnDefinition Width="*" />
                            <ColumnDefinition Width="Auto" />
                            <ColumnDefinition Width="*" />
                        </Grid.ColumnDefinitions>
                    </Grid>
                </ItemsPanelTemplate>
            </StatusBar.ItemsPanel>
            <StatusBarItem>
                <TextBlock Name="TextOpenPath" />
            </StatusBarItem>
            <Separator Grid.Column="1" />
            <StatusBarItem Grid.Column="2">
                <TextBlock Name="TextSavePath" />
            </StatusBarItem>
        </StatusBar>
    </Grid>
</Window>

# C# Code-behind

MainWindow.xaml.cs

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
using System.Data;
using System.IO;
using System.Text;
using System.Windows;
using System.Windows.Input;
using System.Xml;
using System.Xml.Linq;
using ExcelDataReader;
using Microsoft.Win32;

namespace ExcelToXML;

/// <summary>
/// Interaction logic for MainWindow.xaml
/// </summary>
public partial class MainWindow : Window
{
    public MainWindow()
    {
        InitializeComponent();
    }

    private void OpenCommand_Executed(object sender, ExecutedRoutedEventArgs e)
    {
        Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);

        OpenFileDialog openFileDialog = new()
        {
            Filter = "Excel files (*.xls;*.xlsx)|*.xls;*.xlsx",
            InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory)
        };

        if (openFileDialog.ShowDialog() == true)
        {
            TextOpenPath.Text = openFileDialog.FileName;
            TextSavePath.Text = "";

            using var stream = File.Open(openFileDialog.FileName, FileMode.Open, FileAccess.Read);
            using var reader = ExcelReaderFactory.CreateReader(stream);
            // 1. Use the reader methods
            // do
            // {
            //     while (reader.Read())
            //     {
            //         reader.GetDouble(0);
            //     }
            // } while (reader.NextResult());

            // 2. Use the AsDataSet extension method
            var dataSet = reader.AsDataSet(new ExcelDataSetConfiguration()
            {
                ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
                {
                    UseHeaderRow = true
                }
            });

            var dataTable = dataSet.Tables[0];

            DataGridExcel.ItemsSource = dataTable.DefaultView;
        }

    }

    private void SaveCommand_CanExecute(object sender, CanExecuteRoutedEventArgs e)
    {
        e.CanExecute = !string.IsNullOrEmpty(TextEditorXML.Text);
    }

    private void ButtonToXML_Click(object sender, RoutedEventArgs e)
    {
        DataTable dt = ((DataView)DataGridExcel.ItemsSource).ToTable();

        var doc = new XmlDocument();
        var rootTag = TextBoxRootTag.Text;
        var recordTag = TextBoxRecordTag.Text;

        if (RadioTagCaseUpper.IsChecked == true)
        {
            rootTag = rootTag.ToUpper();
            recordTag = recordTag.ToUpper();
        }

        if (RadioTagCaseLower.IsChecked == true)
        {
            rootTag = rootTag.ToLower();
            recordTag = recordTag.ToLower();
        }

        var emps = doc.CreateElement(rootTag);

        foreach (DataRow row in dt.Rows)
        {
            var emp = doc.CreateElement(recordTag);
            foreach (DataColumn col in dt.Columns)
            {
                var colname = col.ToString();

                if (RadioTagCaseUpper.IsChecked == true)
                {
                    colname = colname.ToUpper();
                }

                if (RadioTagCaseLower.IsChecked == true)
                {
                    colname = colname.ToLower();
                }

                var element = doc.CreateElement(colname);

                var data = row[col].ToString() ?? "";
                if (CheckBoxTrimData.IsChecked == true)
                {
                    data = data.Trim();
                }

                element.InnerText = data;

                emp.AppendChild(element);
            }
            emps.AppendChild(emp);
        }
        doc.AppendChild(emps);

        TextEditorXML.Text = XDocument.Parse(doc.OuterXml).ToString();
    }


    private void SaveCommand_Executed(object sender, ExecutedRoutedEventArgs e)
    {
        SaveFileDialog saveFileDialog = new()
        {
            Filter = "XML file (*.xml)|*.xml",
            InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory)
        };

        if (saveFileDialog.ShowDialog() == true)
        {
            TextSavePath.Text = saveFileDialog.FileName;
            File.WriteAllText(saveFileDialog.FileName, TextEditorXML.Text);

            MessageBox.Show("Save XML Success!", "ExcelToXML");
        }
    }
}

# Run Result

dotnet run

Edited on