001: /*
002: * XmlRowDataConverter.java
003: *
004: * This file is part of SQL Workbench/J, http://www.sql-workbench.net
005: *
006: * Copyright 2002-2008, Thomas Kellerer
007: * No part of this code maybe reused without the permission of the author
008: *
009: * To contact the author please send an email to: support@sql-workbench.net
010: *
011: */
012: package workbench.db.exporter;
013:
014: import java.io.IOException;
015: import java.io.Writer;
016: import java.sql.Types;
017: import java.text.SimpleDateFormat;
018: import java.util.Date;
019: import java.util.regex.Matcher;
020: import workbench.log.LogMgr;
021: import workbench.storage.RowData;
022: import workbench.util.FileUtil;
023: import workbench.util.SqlUtil;
024: import workbench.util.StrBuffer;
025: import workbench.util.StringUtil;
026: import workbench.util.ZipOutputFactory;
027:
028: /**
029: * Convert row data to OpenDocument Spreadsheet format (OpenOffice).
030: *
031: * @author support@sql-workbench.net
032: */
033: public class OdsRowDataConverter extends RowDataConverter {
034: private Writer content;
035: private SimpleDateFormat tFormat = new SimpleDateFormat("HH:mm:ss");
036: private SimpleDateFormat dtFormat = new SimpleDateFormat(
037: "yyyy-MM-dd");
038: private SimpleDateFormat tsFormat = new SimpleDateFormat(
039: "yyyy-MM-dd'T'HH:mm:ss");
040:
041: public OdsRowDataConverter() {
042: super ();
043: }
044:
045: public StrBuffer getStart() {
046: Writer out = null;
047: try {
048: if (this .factory != null) {
049: this .factory.done();
050: }
051: this .factory = new ZipOutputFactory(getOutputFile());
052: out = factory
053: .createWriter("META-INF/manifest.xml", "UTF-8");
054:
055: out.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n");
056: out
057: .write("<manifest:manifest xmlns:manifest=\"urn:oasis:names:tc:opendocument:xmlns:manifest:1.0\">\n");
058: out
059: .write(" <manifest:file-entry manifest:media-type=\"application/vnd.oasis.opendocument.spreadsheet\" manifest:full-path=\"/\"/>\n");
060: out
061: .write(" <manifest:file-entry manifest:media-type=\"text/xml\" manifest:full-path=\"content.xml\"/>\n");
062: out
063: .write(" <manifest:file-entry manifest:media-type=\"text/xml\" manifest:full-path=\"meta.xml\"/>\n");
064: out.write("</manifest:manifest>\n");
065: out.close();
066:
067: writeMeta();
068:
069: out = factory.createWriter("mimetype", "UTF-8");
070: out.write("application/vnd.oasis.opendocument.spreadsheet");
071: out.close();
072: this .content = factory.createWriter("content.xml", "UTF-8");
073:
074: content
075: .write("<?xml version=\"1.0\" encoding=\"UTF-8\"?> \n");
076: content
077: .write("<office:document-content xmlns:office=\"urn:oasis:names:tc:opendocument:xmlns:office:1.0\" xmlns:style=\"urn:oasis:names:tc:opendocument:xmlns:style: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:draw=\"urn:oasis:names:tc:opendocument:xmlns:drawing:1.0\" xmlns:fo=\"urn:oasis:names:tc:opendocument:xmlns:xsl-fo-compatible:1.0\" xmlns:xlink=\"http://www.w3.org/1999/xlink\" xmlns:dc=\"http://purl.org/dc/elements/1.1/\" xmlns:meta=\"urn:oasis:names:tc:opendocument:xmlns:meta:1.0\" xmlns:number=\"urn:oasis:names:tc:opendocument:xmlns:datastyle:1.0\" xmlns:presentation=\"urn:oasis:names:tc:opendocument:xmlns:presentation:1.0\" xmlns:svg=\"urn:oasis:names:tc:opendocument:xmlns:svg-compatible:1.0\" xmlns:chart=\"urn:oasis:names:tc:opendocument:xmlns:chart:1.0\" xmlns:dr3d=\"urn:oasis:names:tc:opendocument:xmlns:dr3d:1.0\" xmlns:math=\"http://www.w3.org/1998/Math/MathML\" xmlns:form=\"urn:oasis:names:tc:opendocument:xmlns:form:1.0\" xmlns:script=\"urn:oasis:names:tc:opendocument:xmlns:script:1.0\" xmlns:ooo=\"http://openoffice.org/2004/office\" xmlns:ooow=\"http://openoffice.org/2004/writer\" xmlns:oooc=\"http://openoffice.org/2004/calc\" xmlns:dom=\"http://www.w3.org/2001/xml-events\" xmlns:xforms=\"http://www.w3.org/2002/xforms\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" office:version=\"1.0\"> \n");
078:
079: writeInlineStyles();
080:
081: content.write("<office:body>\n");
082: content.write("<office:spreadsheet> \n");
083: content.write("<table:table table:name=\""
084: + getPageTitle("Export")
085: + "\" table:style-name=\"ta1\">\n\n");
086:
087: int colCount = this .metaData.getColumnCount();
088: for (int i = 0; i < colCount; i++) {
089: content
090: .write("<table:table-column table:style-name=\"co"
091: + (i + 1)
092: + "\" table:default-cell-style-name=\"Default\"/>\n");
093: }
094:
095: if (writeHeader) {
096: content.write("<table:table-header-rows>\n");
097: content
098: .write(" <table:table-row table:style-name=\"ro1\">\n");
099:
100: for (int i = 0; i < colCount; i++) {
101: if (!this .includeColumnInExport(i))
102: continue;
103:
104: String colname = this .metaData.getColumnName(i);
105:
106: content
107: .write(" <table:table-cell table:style-name=\"ce1\" office:value-type=\"string\">\n");
108: content.write(" <text:p>");
109: content.write(colname);
110: content.write("</text:p>\n");
111: content.write(" </table:table-cell>\n");
112: }
113: content.write(" </table:table-row>\n");
114: content.write("</table:table-header-rows>\n\n");
115: }
116: } catch (IOException ex) {
117: LogMgr.logError("OdsRowDataConverter.getStart()",
118: "Error creating archive!", ex);
119: }
120:
121: return null;
122: }
123:
124: private void writeMeta() {
125: Writer out = null;
126: try {
127: out = factory.createWriter("meta.xml", "UTF-8");
128: out.write("<?xml version=\"1.0\" encoding=\"utf-8\"?>\n");
129: out
130: .write("<office:document-meta xmlns:office=\"urn:oasis:names:tc:opendocument:xmlns:office:1.0\" xmlns:xlink=\"http://www.w3.org/1999/xlink\" xmlns:dc=\"http://purl.org/dc/elements/1.1/\" xmlns:meta=\"urn:oasis:names:tc:opendocument:xmlns:meta:1.0\" xmlns:ooo=\"http://openoffice.org/2004/office\" office:version=\"1.0\">\n");
131: out.write("<office:meta>\n");
132: out
133: .write("<meta:generator>SQL Workbench/J</meta:generator>\n");
134: out.write("<dc:title>SQL Workbench/J Export</dc:title>\n");
135: String s = null;
136: if (this .generatingSql != null) {
137: Matcher m = StringUtil.PATTERN_CRLF
138: .matcher(generatingSql);
139: s = m.replaceAll(" ");
140: } else {
141: s = "SELECT * FROM "
142: + metaData.getUpdateTable().getTableExpression(
143: originalConnection);
144: }
145: out.write("<dc:description>");
146: out.write(s);
147: out.write("</dc:description>");
148: out
149: .write("<meta:initial-creator>SQL Workbench/J</meta:initial-creator>\n");
150: out.write("<meta:creation-date>");
151: out.write(tsFormat.format(new Date()));
152: out.write("</meta:creation-date>\n");
153: out.write("</office:meta>\n");
154: out.write("</office:document-meta>\n");
155: } catch (Exception e) {
156:
157: } finally {
158: FileUtil.closeQuitely(out);
159: }
160: }
161:
162: private void writeStyles() {
163: Writer out = null;
164: try {
165: out = factory.createWriter("styles.xml", "UTF-8");
166: out.write("<?xml version=\"1.0\" encoding=\"utf-8\"?>\n");
167: } catch (Exception e) {
168:
169: } finally {
170: FileUtil.closeQuitely(out);
171: }
172: }
173:
174: private void writeInlineStyles() throws IOException {
175: content.write("<office:automatic-styles> \n");
176: for (int i = 0; i < metaData.getColumnCount(); i++) {
177: //int size = metaData.getColumnSize(i) * 2;
178: //style:column-width=\"" + (size)+ "pt\"
179: content.write("<style:style style:name=\"co" + (i + 1)
180: + "\" style:family=\"table-column\"> \n");
181: content
182: .write(" <style:table-column-properties style:use-optimal-column-width=\"true\"/> \n");
183: content.write("</style:style> \n");
184: }
185: String styles = "<style:style style:name=\"ro1\" style:family=\"table-row\"> \n"
186: + " <style:table-row-properties fo:break-before=\"auto\" style:use-optimal-row-height=\"true\"/> \n"
187: + "</style:style> \n"
188: + "<style:style style:name=\"ta1\" style:family=\"table\" style:master-page-name=\"Default\"> \n"
189: + " <style:table-properties table:display=\"true\" style:writing-mode=\"lr-tb\"/> \n"
190: + "</style:style> \n"
191: + "<style:style style:name=\"ce1\" style:family=\"table-cell\" style:parent-style-name=\"Default\"> \n"
192: + " <style:text-properties fo:font-weight=\"bold\" style:font-weight-asian=\"bold\" style:font-weight-complex=\"bold\"/> \n"
193: + "</style:style> \n" + "</office:automatic-styles>";
194: content.write(styles);
195: }
196:
197: public StrBuffer getEnd(long totalRows) {
198: try {
199: content.write("</table:table>\n");
200: content.write("</office:spreadsheet> \n");
201: content.write("</office:body>\n");
202: content.write("</office:document-content>\n");
203: content.close();
204: factory.done();
205: factory = null;
206: } catch (Exception e) {
207: // ignore
208: }
209: return null;
210: }
211:
212: public StrBuffer convertRowData(RowData row, long rowIndex) {
213: int colCount = row.getColumnCount();
214: StrBuffer xml = new StrBuffer(colCount * 50);
215: xml.append("<table:table-row>\n");
216: for (int i = 0; i < colCount; i++) {
217: if (!this .includeColumnInExport(i))
218: continue;
219: Object o = row.getValue(i);
220: if (o == null) {
221: xml.append("<table:table-cell />");
222: continue;
223: }
224: xml.append("<table:table-cell ");
225: xml.append(getCellAttribs(o, i));
226: xml.append(">\n");
227:
228: String value = getValueAsFormattedString(row, i);
229: if (SqlUtil.isCharacterType(metaData.getColumnType(i))) {
230: String[] lines = value.split(StringUtil.REGEX_CRLF);
231: for (String line : lines) {
232: xml.append("<text:p>");
233: writeEscapedXML(xml, line, true);
234: xml.append("</text:p>\n");
235: }
236: } else {
237: xml.append("<text:p>");
238: xml.append(value);
239: xml.append("</text:p>\n");
240: }
241: xml.append("</table:table-cell>\n");
242: }
243: xml.append("</table:table-row>\n\n");
244: try {
245: xml.writeTo(content);
246: } catch (IOException e) {
247: LogMgr.logError("OdsRowDataConverter.convertRowData()",
248: "Error writing row " + rowIndex, e);
249: }
250: return null;
251: }
252:
253: private StringBuilder getCellAttribs(Object data, int column) {
254: StringBuilder attr = new StringBuilder("office:value-type=");
255: int type = metaData.getColumnType(column);
256:
257: if (SqlUtil.isNumberType(type)) {
258: attr.append("\"float\" ");
259: attr.append(" office:value=\"" + data.toString() + "\"");
260: } else if (type == Types.DATE) {
261: attr.append("\"date\" ");
262: attr.append(" office:date-value=\"");
263: if (data != null && data instanceof Date) {
264: Date d = (Date) data;
265: attr.append(dtFormat.format(d));
266: }
267: attr.append("\"");
268: } else if (type == Types.TIMESTAMP) {
269: attr.append("\"date\" ");
270: attr.append(" office:date-value=\"");
271: if (data != null && data instanceof Date) {
272: Date d = (Date) data;
273: attr.append(tsFormat.format(d));
274: }
275: attr.append("\"");
276: } else if (type == Types.TIME) {
277: attr.append("\"date\" ");
278: attr.append(" office:time-value=\"");
279: if (data != null && data instanceof Date) {
280: Date d = (Date) data;
281: attr.append(tFormat.format(d));
282: }
283: attr.append("\"");
284: } else {
285: attr.append("\"string\"");
286: }
287: return attr;
288: }
289:
290: }
|