Java+element實(shí)現(xiàn)excel的導(dǎo)入和導(dǎo)出

更新時(shí)間：2023年04月16日 11:07:59 作者：濤濤英語(yǔ)學(xué)不進(jìn)去

本文主要介紹了Java+element實(shí)現(xiàn)excel的導(dǎo)入和導(dǎo)出，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧

一、上傳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>

              僅允許導(dǎo)入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()
}

效果圖

二、上傳excel后端代碼

Controller層

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

Service層

  @Override
    public Result importData(InputStream inputStream) throws IOException {
    // Perform根據(jù)自己表格的表頭創(chuàng)建的實(shí)體，要意義對(duì)應(yīng)
        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("表頭錯(cuò)誤");
            }
            //獲取數(shù)據(jù)
            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 {
                    //先設(shè)置為字符串再作為數(shù)字讀出來
                    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創(chuàng)建實(shí)體
               
                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-button
                  type="warning"
                  style="width: 100px"
                  @click="exportInfo()"
                >
            <a href="http://localhost:8081/admin/perform/exportexcel" rel="external nofollow" 
                    >導(dǎo)出</a
                  >
                </el-button>

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

四、下載excel后端代碼

Controller層

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

Service層

    @Override
    public void exportExcel(HttpServletResponse response) throws IOException {
        System.out.println("導(dǎo)出表格");
        List<Perform> list = performMapper.selectList(new QueryWrapper<>());
        String sheetName = "教師業(yè)績(jī)表";
        Map<String, String> titleMap = new LinkedHashMap<>();
        titleMap.put("tId", "教師工號(hào)");
        titleMap.put("tName", "教師姓名");
		.....根據(jù)自己的表頭來
        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;
    /***
     * 標(biāo)題行開始位置
     */
    private static final int TITLE_START_POSITION = 0;

    /***
     * 時(shí)間行開始位置
     */
    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);
    }

    /**
     * 生成標(biāo)題（第零行創(chuàng)建）
     *
     * @param titleMap  對(duì)象屬性名稱->表頭顯示名稱
     * @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);
    }

    /**
     * 創(chuàng)建時(shí)間行（第一行創(chuàng)建）
     *
     * @param titleMap 對(duì)象屬性名稱->表頭顯示名稱
     */
    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()));
    }

    /**
     * 創(chuàng)建表頭行（第二行創(chuàng)建）
     *
     * @param titleMap 對(duì)象屬性名稱->表頭顯示名稱
     */
    private static void createHeadRow(Map<String, String> titleMap) {
        // 第1行創(chuàng)建
        HSSFRow headRow = sheet.createRow(HEAD_START_POSITION);
        headRow.setHeight((short) 900);
        int i = 0;
        for (String entry : titleMap.keySet()) {
            // 生成一個(gè)樣式
            HSSFCellStyle style = workbook.createCellStyle();
            // 設(shè)置這些樣式
            style.setAlignment(HorizontalAlignment.CENTER);//水平居中
            style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中

            // 設(shè)置邊框
            style.setBorderBottom(BorderStyle.THIN);
            style.setBorderLeft(BorderStyle.THIN);
            style.setBorderRight(BorderStyle.THIN);
            style.setBorderTop(BorderStyle.THIN);
            // 自動(dòng)換行
            style.setWrapText(true);

            // 生成一個(gè)字體
            HSSFFont font = workbook.createFont();
            font.setFontHeightInPoints((short) 10);
            font.setColor(IndexedColors.WHITE.index);
            font.setBold(false);
            font.setFontName("宋體");

            // 把字體 應(yīng)用到當(dāng)前樣式
            style.setFont(font);
            //style設(shè)置好后，為cell設(shè)置樣式

            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 對(duì)象數(shù)據(jù)集合
     * @param titleMap 表頭   信息
     */
    private static void createContentRow(List<?> dataList, Map<String, String> titleMap) {
        try {
            int i = 0;
            // 生成一個(gè)樣式
            HSSFCellStyle style = workbook.createCellStyle();
            // 設(shè)置這些樣式
            style.setAlignment(HorizontalAlignment.CENTER);//水平居中
            style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中

            // 設(shè)置邊框
            style.setBorderBottom(BorderStyle.THIN);
            style.setBorderLeft(BorderStyle.THIN);
            style.setBorderRight(BorderStyle.THIN);
            style.setBorderTop(BorderStyle.THIN);
            // 自動(dòng)換行
            style.setWrapText(true);

            // 生成一個(gè)字體
            HSSFFont font = workbook.createFont();
            font.setFontHeightInPoints((short) 10);
            font.setColor(IndexedColors.BLACK.index);
            font.setBold(false);
            font.setFontName("宋體");

            // 把字體 應(yīng)用到當(dāng)前樣式
            style.setFont(font);
            //style設(shè)置好后，為cell設(shè)置樣式

            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("調(diào)用" + method + "方法");
                    //反射調(diào)用
                    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();
        }
    }

    /**
     * 自動(dòng)伸縮列（如非必要，請(qǐng)勿打開此方法，耗內(nèi)存）
     *
     * @param size 列數(shù)
     */
    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 {
        //生成表格的不可重復(fù)名
        Date date = new Date();

        // 初始化workbook
        initHSSFWorkbook(sheetName);
        // 表頭行
        createHeadRow(titleMap);
        // 文本行
        createContentRow(list, titleMap);

        //輸出Excel文件
        OutputStream output=response.getOutputStream();
        response.reset();
        //設(shè)置響應(yīng)頭，
        response.setHeader("Content-disposition", "attachment; filename=teacher.xls");
        response.setContentType("application/msexcel");
        workbook.write(output);
        output.close();
    }
}

到此這篇關(guān)于Java+element實(shí)現(xiàn)excel的導(dǎo)入和導(dǎo)出的文章就介紹到這了,更多相關(guān)Java element excel導(dǎo)入和導(dǎo)出內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: