Examples with Sheet jxl.Sheet used on opensource projects

Example 11 with Sheet

use of jxl.Sheet in project Saturn by vipshop.

the class JobServiceImpl method importJobs.

@Override
public List<ImportJobResult> importJobs(String namespace, MultipartFile file, String createdBy) throws SaturnJobConsoleException {
    try {
        Workbook workbook = Workbook.getWorkbook(file.getInputStream());
        Sheet[] sheets = workbook.getSheets();
        List<JobConfig> jobConfigList = new ArrayList<>();
        // 先获取数据并检测内容格式的正确性
        for (int i = 0; i < sheets.length; i++) {
            Sheet sheet = sheets[i];
            int rows = sheet.getRows();
            for (int row = 1; row < rows; row++) {
                Cell[] rowCells = sheet.getRow(row);
                // 如果这一行的表格全为空，则跳过这一行。
                if (!isBlankRow(rowCells)) {
                    jobConfigList.add(convertJobConfig(i + 1, row + 1, rowCells));
                }
            }
        }
        int maxJobNum = getMaxJobNum();
        if (jobIncExceeds(namespace, maxJobNum, jobConfigList.size())) {
            throw new SaturnJobConsoleException(String.format("总作业数超过最大限制(%d)，导入失败", maxJobNum));
        }
        // 再进行添加
        List<ImportJobResult> results = new ArrayList<>();
        for (JobConfig jobConfig : jobConfigList) {
            ImportJobResult importJobResult = new ImportJobResult();
            importJobResult.setJobName(jobConfig.getJobName());
            try {
                addJob(namespace, jobConfig, createdBy);
                importJobResult.setSuccess(true);
            } catch (SaturnJobConsoleException e) {
                importJobResult.setSuccess(false);
                importJobResult.setMessage(e.getMessage());
                log.warn("exception: {}", e);
            } catch (Exception e) {
                importJobResult.setSuccess(false);
                importJobResult.setMessage(e.toString());
                log.warn("exception: {}", e);
            }
            results.add(importJobResult);
        }
        return results;
    } catch (SaturnJobConsoleException e) {
        throw e;
    } catch (Exception e) {
        throw new SaturnJobConsoleException(e);
    }
}

Example 12 with Sheet

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

Example 13 with Sheet

use of jxl.Sheet in project trainning by fernandotomasio.

the class ImportNiveisAprendizagemCSV method execute.

@Override
public void execute() {
    BufferedReader br = null;
    String line;
    String cvsSplitBy = "\\|";
    Map<String, DominioAprendizagemDTO> dominiosMap = new HashMap<String, DominioAprendizagemDTO>();
    Map<String, List<String>> verbosMap = new HashMap<String, List<String>>();
    try {
        br = new BufferedReader(new FileReader(new ClassPathResource("verbos.csv").getFile()));
        while ((line = br.readLine()) != null) {
            // use comma as separator
            String[] data = line.split(cvsSplitBy);
            String verbo = data[0];
            String nivel = data[1];
            List<String> verbos = verbosMap.get(nivel);
            if (verbos != null) {
                verbos.add(verbo);
            } else {
                verbos = new ArrayList<String>();
                verbos.add(verbo);
                verbosMap.put(nivel, verbos);
            }
        }
        WorkbookSettings ws = new WorkbookSettings();
        ws.setEncoding("Cp1252");
        Workbook workbook = Workbook.getWorkbook(new ClassPathResource("niveis.xls").getInputStream(), ws);
        Sheet sheet = workbook.getSheet(0);
        for (int i = 0; i < sheet.getRows(); i++) {
            String nome = sheet.getCell(0, i).getContents();
            String codigo = sheet.getCell(1, i).getContents();
            String descricao = sheet.getCell(2, i).getContents();
            String dominio = sheet.getCell(3, i).getContents();
            NivelAprendizagemDTO nivel = new NivelAprendizagemDTO();
            nivel.setNome(nome);
            nivel.setCodigo(codigo);
            nivel.setDescricao(descricao);
            List<String> verbos = verbosMap.get(nome);
            String[] verbosArray = new String[verbos.size()];
            for (int j = 0; j < verbos.size(); j++) {
                verbosArray[j] = verbos.get(j);
            }
            nivel.setVerbos(verbosArray);
            DominioAprendizagemDTO dominioAprendizagem = dominiosMap.get(dominio);
            if (dominioAprendizagem == null) {
                DominioAprendizagemDTO d = new DominioAprendizagemDTO();
                d.setDescricao(dominio);
                Long dominioId = service.createDominioAprendizagem(d);
                d = service.findDominioAprendizagem(dominioId);
                dominiosMap.put(dominio, d);
                nivel.setDominioAprendizagem(d);
            } else {
                nivel.setDominioAprendizagem(dominioAprendizagem);
            }
            service.createNivelAprendizagem(nivel);
            System.out.println(nome);
        }
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    } catch (BiffException ex) {
        Logger.getLogger(ImportNiveisAprendizagemCSV.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        if (br != null) {
            try {
                br.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
    System.out.println("Done");
}

Example 14 with Sheet

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