废话不多说，直接上代码
excel导入的工具类
import cn.samples.common.utils.DateUtils;
import cn.samples.web.entity.BGoodsInfo;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTMarker;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;
public class ReadPatientExcelUtil {
//总行数
private static int totalRows = 0;
//总条数
private static int totalCells = 0;
//错误信息接收器
private static String errorMsg;
public static List getExcelInfo(MultipartFile mFile) {
String fileName = mFile.getOriginalFilename();//获取文件名
try {
if (!validateExcel(fileName)) {// 验证文件名是否合格
return null;
}
boolean isExcel2003 = true;// 根据文件名判断文件是2003版本还是2007版本
if (isExcel2007(fileName)) {
isExcel2003 = false;
}
List userList = createExcel(mFile.getInputStream(), isExcel2003);
return userList;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
public static List createExcel(InputStream is, boolean isExcel2003) {
try {
Workbook wb = null;
if (isExcel2003) {// 当excel是2003时,创建excel2003
wb = new HSSFWorkbook(is);
} else {// 当excel是2007时,创建excel2007
wb = new XSSFWorkbook(is);
}
List userList = readExcelValue(wb);// 读取Excel里面客户的信息
return userList;
} catch (IOException | ParseException e) {
e.printStackTrace();
}
return null;
}
private static List readExcelValue(Workbook wb) throws ParseException {
//默认会跳过第一行标题
// 得到第一个shell
Sheet sheet = wb.getSheetAt(0);
// 得到Excel的行数
totalRows = sheet.getPhysicalNumberOfRows();
// 得到Excel的列数(前提是有行数)
if (totalRows > 1 && sheet.getRow(0) != null) {
totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
}
List userList = new ArrayList();
// 循环Excel行数
for (int r = 1; r < totalRows; r++) {
Row row = sheet.getRow(r);
if (row == null) {
continue;
}
BGoodsInfo user = new BGoodsInfo();
// 循环Excel的列
for (int c = 0; c < totalCells ; c++) {
Cell cell = row.getCell(c);
if (null != cell) {
if (c == 0) { //第一列
//如果是纯数字,将单元格类型转为String
if (cell.getCellTypeEnum() == CellType.NUMERIC) {
cell.setCellType(CellType.STRING);
}
//商品编码
user.setGCode(cell.getStringCellValue());//将单元格数据赋值给user
} else if (c == 1) {
if (cell.getCellTypeEnum() == CellType.NUMERIC) {
cell.setCellType(CellType.STRING);
}
//商品名称
user.setGName(cell.getStringCellValue());
}else if (c == 2) {
if (cell.getCellTypeEnum() == CellType.NUMERIC) {
cell.setCellType(CellType.STRING);
}
//条形码
user.setGBarCode(cell.getStringCellValue());
} else if (c == 3) {
if (cell.getCellTypeEnum() == CellType.NUMERIC) {
cell.setCellType(CellType.STRING);
}
//区内企业代码
user.setTradeCode(cell.getStringCellValue());
} else if (c == 4) {
if (cell.getCellTypeEnum() == CellType.NUMERIC) {
cell.setCellType(CellType.STRING);
}
//区内企业名称
user.setTradeName(cell.getStringCellValue());
} else if (c == 5) {
if (cell.getCellTypeEnum() == CellType.NUMERIC) {
cell.setCellType(CellType.STRING);
}
//商品规格/型号
user.setGModel(cell.getStringCellValue());
} else if (c == 6) {
if (cell.getCellTypeEnum() == CellType.NUMERIC) {
cell.setCellType(CellType.STRING);
}
//商品料号
user.setGItemNo(cell.getStringCellValue());
} else if (c == 7) {
if (cell.getCellTypeEnum() == CellType.NUMERIC) {
cell.setCellType(CellType.STRING);
}
//申报计量单位
user.setGoodsUnit(cell.getStringCellValue());
} else if (c == 8) {
if (cell.getCellTypeEnum() == CellType.NUMERIC) {
cell.setCellType(CellType.STRING);
}
//识别商品码
user.setGDiscernCode(cell.getStringCellValue());
}else if (c == 9) {
if (cell.getCellTypeEnum() == CellType.NUMERIC) {
cell.setCellType(CellType.STRING);
}
//识别商品名称
user.setGDiscernName(cell.getStringCellValue());
}else if (c == 10) {
if (cell.getCellTypeEnum() == CellType.NUMERIC) {
cell.setCellType(CellType.STRING);
}
//申报人
user.setDeclareUser(cell.getStringCellValue());
}else if (c == 11) {
if (cell.getCellTypeEnum() == CellType.NUMERIC) {
cell.setCellType(CellType.STRING);
}
//申报时间
String stringCellValue = cell.getStringCellValue();
Date date = DateUtils.stringToDate(stringCellValue, "yyyy-MM-dd HH:mm:ss");
user.setDeclareTime(date);
}
}
}
//将excel解析出来的数据赋值给对象添加到list中
user.setCreateTime(new Date());
// 添加到list
userList.add(user);
}
return userList;
}
public static boolean validateExcel(String filePath) {
if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
errorMsg = "文件名不是excel格式";
return false;
}
return true;
}
// @描述：是否是2003的excel，返回true是2003
public static boolean isExcel2003(String filePath) {
return filePath.matches("^.+\.(?i)(xls)$");
}
//@描述：是否是2007的excel，返回true是2007
public static boolean isExcel2007(String filePath) {
return filePath.matches("^.+\.(?i)(xlsx)$");
}
}
h
对于插入的数据进行验证
public void importExcel(MultipartFile file) throws IOException {
List excelInfo = ReadPatientExcelUtil.getExcelInfo(file);
for (BGoodsInfo s : excelInfo) {
QueryWrapper wrapper = new QueryWrapper<>();
wrapper.lambda().eq(BGoodsInfo::getGCode, s.getGCode());
BGoodsInfo list = getOne(wrapper);
if (list == null) {
log.info("该商品没有被备案过，开始备案开始{}", s);
save(s);
continue;
} else {
LambdaUpdateWrapper set = new LambdaUpdateWrapper().eq(BGoodsInfo::getId, list.getId());
log.info("该商品已经备案过，修改该商品备案的数据开始{}", s);
update(s, set);
}
}
}
