React+excelJs导出表格

官方API: https://github.com/exceljs/exceljs/blob/master/README_zh.md#rows
首先安装： npm install exceljs

1. 整理数据格式：

// 根据dept_outpient_id分组
let groupByDeptOutpaientId = fromJS(resData).groupBy(item => item.getIn(['service_item', 'dept_outpatient', 'id']));
 let arr = groupByDeptOutpaientId.toJS()
const serviceItemData = Object.keys(arr).map(eachServiceId => arr[eachServiceId][0].service_item);
const serviceItemList = [];
//构造新对象
  serviceItemData.forEach((item, index) => {
         let serviceItemObj = {};
            const { sort_number, dept_outpatient } = item;
            serviceItemObj.sort_number = sort_number;
            serviceItemObj.serviceName = dept_outpatient.name;
            serviceItemObj.dept_outpatient_id = dept_outpatient.id;
            serviceItemList.push(serviceItemObj)
        })
        const groupedByDate = _.groupBy(resData, 'date');
const calendar = Object.entries(groupedByDate).map(([date, details]) => {
            return {
                date: parseInt(date, 10),
                placeholder: false,
                details,
            }
        });

构造每一行数据内容：

const dayData = Object.keys(groupedByDate).map(eachDate => eachDate).sort();
        const rowsDates = DateService.shardingCalendar(fromJS(calendar));
        const serviceData = [];
        const serviceItemIdxMap = {};
        const weekList = [];
        const dateRows = rowsDates.map((eachWeek, weekIndex) => {
            const weekData = {
                serviceItem: '',
                Remark: '',
            };

            serviceItemList.forEach((eachService, index) => {
                const curDeptOPId = eachService.dept_outpatient_id.toString();
                const weekFields = {
                    serviceItem: eachService.serviceName,
                    dept_outpatient_id: curDeptOPId,
                    sort_number: eachService.sort_number,
                    Remark: "",
                };
                dayData.forEach(eachDay => {
                    const preFix = moment(Number(eachDay)).format('MM/DD');
                    weekFields[`${preFix}.AM.DUTY`] = '';
                    weekFields[`${preFix}.AM.TITLE`] = '';
                    weekFields[`${preFix}.PM.DUTY`] = '';
                    weekFields[`${preFix}.PM.TITLE`] = '';
                });
                serviceItemIdxMap[curDeptOPId] = index;
                serviceData.push(weekFields);
            });
            eachWeek.forEach((eachDay, idx) => {
                const weekDay = moment(eachDay.get('date')).format('MM/DD');
                if (eachDay.get('placeholder')) {
                    weekData[weekDay] = '';
                } else {
                    weekData[weekDay] = moment(eachDay.get('date')).format('MM 月 DD 日');
                    if (eachDay.get('details')) {
                        eachDay.get('details').forEach((eachDetail,index) => {                           
                            const dept_outpatient_id = eachDetail.getIn(['service_item', 'dept_outpatient_id']);
                            const curIndex = serviceItemIdxMap[dept_outpatient_id];
                            if (curIndex >= 0) {
                                const tarServiceItem = serviceData[curIndex];
                                // 只要给这个tarServiceItem里的相应属性填上值
                                const timeQuantum = eachDetail.get('time_quantum') ? eachDetail.getIn(['time_quantum', 'name']) : eachDetail.get('time_quantum_name');
                                const personName = eachDetail.get('person') ? eachDetail.getIn(['person', 'name']) : eachDetail.getIn(['person', 'first_name']);
                                const posiName = eachDetail.getIn(['person', 'sysUsers', 0, 'positions', 0]) ? eachDetail.getIn(['person', 'sysUsers', 0, 'positions', 0, 'name']) : eachDetail.getIn(['service_item', 'name']);
                              

判断一个单元格是否有多个值

//判断一个单元格是否有多个职责
const hasCommaAmTitle= Boolean(serviceData[curIndex][`${weekDay}.AM.TITLE`]);
const hasCommaAmDuty= Boolean(serviceData[curIndex][`${weekDay}.AM.DUTY`]);
const hasCommaPmTitle= Boolean(serviceData[curIndex][`${weekDay}.PM.TITLE`]);
const hasCommaPmDuty= Boolean(serviceData[curIndex][`${weekDay}.PM.DUTY`]);
const amTitle = `${hasCommaAmTitle ? ', ' : ''}${personName}`;
const amDuty = `${hasCommaAmDuty ? ', ' : ''}${currentPosition}`;
const pmTitle = `${hasCommaPmTitle ? ', ' : ''}${personName}`;
const pmDuty = `${hasCommaPmDuty ? ', ' : ''}${currentPosition}`;
// 时间判断上午下午
if (timeQuantum === '上午') {                                    
      tarServiceItem[`${weekDay}.AM.TITLE`] = tarServiceItem[`${weekDay}.AM.TITLE`].concat(amTitle);
       tarServiceItem[`${weekDay}.AM.DUTY`] = tarServiceItem[`${weekDay}.AM.DUTY`].concat(amDuty);
       } else {
            tarServiceItem[`${weekDay}.PM.TITLE`] =  tarServiceItem[`${weekDay}.PM.TITLE`].concat(pmTitle);
              tarServiceItem[`${weekDay}.PM.DUTY`] = tarServiceItem[`${weekDay}.PM.DUTY`].concat(pmDuty);
}

创建表格并且设置：

const workbook = new Excel.Workbook();
        workbook.views = [
            {
                x: 0, y: 0, width: 10000, height: 20000,
                firstSheet: 0, activeTab: 0, visibility: 'visible'
            }
        ];

创建工作表：

// 创建工作表
        const workSheet = workbook.addWorksheet('排班概览');
             workSheet. pageSetup = {
                paperSize: 9,
                orientation: 'landscape',
            },           
            // 之后调整pageSetup设置
            workSheet.pageSetup.margins = {
                left: 0.1, right: 0.1,
                top: 0.25, bottom: 0.2,
                header: 0.2, footer: 0.2
            };
            //打印页面设置固定的行
            workSheet.pageSetup.printTitlesRow = '1:2';
        const columns = [
            { header: '星期', key: 'serviceItem', width: 15 },

        ];
        const secondRow = {
            serviceItem: '科室',

        };

        const weekDayMap = {
            'Monday': '星期一',
            'Tuesday': '星期二',
            'Wednesday': '星期三',
            'Thursday': '星期四',
            'Friday': '星期五',
            'Saturday': '星期六',
            'Sunday': '星期日',
        };
        const tqMap = {
            'AM': '上午',
            'PM': '下午',
        };

 workSheet.columns = columns;
        // Add Main Content
        workSheet.addRow(secondRow);
        dateRows.first().serviceData.forEach(eachDetail => {
            workSheet.addRow(eachDetail);
        });

设置表格样式：

 const Medium = { style: 'medium' };
        const Thin = { style: 'thin' };

        const setCommonStyleOnRows = (workSheet, startRow, endRow) => {
            for (let i = startRow; i <= endRow; i++) {
                // 循环 row 中的　cell，给每个 cell 添加边框, 并居中显示
                const curRow = workSheet.findRow(i);
                curRow.height = 22;
                    if(i>2 ){
                        curRow.values.forEach(item => {
                            let curRowSplit = [];
                           if(item.includes(',')){
                                curRowSplit.push(item.split(',').length); 
                                curRow.height = 22* Math.max(...curRowSplit);
                           }
                        })
                    }
                if (curRow) {
                    curRow.alignment = { vertical: 'middle', horizontal: 'center', wrapText: true };
                    curRow.font = {
                        name: 'Microsoft YaHei UI',
                        size: 10,
                    };
                    curRow.eachCell((cell, index) => {
                        const Border = {};
                        if (i === startRow) {
                            Border['top'] = Medium;
                            Border['bottom'] = Thin;

                        } else if (i === endRow) {
                            Border['top'] = Thin;
                            Border['bottom'] = Medium;
                        } else {
                            Border['top'] = Thin;
                            Border['bottom'] = Thin;
                        }
                        if (index === 1) {
                            Border['left'] = Medium;
                            Border['right'] = Thin;
                            cell.font = {
                                name: 'Microsoft YaHei UI Light',
                                size: 12,
                                bold: true,
                            };
                        } else if (index === Object.keys(serviceData[0]).length - 3) {
                            Border['left'] = Thin;
                            Border['right'] = Medium;
                        } else {
                            Border['left'] = Thin;
                            Border['right'] = Thin;
                        }

                        if (i === startRow) {
                            cell.fill = {
                                type: "pattern",
                                pattern: "solid",
                                fgColor: { argb: "FFF2F2F2" },
                                bgColor: { argb: 'FFF2F2F2' },
                            };
                        }
                        cell.border = Border;
                    });
                    if (i === 1 || i === 2) {
                        curRow.height = 30;
                        curRow.font = {
                            name: 'Microsoft YaHei UI Light',
                            size: 12,
                            bold: true,
                        };
                    } else if (i === 1) {
                        Border['top'] = Medium;
                        Border['bottom'] = Thin;
                    }
                }

            }
        }

        //合并单元格
        for (var i = 0; i <= weekList.length - 1; i++) {
            const left = 2 + (4 * i);
            const right = left + 3;
            workSheet.mergeCells(1, left, 1, right);
        }
        const totalRow = serviceData.length;
        setCommonStyleOnRows(workSheet, 1, (totalRow + 2));