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

esmondb
Member
From: London
Registered: 2010-07-20
Posts: 299

.ods (open document spreadsheet)

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;

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,131
Website

Re: .ods (open document spreadsheet)

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

Offline

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

esmondb
Member
From: London
Registered: 2010-07-20
Posts: 299

Re: .ods (open document spreadsheet)

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;

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,131
Website

Re: .ods (open document spreadsheet)

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!

Offline

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

esmondb
Member
From: London
Registered: 2010-07-20
Posts: 299

Re: .ods (open document spreadsheet)

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);

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,131
Website

Re: .ods (open document spreadsheet)

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

Thanks for the patch!

Offline

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

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,527
Website

Re: .ods (open document spreadsheet)

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]

Offline

Board footer

Powered by FluxBB