Examples with Sheet jxl.Sheet used on opensource projects

Example 6 with Sheet

use of jxl.Sheet in project Saturn by vipshop.

the class ExecutorController method batchAddJobs.

@RequestMapping(value = "batchAddJobs", method = RequestMethod.POST)
public RequestResult batchAddJobs(MultipartHttpServletRequest request) {
    RequestResult result = new RequestResult();
    int successCount = 0;
    int failCount = 0;
    String failMessage = "";
    try {
        Iterator<String> fileNames = request.getFileNames();
        MultipartFile file = null;
        while (fileNames.hasNext()) {
            if (file != null) {
                result.setSuccess(false);
                result.setMessage("仅支持单文件导入");
                return result;
            }
            file = request.getFile(fileNames.next());
        }
        if (file == null) {
            result.setSuccess(false);
            result.setMessage("请选择导入的文件");
            return result;
        }
        String originalFilename = file.getOriginalFilename();
        if (originalFilename == null || !originalFilename.endsWith(".xls")) {
            result.setSuccess(false);
            result.setMessage("仅支持.xls文件导入");
            return result;
        }
        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 = executorService.getMaxJobNum();
        if (executorService.jobIncExceeds(maxJobNum, jobConfigList.size())) {
            String errorMsg = String.format("总作业数超过最大限制(%d)，导入失败", maxJobNum);
            result.setSuccess(false);
            result.setMessage(errorMsg);
            return result;
        }
        // 再进行添加
        for (JobConfig jobConfig : jobConfigList) {
            RequestResult addJobResult = executorService.addJobs(jobConfig);
            if (addJobResult.isSuccess()) {
                successCount++;
            } else {
                failCount++;
                failMessage += " [" + addJobResult.getMessage() + "]";
            }
        }
    } catch (SaturnJobConsoleException e) {
        result.setSuccess(false);
        result.setMessage("导入失败，" + e.getMessage());
        return result;
    } catch (Exception e) {
        result.setSuccess(false);
        result.setMessage("导入失败，错误信息：" + e.toString());
        return result;
    }
    result.setSuccess(true);
    if (failCount > 0) {
        result.setMessage("共导入" + successCount + "个作业，忽略" + failCount + "个。错误信息：" + failMessage);
    } else {
        result.setMessage("共导入" + successCount + "个作业，忽略0个");
    }
    return result;
}

Example 7 with Sheet

use of jxl.Sheet in project portal by ixinportal.

the class BillReceiptController method uploadExcelFile.

// 上传Excel
@RequestMapping(params = "uploadExcel", method = RequestMethod.POST, produces = "text/html")
public String uploadExcelFile(MultipartFile excelFile, Model uiModel) throws IOException, BiffException {
    String fileName = excelFile.getOriginalFilename();
    // 文件类型
    String fileType = FilenameUtils.getExtension(fileName);
    if ((!fileType.toLowerCase().equals("xls")) && (!fileType.toLowerCase().equals("xlsx"))) {
        uiModel.addAttribute("error", "上传失败，上传的文件不是以‘.xls’或‘.xlsx’文件名结尾");
        return "billreceipt/import";
    }
    // CommonsMultipartFile cf= (CommonsMultipartFile)excelFile;
    // DiskFileItem fi = (DiskFileItem)cf.getFileItem();
    // File exFl = fi.getStoreLocation();
    // 读取excel文件
    Workbook book = Workbook.getWorkbook(excelFile.getInputStream());
    // 这里是获取第一个工作表格
    Sheet sheet = book.getSheet(0);
    // 获取总的行数
    int rows = sheet.getRows();
    // 获取总的列数
    int cols = sheet.getColumns();
    // System.out.println("行："+rows+",列："+cols);
    Cell cell;
    String[] id = new String[rows];
    String[] isCheck = new String[rows];
    String[] receiptName = new String[rows];
    String[] receiptSum = new String[rows];
    int a = 0;
    int b = 0;
    for (int i = 0; i < rows; i++) {
        // 读取单元格内容并存放到二维数组中 默认从第一行第一列读取
        cell = sheet.getCell(1, i);
        id[i] = cell.getContents().replace(",", "").trim();
        cell = sheet.getCell(5, i);
        receiptName[i] = cell.getContents();
        cell = sheet.getCell(6, i);
        receiptSum[i] = cell.getContents();
        cell = sheet.getCell(7, i);
        isCheck[i] = cell.getContents();
        if (isCheck[i].equals("是")) {
            b = b + 1;
            Bill bill = sqlSession.selectOne("com.itrus.portal.db.BillMapper.selectByBillId", id[i]);
            if (bill != null) {
                bill.setIsInvoiced(1);
                a = a + 1;
                sqlSession.update("com.itrus.portal.db.BillMapper.updateByPrimaryKeySelective", bill);
                Invoice iv = sqlSession.selectOne("com.itrus.portal.db.InvoiceMapper.selectByPrimaryKey", bill.getInvoice());
                iv.setName(receiptName[i]);
                iv.setInvoiceSum(Double.parseDouble(receiptSum[i]));
                iv.setConfirmTime(new Date());
                sqlSession.update("com.itrus.portal.db.InvoiceMapper.updateByPrimaryKeySelective", iv);
                String oper = "确认开票";
                String info = "订单号: " + bill.getBillId();
                LogUtil.adminlog(sqlSession, oper, info);
            }
        }
    }
    // for(int i=1;i<id.length;i++){
    // System.out.println(id[i]+" "+isCheck[i]);
    // }
    int c = id.length - 1 - b;
    uiModel.addAttribute("a", a);
    uiModel.addAttribute("b", b);
    uiModel.addAttribute("c", c);
    return "billreceipt/import";
}

