将excel导入mysql数据库

Controller层

package com.aishidai.web.ceshi;

 

 

 

import com.aishidai.entitydto.Test;

import com.aishidai.manager.TestCe;

import com.aishidai.manager.impl.TestDaoImpl;

import com.alibaba.fastjson.JSONObject;

import com.google.gson.JsonObject;

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.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Controller;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RequestMethod;

import org.springframework.web.bind.annotation.ResponseBody;

 

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

 

@Controller

public class PushExcelToMysql {

public static String filePath = "C:\\Users\\lenovo\\Desktop\\excel.xls";

 

@Autowired

TestCe testCe;

 

@RequestMapping(value = "/test/add", method = {RequestMethod.GET,RequestMethod.POST})

@ResponseBody

public String addTset() throws Exception {

JSONObject jsonObject = new JSONObject();

jsonObject.put("success", false);

try {

// 创建对Excel工作簿文件的引用

HSSFWorkbook wookbook = new HSSFWorkbook(new FileInputStream(

filePath));

// 在Excel文档中，第一张工作表的缺省索引是0

// 其语句为：HSSFSheet sheet = workbook.getSheetAt(0);

HSSFSheet sheet = wookbook.getSheet("Sheet1");

// 获取到Excel文件中的所有行数

int rows = sheet.getPhysicalNumberOfRows();

// 遍历行

for (int i = 1; i < rows; i++) {

// 读取左上端单元格

HSSFRow row = sheet.getRow(i);

// 行不为空

if (row != null) {

// 获取到Excel文件中的所有的列

int cells = row.getPhysicalNumberOfCells();

String value = "";

// 遍历列

for (int j = 0; j < cells; j++) {

// 获取到列的值

HSSFCell cell = row.getCell(j);

if (cell != null) {

switch (cell.getCellType()) {

case HSSFCell.CELL_TYPE_FORMULA:

break;

case HSSFCell.CELL_TYPE_NUMERIC:

value += cell.getNumericCellValue() + ",";

break;

case HSSFCell.CELL_TYPE_STRING:

value += cell.getStringCellValue() + ",";

break;

default:

value += "0";

break;

}

}

}

 

// 将数据插入到mysql数据库中

String[] val = value.split(",");

Test entity = new Test();

entity.setName(val[0]);

entity.setSex(val[1]);

entity.setPhone(val[2]);

testCe.insert(entity);

}

}

System.out.println("插入成功！");

jsonObject.put("message","插入成功");

jsonObject.put("success",true);

} catch (FileNotFoundException e) {

e.printStackTrace();

} catch (IOException e) {

e.printStackTrace();

}

return jsonObject.toString();

}

 

}

 

servicE层】

、package com.aishidai.manager;

 

import com.aishidai.entitydto.Test;

 

public interface TestCe {

Long insert(Test test);

}

 

实现层

package com.aishidai.manager.impl;

 

import com.aishidai.dao.TestDAO;

import com.aishidai.entitydto.Test;

import com.aishidai.manager.TestCe;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Component;

 

@Component

public class TestDaoImpl implements TestCe{

@Autowired

TestDAO testDao;

 

public Long insert(Test test){

System.out.println("9999");

Long count = testDao.createst(test);

System.out.println("ceshu===="+count);

return count;

}

 

}

 

dao层

 

package com.aishidai.dao;

 

 

import com.aishidai.entitydto.Test;

import org.apache.ibatis.annotations.Insert;

import org.springframework.stereotype.Component;

 

@Component("testDAO")

public interface TestDAO {

 

@Insert("INSERT INTO excel (name, sex,phone) VALUES (#{name},#{sex},#{phone})")

Long createst(Test test);

}

 

model类

package com.aishidai.entitydto;

 

public class Test {

private String name;

private String sex;

private String phone;

 

public String getName() {

return name;

}

 

public void setName(String name) {

this.name = name;

}

 

public String getSex() {

return sex;

}

 

public void setSex(String sex) {

this.sex = sex;

}

 

public String getPhone() {

return phone;

}

 

public void setPhone(String phone) {

this.phone = phone;

}

}

加入依赖

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.8</version>
</dependency>