mORMot and Open Source friends
Check-in [96306d51bd]
Check-in [96306d51bd]
Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
| Comment: | {3211} - add (optional) field types hanldling during create OpenOffice spreadsheet using TSQLTable.GetODSDocument (at most for date types) |
|---|---|
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
96306d51bdb86a7bcb294500cf50c1e7 |
| User & Date: | pavel.mash 2016-12-02 16:59:18 |
|
2016-12-03
| ||
| 19:16 | {3212} new TSQLRestStorageInMemory.GetAllIDs method check-in: 19a426996b user: ab tags: trunk | |
|
2016-12-02
| ||
| 16:59 | {3211} - add (optional) field types hanldling during create OpenOffice spreadsheet using TSQLTable.GetODSDocument (at most for date types) check-in: 96306d51bd user: pavel.mash tags: trunk | |
| 14:49 | {3210} enhanced TSQLTable.GetRowValues method for better performance and optional header/trailer check-in: cbf055cda7 user: ab tags: trunk | |
Changes to SQLite3/mORMot.pas.
8139 8140 8141 8142 8143 8144 8145 8146 8147 8148 8149 8150 8151 8152 8153 ..... 25044 25045 25046 25047 25048 25049 25050 25051 25052 25053 25054 25055 25056 25057 25058 ..... 25065 25066 25067 25068 25069 25070 25071 25072 25073 25074 25075 25076 25077 25078 ..... 25081 25082 25083 25084 25085 25086 25087 25088 25089 25090 25091 25092 25093 25094 25095 25096 25097 25098 25099 25100 25101 25102 25103 25104 |
// - this format is used by ADODB.recordset, easily consummed by MS apps
// - see @http://synopse.info/forum/viewtopic.php?pid=11691#p11691
function GetMSRowSetValues: RawUTF8; overload;
/// save the table in Open Document Spreadsheet compressed format
// - this is a set of XML files compressed in a zip container
// - this method will return the raw binary buffer of the file
// - see @http://synopse.info/forum/viewtopic.php?id=2133
function GetODSDocument: RawByteString;
/// append the table content as a HTML <table> ... </table>
procedure GetHtmlTable(Dest: TTextWriter); overload;
/// save the table as a <html><body><table> </table></body></html> content
function GetHtmlTable(const Header: RawUTF8='<head><style>table,th,td'+
'{border: 1px solid black;border-collapse: collapse;}th,td{padding: 5px;'+
'font-family: sans-serif;}</style></head>'#10): RawUTF8; overload;
/// get the Field index of a FieldName
................................................................................
GetMSRowSetValues(MS,1,RowCount);
result := MS.DataString;
finally
MS.Free;
end;
end;
function TSQLTable.GetODSDocument: RawByteString;
const
ODSmimetype: RawUTF8 = 'application/vnd.oasis.opendocument.spreadsheet';
ODSContentHeader: RawUTF8 = '<office:document-content office:version="1.2" 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>';
................................................................................
'<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>';
var Zip: TZipWriteToStream;
Dest: TRawByteStringStream;
content: RawUTF8;
W: TTextWriter;
U: PPUTF8Char;
R,F: integer;
begin
Dest := TRawByteStringStream.Create;
try
Zip := TZipWriteToStream.Create(Dest);
try
Zip.AddStored('mimetype',pointer(ODSmimetype),length(ODSmimetype));
Zip.AddDeflated('styles.xml',pointer(ODSstyles),length(ODSstyles));
................................................................................
Zip.AddDeflated('META-INF/manifest.xml',pointer(ODSmanifest),length(ODSmanifest));
W := TTextWriter.CreateOwnedStream(65536);
try
W.AddShort(XMLUTF8_HEADER);
W.AddString(ODSContentHeader);
W.Add(FieldCount);
W.AddShort('" />');
U := pointer(fResults);
for R := 0 to fRowCount do begin
W.AddShort('<table:table-row>');
for F := 1 to FieldCount 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.SetText(content);
finally
W.Free;
end;
Zip.AddDeflated('content.xml',pointer(content),length(content));
|
| | > > < > > > > > > > > > > > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
8139 8140 8141 8142 8143 8144 8145 8146 8147 8148 8149 8150 8151 8152 8153 ..... 25044 25045 25046 25047 25048 25049 25050 25051 25052 25053 25054 25055 25056 25057 25058 ..... 25065 25066 25067 25068 25069 25070 25071 25072 25073 25074 25075 25076 25077 25078 25079 ..... 25082 25083 25084 25085 25086 25087 25088 25089 25090 25091 25092 25093 25094 25095 25096 25097 25098 25099 25100 25101 25102 25103 25104 25105 25106 25107 25108 25109 25110 25111 25112 25113 25114 25115 25116 25117 25118 25119 25120 25121 25122 25123 25124 25125 25126 25127 25128 25129 25130 25131 25132 25133 25134 25135 25136 25137 25138 25139 25140 25141 25142 25143 25144 25145 25146 25147 25148 25149 |
// - this format is used by ADODB.recordset, easily consummed by MS apps
// - see @http://synopse.info/forum/viewtopic.php?pid=11691#p11691
function GetMSRowSetValues: RawUTF8; overload;
/// save the table in Open Document Spreadsheet compressed format
// - this is a set of XML files compressed in a zip container
// - this method will return the raw binary buffer of the file
// - see @http://synopse.info/forum/viewtopic.php?id=2133
function GetODSDocument(withColumnTypes: boolean = false): RawByteString;
/// append the table content as a HTML <table> ... </table>
procedure GetHtmlTable(Dest: TTextWriter); overload;
/// save the table as a <html><body><table> </table></body></html> content
function GetHtmlTable(const Header: RawUTF8='<head><style>table,th,td'+
'{border: 1px solid black;border-collapse: collapse;}th,td{padding: 5px;'+
'font-family: sans-serif;}</style></head>'#10): RawUTF8; overload;
/// get the Field index of a FieldName
................................................................................
GetMSRowSetValues(MS,1,RowCount);
result := MS.DataString;
finally
MS.Free;
end;
end;
function TSQLTable.GetODSDocument(withColumnTypes: boolean): RawByteString;
const
ODSmimetype: RawUTF8 = 'application/vnd.oasis.opendocument.spreadsheet';
ODSContentHeader: RawUTF8 = '<office:document-content office:version="1.2" 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>';
................................................................................
'<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>';
var Zip: TZipWriteToStream;
Dest: TRawByteStringStream;
content: RawUTF8;
W: TTextWriter;
U: PPUTF8Char;
R,F: integer;
fieldType: TSQLDBFieldTypeDynArray;
begin
Dest := TRawByteStringStream.Create;
try
Zip := TZipWriteToStream.Create(Dest);
try
Zip.AddStored('mimetype',pointer(ODSmimetype),length(ODSmimetype));
Zip.AddDeflated('styles.xml',pointer(ODSstyles),length(ODSstyles));
................................................................................
Zip.AddDeflated('META-INF/manifest.xml',pointer(ODSmanifest),length(ODSmanifest));
W := TTextWriter.CreateOwnedStream(65536);
try
W.AddShort(XMLUTF8_HEADER);
W.AddString(ODSContentHeader);
W.Add(FieldCount);
W.AddShort('" />');
if (self<>nil) and ((FieldCount>0) or (fRowCount>0)) then begin
U := pointer(fResults);
if withColumnTypes then begin
// retrieve normalized field names and types
if length(fFieldNames)<>fFieldCount then
InitFieldNames;
if not Assigned(fFieldType) then
InitFieldTypes;
SetLength(fieldType,FieldCount);
for f := 0 to FieldCount-1 do
with fFieldType[F] do
fieldType[f] := SQLFieldTypeToDBField(ContentType,ContentTypeInfo);
end;
// write column names
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>');
// and values
for R := 1 to fRowCount do begin
W.AddShort('<table:table-row>');
if withColumnTypes then begin
for F := 0 to FieldCount-1 do begin
W.AddShort('<table:table-cell office:value-type="');
case fieldType[F] of
ftInt64, ftDouble, ftCurrency: begin
W.AddShort('float" office:value="');
W.AddXmlEscape(U^);
end;
ftDate: begin
W.AddShort('date" office:date-value="');
W.AddXmlEscape(U^);
end;
else //ftUnknown, ftNull, ftUTF8, ftBlob:
W.AddShort('string');
end;
W.AddShort('"><text:p>');
if fieldType[F] in [ftUTF8, ftBlob] then
W.AddXmlEscape(U^);
W.AddShort('</text:p></table:table-cell>');
inc(U); // points to next value
end;
end else begin
W.AddShort('<table:table-cell office:value-type="string"><text:p>');
W.AddXmlEscape(U^);
W.AddShort('</text:p></table:table-cell>');
end;
W.AddShort('</table:table-row>');
end;
end;
W.AddShort(ODSContentFooter);
W.SetText(content);
finally
W.Free;
end;
Zip.AddDeflated('content.xml',pointer(content),length(content));
|
Changes to SynopseCommit.inc.
1 |
'1.18.3210'
|
| |
1 |
'1.18.3211'
|