Example 8 with Sheet

use of jxl.Sheet in project portal by ixinportal.

the class ImportBankInformationController method uploadExcelFile.

// 導入功能
@RequestMapping(params = "uploadExcel", method = RequestMethod.POST, produces = "text/html")
public String uploadExcelFile(MultipartFile excelFile, Model uiModel) throws IOException, BiffException {
    String fileName = excelFile.getOriginalFilename();
    // 文件类型
    String fileType = FilenameUtils.getExtension(fileName);
    if ((!fileType.toLowerCase().equals("xls")) && (!fileType.toLowerCase().equals("xlsx"))) {
        uiModel.addAttribute("error", "上传失败，上传的文件不是以‘.xls’或‘.xlsx’文件名结尾");
        return "importbankinformation/import";
    }
    try {
        // 读取excel文件
        Workbook book = Workbook.getWorkbook(excelFile.getInputStream());
        // 这里是获取第一个工作表格
        Sheet sheet = book.getSheet(0);
        // 获取总的行数
        int rows = sheet.getRows();
        // 获取总的列数
        int cols = sheet.getColumns();
        // System.out.println("行："+rows+",列："+cols);
        Cell cell;
        String[] id = new String[rows];
        String[] receiptName = new String[rows];
        // System.out.println(rows);
        // System.out.println(cols);
        int g = 0;
        // if(cols==3){
        for (int i = 1; i < rows; i++) {
            // 读取单元格内容并存放到二维数组中 默认从第一行第一列读取
            // 创建一个银行对象
            ImportBankInformation importBankInformation = new ImportBankInformation();
            // 获取第i行第一列
            cell = sheet.getCell(1, i);
            // 赋值给id[i]
            id[i] = cell.getContents();
            // 赋值给
            importBankInformation.setBankName(id[i]);
            cell = sheet.getCell(2, i);
            receiptName[i] = cell.getContents();
            importBankInformation.setBankCode(receiptName[i]);
            // System.out.println("                "+id[i]);
            // System.out.println(receiptName[i]);
            // 查询数据库数据
            ImportBankInformation importBankInformation1 = sqlSession.selectOne("com.itrus.portal.db.ImportBankInformationMapper.selectByBillId", id[i]);
            // 进行判断如果数据库没有则进行添加
            if (importBankInformation1 == null) {
                sqlSession.insert("com.itrus.portal.db.ImportBankInformationMapper.insert", importBankInformation);
                g++;
            }
        }
        if (g != 0) {
            uiModel.addAttribute("import", "导入成功，导入" + g + "条记录");
        } else {
            uiModel.addAttribute("import", "导入数据已存在");
        }
    // }else{
    // uiModel.addAttribute("import", "导入数据列数不符合");
    // }
    } catch (Exception e) {
        e.printStackTrace();
        uiModel.addAttribute("import", "导入数据格式不正确");
    }
    return "importbankinformation/import";
}

Example 9 with Sheet

use of jxl.Sheet in project aws-doc-sdk-examples by awsdocs.

the class ExcelService method getData.

