.ods (open document spreadsheet)

esmondb · 2014-10-30 22:53:21

CSV doesn't handle line breaks in a field very well and maybe a minimalist ods export from TSQLTable could be a useful alternative.

Based on TSQLTable.GetCSVValues below is a function that produces the content.xml spreadsheet file of a .ods zipped file. (.ods files are generally a zip file conataining mainly xml files).

Could this be added to mORMot? Using a file saved by LibreOffice as a template I've got the boiler-plate xml down to under 1k.

I'm still working on the zipping side. Could someone help here? Ideally I want to extend the function to return a complete zipped open document file. mORMot's documentation is generally great but the part on zipping is a bit terse. I'd be grateful for any help which saves me having to work it out myself. This is the file structure of the zip file needed:

mimetype
content.xml
styles.xml
meta.xml
settings.xml
META-INF/manifest.xml

I've looked at TZipWrite but this writes to disc. Is there an equivalent which returns a memory stream?

Using windows built-in zipping the function below makes a file which is read ok by Libra Office.

reference links:
http://www.forensicswiki.org/wiki/Open_Document_Format
http://what-when-how.com/how-to-build-a … ry-part-2/
http://mashupguide.net/1.0/html/ch17s03.xhtml

const //ODS boilerplate files
  ODSXMLHeader = '<?xml version="1.0" encoding="UTF-8"?>';
  //content.xml
  ODSContentHeader = '<office:document-content xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0" xmlns:text="urn:oasis:names:tc:opendocument:xmlns:text:1.0" xmlns:table="urn:oasis:names:tc:opendocument:xmlns:table:1.0"'+
  ' xmlns:meta="urn:oasis:names:tc:opendocument:xmlns:meta:1.0" ><office:body><office:spreadsheet><table:table table:name="Sheet1" ><table:table-column table:number-columns-repeated="';
  ODSContentFooter = '</table:table><table:named-expressions/></office:spreadsheet></office:body></office:document-content>';
  //styles.xml
  ODSstyles = ODSXMLHeader+'<office:document-styles xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0" office:version="1.2"></office:document-styles>';
  //meta.xml
  ODSmeta = ODSXMLHeader+'<office:document-meta xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0" office:version="1.2"></office:document-meta>';
  //settings.xml
  ODSsettings = ODSXMLHeader+'<office:document-settings xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0" office:version="1.2"><office:settings>';
  //mimetype
  ODSmimetype = ODSXMLHeader+'application/vnd.oasis.opendocument.spreadsheet';


procedure TSQLTable.GetODSContent(Dest: TStream);
var U: PPUTF8Char;
    F,R: integer;
    W: TTextWriter;
begin
  W := TTextWriter.Create(Dest,16384);
  try
    W.AddString(ODSXMLHeader);
    W.AddCR;
    W.AddString(ODSContentHeader);
    W.Add(RowCount);
    W.AddShort('" />');
    U := pointer(fResults);
    for R := 0 to RowCount do begin
      W.AddShort('<table:table-row>');
      for F := 0 to FieldCount-1 do begin
        W.AddShort('<table:table-cell office:value-type="string"><text:p>');
        W.AddXmlEscape(U^);
        W.AddShort('</text:p></table:table-cell>');
        inc(U); // points to next value
      end;
      W.AddShort('</table:table-row>');
    end;
    W.AddShort(ODSContentFooter);
  finally
    W.Flush;
    W.Free;
  end;
end;

ab · 2014-10-31 06:16:06

Just use TZipWrite and write to disk at first.
Call AddDeflated(const aZipName: TFileName; Buf: pointer; Size: integer) to add each content. Use the path in aZipName (e.g. 'META-INF/manifest.xml') to create the sub folders.

If you obtain the expected content, I will extend TZipWrite so that we would able to write into memory.
Then merge the code to mORMot.

BTW you ODSmimetype constant sounds weird.

Thanks for the feedback

esmondb · 2014-10-31 11:05:54

Thanks, I've got this working. Yes, mimetype was wrong and it was missing the manifest.xml.

Only problem now is McAfee complains of 'Potentially annoying download detected' in firefox. I guess this is just a false positive but could it be due to the http headers which are:
'Content-Type:application/vnd.oasis.opendocument.spreadsheet; name="download.ods'+#13#10+'Content-Disposition: attachment; filename="download.ods"'

It's probably not the most elegant but the code below works.

