use of com.cubrid.common.ui.cubrid.table.control.XlsxReaderHandler in project cubrid-manager by CUBRID.
the class ImportFromXlsxRunnable 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();
final File parentFile;
File file = new File(fileName);
if (file.exists()) {
parentFile = file.getParentFile();
} else {
parentFile = null;
}
final XLSXImportFileHandler importFileHandler = (XLSXImportFileHandler) ImportFileHandlerFactory.getHandler(fileName, importConfig);
final List<ImportRowData> rowList = new ArrayList<ImportRowData>();
XlsxReaderHandler xlsxReader = new XlsxReaderHandler((XLSXImportFileHandler) importFileHandler) {
boolean isFirstRowAsColumn = tableConfig.isFirstRowAsColumn();
private String[] rowContentArray;
private ImportRowData rowData = null;
private boolean isFailed = false;
public void operateRows(int sheetIndex, List<String> rowContentlist) {
if (isFailed) {
return;
}
if (currentRow == getTitleRow()) {
return;
}
if (rowContentlist == null) {
return;
}
rowContentArray = new String[rowContentlist.size()];
rowContentlist.toArray(rowContentArray);
boolean isSuccess = true;
try {
/*Process the row data*/
rowData = processRowData(rowContentArray, null, currentRow, parentFile);
rowList.add(rowData);
pStmt.addBatch();
importedRow++;
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("Stoped by user setting.");
isFailed = true;
} catch (OutOfMemoryError error) {
throw new RuntimeException(error);
} finally {
if (!isSuccess) {
rowData.setStatus(ImportStatus.STATUS_COMMIT_FAILED);
writeErrorLog(rowData);
}
}
}
public void startDocument() {
if (isFirstRowAsColumn) {
setTitleRow(0);
}
}
};
xlsxReader.process(fileName);
if (rowList.size() > 0) {
commit(rowList);
}
}
use of com.cubrid.common.ui.cubrid.table.control.XlsxReaderHandler in project cubrid-manager by CUBRID.
the class PstmtDataTask method getRowParameterListFromXlsx.
/**
* Get the row parameter list from excel 2007
*
* @return List<List<PstmtParameter>>
*/
private List<List<PstmtParameter>> getRowParameterListFromXlsx() {
// FIXME move this logic to core module
final List<List<PstmtParameter>> rowParaList = new ArrayList<List<PstmtParameter>>();
XlsxReaderHandler xlsxReader = new XlsxReaderHandler((XLSXImportFileHandler) importFileHandler) {
private SimpleDateFormat datetimeSdf;
private SimpleDateFormat timestampSdf;
private SimpleDateFormat dateSdf;
private SimpleDateFormat timeSdf;
@Override
public void operateRows(int sheetIndex, List<String> rowlist) throws SQLException, DataFormatException {
if (currentRow == getTitleRow()) {
return;
}
int rowFromStart = sheetIndex * (ImportFileConstants.XLSX_ROW_LIMIT + 1 - getTitleRow()) + currentRow;
if (startRow > rowFromStart) {
return;
}
int lastRow = startRow + rowCount + getTitleRow() + 1;
if (lastRow <= rowFromStart) {
return;
}
List<PstmtParameter> paraList = new ArrayList<PstmtParameter>();
for (int i = 0; i < parameterList.size(); i++) {
PstmtParameter pstmtParameter = parameterList.get(i);
PstmtParameter newParam = new PstmtParameter(pstmtParameter.getParamName(), pstmtParameter.getParamIndex(), pstmtParameter.getDataType(), null);
int column = Integer.parseInt(pstmtParameter.getStringParamValue());
String dataType = DataType.getRealType(pstmtParameter.getDataType());
String cellContent = rowlist.get(column).trim();
int cellType = FieldHandlerUtils.getCellType(dataType, cellContent);
double value;
Date dateCon;
switch(cellType) {
case -1:
cellContent = DataType.NULL_EXPORT_FORMAT;
break;
case 2:
try {
value = Double.parseDouble(cellContent);
dateCon = DateUtil.getJavaDate(value);
cellContent = datetimeSdf.format(dateCon);
} catch (NumberFormatException e) {
cellContent = DataType.NULL_EXPORT_FORMAT;
}
break;
case 3:
try {
value = Double.parseDouble(cellContent);
dateCon = DateUtil.getJavaDate(value);
cellContent = timestampSdf.format(dateCon);
} catch (Exception e) {
cellContent = DataType.NULL_EXPORT_FORMAT;
}
break;
case 4:
try {
value = Double.parseDouble(cellContent);
dateCon = DateUtil.getJavaDate(value);
cellContent = dateSdf.format(dateCon);
} catch (Exception e) {
cellContent = DataType.NULL_EXPORT_FORMAT;
}
break;
case 5:
try {
value = Double.parseDouble(cellContent);
dateCon = DateUtil.getJavaDate(value);
cellContent = timeSdf.format(dateCon);
} catch (Exception e) {
cellContent = DataType.NULL_EXPORT_FORMAT;
}
break;
default:
break;
}
String content = FieldHandlerUtils.getRealValueForImport(dataType, cellContent, parentFile);
FormatDataResult formatDataResult = DBAttrTypeFormatter.format(dataType, content, 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);
}
public void startDocument() {
if (isFirstRowAsColumn) {
setTitleRow(0);
}
datetimeSdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS", Locale.getDefault());
timestampSdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.getDefault());
dateSdf = new SimpleDateFormat("yyyy-MM-dd", Locale.getDefault());
timeSdf = new SimpleDateFormat("HH:mm:ss", Locale.getDefault());
}
};
try {
xlsxReader.process(fileName);
} catch (RuntimeException ex) {
throw ex;
} catch (Exception ex) {
throw new RuntimeException(ex);
}
return rowParaList;
}
use of com.cubrid.common.ui.cubrid.table.control.XlsxReaderHandler in project cubrid-manager by CUBRID.
the class PstmtDataTask method executeFromXlsx.
/**
*
* Do with data from excel file
*
* @param monitor IProgressMonitor
* @throws Exception the Exception
*/
private void executeFromXlsx(final IProgressMonitor monitor) throws Exception {
// FIXME move this logic to core module
XlsxReaderHandler xlsxReader = new XlsxReaderHandler((XLSXImportFileHandler) importFileHandler) {
private SimpleDateFormat datetimeSdf;
private SimpleDateFormat timestampSdf;
private SimpleDateFormat dateSdf;
private SimpleDateFormat timeSdf;
@Override
public void operateRows(int sheetIndex, List<String> rowlist) throws SQLException, DataFormatException {
if (currentRow == getTitleRow()) {
return;
}
XLSXImportFileHandler fileHandler = (XLSXImportFileHandler) importFileHandler;
List<Integer> itemsNumberOfSheets = null;
try {
itemsNumberOfSheets = fileHandler.getSourceFileInfo().getItemsNumberOfSheets();
} catch (Exception ex) {
LOGGER.error(ex.getMessage());
return;
}
int rowFromStart = 0;
for (int i = 0; i < sheetIndex; i++) {
rowFromStart += itemsNumberOfSheets.get(i);
}
rowFromStart += currentRow;
int absoluteStartRow = getAbsoluteRowNum(startRow, itemsNumberOfSheets);
if (absoluteStartRow > rowFromStart) {
return;
}
int absoluteEndRow = getAbsoluteRowNum(startRow + rowCount, itemsNumberOfSheets);
int rowCountIncludingTitle = absoluteEndRow - absoluteStartRow;
int relativeRow = rowFromStart - absoluteStartRow;
if (relativeRow > rowCountIncludingTitle - 1) {
return;
}
for (int i = 0; i < parameterList.size(); i++) {
PstmtParameter pstmtParameter = parameterList.get(i);
int column = Integer.parseInt(pstmtParameter.getStringParamValue());
String dataType = DataType.getRealType(pstmtParameter.getDataType());
String cellContent = rowlist.get(column);
int cellType = FieldHandlerUtils.getCellType(dataType, cellContent);
boolean isHaveError = false;
try {
double value;
Date dateCon;
switch(cellType) {
case -1:
cellContent = DataType.NULL_EXPORT_FORMAT;
break;
case 0:
if (cellContent.contains(".")) {
cellContent = cellContent.substring(0, cellContent.indexOf('.'));
}
break;
case 2:
value = Double.parseDouble(cellContent.trim());
dateCon = DateUtil.getJavaDate(value);
cellContent = datetimeSdf.format(dateCon);
break;
case 3:
value = Double.parseDouble(cellContent.trim());
dateCon = DateUtil.getJavaDate(value);
cellContent = timestampSdf.format(dateCon);
break;
case 4:
value = Double.parseDouble(cellContent.trim());
dateCon = DateUtil.getJavaDate(value);
cellContent = dateSdf.format(dateCon);
break;
case 5:
value = Double.parseDouble(cellContent.trim());
dateCon = DateUtil.getJavaDate(value);
cellContent = timeSdf.format(dateCon);
break;
default:
break;
}
} catch (NumberFormatException e) {
isHaveError = true;
}
String content = FieldHandlerUtils.getRealValueForImport(dataType, cellContent, parentFile);
FormatDataResult formatDataResult = DBAttrTypeFormatter.format(dataType, content, 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 {
isHaveError = true;
PstmtParameter parameter = new PstmtParameter(pstmtParameter.getParamName(), pstmtParameter.getParamIndex(), pstmtParameter.getDataType(), null);
parameter.setCharSet(fileCharset);
FieldHandlerUtils.setPreparedStatementValue(parameter, pStmt, dbCharset);
}
if (isHaveError) {
dataTypeErrorHandling(getErrorMsg(i, column, dataType));
}
}
if (pStmt != null) {
pStmt.addBatch();
monitor.worked(PROGRESS_ROW);
workedProgress += PROGRESS_ROW;
}
int importedRow = relativeRow + 1;
if (importedRow > 0 && importedRow % commitLineCountOnce == 0) {
commit(monitor, importedRow);
} else {
if (importedRow == rowCount && importedRow % commitLineCountOnce != 0) {
commit(monitor, importedRow);
}
}
if (isCancel) {
return;
}
}
public void startDocument() {
if (isFirstRowAsColumn) {
setTitleRow(0);
}
datetimeSdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS", Locale.getDefault());
timestampSdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.getDefault());
dateSdf = new SimpleDateFormat("yyyy-MM-dd", Locale.getDefault());
timeSdf = new SimpleDateFormat("HH:mm:ss", Locale.getDefault());
}
private int getAbsoluteRowNum(int value, List<Integer> itemsNumberOfSheets) {
if (itemsNumberOfSheets == null || itemsNumberOfSheets.isEmpty()) {
return value;
}
if (getTitleRow() == -1) {
return value;
}
int upLimit = 0;
int downLimit = 0;
int absoluteVal = value;
for (int i = 0; i < itemsNumberOfSheets.size(); i++) {
int absoluteValIncldingTitle = value + i + 1;
upLimit += itemsNumberOfSheets.get(i);
if (i == 0) {
if (absoluteValIncldingTitle <= upLimit) {
absoluteVal = absoluteValIncldingTitle;
break;
}
} else {
downLimit += itemsNumberOfSheets.get(i - 1);
if (absoluteValIncldingTitle > downLimit && absoluteValIncldingTitle <= upLimit) {
absoluteVal = absoluteValIncldingTitle;
break;
}
}
}
return absoluteVal;
}
};
xlsxReader.process(fileName);
}
Aggregations