import XLSX from 'xlsx';

/**
 * 格式化xlsx文件
 * @param file 文件Bolb
 * @returns Promise resolve表格数组
 */
let FX: (file: Blob) => Promise<unknown> = (file: Blob) => {
    return new Promise((r, j) => {
        let reader = new FileReader()
        //启动函数
        reader.readAsBinaryString(file)
        // onload在文件被读取时自动触发
        reader.onload = (e: any) => {
            // workbook存放excel的所有基本信息
            let workbook = XLSX.read(e.target.result, { type: 'binary' })
    
            r(workbook.SheetNames.map((c: string) => {
                return {
                    SheetName: c,
                    SheetData: XLSX.utils.sheet_to_json(workbook.Sheets[c], { header: 1 })
                }
            }))
        }
    })
}

// 表中大于26列时获取列头
let getCharCol: (n: number) => string = (n: number) => {
    let s = "",
        m = 0
    while (n > 0) {
        m = n % 26 + 1
        s = String.fromCharCode(m + 64) + s
        n = (n - m) / 26
    }
    return s
}

// string to arrayBuffer
let string2ArrayBuffer: (s: any) => ArrayBuffer = (s: any) => {
    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;
}

// 下载文件
let saveAs = (obj: Blob, fileName: string = new Date().getTime().toString()) => {
    let tmp = document.createElement("a");
    tmp.download = `${fileName}.xlsx`
    tmp.href = URL.createObjectURL(obj)
    tmp.click()
    setTimeout(() => URL.revokeObjectURL(tmp.href), 100)
}

/**
 * 单块合并单元格范围，多个范围组成merges
 */
interface Range {
    // s为开始
    s: {
        // 开始列
        c: number;
        // 开始取值范围
        r: number;
    };
    // e为结束
    e: {
        // 开始列
        c: number;
        // 开始取值范围
        r: number;
    };
}

// xlsx中表结构
interface sheet {
    // 表名（页名）
    SheetName: string
    // 表数据，为二维数组，子数组每一个为一列由上到下的数据
    // [
    //     ['列1', '1'],
    //     ['列2', '2']
    // ]
    SheetData: any
    // 合并单元格数据
    merges?: Range[]
}



/**
 * 纵向合并单元格计步器，生成merges
 * @class Pedometer
 * @returns Range[]
 */
class Pedometer {
    index: number
    step: number
    colArr: number[]

    constructor(index = 1) {
        this.index = index
        this.step = 0
        this.colArr = []
    }

    // number[]为合并的列下标数组
    range: () => Range[] = () =>
        this.colArr.map((c: number) => {
            return {
                s: {
                    c: c,
                    r: this.index,
                },
                e: {
                    c: c,
                    r: this.index + this.step - 1,
                },
            }
        })

    // step 总步数，需合并的长度  colArr 合并列的下标数组
    calc: (step: number, colArr: number[]) => Range[] = (
        step: number,
        colArr: number[]
    ) => {
        this.step = step
        this.colArr = colArr

        // 如果step小于等于1，则不合并，直接返回[]，在外层可以被flat()扁平化
        if (this.step <= 1) {
            this.index++
            return []
        }

        // 计算此步所有需要合并的列的
        let temp: Range[] = this.range()

        this.index += this.step

        return temp
    }
}


/**
 * 导出xlsx文件
 * @param fileName 文件名
 * @param tables 表格数组 sheet[]
 */
let EX = (fileName: string, tables: sheet[]) => {
    let t: any = tables.map((c: any, i: number) => {
        let content = {}

        c.SheetData.map((_c: any, _i: number) => _c.map((j: any, k: number) => {
            let key = _i > 25?getCharCol(_i) + (k + 1):String.fromCharCode(65 + _i) + (k + 1)  
            content[key] = { 
                v: j
            }
        }))

        //设置区域,比如表格从A1到D10,SheetNames:标题，
        let coordinate: string[] = Object.keys(content)

        // 表起始位置 一般为A1
        let start: string = coordinate[0]

        let key: number = 0

        c.SheetData.map((_c: any, _i: number) => _c.map((j: any, k: number) => k + 1 >= key?key = k + 1:key))

        let header: string = coordinate[coordinate.length - 1].replace(/[0-9]/ig, '')

        // 表结束位置，一般为最后列的字母 + 最长长度
        let end: string = header + key

        return {
            SheetName: c.SheetName || `Sheet${i}`,
            SheetData: {
                ...content,
                "!ref": start + ":" + end,
                "!merges": c.merges || [],
            }
        }
    })

    let workBook = {
        SheetNames: t.map((c: any) => c.SheetName),
        Sheets: t.reduce((r: any, c: any) => {
            r[c.SheetName] = c.SheetData
            return r
        }, {})
    }

    //这里的数据是用来定义导出的格式类型
    let excelData = XLSX.write(workBook, { 
        bookType: "xlsx", 
        bookSST: false, 
        type: "binary" 
    })

    let blob = new Blob([string2ArrayBuffer(excelData)], { type: "" })

    saveAs(blob, fileName)
}

export { FX, Range, Pedometer, EX }

版权属于：谁把年华错落成诗 所有，转载请注明出处！

本文链接：https://blog.pomears.com/archives/62.html

如果博客部分链接出现404，请留言或者联系博主修复。