Tkinter Excel to XML Converter using Pandas

Q: How do I convert an Excel worksheet to XML with Pandas?

Use pd.read_excel() to create a DataFrame from the worksheet and then use DataFrame.to_xml() to create the XML file.

Q: Can the user select a worksheet from a workbook?

Yes. pd.ExcelFile().sheet_names returns the available worksheet names, which can be displayed in a Tkinter Combobox.

Tkinter Excel worksheet to XML converter using Pandas DataFrame

This project uses Tkinter and Pandas to convert a worksheet from an Excel workbook into XML.

The user selects an Excel file through a Tkinter file browser. The application reads the available worksheet names, lets the user select one, creates a DataFrame with read_excel() and exports the selected worksheet using to_xml().

Excel workbook
      |
      v
Worksheet names
      |
      v
Select worksheet
      |
      v
pd.read_excel()
      |
      v
Pandas DataFrame
      |
      v
XML-safe columns
      |
      v
df.to_xml()
      |
      v
XML file

Excel column names: worksheet headings can contain spaces, punctuation or begin with numbers. These headings are normalized before being used as XML element names.

Show Table of Contents

Select an Excel Workbook
Read Worksheet Names
Create the DataFrame
Create Valid XML Element Names
Map Excel Headers to XML Tags
Choose Root and Row Elements
Preview XML
Export the Complete Worksheet
Missing Excel Values
XML Special Characters
Complete Excel to XML Program
Excel to XML without Tkinter
Use Other Data Sources
Continue the Conversion Projects
FAQs

Select an Excel Workbook Top ↑

Use the Tkinter file browser to select an Excel workbook:

file_path=filedialog.askopenfilename(
    title='Select Excel workbook',
    filetypes=[
        ('Excel files','*.xlsx *.xls'),
        ('All files','*.*')
    ]
)

If the dialog is cancelled, the existing workbook and DataFrame are left unchanged.

Read Worksheet Names from the Workbook Top ↑

An Excel workbook can contain several worksheets. Instead of automatically loading only the first one, create a Pandas ExcelFile:

excel_file=pd.ExcelFile(
    file_path
)

sheet_names=excel_file.sheet_names

For example:

Sales
Customers
Products
Summary

The names are displayed in a readonly Combobox.

sheet_combo['values']=sheet_names

The first worksheet can be selected initially:

sheet_var.set(
    sheet_names[0]
)

Create a DataFrame from the Selected Worksheet Top ↑

After the worksheet is selected:

df=pd.read_excel(
    workbook_path,
    sheet_name=sheet_name
)

The application displays:

workbook filename;
selected worksheet;
number of rows;
number of columns;
first five worksheet rows;
Excel heading to XML element mapping.

Create Valid XML Element Names Top ↑

Excel headings can contain almost any text:

Employee Name
Joining Date
2nd Address
Salary ($)

Before XML export, normalize the headings:

def xml_name(name,prefix='field'):
    name=re.sub(
        r'[^A-Za-z0-9_.-]+',
        '_',
        str(name).strip()
    )

    if not name:
        name=prefix

    if not re.match(
        r'[A-Za-z_]',
        name
    ):
        name=prefix+'_'+name

    return name

Examples:

Employee Name  -> Employee_Name
Joining Date   -> Joining_Date
2nd Address    -> field_2nd_Address
Salary ($)     -> Salary_

Keep XML Column Names Unique Top ↑

Different Excel headings can normalize to the same XML element name.

The program checks every generated tag and adds a numeric suffix when required:

Amount_
Amount__2
Amount__3

A separate DataFrame is used:

df
xml_df

df retains the original Excel headings. xml_df contains XML-safe column names.

Choose XML Root and Row Element Names Top ↑

The user can enter names such as:

Root element: employees
Row element:  employee

The generated XML then has this structure:

<employees>
  <employee>
    ...
  </employee>
  <employee>
    ...
  </employee>
</employees>

The root and row names are validated before creating the XML.

Preview XML before Exporting Top ↑

The first five worksheet records are converted to XML for preview:

preview=xml_df.head(
    5
).to_xml(
    index=False,
    root_name=root_name,
    row_name=row_name,
    encoding='utf-8',
    xml_declaration=True,
    pretty_print=True,
    parser='etree'
)

This confirms the XML structure without creating a potentially large preview from the complete worksheet.

Export the Complete DataFrame with to_xml() Top ↑

The user chooses the destination through a Tkinter Save As dialog.

xml_df.to_xml(
    save_path,
    index=False,
    root_name=root_name,
    row_name=row_name,
    encoding='utf-8',
    xml_declaration=True,
    pretty_print=True,
    parser='etree'
)

