Examples with XLSImportFileHandler com.cubrid.common.ui.cubrid.table.importhandler.handler.XLSImportFileHandler used on opensource projects

Example 1 with XLSImportFileHandler

use of com.cubrid.common.ui.cubrid.table.importhandler.handler.XLSImportFileHandler in project cubrid-manager by CUBRID.

the class ImportFileHandlerFactory method getHandler.

/**
	 * Get the a import handler instance by file's extended name. xls,xlsx,csv
	 * supported.
	 * 
	 * @param fileName of the import file
	 * @param fileCharset String
	 * @return ImportFileHandler
	 */
public static ImportFileHandler getHandler(String fileName, ImportConfig importConfig) {
    String lowerCase = fileName.toLowerCase(Locale.getDefault());
    int importType = importConfig.getImportType();
    if (importType == ImportConfig.IMPORT_FROM_EXCEL) {
        //			} else 
        if (lowerCase.endsWith(".xls")) {
            return new XLSImportFileHandler(fileName, importConfig.getFilesCharset());
        } else if (lowerCase.endsWith(".csv")) {
            return new CSVImportFileHandler(fileName, importConfig.getFilesCharset());
        }
    } else if (importType == ImportConfig.IMPORT_FROM_TXT) {
        return new TxtImportFileHandler(fileName, importConfig.getFilesCharset(), importConfig.getColumnDelimiter(), importConfig.getRowDelimiter());
    } else if (importType == ImportConfig.IMPORT_FROM_SQL) {
    }
    throw new RuntimeException("Not supported file type.");
}

Example 2 with XLSImportFileHandler

use of com.cubrid.common.ui.cubrid.table.importhandler.handler.XLSImportFileHandler in project cubrid-manager by CUBRID.

the class ImportDataFromFileDialog method finish.

/**
	 *
	 * After task finished, call it
	 *
	 *
	 *
	 */
private void finish() {
    if (getStartShowResult()) {
        return;
    } else {
        setStartShowResult(true);
    }
    if (importFileHandler instanceof XLSImportFileHandler) {
        XLSImportFileHandler xlsHandler = (XLSImportFileHandler) importFileHandler;
        xlsHandler.dispose();
    } else if (importFileHandler instanceof XLSXImportFileHandler) {
        XLSXImportFileHandler xlsHandler = (XLSXImportFileHandler) importFileHandler;
        xlsHandler.dispose();
    }
    long endTimestamp = System.currentTimeMillis();
    String spendTime = calcSpendTime(beginTimestamp, endTimestamp);
    List<Status> allStatusList = new ArrayList<Status>();
    int commitedCount = 0;
    boolean isHasError = false;
    boolean isCancel = false;
    List<String> errorList = new ArrayList<String>();
    int totalErrorCount = 0;
    for (PstmtDataTask task : taskList) {
        commitedCount += task.getCommitedCount();
        isCancel = isCancel || task.isCancel();
        if (!task.isSuccess() && task.getErrorMsg() != null) {
            Status status = new Status(IStatus.ERROR, CommonUIPlugin.PLUGIN_ID, task.getErrorMsg());
            allStatusList.add(status);
            isHasError = true;
        }
        errorList.addAll(task.getErrorMsgList());
        totalErrorCount = totalErrorCount + task.getTotalErrorCount();
        if (!errorList.isEmpty()) {
            isHasError = true;
        }
    }
    if (!isHasError && isCancel) {
        String msg = Messages.bind(Messages.msgCancelExecSql, new String[] { String.valueOf(commitedCount), spendTime });
        CommonUITool.openInformationBox(getShell(), Messages.titleExecuteResult, msg);
        close();
        deleteLog();
        return;
    }
    String successMsg = Messages.bind(Messages.msgSuccessExecSql, new String[] { String.valueOf(commitedCount), spendTime });
    if (!isHasError) {
        CommonUITool.openInformationBox(getShell(), Messages.titleExecuteResult, successMsg);
        close();
        deleteLog();
        return;
    }
    if (errorList.isEmpty()) {
        // break
        Status status = new Status(IStatus.INFO, CommonUIPlugin.PLUGIN_ID, successMsg + "\r\n");
        allStatusList.add(0, status);
        IStatus[] errors = new IStatus[allStatusList.size()];
        allStatusList.toArray(errors);
        MultiStatus multiStatus = new MultiStatus(CommonUIPlugin.PLUGIN_ID, IStatus.OK, errors, Messages.msgDetailCause, null);
        String errorMsg = Messages.bind(Messages.errExecSql, new String[] { String.valueOf(commitedCount), spendTime });
        ErrorDialog errorDialog = new ErrorDialog(getShell(), Messages.titleExecuteResult, errorMsg, multiStatus, IStatus.INFO | IStatus.ERROR);
        errorDialog.open();
        if (isHasError) {
            getShell().setVisible(true);
        } else {
            close();
        }
    } else {
        //ignore the error
        String msg = Messages.bind(Messages.importColumnNOTotal, String.valueOf(totalErrorCount)) + "\r\n" + successMsg;
        ImportResultDialog dialog = new ImportResultDialog(getShell(), msg, errorList, errorLogDir);
        dialog.open();
        close();
    }
    deleteLog();
}

