POI 示例（导入，导出）

一、项目结构

 二、POM 依赖

 三、操作Excel

1.导入Excel   xls/xlsx

PoiRead.java

package com.dxj.hospital.util.poi;


import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.tomcat.util.http.fileupload.FileItem;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * xls HSSFWorkbook
 * @author Administrator
 */
public class PoiRead {
    /**
     * 导入excel,读取文件
     */
    public List<List<String>> poiRead(String path){
        List<List<String>> lists=new ArrayList<List<String>>();
        try{
            Workbook excel=WorkbookFactory.create(new FileInputStream(new File(path)));
            /**
             * 获取sheet
             */
            Sheet sheet = excel.getSheetAt(0);
            int rowNum = sheet.getLastRowNum();
            for (int i = 1; i <= rowNum; i++) {
                List<String> list=new ArrayList<>();
                /**
                 * 获取row
                 */
                Row row = sheet.getRow(i);
                if (row!=null){
                    int cellNum=row.getLastCellNum();
                    String cellValue=null;
                    for (int j=1;j<cellNum;j++){
                        /**
                         * 获取cell
                         */
                        Cell cell=row.getCell(j);
                        if (cell!=null){
                            if (cell!=null){
                                cellValue=(String) getValue(cell);
                                System.out.println(cellValue);
                            }else {
                                cellValue="";
                            }
                            list.add(cellValue);
                        }else{
                            break;
                        }
                    }
                }else{
                    break;
                }
                lists.add(list);
            }
            excel.close();
        }catch (Exception e){
            System.out.println(e);
        }
        System.out.println(lists);
        return  lists;
    }

    /**
     * Excel导入时，判断excel中的数据类型
     * @param cell
     * @return
     * @throws Exception
     */
    private static Object getValue(Cell cell)  {
        Object object = null;
        try{
            switch (cell.getCellTypeEnum()) {
                case STRING:
                    object = cell.getRichStringCellValue().getString();
                    break;
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        Date dateCellValue = cell.getDateCellValue();
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                        object=sdf.format(dateCellValue);
                    } else {
                        object = cell.getNumericCellValue();
                    }
                    break;
                case BOOLEAN:
                    object = cell.getBooleanCellValue();
                    break;
                case FORMULA:
                    object = cell.getCellFormula();
                    break;
                case BLANK:
                default:
                    break;
            }
        }catch (Exception e){
            System.out.println(e);
        }
        return object;
    }
}

其中：Workbook wb=WorkbookFactory.create(file)   内涵判断xls，xlsx的方法

ExcelController.java

package com.dxj.hospital.controller;

import com.dxj.hospital.domain.Role;
import com.dxj.hospital.service.RoleService;
import com.dxj.hospital.util.HttpResp;
import com.dxj.hospital.util.RespCode;
import com.dxj.hospital.util.poi.PoiRead;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;


/**
 * poi 导入，导出
 * @author Administrator
 */
@RestController
@RequestMapping("/excel")
public class ExcelController {
    @Autowired
    private RoleService roleService;
    @PutMapping("/excelRead")
    public HttpResp  excelRead() {

        PoiRead poiRead = new PoiRead();
        List<List<String>> lists = poiRead.poiRead("D://excel//hospital.xlsx");
        for (List<String> list:lists) {
            if (list!=null){
                   Role role=new Role();
                   role.setName(list.get(0));
                   role.setBy(list.get(1));
                   roleService.addRole(role);
            }
        }
        return new HttpResp(RespCode.RESP_SUCCESS.getCode(),
                RespCode.RESP_SUCCESS.getMessage(),
                0,null);
    }

}

2.导出Excel

package com.dxj.hospital.util.poi;

import com.dxj.hospital.domain.Role;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * 导出excel
 * @author Administrator
 */
public class PoiWrite {

    public void poiWrite(String path, List<List<String>> lists) {

        File file = new File("D://excel//hospital.xlsx");
        try {
            HSSFWorkbook excel = new HSSFWorkbook();
            HSSFSheet sheet = excel.createSheet("角色");
            HSSFRow row;
            HSSFCell cell;
            for (int i = 0; i < lists.size(); i++) {
                row = sheet.createRow(i);
                for (int j = 0; j < lists.get(i).size(); j++) {
                    cell = row.createCell(j);
                    cell.setCellValue(String.valueOf(lists.get(i).get(j)));
                }
            }
            excel.write(new FileOutputStream(file));
        } catch (Exception e) {
            System.out.println(e);
        }

    }
}