Search in sources :

Example 1 with BiffException

use of jxl.read.biff.BiffException in project cubrid-manager by CUBRID.

the class PstmtDataTask method getRowParameterListFromExcel.

/**
	 *
	 * Get the parameter of PSTMT list from excel file
	 *
	 * @return List<List<PstmtParameter>>
	 */
private List<List<PstmtParameter>> getRowParameterListFromExcel() {
    // FIXME move this logic to core module
    File file = new File(fileName);
    Workbook workbook = null;
    List<List<PstmtParameter>> rowParaList = new ArrayList<List<PstmtParameter>>();
    try {
        if (fileCharset == null) {
            workbook = Workbook.getWorkbook(file);
        } else {
            WorkbookSettings workbookSettings = new WorkbookSettings();
            workbookSettings.setEncoding(fileCharset);
            workbook = Workbook.getWorkbook(file, workbookSettings);
        }
        Sheet[] sheets = workbook.getSheets();
        for (int sheetNum = 0; sheetNum < sheets.length; sheetNum++) {
            Sheet sheet = sheets[sheetNum];
            int rows = sheet.getRows();
            int start = 0;
            if (isFirstRowAsColumn) {
                start = 1;
            }
            for (int i = start; i < rows; i++) {
                List<PstmtParameter> paraList = new ArrayList<PstmtParameter>();
                for (int j = 0; j < parameterList.size(); j++) {
                    PstmtParameter pstmtParameter = parameterList.get(j);
                    PstmtParameter newParam = new PstmtParameter(pstmtParameter.getParamName(), pstmtParameter.getParamIndex(), pstmtParameter.getDataType(), null);
                    int column = Integer.parseInt(pstmtParameter.getStringParamValue());
                    Cell cell = sheet.getCell(column, i);
                    String content = null;
                    String 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()) {
                        newParam.setCharSet(fileCharset);
                        newParam.setParamValue(content);
                    } else {
                        dataTypeErrorHandling(getErrorMsg(i, column, dataType));
                        newParam.setCharSet(fileCharset);
                        newParam.setParamValue(null);
                    }
                    paraList.add(newParam);
                }
                rowParaList.add(paraList);
            }
        }
    } catch (BiffException ex) {
        throw new RuntimeException(ex);
    } catch (IOException ex) {
        throw new RuntimeException(ex);
    } catch (DataFormatException ex) {
        throw new RuntimeException(ex);
    } catch (OutOfMemoryError error) {
        throw new RuntimeException(error);
    } finally {
        if (workbook != null) {
            workbook.close();
        }
    }
    return rowParaList;
}
Also used : BiffException(jxl.read.biff.BiffException) CellFormat(jxl.format.CellFormat) ArrayList(java.util.ArrayList) EmptyCell(jxl.biff.EmptyCell) WorkbookSettings(jxl.WorkbookSettings) IOException(java.io.IOException) Workbook(jxl.Workbook) FormatDataResult(com.cubrid.cubridmanager.core.cubrid.table.model.FormatDataResult) List(java.util.List) ArrayList(java.util.ArrayList) File(java.io.File) Sheet(jxl.Sheet) Cell(jxl.Cell) EmptyCell(jxl.biff.EmptyCell)

Example 2 with BiffException

use of jxl.read.biff.BiffException 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);
    }
}
Also used : BiffException(jxl.read.biff.BiffException) CellFormat(jxl.format.CellFormat) SQLException(java.sql.SQLException) EmptyCell(jxl.biff.EmptyCell) IOException(java.io.IOException) FileNotFoundException(java.io.FileNotFoundException) UnsupportedEncodingException(java.io.UnsupportedEncodingException) SQLException(java.sql.SQLException) BiffException(jxl.read.biff.BiffException) IOException(java.io.IOException) FormatDataResult(com.cubrid.cubridmanager.core.cubrid.table.model.FormatDataResult) ImportFileDescription(com.cubrid.common.ui.cubrid.table.importhandler.ImportFileDescription) XLSImportFileHandler(com.cubrid.common.ui.cubrid.table.importhandler.handler.XLSImportFileHandler) Sheet(jxl.Sheet) Cell(jxl.Cell) EmptyCell(jxl.biff.EmptyCell)

Example 3 with BiffException

use of jxl.read.biff.BiffException 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);
    }
}
Also used : CellFormat(jxl.format.CellFormat) SQLException(java.sql.SQLException) ArrayList(java.util.ArrayList) ImportDataTableFailedEvent(com.cubrid.common.ui.cubrid.table.dialog.imp.event.ImportDataTableFailedEvent) ImportFileDescription(com.cubrid.common.ui.cubrid.table.importhandler.ImportFileDescription) XLSImportFileHandler(com.cubrid.common.ui.cubrid.table.importhandler.handler.XLSImportFileHandler) Cell(jxl.Cell) EmptyCell(jxl.biff.EmptyCell) BiffException(jxl.read.biff.BiffException) ImportRowData(com.cubrid.common.ui.cubrid.table.dialog.imp.model.ImportRowData) EmptyCell(jxl.biff.EmptyCell) IOException(java.io.IOException) BiffException(jxl.read.biff.BiffException) IOException(java.io.IOException) SQLException(java.sql.SQLException) ImportDataFailedEvent(com.cubrid.common.ui.cubrid.table.dialog.imp.event.ImportDataFailedEvent) File(java.io.File) Sheet(jxl.Sheet)

Aggregations

IOException (java.io.IOException)3 Cell (jxl.Cell)3 Sheet (jxl.Sheet)3 EmptyCell (jxl.biff.EmptyCell)3 CellFormat (jxl.format.CellFormat)3 BiffException (jxl.read.biff.BiffException)3 ImportFileDescription (com.cubrid.common.ui.cubrid.table.importhandler.ImportFileDescription)2 XLSImportFileHandler (com.cubrid.common.ui.cubrid.table.importhandler.handler.XLSImportFileHandler)2 FormatDataResult (com.cubrid.cubridmanager.core.cubrid.table.model.FormatDataResult)2 File (java.io.File)2 SQLException (java.sql.SQLException)2 ArrayList (java.util.ArrayList)2 ImportDataFailedEvent (com.cubrid.common.ui.cubrid.table.dialog.imp.event.ImportDataFailedEvent)1 ImportDataTableFailedEvent (com.cubrid.common.ui.cubrid.table.dialog.imp.event.ImportDataTableFailedEvent)1 ImportRowData (com.cubrid.common.ui.cubrid.table.dialog.imp.model.ImportRowData)1 FileNotFoundException (java.io.FileNotFoundException)1 UnsupportedEncodingException (java.io.UnsupportedEncodingException)1 List (java.util.List)1 Workbook (jxl.Workbook)1 WorkbookSettings (jxl.WorkbookSettings)1