Example 3 with XLSImportFileHandler

use of com.cubrid.common.ui.cubrid.table.importhandler.handler.XLSImportFileHandler in project cubrid-manager by CUBRID.

the class ImportDataFromFileDialog method showResultSet.

/**
	 *
	 * Show the result set by query editor
	 *
	 * @param parameterList List<PstmtParameter>
	 */
private void showResultSet(List<PstmtParameter> parameterList) {
    // FIXME move this logic to core module
    IWorkbenchWindow window = PlatformUI.getWorkbench().getActiveWorkbenchWindow();
    if (window == null) {
        return;
    }
    String showedSql = sqlTxt.getText();
    String executedSql = showedSql;
    while (executedSql.endsWith(";")) {
        executedSql = executedSql.substring(0, executedSql.length() - 1);
    }
    executedSql += ";";
    int threadCount = threadCountSpinner.getSelection();
    String fileName = fileNameTxt.getText();
    String charset = fileCharsetCombo.getText();
    int totalLine = Integer.parseInt(totalLinesText.getText());
    int commitedLineOnce = commitLineSpinner.getSelection();
    ImportFileHandler handler = ImportFileHandlerFactory.getHandler(fileName, charset);
    PstmtDataTask task = new PstmtDataTask(sqlTxt.getText(), database, fileName, parameterList, 0, totalLine, commitedLineOnce, charset, firstRowAsColumnBtn.getSelection(), false, null, handler);
    List<List<PstmtParameter>> rowParameterList = null;
    try {
        rowParameterList = task.getRowParameterList();
    } catch (RuntimeException ex) {
        String errorMsg = null;
        if (ex.getCause() == null) {
            errorMsg = ex.getMessage();
        } else {
            if (ex.getCause() instanceof OutOfMemoryError) {
                errorMsg = Messages.errNoAvailableMemory;
            } else {
                errorMsg = ex.getCause().getMessage();
            }
        }
        errorMsg = errorMsg == null || errorMsg.trim().length() == 0 ? "Unknown error." : errorMsg;
        CommonUITool.openErrorBox(getShell(), errorMsg);
        return;
    } catch (Exception ex) {
        String errorMsg = ex.getMessage();
        errorMsg = errorMsg == null || errorMsg.trim().length() == 0 ? "Unknown error." : errorMsg;
        CommonUITool.openErrorBox(getShell(), errorMsg);
        return;
    } finally {
        if (importFileHandler instanceof XLSImportFileHandler) {
            XLSImportFileHandler xlsHandler = (XLSImportFileHandler) importFileHandler;
            xlsHandler.dispose();
        } else if (importFileHandler instanceof XLSXImportFileHandler) {
            XLSXImportFileHandler xlsHandler = (XLSXImportFileHandler) importFileHandler;
            xlsHandler.dispose();
        }
    }
    close();
    int rows = rowParameterList == null ? 0 : rowParameterList.size();
    int rowsOfThread = rows;
    if (threadCount > 1) {
        rowsOfThread = rows / threadCount;
    }
    int rowsOfLastThread = rowsOfThread + (rows % threadCount == 0 ? 0 : rows % threadCount);
    int currentRow = 0;
    for (int i = 0; i < threadCount; i++) {
        QueryUnit editorInput = new QueryUnit();
        IEditorPart editor = null;
        try {
            editor = window.getActivePage().openEditor(editorInput, QueryEditorPart.ID);
        } catch (PartInitException e) {
            editor = null;
        }
        if (editor != null) {
            QueryEditorPart queryEditor = ((QueryEditorPart) editor);
            int endRow = currentRow + rowsOfThread;
            if (i == threadCount - 1) {
                endRow = currentRow + rowsOfLastThread;
            }
            List<List<PstmtParameter>> paraList = new ArrayList<List<PstmtParameter>>();
            StringBuffer showedSqlBuffer = new StringBuffer();
            StringBuffer executeSqlBuffer = new StringBuffer();
            for (int j = currentRow; j < endRow; j++) {
                showedSqlBuffer.append(getCommentSqlValue(rowParameterList.get(j)));
                paraList.add(rowParameterList.get(j));
                executeSqlBuffer.append(executedSql);
            }
            showedSqlBuffer.append(showedSql);
            currentRow = endRow;
            if (!queryEditor.isConnected() && database != null) {
                queryEditor.connect(database);
            }
            if (queryEditor.isConnected()) {
                queryEditor.setQuery(showedSqlBuffer.toString(), executedSql, rowParameterList, true, true, false);
            } else {
                queryEditor.setQuery(showedSqlBuffer.toString(), true, false, false);
            }
        }
    }
}

