本项目是前端vue3，后端springboot开发
需求为：前端导入表格，后端处理表格存储数据，点击按钮可以导出表格。

上传效果：前端点击上传按钮，会跳出选择文件框，选择文件，点击上传。
导出效果：前端点击导出按钮，会跳出下载框，选择位置自动下载。

上传效果图：

下载效果图：

一、上传excel前端代码

            <el-uploadref="file"class="upload-demo":limit="1"accept=".xlsx, .xls"action="http://localhost:8081/admin/perform/importexcel"auto-upload="false"><template #trigger><el-button type="primary">选择文件</el-button></template><el-buttonclass="ml-3"style="margin-left: 20px"type="success"@click="submitUpload">上传文件</el-button><span style="color: red; margin-left: 10px">仅允许导入xls、xlsx格式文件。</span></el-upload>

import { ref, reactive, computed } from "vue"
import { ElMessage, UploadInstance } from "element-plus"const file = ref<UploadInstance>()const submitUpload = () => {file.value!.submit()ElMessage({message: "上传成功",type: "success",})window.location.reload()
}

效果图

二、上传excel后端代码

Controller层

    @PostMapping("/importexcel")public Result importData(MultipartFile file) throws Exception {return performService.importData(file.getInputStream());}

Service层

  @Overridepublic Result importData(InputStream inputStream) throws IOException {// Perform根据自己表格的表头创建的实体，要意义对应List<Perform> res = new ArrayList<>();try {ins = (FileInputStream) inputStream;//true xls文件，false xlsx文件Workbook workbook = null;// XSSFWorkbook instance of HSSFWorkbook 所以通用workbook = new XSSFWorkbook(ins);//获取工作表Sheet sheet = workbook.getSheetAt(0);//获取表头Row rowHead = sheet.getRow(0);//判断表头是否正确if (rowHead.getPhysicalNumberOfCells() < 1) {return Result.error("表头错误");}//获取数据for (int i = 1; i <= sheet.getLastRowNum(); i++) {//获取第一行的用户信息Row row = sheet.getRow(i);String tId;if (row.getCell(0) == null) {tId = "";row.createCell(0).setCellValue(tId);} else {//先设置为字符串再作为数字读出来row.getCell(0).setCellType(CellType.STRING);tId = row.getCell(0).getStringCellValue();}String tName;if (row.getCell(1) == null) {tName = "";row.createCell(1).setCellValue(tName);} else {tName = row.getCell(1).getStringCellValue();}String tDept;if (row.getCell(2) == null) {tDept = "";row.createCell(2).setCellValue(tDept);} else {tDept = row.getCell(2).getStringCellValue();}....................Perorm perform=new Perform()xxxset创建实体System.out.println(perform);res.add(perform);}} catch (IOException e) {e.printStackTrace();} finally {if (ins != null) {try {ins.close();} catch (IOException e) {e.printStackTrace();}}if (out != null) {try {out.close();} catch (IOException e) {e.printStackTrace();}}}new Thread(() -> {//批处理比较快batchInsert(res);}).start();return Result.success(res);}/* 批量插入更快 @param performList*/private void batchInsert(List<Perform> performList) {SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);performList.stream().forEach(perform -> {performMapper.insert(perform);});sqlSession.commit();sqlSession.clearCache();}

三、下载excel前端代码

                <el-buttontype="warning"style="width: 100px"@click="exportInfo()"><a href="http://localhost:8081/admin/perform/exportexcel">导出</a></el-button>

const exportInfo = () => {ElMessage({message: "请稍等",type: "warning",})
}

四、下载excel后端代码

Controller层

    /* 导出表格 @return*/@GetMapping("/exportexcel")public void exportExcel(HttpServletResponse response) throws Exception {performService.exportExcel(response);}

Service层

    @Overridepublic void exportExcel(HttpServletResponse response) throws IOException {System.out.println("导出表格");List<Perform> list = performMapper.selectList(new QueryWrapper<>());String sheetName = "教师业绩表";Map<String, String> titleMap = new LinkedHashMap<>();titleMap.put("tId", "教师工号");titleMap.put("tName", "教师姓名");.....根据自己的表头来ExportExcel.excelExport(response, list, titleMap, sheetName);}

ExportExcel类：

 package com.performance.back.common.utils;import com.baomidou.mybatisplus.core.toolkit.ObjectUtils;
import com.performance.back.admin.dao.entity.Perform;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;/* @ClassName ExportExcel* @Descriotion TODO* @Author nitaotao* @Date 2022/11/14 11:55* @Version 1.0/
public class ExportExcel {private ExportExcel() {}/ 工作簿*/private static HSSFWorkbook workbook;/ sheet*/private static HSSFSheet sheet;/ 标题行开始位置*/private static final int TITLE_START_POSITION = 0;/ 时间行开始位置*/private static final int DATEHEAD_START_POSITION = 1;/ 表头行开始位置*/private static final int HEAD_START_POSITION = 0;/ 文本行开始位置*/private static final int CONTENT_START_POSITION = 1;/* @param sheetName*        sheetName*/private static void initHSSFWorkbook(String sheetName) {workbook = new HSSFWorkbook();sheet = workbook.createSheet(sheetName);sheet.setDefaultColumnWidth(15);}/* 生成标题（第零行创建） @param titleMap  对象属性名称->表头显示名称* @param sheetName sheet名称*/private static void createTitleRow(Map<String, String> titleMap, String sheetName) {CellRangeAddress titleRange = new CellRangeAddress(0, 0, 0, titleMap.size() - 1);sheet.addMergedRegion(titleRange);HSSFRow titleRow = sheet.createRow(TITLE_START_POSITION);HSSFCell titleCell = titleRow.createCell(0);titleCell.setCellValue(sheetName);}/* 创建时间行（第一行创建） @param titleMap 对象属性名称->表头显示名称*/private static void createDateHeadRow(Map<String, String> titleMap) {CellRangeAddress dateRange = new CellRangeAddress(1, 1, 0, titleMap.size() - 1);sheet.addMergedRegion(dateRange);HSSFRow dateRow = sheet.createRow(DATEHEAD_START_POSITION);HSSFCell dateCell = dateRow.createCell(0);dateCell.setCellValue(new SimpleDateFormat("yyyy年MM月dd日").format(new Date()));}/* 创建表头行（第二行创建） @param titleMap 对象属性名称->表头显示名称*/private static void createHeadRow(Map<String, String> titleMap) {// 第1行创建HSSFRow headRow = sheet.createRow(HEAD_START_POSITION);headRow.setHeight((short) 900);int i = 0;for (String entry : titleMap.keySet()) {// 生成一个样式HSSFCellStyle style = workbook.createCellStyle();// 设置这些样式style.setAlignment(HorizontalAlignment.CENTER);//水平居中style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中// 设置边框style.setBorderBottom(BorderStyle.THIN);style.setBorderLeft(BorderStyle.THIN);style.setBorderRight(BorderStyle.THIN);style.setBorderTop(BorderStyle.THIN);// 自动换行style.setWrapText(true);// 生成一个字体HSSFFont font = workbook.createFont();font.setFontHeightInPoints((short) 10);font.setColor(IndexedColors.WHITE.index);font.setBold(false);font.setFontName("宋体");// 把字体 应用到当前样式style.setFont(font);//style设置好后，为cell设置样式HSSFCell headCell = headRow.createCell(i);headCell.setCellValue(titleMap.get(entry));if (i > 14) {// 背景色style.setFillForegroundColor(IndexedColors.BLUE.index);style.setFillPattern(FillPatternType.SOLID_FOREGROUND);style.setFillBackgroundColor(IndexedColors.BLUE.index);} else if (i > 10) {style.setFillForegroundColor(IndexedColors.BLACK.index);style.setFillPattern(FillPatternType.SOLID_FOREGROUND);style.setFillBackgroundColor(IndexedColors.BLACK.index);} else if (i > 7) {style.setFillForegroundColor(IndexedColors.BLUE.index);style.setFillPattern(FillPatternType.SOLID_FOREGROUND);style.setFillBackgroundColor(IndexedColors.BLUE.index);} else if (i >4) {style.setFillForegroundColor(IndexedColors.RED.index);style.setFillPattern(FillPatternType.SOLID_FOREGROUND);style.setFillBackgroundColor(IndexedColors.RED.index);} else {style.setFillForegroundColor(IndexedColors.GREEN.index);style.setFillPattern(FillPatternType.SOLID_FOREGROUND);style.setFillBackgroundColor(IndexedColors.GREEN.index);}headCell.setCellStyle(style);i++;}}/* @param dataList 对象数据集合* @param titleMap 表头   信息*/private static void createContentRow(List<?> dataList, Map<String, String> titleMap) {try {int i = 0;// 生成一个样式HSSFCellStyle style = workbook.createCellStyle();// 设置这些样式style.setAlignment(HorizontalAlignment.CENTER);//水平居中style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中// 设置边框style.setBorderBottom(BorderStyle.THIN);style.setBorderLeft(BorderStyle.THIN);style.setBorderRight(BorderStyle.THIN);style.setBorderTop(BorderStyle.THIN);// 自动换行style.setWrapText(true);// 生成一个字体HSSFFont font = workbook.createFont();font.setFontHeightInPoints((short) 10);font.setColor(IndexedColors.BLACK.index);font.setBold(false);font.setFontName("宋体");// 把字体 应用到当前样式style.setFont(font);//style设置好后，为cell设置样式for (Object obj : dataList) {HSSFRow textRow = sheet.createRow(CONTENT_START_POSITION + i);int j = 0;for (String entry : titleMap.keySet()) {//属性名驼峰式String method = "get" + entry.substring(0, 1).toUpperCase() + entry.substring(1);
//                    System.out.println("调用" + method + "方法");//反射调用Method m = obj.getClass().getMethod(method, null);Object value = m.invoke(obj, null);HSSFCell textcell = textRow.createCell(j);if (ObjectUtils.isNotEmpty(value)) {textcell.setCellValue(value.toString());} else {textcell.setCellValue("");}textcell.setCellStyle(style);j++;}i++;}} catch (Exception e) {e.printStackTrace();}}/* 自动伸缩列（如非必要，请勿打开此方法，耗内存） @param size 列数*/private static void autoSizeColumn(Integer size) {for (int j = 0; j < size; j++) {sheet.autoSizeColumn(j);}}public static void excelExport( HttpServletResponse response, List<Perform> list, Map<String, String> titleMap, String sheetName) throws IOException {//生成表格的不可重复名Date date = new Date();// 初始化workbookinitHSSFWorkbook(sheetName);// 表头行createHeadRow(titleMap);// 文本行createContentRow(list, titleMap);//输出Excel文件OutputStream output=response.getOutputStream();response.reset();//设置响应头，response.setHeader("Content-disposition", "attachment; filename=teacher.xls");response.setContentType("application/msexcel");workbook.write(output);output.close();}
}

因为自己淋过雨，所以想为别人撑把伞