Jav 利用poi读取excel,javpoi读取excel

   
最近一个web工程需要完成一个小功能,利用文件上传然后读取文件内容写入到数据库,这里是操作的excel文件,excel文件分两种后缀,03版本的xls和之后的xlsx,现在大家一般都拿非常好用的插件直接过来用就可以了,我这里用的是java
poi相关资源包。

POI实现java读取excel

Jav 利用poi读取excel,javpoi读取excel

package com.taotaosou.core.zipcode;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelReader {

    public static HSSFSheet readHssfExcel(String path) {
        File file = new File(path);
        FileInputStream is = null;
        HSSFSheet childSheet = null;
        try {
            is = new FileInputStream(file);
            HSSFWorkbook wbs = new HSSFWorkbook(is);
            childSheet = wbs.getSheetAt(0);

        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
                is = null;
            }
        }

        return childSheet;
    }

    public static XSSFSheet readXssfExcel(String path) {
        File file = new File(path);
        FileInputStream is = null;
        XSSFSheet childSheet = null;
        try {
            is = new FileInputStream(file);
            XSSFWorkbook wbs = new XSSFWorkbook(is);
            childSheet = wbs.getSheetAt(0);

        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
                is = null;
            }
        }

        return childSheet;
    }

    public static XSSFSheet readXssfExcel(FileInputStream is) {
        XSSFSheet childSheet = null;
        try {
            XSSFWorkbook wbs = new XSSFWorkbook(is);
            childSheet = wbs.getSheetAt(0);

        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
                is = null;
            }
        }

        return childSheet;
    }

    public static XSSFSheet readXssfExcel(InputStream is) {
        XSSFSheet childSheet = null;
        try {
            XSSFWorkbook wbs = new XSSFWorkbook(is);
            childSheet = wbs.getSheetAt(0);

        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
                is = null;
            }
        }

        return childSheet;
    }

    public static void main(String[] args) {
        XSSFSheet sheet = ExcelReader
                .readXssfExcel(Thread.currentThread().getContextClassLoader().getResourceAsStream("postcode.xlsx"));

        for (int i = 0, num = sheet.getLastRowNum(); i < num; i++) {
            XSSFRow row = sheet.getRow(i);
            System.out.println(row.getCell(0).getNumericCellValue()); 
            System.out.println(row.getCell(1).getStringCellValue()); 
        }
    }
}

 

利用poi读取excel,javpoi读取excel package
com.taotaosou.core.zipcode; import java.io.File; import
java.io.FileInputStream; import java.io.IOException; import
java.io.Inp…

   1,首先要能够读取带xlsx的excel文件,必须要导入如下的jar包

1.下载POI的jar包 , 云盘下载地址: 

 图片 1

commons-fileupload-1.3.jar
commons-io-2.4.jar
dom4j-1.6.1.jar
poi-3.10-beta2.jar
poi-ooxml-3.10-beta2.jar
poi-ooxml-schemas-3.10-beta2.jar
poi-scratchpad-3.10-beta2.jar
xmlbeans-2.3.0.jar

 
然后遇到的问题是在本地工程下建立的一个测试类测试没有异常,两种excel的文件都能读取,但是一旦发布到web工程下,我的工程是在tomcat下跑的。这里就出现了问题,总是报这个异常:

2.读取excel

Exception in thread “main” org.apache.poi.poifs.filesystem.OfficeXmlFileException:
The supplied data appears to be in the Office 2007+ XML. You are calling
the part of POI that deals with OLE2 Office Documents. You need to call
a different part of POI to process this data (eg XSSF instead of HSSF)

 .xlsx  是2010版本excel  用
XSSFWorkbook处理

 
然后随着异常仔细的往上看,最后发现说有一个xbean.jar是否是正确的版本,这个时候我就知道可能是jar包冲突了,但是这时候对web工程的结构有点不是很明确了,为什么在测试类下没有问题,但为啥在web下跑的时候会产生这个异常呢,究其原因,这里要弄明白,在web下跑的时候,在webRoot下下面的WEB-INF下面的lib放的jar才是工程跑的时候用用到的jar,所以这时候我去找,果然发现有一个xbean.jar跟java
poi的xmlbeans-2.3.0.jar冲突了,果断删掉低版本的xbean.jar,问题解决。

.xls    是2003版本excel   用
HSSFWorkbook处理

所以从遇到这个问题上来看,首先要学会从异常中找原因,其次知道原因了之后如何去解决。异常往往能够暴露出能够解决问题的关键所在。希望能帮到遇到此类似问题的朋友。

 

 

图片 2图片 3

