001: package net.sourceforge.squirrel_sql.fw.gui.action;
002:
003: import java.io.BufferedWriter;
004: import java.io.File;
005: import java.io.FileOutputStream;
006: import java.io.FileWriter;
007: import java.io.IOException;
008: import java.io.OutputStream;
009: import java.io.OutputStreamWriter;
010: import java.sql.Types;
011: import java.util.Calendar;
012:
013: import javax.swing.JOptionPane;
014: import javax.swing.JTable;
015:
016: import jxl.Workbook;
017: import jxl.write.WritableCell;
018: import jxl.write.WritableSheet;
019: import jxl.write.WritableWorkbook;
020: import jxl.write.WriteException;
021: import net.sourceforge.squirrel_sql.fw.datasetviewer.ColumnDisplayDefinition;
022: import net.sourceforge.squirrel_sql.fw.datasetviewer.ExtTableColumn;
023: import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.CellComponentFactory;
024: import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.ClobDescriptor;
025: import net.sourceforge.squirrel_sql.fw.gui.GUIUtils;
026: import net.sourceforge.squirrel_sql.fw.util.StringManager;
027: import net.sourceforge.squirrel_sql.fw.util.StringManagerFactory;
028: import net.sourceforge.squirrel_sql.fw.util.log.ILogger;
029: import net.sourceforge.squirrel_sql.fw.util.log.LoggerController;
030:
031: public class TableExportCsvCommand {
032: private static final StringManager s_stringMgr = StringManagerFactory
033: .getStringManager(TableExportCsvCommand.class);
034:
035: private static ILogger s_log = LoggerController
036: .createLogger(TableExportCsvCommand.class);
037:
038: private JTable _table;
039:
040: static interface i18n {
041: //i18n[TableExportCsvCommand.missingClobDataMsg=Found Clob placeholder
042: //({0}) amongst data to be exported. Continue exporting cell data?]
043: String missingClobDataMsg = s_stringMgr.getString(
044: "TableExportCsvCommand.missingClobDataMsg",
045: ClobDescriptor.i18n.CLOB_LABEL);
046: }
047:
048: public TableExportCsvCommand(JTable table) {
049: _table = table;
050: }
051:
052: public void execute() {
053: TableExportCsvController ctrl = new TableExportCsvController();
054:
055: if (false == ctrl.isOK()) {
056: return;
057: }
058:
059: if (checkMissingData(ctrl.getSeparatorChar())) {
060: int choice = JOptionPane.showConfirmDialog(GUIUtils
061: .getMainFrame(), i18n.missingClobDataMsg);
062: if (choice == JOptionPane.YES_OPTION) {
063: // Need to somehow call
064: // SQLResultExecuterPanel.reRunSelectedResultTab(true);
065: //
066: // Something like :
067: // SQLResultExecuterPanel panel = getPanel();
068: // panel.reRunSelectedResultTab(true);
069: //
070: // However, that doesn't apply when the user is exporting from the
071: // table contents table. There needs to be a more generic way to
072: // do this for all tables containing data that is to be exported
073: // where some of the fields contain placeholders.
074: // For now, we just inform the user and let them either continue
075: // or abort and change the configuration manually,
076: // re-run the query / reload the table data and re-export.
077: }
078: if (choice == JOptionPane.NO_OPTION) {
079: // abort the export
080: return;
081: }
082: if (choice == JOptionPane.CANCEL_OPTION) {
083: // abort the export
084: return;
085: }
086: }
087:
088: boolean writeFileSuccess = writeFile(ctrl);
089:
090: if (writeFileSuccess) {
091: String command = ctrl.getCommand();
092:
093: if (null != command) {
094: executeCommand(command);
095: } else {
096: // i18n[TableExportCsvCommand.writeFileSuccess=Export to file
097: // "{0}" is complete.]
098: String msg = s_stringMgr.getString(
099: "TableExportCsvCommand.writeFileSuccess", ctrl
100: .getFile().getAbsolutePath());
101: if (s_log.isInfoEnabled()) {
102: s_log.info(msg);
103: }
104: JOptionPane.showMessageDialog(GUIUtils.getMainFrame(),
105: msg);
106: }
107: }
108: }
109:
110: private boolean checkMissingData(String sepChar) {
111: // TODO: if the use checks "export entire table" and doesn't select all,
112: // then the selected indices are not set, and this check doesn't properly
113: // detect missing data. If export entire table is selected, we need to
114: // set the selected indexes below correctly.
115: int firstSelectedColIdx = _table.getSelectedColumn();
116: int lastSelectedColIdx = firstSelectedColIdx
117: + _table.getSelectedColumnCount();
118: int firstSelectedRowIdx = _table.getSelectedRow();
119: int lastSelectedRowIdx = firstSelectedRowIdx
120: + _table.getSelectedRowCount();
121: for (int colIdx = _table.getSelectedColumn(); colIdx < lastSelectedColIdx; colIdx++) {
122: ExtTableColumn col = (ExtTableColumn) _table
123: .getColumnModel().getColumn(colIdx);
124: int sqlType = col.getColumnDisplayDefinition().getSqlType();
125: if (sqlType == Types.CLOB) {
126: for (int rowIdx = firstSelectedRowIdx; rowIdx < lastSelectedRowIdx; rowIdx++) {
127: Object cellObj = _table.getValueAt(rowIdx, colIdx);
128: String data = getDataCSV(sepChar, cellObj);
129: if (data != null
130: && ClobDescriptor.i18n.CLOB_LABEL
131: .equals(data)) {
132: return true;
133: }
134: }
135: }
136: }
137: return false;
138: }
139:
140: private void executeCommand(String command) {
141: try {
142: Runtime.getRuntime().exec(command);
143: } catch (IOException e) {
144: Object[] params = new Object[] { command, e.getMessage() };
145: // i18n[TableExportCsvCommand.failedToExecuteCommand=Failed to execute\n{0}\nError message\n{1}\nSee last log entry for details.]
146: String msg = s_stringMgr.getString(
147: "TableExportCsvCommand.failedToExecuteCommand",
148: params);
149: s_log.error(msg, e);
150: JOptionPane.showMessageDialog(GUIUtils.getMainFrame(), msg);
151: }
152: }
153:
154: private boolean writeFile(TableExportCsvController ctrl) {
155: File file = null;
156: try {
157:
158: file = ctrl.getFile();
159: if (null != file.getParentFile()) {
160: file.getParentFile().mkdirs();
161: }
162:
163: boolean includeHeaders = ctrl.includeHeaders();
164: boolean complete = ctrl.exportComplete();
165:
166: int nbrSelRows = _table.getSelectedRowCount();
167: if (0 == nbrSelRows || complete) {
168: nbrSelRows = _table.getRowCount();
169: }
170:
171: int nbrSelCols = _table.getSelectedColumnCount();
172: if (0 == nbrSelCols || complete) {
173: nbrSelCols = _table.getColumnCount();
174: }
175:
176: int[] selRows = _table.getSelectedRows();
177: if (0 == selRows.length || complete) {
178: selRows = new int[nbrSelRows];
179: for (int i = 0; i < selRows.length; i++) {
180: selRows[i] = i;
181: }
182: }
183:
184: int[] selCols = _table.getSelectedColumns();
185: if (0 == selCols.length || complete) {
186: selCols = new int[nbrSelCols];
187: for (int i = 0; i < selCols.length; i++) {
188: selCols[i] = i;
189: }
190: }
191:
192: if (TableExportCsvController.EXPORT_FORMAT_CSV == ctrl
193: .getExportFormat()) {
194: return writeCSV(file, ctrl, includeHeaders, nbrSelCols,
195: selCols, nbrSelRows, selRows);
196: } else if (TableExportCsvController.EXPORT_FORMAT_XLS == ctrl
197: .getExportFormat()) {
198: return writeXLS(file, includeHeaders, nbrSelCols,
199: selCols, nbrSelRows, ctrl, selRows);
200: } else {
201: throw new IllegalStateException(
202: "Unknown export format "
203: + ctrl.getExportFormat());
204: }
205:
206: } catch (IOException e) {
207:
208: Object[] params = new Object[] { file, e.getMessage() };
209: // i18n[TableExportCsvCommand.failedToWriteFile=Failed to write file\n{0}\nError message\n{1}\nSee last log entry for details.]
210: String msg = s_stringMgr.getString(
211: "TableExportCsvCommand.failedToWriteFile", params);
212: s_log.error(msg, e);
213: JOptionPane.showMessageDialog(GUIUtils.getMainFrame(), msg);
214: return false;
215: } catch (jxl.write.WriteException e) {
216:
217: Object[] params = new Object[] { file, e.getMessage() };
218: // i18n[TableExportCsvCommand.failedToWriteFile=Failed to write file\n{0}\nError message\n{1}\nSee last log entry for details.]
219: String msg = s_stringMgr.getString(
220: "TableExportCsvCommand.failedToWriteFile", params);
221: s_log.error(msg, e);
222: JOptionPane.showMessageDialog(GUIUtils.getMainFrame(), msg);
223: return false;
224: }
225:
226: }
227:
228: private boolean writeXLS(File file, boolean includeHeaders,
229: int nbrSelCols, int[] selCols, int nbrSelRows,
230: TableExportCsvController ctrl, int[] selRows)
231: throws IOException, WriteException {
232: WritableWorkbook workbook = Workbook.createWorkbook(file);
233: WritableSheet sheet = workbook.createSheet(
234: "Squirrel SQL Export", 0);
235:
236: int curRow = 0;
237: if (includeHeaders) {
238: for (int colIdx = 0; colIdx < nbrSelCols; ++colIdx) {
239: String columnName = _table
240: .getColumnName(selCols[colIdx]);
241: jxl.write.Label label = new jxl.write.Label(colIdx,
242: curRow, columnName);
243: sheet.addCell(label);
244: }
245: curRow++;
246: }
247:
248: for (int rowIdx = 0; rowIdx < nbrSelRows; ++rowIdx) {
249: for (int colIdx = 0; colIdx < nbrSelCols; ++colIdx) {
250: WritableCell xlsCell;
251: if (ctrl.useGloablPrefsFormatting()
252: && _table.getColumnModel().getColumn(colIdx) instanceof ExtTableColumn) {
253: ExtTableColumn col = (ExtTableColumn) _table
254: .getColumnModel().getColumn(colIdx);
255: xlsCell = getXlsCell(col, colIdx, curRow, _table
256: .getValueAt(selRows[rowIdx],
257: selCols[colIdx]));
258: } else {
259: xlsCell = getXlsCell(null, colIdx, curRow, _table
260: .getValueAt(selRows[rowIdx],
261: selCols[colIdx]));
262: }
263: sheet.addCell(xlsCell);
264:
265: }
266: curRow++;
267: }
268:
269: // All sheets and cells added. Now write out the workbook
270: workbook.write();
271: workbook.close();
272:
273: return true;
274: }
275:
276: private WritableCell getXlsCell(ExtTableColumn col, int colIdx,
277: int curRow, Object cellObj) {
278: if (null == cellObj) {
279: return new jxl.write.Label(colIdx, curRow,
280: getDataXLSAsString(cellObj));
281: }
282:
283: if (null == col) {
284: return new jxl.write.Label(colIdx, curRow,
285: getDataXLSAsString(cellObj));
286: }
287:
288: WritableCell ret;
289: ColumnDisplayDefinition colDef = col
290: .getColumnDisplayDefinition();
291: int colType = colDef.getSqlType();
292: switch (colType) {
293: case Types.BIT:
294: case Types.BOOLEAN:
295: ret = new jxl.write.Boolean(colIdx, curRow,
296: (Boolean) cellObj);
297: break;
298: case Types.INTEGER:
299: ret = new jxl.write.Number(colIdx, curRow,
300: ((Number) cellObj).floatValue());
301: break;
302: case Types.SMALLINT:
303: case Types.TINYINT:
304: ret = new jxl.write.Number(colIdx, curRow,
305: ((Number) cellObj).floatValue());
306: break;
307: case Types.DECIMAL:
308: ret = new jxl.write.Number(colIdx, curRow,
309: ((Number) cellObj).floatValue());
310: break;
311: case Types.NUMERIC:
312: ret = new jxl.write.Number(colIdx, curRow,
313: ((Number) cellObj).floatValue());
314: break;
315: case Types.FLOAT:
316: ret = new jxl.write.Number(colIdx, curRow,
317: ((Number) cellObj).floatValue());
318: break;
319: case Types.DOUBLE:
320: ret = new jxl.write.Number(colIdx, curRow,
321: ((Number) cellObj).floatValue());
322: break;
323: case Types.REAL:
324: ret = new jxl.write.Number(colIdx, curRow,
325: ((Number) cellObj).floatValue());
326: break;
327: case Types.BIGINT:
328: ret = new jxl.write.Number(colIdx, curRow, Long
329: .parseLong(cellObj.toString()));
330: break;
331: case Types.DATE:
332: case Types.TIMESTAMP:
333: case Types.TIME:
334: /* Work arround some UTC and Daylight saving offsets */
335: long time = (((java.util.Date) cellObj).getTime());
336:
337: Calendar cal = Calendar.getInstance();
338: cal.setTime((java.util.Date) cellObj);
339:
340: int offset = (cal.get(Calendar.ZONE_OFFSET) + cal
341: .get(Calendar.DST_OFFSET));
342: java.util.Date xlsUTCDate = new java.util.Date(time
343: + offset);
344: ret = new jxl.write.DateTime(colIdx, curRow, xlsUTCDate,
345: jxl.write.DateTime.GMT);
346: break;
347: case Types.CHAR:
348: case Types.VARCHAR:
349: case Types.LONGVARCHAR:
350: cellObj = CellComponentFactory.renderObject(cellObj, col
351: .getColumnDisplayDefinition());
352: ret = new jxl.write.Label(colIdx, curRow,
353: getDataXLSAsString(cellObj));
354: break;
355: default:
356: cellObj = CellComponentFactory.renderObject(cellObj, col
357: .getColumnDisplayDefinition());
358: ret = new jxl.write.Label(colIdx, curRow,
359: getDataXLSAsString(cellObj));
360: }
361: return ret;
362: }
363:
364: private String getDataXLSAsString(Object cellObj) {
365: if (cellObj == null) {
366: return "";
367: } else {
368: return cellObj.toString().trim();
369: }
370: }
371:
372: private boolean writeCSV(File file, TableExportCsvController ctrl,
373: boolean includeHeaders, int nbrSelCols, int[] selCols,
374: int nbrSelRows, int[] selRows) throws IOException {
375: BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(
376: new FileOutputStream(file), ctrl.getCSVCharset()));
377:
378: String separator = ctrl.getSeparatorChar();
379:
380: if (includeHeaders) {
381: for (int colIdx = 0; colIdx < nbrSelCols; ++colIdx) {
382: String columnName = _table
383: .getColumnName(selCols[colIdx]);
384: bw.write(getDataCSV(separator, columnName));
385: if (nbrSelCols - 1 > colIdx) {
386: bw.write(separator);
387: }
388: }
389: bw.write('\n');
390: }
391:
392: for (int rowIdx = 0; rowIdx < nbrSelRows; ++rowIdx) {
393: for (int colIdx = 0; colIdx < nbrSelCols; ++colIdx) {
394: Object cellObj;
395: String cellObjData = null;
396:
397: if (ctrl.useGloablPrefsFormatting()
398: && _table.getColumnModel().getColumn(colIdx) instanceof ExtTableColumn) {
399: ExtTableColumn col = (ExtTableColumn) _table
400: .getColumnModel().getColumn(colIdx);
401: cellObj = _table.getValueAt(selRows[rowIdx],
402: selCols[colIdx]);
403:
404: if (null != cellObj) {
405: cellObj = CellComponentFactory.renderObject(
406: cellObj, col
407: .getColumnDisplayDefinition());
408: cellObjData = getDataCSV(separator, cellObj);
409: }
410: } else {
411: cellObj = _table.getValueAt(selRows[rowIdx],
412: selCols[colIdx]);
413: }
414: cellObjData = getDataCSV(separator, cellObj);
415: bw.write(cellObjData);
416:
417: if (nbrSelCols - 1 > colIdx) {
418: bw.write(separator);
419: }
420: }
421: bw.write('\n');
422: }
423:
424: bw.flush();
425: bw.close();
426:
427: return true;
428: }
429:
430: private String getDataCSV(String sepChar, Object cellObj) {
431: if (cellObj == null) {
432: return "";
433: } else {
434: String ret = cellObj.toString().trim();
435:
436: if (0 <= ret.indexOf(sepChar) || 0 <= ret.indexOf('\n')) {
437: ret = "\"" + ret.replaceAll("\"", "\"\"") + "\"";
438: }
439:
440: return ret;
441: }
442: }
443:
444: }
|