Examples with Sheet jxl.Sheet used on opensource projects

Example 1 with Sheet

use of jxl.Sheet 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;
}

Example 2 with Sheet

use of jxl.Sheet in project summer-bean by cn-cerc.

the class ImportExcel method readFileData.

public DataSet readFileData(Record record) throws Exception {
    FileItem file = this.getFile(record);
    // 获取Excel文件对象
    Workbook rwb = Workbook.getWorkbook(file.getInputStream());
    // 获取文件的指定工作表 默认的第一个
    Sheet sheet = rwb.getSheet(0);
    Template template = this.getTemplate();
    if (template.getColumns().size() != sheet.getColumns())
        throw new RuntimeException(String.format("导入的文件：<b>%s</b>, 其总列数为 %d，而模版总列数为  %d 二者不一致，无法导入！", file.getName(), sheet.getColumns(), template.getColumns().size()));
    DataSet ds = new DataSet();
    for (int row = 0; row < sheet.getRows(); row++) {
        if (row == 0) {
            for (int col = 0; col < sheet.getColumns(); col++) {
                Cell cell = sheet.getCell(col, row);
                String value = cell.getContents();
                String title = template.getColumns().get(col).getName();
                if (!title.equals(value))
                    throw new RuntimeException(String.format("导入的文件：<b>%s</b>，其标题第 %d 列为【 %s】, 模版中为【%s】，二者不一致，无法导入！", file.getName(), col + 1, value, title));
            }
        } else {
            ds.append();
            for (int col = 0; col < sheet.getColumns(); col++) {
                Cell cell = sheet.getCell(col, row);
                String value = cell.getContents();
                if (cell.getType() == CellType.NUMBER) {
                    NumberCell numberCell = (NumberCell) cell;
                    double d = numberCell.getValue();
                    value = formatFloat("0.######", d);
                }
                Column column = template.getColumns().get(col);
                if (!column.validate(row, col, value)) {
                    ColumnValidateException err = new ColumnValidateException("其数据不符合模版要求，当前值为：" + value);
                    err.setTitle(column.getName());
                    err.setValue(value);
                    err.setCol(col);
                    err.setRow(row);
                    if (errorHandle == null || !errorHandle.process(err))
                        throw err;
                }
                ds.setField(column.getCode(), value);
            }
            if (readHandle != null && !readHandle.process(ds.getCurrent()))
                break;
        }
    }
    return ds;
}

Example 3 with Sheet

use of jxl.Sheet in project pentaho-kettle by pentaho.

the class ExcelOutput method isTemplateContained.

private boolean isTemplateContained(Workbook templateWorkbook, File targetFile) throws IOException, BiffException {
    Workbook targetFileWorkbook = Workbook.getWorkbook(targetFile);
    int templateWorkbookNumberOfSheets = templateWorkbook.getNumberOfSheets();
    int targetWorkbookNumberOfSheets = targetFileWorkbook.getNumberOfSheets();
    if (templateWorkbookNumberOfSheets > targetWorkbookNumberOfSheets) {
        return false;
    }
    for (int worksheetNumber = 0; worksheetNumber < templateWorkbookNumberOfSheets; worksheetNumber++) {
        Sheet templateWorkbookSheet = templateWorkbook.getSheet(worksheetNumber);
        Sheet targetWorkbookSheet = targetFileWorkbook.getSheet(worksheetNumber);
        int templateWorkbookSheetColumns = templateWorkbookSheet.getColumns();
        int targetWorkbookSheetColumns = targetWorkbookSheet.getColumns();
        if (templateWorkbookSheetColumns > targetWorkbookSheetColumns) {
            return false;
        }
        int templateWorkbookSheetRows = templateWorkbookSheet.getRows();
        int targetWorkbookSheetRows = targetWorkbookSheet.getRows();
        if (templateWorkbookSheetRows > targetWorkbookSheetRows) {
            return false;
        }
        for (int currentRowNumber = 0; currentRowNumber < templateWorkbookSheetRows; currentRowNumber++) {
            Cell[] templateWorkbookSheetRow = templateWorkbookSheet.getRow(currentRowNumber);
            Cell[] targetWorkbookSheetRow = targetWorkbookSheet.getRow(currentRowNumber);
            templateWorkbookSheetRow.toString();
            targetWorkbookSheetRow.toString();
            if (templateWorkbookSheetRow.length > targetWorkbookSheetRow.length) {
                return false;
            }
            for (int currentCellNumber = 0; currentCellNumber < templateWorkbookSheetRow.length; currentCellNumber++) {
                Cell templateWorksheetCell = templateWorkbookSheetRow[currentCellNumber];
                Cell targetWorksheetCell = targetWorkbookSheetRow[currentCellNumber];
                if (!templateWorksheetCell.getContents().equals(targetWorksheetCell.getContents())) {
                    return false;
                }
            }
        }
    }
    return true;
}

Example 4 with Sheet

use of jxl.Sheet in project pentaho-kettle by pentaho.

the class ExcelOutputTest method testExceptionClosingWorkbook.

@Test
public /**
 * Tests http://jira.pentaho.com/browse/PDI-14420 issue
 */
void testExceptionClosingWorkbook() throws Exception {
    ValueMetaInterface vmi = new ValueMetaString("new_row");
    ExcelOutputData data = new ExcelOutputData();
    int[] ints = { 0 };
    data.fieldnrs = ints;
    RowMeta rowMetaToBeReturned = Mockito.spy(new RowMeta());
    rowMetaToBeReturned.addValueMeta(0, vmi);
    data.previousMeta = rowMetaToBeReturned;
    ExcelOutput excelOutput = Mockito.spy(new ExcelOutput(helper.stepMeta, data, 0, helper.transMeta, helper.trans));
    excelOutput.first = false;
    Object[] row = { new Date() };
    doReturn(row).when(excelOutput).getRow();
    doReturn(rowMetaToBeReturned).when(excelOutput).getInputRowMeta();
    ExcelOutputMeta meta = createStepMeta();
    excelOutput.init(meta, data);
    excelOutput.processRow(meta, data);
    excelOutput.dispose(meta, data);
    excelOutput.init(meta, data);
    excelOutput.processRow(meta, data);
    excelOutput.dispose(meta, data);
    Workbook workbook = Workbook.getWorkbook(XLS_FILE);
    Sheet sheet = workbook.getSheet(0);
    int rows = sheet.getRows();
    Assert.assertSame(rows, 2);
}

Example 5 with Sheet

use of jxl.Sheet 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;
}