CODEWITHSUNDEEP
pythonexcelxml
Jenn D.
Jenn D.

Reputation: 1055

Write Excel 2003 XML from Python

There's great stuff out there for handling Excel files from Python, and I think I'm just falling into a funny little crack: I need to write out a multi-worksheet workbook in the Excel 2003 XML format using pure Python (not win32com or VBA or something). Just like the poster here, I'm taking nasty proprietary files and having to spit them out in precisely the same nasty proprietary way, or else the nasty proprietary software won't take them back. I'm manipulating the data along the way, so this isn't just a format conversion; I need to be in Python to do real work on the files, and then write them back out in the same format they came in. A simpler version of the question was asked here but not directly answered.

The xlsxwriter docs have a nice summary of the current state of the art, which agrees with my own Googling: xlwt will handle the old non-XML formats, openpyxl specifically does Excel 2010 formats, xlsxwriter itself is for 2007+, pythonOffice hasn't been touched since 2012.

Please tell me I don't have to parse everything manually with BeautifulSoup or something to get back to Excel 2003! I can use Python 2, or 3, or both, if needed. Thanks. These are the relevant bits of namespace:

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
 ...
</DocumentProperties>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">

Upvotes: 1

Views: 898

Answers (2)

Geoffroy de Viaris
Geoffroy de Viaris

Reputation: 381

I had the same problem, but found an answer that I think both :

  • Faster to implement
  • Easier to modify, and have evolve over time (expecially if your CSV changes like having new columns added)

I had to convert a regulat CSV to .xml in SpreadsheetML format (XML Spreadsheet 2003), and I found a nice tutorial on how to do it using many ways. For Python 3, I chose ffe (Flat File Extractor)

The essential:

  1. To use ffe, you must be running is in linux environment and install it using $sudo apt-get install ffe for example. (FYI: There is also a binary for windows)
  2. You need to create a .fferc configurations file in a specific format acting like a XML template (see article or documentation links provided)
  3. You can then convert your input csv file into an output xml file using a bash/shell command line as such: $ffe -o output.xml -c csv2xml.fferc input.csv

If you want to prototype quickly, I succeded in a Google Colab Notebook. You can install it using the sudo command I provided above.

Happy coding!

Link to full article : Converting CSV to XML on Ubuntu Communiti Wiki.

Upvotes: 0

jbaar1ooo
jbaar1ooo

Reputation: 11

I've also been dealing with similarly annoying proprietary files. After doing a lot of digging through all of the same python excel extensions, I've also come to the conclusion that yes you will have to parse the xml file manually.

Upvotes: 0

Related Questions