注意:这种方法适用于第二行开始就是数据的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;
}
}