excel 解析 xls 和 xlsx

来源：互联网发布：大连知润信息科技骗局编辑：程序博客网时间：2024/06/06 03:37
1.注意：在使用Apache POI操作Excel2007 的时候，POI操作Excel2007的org.apache.xmlbeans.xmlexception 原因：默认官方下载的POI包中没有包含这个 xmlbeans.jar。
2.NoSuchMethodError: javax.xml.stream.XMLEventFactory.newFactory() ，XMLEventFactory.newFactory()这个API是在JDK 1.6.0.18加入的，你得确保你使用的JDK高于这个版本。
本实例： poi3.11.jar xssfwORK
/**    *  Excel文件解析  xls xlsx  * @author * @version    * @param  */   public class ExcelReader {        private POIFSFileSystem fs;        private HSSFWorkbook wb;        private HSSFSheet sheet;        private HSSFRow row;        private HSSFCell cell;        private XSSFWorkbook xss;    private XSSFSheet xssSheet;    private XSSFRow  xssRow;    private XSSFCell xssCell;        public void readXls(InputStream is) throws IOException {           fs = new POIFSFileSystem(is);            wb = new HSSFWorkbook(fs);         List<Map<String,String>> list = new ArrayList<Map<String,String>>();        //循环工作表        for (int numSheet=0; numSheet<wb.getNumberOfSheets();numSheet++)        {            sheet = wb.getSheetAt(numSheet);           if(sheet==null)        {        continue;        }                //循环行        for(int rowNum=0; rowNum<sheet.getLastRowNum();rowNum++)        {        row = sheet.getRow(rowNum);        if(row==null)        {        continue;        }                //循环列 单元格        for(int cellNum =0; cellNum< row.getLastCellNum();cellNum++)        {        cell = row.getCell(cellNum);         if(cell==null)        {        continue;        }        System.out.println(" "+getCellValue(cell));        }        }        }    }            public List<Map<String,String>> getXls(InputStream is) throws IOException {                       fs = new POIFSFileSystem(is);            wb = new HSSFWorkbook(fs);         List<Map<String,String>> list = new ArrayList<Map<String,String>>();        List<String> titleList = new ArrayList<String>();        Map<String,String> map = null;        //循环工作表                   sheet = wb.getSheetAt(0);                   //循环行        for(int rowNum=0; rowNum<sheet.getLastRowNum();rowNum++)        {        row = sheet.getRow(rowNum);        if(row==null)        {        continue;        }                //循环列 单元格        for(int cellNum =0; cellNum< row.getLastCellNum();cellNum++)        {                cell = row.getCell(cellNum);         if(cell==null)        {        continue;        }                if(rowNum==0)        {        titleList.add(getCellValue(cell));        }else{                 map =  new HashMap<String,String>();         map.put(titleList.get(cellNum) , getCellValue(cell));        }          System.out.println(" "+getCellValue(cell));        }        list.add(map);        }                return list;        }            /*     * 获取cell的内容， 并将其转成String     * param  HSSFCell     * DecimalFormat df = new DecimalFormat("0");// 格式化 number String 字符          * SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串          * DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字      */    public String getCellValue(HSSFCell cell) {            String strCell = "";            switch (cell.getCellType()) {            case HSSFCell.CELL_TYPE_STRING:                strCell = cell.getStringCellValue();               break;            case HSSFCell.CELL_TYPE_NUMERIC:            DecimalFormat df = new DecimalFormat("0");  //数字过长， excel 会把数字变成科学计数法的形式，故需格式化           strCell = String.valueOf(df.format(cell.getNumericCellValue()));                 break;            case HSSFCell.CELL_TYPE_BOOLEAN:                strCell = String.valueOf(cell.getBooleanCellValue());                break;            case HSSFCell.CELL_TYPE_BLANK:                strCell = "";                break;           default:                strCell = "";                break;            }                  return strCell;        }          //-------------------------------------------------------------------------------------         // NoSuchMethodError: javax.xml.stream.XMLEventFactory.newFactory()   //XMLEventFactory.newFactory()这个API是在JDK 1.6.0.18加入的，你得确保你使用的JDK高于这个版本         public String getXssCellValue(XSSFCell cell) {            String strCell = "";            switch (cell.getCellType()) {            case XSSFCell.CELL_TYPE_STRING:                strCell = cell.getStringCellValue();               break;            case XSSFCell.CELL_TYPE_NUMERIC:            DecimalFormat df = new DecimalFormat("0");  //数字过长， excel 会把数字变成科学计数法的形式，故需格式化           strCell = String.valueOf(df.format(cell.getNumericCellValue()));                 break;            case XSSFCell.CELL_TYPE_BOOLEAN:                strCell = String.valueOf(cell.getBooleanCellValue());                break;            case XSSFCell.CELL_TYPE_BLANK:                strCell = "";                break;           default:                strCell = "";                break;            }                  return strCell;        }              public void readXlsx(InputStream is) throws IOException {           xss = new XSSFWorkbook(is);        List<Map<String,String>> list = new ArrayList<Map<String,String>>();        //循环工作表        for (int numSheet=0; numSheet<xss.getNumberOfSheets();numSheet++)        {            xssSheet = xss.getSheetAt(numSheet);           if(xssSheet==null)        {        continue;        }                //循环行        for(int rowNum=0; rowNum<xssSheet.getLastRowNum();rowNum++)        {        xssRow = xssSheet.getRow(rowNum);        if(xssRow==null)        {        continue;        }                //循环列 单元格        for(int cellNum =0; cellNum< xssRow.getLastCellNum();cellNum++)        {        xssCell = xssRow.getCell(cellNum);         if(xssCell==null)        {        continue;        }        System.out.println(" "+getXssCellValue(xssCell));        }        }        }    }            public List<Map<String,String>> getXlsx(InputStream is) throws IOException {                      XSSFWorkbook  xss = new XSSFWorkbook(is);        List<Map<String,String>> list = new ArrayList<Map<String,String>>();        List<String> titleList = new ArrayList<String>();        Map<String,String> map = null;        //循环工作表                   xssSheet = xss.getSheetAt(0);                   //循环行        for(int rowNum=0; rowNum<xssSheet.getLastRowNum();rowNum++)        {        xssRow = xssSheet.getRow(rowNum);        if(xssRow==null)        {        continue;        }                //循环列 单元格        for(int cellNum =0; cellNum< xssRow.getLastCellNum();cellNum++)        {                xssCell = xssRow.getCell(cellNum);         if(xssCell==null)        {        continue;        }                if(rowNum==0)        {        titleList.add(getXssCellValue(xssCell));        }else{                 map =  new HashMap<String,String>();         map.put(titleList.get(cellNum) , getXssCellValue(xssCell));        }          System.out.println(" "+getXssCellValue(xssCell));        }        list.add(map);        }                return list;        }
注意：xls文件， xlsx文件解析必须要分别对应 HSSFWorkbook, XSSFWorkbook 对象，
xls文件直接改扩展名去解析各种异常。。。。
0 0