1.效果演示
2.用到的第三方类库
前端：React,Dva,Antd
后端：ASP.NET CORE，System.Linq.Dynamic.Core,EPPlus.Core
3.基本思路
第一：EF（LINQ）解决动态列的问题，最开始的思路是直接写SQL，后来想想太Low，就放弃了。百度了下，还真有解决方案，System.Linq.Dynamic.Core
第二：.NetCore解决生成Excel文件（流），使用了EPPlus.Core。
第三：解决文件下载的问题，最开始的思路是，直接传流到前端，前端读response.blob()，然后生成文件，结果......失败，因为所有请求要先过Node层，流经过一次转发后，死活没办法变成excel文件。前端能力不强，所以放弃这个思路了。
按照后端的解决方式，就是直接把文件生成到服务端，然后返回路径给前端，前端跳转一次，就可以实现下载，尝试，成功！
4.核心代码
代码只是初版代码，实现了基础需求。仅做参考
后端部分
请求模型：
public class ExportRequestModel<T> { public List<ExportRowModel> RowRequest { get; set; } /// <summary> /// 导出人ID /// </summary> public string ExportUserID { get; set; } /// <summary> /// 导出模块名称 /// </summary> public string ModuleName { get; set; } public T Where { get; set; } } public class ExportRowModel { public string RowName { get; set; } public string RowKey { get; set; } public string TableName { get; set; } }
ExcelHelper.cs:
public static string Export(ExportRequestModel<TWhere> THead, List<TBody> TBody, string LocalPath) { try { string sFileName = $"{THead.ExportUserID}-{Guid.NewGuid()}.xlsx"; var finallypath = LocalPath + "\excel\" + THead.ModuleName; DirectoryInfo di = new DirectoryInfo(finallypath); if (!di.Exists) { di.Create(); } FileInfo file = new FileInfo(Path.Combine(finallypath, sFileName)); var pack = GetExcelPackage(THead.RowRequest, TBody); pack.SaveAs(file); return ("\excel\" + THead.ModuleName + "\" + sFileName).Replace("\", "/"); } catch (Exception) { throw; } } private static ExcelPackage GetExcelPackage(List<ExportRowModel> THead, List<TBody> TBody) { ExcelPackage package = new ExcelPackage(); var worksheet = package.Workbook.Worksheets.Add("sheet1"); //循环生成表头 for (int i = 1; i <= THead.Count; i++) { worksheet.Cells[1, i].Value = THead[i - 1].RowName; } //循环值 for (int i = 1; i <= TBody.Count; i++) { var body = TBody[i - 1]; var bodyPro = body.GetType().GetProperties(); for (int j = 1; j <= bodyPro.Length; j++) { if (bodyPro[j - 1].Name == "Item") { break; } worksheet.Cells[i + 1, j].Value = bodyPro[j - 1].GetValue(body, null); } } return package; } }
示例代码
ApiResult<string> apiResult = new ApiResult<string>(); List<string> selectKey = new List<string>(); foreach (var item in request.RowRequest) { var tabOtherName = string.IsNullOrEmpty(item.TableName) ? item.TableName : item.TableName + "."; selectKey.Add($"{tabOtherName}{item.RowKey} as {item.RowName}"); } var ExportList = _studentStatuService.Query() .Include(n => n.Payment) .Include(n => n.Student) .Include(n => n.Student.Department) .Where(n => (int)n.StudentType > 1) .HasWhere(request.Where.DepartMentID, n => n.Student.DepartmentID == request.Where.DepartMentID) .HasWhere(request.Where.EduMajorID, n => n.Payment.EduMajorId == request.Where.EduMajorID) .HasWhere(request.Where.EduSchoolID, n => n.Payment.EduSchoolId == request.Where.EduSchoolID) .HasWhere(request.Where.Name, n => n.StudentNo.Contains(request.Where.Name)) .HasWhere(request.Where.IdCard, n => n.Student.IDCard.Contains(request.Where.IdCard)) .HasWhere(request.Where.StartTime, n => n.CreateTime>= request.Where.StartTime) .HasWhere(request.Where.EndTime, n => n.CreateTime < request.Where.EndTime) .Select(n => new StudentLessResponse { PhoneNum = n.Student.PhoneNum, School = n.Payment.EduSchoolName, Major = n.Payment.EduMajorName, CreateTime = n.CreateTime.ToString("yyyy-MM-dd HH:mm:ss"), Guid = n.Student.Guid.ToString(), Name = n.Student.Name, DeptName = n.Student.Department.Name, StudentType = (int)n.StudentType, DiplomaType = string.Join("/", EnumHelper.GetDescription(n.DiplomaType)), GetSeason = EnumHelper.GetDescription(n.GetSeason), GetYear = n.GetYear, LevelSeason = EnumHelper.GetDescription(n.LevelSeason), LevelYear = n.LevelYear, StudentNo = n.StudentNo, TemporaryNo = n.TemporaryNo }) .Select($"new ({string.Join(',', selectKey.ToArray())})") .ToDynamicList(); var pack = ExcelHelper<dynamic, StudentPagesRequest>.Export(request, ExportList, LocalPath);
如果需要用到Inner Join
则需要使用SelectMany,具体语法自行百度。
前端代码
Export.js组件：
import React from 'react'; import { Popover, Button, Checkbox, Row, Col, DatePicker } from 'antd'; import PropTypes from 'prop-types'; import { setLocalStorage, getLocalStorage } from '../../utils/cookieHelper'; const { RangePicker } = DatePicker; class Export extends React.Component { state = { visible: false, checkedValues: getLocalStorage(this.props.moduleName), StartTime: null, EndTime: null } onChange = (checkedValues) => { const { exportList } = this.props; this.setState({ checkedValues }); } export = () => { const { checkedValues, EndTime, StartTime } = this.state; const { moduleName, onExport, exportList } = this.props; setLocalStorage(moduleName, checkedValues); const finallyValues = exportList.filter((item, index) => { return checkedValues.indexOf(item.RowKey) > -1; }); const data = { ModuleName: moduleName, RowRequest: finallyValues, where: { StartTime, EndTime } } console.log(data); if (onExport) { onExport(data); } } onTimeChange = (date, dateString) => { this.setState({ StartTime: dateString[0], EndTime: dateString[1] }) } render() { const { exportList, moduleName } = this.props; const ccontent = (<div> <Checkbox.Group onChange={this.onChange} style={{ '220px' }} value={this.state.checkedValues}> <Row> <div style={{ marginBottom: '12px' }}> <div style={{ marginBottom: '5px' }}> 请选择数据产生的时间：</div> <RangePicker onChange={this.onTimeChange} /> </div> {exportList.map((item, index) => { return (<Col span={12}><Checkbox key={item.RowKey} value={item.RowKey}>{item.RowName}</Checkbox></Col>) })} </Row> </Checkbox.Group> <Row style={{ textAlign: 'center', marginTop: '10px' }}> <Col span={2}></Col> <Col span={10}> <a onClick={this.export}>确定</a></Col> <Col span={10}> <a onClick={() => { let visb = !this.state.visible; this.setState({ visible: visb }) }}>取消</a></Col> <Col span={2}></Col> </Row></div>); return ( <Popover content={ccontent} title="请选择导出列" trigger="click" visible={this.state.visible} placement="bottom" > <Button type="primary" onClick={() => { let visb = !this.state.visible; this.setState({ visible: visb }) }} icon={'export'} style={{ marginRight: '10px' }}>导出</Button> </Popover > ); } } Export.propTypes = { onExport: PropTypes.func, moduleName: PropTypes.string, exportList: PropTypes.any }; export default Export;
使用方式：
最后希望对大家有帮组！