最菜的Java导入excel

poi Maven

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.14</version>
        </dependency>

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

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

基础代码

package com.peas.mdp.module.util;

import com.peas.mdp.dto.Teacher;
import org.apache.poi.ss.usermodel.*;

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

public class ExcelUtil {

    public static List<?> getBankListByExcel(InputStream in) throws Exception {

        List<Teacher> list = new ArrayList<>();
        //创建Excel工作薄
        Workbook work = getWorkbook(in);
        if (null == work) {
            throw new Exception("创建Excel工作薄为空！");
        }
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;

        //遍历Excel中所有的sheet
        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            sheet = work.getSheetAt(i);
            if (sheet == null) {
                continue;
            }

            //遍历当前sheet中的所有行
            for (int j =1; j <= sheet.getLastRowNum(); j++) {
                row = sheet.getRow(j);
                if (row == null ) {
                    continue;
                }

                Teacher teacher = new Teacher();

                //把每个单元格的值付给对象的对应属性
                if (row.getCell(0)!=null){
                    teacher.setAccount(String.valueOf(getCellValue(row.getCell(0))));
                }
                if (row.getCell(1)!=null){
                    teacher.setName(String.valueOf(getCellValue(row.getCell(1))));
                }
                if (row.getCell(2)!=null){
                    teacher.setSex(Integer.valueOf((String) getCellValue(row.getCell(2))));
                }
                if (row.getCell(3)!=null){
                    teacher.setAge(Integer.valueOf((String) getCellValue(row.getCell(3))));
                }
                if (row.getCell(4)!=null){
                    teacher.setEducation(String.valueOf(getCellValue(row.getCell(4))));
                }
                if (row.getCell(5)!=null){
                    teacher.setJobTitle(String.valueOf(getCellValue(row.getCell(5))));
                }
                if (row.getCell(6)!=null){
                    teacher.setPosition(String.valueOf(getCellValue(row.getCell(6))));
                }
                if (row.getCell(7) != null) {
                    teacher.setRole(String.valueOf(getCellValue(row.getCell(7))));
                }
                if (row.getCell(8) != null) {
                    teacher.setPhone(String.valueOf(getCellValue(row.getCell(8))));
                }
                if (row.getCell(9) != null) {
                    teacher.setEmail(String.valueOf(getCellValue(row.getCell(9))));
                }
                if (row.getCell(10) != null) {
                    teacher.setKeyWords(String.valueOf(getCellValue(row.getCell(10))));
                }
                if (row.getCell(11) != null) {
                    teacher.setIntroduction(String.valueOf(getCellValue(row.getCell(11))));
                }
                if (row.getCell(12) != null) {
                    teacher.setIdCard(String.valueOf(getCellValue(row.getCell(12))));
                }
                if (row.getCell(13) != null) {
                    teacher.setType(Integer.valueOf((String)getCellValue(row.getCell(13))));
                }

                //遍历所有的列(把每一行的内容存放到对象中)
                list.add(teacher);
            }
        }

        return list;
    }


    /**
     *
     * @param inStr
     * @return
     * @throws Exception
     */
    public static Workbook getWorkbook(InputStream inStr) throws Exception {
        Workbook wb = null;
        wb = WorkbookFactory.create(inStr);
        return wb;
    }

    /**
     * 描述：对表格中数值进行格式化
     *
     * @param cell
     * @return
     */
    public static Object getCellValue(Cell cell) {
        Object value = null;
        DecimalFormat df = new DecimalFormat("0");  //格式化number String字符
        SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");  //日期格式化
        DecimalFormat df2 = new DecimalFormat("0");  //格式化数字

        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                value = cell.getRichStringCellValue().getString();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                    value = df.format(cell.getNumericCellValue());
                } else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
                    value = sdf.format(cell.getDateCellValue());
                } else {
                    value = df2.format(cell.getNumericCellValue());
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case Cell.CELL_TYPE_BLANK:
                value = "";
                break;
            default:
                break;
        }
        return value;
    }


}

Controller

@ApiOperation("excel导入")
    @GET
    @Path("excelFile")
    public Map<String, String> readExcel(@QueryParam("path") File path) throws Exception {

        HashMap<String, String> map = new HashMap<>();
        //读取excel中的内容
        InputStream inputStream = new FileInputStream(path);
        List<Teacher> teachers = (List<Teacher>) ExcelUtil.getBankListByExcel(inputStream);
        for (Teacher teacher : teachers) {
            teacherService.saveOrUpdate(teacher);
        }
        return map;

    }
 /**
     * 读取excel更新数据
     *
     * @param upfile
     * @param
     * @param
     * @throws Exception
     */
/*    
    @RequestMapping(value = "/readExcel")
    @Transactional
    public Map<String, String> readExcel(MultipartFile upfile) throws Exception {

        HashMap<String, String> map = new HashMap<>();

        InputStream in = upfile.getInputStream();
        //读取excel中的内容
        List<LineDownOrder> lineDownOrders = ExcelUtils.getBankListByExcel(in);
        String s = orderService.updateByExcel(lineDownOrders);
        map.put("status", s);
        return map;

    }
*/