angular - Angular 5 以自定义格式导出 Excel
问题描述
我正在使用 Angular 提供的 Excel 服务。我想得到如图所示的输出。此格式需要 Excel 导入。
我的组件调用服务获取 JSON 数据,然后调用 Excel 服务导出输出。如何自定义函数以获取此输出格式?
JSON格式:
[
{
"applicationName": "Application1",
"migration": "Rehost",
"hostname": "DemoVM5",
"ipAddress": "10.0.1.7",
"operatingSystem": "Microsoft(R) Windows(R) Server 2003, Standard Edition",
"migrationStatus": "Failed",
"error": null,
"runDetails": {
"rehostCompletedCount": 0,
"rehostFailedCount": 2,
"refactorCompletedCount": 0,
"refactorFailedCount": 0,
"runId": 41,
"rehostCount": 2,
"refactorCount": 0,
"status": null,
"dateTime": null
}
},
{
"applicationName": "Application1",
"migration": "Rehost",
"hostname": "DemoVM2",
"ipAddress": "10.0.1.6",
"operatingSystem": "Microsoft(R) Windows(R) Server 2003, Standard Edition",
"migrationStatus": "Failed",
"error": null,
"runDetails": {
"rehostCompletedCount": 0,
"rehostFailedCount": 2,
"refactorCompletedCount": 0,
"refactorFailedCount": 0,
"runId": 41,
"rehostCount": 2,
"refactorCount": 0,
"status": null,
"dateTime": null
}
}
]
Excel 服务代码
import {
Injectable
} from '@angular/core';
import * as FileSaver from 'file-saver';
import * as XLSX from 'xlsx';
import * as _ from 'underscore';
const EXCEL_TYPE = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8';
const EXCEL_EXTENSION = '.xlsx';
@Injectable()
export class ExcelService {
public data: any;
public sheetName: string = "Sheet1";
public workbook: XLSX.WorkBook = {
Sheets: {},
SheetNames: [],
Props: {}
}
public ws: any;
public wbout: any;
constructor() {}
public exportAsExcelFile(json: any[], excelFileName: string): void {
this.data = json;
this.downloadExcel(excelFileName);
}
public transformData(data: any) {
let dataNew: any = [];
let keys_arr = [];
_.each(data, function(json) {
let key: any = json;
let arr = _.filter(key, function(val, i) {
let value: any = val;
let index: any = i;
keys_arr.push(index);
if (value == 0) {
return '0';
} else {
return value;
}
});
dataNew.push(arr);
});
dataNew.unshift(_.uniq(keys_arr));
return dataNew;
}
public sheet_from_array_of_arrays(data) {
let ws = {};
let endCell = {
c: 10000000,
r: 10000000
};
let startCell = {
c: 0,
r: 0
};
let range = {
s: endCell,
e: startCell
};
let wscols = [];
for (let R = 0; R != data.length; ++R) {
for (let C = 0; C != data[R].length; ++C) {
wscols.push({
wch: 20
});
if (range.s.r > R) range.s.r = R;
if (range.s.c > C) range.s.c = C;
if (range.e.r < R) range.e.r = R;
if (range.e.c < C) range.e.c = C;
let cell = {
v: data[R][C],
t: 's',
s: {}
};
if (R === 0) {
cell.s = {
"font": {
"bold": true,
"sz": 13,
"alignment": {
"horizontal": "center",
"vertical": "center"
}
}
};
}
if (cell.v == null) continue;
let cell_ref = XLSX.utils.encode_cell({
c: C,
r: R
});
if (typeof cell.v === 'number')
cell.t = 'n';
else if (typeof cell.v === 'boolean')
cell.t = 'b';
else
cell.t = 's';
ws[cell_ref] = cell;
}
}
ws['!cols'] = wscols;
if (range.s.c < 10000000)
ws['!ref'] = XLSX.utils.encode_range(endCell, startCell);
return ws;
}
public generateExcelFile(): any {
this.ws = this.sheet_from_array_of_arrays(this.transformData(this.data));
this.workbook.SheetNames = [];
this.workbook.SheetNames.push(this.sheetName);
this.workbook.Sheets[this.sheetName] = this.ws;
this.wbout = XLSX.write(this.workbook, {
bookType: 'xlsx',
type: 'binary'
});
return this.wbout;
}
public createView(s: any): ArrayBuffer {
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;
}
public downloadExcel(fileName: string): void {
this.sheetName = fileName + '_export_' + new Date().getTime();
FileSaver.saveAs(new Blob([this.createView(this.generateExcelFile())], {
type: "application/octet-stream"
}), fileName + '_export_' + new Date().getTime() + EXCEL_EXTENSION);
}
}
解决方案
对于 XLSX 库,如果您想在 excel 中提供任何样式,您需要将它与 xlsx-style 包一起使用。
XLSX-Style 在这里,您将找到有关如何应用单元格样式的文档。
ExcelJS也是另一个流行且易于使用的客户端 Excel 生成库。这个库还提供了比 xlsx 和 xlsx 样式更多的样式功能。您可以使用 ExcelJS 文章参考导出到 Angular 中的 excel 以供参考。
推荐阅读
- python - 类中的函数返回未定义的全局名称
- html - 试图理解代码中“”的含义
- c++ - 带有 /TC 选项的 MSVC 如何编译用 C++ 编写的 corecrt 库(如 stdio)?
- scala - 当我运行我的测试套件时,它们因 PSQLException 失败:致命:对不起,已经有太多客户了
- if-statement - 使用带有下拉列表的 Excel IF 语句
- angular - 角度组件未打开
- r - Ifelse NULL:获取数字而不是字符串值
- ios - 无法计算约束的确切值
- c++ - 如何在 C++ 中使用 Xpath 和 XQuery 解析 XML 文件?
- vba - 如何编写 Word VBA 语法来捕捉