Vue3中的Excel数据管理：下载模板并导入数据

上传功能

代码一

<el-dialog :title="upload.title" v-model="upload.open" width="400px" append-to-body>
    <!--
    action 使用 axios 上传，#号
    limit 只允许上传一个文件
    accept 限制格式
    on-progress 上传中回调方法，做上传禁用的功能
    disabled 禁用开关
    auto-upload 禁止自动上传
    on-change 上传后的处理
    -->
    <el-upload action="#" :limit="1" accept=".xlsx, .xls" :on-progress="handleFileProgress"
               :disabled="upload.isUploading" :auto-upload="false" drag
               :on-change="handleChangeUpload">

        <!--icon 标签-->
        <el-icon class="el-icon--upload">
            <UploadFilled/>
        </el-icon>
        <div class="el-upload__text">将文件拖到此处，或<em>点击上传</em></div>

        <template #tip>
            <div class="el-upload__tip text-center">
                <span>仅允许导入xls、xlsx格式文件。</span>
                <!--下载模版-->
                <el-link type="primary" :underline="false" style="font-size:12px;vertical-align: baseline;"
                         @click="importTemplate">
                    下载模板
                </el-link>
            </div>
        </template>
    </el-upload>
    <template #footer>
        <div class="dialog-footer">
            <!--确定后下载-->
            <el-button type="primary" @click="submitFileForm">确 定</el-button>
            <el-button @click="upload.open = false">取 消</el-button>
        </div>
    </template>
</el-dialog>

代码二

html

<el-dialog :title="upload.title" v-model="upload.open" width="400px" append-to-body>
    <el-upload :action="upload.url" ref="uploadRef" :limit="1" accept=".xlsx, .xls"
               :on-progress="handleFileProgress"
               :disabled="upload.isUploading" :auto-upload="false" drag
               :headers="upload.headers" :on-success="handleFileSuccess">

        <el-icon class="el-icon--upload">
            <UploadFilled/>
        </el-icon>
        <div class="el-upload__text">将文件拖到此处，或<em>点击上传</em></div>

        <template #tip>
            <div class="el-upload__tip text-center">
                <span>仅允许导入xlsx格式文件。</span>
                <el-link type="primary" :underline="false" style="font-size:12px;vertical-align: baseline;"
                         @click="importTemplate">
                    下载模板
                </el-link>
            </div>
        </template>
    </el-upload>
    <template #footer>
        <div class="dialog-footer">
            <el-button type="primary" @click="submitFileForm">确 定</el-button>
            <el-button @click="upload.open = false">取 消</el-button>
        </div>
    </template>
</el-dialog>

/*** 用户导入参数 */
const upload = ref({
    open: false,
    title: '设备导入',
    // 是否禁用上传
    isUploading: false,
    headers: { Authorization: 'Bearer ' + getToken() },
    url: import.meta.env.VITE_APP_BASE_API + '/ar_check/device/importData'
})

// 打开导入
function handleTeamImport() {
    upload.value.open = true
}

// 文件上传中
function handleFileProgress() {
    upload.value.isUploading = true
}

// 文件上传完成
function handleFileSuccess(response, file) {
    upload.value.open = false
    upload.value.isUploading = false
    proxy.$refs['uploadRef'].handleRemove(file)
    proxy.$alert('<div style=\'overflow: auto;overflow-x: hidden;max-height: 70vh;padding: 10px 20px 0;\'>' +
        response.msg + '</div>', '导入结果', { dangerouslyUseHTMLString: true })
    getList()
}

// 下载模板
function importTemplate() {
    proxy.download('ar_check/device/importTemplate', {}, `device_template_${new Date().getTime()}.xlsx`)
}

// 确定上传
async function submitFileForm() {
    proxy.$refs.uploadRef.submit()
}

介绍一下 POI OOXML 的使用

首先需要创建一个工作薄，这里有几个注意事项

• HSSF － 提供读写Microsoft Excel XLS格式档案的功能。
• XSSF － 提供读写Microsoft Excel OOXML XLSX格式档案的功能。
• HWPF － 提供读写Microsoft Word DOC97格式档案的功能。
• XWPF － 提供读写Microsoft Word DOC2003格式档案的功能。
• HSLF － 提供读写Microsoft PowerPoint格式档案的功能。
• HDGF － 提供读Microsoft Visio格式档案的功能。
• HPBF － 提供读Microsoft Publisher格式档案的功能。
• HSMF － 提供读Microsoft Outlook格式档案的功能。

