Entity实体类
package com.cbb.entity;
import java.util.Date;
import org.springframework.format.annotation.DateTimeFormat;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import com.fasterxml.jackson.annotation.JsonInclude;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
/**
* 导入用户信息Excel表格
*
* @author 陈斌斌
*
* @date 2022年5月11日 09点24分
*
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
@JsonInclude(JsonInclude.Include.NON_NULL)
@TableName("user")
public class ImportUser {
/**
* 姓名
*/
private String name;
/**
* 年龄
*/
private Integer age;
/**
* 邮箱
*/
private String email;
/**
* 政治面貌
*/
private String status;
/**
* 注册时间
*/
@TableField(value = "createtime")
@DateTimeFormat(pattern = "yyyy-MM-dd")
private Date createTime;
/**
* 生日
*/
@DateTimeFormat(pattern = "yyyy-MM-dd")
private Date birthday;
}
mapper接口
package com.cbb.mapper;
import java.util.List;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.cbb.entity.ImportCard;
/**
* 这是一个关于批量导入Excel银行卡信息的接口
*
* @author 陈斌斌
* @Date 2022年5月11日 09点25分
*
*/
public interface ImportCardMapper extends BaseMapper<ImportCard> {
/**
*
* 批量添加
*
* @param importExcel
* @return
*/
int addAll(List<ImportCard> importExcel);
}
xml
<insert id="addAll" parameterType="com.cbb.entity.ImportCard">
INSERT INTO CARD (CID,TYPE,PRICE,CREATETIME) VALUES
<foreach collection="list" separator=","
item="item" index="index">
(
#{item.cid},
#{item.type},
#{item.price},
#{item.createTime}
)
</foreach>
</insert>
service接口
package com.cbb.service;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.web.multipart.MultipartFile;
import com.cbb.entity.ImportCard;
/**
* 这是一个关于批量导入Excel银行卡信息业务处理的接口
*
* @author 陈斌斌
* @Date 2022年5月11日 09点25分
*
*/
public interface ImportCardService {
/**
*
* 批量添加银行卡信息
*
* @param importExcel
* @return
*/
int addAll(List<ImportCard> importExcel);
/**
* 导入银行卡信息Excel文件,读取里面的数据,遍历为数组进行添加
*
* @param file
* @param request
* @param response
* @return
*/
List<List<Object>> uploadExcel(MultipartFile file, HttpServletRequest request, HttpServletResponse response);
}
实现类imple
package com.cbb.serviceimple;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import com.cbb.entity.ImportCard;
import com.cbb.mapper.ImportCardMapper;
import com.cbb.service.ImportCardService;
import com.cbb.util.ExcelUtils;
import lombok.RequiredArgsConstructor;
/**
* 这是一个关于批量导入Excel银行卡信息业务处理的实现类,处理业务逻辑
*
* @author 陈斌斌
* @Date 2022年5月11日 09点25分
*
*/
@Service
@RequiredArgsConstructor(onConstructor = @__(@Autowired))
public class ImportCardServiceImple implements ImportCardService {
/**
* 批量导入银行卡信息excel的接口
*/
private final ImportCardMapper importCardMapper;
/**
*
* 批量添加银行卡信息
*
* @param importExcel
* @return
*/
@Override
public int addAll(List<ImportCard> importExcel) {
return importCardMapper.addAll(importExcel);
}
/**
* 导入银行卡信息Excel文件,读取里面的数据,遍历为数组进行添加
*
* @param file
* @param request
* @param response
* @return
*/
@Override
public List<List<Object>> uploadExcel(MultipartFile file, HttpServletRequest request,
HttpServletResponse response) {
if (file.isEmpty()) {
try {
throw new Exception("文件不存在!");
} catch (Exception e) {
e.printStackTrace();
}
}
InputStream in = null;
try {
in = file.getInputStream();
} catch (IOException e) {
e.printStackTrace();
}
List<List<Object>> listob = null;
try {
listob = new ExcelUtils().getBankListByExcel(in, file.getOriginalFilename());
} catch (Exception e) {
e.printStackTrace();
}
return listob;
}
}
导入工具类util
package com.cbb.util;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.stereotype.Component;
/**
* Created by Guanzhong Hu
* Date :2020/2/12
* Description : excel导入工具类
* Version :1.0
*/
@Component
public class ExcelUtils {
private final static String excel2003L =".xls"; //2003- 版本的excel
private final static String excel2007U =".xlsx"; //2007+ 版本的excel
public List<List<Object>> getBankListByExcel(InputStream in, String fileName) throws Exception {
List<List<Object>> list = null;
//创建Excel工作薄
Workbook work = ExcelUtils.getWorkbook(in, fileName);
if (null == work) {
throw new Exception("创建Excel工作薄为空!");
}
Sheet sheet = null; //页数
Row row = null; //行数
Cell cell = null; //列数
list = new ArrayList<List<Object>>();
//遍历Excel中所有的sheet
for (int i = 0; i < work.getNumberOfSheets(); i++) {
sheet = work.getSheetAt(i);
if (sheet == null) {
continue;
}
//遍历当前sheet中的所有行
for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
row = sheet.getRow(j);
if (row == null || row.getFirstCellNum() == j) {
continue;
}
//遍历所有的列
List<Object> li = new ArrayList<Object>();
for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
cell = row.getCell(y);
li.add(ExcelUtils.getValue(cell));
}
list.add(li);
}
}
return list;
}
/**
* @Description:获取IO流中的数据,组装成List<List<Object>>对象
* @param in,fileName
* @return
* @throws IOException
*/
public static List<List<Object>> getListByExcel(InputStream in, String fileName) throws Exception{
List<List<Object>> list = null;
//创建Excel工作薄
Workbook work = getWorkbook(in,fileName);
if(null == work){
throw new Exception("创建Excel工作薄为空!");
}
Sheet sheet = null; //页数
Row row = null; //行数
Cell cell = null; //列数
list = new ArrayList<List<Object>>();
//遍历Excel中所有的sheet
for (int i = 0; i < work.getNumberOfSheets(); i++) {
sheet = work.getSheetAt(i);
if(sheet==null){continue;}
//遍历当前sheet中的所有行
for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
row = sheet.getRow(j);
if(row==null||row.getFirstCellNum()==j){continue;}
//遍历所有的列
List<Object> li = new ArrayList<Object>();
for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
cell = row.getCell(y);
li.add(getValue(cell));
}
list.add(li);
}
}
return list;
}
/**
* @Description:根据文件后缀,自适应上传文件的版本
* @param inStr,fileName
* @return
* @throws Exception
*/
public static Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{
Workbook wb = null;
String fileType = fileName.substring(fileName.lastIndexOf("."));
if(excel2003L.equals(fileType)){
wb = new HSSFWorkbook(inStr); //2003-
}else if(excel2007U.equals(fileType)){
wb = new XSSFWorkbook(inStr); //2007+
}else{
throw new Exception("解析的文件格式有误!");
}
return wb;
}
/**
* @Description:对表格中数值进行格式化
* @param cell
* @return
*/
//解决excel类型问题,获得数值
public static String getValue(Cell cell) {
String value = "";
if(null==cell){
return value;
}
switch (cell.getCellType()) {
//数值型
case NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
//如果是date类型则 ,获取该cell的date值
Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
value = format.format(date);;
}else {// 纯数字
BigDecimal big=new BigDecimal(cell.getNumericCellValue());
value = big.toString();
//解决1234.0 去掉后面的.0
if(null!=value&&!"".equals(value.trim())){
String[] item = value.split("[.]");
if(1<item.length&&"0".equals(item[1])){
value=item[0];
}
}
}
break;
//字符串类型
case STRING:
value = cell.getStringCellValue().toString();
break;
// 公式类型
case FORMULA:
//读公式计算值
value = String.valueOf(cell.getNumericCellValue());
if (value.equals("NaN")) {// 如果获取的数据值为非法值,则转换为获取字符串
value = cell.getStringCellValue().toString();
}
break;
// 布尔类型
case BOOLEAN:
value = " "+ cell.getBooleanCellValue();
break;
default:
value = cell.getStringCellValue().toString();
}
if("null".endsWith(value.trim())){
value="";
}
return value;
}
}
controller层
package com.cbb.controller;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import com.cbb.entity.Card;
import com.cbb.service.CardService;
import lombok.RequiredArgsConstructor;
/**
* 这是一个关于银行卡的控制层
*
* @author 陈斌斌
* @Date 2022年5月11日 09点25分
*
*/
@RestController
@RequiredArgsConstructor(onConstructor = @__(@Autowired))
public class CardController {
/**
* 导入数据
*
* @param filepath
* @return
* @throws Exception
*/
@RequestMapping("importExcelCard")
public Map<String, String> importExcel(MultipartFile file, HttpServletRequest request, HttpServletResponse response)
throws Exception {
//file是前端传来的excel文件
return cardService.importExcel(file, request, response);
}
}