package utils;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.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 class ReadExcel {

    public static List<List<String>> readExcel(String path) throws IOException {
        if (path.endsWith(".xlsx")) {
            return readXlsx(path);
        } else if (path.endsWith(".xls")) {
            return readXlsx(path);
        } else {
            return null;
        }
    }

    /**
     * Read the Excel 2010
     */
    public static List<List<String>> readXlsx(String path) throws IOException {
        InputStream is = new FileInputStream(path);
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);

        List<List<String>> list = new ArrayList<List<String>>();
        // Read the Sheet
        int numSheets = xssfWorkbook.getNumberOfSheets();// 获取sheet页数
        for (int numSheet = 0; numSheet < 1; numSheet++) {
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
            if (xssfSheet == null) {
                continue;
            }
            // Read the Row
            int rowNums = xssfSheet.getLastRowNum();
            for (int rowNum = 0; rowNum <= rowNums; rowNum++) {
                List<String> l = new ArrayList<String>();
                // 获取对应的行数据
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                if (xssfRow != null) {
                    // 获取列数
                    int columnNum = xssfRow.getLastCellNum();
                    for (int cloNum = 0; cloNum < columnNum; cloNum++) {
                        l.add(getValue(xssfRow.getCell(cloNum)));
                    }
                    list.add(l);
                }
            }
        }
        return list;
    }

    /**
     * Read the Excel 2003
     */
    public void readXls(String path) throws IOException {
        InputStream is = new FileInputStream(path);
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);

        List<List<String>> list = new ArrayList<List<String>>();
        // Read the Sheet
        int numSheets = hssfWorkbook.getNumberOfSheets();// 获取sheet页数
        for (int numSheet = 0; numSheet < 1; numSheet++) {
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }
            // Read the Row
            int rowNums = hssfSheet.getLastRowNum();
            for (int rowNum = 0; rowNum <= rowNums; rowNum++) {
                // 获取对应的行数据
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                List<String> l = new ArrayList<String>();
                if (hssfRow != null) {
                    // 获取列数
                    int columnNum = hssfRow.getLastCellNum();
                    for (int cloNum = 0; cloNum < columnNum; cloNum++) {
                        l.add(getValue(hssfRow.getCell(cloNum)));
                    }
                    list.add(l);
                }
            }
        }
    }

    /**
     * 取值2010excel
     * 
     * @param cell
     * @return
     */
    private static String getValue(XSSFCell cell) {
        if (cell == null) {
            return "";
        }

        String value = "";

        switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_STRING:
            value = cell.getStringCellValue();
            break;
        case HSSFCell.CELL_TYPE_NUMERIC:
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                Date date = cell.getDateCellValue();
                if (date != null) {
                    value = new SimpleDateFormat("yyyy-MM-dd").format(date);
                } else {
                    value = "";
                }
            } else {
                value = new DecimalFormat("0").format(cell.getNumericCellValue());
            }
            break;
        case HSSFCell.CELL_TYPE_FORMULA:
            // 导入时如果为公式生成的数据则无值
            if (!cell.getStringCellValue().equals("")) {
                value = cell.getStringCellValue();
            } else {
                value = cell.getNumericCellValue() + "";
            }
            break;
        case HSSFCell.CELL_TYPE_BLANK:
            break;
        case HSSFCell.CELL_TYPE_ERROR:
            break;
        case HSSFCell.CELL_TYPE_BOOLEAN:
            value = (cell.getBooleanCellValue() == true ? "Y" : "N");
            break;
        default:
            value = "";
        }
        return value.trim();
    }

    /**
     * 取值2003excel
     * 
     * @param cell
     * @return
     */
    private String getValue(HSSFCell cell) {
        if (cell == null) {
            return "";
        }

        String value = "";

        switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_STRING:
            value = cell.getStringCellValue();
            break;
        case HSSFCell.CELL_TYPE_NUMERIC:
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                Date date = cell.getDateCellValue();
                if (date != null) {
                    value = new SimpleDateFormat("yyyy-MM-dd").format(date);
                } else {
                    value = "";
                }
            } else {
                value = new DecimalFormat("0").format(cell.getNumericCellValue());
            }
            break;
        case HSSFCell.CELL_TYPE_FORMULA:
            // 导入时如果为公式生成的数据则无值
            if (!cell.getStringCellValue().equals("")) {
                value = cell.getStringCellValue();
            } else {
                value = cell.getNumericCellValue() + "";
            }
            break;
        case HSSFCell.CELL_TYPE_BLANK:
            break;
        case HSSFCell.CELL_TYPE_ERROR:
            break;
        case HSSFCell.CELL_TYPE_BOOLEAN:
            value = (cell.getBooleanCellValue() == true ? "Y" : "N");
            break;
        default:
            value = "";
        }
        return value.trim();
    }
}

ReadExcel.java

 

相关文章