JS操作excel文件

操作excel文件

1. 读取excel文件

a. 安装依赖

npm i xlsx -D

b. 代码演示(react 版)

import { Button, message, Tag, Tooltip, Upload } from 'antd';
import { UploadChangeParam, UploadFile, UploadProps } from 'antd/lib/upload/interface';
import React, { useCallback, useState } from 'react';
import XLSX from 'xlsx';
import { UploadOutlined } from '@ant-design/icons';

// 格式化内存
export function formatMemory(num: string | number) {
    if (/k|g|m|t/i.test(num.toString())) {
        return num.toString();
    }
    if (num == null) {
        return '';
    }
    const level = [ 'B', 'KB', 'MB', 'GB', 'TB' ];
    let cur = parseFloat(num.toString());
    let levelI = 0;
    while (cur >= 1024) {
        levelI++;
        cur /= 1024;
    }
    return `${Math.round(cur * 100) / 100} ${level[levelI]}`;
}


export function parseConcatRegionFile(file?: File | null, cb?: (data: any[]) => void) {
    if (file == null) {
        return null;
    }
    const reader = new FileReader();
    /** 这里判断下是否为csv文档 */

    let isCsv = file.name.endsWith('.csv');

    reader.onload = e => {
        /* Parse data */
        let bstr = e.target?.result;
        if (bstr == null) {
            cb && cb([]);
            return;
        }
        if (isCsv) {
            const uni8Array = new Uint8Array(bstr as ArrayBuffer);
            /** 239,187,191 === \ufeff */
            const coding = uni8Array[0] === 239 && uni8Array[1] === 187 && uni8Array[2] === 191 ? 'utf8' : 'gbk';
            const t = new TextDecoder(coding);
            bstr = t.decode(uni8Array);
        }
        const wb = XLSX.read(bstr, { type: isCsv ? 'string' : 'array' });

        /* Get first worksheet */
        const wsname = wb.SheetNames[0];
        const ws = wb.Sheets[wsname];
        /* Convert array of arrays */
        const data = XLSX.utils.sheet_to_json<string[]>(ws, { header: 1 });

        if (data == null || data.length <= 1) {
            cb && cb([]);
            return;
        }
        cb && cb(data);
    };

    reader.readAsArrayBuffer(file as any);

    return () => {
        reader.abort();
    };
}

interface UploadImgProps extends Omit<UploadProps, 'onChange'> {
    uploadNode?: React.ReactNode;
    maxSize?: number; // 最大上传size,单位为b, 默认2M
    valueMaxlength?: number; // 文件名显示最大长度
    onChange?: (data: any[]) => void; // 输出识别到的数组，原样输出
}

function ReadFile(props: UploadImgProps) {
    const { maxSize = 2 * 1024 * 1024, accept, valueMaxlength = 20, uploadNode, ...otherReset } = props;

    const [ value, setValue ] = useState('');

    const beforeUpload = useCallback(
        file => {
            console.log(accept, accept?.toLowerCase().split(','), file.type);
            if (
                accept != null &&
                !accept?.toLowerCase().split(',').includes(file.type) &&
                !accept
                    .toLowerCase()
                    .split(',')
                    .some(name => file?.name.endsWith(name))
            ) {
                message.error(`文件扩展名 ${file.type} 错误，需要 ${accept}`);
                return false;
            }
            if (maxSize && file.size > maxSize) {
                message.error('文件大小最大不能超过' + formatMemory(maxSize));
                return false;
            }
            return true;
        },
        [ accept, maxSize ]
    );

    const handleChange = useCallback(
        (info: UploadChangeParam<UploadFile<any>>) => {
            const { file } = info;
            setValue(file?.name);
            parseConcatRegionFile(file?.originFileObj as File, data => {
                props?.onChange?.(data || []);
            });
        },
        [ props ]
    );

    const handleClose = useCallback(() => {
        setValue('');
        props?.onChange?.([]);
    }, [ props ]);

    return (
        <>
            {value ? (
                <Tooltip title={value}>
                    <Tag closable onClose={handleClose}>
                        {value?.length > valueMaxlength ? value?.substring(0, valueMaxlength) + '...' : value}
                    </Tag>
                </Tooltip>
            ) : (
                <Upload
                    {...otherReset}
                    accept={accept || 'application/vnd.ms-excel'}
                    beforeUpload={beforeUpload}
                    onChange={handleChange}
                >
                    {uploadNode || <Button icon={<UploadOutlined />}>数据导入</Button>}
                </Upload>
            )}
        </>
    );
}
export default ReadFile;

