日期:2014-05-20  浏览次数:20777 次

Java操作Excel???
是否有人对Java操作Excel比较熟悉的求赐教?

------解决方案--------------------
jxl插件比较好用,网上有好多实例,可以找一下

http://download.csdn.net/detail/VCXIAOHEI/2164189
------解决方案--------------------
下面是我们项目中用到的,我自己写的读取excel,是用poi,excel版本不同,读取会有一点区别,我标注了,lz可以参考下
Java code

public List<Map<String, Object>> loadChitData(File excelFile,
            boolean isDelete) throws BusinessException {
        if (!excelFile.exists()) {
            throw new BusinessException("要读取的文件不存在");
        }

        List<Map<String, Object>> chits = new ArrayList<Map<String, Object>>();
        InputStream stream = null;
        boolean errFlag = false;
        try {
            stream = new FileInputStream(excelFile);
            Map<String, Integer> orgs = getOrgs(); // 所有部门
            Workbook wb = null;
            if(excelFile.getName().endsWith("xls")){
                wb = new HSSFWorkbook(stream);   //读取2003
            }else{
                wb = new XSSFWorkbook(stream);    //读取2007
            }
            if (wb.getNumberOfSheets() == 0) {
                throw new BusinessException("要导入的Excel文件中不存在数据");
            }
            Sheet sheet = wb.getSheetAt(0);
            
            Set<String> numSet = new HashSet<String>();
            int rowIndex = 1;
            boolean isNext = true;
              while(isNext){
                int lineNo = rowIndex+1;
                Row row = sheet.getRow(rowIndex);
                
                Map<String, Object> chit = new HashMap<String, Object>();
                if (row.getCell(0) == null || row.getCell(0).getStringCellValue().equals("")) {
                    throw new BusinessException("导入的代金券第" + lineNo + "行名称为空");
                } else {
                    String name = row.getCell(0).getStringCellValue();
                    chit.put("name", name);
                }

                
                if (row.getCell(1) == null || row.getCell(1).getStringCellValue().equals("")) {
                    throw new BusinessException("导入的代金券第" + lineNo + "行条码为空");
                }else{
                    String num = row.getCell(1).getStringCellValue();
                    numSet.add(num);
                    chit.put("num", num); // 代金券面值
                }
                
                if (row.getCell(2) == null || row.getCell(2).getNumericCellValue() == 0.0) {
                    throw new BusinessException("导入的代金券第" + lineNo + "行面值为空");
                } else {
                    String faceValue = row.getCell(2).getNumericCellValue()+"";
                    BigDecimal DecFaceValue = new BigDecimal(faceValue);
                    chit.put("faceValue", DecFaceValue);
                }

                // 所属部门编号
                String orgName = row.getCell(3)!=null?row.getCell(3).getStringCellValue():"";
                
                if (orgs.containsKey(orgName)) {
                    int orgId = orgs.get(orgName);
                    chit.put("orgId", orgId);
                } else {
                    chit.put("orgId", null);
                }

                chit.put("orgName",orgName);

                String indate = dateFormat(row.getCell(4).getDateCellValue());
                if (indate == null) {
                    throw new BusinessException("导入的代金券第" + lineNo + "行有效期为空");
                } else {
                    
                    chit.put("indate", indate);
                }
                String onsetDate = dateFormat(row.getCell(5).getDateCellValue());
                if (onsetDate == null) {
                    throw new BusinessException("导入的代金券第" + lineNo + "行生效时间为空");
                } else {
                    
                    chit.put("onsetDate", onsetDate);
                }
                chit.put("remark", row.getCell(6)!=null?row.getCell(6).getStringCellValue():"");
                chits.add(chit);
                rowIndex = rowIndex + 1;
                row = sheet.getRow(rowIndex);
                if(row == null){
                    isNext = false;
                    rowIndex = rowIndex - 1;
                }
            }

            if (rowIndex > numSet.size()) {
                throw new BusinessException("导入代金券条码不能重复");
            }
        } catch (BusinessException e) {
            errFlag = true;
            throw e;
        } catch (Exception e) {
            errFlag = true;
            throw new BusinessException(e.getMessage());
        }

        finally {
            if (isDelete || errFlag) {
                // 关闭文件流
                try {
                    if (stream != null) {
                        stream.close();
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }

                excelFile.delete();
            }
        }

        return chits;
    }