public String getData(String bucketName, String object) throws IOException, BiffException {
    // Get the Excel speadsheet from the Amazon S3 bucket.
    S3Service s3Service = new S3Service();
    byte[] data = s3Service.getObjectBytes(bucketName, object);
    InputStream inputStrean = new ByteArrayInputStream(data);
    List<PopData> myList = new ArrayList();
    System.out.println("Retrieving data from the Excel Spreadsheet");
    Workbook wb = Workbook.getWorkbook(inputStrean);
    Sheet sheet = wb.getSheet(0);
    try {
        // Read the data from the excel spreadsheet.
        Sheet s = wb.getSheet(0);
        int b = s.getColumns();
        System.out.println("The No. of Columns in the Sheet are = " + b);
        int a = s.getRows();
        System.out.println("The No. of Rows in the sheet are = " + a);
        PopData popData = null;
        // Loop through the rows in the spreadsheet.
        for (int zz = 0; zz < a; zz++) {
            // Get the first cell.
            System.out.println(zz);
            Cell[] row = sheet.getRow(zz);
            if (zz == 0)
                System.out.println("Not 1st row");
            else {
                popData = new PopData();
                for (Cell cell : row) {
                    int colIndex = cell.getColumn();
                    String val = cell.getContents();
                    switch(colIndex) {
                        case 0:
                            popData.setName(val);
                            break;
                        case 1:
                            popData.setCode(val);
                            break;
                        case 2:
                            popData.set2010(val);
                            break;
                        case 3:
                            popData.set2011(val);
                            break;
                        case 4:
                            popData.set2012(val);
                            break;
                        case 5:
                            popData.set2013(val);
                            break;
                        case 6:
                            popData.set2014(val);
                            break;
                        case 7:
                            popData.set2015(val);
                            break;
                        case 8:
                            popData.set2016(val);
                            break;
                        case 9:
                            popData.set2017(val);
                            break;
                        case 10:
                            popData.set2018(val);
                            break;
                        default:
                            {
                                popData.set2019(val);
                                myList.add(popData);
                            }
                    }
                }
            }
        }
        myList.sort(Comparator.comparing(PopData::getName));
        String transformXML = convertToString(toXml(myList));
        return transformXML;
    } catch (Exception e) {
        e.printStackTrace();
    }
    return "";
}

Example 10 with Sheet

use of jxl.Sheet in project cubrid-manager by CUBRID.

the class PstmtDataTask method executeFromXls.

/**
	 * Do with data from excel file
	 *
	 * @param monitor IProgressMonitor
	 */
private void executeFromXls(IProgressMonitor monitor) {
    // FIXME move this logic to core module
    try {
        XLSImportFileHandler fileHandler = (XLSImportFileHandler) importFileHandler;
        Sheet[] sheets = fileHandler.getSheets();
        ImportFileDescription fileDesc = fileHandler.getSourceFileInfo();
        int rowNum = 0;
        int currentRow = 0;
        for (int sheetNum = 0; sheetNum < sheets.length; sheetNum++) {
            int start = 0;
            int lastRowNum = rowNum;
            int rows = fileDesc.getItemsNumberOfSheets().get(sheetNum);
            if (isFirstRowAsColumn) {
                rowNum += rows - 1;
            } else {
                rowNum += rows;
            }
            if (startRow > rowNum) {
                continue;
            }
            if (lastRowNum >= startRow) {
                start = isFirstRowAsColumn ? 1 : 0;
            } else {
                start = startRow - lastRowNum + (isFirstRowAsColumn ? 1 : 0);
            }
            Sheet sheet = sheets[sheetNum];
            String content = null;
            String pattern = null;
            for (int i = start; i < rows && currentRow < rowCount; i++) {
                for (int j = 0; j < parameterList.size(); j++) {
                    PstmtParameter pstmtParameter = parameterList.get(j);
                    int column = Integer.parseInt(pstmtParameter.getStringParamValue());
                    Cell cell = sheet.getCell(column, i);
                    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();
                        }
                    }
                    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()) {
                        PstmtParameter parameter = new PstmtParameter(pstmtParameter.getParamName(), pstmtParameter.getParamIndex(), pstmtParameter.getDataType(), content);
                        parameter.setCharSet(fileCharset);
                        FieldHandlerUtils.setPreparedStatementValue(parameter, pStmt, dbCharset);
                    } else {
                        dataTypeErrorHandling(getErrorMsg(i, column, dataType));
                        PstmtParameter parameter = new PstmtParameter(pstmtParameter.getParamName(), pstmtParameter.getParamIndex(), pstmtParameter.getDataType(), null);
                        parameter.setCharSet(fileCharset);
                        FieldHandlerUtils.setPreparedStatementValue(parameter, pStmt, dbCharset);
                    }
                }
                if (pStmt != null) {
                    pStmt.addBatch();
                    monitor.worked(PROGRESS_ROW);
                    workedProgress += PROGRESS_ROW;
                }
                currentRow++;
                if (currentRow > 0 && currentRow % commitLineCountOnce == 0) {
                    commit(monitor, currentRow);
                }
                if (isCancel) {
                    return;
                }
            }
        }
        if (currentRow > 0 && currentRow % commitLineCountOnce > 0) {
            commit(monitor, currentRow);
        }
    } catch (SQLException ex) {
        throw new RuntimeException(ex);
    } catch (BiffException ex) {
        throw new RuntimeException(ex);
    } catch (IOException ex) {
        throw new RuntimeException(ex);
    } catch (DataFormatException ex) {
        throw new RuntimeException(ex);
    } catch (Exception ex) {
        throw new RuntimeException(ex);
    } catch (OutOfMemoryError error) {
        throw new RuntimeException(error);
    }
}