|
25 | 25 |
|
26 | 26 | import java.io.*; |
27 | 27 | import java.lang.reflect.*; |
| 28 | +import java.nio.charset.Charset; |
28 | 29 | import java.sql.ResultSet; |
29 | 30 | import java.sql.ResultSetMetaData; |
30 | 31 | import java.sql.SQLException; |
|
34 | 35 | import java.util.concurrent.Executors; |
35 | 36 | import java.util.zip.ZipEntry; |
36 | 37 | import java.util.zip.ZipInputStream; |
| 38 | +import java.util.zip.ZipOutputStream; |
37 | 39 |
|
38 | 40 | import javax.xml.parsers.ParserConfigurationException; |
39 | 41 |
|
@@ -292,7 +294,7 @@ protected String checkOptions(File file, String options) throws IOException { |
292 | 294 |
|
293 | 295 |
|
294 | 296 | static final String[] loadExtensions = { "csv", "tsv", "ods", "bin" }; |
295 | | - static final String[] saveExtensions = { "csv", "tsv", "html", "bin" }; |
| 297 | + static final String[] saveExtensions = { "csv", "tsv", "ods", "bin", "html" }; |
296 | 298 |
|
297 | 299 | static public String extensionOptions(boolean loading, String filename, String options) { |
298 | 300 | String extension = PApplet.checkExtension(filename); |
@@ -360,7 +362,7 @@ protected void parse(InputStream input, String options) throws IOException { |
360 | 362 | loadBinary(input); |
361 | 363 |
|
362 | 364 | } else if (extension.equals("ods")) { |
363 | | - odsParse(input, worksheet); |
| 365 | + odsParse(input, worksheet, header); |
364 | 366 |
|
365 | 367 | } else { |
366 | 368 | BufferedReader reader = PApplet.createReader(input); |
@@ -615,7 +617,7 @@ private InputStream odsFindContentXML(InputStream input) { |
615 | 617 | } |
616 | 618 |
|
617 | 619 |
|
618 | | - protected void odsParse(InputStream input, String worksheet) { |
| 620 | + protected void odsParse(InputStream input, String worksheet, boolean header) { |
619 | 621 | try { |
620 | 622 | InputStream contentStream = odsFindContentXML(input); |
621 | 623 | XML xml = new XML(contentStream); |
@@ -631,7 +633,7 @@ protected void odsParse(InputStream input, String worksheet) { |
631 | 633 | for (XML sheet : sheets) { |
632 | 634 | // System.out.println(sheet.getAttribute("table:name")); |
633 | 635 | if (worksheet == null || worksheet.equals(sheet.getString("table:name"))) { |
634 | | - odsParseSheet(sheet); |
| 636 | + odsParseSheet(sheet, header); |
635 | 637 | found = true; |
636 | 638 | if (worksheet == null) { |
637 | 639 | break; // only read the first sheet |
@@ -662,7 +664,7 @@ protected void odsParse(InputStream input, String worksheet) { |
662 | 664 | * Parses a single sheet of XML from this file. |
663 | 665 | * @param The XML object for a single worksheet from the ODS file |
664 | 666 | */ |
665 | | - private void odsParseSheet(XML sheet) { |
| 667 | + private void odsParseSheet(XML sheet, boolean header) { |
666 | 668 | // Extra <p> or <a> tags inside the text tag for the cell will be stripped. |
667 | 669 | // Different from showing formulas, and not quite the same as 'save as |
668 | 670 | // displayed' option when saving from inside OpenOffice. Only time we |
@@ -759,13 +761,19 @@ private void odsParseSheet(XML sheet) { |
759 | 761 | } |
760 | 762 | } |
761 | 763 | } |
762 | | - if (rowNotNull && rowRepeat > 1) { |
763 | | - String[] rowStrings = getStringRow(rowIndex); |
764 | | - for (int r = 1; r < rowRepeat; r++) { |
765 | | - addRow(rowStrings); |
| 764 | + if (header) { |
| 765 | + removeTitleRow(); // efficient enough on the first row |
| 766 | + header = false; // avoid infinite loop |
| 767 | + |
| 768 | + } else { |
| 769 | + if (rowNotNull && rowRepeat > 1) { |
| 770 | + String[] rowStrings = getStringRow(rowIndex); |
| 771 | + for (int r = 1; r < rowRepeat; r++) { |
| 772 | + addRow(rowStrings); |
| 773 | + } |
766 | 774 | } |
| 775 | + rowIndex += rowRepeat; |
767 | 776 | } |
768 | | - rowIndex += rowRepeat; |
769 | 777 | } |
770 | 778 | } |
771 | 779 |
|
@@ -971,6 +979,13 @@ public boolean save(OutputStream output, String options) { |
971 | 979 | writeCSV(writer); |
972 | 980 | } else if (extension.equals("tsv")) { |
973 | 981 | writeTSV(writer); |
| 982 | + } else if (extension.equals("ods")) { |
| 983 | + try { |
| 984 | + saveODS(output); |
| 985 | + } catch (IOException e) { |
| 986 | + e.printStackTrace(); |
| 987 | + return false; |
| 988 | + } |
974 | 989 | } else if (extension.equals("html")) { |
975 | 990 | writeHTML(writer); |
976 | 991 | } else if (extension.equals("bin")) { |
@@ -1140,6 +1155,207 @@ protected void writeEntryHTML(PrintWriter writer, String entry) { |
1140 | 1155 | } |
1141 | 1156 |
|
1142 | 1157 |
|
| 1158 | + protected void saveODS(OutputStream os) throws IOException { |
| 1159 | + ZipOutputStream zos = new ZipOutputStream(os); |
| 1160 | + |
| 1161 | + final String xmlHeader = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>"; |
| 1162 | + |
| 1163 | + ZipEntry entry = new ZipEntry("META-INF/manifest.xml"); |
| 1164 | + String[] lines = new String[] { |
| 1165 | + xmlHeader, |
| 1166 | + "<manifest:manifest xmlns:manifest=\"urn:oasis:names:tc:opendocument:xmlns:manifest:1.0\">", |
| 1167 | + " <manifest:file-entry manifest:media-type=\"application/vnd.oasis.opendocument.spreadsheet\" manifest:version=\"1.2\" manifest:full-path=\"/\"/>", |
| 1168 | + " <manifest:file-entry manifest:media-type=\"text/xml\" manifest:full-path=\"content.xml\"/>", |
| 1169 | + " <manifest:file-entry manifest:media-type=\"text/xml\" manifest:full-path=\"styles.xml\"/>", |
| 1170 | + " <manifest:file-entry manifest:media-type=\"text/xml\" manifest:full-path=\"meta.xml\"/>", |
| 1171 | + " <manifest:file-entry manifest:media-type=\"text/xml\" manifest:full-path=\"settings.xml\"/>", |
| 1172 | + "</manifest:manifest>" |
| 1173 | + }; |
| 1174 | + zos.putNextEntry(entry); |
| 1175 | + zos.write(PApplet.join(lines, "\n").getBytes()); |
| 1176 | + zos.closeEntry(); |
| 1177 | + |
| 1178 | + /* |
| 1179 | + entry = new ZipEntry("meta.xml"); |
| 1180 | + lines = new String[] { |
| 1181 | + xmlHeader, |
| 1182 | + "<office:document-meta office:version=\"1.0\"" + |
| 1183 | + " xmlns:office=\"urn:oasis:names:tc:opendocument:xmlns:office:1.0\" />" |
| 1184 | + }; |
| 1185 | + zos.putNextEntry(entry); |
| 1186 | + zos.write(PApplet.join(lines, "\n").getBytes()); |
| 1187 | + zos.closeEntry(); |
| 1188 | +
|
| 1189 | + entry = new ZipEntry("meta.xml"); |
| 1190 | + lines = new String[] { |
| 1191 | + xmlHeader, |
| 1192 | + "<office:document-settings office:version=\"1.0\"" + |
| 1193 | + " xmlns:config=\"urn:oasis:names:tc:opendocument:xmlns:config:1.0\"" + |
| 1194 | + " xmlns:office=\"urn:oasis:names:tc:opendocument:xmlns:office:1.0\"" + |
| 1195 | + " xmlns:ooo=\"http://openoffice.org/2004/office\"" + |
| 1196 | + " xmlns:xlink=\"http://www.w3.org/1999/xlink\" />" |
| 1197 | + }; |
| 1198 | + zos.putNextEntry(entry); |
| 1199 | + zos.write(PApplet.join(lines, "\n").getBytes()); |
| 1200 | + zos.closeEntry(); |
| 1201 | +
|
| 1202 | + entry = new ZipEntry("settings.xml"); |
| 1203 | + lines = new String[] { |
| 1204 | + xmlHeader, |
| 1205 | + "<office:document-settings office:version=\"1.0\"" + |
| 1206 | + " xmlns:config=\"urn:oasis:names:tc:opendocument:xmlns:config:1.0\"" + |
| 1207 | + " xmlns:office=\"urn:oasis:names:tc:opendocument:xmlns:office:1.0\"" + |
| 1208 | + " xmlns:ooo=\"http://openoffice.org/2004/office\"" + |
| 1209 | + " xmlns:xlink=\"http://www.w3.org/1999/xlink\" />" |
| 1210 | + }; |
| 1211 | + zos.putNextEntry(entry); |
| 1212 | + zos.write(PApplet.join(lines, "\n").getBytes()); |
| 1213 | + zos.closeEntry(); |
| 1214 | +
|
| 1215 | + entry = new ZipEntry("styles.xml"); |
| 1216 | + lines = new String[] { |
| 1217 | + xmlHeader, |
| 1218 | + "<office:document-styles office:version=\"1.0\"" + |
| 1219 | + " xmlns:office=\"urn:oasis:names:tc:opendocument:xmlns:office:1.0\" />" |
| 1220 | + }; |
| 1221 | + zos.putNextEntry(entry); |
| 1222 | + zos.write(PApplet.join(lines, "\n").getBytes()); |
| 1223 | + zos.closeEntry(); |
| 1224 | + */ |
| 1225 | + |
| 1226 | + final String[] dummyFiles = new String[] { |
| 1227 | + "meta.xml", "settings.xml", "styles.xml" |
| 1228 | + }; |
| 1229 | + lines = new String[] { |
| 1230 | + xmlHeader, |
| 1231 | + "<office:document-meta office:version=\"1.0\"" + |
| 1232 | + " xmlns:office=\"urn:oasis:names:tc:opendocument:xmlns:office:1.0\" />" |
| 1233 | + }; |
| 1234 | + byte[] dummyBytes = PApplet.join(lines, "\n").getBytes(); |
| 1235 | + for (String filename : dummyFiles) { |
| 1236 | + entry = new ZipEntry(filename); |
| 1237 | + zos.putNextEntry(entry); |
| 1238 | + zos.write(dummyBytes); |
| 1239 | + zos.closeEntry(); |
| 1240 | + } |
| 1241 | + |
| 1242 | + // |
| 1243 | + |
| 1244 | + entry = new ZipEntry("mimetype"); |
| 1245 | + zos.putNextEntry(entry); |
| 1246 | + zos.write("application/vnd.oasis.opendocument.spreadsheet".getBytes()); |
| 1247 | + zos.closeEntry(); |
| 1248 | + |
| 1249 | + // |
| 1250 | + |
| 1251 | + entry = new ZipEntry("content.xml"); |
| 1252 | + zos.putNextEntry(entry); |
| 1253 | + //lines = new String[] { |
| 1254 | + writeUTF(zos, new String[] { |
| 1255 | + xmlHeader, |
| 1256 | + "<office:document-content" + |
| 1257 | + " xmlns:office=\"urn:oasis:names:tc:opendocument:xmlns:office:1.0\"" + |
| 1258 | + " xmlns:text=\"urn:oasis:names:tc:opendocument:xmlns:text:1.0\"" + |
| 1259 | + " xmlns:table=\"urn:oasis:names:tc:opendocument:xmlns:table:1.0\"" + |
| 1260 | + " office:version=\"1.2\">", |
| 1261 | + " <office:body>", |
| 1262 | + " <office:spreadsheet>", |
| 1263 | + " <table:table table:name=\"Sheet1\" table:print=\"false\">" |
| 1264 | + }); |
| 1265 | + //zos.write(PApplet.join(lines, "\n").getBytes()); |
| 1266 | + |
| 1267 | + byte[] rowStart = " <table:table-row>\n".getBytes(); |
| 1268 | + byte[] rowStop = " </table:table-row>\n".getBytes(); |
| 1269 | + |
| 1270 | + if (hasColumnTitles()) { |
| 1271 | + zos.write(rowStart); |
| 1272 | + for (int i = 0; i < getColumnCount(); i++) { |
| 1273 | + saveStringODS(zos, columnTitles[i]); |
| 1274 | + } |
| 1275 | + zos.write(rowStop); |
| 1276 | + } |
| 1277 | + |
| 1278 | + for (TableRow row : rows()) { |
| 1279 | + zos.write(rowStart); |
| 1280 | + for (int i = 0; i < getColumnCount(); i++) { |
| 1281 | + if (columnTypes[i] == STRING || columnTypes[i] == CATEGORY) { |
| 1282 | + saveStringODS(zos, row.getString(i)); |
| 1283 | + } else { |
| 1284 | + saveNumberODS(zos, row.getString(i)); |
| 1285 | + } |
| 1286 | + } |
| 1287 | + zos.write(rowStop); |
| 1288 | + } |
| 1289 | + |
| 1290 | + //lines = new String[] { |
| 1291 | + writeUTF(zos, new String[] { |
| 1292 | + " </table:table>", |
| 1293 | + " </office:spreadsheet>", |
| 1294 | + " </office:body>", |
| 1295 | + "</office:document-content>" |
| 1296 | + }); |
| 1297 | + //zos.write(PApplet.join(lines, "\n").getBytes()); |
| 1298 | + zos.closeEntry(); |
| 1299 | + |
| 1300 | + zos.flush(); |
| 1301 | + zos.close(); |
| 1302 | + } |
| 1303 | + |
| 1304 | + |
| 1305 | + void saveStringODS(OutputStream output, String text) throws IOException { |
| 1306 | + // At this point, I should have just used the XML library. But this does |
| 1307 | + // save us from having to create the entire document in memory again before |
| 1308 | + // writing to the file. So while it's dorky, the outcome is still useful. |
| 1309 | + StringBuilder sanitized = new StringBuilder(); |
| 1310 | + if (text != null) { |
| 1311 | + char[] array = text.toCharArray(); |
| 1312 | + for (char c : array) { |
| 1313 | + if (c == '&') { |
| 1314 | + sanitized.append("&"); |
| 1315 | + } else if (c == '\'') { |
| 1316 | + sanitized.append("'"); |
| 1317 | + } else if (c == '"') { |
| 1318 | + sanitized.append("""); |
| 1319 | + } else if (c == '<') { |
| 1320 | + sanitized.append("<"); |
| 1321 | + } else if (c == '>') { |
| 1322 | + sanitized.append("&rt;"); |
| 1323 | + } else if (c < 32 || c > 127) { |
| 1324 | + sanitized.append("&#" + ((int) c) + ";"); |
| 1325 | + } else { |
| 1326 | + sanitized.append(c); |
| 1327 | + } |
| 1328 | + } |
| 1329 | + } |
| 1330 | + |
| 1331 | + writeUTF(output, |
| 1332 | + " <table:table-cell office:value-type=\"string\">", |
| 1333 | + " <text:p>" + sanitized + "</text:p>", |
| 1334 | + " </table:table-cell>"); |
| 1335 | + } |
| 1336 | + |
| 1337 | + |
| 1338 | + void saveNumberODS(OutputStream output, String text) throws IOException { |
| 1339 | + writeUTF(output, |
| 1340 | + " <table:table-cell office:value-type=\"float\" office:value=\"" + text + "\">", |
| 1341 | + " <text:p>" + text + "</text:p>", |
| 1342 | + " </table:table-cell>"); |
| 1343 | + } |
| 1344 | + |
| 1345 | + |
| 1346 | + static Charset utf8; |
| 1347 | + |
| 1348 | + static void writeUTF(OutputStream output, String... lines) throws IOException { |
| 1349 | + if (utf8 == null) { |
| 1350 | + utf8 = Charset.forName("UTF-8"); |
| 1351 | + } |
| 1352 | + for (String str : lines) { |
| 1353 | + output.write(str.getBytes(utf8)); |
| 1354 | + output.write('\n'); |
| 1355 | + } |
| 1356 | + } |
| 1357 | + |
| 1358 | + |
1143 | 1359 | protected void saveBinary(OutputStream os) throws IOException { |
1144 | 1360 | DataOutputStream output = new DataOutputStream(new BufferedOutputStream(os)); |
1145 | 1361 | output.writeInt(0x9007AB1E); // version |
@@ -1904,6 +2120,15 @@ public TableRow addRow(Object[] columnData) { |
1904 | 2120 | } |
1905 | 2121 |
|
1906 | 2122 |
|
| 2123 | + public void addRows(Table source) { |
| 2124 | + int index = getRowCount(); |
| 2125 | + setRowCount(index + source.getRowCount()); |
| 2126 | + for (TableRow row : source.rows()) { |
| 2127 | + setRow(index++, row); |
| 2128 | + } |
| 2129 | + } |
| 2130 | + |
| 2131 | + |
1907 | 2132 | public void insertRow(int insert, Object[] columnData) { |
1908 | 2133 | for (int col = 0; col < columns.length; col++) { |
1909 | 2134 | switch (columnTypes[col]) { |
@@ -3435,9 +3660,18 @@ public void replace(String orig, String replacement) { |
3435 | 3660 | public void replace(String orig, String replacement, int col) { |
3436 | 3661 | if (columnTypes[col] == STRING) { |
3437 | 3662 | String[] stringData = (String[]) columns[col]; |
3438 | | - for (int row = 0; row < rowCount; row++) { |
3439 | | - if (stringData[row].equals(orig)) { |
3440 | | - stringData[row] = replacement; |
| 3663 | + |
| 3664 | + if (orig != null) { |
| 3665 | + for (int row = 0; row < rowCount; row++) { |
| 3666 | + if (orig.equals(stringData[row])) { |
| 3667 | + stringData[row] = replacement; |
| 3668 | + } |
| 3669 | + } |
| 3670 | + } else { // null is a special case (and faster anyway) |
| 3671 | + for (int row = 0; row < rowCount; row++) { |
| 3672 | + if (stringData[row] == null) { |
| 3673 | + stringData[row] = replacement; |
| 3674 | + } |
3441 | 3675 | } |
3442 | 3676 | } |
3443 | 3677 | } |
|