<template>
<div ref="sheetContainer" v-bind:id="sheetContainerId" class="grid"></div>
</template>
<script>
import Spreadsheet from "x-data-spreadsheet";
import zhCN from "x-data-spreadsheet/src/locale/zh-cn";
import _ from "lodash";
import * as Excel from "exceljs/dist/exceljs";
import * as tinycolor from "tinycolor2";
import { Guid } from "js-guid";
export default {
name: "xspreadsheet",
props: {
ColumnCount: {
type: Number,
default: () => 50,
},
ColumnWidth: {
type: Number,
default: () => 100,
},
RowCount: {
type: Number,
default: () => 9999,
},
SheetName: {
type: String,
default: () => [],
},
Headers: {
type: Array,
default: () => [],
},
Records: {
type: Array,
default: () => [],
},
/*表頭樣式*/
HeaderStyle: {
type: Object,
default: () => {
return {
//bgcolor: "#f4f5f8",
textwrap: true,
color: "#900b09",
align: "center",
valign: "middle",
border: {
top: ["thin", "#1E1E1E"],
bottom: ["thin", "#1E1E1E"],
right: ["thin", "#1E1E1E"],
left: ["thin", "#1E1E1E"],
},
font: {
bold: true,
},
};
},
},
/*表體樣式*/
RecordStyle: {
type: Object,
default: () => {
return {
//bgcolor: "#f4f5f8",
textwrap: true,
color: "#900b09",
align: "left",
valign: "middle",
border: {
top: ["thin", "#1E1E1E"],
bottom: ["thin", "#1E1E1E"],
right: ["thin", "#1E1E1E"],
left: ["thin", "#1E1E1E"],
},
font: {
bold: false,
},
};
},
},
File: {
type: null,
default: () => null,
},
ExportJsonProperties: {
type: Array,
default: () => [],
},
},
data() {
return {
xs: null,
sheetContainerId: Guid.newGuid().toString(),
DataSource: []
};
},
mounted() {
this.$nextTick(() => {
this.init();
});
},
watch: {
File: {
handler(newV, oldV) {
this.$nextTick(() => {
this.loadExcelFile(newV);
});
},
},
Headers: {
deep: true,
handler(newV) {
let result = [];
if (Array.isArray(newV) && newV.length > 0) {
let headerRow = { cells: [] };
for (let i = 0; i < newV.length; i++) {
headerRow.cells.push({
text: newV[i],
editable: false,
style: 0,
});
}
result.push(headerRow);
}
if (Array.isArray(this.Records) && this.Records.length > 0) {
for (let i = 0; i < this.Records.length; i++) {
let recordRow = { cells: [] };
if (JSON.stringify(this.Records[i]) != "{}") {
for(let k=0; k < this.ExportJsonProperties.length; k++) {
recordRow.cells.push({
text: this.Records[i][this.ExportJsonProperties[k]] + "",
editable: true,
style: 1,
});
}
} else {
for (let i = 0; i < this.ColumnCount; i++) {
recordRow.cells.push({
text: "",
editable: true,
style: 1,
});
}
}
result.push(recordRow);
}
}
this.DataSource = result;
},
},
Records: {
deep: true,
handler(newV) {
let result = [];
if (Array.isArray(this.Headers) && this.Headers.length > 0) {
let headerRow = { cells: [] };
for (let i = 0; i < this.Headers.length; i++) {
headerRow.cells.push({
text: this.Headers[i],
editable: false,
style: 0,
});
}
result.push(headerRow);
}
if (Array.isArray(newV) && newV.length > 0) {
for (let i = 0; i < newV.length; i++) {
let recordRow = { cells: [] };
if (JSON.stringify(newV[i]) != "{}") {
for(let k=0; k < this.ExportJsonProperties.length; k++) {
recordRow.cells.push({
text: newV[i][this.ExportJsonProperties[k]] + "",
editable: true,
style: 1,
});
}
} else {
for (let i = 0; i < this.ColumnCount; i++) {
recordRow.cells.push({
text: "",
editable: true,
style: 1,
});
}
}
result.push(recordRow);
}
}
this.DataSource = result;
}
},
DataSource : {
deep : true,
handler(newW) {
if (this.xs) {
console.log(newW)
this.xs.loadData([
{
name: this.SheetName,
styles: [this.HeaderStyle, this.RecordStyle],
rows: newW,
},
]);
}
}
}
},
methods: {
// 初始化表格
init() {
if (
this.$refs.sheetContainer &&
this.$refs.sheetContainer.offsetHeight &&
this.$refs.sheetContainer.offsetWidth
) {
//設(shè)置中文
Spreadsheet.locale("zh-cn", zhCN);
this.xs = new Spreadsheet(
document.getElementById(this.sheetContainerId),
{
mode: "edit",
showToolbar: true,
showGrid: true,
showContextmenu: true,
showBottomBar: true,
view: {
height: () =>
this.$refs.sheetContainer &&
this.$refs.sheetContainer.offsetHeight &&
_.isNumber(this.$refs.sheetContainer.offsetHeight)
? this.$refs.sheetContainer.offsetHeight
: 0,
width: () =>
this.$refs.sheetContainer &&
this.$refs.sheetContainer.offsetWidth &&
_.isNumber(this.$refs.sheetContainer.offsetWidth)
? this.$refs.sheetContainer.offsetWidth
: 0,
},
formats: [],
fonts: [],
formula: [],
row: {
len: this.RowCount,
height: 25,
},
col: {
len: this.ColumnCount,
width: this.ColumnWidth,
indexWidth: 60,
minWidth: 60,
},
}
);
this.loadData();
}
},
loadData() {
if (this.xs) {
this.xs.loadData([
{
name: this.SheetName,
styles: [this.HeaderStyle, this.RecordStyle],
rows: this.DataSource,
},
]);
}
},
// 導(dǎo)入excel
loadExcelFile(file) {
if (file) {
const wb = new Excel.Workbook();
const reader = new FileReader();
reader.readAsArrayBuffer(file);
reader.onload = () => {
const buffer = reader.result;
// 微軟的 Excel ColorIndex 一個(gè)索引數(shù)字對(duì)應(yīng)一個(gè)顏色
const indexedColors = [
"000000",
"FFFFFF",
"FF0000",
"00FF00",
"0000FF",
"FFFF00",
"FF00FF",
"00FFFF",
"000000",
"FFFFFF",
"FF0000",
"00FF00",
"0000FF",
"FFFF00",
"FF00FF",
"00FFFF",
"800000",
"008000",
"000080",
"808000",
"800080",
"008080",
"C0C0C0",
"808080",
"9999FF",
"993366",
"FFFFCC",
"CCFFFF",
"660066",
"FF8080",
"0066CC",
"CCCCFF",
"000080",
"FF00FF",
"FFFF00",
"00FFFF",
"800080",
"800000",
"008080",
"0000FF",
"00CCFF",
"CCFFFF",
"CCFFCC",
"FFFF99",
"99CCFF",
"FF99CC",
"CC99FF",
"FFCC99",
"3366FF",
"33CCCC",
"99CC00",
"FFCC00",
"FF9900",
"FF6600",
"666699",
"969696",
"003366",
"339966",
"003300",
"333300",
"993300",
"993366",
"333399",
"333333",
];
wb.xlsx.load(buffer).then((workbook) => {
let workbookData = [];
workbook.eachSheet((sheet, sheetIndex) => {
// 構(gòu)造x-data-spreadsheet 的 sheet 數(shù)據(jù)源結(jié)構(gòu)
let sheetData = {
name: sheet.name,
styles: [],
rows: {},
merges: [],
};
// 收集合并單元格信息
let mergeAddressData = [];
for (let mergeRange in sheet._merges) {
sheetData.merges.push(sheet._merges[mergeRange].shortRange);
let mergeAddress = {};
// 合并單元格起始地址
mergeAddress.startAddress = sheet._merges[mergeRange].tl;
// 合并單元格終止地址
mergeAddress.endAddress = sheet._merges[mergeRange].br;
// Y軸方向跨度
mergeAddress.YRange =
sheet._merges[mergeRange].model.bottom -
sheet._merges[mergeRange].model.top;
// X軸方向跨度
mergeAddress.XRange =
sheet._merges[mergeRange].model.right -
sheet._merges[mergeRange].model.left;
mergeAddressData.push(mergeAddress);
}
sheetData.cols = {};
for (let i = 0; i < sheet.columns.length; i++) {
sheetData.cols[i.toString()] = {};
if (sheet.columns[i].width) {
// 不知道為什么從 exceljs 讀取的寬度顯示到 x-data-spreadsheet 特別小, 這里乘以8
sheetData.cols[i.toString()].width =
sheet.columns[i].width * 8;
} else {
// 默認(rèn)列寬
sheetData.cols[i.toString()].width = 100;
}
}
// 遍歷行
sheet.eachRow((row, rowIndex) => {
sheetData.rows[(rowIndex - 1).toString()] = { cells: {} };
//includeEmpty = false 不包含空白單元格
row.eachCell(
{ includeEmpty: true },
function (cell, colNumber) {
let cellText = "";
if (cell.value && cell.value.result) {
// Excel 單元格有公式
cellText = cell.value.result;
} else if (cell.value && cell.value.richText) {
// Excel 單元格是多行文本
for (let text in cell.value.richText) {
// 多行文本做累加
cellText += cell.value.richText[text].text;
}
} else {
// Excel 單元格無公式
cellText = cell.value;
}
//解析單元格,包含樣式
//*********************單元格存在背景色******************************
// 單元格存在背景色
let backGroundColor = null;
if (
cell.style.fill &&
cell.style.fill.fgColor &&
cell.style.fill.fgColor.argb
) {
// 8位字符顏色先轉(zhuǎn)rgb再轉(zhuǎn)16進(jìn)制顏色
backGroundColor = ((val) => {
val = val.trim().toLowerCase(); //去掉前后空格
let color = {};
try {
let argb =
/^#?([a-f\d]{2})([a-f\d]{2})([a-f\d]{2})([a-f\d]{2})$/i.exec(
val
);
color.r = parseInt(argb[2], 16);
color.g = parseInt(argb[3], 16);
color.b = parseInt(argb[4], 16);
color.a = parseInt(argb[1], 16) / 255;
return tinycolor(
`rgba(${color.r}, ${color.g}, ${color.b}, ${color.a})`
).toHexString();
} catch (e) {
console.log(e);
}
})(cell.style.fill.fgColor.argb);
}
if (backGroundColor) {
cell.style.bgcolor = backGroundColor;
}
//*************************************************************************** */
//*********************字體存在背景色******************************
// 字體顏色
let fontColor = null;
if (
cell.style.font &&
cell.style.font.color &&
cell.style.font.color.argb
) {
// 8位字符顏色先轉(zhuǎn)rgb再轉(zhuǎn)16進(jìn)制顏色
fontColor = ((val) => {
val = val.trim().toLowerCase(); //去掉前后空格
let color = {};
try {
let argb =
/^#?([a-f\d]{2})([a-f\d]{2})([a-f\d]{2})([a-f\d]{2})$/i.exec(
val
);
color.r = parseInt(argb[2], 16);
color.g = parseInt(argb[3], 16);
color.b = parseInt(argb[4], 16);
color.a = parseInt(argb[1], 16) / 255;
return tinycolor(
`rgba(${color.r}, ${color.g}, ${color.b}, ${color.a})`
).toHexString();
} catch (e) {
console.log(e);
}
})(cell.style.font.color.argb);
}
if (fontColor) {
//console.log(fontColor)
cell.style.color = fontColor;
}
//************************************************************************ */
// exceljs 對(duì)齊的格式轉(zhuǎn)成 x-date-spreedsheet 能識(shí)別的對(duì)齊格式
if (
cell.style.alignment &&
cell.style.alignment.horizontal
) {
cell.style.align = cell.style.alignment.horizontal;
cell.style.valign = cell.style.alignment.vertical;
}
//處理合并單元格
let mergeAddress = _.find(mergeAddressData, function (o) {
return o.startAddress == cell._address;
});
if (mergeAddress) {
// 遍歷的單元格屬于合并單元格
if (cell.master.address != mergeAddress.startAddress) {
// 不是合并單元格中的第一個(gè)單元格不需要計(jì)入數(shù)據(jù)源
return;
}
// 說明是合并單元格區(qū)域的起始單元格
sheetData.rows[(rowIndex - 1).toString()].cells[
(colNumber - 1).toString()
] = {
text: cellText,
style: 0,
merge: [mergeAddress.YRange, mergeAddress.XRange],
};
//解析單元格,包含樣式
let xsCellStyle = _.cloneDeep(cell.style);
xsCellStyle.border = {};
// 邊框線
if (
cell.style.border &&
JSON.stringify(cell.style.border) != "{}"
) {
let coloneStyle = cell.style.border;
xsCellStyle.border = {};
if (coloneStyle.bottom) {
xsCellStyle.border.bottom = [];
xsCellStyle.border.bottom[0] =
coloneStyle.bottom.style;
if (_.isString(coloneStyle.bottom.color)) {
xsCellStyle.border.bottom[1] =
coloneStyle.bottom.color;
} else {
xsCellStyle.border.bottom[1] = "#000000";
}
}
if (coloneStyle.right) {
xsCellStyle.border.right = [];
xsCellStyle.border.right[0] = coloneStyle.right.style;
if (_.isString(coloneStyle.right.color)) {
xsCellStyle.border.right[1] =
coloneStyle.right.color;
} else {
xsCellStyle.border.right[1] = "#000000";
}
}
if (coloneStyle.left) {
xsCellStyle.border.left = [];
xsCellStyle.border.left[0] = coloneStyle.left.style;
if (_.isString(coloneStyle.left.color)) {
xsCellStyle.border.left[1] = coloneStyle.left.color;
} else {
xsCellStyle.border.left[1] = "#000000";
}
}
if (coloneStyle.top) {
xsCellStyle.border.top = [];
xsCellStyle.border.top[0] = coloneStyle.top.style;
if (_.isString(coloneStyle.top.color)) {
xsCellStyle.border.top[1] = coloneStyle.top.color;
} else {
xsCellStyle.border.top[1] = "#000000";
}
}
}
sheetData.styles.push(xsCellStyle);
//對(duì)應(yīng)的style存放序號(hào)
sheetData.rows[(rowIndex - 1).toString()].cells[
(colNumber - 1).toString()
].style = sheetData.styles.length - 1;
} else {
// 非合并單元格
sheetData.rows[(rowIndex - 1).toString()].cells[
(colNumber - 1).toString()
] = { text: cellText, style: 0 };
//解析單元格,包含樣式
let xsCellStyle = _.cloneDeep(cell.style);
xsCellStyle.border = {};
// 邊框線
if (
cell.style.border &&
JSON.stringify(cell.style.border) != "{}"
) {
let coloneStyle = cell.style.border;
xsCellStyle.border = {};
if (coloneStyle.bottom) {
xsCellStyle.border.bottom = [];
xsCellStyle.border.bottom[0] =
coloneStyle.bottom.style;
if (_.isString(coloneStyle.bottom.color)) {
xsCellStyle.border.bottom[1] =
coloneStyle.bottom.color;
} else {
xsCellStyle.border.bottom[1] = "#000000";
}
}
if (coloneStyle.right) {
xsCellStyle.border.right = [];
xsCellStyle.border.right[0] = coloneStyle.right.style;
if (_.isString(coloneStyle.right.color)) {
xsCellStyle.border.right[1] =
coloneStyle.right.color;
} else {
xsCellStyle.border.right[1] = "#000000";
}
}
if (coloneStyle.left) {
xsCellStyle.border.left = [];
xsCellStyle.border.left[0] = coloneStyle.left.style;
if (_.isString(coloneStyle.left.color)) {
xsCellStyle.border.left[1] = coloneStyle.left.color;
} else {
xsCellStyle.border.left[1] = "#000000";
}
}
if (coloneStyle.top) {
xsCellStyle.border.top = [];
xsCellStyle.border.top[0] = coloneStyle.top.style;
if (_.isString(coloneStyle.top.color)) {
xsCellStyle.border.top[1] = coloneStyle.top.color;
} else {
xsCellStyle.border.top[1] = "#000000";
}
}
}
sheetData.styles.push(xsCellStyle);
//對(duì)應(yīng)的style存放序號(hào)
sheetData.rows[(rowIndex - 1).toString()].cells[
(colNumber - 1).toString()
].style = sheetData.styles.length - 1;
}
}
);
});
workbookData.push(sheetData);
});
this.xs.loadData(workbookData);
});
};
}
},
// 導(dǎo)出excel
exportExcel(fileName) {
const exceljsWorkbook = new Excel.Workbook();
exceljsWorkbook.modified = new Date();
this.xs.getData().forEach(function (xws) {
let rowobj = xws.rows;
// 構(gòu)造exceljs文檔結(jié)構(gòu)
const exceljsSheet = exceljsWorkbook.addWorksheet(xws.name);
// 讀取列寬
let sheetColumns = [];
let colIndex = 0;
for (let col in xws.cols) {
if (xws.cols[col].width) {
sheetColumns.push({
header: colIndex + "",
key: colIndex + "",
width: xws.cols[col].width / 8,
});
}
colIndex++;
}
exceljsSheet.columns = sheetColumns;
for (let ri = 0; ri < rowobj.len; ++ri) {
let row = rowobj[ri];
if (!row) continue;
// 構(gòu)造exceljs的行(如果尚不存在，則將返回一個(gè)新的空對(duì)象)
const exceljsRow = exceljsSheet.getRow(ri + 1);
Object.keys(row.cells).forEach(function (k) {
let idx = +k;
if (isNaN(idx)) return;
const exceljsCell = exceljsRow.getCell(Number(k) + 1);
exceljsCell.value = row.cells[k].text;
if (
xws.styles[row.cells[k].style]
) {
// 垂直對(duì)齊方式
if (xws.styles[row.cells[k].style].valign) {
if (
exceljsCell.alignment == undefined ||
exceljsCell.alignment == null
) {
exceljsCell.alignment = {};
}
exceljsCell.alignment.vertical =
xws.styles[row.cells[k].style].valign;
}
// 水平對(duì)齊方式
if (xws.styles[row.cells[k].style].align) {
if (
exceljsCell.alignment == undefined ||
exceljsCell.alignment == null
) {
exceljsCell.alignment = {};
}
exceljsCell.alignment.horizontal =
xws.styles[row.cells[k].style].align;
}
// exceljsSheet.getCell(exceljsCell._address).alignment = { vertical: xws.styles[row.cells[k].style].valign, horizontal: xws.styles[row.cells[k].style].align }
}
// 邊框
if (
JSON.stringify(xws.styles[row.cells[k].style]) != "{}" &&
JSON.stringify(xws.styles[row.cells[k].style].border) != "{}"
) {
//exceljsCell.border = xws.styles[row.cells[k].style].border;
exceljsCell.border = {};
// bottom
if (
xws.styles[row.cells[k].style].border.bottom &&
Array.isArray(xws.styles[row.cells[k].style].border.bottom) &&
xws.styles[row.cells[k].style].border.bottom.length == 2
) {
exceljsCell.border.bottom = {};
exceljsCell.border.bottom.style =
xws.styles[row.cells[k].style].border.bottom[0];
exceljsCell.border.bottom.color = {};
//exceljsCell.border.bottom.color.indexed = 64
exceljsCell.border.bottom.color =
xws.styles[row.cells[k].style].border.bottom[1];
}
// left
if (
xws.styles[row.cells[k].style].border.left &&
Array.isArray(xws.styles[row.cells[k].style].border.left) &&
xws.styles[row.cells[k].style].border.left.length == 2
) {
exceljsCell.border.left = {};
exceljsCell.border.left.style =
xws.styles[row.cells[k].style].border.left[0];
exceljsCell.border.left.color = {};
//exceljsCell.border.left.color.indexed = 64
exceljsCell.border.left.color =
xws.styles[row.cells[k].style].border.left[1];
}
// right
if (
xws.styles[row.cells[k].style].border.right &&
Array.isArray(xws.styles[row.cells[k].style].border.right) &&
xws.styles[row.cells[k].style].border.right.length == 2
) {
exceljsCell.border.right = {};
exceljsCell.border.right.style =
xws.styles[row.cells[k].style].border.right[0];
exceljsCell.border.right.color = {};
//exceljsCell.border.right.color.indexed = 64
exceljsCell.border.right.color =
xws.styles[row.cells[k].style].border.right[1];
}
// top
if (
xws.styles[row.cells[k].style].border.top &&
Array.isArray(xws.styles[row.cells[k].style].border.top) &&
xws.styles[row.cells[k].style].border.top.length == 2
) {
exceljsCell.border.top = {};
exceljsCell.border.top.style =
xws.styles[row.cells[k].style].border.top[0];
exceljsCell.border.top.color = {};
//exceljsCell.border.right.color.indexed = 64
exceljsCell.border.top.color =
xws.styles[row.cells[k].style].border.top[1];
}
}
// 背景色
if (xws.styles[row.cells[k].style].bgcolor) {
let rgb = tinycolor(
xws.styles[row.cells[k].style].bgcolor
).toRgb();
let rHex = parseInt(rgb.r).toString(16).padStart(2, "0");
let gHex = parseInt(rgb.g).toString(16).padStart(2, "0");
let bHex = parseInt(rgb.b).toString(16).padStart(2, "0");
let aHex = parseInt(rgb.a).toString(16).padStart(2, "0");
let _bgColor = aHex + rHex + gHex + bHex;
// 設(shè)置exceljs背景色
exceljsCell.fill = {
type: "pattern",
pattern: "solid",
fgColor: { argb: _bgColor },
};
}
// 字體
exceljsCell.font = xws.styles[row.cells[k].style].font;
// 字體顏色
if (xws.styles[row.cells[k].style].color) {
let rgb = tinycolor(xws.styles[row.cells[k].style].color).toRgb();
let rHex = parseInt(rgb.r).toString(16).padStart(2, "0");
let gHex = parseInt(rgb.g).toString(16).padStart(2, "0");
let bHex = parseInt(rgb.b).toString(16).padStart(2, "0");
let aHex = parseInt(rgb.a).toString(16).padStart(2, "0");
let _fontColor = aHex + rHex + gHex + bHex;
exceljsCell.font.color = { argb: _fontColor };
}
// 合并單元格
if (row.cells[k].merge) {
// 開始行
let startRow = ri + 1;
// 結(jié)束行,加上Y軸跨度
let endRow = startRow + row.cells[k].merge[0];
// 開始列
let startColumn = Number(k) + 1;
// 結(jié)束列,加上X軸跨度
let endColumn = startColumn + row.cells[k].merge[1];
// 按開始行，開始列，結(jié)束行，結(jié)束列合并
exceljsSheet.mergeCells(startRow, startColumn, endRow, endColumn);
}
});
}
});
// writeBuffer 把寫好的excel 轉(zhuǎn)換成 ArrayBuffer 類型
exceljsWorkbook.xlsx.writeBuffer().then((data) => {
const link = document.createElement("a");
// Blob 實(shí)現(xiàn)下載excel
const blob = new Blob([data], {
type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8",
});
link.href = window.URL.createObjectURL(blob);
link.download = `${fileName}.xlsx`;
link.click();
});
},
// 導(dǎo)出json
exportJson() {
let sheetsData = this.xs.getData();
let rows = Object.entries(sheetsData[0].rows);
let jsonData = [];
if (Array.isArray(this.ExportJsonProperties) && this.ExportJsonProperties.length > 0) {
// 遍歷數(shù)據(jù)，跳過第一行表頭
for (let i = 1; i < rows.length; i++) {
if (rows[i] && rows[i][1] && rows[i][1].cells) {
let row = Object.entries(rows[i][1].cells);
// 構(gòu)造行對(duì)象
let JsonRow = {};
for (let k = 0; k < row.length; k++) {
let cells = row[k];
JsonRow[this.ExportJsonProperties[k]] = cells[1].text;
}
jsonData.push(JsonRow);
}
}
}
return jsonData;
},
},
destroyed() {},
};
</script>
<style>
</style>
400 186 1886
