vue-vite-ts/src/utils/exp2File.ts

import * as XLSX from 'xlsx'

/**
   * 解析后的浮点数转时间戳
   * @param num 时间浮点
   */
const number2times = (num: number) => {
    const utc_value = (num - 25569) * 86400
    const date_info = new Date(utc_value * 1000).getTime()
    const newtime = (date_info - 8 * 3600 * 1000)
    return newtime
}

/**
 * 自动下载
 * @param url 保存地址|blob
 * @param saveName 文件名
 */
const openDownloadDialog = (url: any, saveName: string) => {
    if (typeof url === 'object' && url instanceof Blob) {
        url = URL.createObjectURL(url) // 创建blob地址
    }
    const aLink = document.createElement('a')
    aLink.href = url
    aLink.download = saveName || '' // HTML5新增的属性，指定保存文件名，可以不要后缀，注意，file:///模式下不会生效
    let event
    if (window.MouseEvent) event = new MouseEvent('click')
    else {
        event = document.createEvent('MouseEvents')
        event.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0, null)
    }
    aLink.dispatchEvent(event)
}

/**
 * 解析excel表格
 * @param file 文件
 * @returns 
 */
export const exp2json = async (file: File) => {
    return await new Promise((resolve, reject) => {
        try {
            const reader = new FileReader()
            reader.onload = (e: any) => {
                const wb = XLSX.read(e.target.result, {
                    type: 'binary'
                }) // 读取完成的数据
                // 转成json header解析第一行标题
                const data = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]], { header: 1 })
                resolve(data)
            }
            reader.readAsBinaryString(file)
        } catch (error) {
            console.log('解析错误')
            reject(error)
        }
    })
}

/**
 * 导出excel表格
 * @param arr 数据是数组包含的对象
 * @param fileName 名字
 */
export const exp2excel = (arr: Array<any>, fileName:string,cellMerges?:Array<any>) => {
    const sheet = XLSX.utils.json_to_sheet(arr);
    // excel宽高设置
    sheet["!cols"] = arr.map(() => {
        return { wch: 30 }
    })
    if(cellMerges){
        sheet['!merges'] = cellMerges; // <====合并单元格
    }
    // 转blob
    const sheet2blob = (sheet: any, sheetName='sheet1') => {
        const workbook = {
            SheetNames: [sheetName],
            Sheets: {} as any
        };
        workbook.Sheets[sheetName] = sheet;
        // 生成excel的配置项
        const wopts = {
            bookType: 'xlsx', // 要生成的文件类型
            bookSST: false, // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
            type: 'binary'
        } as any
        const wbout = XLSX.write(workbook, wopts);
        // 字符串转ArrayBuffer
        const s2ab = (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;
        }
        const blob = new Blob([s2ab(wbout)], { type: "application/octet-stream" });

        return blob;
    }
    openDownloadDialog(sheet2blob(sheet), fileName + '.xlsx')
}

/**
 * dom导出excel
 * @param domID domID
 * @param fileName 文件名
 */
export const dom2excel = (domID: string, fileName: string) => {
    const dom = document.getElementsByTagName(domID)
    if (!dom) return
    const wb = XLSX.utils.table_to_book(dom[0])
    const baty = XLSX.write(wb, { bookType: 'xlsx', bookSST: false, type: 'binary' })
    // 字符串转ArrayBuffer
    const s2ab = (s: any) => {
        const buf = new ArrayBuffer(s.length)
        const view = new Uint8Array(buf)
        for (let i = 0; i !== s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF
        return buf
    }
    const blob = new Blob([s2ab(baty)], { type: 'application/octet-stream' })
    openDownloadDialog(blob, fileName + '.xlsx')
}
/**
 * 数组按指定key值分组
 * @param {*} array 
 * @param {*} id 
 * @returns 
 */
 export function groupBy(array:Array<any>, id:string) {
    let groups = {} as any;
    array.forEach((o) => {
        let group = JSON.stringify(o[id]);
        if (typeof o[id] === 'string') {
            group = o[id]
        }
        groups[group] = groups[group] || [];
        groups[group].push(o);
    });
    // return Object.values(groups);
    return groups;
}