Example 4 with XLSImportFileHandler

use of com.cubrid.common.ui.cubrid.table.importhandler.handler.XLSImportFileHandler in project cubrid-manager by CUBRID.

the class PstmtDataTask method executeFromXls.

/**
	 * Do with data from excel file
	 *
	 * @param monitor IProgressMonitor
	 */
private void executeFromXls(IProgressMonitor monitor) {
    // FIXME move this logic to core module
    try {
        XLSImportFileHandler fileHandler = (XLSImportFileHandler) importFileHandler;
        Sheet[] sheets = fileHandler.getSheets();
        ImportFileDescription fileDesc = fileHandler.getSourceFileInfo();
        int rowNum = 0;
        int currentRow = 0;
        for (int sheetNum = 0; sheetNum < sheets.length; sheetNum++) {
            int start = 0;
            int lastRowNum = rowNum;
            int rows = fileDesc.getItemsNumberOfSheets().get(sheetNum);
            if (isFirstRowAsColumn) {
                rowNum += rows - 1;
            } else {
                rowNum += rows;
            }
            if (startRow > rowNum) {
                continue;
            }
            if (lastRowNum >= startRow) {
                start = isFirstRowAsColumn ? 1 : 0;
            } else {
                start = startRow - lastRowNum + (isFirstRowAsColumn ? 1 : 0);
            }
            Sheet sheet = sheets[sheetNum];
            String content = null;
            String pattern = null;
            for (int i = start; i < rows && currentRow < rowCount; i++) {
                for (int j = 0; j < parameterList.size(); j++) {
                    PstmtParameter pstmtParameter = parameterList.get(j);
                    int column = Integer.parseInt(pstmtParameter.getStringParamValue());
                    Cell cell = sheet.getCell(column, i);
                    content = null;
                    pattern = null;
                    if (cell == null) {
                        content = null;
                    } else if (cell instanceof EmptyCell) {
                        content = null;
                    } else {
                        content = cell.getContents();
                        CellFormat format = cell.getCellFormat();
                        if (format != null && format.getFormat() != null) {
                            pattern = format.getFormat().getFormatString();
                        }
                    }
                    String dataType = DataType.getRealType(pstmtParameter.getDataType());
                    content = FieldHandlerUtils.getRealValueForImport(dataType, content, parentFile);
                    FormatDataResult formatDataResult = null;
                    if (StringUtil.isEmpty(pattern)) {
                        formatDataResult = DBAttrTypeFormatter.format(dataType, content, false, dbCharset, true);
                    } else {
                        formatDataResult = DBAttrTypeFormatter.format(dataType, content, pattern, false, dbCharset, true);
                    }
                    if (formatDataResult.isSuccess()) {
                        PstmtParameter parameter = new PstmtParameter(pstmtParameter.getParamName(), pstmtParameter.getParamIndex(), pstmtParameter.getDataType(), content);
                        parameter.setCharSet(fileCharset);
                        FieldHandlerUtils.setPreparedStatementValue(parameter, pStmt, dbCharset);
                    } else {
                        dataTypeErrorHandling(getErrorMsg(i, column, dataType));
                        PstmtParameter parameter = new PstmtParameter(pstmtParameter.getParamName(), pstmtParameter.getParamIndex(), pstmtParameter.getDataType(), null);
                        parameter.setCharSet(fileCharset);
                        FieldHandlerUtils.setPreparedStatementValue(parameter, pStmt, dbCharset);
                    }
                }
                if (pStmt != null) {
                    pStmt.addBatch();
                    monitor.worked(PROGRESS_ROW);
                    workedProgress += PROGRESS_ROW;
                }
                currentRow++;
                if (currentRow > 0 && currentRow % commitLineCountOnce == 0) {
                    commit(monitor, currentRow);
                }
                if (isCancel) {
                    return;
                }
            }
        }
        if (currentRow > 0 && currentRow % commitLineCountOnce > 0) {
            commit(monitor, currentRow);
        }
    } catch (SQLException ex) {
        throw new RuntimeException(ex);
    } catch (BiffException ex) {
        throw new RuntimeException(ex);
    } catch (IOException ex) {
        throw new RuntimeException(ex);
    } catch (DataFormatException ex) {
        throw new RuntimeException(ex);
    } catch (Exception ex) {
        throw new RuntimeException(ex);
    } catch (OutOfMemoryError error) {
        throw new RuntimeException(error);
    }
}

