数据可视化大屏项目中，后台管理数据往往需要导入导出的功能以供快速操作数据，今天我们以后端koa来实现导入导出的功能。

接收文件

app.js文件内，我们需要配置一下，文件接收

import koaBody from 'koa-body';

function applyBodyParse(app) {
    app.use(koaBody({
        multipart: true,
        formidable: {
            maxFileSize: 200 * 1024 * 1024 // 设置上传文件大小限制，例如200MB
        }
    }));
}

后端api接口

import { LinRouter, NotFound } from "lin-mizar";
import { PositiveIdValidator } from "../../validator/common";
import { CreateOrUpdateCompanyValidator } from "../../validator/company";
import { CompanyDao } from "../../dao/company";
import * as XLSX from 'xlsx';
import fs from 'fs';
import { Company } from '../../model/company';
const companyApi = new LinRouter({
    prefix: "/v1/company"
});

const companyDao = new CompanyDao();

const responseWrapper = (ctx, data, msg = '操作成功') => {
    ctx.body = {
        code: 1,
        data,
        msg
    };
};

const errorHandler = (ctx, error) => {
    console.error(error);
    ctx.body = {
        code: 0,
        data: null,
        msg: error.message || '操作失败'
    };
};

companyApi.get("/", async (ctx) => {
    try {
        const page = parseInt(ctx.query.page) || 1;
        const pageSize = parseInt(ctx.query.pageSize) || 10;
        const { items, total } = await companyDao.getCompanies(page, pageSize);
        responseWrapper(ctx, { list: items, total, page, pageSize }, '获取公司列表成功');
    } catch (error) {
        errorHandler(ctx, error);
    }
});
companyApi.post("/import", async (ctx) => {
    const file = ctx.request.files.file;
    if (!file) {
        throw new NotFound({ code: 10024, message: '没有上传文件' });
    }

    try {
        const fileContent = fs.readFileSync(file.filepath);
        const workbook = XLSX.read(fileContent, { type: 'buffer' });
        const sheetName = workbook.SheetNames[0];
        const worksheet = workbook.Sheets[sheetName];
        const rawData = XLSX.utils.sheet_to_json(worksheet);

        const parsedData = rawData.map(row => ({
            name: row['名称'] || '',
            industryChainName: row['产业链'] || '',
            about: row['简介'] || '',
            img: row['图片'] || '',
            latitude: row['纬度'] || null,
            longitude: row['经度'] || null
        }));

        const importResult = await Company.importCompanyData(parsedData);
        responseWrapper(ctx, importResult, '导入成功');
    } catch (error) {
        errorHandler(ctx, error);
    }
});

