springboot+vue用poi技术实现excel导入并插入到数据库,在页面更新

技术:

前端:vue

后端:springboot

数据库:mysql

pom.xml:

<!-- excel -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.17</version>
		</dependency>

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

	

 KHController:
 

package com.heeexy.example.controller;

import java.io.BufferedOutputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.apache.shiro.authz.annotation.RequiresPermissions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
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.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import com.alibaba.fastjson.JSONObject;
import com.heeexy.example.bean.KH;
import com.heeexy.example.bean.LianXiRen;
import com.heeexy.example.service.KHService;
import com.heeexy.example.util.CommonUtil;


@RestController
@RequestMapping("/kh")
@CrossOrigin
@ResponseBody
public class KHController {

	@Autowired
	private KHService khService;

	// 列表
	@RequiresPermissions("kh:lists")
	@GetMapping("/listKHS")
	public JSONObject listKHS(HttpServletRequest request) {
		return khService.listKHS(CommonUtil.request2Json(request));
	}

	// 查询方法
	@RequiresPermissions("kh:list")
	@GetMapping("/listXZ")
	public JSONObject listXZ(HttpServletRequest request) {
		return khService.listXZ(CommonUtil.request2Json(request));
	}

    
    /**
     * excel导入
     */
	
	@RequiresPermissions("kh:upload")
    @PostMapping("/upload")
    public String addKHexcel(@RequestParam("file") MultipartFile file) throws Exception {
	      String fileName = file.getOriginalFilename();
	      String test =  khService.batchImport(fileName, file);
	      System.out.println("test="+test);
	      return test;
    }
	  


	
}

KHService:

package com.heeexy.example.service;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.Map;

import org.springframework.web.multipart.MultipartFile;

import com.alibaba.fastjson.JSONObject;
import com.heeexy.example.bean.KH;
import com.heeexy.example.bean.LianXiRen;

public interface KHService {
	
	//列表
	public JSONObject listKHS(JSONObject jsonObject);
	
	//查询方法
    public JSONObject listXZ(JSONObject jsonObject);

    
    //excel导入
    public String batchImport(String fileName, MultipartFile file) throws Exception;
   
    
	

}

KHServiceImpl:

package com.heeexy.example.service.impl;

import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.*;
import java.util.Map;

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.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.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import com.alibaba.fastjson.JSONObject;
import com.heeexy.example.bean.DD;
import com.heeexy.example.bean.KH;
import com.heeexy.example.bean.LianXiRen;
import com.heeexy.example.dao.KHDao;
import com.heeexy.example.service.KHService;
import com.heeexy.example.util.CommonUtil;

@Service
public class KHServiceImpl implements KHService {
	
	@Autowired
	private KHDao khDao;
	
	
	//列表
	@Override
	public JSONObject listKHS(JSONObject jsonObject) {
		    CommonUtil.fillPageParam(jsonObject);
	        int count = khDao.countKH(jsonObject);
	        List<JSONObject> list = khDao.listKHS(jsonObject);
	        return CommonUtil.successPage(jsonObject, list, count);
	}

	//查询方法
	@Override
	public JSONObject listXZ(JSONObject jsonObject) {
		CommonUtil.fillPageParam(jsonObject);
        int count = khDao.countXZ(jsonObject);
        List<JSONObject> list = khDao.listXZ(jsonObject);
        return CommonUtil.successPage(jsonObject, list, count);
	}
	
	
	/**
	 * id放入list
	 * 
	 * @param id
	 *            id(多个已逗号分隔)
	 * @return List集合
	 * declareAd
	 */
	public List<String> getList(String id) {
		List<String> list = new ArrayList<String>();
		
		String[] str = id.split(",");
		for (int i = 0; i < str.length; i++) {
			list.add(str[i]);
		}
		return list;
	
	}
	

		


	
		
		/**
		 * excel导入
		 */
		
		@Transactional(readOnly = false,rollbackFor = Exception.class)
	    @Override
	    public String batchImport(String fileName, MultipartFile file) throws Exception {
	 
	        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$&#