«

怎么使用Java+element实现excel导入和导出

时间:2024-6-16 06:33     作者:韩俊     分类: Java


本篇内容介绍了“怎么使用Java+element实现excel导入和导出”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!

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

上传效果:前端点击上传按钮,会跳出选择文件框,选择文件,点击上传。

导出效果:前端点击导出按钮,会跳出下载框,选择位置自动下载。

上传效果图:

下载效果图:

一、上传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"
                
                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()
}

效果图

二、上传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根据自己表格的表头创建的实体,要意义对应
        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-button
                  type="warning"
                  
                  @click="exportInfo()"
                >
            <a href="https://www.maopiaopiao.com">
const exportInfo = () => {
  ElMessage({
    message: "请稍等",
    type: "warning",
  })
}

四、下载excel后端代码

Controller层

    /**
     * 导出表格
     *
     * @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("导出表格");
        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();

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

标签: java

热门推荐