废话不多说，直接上代码
excel导入的工具类
import mon.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.x.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;/*** @author* @version 1.0*/public class ReadPatientExcelUtil {//总行数private static int totalRows = 0;//总条数private static int totalCells = 0;//错误信息接收器private static String errorMsg;/*** 读EXCEL文件，获取信息集合** @return*/public static List<BGoodsInfo> getExcelInfo(MultipartFile mFile) {String fileName = mFile.getOriginalFilename();//获取文件名try {if (!validateExcel(fileName)) {// 验证文件名是否合格return null;}boolean isExcel = true;// 根据文件名判断文件是版本还是版本if (isExcel(fileName)) {isExcel = false;}List<BGoodsInfo> userList = createExcel(mFile.getInputStream(), isExcel);return userList;} catch (Exception e) {e.printStackTrace();}return null;}/*** 根据excel里面的内容读取客户信息** @param is输入流* @param isExcel excel是还是版本* @return* @throws IOException*/public static List<BGoodsInfo> createExcel(InputStream is, boolean isExcel) {try {Workbook wb = null;if (isExcel) {// 当excel是时,创建excelwb = new HSSFWorkbook(is);} else {// 当excel是时,创建excelwb = new XSSFWorkbook(is);}List<BGoodsInfo> userList = readExcelValue(wb);// 读取Excel里面客户的信息return userList;} catch (IOException | ParseException e) {e.printStackTrace();}return null;}/*** 读取Excel里面客户的信息** @param wb* @return*/private static List<BGoodsInfo> readExcelValue(Workbook wb) throws ParseException {//默认会跳过第一行标题// 得到第一个shellSheet sheet = wb.getSheetAt(0);// 得到Excel的行数totalRows = sheet.getPhysicalNumberOfRows();// 得到Excel的列数(前提是有行数)if (totalRows > 1 && sheet.getRow(0) != null) {totalCells = sheet.getRow(0).getPhysicalNumberOfCells();}List<BGoodsInfo> userList = new ArrayList<BGoodsInfo>();// 循环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) { //第一列//如果是纯数字,将单元格类型转为Stringif (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());// 添加到listuserList.add(user);}return userList;}/*** 验证EXCEL文件** @param filePath* @return*/public static boolean validateExcel(String filePath) {if (filePath == null || !(isExcel(filePath) || isExcel(filePath))) {errorMsg = "文件名不是excel格式";return false;}return true;}// @描述：是否是的excel，返回true是public static boolean isExcel(String filePath) {return filePath.matches("^.+\\.(?i)(xls)$");}//@描述：是否是的excel，返回true是public static boolean isExcel(String filePath) {return filePath.matches("^.+\\.(?i)(xlsx)$");}}h
对于插入的数据进行验证
public void importExcel(MultipartFile file) throws IOException {List<BGoodsInfo> excelInfo = ReadPatientExcelUtil.getExcelInfo(file);for (BGoodsInfo s : excelInfo) {QueryWrapper<BGoodsInfo> 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<BGoodsInfo> set = new LambdaUpdateWrapper<BGoodsInfo>().eq(BGoodsInfo::getId, list.getId());log.info("该商品已经备案过，修改该商品备案的数据开始{}", s);update(s, set);}}}
如果觉得《excel文件导入到mysql数据库中》对你有帮助，请点赞、收藏，并留下你的观点哦！