index=False prevents the Pandas DataFrame index from appearing as another XML element.

Missing Excel Values in XML Top ↑

A worksheet can contain blank cells:

Name  | Mark
John  | 75
Max   |
Arnold| 55

Pandas represents the blank value as missing data. XML export can represent this as an empty element.

<Mark />

If another value is required, clean the DataFrame before export. See Tkinter Pandas data cleaning.

XML Special Characters Are Escaped Automatically Top ↑

Suppose an Excel cell contains:

Research & Development

the XML serializer creates valid XML text:

Research &amp; Development

Do not manually replace ampersands or angle brackets before calling to_xml().

Complete Tkinter Excel to XML Converter Top ↑

import os
import re
import tkinter as tk
from tkinter import filedialog
from tkinter import ttk

import pandas as pd

workbook_path=None
df=None
xml_df=None

root=tk.Tk()
root.title('Excel to XML Converter - plus2net')
root.geometry('820x650')
root.minsize(650,520)

root.columnconfigure(0,weight=1)
root.rowconfigure(5,weight=1)

file_var=tk.StringVar(value='No Excel workbook selected')
sheet_var=tk.StringVar()
root_var=tk.StringVar(value='data')
row_var=tk.StringVar(value='record')
summary_var=tk.StringVar(value='Rows: 0 | Columns: 0')
status_var=tk.StringVar(value='Select an Excel workbook.')

def xml_name(name,prefix='field'):
    name=re.sub(
        r'[^A-Za-z0-9_.-]+',
        '_',
        str(name).strip()
    )

if not name:
        name=prefix

if not re.match(r'[A-Za-z_]',name):
        name=prefix+'_'+name

return name

def valid_xml_name(name):
    return bool(
        re.fullmatch(
            r'[A-Za-z_][A-Za-z0-9_.-]*',
            name
        )
    )

def create_xml_dataframe(data):
    used=set()
    new_columns=[]
    mapping=[]

for index,column in enumerate(data.columns,1):
        base=xml_name(column,f'field_{index}')
        name=base
        counter=2

while name in used:
            name=f'{base}_{counter}'
            counter+=1

used.add(name)
        new_columns.append(name)
        mapping.append((str(column),name))

output=data.copy()
    output.columns=new_columns

return output,mapping

def show_text(text):
    output.delete('1.0',tk.END)
    output.insert(tk.END,text)

def select_workbook():
    global workbook_path,df,xml_df

file_path=filedialog.askopenfilename(
        title='Select Excel workbook',
        filetypes=[
            ('Excel files','*.xlsx *.xls'),
            ('All files','*.*')
        ]
    )

if not file_path:
        status_var.set('No Excel workbook selected.')
        return

try:
        excel_file=pd.ExcelFile(file_path)
        sheet_names=excel_file.sheet_names
        excel_file.close()
    except (
        OSError,
        ValueError,
        ImportError
    ) as e:
        status_var.set(f'Unable to open workbook: {e}')
        return

if not sheet_names:
        status_var.set('No worksheets found in the workbook.')
        return

workbook_path=file_path
    df=None
    xml_df=None

file_var.set(file_path)

sheet_combo['values']=sheet_names
    sheet_var.set(sheet_names[0])

load_sheet_button.config(state=tk.NORMAL)
    preview_button.config(state=tk.DISABLED)
    save_button.config(state=tk.DISABLED)

show_text(
        'WORKSHEETS\n\n'
        +'\n'.join(sheet_names)
    )

summary_var.set('Rows: 0 | Columns: 0')

status_var.set(
        f'Found {len(sheet_names)} worksheet(s). '
        'Select a worksheet and click Load Worksheet.'
    )

def load_worksheet():
    global df,xml_df

if workbook_path is None:
        status_var.set('Select an Excel workbook first.')
        return

sheet_name=sheet_var.get()

if not sheet_name:
        status_var.set('Select a worksheet.')
        return

try:
        new_df=pd.read_excel(
            workbook_path,
            sheet_name=sheet_name
        )
    except (
        OSError,
        ValueError,
        ImportError
    ) as e:
        status_var.set(f'Unable to read worksheet: {e}')
        return

df=new_df
    xml_df,mapping=create_xml_dataframe(df)

mapping_text='\n'.join(
        f'{source} -> {target}'
        for source,target in mapping
    )

preview=df.head(5).to_string(index=False)

show_text(
        f'WORKSHEET: {sheet_name}\n\n'
        'DATA PREVIEW\n\n'
        f'{preview}\n\n'
        'EXCEL COLUMN -> XML ELEMENT\n\n'
        f'{mapping_text}'
    )

