springboot excel操作

springboot 对Excel 操作

1 pom.xml

org.apache.poi poi 3.17 org.apache.poi poi-ooxml 3.15

2 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,“文件流异常”);
  }
  }
  }