在工作页创建行以及单元格。

// 新建一个工作簿
Workbook workbook = new XSSFWorkbook();
// 创建sheet页
Sheet sheet = workbook.createSheet();

// 在第三行开始写
Row row = sheet.createRow(3);
Cell cell = row.createCell(0);
cell.setCellValue("哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈");

// 合并行，表示表的第三列，结束第三列，合并 0 - 6 行
sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 8));

// 创建单元格样式
CellStyle cellStyle = workbook.createCellStyle();
// 设置单元格居中方式---左对齐
cellStyle.setAlignment(HorizontalAlignment.LEFT);

// 应用单元格样式
cell.setCellStyle(cellStyle);

// 将数据写入到文件中
FileOutputStream fout = new FileOutputStream("D:\\Downloads\\example.xlsx");
workbook.write(fout);
fout.close();
workbook.close();

设置单元格格式以及字体样式

// 创建单元格样式
CellStyle cellStyle2 = workbook.createCellStyle();
// 设置单元格居中方式---居中
cellStyle2.setAlignment(HorizontalAlignment.CENTER);
// 设置单元格颜色--灰色
cellStyle2.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
// 使用单一颜色填充
cellStyle2.setFillPattern(FillPatternType.SOLID_FOREGROUND);

// 字体格式
Font font = workbook.createFont();
// 加粗
font.setBold(true);
// 设置颜色--白色
font.setColor(IndexedColors.WHITE.getIndex());
// 将字体设置到样式上
cellStyle2.setFont(font);

// 最后单元格使用样式
t1.setCellStyle(cellStyle2);

最后输出到指定位置

// 创建一个文件输出流，用于将工作簿写入文件
FileOutputStream fout = new FileOutputStream("D:\\Downloads\\example.xlsx");
// 将工作簿写入文件流
workbook.write(fout);
// 关闭文件流
fout.close();
// 关闭工作簿
workbook.close();

获取 excel 的图片

图片是独立的，根据单元格的位置获取

InputStream inputStream = Files.newInputStream(Paths.get("D:\\Downloads\\device_template_1714287622392.xlsx"));
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);

// 获取第一个 sheet 表
XSSFSheet sheet = workbook.getSheetAt(0);
if (sheet == null) {
    throw new IOException("损坏的文件！");
}

// 获取绘图包
XSSFDrawing drawing = sheet.getDrawingPatriarch();
if (drawing != null) {
    // 获取所有图形形状
    List<XSSFShape> shapes = drawing.getShapes();
    for (XSSFShape shape : shapes) {

        // 形状获取对应的图片数据
        XSSFPicture picture = (XSSFPicture) shape;
        XSSFPictureData pictureData = picture.getPictureData();

        //图片形状在工作表中的位置, 所在行列起点和终点位置
        XSSFClientAnchor anchor = (XSSFClientAnchor) shape.getAnchor();

        // 行
        short col1 = anchor.getCol1();
        // 列
        int row1 = anchor.getRow1();
        //文件扩展名
        String suffix = pictureData.suggestFileExtension();
        // 文件格式
        String mimeType = pictureData.getMimeType();
        // 文件数据
        byte[] data = pictureData.getData();

        // 转换成 MultipartFile 进行文件上传，参数一和参数二是文件名，参数三是类型，参数四是字节数据
        MultipartFile multipartFile = new MockMultipartFile("", "", mimeType, data);
    }
}

转换 MultipartFile 方式二

代码实现

MultipartFile multipartFile = new CommonsMultipartFile(createFileItem(data, mimeType));
if (col1 == k && row1 == i) {
    try {
        return FileUploadUtils.upload(multipartFile);
    } catch (IOException e) {
        log.error("系统上传excel图片异常");
        return null;
    }
}

createFileItem 函数

private static FileItem createFileItem(byte[] data, String mimeType) {
    final DiskFileItemFactory fac = new DiskFileItemFactory();
    FileItem fileItem = fac.createItem("文件名", mimeType, true, "文件名");
    final OutputStream fileItemOutStream;
    try {
        fileItemOutStream = fileItem.getOutputStream();
    } catch (IOException e) {
        log.error("获取FileItem输出流异常：{}", e.getMessage(), e);
        throw new ServiceException("系统异常");
    }

    try {
        IOUtils.copy(new ByteArrayInputStream(data), fileItemOutStream);
    } catch (IOException e) {
        log.error("写入FileItem异常：{}", e.getMessage(), e);
        throw new ServiceException("系统异常");
    }
    return fileItem;
}

