/// <summary>
/// 导出Excel数据功能
/// </summary>
/// <param name="pContext"></param>
/// <returns></returns>
private string export(HttpContext pContext)
{
//获取所有的符合条件的数据
DataTable dtTest = GetStoreData(pContext);
GridInitEntity g = GetInitGridData(pContext);
#region 替换标题信息
if (g != null && g.GridColumnDefinds != null)
{
for (int i = 0; i < dtTest.Columns.Count; i++)
{
if (dtTest.Columns[i].ColumnName == "StoreID")
{
dtTest.Columns[i].ColumnName = "终端标识ID";
continue;
}
for (int j = 0; j < g.GridColumnDefinds.Count; j++)
{
if (dtTest.Columns[i].ColumnName == g.GridColumnDefinds[j].DataIndex)
{
dtTest.Columns[i].ColumnName = g.GridColumnDefinds[j].ColumnText;
break;
}
}
}
}
#endregion
Workbook wbTest = JIT.Utility.DataTableExporter.WriteXLS(dtTest, 0);
string MapUrl = pContext.Server.MapPath(@"~/File/Excel/" + DateTime.Now.ToString("yyyy.MM.dd.HH.mm.ss.ms") + ".xls");
wbTest.Save(MapUrl); //保存Excel文件
OutPutExcel(pContext, MapUrl); //输出Excel文件
return("{success:true}");
}
/// <summary>
/// 初始化会员列表的定义数据模型与列表定义
/// </summary>
/// <param name="pContext"></param>
/// <returns></returns>
private GridInitEntity GetInitGridData(HttpContext pContext)
{
GridInitEntity g = new GridInitEntity();
g.GridDataDefinds = GetGridDataModels(pContext);
g.GridColumnDefinds = GetGridColumns(pContext);
return(g);
}
/// <summary>
/// 初始化终端点选控件
/// </summary>
/// <param name="pContext"></param>
/// <returns></returns>
private GridInitEntity GetInitSelectGridData(HttpContext pContext)
{
GridInitEntity g = new GridInitEntity();
g.GridDataDefinds = GetStoreGridDataModels(pContext);
g.GridColumnDefinds = GetStoreGridColumns(pContext);
// g.GridDatas = GetStoreSelectPageData(pContext);
return(g);
}
/// <summary>
/// 获取
/// </summary>
/// <param name="pContext"></param>
/// <returns></returns>
private GridInitEntity GetInitGridDataByEventID(string pEventID)
{
GridInitEntity g = new GridInitEntity();
if (!string.IsNullOrEmpty(pEventID))
{
g.GridDataDefinds = GetGridDataModelsByEventID(pEventID);
g.GridColumnDefinds = GetGridColumnsByEventID(pEventID);
}
return(g);
}
private string Export(HttpContext pContext)
{
//获取所有的符合条件的数据
DataTable dtExport = GetStoreData(pContext);
GridInitEntity g = GetInitGridData(pContext);
#region 替换标题信息
if (g != null && g.GridColumnDefinds != null)
{
if (dtExport.Columns.Contains("VIPID"))
{
dtExport.Columns.Remove("VIPID");
}
for (int i = 0; i < dtExport.Columns.Count; i++)
{
for (int j = 0; j < g.GridColumnDefinds.Count; j++)
{
if (dtExport.Columns[i].ColumnName == g.GridColumnDefinds[j].DataIndex)
{
dtExport.Columns[i].ColumnName = g.GridColumnDefinds[j].ColumnText;
break;
}
}
}
}
#endregion
Workbook wbExport = JIT.Utility.DataTableExporter.WriteXLS(dtExport, 0);
wbExport.Worksheets[0].FreezePanes(1, 1, 1, 1);
wbExport.Worksheets[0].AutoFitColumns();
string mapPath = pContext.Server.MapPath(@"~/File/Excel/");
if (!Directory.Exists(mapPath.Remove(mapPath.LastIndexOf(@"\"), mapPath.Length - mapPath.LastIndexOf(@"\"))))//创建目录
{
Directory.CreateDirectory(mapPath);
}
string filePath = mapPath + DateTime.Now.ToString("yyyy.MM.dd.HH.mm.ss.ms") + ".xls";
wbExport.Save(filePath); //保存Excel文件
new ExcelCommon().OutPutExcel(pContext, filePath); //输出Excel文件
return("{success:true}");
}
public string GetImportFile(HttpContext pContext)
{
DateTime dtStart = DateTime.Now;
Aspose.Cells.License lic = new Aspose.Cells.License();
lic.SetLicense("Aspose.Total.lic");
var g = new GridInitEntity();
Workbook workBook = new Workbook();
Worksheet sheet = null;
g.GridColumnDefinds = GetGridColumns(pContext);
workBook.Open(HttpContext.Current.Server.MapPath("/File/Excel/DownLoad/VIPTemplate.xls"));
sheet = workBook.Worksheets[1];
sheet.Name = "会员";
for (int i = 0; i < g.GridColumnDefinds.Count; i++)
{
sheet.Cells[0, i].PutValue(g.GridColumnDefinds[i].ColumnText);
if (g.GridColumnDefinds[i].DataIndex.ToLower() == "storename")//冻结
{
sheet.FreezePanes(1, 1, 1, 0);
}
Style s = sheet.Cells[0, i].GetStyle();//颜色设置
s.Font.Color = Color.White;
s.Font.IsBold = true;
s.Pattern = BackgroundType.Solid;
if (g.GridColumnDefinds[i].IsMustDo.HasValue &&
g.GridColumnDefinds[i].IsMustDo.Value == 1)
{
s.ForegroundColor = Color.Red;
}
else
{
s.ForegroundColor = Color.Gray;
}
sheet.Cells[0, i].SetStyle(s);
}
sheet.AutoFitColumns();
string savePath = pContext.Server.MapPath(string.Format("/File/Excel/DownLoad/temp/{0}_{1}_{2}.xls",
CurrentUserInfo.ClientID,
CurrentUserInfo.UserID,
DateTime.Now.ToString("yyyyMMddHHmmssfff")));
if (!Directory.Exists(savePath.Remove(savePath.LastIndexOf(@"\"), savePath.Length - savePath.LastIndexOf(@"\"))))//创建目录
{
Directory.CreateDirectory(savePath);
}
Loggers.Debug(new DebugLogInfo()
{
ClientID = this.CurrentUserInfo.ClientID, UserID = this.CurrentUserInfo.UserID, Message = "输出模板文件开始[" + DateTime.Now + "]"
});
workBook.Save(savePath);
new ExcelCommon().OutPutExcel(pContext, savePath);
DateTime dtFinish = DateTime.Now;
Loggers.Debug(new DebugLogInfo()
{
ClientID = this.CurrentUserInfo.ClientID, UserID = this.CurrentUserInfo.UserID, Message = "输出模板文件结束[" + DateTime.Now + "].共花费时间[" + (dtFinish - dtStart).TotalSeconds.ToString() + "]s"
});
return("test");
}
private void DoWork()
{
while (true)
{
try
{
ConfigurationManager.RefreshSection("appSettings");
var time = ConfigurationManager.AppSettings["Timing"];
TimeSpan.TryParse(time, out _timeSpan);
if (DateTime.Now.TimeOfDay.Hours == _timeSpan.Hours)
{
//支持配置多客户
string[] customerIDs = ConfigurationManager.AppSettings["CustomerIDs"].Split(',');
foreach (string customerID in customerIDs)
{
loggingSessionInfo = Default.GetBSLoggingSession(customerID, "1");
var eventsService = new LEventsBLL(loggingSessionInfo);
LEventsEntity queryEntity = new LEventsEntity();
queryEntity.EventType = 0;
var data = eventsService.WEventGetWebEvents(queryEntity, 0, 100000000);
foreach (var item in data)
{
XieHuiBaoBLL b = new XieHuiBaoBLL(loggingSessionInfo, "vip");
List <DefindControlEntity> l = new List <DefindControlEntity>();
PageResultEntity pageEntity = null;
try
{
pageEntity = b.GetPageDataByEventID(l, 100000000, 0, item.EventID);
}
catch (Exception e)
{
Loggers.Debug(new DebugLogInfo
{
Message = string.Format("{0} {1} (EventID:{3})报名人员信息通知邮件发送{2}", DateTime.Now.ToShortDateString(), DateTime.Now.TimeOfDay, "失败", item.EventID)
});
Loggers.Exception(new BasicUserInfo(), e);
continue;
}
if (pageEntity.GridData != null)
{
if (pageEntity.GridData.Rows.Count == 0)
{
continue;
}
}
GridInitEntity g = GetInitGridDataByEventID(item.EventID);
if (pageEntity != null && pageEntity.GridData != null)
{
#region 替换标题信息
if (g != null && g.GridColumnDefinds != null)
{
if (pageEntity.GridData.Columns.Contains("ROW_NUMBER"))
{
pageEntity.GridData.Columns.Remove("ROW_NUMBER");
}
if (pageEntity.GridData.Columns.Contains("SignUpID"))
{
pageEntity.GridData.Columns.Remove("SignUpID");
}
if (pageEntity.GridData.Columns.Count == 0)
{
continue;
}
for (int i = 0; i < pageEntity.GridData.Columns.Count; i++)
{
for (int j = 0; j < g.GridColumnDefinds.Count; j++)
{
if (pageEntity.GridData.Columns[i].ColumnName.ToLower() == g.GridColumnDefinds[j].DataIndex.ToLower())
{
pageEntity.GridData.Columns[i].ColumnName = g.GridColumnDefinds[j].ColumnText;
break;
}
}
}
}
#endregion
}
//数据获取
Workbook wb = DataTableExporter.WriteXLS(pageEntity.GridData, 0);
wb.Worksheets[0].Name = "参加活动人员信息";
string savePath = ConfigurationManager.AppSettings["FileURL"];
if (!Directory.Exists(savePath))
{
Directory.CreateDirectory(savePath);
}
savePath = savePath + "/" + item.Title + DateTime.Now.ToString("yyyyMMdd") + ".xls";
wb.Save(savePath);//保存Excel文件
#region 邮件发送
FromSetting fs = new FromSetting();
fs.SMTPServer = ConfigurationManager.AppSettings["SmtpServer"];
fs.SendFrom = ConfigurationManager.AppSettings["MailSendFrom"];
fs.UserName = ConfigurationManager.AppSettings["MailUserName"];
fs.Password = ConfigurationManager.AppSettings["MailUserPassword"];
string mailTo = ConfigurationManager.AppSettings["MailTo"];
string isSendMail = ConfigurationManager.AppSettings["IsSendMailToCreator"];
//是否发送成功
bool result = false;
if (!string.IsNullOrEmpty(mailTo))
{
result = Mail.SendMail(fs, mailTo, ConfigurationManager.AppSettings["MailTitle"], ConfigurationManager.AppSettings["Subject"], savePath.Split(','));
}
if (isSendMail == "1")
{
if (!string.IsNullOrEmpty(item.Email))
{
if (DateTime.Now < DateTime.Parse(item.EndTime))
{
//等于0表示不发送邮件
if (item.MailSendInterval == null || item.MailSendInterval == 0)
{
continue;
}
//日期差与间隔时间取余判断是否符合发送邮件日期
int dateDiff = ((TimeSpan)(DateTime.Now.Date - item.LastUpdateTime.Value.Date)).Days;
if (dateDiff % item.MailSendInterval != 0)
{
continue;
}
result = Mail.SendMail(fs, item.Email, ConfigurationManager.AppSettings["MailTitle"], ConfigurationManager.AppSettings["Subject"], savePath.Split(','));
Loggers.Debug(new DebugLogInfo
{
Message = string.Format("{0} {1} (EventID:{3})报名人员信息通知邮件发送{2}", DateTime.Now.ToShortDateString(), DateTime.Now.TimeOfDay, result ? "成功" : "失败", item.EventID)
});
}
}
}
#endregion
}
}
}
}
catch (Exception ex)
{
Loggers.Exception(new BasicUserInfo(), ex);
}
Thread.Sleep(new TimeSpan(0, 1, 0, 0));
}
}
