Excel Oracle+Mybatis导入数据以及导出模板

注意：这种方法适用于第二行开始就是数据的Excel

首先导入jar

		<!--导入导出Excel-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.10-FINAL</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.10-FINAL</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.14-beta1</version>
        </dependency>

1.controller

package com.springboot.wensocket.importexport.controller;

import com.springboot.wensocket.importexport.service.ExcelExportService;
import com.springboot.wensocket.importexport.util.ReadExcel;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.DataFormat;
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.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Controller
@RequestMapping("/excel")
public class ExcelExport {

    @Autowired
    private ExcelExportService service;


    /**
     * 导入excel数据存到list里面 通过调接口把list存到数据库中
     * @param excel
     * @return
     * @throws IOException
     */
    @RequestMapping("/exportfsdx1")
    @ResponseBody
    public Map excel(@RequestParam("excel") MultipartFile excel) throws IOException {
        System.out.println("111");
        Map<String,String> map = new HashMap<String,String>();
        String result;
        //与excel表头的字段对应 存到list里面数据的键
        String[] a = {"id","name","password","perms"};
        ReadExcel ReadExcel = new ReadExcel();
        List<Map<String, Object>> list = ReadExcel.getExcelInfo(excel, a);
        System.out.println(list);
        int count = service.insert(list);
        if(list != null && !list.isEmpty()&&count==list.size()){
            result = "1";
        }else{
            result = "-1";
        }
        map.put("ret",result);
        return  map;
    }

    /**
     *  导出模板
     * @param response
     */
    @RequestMapping("/excelDcmb")
    public void excelDcmb(HttpServletResponse response){
        HSSFWorkbook workbook = new HSSFWorkbook(); //创建Excel
        HSSFSheet sheet = workbook.createSheet("Sheet1");//创建Sheet

        HSSFCellStyle style = workbook.createCellStyle();//样式
        DataFormat dataFormat = workbook.createDataFormat();
        style.setDataFormat(dataFormat.getFormat("@"));
        sheet.setDefaultColumnStyle(0,style);
        sheet.setDefaultColumnStyle(1,style);
        sheet.setDefaultColumnStyle(2,style);
        sheet.setDefaultColumnStyle(3,style);
        //设置列宽
        sheet.setColumnWidth(0, 6000);
        sheet.setColumnWidth(1, 6000);
        sheet.setColumnWidth(2, 6000);
        sheet.setColumnWidth(3, 8000);
        //设置高宽
        sheet.setDefaultRowHeight((short) (20*20));

        HSSFRow T_row = sheet.createRow(0);//创建一行表头
        T_row.createCell(0, HSSFCell.CELL_TYPE_STRING).setCellValue("id");		//设置表头列名0
        T_row.createCell(1, HSSFCell.CELL_TYPE_STRING).setCellValue("姓名");			//设置表头列名1
        T_row.createCell(2, HSSFCell.CELL_TYPE_STRING).setCellValue("密码");		//设置表头列名2
        T_row.createCell(3, HSSFCell.CELL_TYPE_STRING).setCellValue("perms");		//设置表头列名3
        try {
            //设置文件头
            ServletOutputStream outStream = response.getOutputStream();
            String fileName = URLEncoder.encode("模板.xls", "UTF-8");  //设置文件名
            response.reset();
            response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
            response.setContentType("application/octet-stream; charset=UTF-8");
            //将文件流写入response 流
            workbook.write(outStream);
            outStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

2.service

package com.springboot.wensocket.importexport.service;

import com.springboot.wensocket.importexport.dao.ExcelExportDao;
import com.springboot.wensocket.importexport.entity.Person;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.beans.Transient;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Service
public class ExcelExportService {
    @Autowired
    private ExcelExportDao dao;

    @Transient
    public int insert(List<Map<String,Object>> list){
        List<Person> salaryList = new ArrayList<Person>();
        for (int i = 0; i < list.size(); i++) {
            Person p = new Person();
            //通过遍历实现把每一列封装成一个model中，再把所有的model用List集合装载
            p.setId(String.valueOf(list.get(i).get("id")));
            p.setName(String.valueOf(list.get(i).get("name")));
            p.setPassword(String.valueOf(list.get(i).get("password")));
            p.setPerms(String.valueOf(list.get(i).get("perms")));
            salaryList.add(p);
        }
        return dao.insert(salaryList);
    }
}

3.Mapper

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.springboot.wensocket.importexport.dao.ExcelExportDao">


    <insert id="insert" parameterType="java.util.List">
        insert into sys_user (ID, name, password,perms)
        <foreach collection="list" item="item" index="index" open="(" close=")" separator="union">
            select
            #{item.id,jdbcType=VARCHAR},
            #{item.name,jdbcType=VARCHAR},
            #{item.password,jdbcType=VARCHAR},
            #{item.perms,jdbcType=VARCHAR}
            from dual
        </foreach>
    </insert>
</mapper>

4.dao

package com.springboot.wensocket.importexport.dao;

import com.springboot.wensocket.importexport.entity.Person;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;
import java.util.Map;

@Mapper
public interface ExcelExportDao {
    //Excel 批量导入
    int insert(List<Person> seorderkList);

}

5.Entity

package com.springboot.wensocket.importexport.entity;

public class Person {
    private String id;
    private String name;
    private int age;
    private String sex;
    private String password;
    private String perms;

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getPerms() {
        return perms;
    }

    public void setPerms(String perms) {
        this.perms = perms;
    }

    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    public String getSex() {
        return sex;
    }
    public void setSex(String sex) {
        this.sex = sex;
    }
}

6.util

package com.springboot.wensocket.importexport.entity;

public class Person {
    private String id;
    private String name;
    private int age;
    private String sex;
    private String password;
    private String perms;

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getPerms() {
        return perms;
    }

    public void setPerms(String perms) {
        this.perms = perms;
    }

    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    public String getSex() {
        return sex;
    }
    public void setSex(String sex) {
        this.sex = sex;
    }
}