下载模板功能实现

首先后端完成上面的excel 文件格式内容等

前端调用 SpringMvc 接口

import { saveAs } from 'file-saver'
try {
    const downloadLoadingInstance = ElLoading.service({ text: '正在下载数据，请稍候', background: 'rgba(0, 0, 0, 0.7)' })
    // 发送 POST 请求到指定的 URL，获取响应数据
    const data = await service.post(url, {}, {
        // 设置请求头为 'application/x-www-form-urlencoded'
        headers: { 'Content-Type': 'application/x-www-form-urlencoded' },
        // 设置响应类型为 Blob
        responseType: 'blob'
    })
    // 如果响应数据存在
    if (data) {
        // 检查响应数据是否为 Blob 类型---是否是JSON
        const isBlob = blobValidate(data)
        if (isBlob) {
            // 如果是 Blob 类型，则创建 Blob 对象并保存为文件
            const blob = new Blob([data])
            saveAs(blob, filename)
        }
    }
    // 关闭加载动画
    downloadLoadingInstance.close()
} catch (error) {
    console.error(error)
    // 显示下载文件错误信息
    ElMessage.error('下载文件出现错误，请联系管理员！')
    // 关闭加载动画
    downloadLoadingInstance.close()
}

上传导入

需要创建一个输入流来读取 execel 文件。

getLastRowNum 获取有文本内容的最后一行列的索引，因为我这里前五行是解释内容，所以 == 5 表示没有数据。

如果是前端传递的，要这样写 MultipartFile 的 getInputStream 方法获取流

InputStream inputStream = Files.newInputStream(Paths.get("D:\\Downloads\\team_template_1714287622392.xlsx"));
Workbook workbook = WorkbookFactory.create(inputStream);
// 获取第一个 sheet 表
Sheet sheet = workbook.getSheetAt(0);
if (sheet == null) {
    throw new IOException("损坏的文件！");
}
if (sheet.getLastRowNum() == 5) {
    throw new IOException("导入班组数据不能为空！");
}

由于 poi ooxml 读取数据需要指定类型很麻烦，我做了一个方法转换

/**
 * 将单元格的值以字符串形式返回。
 *
 * @param cell 单元格对象
 * @return 字符串形式的单元格值，如果单元格为 null，则返回空字符串
 */
public static String getCellValueAsString(Cell cell) {
    if (cell == null) {
        return "";
    }

    switch (cell.getCellType()) {
        case NUMERIC:
            // 如果单元格类型为数字，将数字转换为字符串并去除空格
            return String.valueOf((long) cell.getNumericCellValue()).trim();
        case BOOLEAN:
            // 如果单元格类型为布尔值，将布尔值转换为字符串并去除空格
            return String.valueOf(cell.getBooleanCellValue()).trim();
        case STRING:
            // 如果单元格类型为字符串，直接返回字符串并去除空格
            return cell.getStringCellValue().trim();
        case FORMULA:
            // 如果单元格是公式，将计算结果作为字符串返回，并去除空格
            return cell.getCellFormula().trim();
        case BLANK:
            // 如果单元格为空，返回空字符串
            return "";
        case ERROR:
            // 如果单元格是错误类型，返回错误码作为字符串
            return String.valueOf(cell.getErrorCellValue());
        default:
            // 其他类型的单元格返回空字符串
            return "";
    }
}

封装方法

判断当前行否为空

在某些情况下，用户输入了空格信息，没有实际的内容。

/**
 * 判断Excel行是否为空
 * @param row Excel行对象
 * @return 如果行为空，则返回true；否则返回false
 */
public static boolean isRowEmpty(Row row) {
    if (row == null) {
        return true;
    }
    
    for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
        Cell cell = row.getCell(i, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
        if (cell != null) {
            if (cell.getCellType() == CellType.STRING) {
                String value = cell.getStringCellValue().trim();
                if (!value.isEmpty()) {
                    return false;
                }
            } else if (cell.getCellType() != CellType.BLANK) {
                return false;
            }
        }
    }
    
    return true;
}