summary_var.set(
        f'Rows: {df.shape[0]} | Columns: {df.shape[1]}'
    )

preview_button.config(state=tk.NORMAL)
    save_button.config(state=tk.NORMAL)

status_var.set(
        f'Loaded worksheet "{sheet_name}".'
    )

def get_xml_names():
    root_name=root_var.get().strip()
    row_name=row_var.get().strip()

if not valid_xml_name(root_name):
        status_var.set('Enter a valid XML root element name.')
        return None

if not valid_xml_name(row_name):
        status_var.set('Enter a valid XML row element name.')
        return None

return root_name,row_name

def preview_xml():
    if xml_df is None:
        status_var.set('Load an Excel worksheet first.')
        return

names=get_xml_names()

if names is None:
        return

root_name,row_name=names

try:
        xml_text=xml_df.head(5).to_xml(
            index=False,
            root_name=root_name,
            row_name=row_name,
            encoding='utf-8',
            xml_declaration=True,
            pretty_print=True,
            parser='etree'
        )
    except (ValueError,TypeError) as e:
        status_var.set(f'Unable to create XML preview: {e}')
        return

show_text(
        'XML PREVIEW - FIRST 5 ROWS\n\n'
        +xml_text
    )

status_var.set('Showing XML preview.')

def save_xml():
    if xml_df is None:
        status_var.set('Load an Excel worksheet first.')
        return

names=get_xml_names()

if names is None:
        return

root_name,row_name=names

save_path=filedialog.asksaveasfilename(
        title='Save XML file',
        defaultextension='.xml',
        filetypes=[
            ('XML files','*.xml'),
            ('All files','*.*')
        ]
    )

if not save_path:
        status_var.set('XML save cancelled.')
        return

try:
        xml_df.to_xml(
            save_path,
            index=False,
            root_name=root_name,
            row_name=row_name,
            encoding='utf-8',
            xml_declaration=True,
            pretty_print=True,
            parser='etree'
        )
    except (OSError,ValueError,TypeError) as e:
        status_var.set(f'Unable to save XML: {e}')
        return

status_var.set(
        f'Saved {len(xml_df)} row(s) from worksheet '
        f'"{sheet_var.get()}" to {os.path.basename(save_path)}.'
    )

top_frame=ttk.Frame(root,padding=10)
top_frame.grid(row=0,column=0,sticky='ew')
top_frame.columnconfigure(1,weight=1)

ttk.Button(
    top_frame,
    text='Select Excel',
    command=select_workbook
).grid(row=0,column=0,padx=(0,10))

ttk.Label(
    top_frame,
    textvariable=file_var,
    wraplength=650
).grid(row=0,column=1,sticky='w')

sheet_frame=ttk.Frame(root,padding=(10,5))
sheet_frame.grid(row=1,column=0,sticky='w')

ttk.Label(sheet_frame,text='Worksheet:').grid(row=0,column=0,padx=(0,5))

sheet_combo=ttk.Combobox(
    sheet_frame,
    textvariable=sheet_var,
    state='readonly',
    width=28
)
sheet_combo.grid(row=0,column=1,padx=5)

load_sheet_button=ttk.Button(
    sheet_frame,
    text='Load Worksheet',
    command=load_worksheet,
    state=tk.DISABLED
)
load_sheet_button.grid(row=0,column=2,padx=5)

xml_frame=ttk.LabelFrame(
    root,
    text='XML Structure',
    padding=10
)
xml_frame.grid(row=2,column=0,sticky='ew',padx=10,pady=5)

ttk.Label(xml_frame,text='Root:').grid(row=0,column=0,padx=5)
ttk.Entry(xml_frame,textvariable=root_var,width=18).grid(row=0,column=1,padx=5)

ttk.Label(xml_frame,text='Row:').grid(row=0,column=2,padx=5)
ttk.Entry(xml_frame,textvariable=row_var,width=18).grid(row=0,column=3,padx=5)

preview_button=ttk.Button(
    xml_frame,
    text='Preview XML',
    command=preview_xml,
    state=tk.DISABLED
)
preview_button.grid(row=0,column=4,padx=10)

save_button=ttk.Button(
    xml_frame,
    text='Save XML',
    command=save_xml,
    state=tk.DISABLED
)
save_button.grid(row=0,column=5,padx=5)

ttk.Label(
    root,
    textvariable=summary_var,
    font=('Arial',10,'bold')
).grid(row=3,column=0,sticky='w',padx=10,pady=(5,0))