Example 5 with XLSImportFileHandler

use of com.cubrid.common.ui.cubrid.table.importhandler.handler.XLSImportFileHandler in project cubrid-manager by CUBRID.

the class ImportFromXlsRunnable method doRun.

/* (non-Javadoc)
	 * @see com.cubrid.common.ui.cubrid.table.dialog.imp.progress.AbsImportDataThread#doRun()
	 */
@Override
protected void doRun() throws Exception {
    // FIXME move this logic to core module
    if (pStmt == null) {
        handleEvent(new ImportDataFailedEvent(tableName, tableConfig.getLineCount(), tableConfig.getInsertDML(), "Invalid parameters."));
        return;
    }
    String fileName = tableConfig.getFilePath();
    boolean isFirstRowAsColumn = tableConfig.isFirstRowAsColumn();
    File parentFile;
    File file = new File(fileName);
    if (file.exists()) {
        parentFile = file.getParentFile();
    } else {
        parentFile = null;
    }
    int start = 0;
    if (isFirstRowAsColumn) {
        start = 1;
    }
    try {
        XLSImportFileHandler fileHandler = (XLSImportFileHandler) ImportFileHandlerFactory.getHandler(fileName, importConfig);
        Sheet[] sheets = fileHandler.getSheets();
        ImportFileDescription fileDesc = getFileDescription(fileHandler);
        int currentRow = 0;
        List<ImportRowData> rowList = new ArrayList<ImportRowData>();
        for (int sheetNum = 0; sheetNum < sheets.length; sheetNum++) {
            int rows = fileDesc.getItemsNumberOfSheets().get(sheetNum);
            Sheet sheet = sheets[sheetNum];
            String[] rowContent = null;
            String[] patterns = null;
            ImportRowData rowData = null;
            String content = null;
            String pattern = null;
            for (int i = start; i < rows; i++) {
                boolean isSuccess = true;
                try {
                    int columns = sheet.getColumns();
                    for (int j = 0; j < columns; j++) {
                        rowContent = new String[columns];
                        patterns = new String[columns];
                        Cell[] cells = sheet.getRow(i);
                        for (int k = 0; k < cells.length; k++) {
                            Cell cell = cells[k];
                            content = null;
                            pattern = null;
                            if (cell == null) {
                                content = null;
                            } else if (cell instanceof EmptyCell) {
                                content = null;
                            } else {
                                content = cell.getContents();
                                CellFormat format = cell.getCellFormat();
                                if (format != null && format.getFormat() != null) {
                                    pattern = format.getFormat().getFormatString();
                                }
                            }
                            rowContent[k] = content;
                            patterns[k] = pattern;
                        }
                    }
                    /*Process the row data*/
                    rowData = processRowData(rowContent, patterns, currentRow, parentFile);
                    pStmt.addBatch();
                    rowList.add(rowData);
                    currentRow++;
                    /*Process commit*/
                    if (rowList.size() >= importConfig.getCommitLine()) {
                        commit(rowList);
                    }
                    if (isCanceled) {
                        return;
                    }
                } catch (SQLException ex) {
                    isSuccess = false;
                    LOGGER.debug(ex.getMessage());
                } catch (StopPerformException ex) {
                    isSuccess = false;
                    handleEvent(new ImportDataTableFailedEvent(tableName));
                    LOGGER.debug("Stop import by user setting.");
                    break;
                } catch (OutOfMemoryError error) {
                    throw new RuntimeException(error);
                } finally {
                    if (!isSuccess) {
                        rowData.setStatus(ImportStatus.STATUS_COMMIT_FAILED);
                        writeErrorLog(rowData);
                    }
                }
            }
        }
        if (rowList.size() > 0) {
            commit(rowList);
        }
    } catch (BiffException ex) {
        throw new RuntimeException(ex);
    } catch (IOException ex) {
        throw new RuntimeException(ex);
    } catch (Exception ex) {
        throw new RuntimeException(ex);
    } catch (OutOfMemoryError error) {
        throw new RuntimeException(error);
    }
}