excel 文件读取

原

JAVA解析Excel需要的包及代码

2017年10月27日 18:59:09 ZR2023 阅读数：686 标签： java 更多

个人分类： Java Web

需要的包： 
commons-collections4-4.1.jar 
poi-3.17.jar 
poi-ooxml-3.17.jar 
xmlbeans-2.6.0.jar 
poi-ooxml-schemas-3.17.jar

maven只需要写：

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.10-FINAL</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.10-FINAL</version>
        </dependency>

以下为解析的代码：

​
package util;

import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

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.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

public class ExcelReader {
    //需要解析的是xlsx文件
    public List<List<String>> readXlsx(String path) throws Exception{
        //获取文件输入流
        InputStream is = new FileInputStream(path);
        //获取读取的实例
        XSSFWorkbook xssfWorkbook=new XSSFWorkbook(is);
        //记录Excel结果的变量
        List<List<String>> result = new ArrayList<List<String>>();
        //遍历每一页
        //for(int index=0;index<hssfWorkbook.getNumberOfSheets();index++)
        //HSSFSheet hssfSheet=hssfWorkbook.getSheetAt(index);
        for(int index=0;index<xssfWorkbook.getNumberOfSheets();index++) {
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(index);
            if(xssfSheet == null)
                continue;
            //遍历每一行
            for(int row=1;row<=xssfSheet.getLastRowNum();row++) {
                XSSFRow xssfRow = xssfSheet.getRow(row);

                int minCol = xssfRow.getFirstCellNum();
                int maxCol = xssfRow.getLastCellNum();
                List<String> rowList = new ArrayList<String>();
                //遍历该行，获取每个celll元素
                for(int col = minCol;col<maxCol;col++) {
                    XSSFCell cell = xssfRow.getCell(col);
                    if(cell==null) {
                        continue;
                    }
                    rowList.add(cell.toString());
                }
                result.add(rowList);
            }
        }
        xssfWorkbook.close();
        return result;
    }

    //需要解析的是xls文件
    public List<List<String>> readXls(String path) throws Exception{
        InputStream is = new FileInputStream(path);
        HSSFWorkbook hssfWorkbook=new HSSFWorkbook(is);
        List<List<String>> result = new ArrayList<List<String>>();
        for(int index=0;index<hssfWorkbook.getNumberOfSheets();index++) {
            HSSFSheet hssfSheet=hssfWorkbook.getSheetAt(index);
            if(hssfSheet == null)
                continue;
            for(int row=1;row<=hssfSheet.getLastRowNum();row++) {
                HSSFRow hssfRow = hssfSheet.getRow(row);

                int minCol = hssfRow.getFirstCellNum();
                int maxCol = hssfRow.getLastCellNum();
                List<String> rowList = new ArrayList<String>();
                //遍历该行，获取每个celll元素
                for(int col = minCol;col<maxCol;col++) {
                    HSSFCell cell = hssfRow.getCell(col);
                    if(cell==null) {
                        continue;
                    }
                    rowList.add(cell.toString());
                }
                result.add(rowList);
            }
        }
        hssfWorkbook.close();
        return result;
    }

    @Test
    public void test() throws Exception {
        ExcelReader excelReader = new ExcelReader();
        List<List<String>> result = excelReader.readXlsx("D:\\code\\worksp\\oxygen\\onmyoji_card\\src\\util\\test.xlsx");
        System.out.println(result);
    }

}

​

一下为读取手机号代码

 

  @ApiOperation(value = "读取文件")
    @RequestMapping(value = {"/sys/system/Batch/uploadFiles"}, method = RequestMethod.POST)
    public ApiResult uploadFiles(MultipartFile file) {
        if (file == null) {
            return ApiResult.error("文件为空");
        }
        String fileName = file.getOriginalFilename();
        String fileTyle=fileName.substring(fileName.lastIndexOf("."),fileName.length());
        System.out.println(fileTyle+"*************************");
        try {
            if(fileTyle.equals(".xlsx")){
                Map<String,Object> read = readXlsx(file);
                if(read.size() == 0){
                    return ApiResult.error("手机号为空或手机号格式错误!");
                }
                return ApiResult.ok(read);

            }else {
                return ApiResult.error("文件格式错误,请上传excel文件");
            }
        }catch (Exception e) {
            e.printStackTrace();
            return ApiResult.error("文档解析错误请稍后重试");
        }

    }

 

​
 /**excel xlsx 格式处理*/
    private Map<String,Object> readXlsx(MultipartFile File){
        boolean num = false;
        //记录Excel结果的变量
        long uploadNumber = 0;
        long repetNumber ;
        Set<String> phones = new HashSet<>();
        Map<String,Object> resultMap = new HashMap<>();
        try (InputStream is = File.getInputStream()){
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
            NumberFormat nf = NumberFormat.getInstance();
            //遍历每一页
            //for(int index=0;index<hssfWorkbook.getNumberOfSheets();index++)
            //HSSFSheet hssfSheet=hssfWorkbook.getSheetAt(index);
            for (int index = 0; index < xssfWorkbook.getNumberOfSheets(); index++) {
                XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(index);
                if (xssfSheet == null)
                    continue;
                //遍历每一行
                for (int row = 1; row <= xssfSheet.getLastRowNum(); row++) {
                    XSSFRow xssfRow = xssfSheet.getRow(row);
                    if(xssfRow == null)
                        continue;
                    int minCol = xssfRow.getFirstCellNum();
                    int maxCol = xssfRow.getLastCellNum();
                    //遍历该行，获取每个celll元素
                    for (int col = minCol; col < maxCol; col++) {
                        XSSFCell cell = xssfRow.getCell(col);
                        if (cell == null) {
                            continue;
                        }
                        /** CellType 类型 值
                         CELL_TYPE_NUMERIC 数值型 0
                         CELL_TYPE_STRING 字符串型 1
                         CELL_TYPE_FORMULA 公式型 2
                         CELL_TYPE_BLANK 空值 3
                         CELL_TYPE_BOOLEAN 布尔型 4
                         CELL_TYPE_ERROR 错误 5*/
                        if(cell.getCellType() == 0){
                            String s = nf.format(cell.getNumericCellValue());
                            if (s.contains(",")){
                                s = s.replace(",", "");
                            }
                            uploadNumber++;
                            //目前手机号先根据11为长度验证
                            if(s.length() != 11){
                                return new HashMap<>();
                            }
                            phones.add(s);
                        }else {
                            return new HashMap<>();
                        }
                    }
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        repetNumber =uploadNumber - phones.size();
        uploadNumber = uploadNumber-repetNumber;
        resultMap.put("uploadNumber",uploadNumber);
        resultMap.put("repetNumber",repetNumber);
        resultMap.put("phones",phones);
        return resultMap;
    }

​