ajax导入excel并添加到数据库,Java使用POI将Excel文件数据导入到Mongo数据库

前端ajax,后端springBoot

1.准备：

1.导入相关的依赖的依赖:

org.springframework.boot

spring-boot-starter-data-mongodb

org.springframework.boot

spring-boot-starter-web

org.projectlombok

lombok

true

org.mongodb

mongodb-driver

org.mongodb

mongodb-driver-core

org.mongodb

bson

org.apache.poi

poi

3.16

org.apache.poi

poi-ooxml

3.14

net.sourceforge.jexcelapi

jxl

2.6.10

org.springframework.boot

spring-boot-starter-test

test

org.junit.vintage

junit-vintage-engine

2.设置接收的最大容量：

在application.properties文件中：

## 设置接收文件的大小

spring.servlet.multipart.max-file-size=10MB

spring.servlet.multipart.max-request-size=20MB

2.前端代码：

提交

这个是选择文件的file,注意这个input标签必须要在form表单中，而且form的enctype必须为multipart/form-data

$("#btn").click(function() {

var formData = new FormData();

formData.append("excelFile", $("#file1")[0].files[0]);

$.ajax({

url: "/excleimport",

data: formData,

type: "post",

processData: false,

contentType: false,

success: function(data) {

alert(data.result);

},

error: function() {

alert("失败")

}

});

})

这个是js脚本内容,注意的是 processData，contentType这两个属性必须为false；

至此前端内容就结束了，可以直接copy了改就可以了

接下来再看看后端代码，我会贴出excel解析的部分，有需要的可以作为参考：

controller层

/**

* Excel表格插入数据到MongoDB

* @param excelFile

* @param response

* @return

* @throws IOException

*/

@ResponseBody

@RequestMapping(value = "/excleimport", method = RequestMethod.POST)

public Map excleimport(@RequestParam MultipartFile excelFile,

HttpServletResponse response) throws IOException {

response.setHeader("Access-Control-Allow-Origin", "*");

Map map = new HashMap();

String name = excelFile.getOriginalFilename();

if (!name.endsWith(".xls") && !name.endsWith(".xlsx")) {

System.out.println("文件不是excel类型");

map.put("result", "文件类型错误");

} else {

map.put("result",ExcelToMongo.getDataFromExcel(excelFile.getInputStream()));

}

return map;

}

到此所有的文件传值与接收就结束了，下面在附上ExcelToMongo.getDataFromExcel方法,也就是解析excel的方法：

package com.pmj.sign.util;

import com.mongodb.MongoClient;

import com.mongodb.MongoClientURI;

import com.mongodb.MongoCredential;

import com.mongodb.ServerAddress;

import com.mongodb.client.MongoCollection;

import com.mongodb.client.MongoDatabase;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

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.bson.Document;

import java.io.IOException;

import java.io.InputStream;

import java.util.ArrayList;

import java.util.List;

public class ExcelToMongo {

public static String getDataFromExcel(InputStream inputStream) {

Integer PORT = 27017; //端口号

String IP = "localhost"; //Ip

String DATABASE = "java"; //数据库名称

String USERNAME = "pmj"; //用户名

String PASSWORD = "123456"; //密码

String COLLECTION = "sign"; //文档名称

try {

// 根据输入流导入Excel产生Workbook对象

Workbook workbook = null;

try {

workbook = new HSSFWorkbook(inputStream);

} catch (IOException e) {

e.printStackTrace();

}

// IP，端口

ServerAddress serverAddress = new ServerAddress(IP, PORT);

List address = new ArrayList();

address.add(serverAddress);

// 用户名，数据库，密码

MongoCredential credential = MongoCredential.createCredential(USERNAME, DATABASE, PASSWORD.toCharArray());

List credentials = new ArrayList();

credentials.add(credential);

// 通过验证获取连接

MongoClient mongoClient = new MongoClient(address, credentials);

// 连接到数据库

MongoDatabase mongoDatabase = mongoClient.getDatabase(DATABASE);

// 连接文档

MongoCollection collection = mongoDatabase.getCollection(COLLECTION);

System.out.println("连接成功");

List documents = new ArrayList();

List fieldList = new ArrayList();

// 获取Excel文档中第一个表单

Sheet sheet = workbook.getSheetAt(0);

Row row0 = sheet.getRow(0);

for (Cell cell : row0) {

fieldList.add(cell.toString());

}

int rows = sheet.getLastRowNum() + 1;

int cells = fieldList.size();

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

Row row = sheet.getRow(i);

Document document = new Document();

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

Cell cell = row.getCell(j);

document.append(fieldList.get(j), cell.toString());

}

documents.add(document);

}

collection.insertMany(documents);

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

return "插入成功";

} catch (Exception e) {

System.err.println(e.getClass().getName() + ": " + e.getMessage());

return "插入失败";

}

}

}