java 导入、导出Excel文件，后端代码

controller

import com.github.xiaoymin.knife4j.annotations.ApiOperationSupport;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springblade.common.utils.StringUtils;
import org.springblade.core.boot.ctrl.BladeController;
import org.springblade.core.excel.util.ExcelUtil;
import org.springblade.core.tool.utils.BeanUtil;
import org.springblade.core.tool.utils.DateUtil;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 学生信息 控制器
 *
 * @author BladeX
 * @since 2022-09-15
 */
@Slf4j
@RestController
@AllArgsConstructor
@RequestMapping("/major")
@Api(value = "学生信息", tags = "学生信息接口")
public class MajorController extends BladeController {

	/**
	 * 导出
	 * @author xuyh
	 * @date 2022/9/15
	 * @param response
	 * @param major
	 */
	@GetMapping("/export")
	@ApiOperation(value = "导出")
	@ApiOperationSupport(order = 4)
	public void export(Student stu, HttpServletResponse response) {
		if (ObjectUtils.isNotEmpty(stu)) {
			// 查询要导出的数据
			List<Student> stuList = studentService.selectStuList(stu);
			List<StudentExcel> result = new ArrayList<>();
			stuList .forEach(item -> {
				StudentExcel stuExcel = new StudentExcel();
				BeanUtils.copyProperties(item, StudentExcel);
				result.add(stuExcel);
			});
			ExcelUtil.export(response, "学生信息表" + DateUtil.time(), "学生信息", result, StudentExcel.class);
		}
	}


	/**
	 * 导入
	 * @author xuyh
	 * @date 2022/9/16
	 * @param file
	 * @return org.springblade.core.tool.api.R
	 */
	@PostMapping("/import")
	@ApiOperationSupport(order = 10)
	@ApiOperation(value = "导入Excel", notes = "传参MultipartFile")
	public R importExcel(MultipartFile file) throws Exception {
		try {
			String result = studentService.importExcel(file);
			return ObjectUtil.isEmpty(result) ? R.success("操作成功") : R.fail("导入失败, 错误详情: " + result);
		} catch (ParseException e) {
			log.error("导入异常 " + e.getMessage(), e);
			return R.fail("导入数据格式错误" +  e.getMessage());
		} catch (Exception e) {
			log.error("导入异常 " + e.getMessage(), e);
			return R.fail("导入异常 " + e.getMessage());
		}
	}
}

sevice

import org.springframework.web.multipart.MultipartFile;

/**
 * 学生信息 服务类
 */
public interface IMajorService extends BaseService<Major> {

	/**
	 * 导入
	 * @author xuyh
	 * @date 2022/9/16
	 * @param file
	 * @return java.lang.String
	 */
	String importExcel(MultipartFile file) throws Exception;
}

serviceImpl

import org.springblade.core.excel.util.ExcelUtil;
import org.springblade.core.log.exception.ServiceException;
import org.springblade.core.tool.utils.BeanUtil;
import org.springblade.core.tool.utils.CollectionUtil;
import org.springframework.web.multipart.MultipartFile;

import java.util.*;

/**
 * 学生信息 服务实现类
 */
@Service
public class StudentServiceImpl extends BaseServiceImpl<StudentMapper, Student> implements IStudentService {
	@Override
	public String importExcel(MultipartFile file) throws Exception{

		// 获取文件名、文件类型
		String filename = file.getOriginalFilename();
		String fileType = filename.substring(filename.lastIndexOf(".") + 1).toLowerCase(Locale.US);
		StringBuilder message = new StringBuilder();
		// 检查文件是否规范
		if (fileType.equals("xls") || fileType.equals("xlsx")) {
			ImportCheckTools importCheckTools = new ImportCheckTools();
			String[] colimnName = {"姓名*", "性别*", "年级*", "班级*", "电话", "备注"};
			message = importCheckTools.verificationStudentExcelHeadLine(file, colimnName);
			if (StringUtils.isNotEmpty(message)) {
				return message;
			}
		} else {
			throw new ServiceException("仅支持上传.xls 或 .xlsx 文件");
		}

		/* 文件数据校验 */
		List<Student> StudentList = new ArrayList<>();
		// 读取Excel
		List<StudentExcel> stuExcelList = ExcelUtil.read(file, MajorTemplateExcel.class);

		// 文件空判断
		if (CollectionUtil.isEmpty(stuExcelList)) {
			message.append("导入文件为空");
			return message;
		}
	
		for (StudentExcel excel : stuExcelList) {
			Student stu = new Student();
			BeanUtil.copyProperties(excel, stu);
			studentList.add(stu);
		}
		// 保存
		saveBatch(studentList);
		return message;
	}
}

工具类

// 工具类
import org.apache.http.ParseException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.springblade.common.utils.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;

public class ImportCheckTools {

	/***
	 * 校验导入的列表Excel文件标题行是否为标准行   参数（MultipartFile，自定义规定的字段columnName）
	 */
	public String verificationStudentExcelHeadLine(MultipartFile file, String[] columnName) throws IOException {
		//整个标题名字
		String fileName = file.getOriginalFilename();
		//标题的后缀名字
		String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);
		//判断后缀名是否支持该格式
		if (!"xls".equals(suffix) && !"xlsx".equals(suffix)) {
			throw new ParseException("上传文件只支持xls和xlsx文件后缀");
		}
		InputStream fin = file.getInputStream();
		Workbook wb = null;
		//不同类型的后缀用不同的工具转换
		if ("xls".equals(suffix)) {
			wb = new HSSFWorkbook(fin);
		} else if ("xlsx".equals(suffix)) {
			wb = new XSSFWorkbook(fin);
		}
		//定义返回的标题出错提示
		String result = null;
		Sheet sheet = wb.getSheetAt(0);
		Row row = sheet.getRow(0);
		//获取excel的标题列个数与自定义的标题个数是否大于自定义
		if (row != null && row.getLastCellNum() >= columnName.length) {
			int lastCellNum = row.getLastCellNum();
			for (int idx = 0; idx < lastCellNum; idx++) {
				//取出下标的标题名称   判断与标准的是否一致
				String value = getCellValue(row.getCell(idx));
				if (StringUtils.isBlank(value) || !columnName[idx].equals(value)) {
					return result = "标题行第" + (idx + 1) + "列名称错误！请检查上传的excel模板是否正确";
				}
			}
		} else {
			result = "上传文件首行不能为空或与导出模版的表格表头不一致！";
		}
		return result;
	}
}