From a one-off script to a SaaS: notes on multi-sheet Excel nested XSD

So a few months ago I was handed a thing at work. My job is junior web
dev, mostly Laravel and Drupal, and one day a ticket landed in my
queue that said: convert this Excel files into XMLs, validate it against
this XSDs, submit it to the EIT portal for grant reporting.

I thought — Excel to XML, cool, I've done CSVs before, how different can
it be.

Then I opened the XSD, it wasn't a flat thing.
The schema didn't describe rows. It grouped data under several distinct
entity types — projects, partners, deliverables, KPIs — each with its
own nested shape and its own required fields. And the Excel template
reflected that: one sheet per entity type, with headers that mostly
matched the XSD field names, except where the person who built the
template had used slightly different wording, or added trailing spaces.

So the real task wasn't "convert rows to XML". It was:

• Parse N sheets from one workbook
• Figure out which XSD entity each sheet corresponds to
• Walk the XSD to learn the nested structure
• Compose one XML tree from all the sheets together
• Validate the whole thing before anyone had to hit submit

PhpSpreadsheet handled step 1 beautifully. Everything else was me,
var_dumps, and some AI assistance on the parts I couldn't figure out
alone.

Walking the XSD instead of hard-coding it

The temptation, honestly, was to hard-code everything. Look at the XSD
once, write a transform() function full of assumptions, ship it.
I did that first. It worked. Then the XSD updated and broke every
assumption.

The second version read the XSD instead:

$dom = new \DOMDocument();
$dom->load($xsdPath);

$xpath = new \DOMXPath($dom);
$xpath->registerNamespace('xs', 'http://www.w3.org/2001/XMLSchema');

$entities = $xpath->query(
    '/xs:schema/xs:element//xs:complexType/xs:sequence/xs:element'
);

foreach ($entities as $entity) {
    $name = $entity->getAttribute('name');
    $typeRef = $entity->getAttribute('type');
    // Recurse into the type to learn its shape
}

When the XSD changed again (and it did, several times), I didn't touch the PHP. The script read the new schema and adapted. Smug moment.

Mapping sheets to entities, which is actually the hard part

Most Excel-to-XML tutorials wave their hands here. The reality: sheet
names almost never match XSD entity names cleanly. "Partners" vs
"Partner_Organizations". "KPIs" vs "Project KPIs (KIC Level)".

What worked was embarrassingly simple:

function matchSheetToEntity(string $sheetName, array $entityNames): ?string
{
    $normalized = strtolower(preg_replace('/[^a-z0-9]/', '', $sheetName));

    foreach ($entityNames as $entity) {
        $entityNorm = strtolower(preg_replace('/[^a-z0-9]/', '', $entity));
        if ($normalized === $entityNorm) {
            return $entity;
        }
    }

    $best = ['entity' => null, 'score' => 0];
    foreach ($entityNames as $entity) {
        similar_text($normalized, strtolower($entity), $pct);
        if ($pct > $best['score']) {
            $best = ['entity' => $entity, 'score' => $pct];
        }
    }

    return $best['score'] >= 75 ? $best['entity'] : null;
}

Not beautiful. But it got around 80% right on the first pass, and I
surfaced the other 20% as explicit "please confirm this mapping"
prompts. Which, it turns out, people prefer to a tool that silently
gets it wrong.

The validation step is actually the point

Generating XML is a solved problem. Generating XML that passes a
real-world XSD on the first try is a different sport. Required fields,
enum constraints, xs:choice branches, minOccurs/maxOccurs rules —
the schema rejects you for reasons you didn't know existed until it did.

PHP's DOMDocument::schemaValidate() does the work, and
libxml_get_errors() returns per-line details. The useful move is
translating those errors back into Excel coordinates:

libxml_use_internal_errors(true);
$doc = new \DOMDocument();
$doc->loadXML($generatedXml);

if (! $doc->schemaValidate($xsdPath)) {
    $errors = libxml_get_errors();
    libxml_clear_errors();

    foreach ($errors as $error) {
        $cellRef = $this->xmlLineToCell($error->line);
        $report[] = [
            'cell' => $cellRef,
            'rule' => $error->message,
        ];
    }
}

"Row 47 of the Projects sheet, column 'Budget': expected positive
decimal, got 'TBD'" is a wildly more useful error message than "schema
validation failed". That one UX detail probably saved more rejected
submissions than any other part of the whole thing.

Then I realized something

The script worked. I shipped it. I moved on.

A couple of weeks later a friend mentioned in passing he was dealing
with Peppol invoice generation at his job. He described the same shape
of problem: multi-sheet Excel template, strict XSD, nested entities,
portal that rejected anything off-spec. Different domain, identical
pain.

That's when it clicked. The EIT thing wasn't an EIT thing. It was a
category:

• Peppol invoice implementers across the EU
• FatturaPA and ZUGFeRD compliance in Italy and Germany
• In-house ERP-to-partner-XML pipes at basically any B2B company
• Grant reporting at research orgs and NGOs
• Tax and customs filings everywhere

All the same underlying problem: someone with a specific XSD and messy
Excel data, trying to produce valid XML, with usable errors when it
fails.

So I spent some nights and weekends refactoring the one-off script into
something that could accept any XSD, from any user. The main
changes:

• Hard-coded schema → users upload their own XSD
• Single-use parser → saved mapping templates you can reuse next cycle
• Trusted local script → multi-tenant with per-user encryption, because people are going to upload real invoice data
• "XML output" → "XML output + a per-row error report" (which, again, is secretly the point)

That's now a small SaaS called NicheXML. Free
tier, no credit card, zero paying customers yet because I only launched
it a couple of weeks ago. If you've built similar pipes, I'd genuinely
like to compare notes on the mapping UX — that's the part I'm least
sure about.

Things I'd tell past-me

A few things I wish someone had said to me when I started the EIT thing:

• A one-off is often a product you haven't labeled yet. If somebody handed you a specific schema, somebody else got handed a different one that works the same way. You're not that unique.
• Let the XSD drive the code. Hard-coding the schema feels faster for exactly one afternoon and then costs you forever.
• "80% auto, 20% ask" beats "100% auto, 20% wrong." Users forgive a prompt. They remember when you silently mapped the wrong column.
• Error messages are the product. "Row 47 is wrong because of X" gets remembered. "XML validation failed" is cursed.
• If you're holding other people's regulated data, encrypt it per tenant before you store it. B2B invoicing data is not nothing.

Thanks for reading. If you've worked on anything similar — especially
the sheet-to-entity mapping part — hit me in the comments, I'd love to
compare notes.

---

Built with Laravel 12, PHP 8.3, PhpSpreadsheet, MySQL, Redis. Hosted
on a Hetzner.

---