Java+element實現excel的匯入和匯出

            <el-upload
              ref="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-button
                class="ml-3"
                style="margin-left: 20px"
                type="success"
                @click="submitUpload"
              >
                上傳檔案
              </el-button>

              僅允許匯入xls、xlsx格式檔案。
            </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()
}

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

  @Override
    public 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();
    }

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

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

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

    @Override
    public 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);
    }

 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();

        // 初始化workbook
        initHSSFWorkbook(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();
    }
}