springmvc ajax excel文件上传,JSP+ajax+springMVC+MayBatis处理excel上传导入

jsp

导入

jquery

//数据导入

function importData(){

var files = $('input[name="importData"]').prop('files');//获取到文件列表

if(files.length == 0){

alert('请选择文件');

return;

}else{

var formData = new FormData();

var name = $("#importData").val();

formData.append("file",$("#importData")[0].files[0]);

formData.append("name",name);

var reg = /^.*\.(?:xls|xlsx)$/i;

if (!reg.test(name)) {//校验是否是excel格式的文件

alert("请上传excel格式的文件!");

return;

}

var url = "url";

$.ajax({

type : "post",

async : false,

traditional : true,

url : url,

dataType : "json",

data : formData,

processData : false,

contentType : false,

success : function(result) {

if (result.success) {

//保存刷新

//........

alert("导入成功!");

}

},

error : function(errorMsg) {

alert("导入失败!");

}

});

}

}

java

@Autowired

private IDimUserFileConfService dimUserFileConfService;

@Autowired

private IDimUserGiConfService dimUserGiConfService;

@RequestMapping(value = "importExel.json", method = RequestMethod.POST)

@ResponseBody

public Object importdata(@RequestParam("file") MultipartFile file,HttpServletRequest request, HttpServletResponse response,@RequestParam("user_id") String user_id,

@RequestParam("user_name") String user_name) {

String id = user_id+DateUtils.getDateTimeStr();

Date create_time = new Date();

String filename = file.getOriginalFilename();//原文件名字

XSSFWorkbook workbook1 = null;

HSSFWorkbook workbook2 = null;

try {

InputStream is = file.getInputStream();//获取输入流

if(filename.endsWith(".xlsx")){

workbook1 = new XSSFWorkbook(is);

XSSFSheet sheet = workbook1.getSheetAt(0); // 创建对工作表的引用

int rows = sheet.getPhysicalNumberOfRows();// 获取表格的

int columns = 0;

for (int r = 0; r < rows; r++) { // 循环遍历表格的行

if(r==0){

//在第一行标题行计算出列宽度,因为数据行中可能会有空值

columns = sheet.getRow(r).getLastCellNum();

continue;

}

String value = "";

Row row = sheet.getRow(r); // 获取单元格中指定的行对象

if (row != null) { //遍历单元格

for(int i = 0 ; i < columns; i++) {

Cell cell = row.getCell(i);

cell.setCellType(Cell.CELL_TYPE_STRING);

String temp = cell.getStringCellValue();

if (temp.indexOf(".") > -1) { //处理有小数的读取错误

value += String.valueOf(new Double(temp)).trim()+",";

} else {

value += temp.trim()+",";

}

}

}

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

int k = 0;

if(r % 200 == 0) {//文件拆分，每200行一个文件

k++;

id = k+user_id+DateUtils.getDateTimeStr()+"p";

//保存文件信息

dimUserFileConfService.addUserGiFile(id, filename+"_p"+k, user_id, user_name, new Date());

}

//处理用户地理信息保存

dimUserGiConfService.addUserGi(str[0], str[1], str[2], id);

}

if(!id.endsWith("p")) {

//保存文件信息

dimUserFileConfService.addUserGiFile(id, filename, user_id, user_name, create_time);

}

} else {

workbook2 = new HSSFWorkbook(is);

HSSFSheet sheet = workbook2.getSheetAt(0); // 创建对工作表的引用

int rows = sheet.getPhysicalNumberOfRows();// 获取表格的

int columns = 0;

for (int r = 0; r < rows; r++) { // 循环遍历表格的行

if(r==0){

//在第一行标题行计算出列宽度,因为数据行中可能会有空值

columns = sheet.getRow(r).getLastCellNum();

continue;

}

String value = "";

Row row = sheet.getRow(r); // 获取单元格中指定的行对象

if (row != null) { //遍历单元格

for(int i = 0 ; i < columns; i++) {

Cell cell = row.getCell(i);

cell.setCellType(Cell.CELL_TYPE_STRING);

String temp = cell.getStringCellValue();

if (temp.indexOf(".") > -1) { //处理有小数的读取错误

value += String.valueOf(new Double(temp)).trim()+",";

} else {

value += temp.trim()+",";

}

}

}

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

int k = 0;

if(r % 200 == 0) {//文件拆分，每200行一个文件

k++;

id = k+user_id+DateUtils.getDateTimeStr()+"p";

//保存文件信息

dimUserFileConfService.addUserGiFile(id, filename+"_p"+k, user_id, user_name, new Date());

}

//处理用户地理信息保存

dimUserGiConfService.addUserGi(str[0], str[1], str[2], id);

}

if(!id.endsWith("p")) {

//保存文件信息

dimUserFileConfService.addUserGiFile(id, filename, user_id, user_name, create_time);

}

}

} catch (IOException e) {

e.printStackTrace();

}finally {

try {

if(workbook1 != null) {

workbook1.close();

}

if(workbook2 != null) {

workbook2.close();

}

} catch (IOException e) {

e.printStackTrace();

}

}

return new RestResult(true, "ok");

}

maybatis

INSERT INTO dim_user_gi_file

values

(#{id},#{f_name},#{user_id},#{user_name},#{create_time, jdbcType=DATE})