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 {

	/**
	 * @param inStr excel文件的流
	 * @param excel_url 文件路径(必须要带后缀)
	 * @return
	 * @throws Exception
	 */
	public static ArrayList<ArrayList<String>> excelReader(InputStream inStr,String excel_url) throws Exception {
		Workbook wb = getWorkbook(inStr,excel_url);
		ArrayList<ArrayList<String>> ans=new ArrayList<ArrayList<String>>();
		//遍历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<String> curarr=new ArrayList<String>();
				for(int c = 0 ; c<columnNum ; c++){
					if (null == row.getCell(c)){
						curarr.add("");
					}else{
						row.getCell(c).setCellType(Cell.CELL_TYPE_STRING);
						Cell cell = row.getCell(c);
						curarr.add( Trim_str( getValue(cell) ) );
					}
				}
				ans.add(curarr);
			}
		}
		return ans;
	}
}

• 使用示例

/**
     * 读取excel文件存到数据库
     * @param request
     * @return
     */
    @RequestMapping("/up")
    @ResponseBody
    public String up(@RequestParam("file") MultipartFile file, HttpServletRequest request){
        String path = "F:/data/工作表.xlsx";
        try {
            FileInputStream is = new FileInputStream(path);
            //调用读取工具类
            List<ArrayList<String>> 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<String> 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依赖

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