nodejs导出excel----exceljs （1）

导出excel的库

exceljs可导出excel，提供了可支持文件路径及文件流的方式去导出

使用方法

import * as Excel from 'exceljs'; ; // 如果是js则为 var Excel = require('exceljs');
import * as Stream from 'stream';
import { CellData } from '../models/base/zzj-cell.base';
const tinytinycolor  = require('tinytinycolor'); //颜色值的生成

实例化和引入excel

     this.workBook = new Excel.Workbook();
     if(Object.prototype.toString.call(excel) == '[object String]') {
         await this.workBook.xlsx.readFile(<string>excel);
     } else {
         await this.workBook.xlsx.read(excel);
     }

.read(excel)中excel为流，readFile(excel)为excel的文件路径，并且这个两个方法返回都是Promise，可以接着用then继续后续的操作

选取使用worksheet

this.workBook.getWorksheet(this.worksheetNum); //从1开始计数

worksheet中方法

合并了的单元格信息是单独保存了

获取合并单元格的信息

(<any>this.worksheet)._merges; // _开头的方法或变量都是约定的私有的，所以在index.d.ts中没声明，js中就没影响了

mergeCell的结构为

获取单元格的值

官方中提供了遍历每一行每一列的方法

worksheet.eachRow((row, rowNum) => {
    // 遍历每一行
    row.eachCell((cell,col)=>{
        //遍历这一行中的每一个单元格
    });
});

官方提供的这两个方法有个缺点，不管这个单元是否修改了样式和别的属性，只要是没有值都是不会被遍历的，所以

    public eachRow(callBack: EachCellCallback) {
        let row = (<any>this.worksheet)._rows;
        for(let i = 0; i < row.length; i++) {
            if(row[i] == undefined) continue;
            callBack(row[i], i); 
        }        
    }

单元格的数据结构

export interface Cell extends Style, Address {
    readonly worksheet: Worksheet;
    readonly workbook: Workbook;

    readonly effectiveType: ValueType;
    readonly isMerged: boolean;
    readonly master: Cell;
    readonly isHyperlink: boolean;
    readonly hyperlink: string; // todo
    readonly text: string;
    readonly fullAddress: {
        sheetName: string;
        address: Address;
        row: Row;
        col: Column;
    };
    model: CellModel;
    /**
     * Assign (or get) a name for a cell (will overwrite any other names that cell had)
     */
    name: string;

    /**
     * Assign (or get) an array of names for a cell (cells can have more than one name)
     */
    names: string[];

    /**
     * Cells can define what values are valid or not and provide
     * prompting to the user to help guide them.
     */
    dataValidation: DataValidation;

    /**
     * Value of the cell
     */
    value: CellValue;

    /**
     * convenience getter to access the formula
     */
    readonly formula: string;

    /**
     * convenience getter to access the formula result
     */
    readonly result: number | string | Date;

    /**
     * The type of the cell's value
     */
    readonly type: ValueType;

    /**
     * The type of the cell's formula
     */
    readonly formulaType: FormulaType;

    /**
     * The styles of the cell
     */
    style: Partial<Style>;

    addName(name: string): void;

    /**
     * Remove a name from a cell
     */
    removeName(name: string): void;
    removeAllNames(): void;

    destroy(): void;
    toCsvString(): string;
    release(): void;
    addMergeRef(): void;
    releaseMergeRef(): void;
    merge(master: Cell): void;
    unmerge(): void;
    isMergedTo(master: Cell): boolean;
    toString(): string;
}

获取单元格的值

既是单元格结构体中的value,但是单元格的value也是有中类型，类型有：

export type CellValue =
    | null | number | string | boolean | Date
    | CellErrorValue
    | CellRichTextValue | CellHyperlinkValue
    | CellFormulaValue | CellSharedFormulaValue;

所以获取值的方法

    public getValue(cell: Excel.Cell, CellObj: CellData) {
        try {
            let valueType = Object.prototype.toString.call(cell.value);
            let cellValue = <any>cell.value;
            if(valueType == '[object Object]') {
                if(cell.value.hasOwnProperty('richText')) {
                    let val = '';
                    for(let obj of cellValue.richText) {
                        val += obj.text;
                    }
                    CellObj.value = val;
                }
                if(cell.value.hasOwnProperty('error')) {
                    CellObj.value = cellValue.error
                }
                if(cell.value.hasOwnProperty('hyperlink')) {
                    CellObj.value = cellValue.text + cellValue.hyperlink;
                }
                if(cell.value.hasOwnProperty('sharedFormula')) {

                }
            } else if(valueType == '[object String]' || valueType == '[object Number]') {
               CellObj.value = cell.text; 
            } else if (valueType == '[object Null]') {

            } else if(valueType == '[object Boolean]') {

            } else if(valueType == '[object Date]') {

            }      
        } catch (error) {
            CellObj.value = '';
        }

    }

只是写了几中常用的
获取别，明天写……