5 ms·
How neat! Here's a little Excel-file writer in PHP; Excel will read these files, but this library won't: <?php echo '<?xml version="1.0" encoding="utf-8"?
by geocar 9y ago
How neat!
Here's a little Excel-file writer in PHP; Excel will read these files, but this library won't:
<?php echo '<?xml version="1.0" encoding="utf-8"?>'; ?>
<?php echo '<?mso-application progid="Excel.Sheet"?>'; ?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:html="http://www.w3.org/TR/REC-html40">
<Worksheet ss:Name="Sheet1">
<?php $data = array(array(4,5,6),array("dude","whatever","m<om")); ?>
<Table>
<?php foreach($data as $row): ?>
<Row>
<?php foreach($row as $cell): ?>
<Cell>
<Data ss:Type="String"><?= htmlentities($cell) ?></Data>
</Cell>
<?php endforeach; ?>
</Row>
<?php endforeach; ?>
</Table>
</Worksheet>
</Workbook>
They're "technically" Microsoft Office XML-format[1], and not "Excel files", but users don't tend to be amused by programmers being technically right so I tend not to argue about that sort of thing. I only bring it up here in case someone wants to search for a specification on it.
[1]: https://en.wikipedia.org/wiki/Microsoft_Office_XML_formats https://en.wikipedia.org/wiki/Microsoft_Office_XML_formats
- merb 9y agothis is a spreadsheet ml2003 and not supporting it is not a big deal since this format is probably not widely used in the wild. Basically this file should need a .xml file format, but excel will read files with .xls as well (besides that this is not correct). Basically the XLS was/is a binary format and XLSX is the new spreadsheet ml 2007+ syntax that is standardised. (edit: well the good part of the xml-files were that they could be streamed. this is / was huge for data processing and exporting a la csv)
- jontro 9y agoReally nice. What is the recommended mime-type for this format? text/xml?
- geocar 9y agoI use: application/vnd.ms-excel because that's what launches Excel.
- deleted 9y ago[deleted]
- mhd 9y agoI think that's the format you get for Salesforce reports, and as it's neither the old XLS nor the zipped-xml xlsx, quite a few tools have issues importing that, not just this lib. And then we're back to the legion of CSV variants…
- gatmne 9y agoI'd argue that you must at least attempt deal with csvs before you can claim to be an excel file reader because end-users will be supplying you with whatever format their reports are in. "Be flexible in what you accept" seems especially apt here.
- flomo 9y agoSimilarly, you can also create a HTML document containing a single <table>, and Excel will open it with the right extension/mime type.
- gatmne 9y agoThis might come in handy! Does this work with libreoffice? Do you have a sample on hand that I can test?
- geocar 9y agoYes! It works with libreoffice. It also works with gnumeric. The output of the program is: https://www.dropbox.com/s/9joeudijfav3pe7/a.xls?dl=1 https://www.dropbox.com/s/9joeudijfav3pe7/a.xls?dl=1 (although it was short enough to fit in a comment, so you might just have tried running it!)
- gatmne 9y agoAgain, thank you so much for sharing this. A format that's trivial to generate in a single pass with no special packing and zipping requirements is a godsend. It supports Word documents too!
- Piskvorrr 9y agoAlso, Excel will read the old BIFF files (XLS), CSVs, SYLK, and whatnot. You are doing, in reverse, exactly that which you oppose: "unless this library can open each and every and any file that Excel can, bug-for-bug compatible, it's Wrong!" (While it is true that users will toss anything at you, it's quite pointless to expect one library to handle all of it: we used one to sniff the content, another to parse CSVs, yet another to parse XLSX, and in-house code for this obscure abomination which sadly still gets about 10% of use)
- geocar 9y ago> it's quite pointless to expect one library to handle all of it Libraries don't just come with implementation, but also a use model. Perl, for example has: Spreadsheet::XLSX Spreadsheet::ParseExcel These have very different user (programmer) interfaces -- so different that now there's a Spreadsheet::ParseXLSX which just allows programmers (and programs) used to one interface, consume the new files with very little changes. This rust library has it's own model, and if it's a good model it makes sense to implement other file formats with this model -- and indeed this is a good way to test if it's a good model. Other things like CSV are so fundamentally different from XLS/XLSX that they're going to have a different model, and so it makes sense that these require different libraries.
- deleted 9y ago[deleted]
- fleetfox 9y agoThat is not how you build XML document in PHP. One of the reasons people hate PHP because it gets abused like this.
- krapp 9y agoTrue, PHP has its own classes for building XML/HTML[0], although using it to build or parse HTML (say, from a cURL request) is a bit hairy. Proper headers should be sent as well. [0]http://php.net/manual/en/class.domdocument.php http://php.net/manual/en/class.domdocument.php