const //ODS boilerplate files
  //mimetype
  ODSmimetype = 'application/vnd.oasis.opendocument.spreadsheet';
  ODSXMLHeader = '<?xml version="1.0" encoding="UTF-8"?>';
  //content.xml
  ODSContentHeader = '<office:document-content xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0" xmlns:text="urn:oasis:names:tc:opendocument:xmlns:text:1.0" xmlns:table="urn:oasis:names:tc:opendocument:xmlns:table:1.0"'+
  ' xmlns:meta="urn:oasis:names:tc:opendocument:xmlns:meta:1.0" ><office:body><office:spreadsheet><table:table table:name="Sheet1" ><table:table-column table:number-columns-repeated="';
  ODSContentFooter = '</table:table><table:named-expressions/></office:spreadsheet></office:body></office:document-content>';
  //styles.xml
  ODSstyles = ODSXMLHeader+'<office:document-styles xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0" office:version="1.2"></office:document-styles>';
  //meta.xml
  ODSmeta = ODSXMLHeader+'<office:document-meta xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0" office:version="1.2"></office:document-meta>';
  //settings.xml
  ODSsettings = ODSXMLHeader+'<office:document-settings xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0" office:version="1.2"><office:settings>';
  //manifest.xml
  ODSmanifest = ODSXMLHeader+'<manifest:manifest xmlns:manifest="urn:oasis:names:tc:opendocument:xmlns:manifest:1.0" manifest:version="1.2"><manifest:file-entry manifest:full-path="/" manifest:version="1.2" manifest:media-type="application/vnd.oasis.opendocument.spreadsheet"/>'+
  '<manifest:file-entry manifest:full-path="meta.xml" manifest:media-type="text/xml"/><manifest:file-entry manifest:full-path="settings.xml" manifest:media-type="text/xml"/>'+
  '<manifest:file-entry manifest:full-path="content.xml" manifest:media-type="text/xml"/><manifest:file-entry manifest:full-path="styles.xml" manifest:media-type="text/xml"/></manifest:manifest>';


procedure TSQLTable.GetODSContent(Dest: TMemoryStream);
var U: PPUTF8Char;
    F,R: integer;
    W: TTextWriter;
    Z: TZipWrite;
    TempFile: array[0..MAX_PATH] of Char;
begin
  GetTempPath(SizeOf(TempFile) - 1, TempFile);
  GetTempFileName(TempFile, '~', 0, TempFile);
  Z := TZipWrite.Create(TempFile);
  Z.AddStored('mimetype', PAnsiChar(ODSmimetype), length(ODSmimetype));
  Z.AddDeflated('styles.xml', PAnsiChar(ODSstyles), length(ODSstyles));
  Z.AddDeflated('meta.xml', PAnsiChar(ODSmeta), length(ODSmeta));
  Z.AddDeflated('settings.xml', PAnsiChar(ODSsettings), length(ODSsettings));
  Z.AddDeflated('META-INF/manifest.xml', PAnsiChar(ODSmanifest), length(ODSmanifest));
  W := TTextWriter.Create(Dest,16384);
  try
    W.AddString(ODSXMLHeader);
    W.AddCR;
    W.AddString(ODSContentHeader);
    W.Add(RowCount);
    W.AddShort('" />');
    U := pointer(fResults);
    for R := 0 to RowCount do begin
      W.AddShort('<table:table-row>');
      for F := 0 to FieldCount-1 do begin
        W.AddShort('<table:table-cell office:value-type="string"><text:p>');
        W.AddXmlEscape(U^);
        W.AddShort('</text:p></table:table-cell>');
        inc(U); // points to next value
      end;
      W.AddShort('</table:table-row>');
    end;
    W.AddShort(ODSContentFooter);
    W.Flush;
    Z.AddDeflated('content.xml', PAnsiChar(W.Text), length(W.Text));
    Dest.LoadFromFile(TempFile);
  finally
    Z.Free;
    W.Free;
    DeleteFile(TempFile);
  end;
end;

ab · 2014-10-31 12:24:07

Should be implemented by http://synopse.info/fossil/info/fb172b784f
Thanks for sharing!

When I open the .ods file, Microsoft Office complains that it should be repaired.
But I see no data loss.
Do you have any idea?

Feedback is welcome!

esmondb · 2014-11-01 13:41:20

Thanks a lot for implementing this.

I've found a few bugs which should be fixed by changing the lines below in mORMot.pas

I haven't got Microsoft Office to test it but it now passes this ODF validator:
https://odf-validator.rhcloud.com/

17590 ODSContentHeader: RawUTF8 = '<office:document-content office:version="1.2" xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0"'+

17597 ODSsettings: RawUTF8 = XMLUTF8_HEADER+'<office:document-settings xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0" office:version="1.2"></office:document-settings>';

17622 W.Add(FieldCount);

ab · 2014-11-03 07:51:59

Should be fixed by http://synopse.info/fossil/info/8f12708347

Thanks for the patch!

mpv · 2016-12-02 17:02:14

I'm improve a little ODF export by adding (optional) data types for cells. We need it for correctly handle a Date and Numbers. See [96306d51bd]

mORMot Open Source

#1 2014-10-30 22:53:21

.ods (open document spreadsheet)

#2 2014-10-31 06:16:06

Re: .ods (open document spreadsheet)

#3 2014-10-31 11:05:54

Re: .ods (open document spreadsheet)

#4 2014-10-31 12:24:07

Re: .ods (open document spreadsheet)

#5 2014-11-01 13:41:20

Re: .ods (open document spreadsheet)

#6 2014-11-03 07:51:59

Re: .ods (open document spreadsheet)

#7 2016-12-02 17:02:14

Re: .ods (open document spreadsheet)

Board footer