先建了一个查询,按照需求将数据库数据导出数据到List,前端再从List里导出成Excel表,后端实现的查询功能,前端转的Excel
后端
Controller
// 导出为Excel
[HttpPost]
[Route("Export")]
public ActionResult Export([FromBody] object data)
{
JObject form = (JObject)JsonConvert.DeserializeObject(data.ToString());
List<ExportInfo> result = _GlitchViewService.Export(form);
JsonResult jr = new JsonResult(result);
return jr;
}
GlitchViewService
List<ExportInfo> Export(JObject form);
Service
public List<ExportInfo> Export(JObject form)
{
string[] checkedCities = form["checkedCities"].ToString().Split(',');
List<string> organization = new List<string>(FormatOrganizationName(checkedCities));
string groupName = form["groupName"].ToString() == "" ? null : form["groupName"].ToString();
string substationName = form["substationName"].ToString() == "" ? null : form["substationName"].ToString();
string lineName = form["lineName"].ToString() == "" ? null : form["lineName"].ToString();
string glitchType = form["glitchType"].ToString() == "" ? null : form["glitchType"].ToString();
string tripType = form["tripType"].ToString() == "" ? null : form["tripType"].ToString();
string coincidence = form["coincidence"].ToString() == "" ? null : form["coincidence"].ToString();
string faSelfHealing = form["faSelfHealing"].ToString() == "" ? null : form["faSelfHealing"].ToString();
DateTime? start;
if (string.IsNullOrEmpty(form["start"].ToString()))
{
start = null;
}
else
{
start = DateTime.Parse(form["start"].ToString());
}
DateTime? end;
if (string.IsNullOrEmpty(form["end"].ToString()))
{
end = null;
}
else
{
end = DateTime.Parse(form["end"].ToString());
}
string permition = form["perorganization"].ToString() == "null" ? null : form["perorganization"].ToString();
string perorganization = null;
if (null != permition)
{
perorganization = _officeRepository.QueryOfficeName(permition);
}
List<ExportInfo> list = _glitchViewRepository.Export(organization, groupName, substationName, lineName,
glitchType, tripType, coincidence, faSelfHealing, start, end, perorganization);
return list;
}
IGlitchViewRepository
List<ExportInfo> Export(List<string> organization, string groupName, string substationName, string lineName,
string glitchType, string tripType, string coincidence, string faSelfHealing, DateTime? start, DateTime? end, string perorganization);
XML
<Statement Id="Export">
SELECT * from dbo.[VglitchView] vg
<Where>
vg.OrganizationName in(
<For Prepend="" Property="organization" Separator="," >
@organization
</For>
)
<IsNotEmpty Prepend="And" Property="groupName">
vg.GroupName LIKE '%'+@groupName+'%'
</IsNotEmpty>
<IsNotEmpty Prepend="And" Property="substationName">
vg.SubstationName LIKE '%'+@substationName+'%'
</IsNotEmpty>
<IsNotEmpty Prepend="And" Property="lineName">
vg.LineName LIKE '%'+@lineName+'%'
</IsNotEmpty>
<IsNotEmpty Prepend="And" Property="start">
vg.PowerDownTime <![CDATA[ >= ]]> @start
</IsNotEmpty>
<IsNotEmpty Prepend="And" Property="end">
vg.PowerDownTime <![CDATA[ <= ]]> @end
</IsNotEmpty>
<IsNotEmpty Prepend="And" Property="glitchType">
vg.GlitchType = @glitchType
</IsNotEmpty>
<IsNotEmpty Prepend="And" Property="tripType">
vg.TripType = @tripType
</IsNotEmpty>
<IsNotEmpty Prepend="And" Property="coincidence">
vg.Coincidence = @coincidence
</IsNotEmpty>
<IsNotEmpty Prepend="And" Property="faSelfHealing">
vg.FASelfHealing LIKE '%' + @faSelfHealing + '%'
</IsNotEmpty>
<IsNotEmpty Prepend="And" Property="perorganization">
vg.OrganizationName = @perorganization
</IsNotEmpty>
</Where>
</Statement>
ExportInfo
public partial class ExportInfo
{
public string OrganizationName { get; set; }
public string GroupName { get; set; }
public string SubstationName { get; set; }
public string LineName { get; set; }
public string GlitchType { get; set; }
public string TripType { get; set; }
public string TripSwitchName { get; set; }
public string TripSwitchType { get; set; }
public DateTime? PowerDownTime { get; set; }
public DateTime? PowerUpTime { get; set; }
public string Coincidence { get; set; }
public string FAStart { get; set; }
public string FASelfHealing { get; set; }
public int? Times { get; set; }
public string DownTime { get; set; }
public double? Gongbian { get; set; }
public double? Zhuanbian { get; set; }
public double? DownCounts { get; set; }
public string Description { get; set; }
public string Reason1 { get; set; }
public string Reason2 { get; set; }
public string Problem1 { get; set; }
public string FailureReason { get; set; }
public string Reason3 { get; set; }
public string Problem2 { get; set; }
public string Verified { get; set; }
public string Appeal { get; set; }
public string Corrective { get; set; }
public string Implement { get; set; }
public DateTime? CompleteTime { get; set; }
public string Evaluate { get; set; }
public string Assessment { get; set; }
}
前端
//按钮绑定的触发
handleDownload() {
this.downloadLoading = true
var self = this
request({
url: self.url + '/api/GlitchView/Export',
method: 'post',
headers: {
'Authorization': 'Bearer ' + getToken(),
'Content-Type': 'application/json'
},
contentType: 'application/json',
data: JSON.stringify(self.listQuery)
}).then(response => {
self.list = response
import('@/vendor/Export2Excel').then(excel => {
// 一级表头
const multiHeader = [
['停电信息上报', '', '', '',
'', '', '', '', '',
'', '', '', '', '配网故障分析',
'', '', '', '', '', '',
'', '', '', '', '', '', '',
'缺陷闭环管控', '', '', '评价与考核', '']
]
// 二级表头
const multiHeader2 = [
['单位', '班组/供电所', '变电站', '线路名称',
'故障类型', '跳闸类型', '跳闸开关名称', '跳闸开关类别', '停电时间',
'恢复送电时间', '重合情况', 'FA是否启动', 'FA是否自愈', '可靠性信息',
'', '', '', '', '配网一次分析', '',
'', '', '配网二次分析', '', '', '是否属实', '申诉理由',
'整改措施与计划', '整改落实情况', '整改完成时间', '专业闭环管控评价', '是否典型故障']
]
// 三级表头
const tHeader =
['', '', '', '',
'', '', '', '', '',
'', '', '', '', '累计故障次数',
'停电区间', '公变数量', '专变数量', '停电时户数', '故障说明', '原因一级分类',
'原因二级分类', '暴露问题', '自愈失败原因', '二次缺陷分类', '暴露问题', '', '',
'', '', '', '', '']
// const tHeader = ['序号', '单位', '班组/供电所', '变电站', '线路名称',
// '故障类型', '跳闸类型', '跳闸开关名称', '跳闸开关类别', '停电时间',
// '恢复送电时间', '重合情况', 'FA是否启动', 'FA是否自愈', '累计故障次数',
// '停电区间', '公变数量', '专变数量', '故障说明', '原因一级分类',
// '原因二级分类', '暴露问题', '自愈失败原因', '二次缺陷分类', '暴露问题',
// '整改措施与计划', '整改落实情况', '整改完成时间', '专业闭环管控评价', '是否典型故障']
const filterVal = ['organizationName', 'groupName', 'substationName', 'lineName',
'glitchType', 'tripType', 'tripSwitchName', 'tripSwitchType', 'powerDownTime',
'powerUpTime', 'coincidence', 'faStart', 'faSelfHealing', 'times',
'downTime', 'gongbian', 'zhuanbian', 'downCounts', 'description', 'reason1',
'reason2', 'problem1', 'failureReason', 'reason3', 'problem2', 'verified', 'appeal',
'corrective', 'implement', 'completeTime', 'evaluate', 'assessment']
const list = self.list
const data = self.formatJson(filterVal, list)
// 合并单元格
const merges = [
// 一级合并
'A1:M1',
'N1:AA1',
'AB1:AD1',
'AE1:AF1',
// 二级合并
'A2:A3',
'B2:B3',
'C2:C3',
'D2:D3',
'E2:E3',
'F2:F3',
'G2:G3',
'H2:H3',
'I2:I3',
'J2:J3',
'K2:K3',
'L2:L3',
'M2:M3',
'N2:R2',
'S2:V2',
'W2:Y2',
'Z2:Z3',
'AA2:AA3',
'AB2:AB3',
'AC2:AC3',
'AD2:AD3',
'AE2:AE3',
'AF2:AF3'
]
//调用的Excel导出
excel.export_json_to_excel({
multiHeader, // 第一行的表头
multiHeader2, // 第二行的表头
header: tHeader,
data,
merges,
filename: self.filename,
autoWidth: self.autoWidth,
bookType: self.bookType
})
self.downloadLoading = false
})
})
},
// 数据格式化
formatJson(filterVal, jsonData) {
return jsonData.map(v =>
filterVal.map(j => {
if (j === 'timestamp') {
return parseTime(v[j])
} else {
return v[j]
}
})
)
},
Export2Excel(改动部分)
export function export_json_to_excel({
multiHeader = [],
multiHeader2 = [],//第二个参数
header,
data,
filename,
merges = [],
autoWidth = true,
bookType = 'xlsx'
} = {}) {
/* original data */
filename = filename || 'excel-list'
data = [...data]
data.unshift(header);
//第二行表头
for (let i = multiHeader2.length - 1; i > -1; i--) {
data.unshift(multiHeader2[i])
}
for (let i = multiHeader.length - 1; i > -1; i--) {
data.unshift(multiHeader[i])
}
var ws_name = "SheetJS";
var wb = new Workbook(),
ws = sheet_from_array_of_arrays(data);
if (merges.length > 0) {
if (!ws['!merges']) ws['!merges'] = [];
merges.forEach(item => {
ws['!merges'].push(XLSX.utils.decode_range(item))
})
}
改动后完整的Export2Excel
/* eslint-disable */
import { saveAs } from 'file-saver'
import XLSX from 'xlsx'
function generateArray(table) {
var out = [];
var rows = table.querySelectorAll('tr');
var ranges = [];
for (var R = 0; R < rows.length; ++R) {
var outRow = [];
var row = rows[R];
var columns = row.querySelectorAll('td');
for (var C = 0; C < columns.length; ++C) {
var cell = columns[C];
var colspan = cell.getAttribute('colspan');
var rowspan = cell.getAttribute('rowspan');
var cellValue = cell.innerText;
if (cellValue !== "" && cellValue == +cellValue) cellValue = +cellValue;
//Skip ranges
ranges.forEach(function (range) {
if (R >= range.s.r && R <= range.e.r && outRow.length >= range.s.c && outRow.length <= range.e.c) {
for (var i = 0; i <= range.e.c - range.s.c; ++i) outRow.push(null);
}
});
//Handle Row Span
if (rowspan || colspan) {
rowspan = rowspan || 1;
colspan = colspan || 1;
ranges.push({
s: {
r: R,
c: outRow.length
},
e: {
r: R + rowspan - 1,
c: outRow.length + colspan - 1
}
});
};
//Handle Value
outRow.push(cellValue !== "" ? cellValue : null);
//Handle Colspan
if (colspan)
for (var k = 0; k < colspan - 1; ++k) outRow.push(null);
}
out.push(outRow);
}
return [out, ranges];
};
function datenum(v, date1904) {
if (date1904) v += 1462;
var epoch = Date.parse(v);
return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);
}
function sheet_from_array_of_arrays(data, opts) {
var ws = {};
var range = {
s: {
c: 10000000,
r: 10000000
},
e: {
c: 0,
r: 0
}
};
for (var R = 0; R != data.length; ++R) {
for (var C = 0; C != data[R].length; ++C) {
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;
var cell = {
v: data[R][C]
};
if (cell.v == null) continue;
var 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 if (cell.v instanceof Date) {
cell.t = 'n';
cell.z = XLSX.SSF._table[14];
cell.v = datenum(cell.v);
} else cell.t = 's';
ws[cell_ref] = cell;
}
}
if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
return ws;
}
function Workbook() {
if (!(this instanceof Workbook)) return new Workbook();
this.SheetNames = [];
this.Sheets = {};
}
function s2ab(s) {
var buf = new ArrayBuffer(s.length);
var view = new Uint8Array(buf);
for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
return buf;
}
export function export_table_to_excel(id) {
var theTable = document.getElementById(id);
var oo = generateArray(theTable);
var ranges = oo[1];
/* original data */
var data = oo[0];
var ws_name = "SheetJS";
var wb = new Workbook(),
ws = sheet_from_array_of_arrays(data);
/* add ranges to worksheet */
// ws['!cols'] = ['apple', 'banan'];
ws['!merges'] = ranges;
/* add worksheet to workbook */
wb.SheetNames.push(ws_name);
wb.Sheets[ws_name] = ws;
var wbout = XLSX.write(wb, {
bookType: 'xlsx',
bookSST: false,
type: 'binary'
});
saveAs(new Blob([s2ab(wbout)], {
type: "application/octet-stream"
}), "test.xlsx")
}
export function export_json_to_excel({
multiHeader = [],
multiHeader2 = [],//第二个参数
header,
data,
filename,
merges = [],
autoWidth = true,
bookType = 'xlsx'
} = {}) {
/* original data */
filename = filename || 'excel-list'
data = [...data]
data.unshift(header);
//第二行表头
for (let i = multiHeader2.length - 1; i > -1; i--) {
data.unshift(multiHeader2[i])
}
for (let i = multiHeader.length - 1; i > -1; i--) {
data.unshift(multiHeader[i])
}
var ws_name = "SheetJS";
var wb = new Workbook(),
ws = sheet_from_array_of_arrays(data);
if (merges.length > 0) {
if (!ws['!merges']) ws['!merges'] = [];
merges.forEach(item => {
ws['!merges'].push(XLSX.utils.decode_range(item))
})
}
if (autoWidth) {
/*设置worksheet每列的最大宽度*/
const colWidth = data.map(row => row.map(val => {
/*先判断是否为null/undefined*/
if (val == null) {
return {
'wch': 10
};
}
/*再判断是否为中文*/
else if (val.toString().charCodeAt(0) > 255) {
return {
'wch': val.toString().length * 2
};
} else {
return {
'wch': val.toString().length
};
}
}))
/*以第一行为初始值*/
let result = colWidth[0];
for (let i = 1; i < colWidth.length; i++) {
for (let j = 0; j < colWidth[i].length; j++) {
if (result[j]['wch'] < colWidth[i][j]['wch']) {
result[j]['wch'] = colWidth[i][j]['wch'];
}
}
}
ws['!cols'] = result;
}
/* add worksheet to workbook */
wb.SheetNames.push(ws_name);
wb.Sheets[ws_name] = ws;
var wbout = XLSX.write(wb, {
bookType: bookType,
bookSST: false,
type: 'binary'
});
saveAs(new Blob([s2ab(wbout)], {
type: "application/octet-stream"
}), `${filename}.${bookType}`);
}