public string OnlineExportData(ExportDataModel exp)
{
EasyMan.Dtos.ErrorInfo err = new EasyMan.Dtos.ErrorInfo();
err.IsError = false;
try
{
//非压缩导出
string strFileName = exp.FileName;
//lngDbServerId = (long)exp.DbServerId;
strMapPathAll = "";
strPathAll = "";
string strPath = exp.FilePath;
Easyman.Common.Fun.GetPath(ref strFileName, exp.FileFormat, ref strMapPathAll, ref strPath);//重新整理保存路径
#region 表结果dt
DataTable dt = new DataTable();
if (exp.DbServerId == null)
{
dt = DbHelper.ExecuteGetTable(exp.Sql);
}
else
{
dt = _dbServerAppService.ExecuteGetTable((int)exp.DbServerId, exp.Sql); //要导出的数据集
}
#endregion
string ColumnHeader = "";
switch (exp.FileFormat.ToLower())
{
case ".xlsx":
ColumnHeader = GetHeadStr(exp.TopFields); //将表头对像转成字符串
OutExcelHelper.ExportExcel(dt, strFileName, strMapPathAll, ColumnHeader, true);
break;
case ".csv":
OutExcelHelper.ExportCsv(dt, strFileName, strMapPathAll, ColumnHeader, true);
break;
case ".txt":
OutExcelHelper.ExportTxt(dt, strFileName, strMapPathAll, ColumnHeader, true);
break;
default:
ColumnHeader = GetHeadStr(exp.TopFields); //将表头对像转成字符串
OutExcelHelper.ExportExcel(dt, strFileName, strMapPathAll, ColumnHeader, true);
break;
}
exp.FileName += exp.FileFormat;
SavaDBSql(strPath, strMapPathAll, exp);
return(strPath);
}
catch (Exception ex)
{
err.IsError = true;
err.Message = ex.Message;
throw;
}
}
public async Task <IActionResult> Export([Bind("MondayStartTime,MondayEndTime,TuesdayStartTime,TuesdayEndTime," +
"WednesdayStartTime,WednesdayEndTime,ThursdayStartTime,ThursdayEndTime,FridayStartTime,FridayEndTime," +
"SaturdayStartTime,SaturdayEndTime,SundayStartTime,SundayEndTime")] ExportDataModel exportData)
{
var user = GetCurrentUserAsync().Result;
List <Movie> movies = await _context.Movie.Where(x => x.UserId == user.Id)
.OrderBy(x => x.Position).ToListAsync();
Dictionary <DayOfWeek, (DateTime?startTime, DateTime?endTime, int amount)> availability = new Dictionary <DayOfWeek, (DateTime?, DateTime?, int)>()
{
{ DayOfWeek.Monday, (exportData.MondayStartTime, exportData.MondayEndTime, exportData.MondayAmount) },
{ DayOfWeek.Tuesday, (exportData.TuesdayStartTime, exportData.TuesdayEndTime, exportData.TuesdayAmount) },
public byte[] Export <TData>(ExportDataModel <TData> model) where TData : SaasBaseModel
{
IWorkbook workbook = base.CreateWorkbook();
ISheet sheet = base.CreateSheet(workbook, typeof(TData).Name);
var columns = _exportColumnProvider.Provide <TData>();
base.SetHead(sheet, columns);
var rowIndex = 1;
foreach (var data in model.Datas)
{
SetRows <TData>(sheet, data, rowIndex, columns);
rowIndex++;
}
return(base.ExportBase(workbook));
}
public override Task ExportData <TDataSetType>(ExportDataModel <TDataSetType> model)
{
if (!IsExistExportsFolder())
{
CreateExportsFolder();
}
using (StreamWriter streamWriter = File.CreateText($@"{ExportsDirectory}\{model.FileName}_{DateTime.Now:yyyy-MM-dd_hh-mm-ss}.json"))
{
JsonSerializer serializer = new();
serializer.Serialize(streamWriter, model.DataSet);
}
return(Task.CompletedTask);
}
/// <summary>
/// 多线程执行体
/// </summary>
/// <param name="exp"></param>
private static void OutExportFile(object exp)
{
ExportDataModel ExportDataM = (ExportDataModel)exp;
DataTable dt = (DataTable)ExportDataM.ObjParam;
switch (ExportDataM.FileFormat.ToLower())
{
case ".xlsx":
OutExcelHelper.ExportExcel(dt, ExportDataM.FileName, ExportDataM.FilePath, ExportDataM.ColumnHeader, false);
break;
case ".csv":
OutExcelHelper.ExportCsv(dt, ExportDataM.FileName, ExportDataM.FilePath, ExportDataM.ColumnHeader, false);
break;
case ".txt":
OutExcelHelper.ExportTxt(dt, ExportDataM.FileName, ExportDataM.FilePath, ExportDataM.ColumnHeader, false);
break;
default:
OutExcelHelper.ExportExcel(dt, ExportDataM.FileName, ExportDataM.FilePath, ExportDataM.ColumnHeader, false);
break;
}
}
/// <summary>
/// 将生成的数据保存数据库
/// </summary>
/// <param name="strPath">保存后的虚拟路径</param>
/// <param name="strMapPath">保存后的物理路径</param>
public void SavaDBSql(string strPath, string strMapPath, ExportDataModel exp)
{
long lngUserId = (long)(exp.ExportWay == "online" ? GetCurrentUserAsync().Result.Id : exp.UserId);
#region 更新文件表
//文件管理列表
Easyman.Domain.Files files = new Easyman.Domain.Files();
files.FileType = exp.ExportWay;
files.Name = exp.DisplayName;
files.TrueName = exp.FileName;
files.UploadTime = DateTime.Now;
files.UserId = lngUserId;
if (exp.ExportWay == "online")
{
files.Id = 0;
}
else
{
files.Id = exp.FilesId == null ? 0 : (int)exp.FilesId;
}
if (strPath != null && strMapPath != null && strPath != "" && strMapPath != "")
{
strPath = strPath.Replace("\\", "/");
System.IO.FileInfo FileObj = new FileInfo(strMapPath);
files.Length = FileObj.Length;//文件大小
files.Path = strPath;
files.Url = strPath;
exp.FileSize = (int)FileObj.Length;
exp.EndTime = DateTime.Now;
exp.FilePath = strPath;
exp.Status = "生成成功";
}
exp.FilesId = _filesRepository.InsertOrUpdateAndGetId(files); //更新文件管理列表
if (exp.FilesId <= 0)
{
lngUserId = 0;
EasyMan.Dtos.ErrorInfo err = new EasyMan.Dtos.ErrorInfo();
err.IsError = false;
err.Message = "导出文件后，保存至文件管理数据库出错";
return;
}
#endregion
exp.FilesId = exp.FilesId;
exp.UserId = lngUserId;
if (exp.ExportWay == "online")
{
exp.BeginTime = DateTime.Now;
exp.Id = 0;
}
if (strPath == null || strMapPath == null)
{
///向导出数据生成记录里面插入数据
exp.BeginTime = DateTime.Now;
exp.FileSize = 0;
exp.Status = "生成中";
}
var ent = AutoMapper.Mapper.Map <ExportData>(exp);
exp.Id = _exportDataRepository.InsertOrUpdateAndGetId(ent); //更新导出数据生成记录
if (exp.Id <= 0)
{
EasyMan.Dtos.ErrorInfo err = new EasyMan.Dtos.ErrorInfo();
err.IsError = false;
err.Message = "导出文件后，保存至生成记录数据库出错";
return;
}
if (exp.ExportWay == "online")
{
DateTime dtmCreateTime = DateTime.Now;
//数据下载记录表
DownData downData = new DownData();
downData.DisplayName = exp.DisplayName;
downData.DownBeginTime = dtmCreateTime;
downData.DownEndTime = dtmCreateTime;
downData.ExportDataId = exp.Id;
downData.FileName = exp.FileName;
downData.FilePath = strPath;
downData.FileSize = exp.FileSize;
downData.Status = "成功";
downData.UserId = lngUserId;
if (_downDataRepository.InsertOrUpdateAndGetId(downData) <= 0)
{
EasyMan.Dtos.ErrorInfo err = new EasyMan.Dtos.ErrorInfo();
err.IsError = false;
err.Message = "导出文件后，保存至下载记录表数据库出错";
return;
}
}
}
/// <summary>
/// 检测线程是否全部执行完成
/// </summary>
/// <param name="source"></param>
/// <param name="e"></param>
private void OutTime(object source, System.Timers.ElapsedEventArgs e, string strPath, string strFileName, ExportDataModel exp, List <Thread> LstThread)
{
#region 检测线程是否全部执行完成
List <Thread> outThreadList = LstThread;
List <Thread> TempOutThreadList = LstThread;
#region 清除已停止的线程
for (int i = 0; i < TempOutThreadList.Count; i++)
{
Thread item = TempOutThreadList[i];
if (item.IsAlive == false)
{
outThreadList.Remove(item);
ThreadList.Remove(item);
}
}
#endregion
#region 到时停止现有运行的线程
if (DateTime.Now >= dtmEndTime)
{
TempOutThreadList = outThreadList;
for (int i = 0; i < TempOutThreadList.Count; i++)
{
Thread outItem = TempOutThreadList[i];
if (outItem.IsAlive == false)
{
outItem.Abort();//停止线程
outThreadList.Remove(outItem);
}
}
}
#endregion
#endregion
if (ThreadList.Count == 0)
{
t.Stop();//停止循环检测
string strMapPath = "";
strMapPath = strMapPathAll.Split('|')[0];
string[] panth = strMapPath.Split('\\');
strMapPath = strMapPath.Replace(panth[panth.Length - 1], "");
strMapPath = strMapPath + strFileName;
exp.FilePath = strMapPath;
if (1 < intPagIndex)
{
ZipHelper zip = new ZipHelper();
strMapPath += ".zip";
exp.FileFormat = ".zip";
exp.FileName = strFileName + ".zip";
strPath += "/" + DateTime.Now.Month + "/" + strFileName + ".zip";
exp.FilePath += ".zip";
zip.ZipFiles(strMapPathAll, strMapPath);
}
SavaDBSql(strPath, strMapPath, exp);
}
}
/// <summary>
/// 离线导出
/// </summary>
/// <param name="exp">数据集</param>
/// <param name="intCountNum">数据总量</param>
public string OfflineExportData(ExportDataModel exp, int intCountNum)
{
EasyMan.Dtos.ErrorInfo err = new EasyMan.Dtos.ErrorInfo();
err.IsError = false;
var config = GetExportConfig("report");
var intTheadCount = 1000;//线程最大值
if (ThreadList.Count >= intTheadCount)
{
return("导出时，由于当前运行的线程数已达到了最大值，请稍后再尝试离线下载，或请联系管理给于帮助！");
}
int intRowNum = (int)config.MaxRowNum; //得到配置的每个文档最大行数
dtmEndTime = DateTime.Now.AddSeconds((int)config.MaxTime); //得到配置的最大导出时长（秒）
intPagIndex = intCountNum / intRowNum; //计算总页数
string strFileName = exp.FileName;
//lngDbServerId = (long)exp.DbServerId;
strMapPathAll = "";
strPathAll = "";
exp.UserId = GetCurrentUserAsync().Result.Id;
if (intCountNum % intRowNum != 0)
{
intPagIndex++;
}
#region 转换表头
if (exp.FileFormat.ToLower() == ".xlsx")
{
exp.ColumnHeader = GetHeadStr(exp.TopFields);//将表头对像转成字符串
}
else
{
exp.ColumnHeader = "";
}
string strFilePath = exp.FilePath;//保存原始数据
#endregion
object obj = new object();
if (intPagIndex > 1) //如果大于1页，就启用分文件再压缩的方式离线导出文件
{
string strTempFileFormat = exp.FileFormat; //暂存文件格式
exp.FileFormat = ".zip"; //压缩文件格式
SavaDBSql(null, null, exp); //添加初始值
exp.FileFormat = strTempFileFormat; //还原文件格式
TempThreadList = new List <Thread>(); //记录线程信息
for (int i = 1; i <= intPagIndex; i++)
{
string strTempFileName = strFileName + "_" + i + exp.FileFormat;
exp.FileName = strTempFileName;
DataTable dt = new DataTable();
if (exp.DbServerId == null)
{
string sqlOne = _reportAppService.SqlForPage(DbHelper.GetCurConnection().DbType.ToString(), exp.Sql, i, intRowNum, ref err);
dt = DbHelper.ExecuteGetTable(sqlOne);
}
else
{
dt = _dbServerAppService.ExecuteGetTable((int)exp.DbServerId, exp.Sql, i, intRowNum); //要导出的数据集
}
dt.Columns.Remove("N"); //删除分页引起的多一列数据
exp.ObjParam = dt;
string strPath = strFilePath; //虚拟地址
string strMapPath = ""; //物理地址
Easyman.Common.Fun.GetPath(ref strTempFileName, exp.FileFormat, ref strMapPath, ref strPath); //重新整理保存路径
exp.FilePath = strMapPath;
strPathAll += (strPathAll == "" ? "" : "|") + strPath;
strMapPathAll += (strMapPathAll == "" ? "" : "|") + strMapPath;
Thread thread = new Thread(new ParameterizedThreadStart(OutExportFile));//启用多线程处理数据
thread.IsBackground = true;
thread.Name = strTempFileName.Replace(exp.FileFormat, "");
obj = new object();
obj = exp;
thread.Start(obj);
ThreadList.Add(thread); //添加到总记录里面
TempThreadList.Add(thread); //添加到临时记录
}
ExportDataModel ExportDataM = new ExportDataModel();
ExportDataM = exp;
t = new System.Timers.Timer(10000); //实例化Timer类，设置间隔时间为10000毫秒；
t.Elapsed += new System.Timers.ElapsedEventHandler((s, e) => OutTime(s, e, strFilePath, strFileName, (ExportDataModel)obj, TempThreadList));
t.AutoReset = true; //设置是执行一次（false）还是一直执行(true)；
t.Enabled = true; //是否执行System.Timers.Timer.Elapsed事件；
t.Start();
}
else
{
SavaDBSql(null, null, exp); //添加初始值
//非压缩导出
TempThreadList = new List <Thread>(); //记录线程信息
string strPath = exp.FilePath;
Easyman.Common.Fun.GetPath(ref strFileName, exp.FileFormat, ref strMapPathAll, ref strPath); //重新整理保存路径
exp.FileName = strFileName;
exp.FilePath = strMapPathAll;
DataTable dt = new DataTable();
if (exp.DbServerId == null)
{
dt = DbHelper.ExecuteGetTable(exp.Sql);
}
else
{
dt = _dbServerAppService.ExecuteGetTable((int)exp.DbServerId, exp.Sql); //要导出的数据集
}
//exp.ObjParam = _dbServerAppService.ExecuteGetTable((int)exp.DbServerId, exp.Sql); //要导出的数据集
exp.ObjParam = dt;
Thread thread = new Thread(new ParameterizedThreadStart(OutExportFile));//启用多线程处理数据
thread.IsBackground = true;
thread.Name = strFileName.Replace(exp.FileFormat, "");
obj = new object();
obj = exp;
thread.Start(obj);
ThreadList.Add(thread); //添加到总记录里面
TempThreadList.Add(thread); //添加到临时记录
ExportDataModel ExportDataM = new ExportDataModel();
ExportDataM = exp;
t = new System.Timers.Timer(10000); //实例化Timer类，设置间隔时间为10000毫秒；
t.Elapsed += new System.Timers.ElapsedEventHandler((s, e) => OutTime(s, e, strPath, strFileName, ExportDataM, TempThreadList));
t.AutoReset = true; //设置是执行一次（false）还是一直执行(true)；
t.Enabled = true; //是否执行System.Timers.Timer.Elapsed事件；
t.Start();
}
return("导出命令已发出");
}
/// <summary>
/// 导出数据
/// </summary>
/// <returns></returns>
public ActionResult ExportData()
{
string sql = "";
long tbReportId = 0; //初始化tbReportId
string strResult = ""; //返回参数
//获得参数
string code = Request["code"].Trim();
if (!string.IsNullOrEmpty(Request["tbReportId"].Trim()))
{
tbReportId = Convert.ToInt64(Request["tbReportId"].Trim());
}
string queryParams = Request["queryParams"]; //查询条件
string url = Request["url"]; //页面url
url = System.Web.HttpUtility.UrlDecode(url);
string bootUrl = Request["bootUrl"]; //网站根目录（含虚拟层级）
string strHost = bootUrl.Substring(0, bootUrl.IndexOf(Request.Url.Authority.ToLower()) + Request.Url.Authority.Length); //http头
string exportWay = Request["exportWay"]; //导出方式
string fileFormat = Request["fileFormat"]; //文件格式
EasyMan.Dtos.ErrorInfo err = new EasyMan.Dtos.ErrorInfo();
err.IsError = false;
#region 逻辑部分
try
{
var report = _reportAppService.GetReportBase(code);
var expCfg = _exportAppService.GetExportConfig("report");//得到当前功能的配置信息
#region 默认值
int intWaitTime = 60000;
int intMaxRowNum = 1800000;
int intDataSize = 51200;
int intValidDay = 15;
string strPath = "upfiles";
if (expCfg.WaitTime == null || expCfg.WaitTime <= 0)
{
expCfg.WaitTime = intWaitTime;
}
if (expCfg.MaxRowNum == null || expCfg.MaxRowNum <= 0)
{
expCfg.MaxRowNum = intMaxRowNum;
}
if (expCfg.DataSize == null || expCfg.DataSize <= 0)
{
expCfg.DataSize = intDataSize;
}
if (expCfg.ValidDay == null || expCfg.ValidDay <= 0)
{
expCfg.ValidDay = intValidDay;
}
if (expCfg.Path == null || expCfg.Path.Trim() == "")
{
expCfg.Path = (bootUrl.Replace(strHost, "") + strPath).Replace("\\", "/").Replace("//", "/");
}
else
{
expCfg.Path = (bootUrl.Replace(strHost, "") + expCfg.Path).Replace("\\", "/").Replace("//", "/");
}
#endregion
sql = _reportAppService.GetSqlForField(code, queryParams, tbReportId, ref err);
if (err.IsError)
{
throw new Exception(err.Message);
}
int intCountSize = IntDataSize(sql, (report == null || report.DbServerId == null ? null : report.DbServerId));//返回当前集合条数
if (intCountSize <= 0)
{
return(Content("暂无无数据导出！"));
}
#region 抽样数据
double WaitTime = (double)expCfg.WaitTime;
DateTime datEndDate = DateTime.Now.AddMilliseconds(WaitTime);//最大等待时长
if (DateTime.Now > datEndDate && exportWay == "online")
{
return(Content("online导出时，由于数据量过大，在统计数据时超出online最大等待时长，请转为offline导出。是否转为offline导出？"));
}
long intPumping = GetPumpingSize(report.DbServerId, sql, intCountSize, WaitTime, exportWay);//通过样品数据预估数据集大小,如果小于等于0，表示超时
if (intPumping <= 0)
{
return(Content("online导出时，由于数据量过大，在数据抽样时超出online最大等待时长，请转为offline导出。是否转为offline导出？"));
}
#endregion
//判断是否为offline
if ((intCountSize > expCfg.MaxRowNum || intPumping > expCfg.DataSize) && exportWay == "online")
{
return(Content("online导出最大支持" + expCfg.MaxRowNum + "条数据及" + expCfg.DataSize + "KB字节,是否转为offline导出？"));
}
object objTopFields = "";
if (tbReportId != 0)
{
var topFieldArr = _tbReportAppService.GetFildTopList(tbReportId); //获取多表头字段集合
var fieldArr = _tbReportAppService.GetFildList(tbReportId); //获取字段集合
if (topFieldArr != null && topFieldArr.Count > 0)
{
objTopFields = GetTopFieldForExcel(topFieldArr.ToArray(), fieldArr.ToArray()); //多表头信息
}
}
//根据url得到module
var module = _moduleAppService.GetModuleByUrl(url);
string strExt = GetExtend(fileFormat.ToLower());
ExportDataModel exp = new ExportDataModel
{
ReportCode = code,
DisplayName = (module.Name == null || module.Name.Trim() == "" ? "" : module.Name + "_") + DateTime.Now.Ticks,
ExportWay = exportWay,
FromUrl = url,
FileFormat = strExt,
FilePath = expCfg.Path,
ValidDay = expCfg.ValidDay,
TopFields = objTopFields,//多表头信息
ColumnHeader = "",
Sql = sql,
DbServerId = report == null || report.DbServerId == null ? null : report.DbServerId,
FileName = (module == null ? "无名称" : module.Name) + "_" + DateTime.Now.Ticks,
Status = "生成中",
ObjParam = "",
IsClose = false
};
if (module.Id > 0)
{
exp.ModuleId = module.Id;
}
//针对两种形式的导出处理，待完善
switch (exportWay)
{
case "offline":
strResult = _exportAppService.OfflineExportData(exp, intCountSize);
break;
case "online":
strResult = strHost + _exportAppService.OnlineExportData(exp);
break;
default:
strResult = strHost + _exportAppService.OnlineExportData(exp);
break;
}
}
catch (Exception ex)
{
err.IsError = false;
string strHtml = "<script src=\"../Scripts/jquery-2.2.4.min.js\"></script>";
strHtml += "<script src=\"../Common/rootUrl.js\"></script>";
strHtml += "<script src=\"../Common/Scripts/errorPage/error.js\"></script>";
strHtml += "<script>$(function () {SendErrorInfo('导出提示','导出时，程序异常。代码：" + ex.Message + ",请联系管理员')})</script>";
return(Content(strHtml));
}
#endregion
return(Content(strResult));
}
public abstract Task ExportData <TDataSetType>(ExportDataModel <TDataSetType> model);