What does "OpenXML standard" for PHPExcel mean

Topics: Developer Forum, Project Management Forum
May 16, 2016 at 8:31 PM

On the PHPExcel - Homepage it says "This project is built around Microsoft's OpenXML standard and PHP". What does OpenXML means in this content exactly? In the examples (PHP code how to use PHPExcel) I don't see any XML code (!?).

I'am asking because I work on a project to store spreadsheet content (from Excel or OpenOffice) as (when possible XML-) text in a MySQL database. This to search through it much more easily then through many files.

May 16, 2016 at 10:28 PM
It's a reference to the MS Excel OfficeOpenXML format used for xlsx files that was introduce with MS Excel 2007.
May 17, 2016 at 8:53 PM
Yes, that's clear to me. I just wondered if I can see/use somewhere in PHPExcel "real" Excel XML code like this:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="6" uniqueCount="6"><si><t>A</t></si><si><t>B</t></si><si><t>C</t></si><si><t>Hello</t></si><si><t>World</t></si><si><t>!</t></si></sst>

All I see from the PHPExcel examples is text like this:

$worksheet->SetCellValueByColumnAndRow(0, 1, 'Hello World');

As I said I'd like to store spreadsheet content as string/text in a database. As the content itself represents already (XML) text, it makes sense to me to use it 1:1. The goal is not to transform one spreadsheet table into one database table but to transform a certain area of a spreadsheet table into a database entity...

May 17, 2016 at 9:53 PM
You won't see any XML directly inside the PHPExcel library.... it reads and writes the OfficeOpenXML format (using PHP's SimpleXML extension to read, and XMLWriter extension to write the markup. The whole point of PHPExcel is to provide a common API too access (read/create/modify/write) spreadsheet data.... it's an abstraction library that means users can work with the spreadsheet without having to understand the XML itself.... and it also supports OASIS and Gnumeric XML formats; and non-XML formats as well; so its purpose as an abstraction layer.
May 17, 2016 at 10:12 PM
Mark, thank's a lot for your immediate reply!

May I ask you for a clue how to solve/circumvent this issue OR possibly where to find a solution for the task "store spreadsheet content as string/text in a database and get it to a clients spreadsheet programm"?

Right now I see the following options:

  • Store XML text in the database
  • Write php code to translate this code into something PHPExcel readable
  • Execute the PHPExcel code and get a in-memory Excel file
  • Open this file and copy-paste the content into users Excel file
  • Store XML text in the database
  • Write php code to open a new Excel file
  • Access the Excel file XML code and insert the database XML text, save the file
  • Open the Excel as in-memory Excel file
  • Copy-paste the content into users Excel file
  • A completely other way I'm not aware of as I'm not a coder/programmer

Hope you can follow this thoughts *