package com.platform.service.impl; import com.alibaba.fastjson.JSON; import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl; import com.platform.mapper.ExcelTemplateMapper; import com.platform.model.dto.ExcelTemplateDTO; import com.platform.model.entity.ExcelTemplateEntity; import com.platform.model.vo.ExcelTemplateVO; import com.platform.model.vo.TableHeaderVO; import com.platform.service.ExcelTemplateService; import com.platform.utils.DozerUtils; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import org.springframework.util.ObjectUtils; import org.springframework.web.multipart.MultipartFile; import javax.servlet.http.HttpServletResponse; import java.io.*; import java.net.URLEncoder; import java.util.ArrayList; import java.util.List; /** * @author : wujipeng * @date : 2025/7/30 15:05 * @Version: 1.0 * @Desc : 描述信息 */ @Service public class ExcelTemplateServiceImpl extends ServiceImpl implements ExcelTemplateService { @Override public List pageExcelTemplate(ExcelTemplateDTO excelTemplateDTO) { List excelTemplateList = baseMapper.pageExcelTemplate(excelTemplateDTO); for (ExcelTemplateVO excelTemplateVO : excelTemplateList) { if (!ObjectUtils.isEmpty(excelTemplateVO) && !ObjectUtils.isEmpty(excelTemplateVO.getJson())) { String json = excelTemplateVO.getJson(); List tableHeaderList = JSON.parseArray(json, TableHeaderVO.class); excelTemplateVO.setTableHeaderList(tableHeaderList); } if (!ObjectUtils.isEmpty(excelTemplateVO) && !ObjectUtils.isEmpty(excelTemplateVO.getAllHeader())) { String json = excelTemplateVO.getAllHeader(); List tableHeaderList = JSON.parseArray(json, TableHeaderVO.class); excelTemplateVO.setAllList(tableHeaderList); } } return excelTemplateList; } @Override public ExcelTemplateVO detail(Integer id) { ExcelTemplateVO excelTemplate = DozerUtils.convertBean(getById(id), ExcelTemplateVO.class); if (!ObjectUtils.isEmpty(excelTemplate) && !ObjectUtils.isEmpty(excelTemplate.getJson())) { String json = excelTemplate.getJson(); List tableHeaderList = JSON.parseArray(json, TableHeaderVO.class); excelTemplate.setTableHeaderList(tableHeaderList); } if (!ObjectUtils.isEmpty(excelTemplate) && !ObjectUtils.isEmpty(excelTemplate.getAllHeader())) { String json = excelTemplate.getAllHeader(); List tableHeaderList = JSON.parseArray(json, TableHeaderVO.class); excelTemplate.setAllList(tableHeaderList); } return excelTemplate; } @Override public void export(List ids, HttpServletResponse response) { // 设置响应头 response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setCharacterEncoding("utf-8"); String fileName = "模板.xlsx"; try { // 设置响应头,告诉浏览器这是文件下载 response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8")); } catch (UnsupportedEncodingException e) { throw new RuntimeException("文件名编码异常", e); } // 创建一个excel文件对象 try (XSSFWorkbook workbook = new XSSFWorkbook(); OutputStream outputStream = response.getOutputStream()) { for (int i = 0; i < ids.size(); i++) { ExcelTemplateEntity excelTemplate = this.getById(ids.get(i)); XSSFSheet sheet = workbook.createSheet("sheet" + i); sheet.createRow(0); sheet.createRow(1); sheet.createRow(2); sheet.createRow(3); String json = excelTemplate.getJson(); List tableHeaderList = JSON.parseArray(json, TableHeaderVO.class); for (int j = 0; j < tableHeaderList.size(); j++) { TableHeaderVO tableHeaderVO = tableHeaderList.get(j); if (ObjectUtils.isEmpty(tableHeaderVO)) { sheet.getRow(0).createCell(j).setCellValue("-"); sheet.getRow(1).createCell(j).setCellValue("-"); } else { sheet.getRow(0).createCell(j).setCellValue(tableHeaderVO.getName()); sheet.getRow(1).createCell(j).setCellValue(tableHeaderVO.getIndex()); } } // 自动调整列宽 for (int j = 0; j < tableHeaderList.size(); j++) { sheet.autoSizeColumn(j); } String allHeader = excelTemplate.getAllHeader(); List tableHeaderList1 = JSON.parseArray(allHeader, TableHeaderVO.class); for (int j = 0; j < tableHeaderList1.size(); j++) { TableHeaderVO tableHeaderVO = tableHeaderList1.get(j); if (ObjectUtils.isEmpty(tableHeaderVO)) { sheet.getRow(2).createCell(j).setCellValue("-"); sheet.getRow(3).createCell(j).setCellValue("-"); } else { sheet.getRow(2).createCell(j).setCellValue(tableHeaderVO.getName()); sheet.getRow(3).createCell(j).setCellValue(tableHeaderVO.getIndex()); } } // 自动调整列宽 for (int j = 0; j < tableHeaderList1.size(); j++) { sheet.autoSizeColumn(j); } } // 写入输出流 workbook.write(outputStream); outputStream.flush(); } catch (IOException e) { throw new RuntimeException("导出Excel失败", e); } } @Override @Transactional public Boolean importExcel(MultipartFile file) { // 验证文件是否为空 if (file.isEmpty()) { throw new RuntimeException("导入文件不能为空"); } try (InputStream inputStream = file.getInputStream(); XSSFWorkbook workbook = new XSSFWorkbook(inputStream)) { // 遍历所有sheet for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) { XSSFSheet sheet = workbook.getSheetAt(sheetIndex); // 跳过空sheet if (sheet.getPhysicalNumberOfRows() == 0) { continue; } // 读取表头信息(兼容不同行数的sheet) List jsonHeaders = new ArrayList<>(); List allHeaders = new ArrayList<>(); // 读取jsonHeaders(前两行) if (sheet.getRow(0) != null && sheet.getRow(1) != null) { XSSFRow row0 = sheet.getRow(0); XSSFRow row1 = sheet.getRow(1); int maxCells = Math.max( row0.getLastCellNum(), row1.getLastCellNum() ); for (int cellIndex = 0; cellIndex < maxCells; cellIndex++) { String name = getCellValueAsString(row0.getCell(cellIndex)); int index = getCellValueAsInt(row1.getCell(cellIndex),0); if (!"-".equals(name)) { TableHeaderVO header = new TableHeaderVO(); header.setName(name); header.setIndex(index); jsonHeaders.add(header); } } } // 读取allHeaders(第3、4行,如果有的话) if (sheet.getRow(2) != null && sheet.getRow(3) != null) { XSSFRow row2 = sheet.getRow(2); XSSFRow row3 = sheet.getRow(3); int maxCells = Math.max( row2.getLastCellNum(), row3.getLastCellNum() ); for (int cellIndex = 0; cellIndex < maxCells; cellIndex++) { String name = getCellValueAsString(row2.getCell(cellIndex)); int index = getCellValueAsInt(row3.getCell(cellIndex),0); if (!"-".equals(name)) { TableHeaderVO header = new TableHeaderVO(); header.setName(name); header.setIndex(index); allHeaders.add(header); } } } // 创建或更新模板实体 ExcelTemplateEntity template = new ExcelTemplateEntity(); template.setJson(JSON.toJSONString(jsonHeaders)); template.setAllHeader(JSON.toJSONString(allHeaders)); template.setName(sheet.getSheetName()); // 保存到数据库 this.saveOrUpdate(template); } } catch (IOException e) { throw new RuntimeException("导入Excel失败: " + e.getMessage(), e); } return Boolean.TRUE; } /** * 辅助方法:获取单元格值并转为字符串 */ private String getCellValueAsString(XSSFCell cell) { if (cell == null) { return "-"; } switch (cell.getCellType()) { case STRING: return cell.getStringCellValue(); case NUMERIC: return String.valueOf(cell.getNumericCellValue()); case BOOLEAN: return String.valueOf(cell.getBooleanCellValue()); case FORMULA: return cell.getCellFormula(); default: return "-"; } } /** * 获取单元格值并转为int类型(带默认值) * * @param cell Excel单元格对象 * @param defaultValue 转换失败时返回的默认值 * @return 转换后的int值,如果单元格为空或转换失败则返回指定的默认值 */ private int getCellValueAsInt(XSSFCell cell, int defaultValue) { if (cell == null) { return defaultValue; } try { switch (cell.getCellType()) { case NUMERIC: return (int) cell.getNumericCellValue(); case STRING: String strValue = cell.getStringCellValue().trim(); if (strValue.isEmpty()) { return defaultValue; } return Integer.parseInt(strValue); case BOOLEAN: return cell.getBooleanCellValue() ? 1 : 0; case FORMULA: switch (cell.getCachedFormulaResultType()) { case NUMERIC: return (int) cell.getNumericCellValue(); case STRING: String formulaStrValue = cell.getStringCellValue().trim(); return formulaStrValue.isEmpty() ? defaultValue : Integer.parseInt(formulaStrValue); case BOOLEAN: return cell.getBooleanCellValue() ? 1 : 0; default: return defaultValue; } default: return defaultValue; } } catch (NumberFormatException e) { // 记录日志或根据需要处理异常 return defaultValue; } } @Override public ExcelTemplateEntity getMaxId() { return baseMapper.getMaxId(); } }