java使用poi导入excel

1.添加poi的jar包

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

2.控制层。支持导入.xls结尾和.xlsx结尾的excel

 

package com.bos.restController;

import com.bos.common.ResultData;
import com.bos.test.User;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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 org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

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

/**
 * create by luojie 2018/8/8
 */
@RestController
public class TestController {
    public static final String EXCEL_TYPE_2003 = "xls";
    public static final String EXCEL_TYPE_2007 = "xlsx";

    /**
     * 导入excel
     * @param file
     * @return
     */
    @RequestMapping(value = "readExceal")
    public ResultData readExcel(@RequestParam(value = "uploadFile", required = false) MultipartFile file) {
        ResultData result = new ResultData();
        if (!file.isEmpty()) {
            try {
                String filename = file.getOriginalFilename();//取得文件原名
                Integer indexStar = filename.lastIndexOf(".") + 1;//取得文件后缀名
                String excelTypeName = filename.substring(indexStar);//取得文件后缀名
                //将读取出来的数据放入list
                List<User> list =readExcel(file.getInputStream(),excelTypeName);
                //打印
                for (User user : list) {
                    System.out.println(user.getName()+"---"+user.getSex());
                }
            } catch (Exception e) {
                result.setResult("false");
                result.setMessage("导入失败");
                e.printStackTrace();
            }
        }
        return result;
    }

    /**
     * 读取excel
     * @param inputStream
     * @param excelTypeName
     * @return
     */
    private static List readExcel(InputStream inputStream, String excelTypeName) {
        List<User> l = new ArrayList<>();
        Workbook wb = null;
        try {
            if(EXCEL_TYPE_2007.equals(excelTypeName)){
                wb = new XSSFWorkbook(inputStream);
            }else {
                wb=new HSSFWorkbook(inputStream);
            }
            //获得excel中第一个表格
            Sheet sheet =wb.getSheetAt(0);
            boolean flag = true;
            for (Row cells : sheet) {
                //跳过第一行
                if (flag == true) {
                    flag = false;
                    continue;
                }
                User u = new User();
                //把excel中的数据都转成String类型
                u.setName(toStringValue(cells.getCell(0)));
                u.setSex(toStringValue(cells.getCell(1)));
                l.add(u);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return l;
    }

    //把excel单元格数据类型转成字符串类型
    public static String toStringValue(Cell cell) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        return cell.toString();
    }
}

3.1前台页面（jsp）

<form id="f1" method="post" enctype="multipart/form-data">
     <input name="uploadFile" type="file">
     <input type="button" onclick="javaScript:uploadExcel()" value="上传">
 </form>

3.2前台页面（js）

function uploadExcel() {
    var formData=new FormData($('#f1')[0]);
    $.ajax({
        type: 'post',
        url: "../readExcel",
        data: formData,
        cache: false,
        processData: false,
        contentType: false,
        success: function (data) {
            alert("导入成功");
        }
    });
}

4。测试.xls结尾的excel

 5.测试 .xlsx结尾的Excel

 

 有问题欢迎留言，互相学习！