ttk.Separator(
    root,
    orient='horizontal'
).grid(row=4,column=0,sticky='ew',padx=10,pady=5)

output_frame=ttk.Frame(root,padding=(10,5))
output_frame.grid(row=5,column=0,sticky='nsew')
output_frame.columnconfigure(0,weight=1)
output_frame.rowconfigure(0,weight=1)

output=tk.Text(output_frame,wrap='none')
output.grid(row=0,column=0,sticky='nsew')

y_scroll=ttk.Scrollbar(
    output_frame,
    orient='vertical',
    command=output.yview
)
y_scroll.grid(row=0,column=1,sticky='ns')

x_scroll=ttk.Scrollbar(
    output_frame,
    orient='horizontal',
    command=output.xview
)
x_scroll.grid(row=1,column=0,sticky='ew')

output.configure(
    yscrollcommand=y_scroll.set,
    xscrollcommand=x_scroll.set
)

ttk.Label(
    root,
    textvariable=status_var,
    wraplength=770
).grid(row=6,column=0,sticky='w',padx=10,pady=(5,10))

root.mainloop()

Why Select the Worksheet Explicitly? Top ↑

The earlier version used:

pd.read_excel(file_path)

which reads the default worksheet.

A workbook such as:

sales.xlsx

Sheet: January
Sheet: February
Sheet: March
Sheet: Summary

should allow the user to choose which worksheet is converted.

The revised application first discovers:

excel_file.sheet_names

and then passes the selected worksheet to:

pd.read_excel(
    workbook_path,
    sheet_name=sheet_name
)

Why Keep df and xml_df Separately? Top ↑

The original Excel DataFrame may contain headings such as:

Employee Name
Joining Date
Salary ($)

These are useful DataFrame labels, so there is no need to permanently alter them.

df
 |
 +--> original Excel headings

xml_df
 |
 +--> Employee_Name
 +--> Joining_Date
 +--> Salary_

Only the XML-specific DataFrame receives normalized element names.

Excel to XML without Tkinter Top ↑

If a GUI and worksheet selector are not required:

import pandas as pd

df=pd.read_excel(
    'students.xlsx',
    sheet_name='Sheet1'
)

df.to_xml(
    'students.xml',
    index=False,
    root_name='students',
    row_name='student',
    parser='etree'
)

This short version assumes the Excel column headings are already valid XML element names.

Use Other Data Sources with the Same Pattern Top ↑

The important pattern is:

Data source
    |
    v
Pandas DataFrame
    |
    v
to_xml()

The DataFrame can come from several Plus2net project workflows.

CSV to XML Top ↑

Use CSV to XML with Tkinter and Pandas when the source data is a CSV file.

CSV to JSON or XML Top ↑

Use CSV to JSON/XML converter when the user should choose the destination format.

SQLite to DataFrame Top ↑

Use the SQLite to Pandas DataFrame project as a starting point when the source data comes from an SQLite table.

Once the records are stored in a DataFrame, the same XML export concepts can be applied.

Continue the Tkinter Pandas Conversion Projects Top ↑

Convert CSV directly to XML:

CSV to XML

Choose between JSON and XML output:

CSV to JSON or XML

Export SQLite records to CSV:

SQLite to CSV

Frequently Asked Questions Top ↑

Q1: How do I convert an Excel worksheet to XML with Pandas?

Use pd.read_excel() to create a DataFrame from the worksheet and then use DataFrame.to_xml() to create the XML file.

Q2: Can the user select a worksheet from a workbook?

Yes. pd.ExcelFile().sheet_names returns the available worksheet names, which can be displayed in a Tkinter Combobox.

Q3: Why must Excel headings be normalized before XML export?

Excel headings can contain spaces, punctuation and leading digits, while XML element names must follow XML naming rules.

Q4: How are duplicate XML element names handled?

The application checks each normalized name and adds a numeric suffix when another generated XML name is already in use.

Q5: How are blank Excel cells represented in XML?

Missing DataFrame values can be represented by empty XML elements. The DataFrame can also be cleaned before export if another value is required.

Q6: Does Pandas escape XML special characters automatically?

Yes. The XML serializer handles characters such as ampersands and angle brackets when the document is generated.

Q7: Does converting a worksheet modify the Excel workbook?

No. The Excel file is only read. The application creates a separate XML output file.

CSV to JSON/XML Directory Browser

Tkinter Projects Tkinter Pandas Projects

Python Tkinter Pandas read_excel() to_xml() XML Python Tutorials

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

Convert Excel Worksheet to XML using Tkinter and Pandas