excel导入

一、引入JAR包

<dependency>
    <groupId>com.google.guava</groupId>
    <artifactId>guava</artifactId>
    <version>17.0</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.16</version>
</dependency>

二、后台代码

package cn.rhtop.extrade.backend.controller;

import com.google.common.io.Files;
import com.google.gson.Gson;
import jodd.util.URLDecoder;
import org.apache.commons.lang.StringUtils;
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.poifs.filesystem.POIFSFileSystem;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import javax.rmi.CORBA.Util;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
     * 行政区域excel导入
     * @param session
     * @param file
     * @return
     */
    @RequestMapping("/area/excelImport")
    @ResponseBody
    public String areaExcelImport(@RequestParam(value = "file") MultipartFile file){
        File localFile = null;
        try {//上传文件
            localFile = new File("/file/excel" + "/" + file.getOriginalFilename());
            Files.write(file.getBytes(), localFile);
            if (localFile == null || !localFile.exists()){
                return "文件上传失败";
            }
        } catch (IOException e1) {
            e1.printStackTrace();
            return "文件上传异常";
        }
        POIFSFileSystem fs = null;
        try {
            fs = new POIFSFileSystem(new FileInputStream(localFile.getAbsolutePath()));
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            return "文件未找到";
        } catch (IOException e) {
            e.printStackTrace();
            return "POIFSFileSystem的IO异常";
        }
        HSSFWorkbook wb = null;
        try {
            wb = new HSSFWorkbook(fs);
        } catch (IOException e) {
            e.printStackTrace();
            return "HSSFWorkbook的IO异常";
        }
        HSSFSheet hssfSheet=wb.getSheetAt(0);
        if(hssfSheet!=null){
            int[] cellIndex = {-1,-1,-1,-1,-1,-1,-1,-1,-1};
            //遍历excel表头,从第二行开始 即 rowNum=1,逐个获取单元格的内容,然后进行格式处理,最后插入数据库
            HSSFRow hssfNameRow = hssfSheet.getRow(0);
            if(hssfNameRow==null || hssfNameRow.getCell(0) == null || "".equals(formatCell(hssfNameRow.getCell(0)))){
                return "第二列未发现表头信息";
            }
            for(int cellNum=0; cellNum<=hssfNameRow.getLastCellNum(); cellNum++){
                HSSFCell cell = hssfNameRow.getCell(cellNum);
                if (cell != null && StringUtils.isNotEmpty(cell.getStringCellValue())){
                    String cellName = cell.getStringCellValue().replaceAll(" ", "");
                    if ("区划编码".equals(cellName)){
                        cellIndex[0] = cellNum;
                    }else if ("区划名称".equals(cellName)){
                        cellIndex[1] = cellNum;
                    }else if ("区划级别".equals(cellName)){
                        cellIndex[2] = cellNum;
                    }else if ("区划全名".equals(cellName)){
                        cellIndex[3] = cellNum;
                    }else if ("电话区号".equals(cellName)){
                        cellIndex[4] = cellNum;
                    }else if ("邮政编码".equals(cellName)){
                        cellIndex[5] = cellNum;
                    }else if ("对照码".equals(cellName)){
                        cellIndex[6] = cellNum;
                    }else if ("使用".equals(cellName)){
                        cellIndex[7] = cellNum;
                    }else if ("审核".equals(cellName)){
                        cellIndex[8] = cellNum;
                    }
                }
            }

//            for(int cellNum=0; cellNum<cellIndex.length; cellNum++){
//                if (cellIndex[cellNum] == -1){
//                    String name = "";
//                    if (cellNum == 0){
//                        name = "区划编码";
//                    }else if (cellNum == 1){
//                        name = "区划名称";
//                    }else if (cellNum == 2){
//                        name = "区划级别";
//                    }else if (cellNum == 3){
//                        name = "区划全名";
//                    }else if (cellNum == 4){
//                        name = "电话区号";
//                    }else if (cellNum == 5){
//                        name = "邮政编码";
//                    }else if (cellNum == 6){
//                        name = "对照码";
//                    }else if (cellNum == 7){
//                        name = "使用";
//                    }else if (cellNum == 8){
//                        name = "审核";
//                    }
//                    System.out.println("未发现["+name+"]列头信息，请确认表头名称是否正确！");
//                    return map;
//                }
//            }
            //遍历excel,从第三行开始 即 rowNum=2,逐个获取单元格的内容,然后进行格式处理,最后插入数据库
            String shengParent = "1001";
            String shiParent = "";
            String quParent = "";
            for(int rowNum=1;rowNum<=hssfSheet.getLastRowNum();rowNum++){
                try{
                    HSSFRow hssfRow=hssfSheet.getRow(rowNum);
                    if(hssfRow==null||hssfRow.getCell(0) == null||"".equals(formatCell(hssfRow.getCell(0)))){
                        continue;
                    }
                    DictItem di = new DictItem();
                    //用固定列来读取数据
                    String itemCode = formatCell(hssfRow.getCell(0));
                    di.setItemCode(itemCode);
                    di.setParamName(formatCell(hssfRow.getCell(1)));
                    String type = formatCell(hssfRow.getCell(2));
                    if("省".equals(type)){
                        di.setType(6);
                        di.setParentCode("1001");
                        shengParent = di.getItemCode();
                    }else if("市".equals(type)){
                        di.setType(5);
                        di.setParentCode(shengParent);
                        shiParent = di.getItemCode();
                    }else if("区".equals(type)){
                        di.setType(9);
                        di.setParentCode(shiParent);
                        quParent = di.getItemCode();
                    }else if("街道/镇".equals(type)){
                        di.setType(10);
                        di.setParentCode(quParent);
                    }
                    di.setCreateTime(new Date());
                    di.setUpdateTime(new Date());
                    dictItemService.saveDictItem(di);
                }catch(Exception e){
                    e.printStackTrace();
                }
            }
        }
        return "导入成功";
    }

    //处理Excel的一个方法
    public String formatCell(HSSFCell hssfCell){
        if(hssfCell==null){
            return "";
        }else{
            if(hssfCell.getCellType()==HSSFCell.CELL_TYPE_BOOLEAN){
                return String.valueOf(hssfCell.getBooleanCellValue());
            }else if(hssfCell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC){
                DecimalFormat df = new DecimalFormat("0");
                String whatYourWant = df.format(hssfCell.getNumericCellValue());
                return whatYourWant;
            }else{
                return String.valueOf(hssfCell.getStringCellValue());
            }
        }
    }

三、导入模板示例