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;
}
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);
}
}
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);
}
}
Aggregations