springboot 对Excel 操作
1 pom.xml
org.apache.poi poi 3.17 org.apache.poi poi-ooxml 3.152 mapper
@Mapper
public interface SysExcelMapper {
int excelToDB(@Param(“name”) String name,@Param(“url”) String url);
}
service
public interface SysExcelService {
//处理上传的excel文件
List<List> getBankListByExcel(InputStream in, String fileName)throws Exception;
//判断excel文件的格式
Workbook getWorkbook(InputStream inStr, String fileName)throws Exception;
int excelToDB(String name,String url);
}
3 serviceImpl
package com.back.service.impl;
import com.back.mapper.SysExcelMapper;
import com.back.service.SysExcelService;
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.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
/**
-
@ClassName SysExcelServiceImpl
-
@Description TODO
-
@Author
-
@Date 2019/1/11 14:00
**/
@Service
public class SysExcelServiceImpl implements SysExcelService {
@Autowired
private SysExcelMapper sysExcelMapper;
private final static String excel2003 =".xls";
private final static String excel2007 =".xlsx";
@Override
public List<List> getBankListByExcel(InputStream in, String fileName) throws Exception {
List<List> list = null;
//创建excel
Workbook work = this.getWorkbook(in,fileName);
if(nullwork){
throw new Exception(“创建Excel工作薄为空!”);
}
Sheet sheet = null;
Row row = null;
Cell cell = null;
list = new ArrayList<List>();
for (int i = 0;i<work.getNumberOfSheets();i++){
sheet = work.getSheetAt(i);
if(sheetnull){
continue;
}
for(int j = sheet.getFirstRowNum();j<=sheet.getLastRowNum();j++){
row = sheet.getRow(j);
if(row==null||row.getFirstCellNum()==j){
continue;
}
List li = new ArrayList();
for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
cell = row.getCell(y);
li.add(cell);
}
list.add(li);
}
}
work.close();
return list;
}@Override
public Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
Workbook wb = null;
String fileType = fileName.substring(fileName.lastIndexOf("."));
if(excel2003.equals(fileType)){
wb = new HSSFWorkbook(inStr);
}else if(excel2007.equals(fileType)){
wb = new XSSFWorkbook(inStr);
}else{
throw new Exception(“解析的文件格式有误!”);
}
return wb;
}@Override
public int excelToDB(String name, String url) {
return sysExcelMapper.excelToDB(name,url);
}
}
4 controller
package com.back.controller;
import com.back.entity.ResponseEntity;
import com.back.service.SysExcelService;
import io.swagger.annotations.Api;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
/**
- @ClassName SysExcelController
- @Description TODO
- @Author
- @Date 2019/1/11 14:15
**/
@RestController
@Slf4j
@Api(description = “excel文档”)
public class SysExcelController {
@Autowired
private SysExcelService sysExcelService;
@RequestMapping(value = “/excel”,method = RequestMethod.POST)
public ResponseEntity excel(@RequestParam(“file”) MultipartFile file) {
ResponseEntity responseEntity = new ResponseEntity();
InputStream inputStream =null;
List<List> list = null;
if(file.isEmpty()){
log.info(“文件不能为空”);
return responseEntity.fail(300,“文件不能为空”);
}
try {
inputStream = file.getInputStream();
list = sysExcelService.getBankListByExcel(inputStream,file.getOriginalFilename());
inputStream.close();
//连接数据库部分
for (int i = 0; i < list.size(); i++) {
List lo = list.get(i);
//调用mapper中的excelToDB方法
sysExcelService.excelToDB(String.valueOf(lo.get(0)),String.valueOf(lo.get(1)));
}
log.info(“处理成功”);
return responseEntity.success(200,“处理成功”);
} catch (IOException e) {
log.info(“文件流异常”);
return responseEntity.fail(300,“文件流异常”);
} catch (Exception e) {
log.info(“文件流异常”);
return responseEntity.fail(300,“文件流异常”);
}
}
}