java读取Excel工具类及实现示例

• 工具类代码

package com.tests.utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import sun.misc.base64Encoder;

import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;


public class ExcelUtil {

	
	public static ArrayList> excelReader(InputStream inStr,String excel_url) throws Exception {
		Workbook wb = getWorkbook(inStr,excel_url);
		ArrayList> ans=new ArrayList>();
		//遍历excel中的sheet
		for (int numSheet = 0; numSheet < wb.getNumberOfSheets(); numSheet++) {
			Sheet sheetAt = wb.getSheetAt(numSheet);
			if (sheetAt == null) {
				continue;
			}
			int columnNum = 0;
			if(sheetAt.getLastRowNum()>0){
				//根据表头的数据确定列数
				columnNum=sheetAt.getRow(0).getPhysicalNumberOfCells();
			}
			// 对于每个sheet，读取其中的每一行
			for (int rowNum = 0; rowNum <= sheetAt.getLastRowNum(); rowNum++) {
				Row row = sheetAt.getRow(rowNum);
				if (row == null){
					continue;
				}
				ArrayList curarr=new ArrayList();
				for(int c = 0 ; c 
使用示例
 
    @RequestMapping("/up")
    @ResponseBody
    public String up(@RequestParam("file") MultipartFile file, HttpServletRequest request){
        String path = "F:/data/工作表.xlsx";
        try {
            FileInputStream is = new FileInputStream(path);
            //调用读取工具类
            List> data = ExcelUtil.excelReader(is,path);
            User user = new User();
            for (int i = 0; i < data.size(); i++) {
                String s = data.get(i).get(0);
                if (!"姓名".equals(s)) {
                    ArrayList list = data.get(i);
                    String name = list.get(0);
                    String age = list.get(1);
                    String phone = list.get(2);
                    user.setName(name);
                    user.setAge(Integer.parseInt(age));
                    user.setPhone(phone);
                    userService.setUser(user);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
 
maven依赖
 
	
        org.apache.poi
        poi-ooxml
        3.10-FINAL