2. excel 导出

a. 后端response返回文件流导出

export function createBlob(data: any, filename: string, blobOptions: BlobPropertyBag) {
    let blob = new Blob([ data ], blobOptions || { type: 'application/vnd.ms-excel' });
    let dom = document.createElement('a');
    dom.download = filename;
    dom.style.display = 'none';
    dom.href = URL.createObjectURL(blob);
    document.body.appendChild(dom);
    dom.click();
    setTimeout(() => {
        document.body.removeChild(dom);
    }, 1000);
}

// 调用demo演示
axios.get(`/api/demo`, { params, responseType: 'blob' }).then(data => {
     createBlob(data, `xxxxx.csv`);
})

b. JSON 数据转文件导出

i. 使用浏览器自带的href属性导出文件

export function jsonToExcel(jsonData: any[], filename = 'export.xls') {
    if (!Array.isArray(jsonData) || !jsonData?.length) {
        return;
    }
    let str = '';
    // 列标题
    Object.keys(jsonData[0]).forEach(k => {
        str += k + '\t,';
    });
    str += '\n';
    // 增加\t为了不让表格显示科学计数法或者其他格式
    for (let i = 0; i < jsonData.length; i++) {
        // eslint-disable-next-line no-loop-func
        Object.keys(jsonData[i]).forEach(key => {
            str += `${jsonData[i][key] + '\t'},`;
        });
        str += '\n';
    }
    // encodeURIComponent解决中文乱码
    const uri = `data:text/${filename.split('.').pop()};charset=utf-8,\ufeff${encodeURIComponent(str)}`;
    // 通过创建a标签实现
    let dom = document.createElement('a');
    dom.download = filename;
    dom.style.display = 'none';
    dom.href = uri;
    document.body.appendChild(dom);
    dom.click();
    setTimeout(() => {
        document.body.removeChild(dom);
    }, 1000);
}

// demo调用演示
let sheet1data = [ // 注意数组中每列key的排序，因为导出列表时按照key的排序导出的
    { 部门: '行政部', 姓名: 'zhangsan', age: 18 },
    { 部门: 'IT', 姓名: 'lisi', age: 19 }
];
jsonToExcel(
    sheet1data,
    'demo.xls'
);

ii. 使用xlsx write导出

/** json转excel表格 */
export function jsonToExcel(data: any[], filename: string) {
    let wopts: any = {
        bookType: filename.split('.').pop(),
        bookSST: false,
        type: 'binary'
    };
    /* create a new blank workbook */
    let workbook = XLSX.utils.book_new();
    let sheet1 = XLSX.utils.json_to_sheet(data);
    XLSX.utils.book_append_sheet(workbook, sheet1, 'sheet1');
    let wbout = XLSX.write(workbook, wopts);
    createBlob([ string2u8buff(wbout) ], filename);
}

function string2u8buff(s: string) {
    let buf = new ArrayBuffer(s.length);
    let view = new Uint8Array(buf);
    for (let i = 0; i !== s.length; ++i) {
        view[i] = s.charCodeAt(i) & 0xff;
    }
    return buf;
}

// demo调用演示
let sheet1data = [ // 注意数组中每列key的排序，因为导出列表时按照key的排序导出的
    { 部门: '行政部', 姓名: 'zhangsan', age: 18 },
    { 部门: 'IT', 姓名: 'lisi', age: 19 }
];
jsonToExcel(
    sheet1data,
    'demo.xls'
);