工作中經常遇到Excel模板下載，然后填好后再導入的情況，簡單記錄下，方便下次使用

Excel模版下載（返回Base64）

模板文件存放位置

import java.util.Base64;
import org.apache.commons.io.IOUtils;
import org.springframework.core.io.ClassPathResource;/*** 下載模板* @return*/public Response<RespDownLoad> downloadTemplateFile() {RespDownLoad respDownLoad = new RespDownLoad();try{String fileName = "稅費商戶白名單導入模板.xlsx";ClassPathResource resource = new ClassPathResource("excelTemplate/template.xlsx");InputStream inputStream = resource.getInputStream();// 將流轉為Base64respDownLoad.setFileData(Base64.getEncoder().encodeToString(IOUtils.toByteArray(inputStream)));respDownLoad.setFileName(fileName);return  Response.success(respDownLoad, "文件輸出流生成成功");}catch (Exception e) {e.printStackTrace();}return  Response.fail("99999", "文件輸出失敗");}

@Data
public class RespDownLoad implements Serializable {private String fileData;private String fileName;
}

文件導入-傳Base64

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;private static final String FILE_EXTENSION = "xls,xlsx";private static final String STR_POINT = ".";private static final String STR_SPLIT = ",";private static final int MAX_SIZE = 500;/*** 文件導入 傳base64* @return*/public Response uploadFileByBase64(Request<UploadReq> request) throws IOException {UploadReq body = request.getBody();if (null == body.getFileData()) {log.error("上傳文件為空");throw new BizException("99999","上傳文件為空");}// 獲取上傳時的文件名稱String fileName = body.getFileName();// 文件名稱校驗if (checkFileName(fileName)) {log.error("[baseUpload][文件名稱不允許包含危險字符]");throw new BizException("99999","文件名稱不允許包含危險字符");}//文件后綴校驗checkFileNameExtension(fileName);//base64轉fileMultipartFile file = convertBase64ToMultipartFile(body.getFileData(), fileName);try (InputStream inputStream = file.getInputStream()) {Workbook workbook = new XSSFWorkbook(inputStream);Sheet sheet = workbook.getSheetAt(0);List<BuppsTaxMercode> list = new ArrayList<>();// 使用 sheet.rowIterator() 并跳過第一行Row firstRow = sheet.getRow(0);if (firstRow == null) {log.error("第一行不存在，文件格式錯誤！");throw new BizException("99999", "第一行不存在，文件格式錯誤！");}// 從第二行開始迭代，存入listfor (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {Row row = sheet.getRow(rowIndex);if (row == null) {continue; // 避免空行}BuppsTaxMercode info = new BuppsTaxMercode();info.setMerchCode(convertCelType(row.getCell(0)));info.setMerchName(convertCelType(row.getCell(1)));info.setTenantId(convertCelType(row.getCell(2)));info.setStatus(convertCelType(row.getCell(3)));info.setCreateDatetime(DateUtil.getCurrentDateTime14());if(org.springframework.util.StringUtils.isEmpty(info.getMerchCode())){continue;}list.add(info);}// 批量插入數據庫if (!CollectionUtils.isEmpty(list)) {if (list.size() > MAX_SIZE) {log.error("超出上傳上限,請分批處理!");throw new BizException("99999", "超出上傳上限,請分批處理!");}manager.saveBatch(list);} else {throw new BizException("99999", "請勿上傳空文件！");}} catch (Exception e) {log.error("文件導入異常", e);throw new BizException("99999", "文件導入異常");}return Response.success();}/*** 匹配字符串中的../及< > " &等危險字符** @param fileName* @return*/private Boolean checkFileName(String fileName) {String regex = "[&<>\"'/]|(\\.\\./)";Pattern pattern = Pattern.compile(regex);Matcher matcher = pattern.matcher(fileName);return matcher.find();}/*** 校驗文件擴展名*/public void checkFileNameExtension(String fileName) {if (org.springframework.util.StringUtils.isEmpty(fileName) || fileName.lastIndexOf(STR_POINT) <= 0) {throw new BizException("99999","文件名不符合要求");}String fileExt = fileName.substring(fileName.lastIndexOf(STR_POINT) + 1);String[] extList = FILE_EXTENSION.split(STR_SPLIT);if (!Arrays.asList(extList).contains(fileExt.toLowerCase())) {throw new BizException("99999","不支持的文件后綴");}}public static String convertCelType(Cell cell) {switch (cell.getCellType()) {case NUMERIC:return String.valueOf((int) cell.getNumericCellValue());case STRING:return cell.getStringCellValue();default:return null;}}public static MultipartFile convertBase64ToMultipartFile(String base64String, String fileName) throws IOException {// Step 1: 解碼Base64字符串byte[] decodedBytes = Base64.getDecoder().decode(base64String);// Step 2: 轉換為InputStreamByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(decodedBytes);// Step 3: 創建MultipartFileMultipartFile multipartFile = new MockMultipartFile(fileName, fileName, "application/vnd.ms-excel;charset=utf-8", byteArrayInputStream);return multipartFile;}

@Data
public class UploadReq implements Serializable {private String fileData;private String fileName;
}

文件導入-傳MultipartFile

 /*** 文件導入，傳MultipartFile* @return*/@RequestMapping("/uploadFileByStream")@ResponseBodypublic Response uploadFileByStream(@RequestParam MultipartFile file) {if (null == file) {log.error("上傳文件為空");throw new BizException("99999","上傳文件為空");}// 獲取上傳時的文件名稱String fileName = file.getOriginalFilename();// 文件名稱校驗if (checkFileName(fileName)) {log.error("[baseUpload][文件名稱不允許包含危險字符]");throw new BizException("99999","文件名稱不允許包含危險字符");}//文件后綴校驗checkFileNameExtension(fileName);try (InputStream inputStream = file.getInputStream()) {Workbook workbook = new XSSFWorkbook(inputStream);Sheet sheet = workbook.getSheetAt(0);List<BuppsTaxMercode> list = new ArrayList<>();// 使用 sheet.rowIterator() 并跳過第一行Row firstRow = sheet.getRow(0);if (firstRow == null) {log.error("第一行不存在，文件格式錯誤！");throw new BizException("99999", "第一行不存在，文件格式錯誤！");}// 從第二行開始迭代，存入listfor (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {Row row = sheet.getRow(rowIndex);if (row == null) {continue; // 避免空行}BuppsTaxMercode info = new BuppsTaxMercode();info.setMerchCode(convertCelType(row.getCell(0)));info.setMerchName(convertCelType(row.getCell(1)));info.setTenantId(convertCelType(row.getCell(2)));info.setStatus(convertCelType(row.getCell(3)));info.setCreateDatetime(DateUtil.getCurrentDateTime14());if(org.springframework.util.StringUtils.isEmpty(info.getMerchCode())){continue;}list.add(info);}// 批量插入數據庫if (!CollectionUtils.isEmpty(list)) {if (list.size() > MAX_SIZE) {log.error("超出上傳上限,請分批處理!");throw new BizException("99999", "超出上傳上限,請分批處理!");}manager.saveBatch(list);} else {throw new BizException("99999", "請勿上傳空文件！");}} catch (Exception e) {log.error("文件導入異常", e);throw new BizException("99999", "文件導入異常");}return Response.success();}