use of jxl.Workbook 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.Workbook in project cubrid-manager by CUBRID.
the class XLSImportFileHandler method getSourceFileInfo.
/**
* Get the source file information
*
* @return ImportFileDescription
* @throws Exception in process.
*/
public ImportFileDescription getSourceFileInfo() throws Exception {
// FIXME move this logic to core module
synchronized (this) {
if (importFileDescription == null) {
final List<String> colsLst = new ArrayList<String>();
final List<Integer> itemsNumberOfSheets = new ArrayList<Integer>();
importFileDescription = new ImportFileDescription(0, 0, colsLst);
IRunnableWithProgress runnable = new IRunnableWithProgress() {
public void run(final IProgressMonitor monitor) {
monitor.beginTask("", IProgressMonitor.UNKNOWN);
Workbook workbook = null;
int totalRowCount = 0;
int sheetNum = 0;
try {
if (fileCharset == null) {
workbook = Workbook.getWorkbook(new File(fileName));
} else {
WorkbookSettings workbookSettings = new WorkbookSettings();
workbookSettings.setEncoding(fileCharset);
workbook = Workbook.getWorkbook(new File(fileName), workbookSettings);
}
// get column count and total row count
sheetNum = workbook.getNumberOfSheets();
if (sheetNum > 0) {
int columnCount = workbook.getSheet(0).getColumns();
for (int j = 0; !monitor.isCanceled() && j < columnCount; j++) {
Cell cell = workbook.getSheet(0).getCell(j, 0);
//$NON-NLS-1$
colsLst.add(cell == null ? "" : cell.getContents());
}
}
for (int i = 0; !monitor.isCanceled() && i < sheetNum; i++) {
int rowsInSheet = workbook.getSheet(i).getRows();
itemsNumberOfSheets.add(Integer.valueOf(rowsInSheet));
totalRowCount += rowsInSheet;
}
if (monitor.isCanceled()) {
throw new InterruptedException();
}
} catch (Exception e) {
LOGGER.error(e.getMessage(), e);
throw new RuntimeException(e);
} finally {
importFileDescription.setSheetNum(sheetNum);
importFileDescription.setTotalCount(totalRowCount);
importFileDescription.setFirstRowCols(colsLst);
importFileDescription.setItemsNumberOfSheets(itemsNumberOfSheets);
if (workbook != null) {
workbook.close();
}
monitor.done();
}
}
};
PlatformUI.getWorkbench().getProgressService().busyCursorWhile(runnable);
}
return importFileDescription;
}
}
use of jxl.Workbook in project oxTrust by GluuFederation.
the class ExcelService method readExcelFile.
public org.gluu.oxtrust.model.table.Table readExcelFile(InputStream excelFile) {
org.gluu.oxtrust.model.table.Table result = null;
Workbook workbook = null;
try {
workbook = Workbook.getWorkbook(excelFile);
// Get the first sheet
Sheet sheet = workbook.getSheet(0);
result = new org.gluu.oxtrust.model.table.Table();
// Loop over columns and rows
for (int j = 0; j < sheet.getColumns(); j++) {
for (int i = 0; i < sheet.getRows(); i++) {
result.addCell(new org.gluu.oxtrust.model.table.Cell(j, i, sheet.getCell(j, i).getContents()));
}
}
} catch (Exception ex) {
log.error("Failed to read Excel file", ex);
} finally {
if (workbook != null) {
workbook.close();
}
}
return result;
}
use of jxl.Workbook in project yyl_example by Relucent.
the class ExcelParse method getResult.
/**
* 解析excel文件,并按照模版生成对应内容
* @param excelFile excel文件路径
* @return excel对应的的字符串 如果解析过程中出现错误则返回NULL
*/
public String getResult(String excelFile) {
Workbook workbook = null;
try {
workbook = Workbook.getWorkbook(new FileInputStream(excelFile));
Sheet sheet = workbook.getSheet(0);
StringBuilder sbr = new StringBuilder();
int indexPlace = 0;
for (int i = 0; i < templetlist.size(); i++) {
Object obj = templetlist.get(i);
if (obj == PLACEHOLDER) {
String locName = (String) mappinglist.get(indexPlace++);
try {
String cellStr = sheet.getCell(locName).getContents();
sbr.append(cellStr);
} catch (Exception e) {
e.printStackTrace();
}
} else {
sbr.append(obj);
}
}
return sbr.toString();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (workbook != null) {
workbook.close();
}
}
return null;
}
Aggregations