poi导入

poi导入Excel

1.引入pom依赖

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

2.直接写工具类

package com.example.springpoiexcel.utils;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.web.multipart.MultipartFile;

public class POIUtils {
    private static int totalRows = 0;// 总行数
    private static int totalCells = 0;// 总列数

    public static Map<String, List<List<String>>> readExcel(MultipartFile file) {
        Map<String, List<List<String>>> maps = new HashMap<String, List<List<String>>>();
        try {
            Workbook wb = WorkbookFactory.create(file.getInputStream());
            maps = read(wb);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return maps;
    }

    public int getTotalRows() {
        return totalRows;
    }

    public int getTotalCells() {
        return totalCells;
    }


    public static Map<String, List<List<String>>> read(Workbook wb) {
        Map<String, List<List<String>>> maps = new HashMap<String, List<List<String>>>();
        int number = wb.getNumberOfSheets();
        if (number > 0) {
            for (int i = 0; i < number; i++) { // 循环每个工作表
                List<List<String>> list = new ArrayList<List<String>>();
                int delnumber = 0;// 第一页去除行数
                Sheet sheet = wb.getSheetAt(i);
                totalRows = sheet.getPhysicalNumberOfRows() - delnumber; // 获取工作表中行数
                if (totalRows >= 1 && sheet.getRow(delnumber) != null) {
                    totalCells = sheet.getRow(0).getPhysicalNumberOfCells(); // 得到当前行的所有单元格
                    for (int j = 1; j < totalRows; j++) {
                        List<String> rowLst = new ArrayList<String>();
                        for (int f = 0; f < totalCells; f++) {
                            if (totalCells > 0) {
                                String value = getCell(sheet.getRow(j).getCell(f));
                                rowLst.add(value);
                            }
                        }
                        list.add(rowLst);
                    }
                }
                maps.put(sheet.getSheetName(), list);
            }
        }
        return maps;
    }

    public static String getCell(Cell cell) {
        String cellValue = null;
        /*
         * if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { if
         * (HSSFDateUtil.isCellDateFormatted(cell)) { cellValue =
         * getRightStr(cell.getDateCellValue() + ""); } else {
         *
         * cellValue = getRightStr(cell.getNumericCellValue() + ""); } } else if
         * (Cell.CELL_TYPE_STRING == cell.getCellType()) { cellValue =
         * cell.getStringCellValue(); } else if (Cell.CELL_TYPE_BOOLEAN ==
         * cell.getCellType()) { cellValue = cell.getBooleanCellValue() + ""; }
         * else { cellValue = cell.getStringCellValue(); }
         */
        HSSFDataFormatter hSSFDataFormatter = new HSSFDataFormatter();
        cellValue = hSSFDataFormatter.formatCellValue(cell); // 使用EXCEL原来格式的方式取得值
        return cellValue;
    }
}

3.Controller直接调用，

package com.example.springpoiexcel.controller;

import com.example.springpoiexcel.utils.POIUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import javax.servlet.http.HttpServletRequest;
import java.util.List;
import java.util.Map;

public class POIExcelController {


    @RequestMapping("/uploadWhiteImport")
    public void excel(HttpServletRequest request) {

        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
        MultipartFile file = multipartRequest.getFile("userImport");
        Map<String, List<List<String>>> stringListMap = POIUtils.readExcel(file);

    }

4.备注

可以对StringListMap里面的值进行判断取值

5.OK