Search in sources :

Example 1 with Workbook

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;
}
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 Workbook

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;
    }
}
Also used : ArrayList(java.util.ArrayList) WorkbookSettings(jxl.WorkbookSettings) Workbook(jxl.Workbook) BiffException(jxl.read.biff.BiffException) IOException(java.io.IOException) IRunnableWithProgress(org.eclipse.jface.operation.IRunnableWithProgress) IProgressMonitor(org.eclipse.core.runtime.IProgressMonitor) ImportFileDescription(com.cubrid.common.ui.cubrid.table.importhandler.ImportFileDescription) File(java.io.File) Cell(jxl.Cell)

Example 3 with Workbook

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;
}
Also used : Sheet(jxl.Sheet) Workbook(jxl.Workbook)

Example 4 with Workbook

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;
}
Also used : Sheet(jxl.Sheet) Workbook(jxl.Workbook) FileInputStream(java.io.FileInputStream) IOException(java.io.IOException)

Aggregations

Workbook (jxl.Workbook)4 IOException (java.io.IOException)3 Sheet (jxl.Sheet)3 File (java.io.File)2 ArrayList (java.util.ArrayList)2 Cell (jxl.Cell)2 WorkbookSettings (jxl.WorkbookSettings)2 BiffException (jxl.read.biff.BiffException)2 ImportFileDescription (com.cubrid.common.ui.cubrid.table.importhandler.ImportFileDescription)1 FormatDataResult (com.cubrid.cubridmanager.core.cubrid.table.model.FormatDataResult)1 FileInputStream (java.io.FileInputStream)1 List (java.util.List)1 EmptyCell (jxl.biff.EmptyCell)1 CellFormat (jxl.format.CellFormat)1 IProgressMonitor (org.eclipse.core.runtime.IProgressMonitor)1 IRunnableWithProgress (org.eclipse.jface.operation.IRunnableWithProgress)1