You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
 
 
 
 

462 lines
14 KiB

import XEUtils from 'xe-utils'
import {
VXETable,
Table,
InterceptorExportParams,
InterceptorImportParams,
ColumnConfig,
TableExportConfig,
ColumnAlign
} from 'vxe-table/lib/vxe-table'
import * as ExcelJS from 'exceljs'
const defaultHeaderBackgroundColor = 'f8f8f9'
const defaultCellFontColor = '606266'
const defaultCellBorderStyle = 'thin'
const defaultCellBorderColor = 'e8eaec'
function getCellLabel (column: ColumnConfig, cellValue: any) {
if (cellValue) {
switch (column.cellType) {
case 'string':
return XEUtils.toString(cellValue)
case 'number':
if (!isNaN(cellValue)) {
return Number(cellValue)
}
break
default:
if (cellValue.length < 12 && !isNaN(cellValue)) {
return Number(cellValue)
}
break
}
}
return cellValue
}
function getFooterData (opts: TableExportConfig, footerData: any[][]) {
const { footerFilterMethod } = opts
return footerFilterMethod ? footerData.filter((items, index) => footerFilterMethod({ items, $rowIndex: index })) : footerData
}
function getFooterCellValue ($table: Table, opts: TableExportConfig, rows: any[], column: ColumnConfig) {
const cellValue = getCellLabel(column, rows[$table.getVMColumnIndex(column)])
return cellValue
}
declare module 'vxe-table/lib/vxe-table' {
interface ColumnInfo {
_row: any;
_colSpan: number;
_rowSpan: number;
childNodes: ColumnConfig[];
}
}
function getValidColumn (column: ColumnConfig): ColumnConfig {
const { childNodes } = column
const isColGroup = childNodes && childNodes.length
if (isColGroup) {
return getValidColumn(childNodes[0])
}
return column
}
function setExcelRowHeight (excelRow: ExcelJS.Row, height: number) {
if (height) {
excelRow.height = XEUtils.floor(height * 0.75, 12)
}
}
function setExcelCellStyle (excelCell: ExcelJS.Cell, align?: ColumnAlign) {
excelCell.protection = {
locked: false
}
excelCell.alignment = {
vertical: 'middle',
horizontal: align || 'left'
}
}
function getDefaultBorderStyle () {
return {
top: {
style: defaultCellBorderStyle,
color: {
argb: defaultCellBorderColor
}
},
left: {
style: defaultCellBorderStyle,
color: {
argb: defaultCellBorderColor
}
},
bottom: {
style: defaultCellBorderStyle,
color: {
argb: defaultCellBorderColor
}
},
right: {
style: defaultCellBorderStyle,
color: {
argb: defaultCellBorderColor
}
}
}
}
function exportXLSX (params: InterceptorExportParams) {
const msgKey = 'xlsx'
const { $table, options, columns, colgroups, datas } = params
const { $vxe, rowHeight, headerAlign: allHeaderAlign, align: allAlign, footerAlign: allFooterAlign } = $table
const { modal, t } = $vxe
const { message, sheetName, isHeader, isFooter, isMerge, isColgroup, original, useStyle, sheetMethod } = options
const showMsg = message !== false
const mergeCells = $table.getMergeCells()
const colList: any[] = []
const footList: any[] = []
const sheetCols: any[] = []
const sheetMerges: { s: { r: number, c: number }, e: { r: number, c: number } }[] = []
let beforeRowCount = 0
const colHead: any = {}
columns.forEach((column) => {
const { id, property, renderWidth } = column
colHead[id] = original ? property : column.getTitle()
sheetCols.push({
key: id,
width: XEUtils.ceil(renderWidth / 8, 1)
})
})
// 处理表头
if (isHeader) {
// 处理分组
if (isColgroup && !original && colgroups) {
colgroups.forEach((cols, rIndex) => {
let groupHead: any = {}
columns.forEach((column) => {
groupHead[column.id] = null
})
cols.forEach((column) => {
const { _colSpan, _rowSpan } = column
const validColumn = getValidColumn(column)
const columnIndex = columns.indexOf(validColumn)
groupHead[validColumn.id] = original ? validColumn.property : column.getTitle()
if (_colSpan > 1 || _rowSpan > 1) {
sheetMerges.push({
s: { r: rIndex, c: columnIndex },
e: { r: rIndex + _rowSpan - 1, c: columnIndex + _colSpan - 1 }
})
}
})
colList.push(groupHead)
})
} else {
colList.push(colHead)
}
beforeRowCount += colList.length
}
// 处理合并
if (isMerge && !original) {
mergeCells.forEach(mergeItem => {
let { row: mergeRowIndex, rowspan: mergeRowspan, col: mergeColIndex, colspan: mergeColspan } = mergeItem
sheetMerges.push({
s: { r: mergeRowIndex + beforeRowCount, c: mergeColIndex },
e: { r: mergeRowIndex + beforeRowCount + mergeRowspan - 1, c: mergeColIndex + mergeColspan - 1 }
})
})
}
const rowList = datas.map(item => {
const rest: any = {}
columns.forEach((column) => {
rest[column.id] = getCellLabel(column, item[column.id])
})
return rest
})
beforeRowCount += rowList.length
// 处理表尾
if (isFooter) {
const { footerData } = $table.getTableData()
const footers = getFooterData(options, footerData)
const mergeFooterItems = $table.getMergeFooterItems()
// 处理合并
if (isMerge && !original) {
mergeFooterItems.forEach(mergeItem => {
let { row: mergeRowIndex, rowspan: mergeRowspan, col: mergeColIndex, colspan: mergeColspan } = mergeItem
sheetMerges.push({
s: { r: mergeRowIndex + beforeRowCount, c: mergeColIndex },
e: { r: mergeRowIndex + beforeRowCount + mergeRowspan - 1, c: mergeColIndex + mergeColspan - 1 }
})
})
}
footers.forEach((rows) => {
const item: any = {}
columns.forEach((column) => {
item[column.id] = getFooterCellValue($table, options, rows, column)
})
footList.push(item)
})
}
const exportMethod = () => {
const workbook = new ExcelJS.Workbook()
const sheet = workbook.addWorksheet(sheetName)
workbook.creator = 'vxe-table'
sheet.columns = sheetCols
if (isHeader) {
sheet.addRows(colList).forEach(excelRow => {
if (useStyle) {
setExcelRowHeight(excelRow, rowHeight)
}
excelRow.eachCell(excelCell => {
const excelCol = sheet.getColumn(excelCell.col)
const column: any = $table.getColumnById(excelCol.key as string)
const { headerAlign, align } = column
setExcelCellStyle(excelCell, headerAlign || align || allHeaderAlign || allAlign)
if (useStyle) {
Object.assign(excelCell, {
font: {
bold: true,
color: {
argb: defaultCellFontColor
}
},
fill: {
type: 'pattern',
pattern:'solid',
fgColor: {
argb: defaultHeaderBackgroundColor
}
},
border: getDefaultBorderStyle()
})
}
})
})
}
sheet.addRows(rowList).forEach(excelRow => {
if (useStyle) {
setExcelRowHeight(excelRow, rowHeight)
}
excelRow.eachCell(excelCell => {
const excelCol = sheet.getColumn(excelCell.col)
const column: any = $table.getColumnById(excelCol.key as string)
const { align } = column
setExcelCellStyle(excelCell, align || allAlign)
if (useStyle) {
Object.assign(excelCell, {
font: {
color: {
argb: defaultCellFontColor
}
},
border: getDefaultBorderStyle()
})
}
})
})
if (isFooter) {
sheet.addRows(footList).forEach(excelRow => {
if (useStyle) {
setExcelRowHeight(excelRow, rowHeight)
}
excelRow.eachCell(excelCell => {
const excelCol = sheet.getColumn(excelCell.col)
const column: any = $table.getColumnById(excelCol.key as string)
const { footerAlign, align } = column
setExcelCellStyle(excelCell, footerAlign || align || allFooterAlign || allAlign)
if (useStyle) {
Object.assign(excelCell, {
font: {
color: {
argb: defaultCellFontColor
}
},
border: getDefaultBorderStyle()
})
}
})
})
}
if (useStyle && sheetMethod) {
sheetMethod({ options, workbook, worksheet: sheet, columns, colgroups, datas, $table })
}
sheetMerges.forEach(({ s, e }) => {
sheet.mergeCells(s.r + 1, s.c + 1, e.r + 1, e.c + 1)
})
workbook.xlsx.writeBuffer().then(buffer => {
var blob = new Blob([buffer], { type: 'application/octet-stream' })
// 导出 xlsx
downloadFile(params, blob, options)
if (showMsg) {
modal.close(msgKey)
modal.message({ message: t('vxe.table.expSuccess'), status: 'success' })
}
})
}
if (showMsg) {
modal.message({ id: msgKey, message: t('vxe.table.expLoading'), status: 'loading', duration: -1 })
setTimeout(exportMethod, 1500)
} else {
exportMethod()
}
}
function downloadFile (params: InterceptorExportParams, blob: Blob, options: TableExportConfig) {
const { $table } = params
const { $vxe } = $table
const { modal, t } = $vxe
const { message, filename, type } = options
const showMsg = message !== false
if (window.Blob) {
if (navigator.msSaveBlob) {
navigator.msSaveBlob(blob, `${filename}.${type}`)
} else {
const linkElem = document.createElement('a')
linkElem.target = '_blank'
linkElem.download = `${filename}.${type}`
linkElem.href = URL.createObjectURL(blob)
document.body.appendChild(linkElem)
linkElem.click()
document.body.removeChild(linkElem)
}
} else {
if (showMsg) {
modal.alert({ message: t('vxe.error.notExp'), status: 'error' })
}
}
}
function checkImportData (tableFields: string[], fields: string[]) {
return fields.some(field => tableFields.indexOf(field) > -1)
}
declare module 'vxe-table/lib/vxe-table' {
interface Table {
_importResolve?: Function | null;
_importReject?: Function | null;
}
}
function importError (params: InterceptorImportParams) {
const { $table, options } = params
const { $vxe, _importReject } = $table
const showMsg = options.message !== false
const { modal, t } = $vxe
if (showMsg) {
modal.message({ message: t('vxe.error.impFields'), status: 'error' })
}
if (_importReject) {
_importReject({ status: false })
}
}
function importXLSX (params: InterceptorImportParams) {
const { $table, columns, options, file } = params
const { $vxe, _importResolve } = $table
const { modal, t } = $vxe
const showMsg = options.message !== false
const fileReader = new FileReader()
fileReader.onerror = () => {
importError(params)
}
fileReader.onload = (evnt) => {
const tableFields: string[] = []
columns.forEach((column) => {
const field = column.property
if (field) {
tableFields.push(field)
}
})
const workbook = new ExcelJS.Workbook()
const readerTarget = evnt.target
if (readerTarget) {
workbook.xlsx.load(readerTarget.result as ArrayBuffer).then(wb => {
const firstSheet = wb.worksheets[0]
if (firstSheet) {
const sheetValues = firstSheet.getSheetValues() as string[][]
const fieldIndex = XEUtils.findIndexOf(sheetValues, (list) => list && list.length > 0)
const fields = sheetValues[fieldIndex] as string[]
const status = checkImportData(tableFields, fields)
if (status) {
const records = sheetValues.slice(fieldIndex).map(list => {
const item : any= {}
list.forEach((cellValue, cIndex) => {
item[fields[cIndex]] = cellValue
})
const record: any = {}
tableFields.forEach(field => {
record[field] = XEUtils.isUndefined(item[field]) ? null : item[field]
})
return record
})
$table.createData(records)
.then((data: any[]) => {
let loadRest: Promise<any>
if (options.mode === 'insert') {
loadRest = $table.insertAt(data, -1)
} else {
loadRest = $table.reloadData(data)
}
return loadRest.then(() => {
if (_importResolve) {
_importResolve({ status: true })
}
})
})
if (showMsg) {
modal.message({ message: t('vxe.table.impSuccess', [records.length]), status: 'success' })
}
} else {
importError(params)
}
} else {
importError(params)
}
})
} else {
importError(params)
}
}
fileReader.readAsArrayBuffer(file)
}
function handleImportEvent (params: InterceptorImportParams) {
if (params.options.type === 'xlsx') {
importXLSX(params)
return false
}
}
function handleExportEvent (params: InterceptorExportParams) {
if (params.options.type === 'xlsx') {
exportXLSX(params)
return false
}
}
/**
* 基于 vxe-table 表格的增强插件,支持导出 xlsx 格式
*/
export const VXETablePluginExportXLSX = {
install (vxetable: typeof VXETable) {
const { interceptor } = vxetable
vxetable.setup({
export: {
types: {
xlsx: 0
}
}
})
interceptor.mixin({
'event.import': handleImportEvent,
'event.export': handleExportEvent
})
}
}
if (typeof window !== 'undefined' && window.VXETable && window.VXETable.use) {
window.VXETable.use(VXETablePluginExportXLSX)
}
export default VXETablePluginExportXLSX