// 添加导出路由
companyApi.get("/export", async (ctx) => {
    try {
        const buffer = await Company.exportCompanyData();
        ctx.set('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        ctx.set('Content-Disposition', 'attachment; filename=companies.xlsx');
        ctx.body = buffer;
    } catch (error) {
        errorHandler(ctx, error);
    }
});

后端model

import {Model, DataTypes} from 'sequelize';
import sequelize from '../lib/db';
import * as XLSX from 'xlsx';

class Company extends Model {
    static associate(models) {
        Company.belongsTo(models.IndustryChain, {
            foreignKey: 'industry_chain_id',
            as: 'industryChain',
            onDelete: 'SET NULL',
            onUpdate: 'CASCADE'
        });
        Company.hasMany(models.Talent, {
            foreignKey: 'company_id',
            as: 'talents'
        });
        Company.hasMany(models.Platform, {
            foreignKey: 'company_id',
            as: 'platforms'
        });
    }

    static async importCompanyData(parsedData) {
        const transaction = await sequelize.transaction();
        try {
            const importedData = [];
            const failures = [];
            let successCount = 0;
            let failureCount = 0;

            for (const [index, data] of parsedData.entries()) {
                try {
                    let industryChain = null;
                    if (data.industryChainName) {
                        [industryChain] = await sequelize.models.IndustryChain.findOrCreate({
                            where: {name: data.industryChainName},
                            defaults: {name: data.industryChainName},
                            transaction
                        });
                    }

                    const companyData = {
                        name: data.name,
                        industry_chain_id: industryChain ? industryChain.id : null,
                        about: data.about,
                        img: data.img,
                        latitude: data.latitude,
                        longitude: data.longitude
                    };

                    const [company, created] = await Company.findOrCreate({
                        where: {name: data.name},
                        defaults: companyData,
                        transaction
                    });

                    if (!created) {
                        await company.update(companyData, {transaction});
                    }

                    importedData.push(company);
                    successCount++;
                } catch (error) {
                    console.error(`导入第 ${index + 1} 行时出错:`, error);
                    failures.push({row: index + 1, error: error.message});
                    failureCount++;
                }
            }

            await transaction.commit();
            return {successCount, failureCount, failures};
        } catch (error) {
            await transaction.rollback();
            throw error;
        }
    }

    static async exportCompanyData() {
        try {
            const companyData = await Company.findAll({
                include: [{model: sequelize.models.IndustryChain, as: 'industryChain', attributes: ['name']}]
            });

            const formattedData = companyData.map(data => ({
                名称: data.name || '',
                产业链: data.industryChain ? data.industryChain.name : '',
                简介: data.about || '',
                图片: data.img || '',
                纬度: data.latitude || '',
                经度: data.longitude || ''
            }));

            const workbook = XLSX.utils.book_new();
            const worksheet = XLSX.utils.json_to_sheet(formattedData);
            XLSX.utils.book_append_sheet(workbook, worksheet, "公司数据");
            const buffer = XLSX.write(workbook, {type: 'buffer', bookType: 'xlsx'});

            return buffer;
        } catch (error) {
            console.error('导出公司数据时发生错误:', error);
            throw error;
        }
    }
}

Company.init(
    {
        id: {
            type: DataTypes.INTEGER,
            primaryKey: true,
            autoIncrement: true
        },
        name: {
            type: DataTypes.STRING(100),
            allowNull: false
        },
        industry_chain_id: {
            type: DataTypes.INTEGER,
            allowNull: true
        },
        about: {
            type: DataTypes.TEXT,
            allowNull: true
        },
        img: {
            type: DataTypes.STRING(255),
            allowNull: true
        },
        latitude: {
            type: DataTypes.DECIMAL(10, 8),
            allowNull: true,
            comment: '纬度'
        },
        longitude: {
            type: DataTypes.DECIMAL(11, 8),
            allowNull: true,
            comment: '经度'
        }
    },
    {
        sequelize,
        tableName: 'company',
        modelName: 'Company'
    }
);

export {Company};

前端请求传文件

接口封装

// 导出
export function exportCompany() {
    return request({
        url: '/v1/company/export',
        method: 'get',
        responseType: 'blob' // 重要：指定响应类型为blob
    })
}

// 导入
export function importCompany(file) {
    const formData = new FormData()
    formData.append('file', file)
    return request({
        url: '/v1/company/import',
        method: 'post',
        data: formData,
        headers: {
            'Content-Type': 'multipart/form-data'
        }
    })
}

html

<div class="topInfoRight">
    <el-button class="mr10" type="success" @click="handleExport">导出数据</el-button>
    <el-upload
        class="upload-demo"
        action="#"
        :on-change="handleFileChange"
        :auto-upload="false"
        :show-file-list="false"
    >
      <el-button type="warning">导入数据</el-button>
    </el-upload>
  </div>

js逻辑代码

import {importCompany,exportCompany} from '@/api/api/company'

const handleExport = async () => {
  try {
    const response = await exportCompany()
    const blob = new Blob([response], {type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'})
    const link = document.createElement('a')
    link.href = window.URL.createObjectURL(blob)
    link.download = '公司管理.xlsx'
    link.click()
    window.URL.revokeObjectURL(link.href)
    ElMessage.success('导出成功')
  } catch (error) {
    console.error('导出失败:', error)
    ElMessage.error('导出失败')
  }
}

const handleFileChange = async (file) => {
  if (file.raw) {
    const response = await importCompany(file.raw)
    console.log(response)
    if (response.code === 1) {
      ElMessage.success(response.msg)
      fetchData() // 刷新数据
    } else {
      ElMessage.error(response.msg || '导入失败')
    }
  }
}

到这里，一个koa导出导入的逻辑就大概完成了。