POI使用心得

1.      刚开始的时候用的是jxl.jar 感觉还挺好用

但是接着问题来了  它不能解析 excel2007 这个原因是它本是韩国的组织写的jar包，但是现在已经不再提供更新，所以只能解析excel2003的

2．知道原因后我就开始找替代的办法，最终在网上看到apache的poi可以解决这个问题，于是赶紧看解决办法，然后下载jar包等，但最后遇到2个问题

   1.类型问题，郁闷的不行靠，弄什么类型啊，让我老是出错，最终解决办法是将其全部转化为string的就可以 .toString.

   2.这个问题也够头疼的 什么xml反射异常，我弄了老半天都不知道怎么回事，最后在网上查看很久原来是少jar包，或jar包冲突导致的。根据看我的jar包分布没有冲突，最后看到原来是少一个jar 就是这个jar包！

3.有个问题是如果是数字 就会多个。0

解决办法是：

 

if (null != rows) {

                     IncomeType income = new IncomeType();

                     for (int k = 0; k <= rows.getLastCellNum(); k++) {

                         XSSFCell cell = rows.getCell(k);

                         if (null != cell) {

                            rows.getCell(0).setCellType(Cell.CELL_TYPE_STRING);

                            income.setCode(rows.getCell(0).toString());

                            income.setName(rows.getCell(1).toString());

                         }

                     }

                     incomeTypeList.add(income);

 

 

 

4．org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet

出现这个错误的原因也是少了个jar 这个jar名字是：

 

网上的解释：

 

使用POI中的XSSFWorkbook操作excel2007（xlsx）的异常：找不到类解决  
最近使用POI中的XSSFWorkbook操作excel2007（xlsx）的时候抛出以下异常：
严重: Servlet.service() for servletStaffIpRestrictServlet threw exception
java.lang.ClassNotFoundException:org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet
atorg.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1680)

解决方案：
poi包中默认不支持excel2007，如果需要解析，则需要引入poi-ooxml-schemas-xx.jar包。

引入此包后，可能还会有个异常，原因是找不到dom4j的jar包。
因为poi-ooxml-schemas-xx.jar需要依赖dom4j-xxx.jar包

 

 

代码：

 

 

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.xssf.usermodel.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

 

public String readExcel() {

       if (file == null) {

           return ajaxJsonErrorMessage("请选择上传的文件!");

       }

       if (!fileFileName.endsWith(".xls") && !fileFileName.endsWith(".xlsx")) {

           return ajaxJsonErrorMessage("请选择Excel文件!");

       }

       List<IncomeType> incomeTypeList = new ArrayList<IncomeType>();

       if (fileFileName.endsWith(".xls")) {

           HSSFWorkbook wbs = null;

           try {

              FileInputStream is = new FileInputStream(file);

              wbs = new HSSFWorkbook(is);

              HSSFSheet childSheet = wbs.getSheetAt(0);

              for (int j = 1; j <= childSheet.getLastRowNum(); j++) {

                  HSSFRow row = childSheet.getRow(j);

                  if (null != row) {

                     IncomeType income = new IncomeType();

                     for (int k = 0; k <= row.getLastCellNum(); k++) {

                         HSSFCell cell = row.getCell(k);

                         if (null != cell) {

                            income.setCode(row.getCell(0).toString());

                            income.setName(row.getCell(1).toString());

                         }

                     }

                     incomeTypeList.add(income);

                  }

              }

           } catch (Exception e) {

              e.printStackTrace();

           }

       }

       if (fileFileName.endsWith(".xlsx")) {

           XSSFWorkbook wbs = null;

           try {

              FileInputStream is = new FileInputStream(file);

              wbs = new XSSFWorkbook(is);

              XSSFSheet childSheet = wbs.getSheetAt(0);

              for (int j = 1; j <= childSheet.getLastRowNum(); j++) {

                  XSSFRow rows = childSheet.getRow(j);

                  if (null != rows){

                     IncomeType income = new IncomeType();

                     for (int k = 0; k <= rows.getLastCellNum(); k++) {

                         XSSFCell cell = rows.getCell(k);

                         if (null != cell) {

                            rows.getCell(0).setCellType (Cell.CELL_TYPE_STRING);

                            income.setCode(rows.getCell(0).toString());

                            income.setName(rows.getCell(1).toString());

                         }

                     }

                     incomeTypeList.add(income);

                  }

              }

           } catch (Exception e) {

              e.printStackTrace();

           }

       }

       Iterator<IncomeType> iter = incomeTypeList.iterator();

       while (iter.hasNext()) {

           IncomeType incomeType = iter.next();

           if (incomeTypeService.isExist("code", incomeType.getCode())) {

              return ajaxJsonErrorMessage("收入类别编号" + incomeType.getCode()

                     + "已存在请更换!");

           }

       }

       Iterator<IncomeType> iterSave =incomeTypeList.iterator();

       int successNum = 0;

       while (iterSave.hasNext()) {

           IncomeType incomeType = iterSave.next();

           incomeTypeService.save(incomeType);

           successNum++;

       }

       return ajaxJsonSuccessMessage("上传成功" + successNum + "条!",

              "incomeType_list", true);

    }