/// <summary>
/// 取得起訖時間
/// </summary>
/// <param name="now"></param>
/// <returns></returns>
private (DateTime startDateTime, DateTime endDateTime) GetSpan(DateTime now, ReportEnum report)
{
this._logger.LogInformation("report:{0}", report.ToString());
DateTime start = DateTime.MinValue;
DateTime end = DateTime.MinValue;
if (report == ReportEnum.Weekly)
{
//// 星期一
var week = (int)now.DayOfWeek;
end = now.AddDays(-week);
start = end.AddDays(-7);
end = new DateTime(end.Year, end.Month, end.Day, 23, 59, 59);
}
else if (report == ReportEnum.Monthly)
{
//// 每月 1號
start = now.AddMonths(-1);
start = new DateTime(start.Year, start.Month, 1);
var endDay = DateTime.DaysInMonth(start.Year, start.Month);
end = new DateTime(start.Year, start.Month, endDay, 23, 59, 59);
}
else if (report == ReportEnum.Quarterly)
{
//// 1, 4, 7, 10月 1號
var mod = now.Month % 3;
mod = (mod == 0) ? 3 : mod;
end = now.AddMonths(-mod);
start = end.AddMonths(-2);
start = new DateTime(start.Year, start.Month, 1);
var endDay = DateTime.DaysInMonth(end.Year, end.Month);
end = new DateTime(end.Year, end.Month, endDay, 23, 59, 59);
}
else if (report == ReportEnum.Yearly)
{
//// 1月 1號
start = now.AddYears(-1);
start = new DateTime(start.Year, 1, 1);
end = new DateTime(start.Year, 12, 31, 23, 59, 59);
}
this._logger.LogInformation("Time: {0} ~ {1}", report, start, end);
return(startDateTime : start, endDateTime : end);
}
/// <summary>
/// 熱門排行
/// </summary>
public void HotFeed(ReportEnum report)
{
var title = "上週";
var span = this.GetSpan(DateTime.Now, report);
var feedList = this._feedRepository.GetFeedListWithFeedLike(span.startDateTime, span.endDateTime)
.OrderByDescending(i => i.FeedLike)
.Take(3);
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendLine($"{title} TOP *{feedList.Count()}*");
foreach (var i in feedList)
{
stringBuilder.AppendLine($"{i.FeedTitle}, {i.FeedCreatedUser} {i.FeedCreatedDateTime.ToString("yyyy/MM/dd HH:mm")}");
}
this._notifyService.Send(stringBuilder.ToString());
}
public static async Task SendReport(string msg, ReportEnum type)
{
var embedBuilder = new EmbedBuilder()
.WithTitle(type.ToString())
.WithDescription(msg);
var log = Client.GetChannel(logChannel) as SocketTextChannel;
switch (type)
{
case ReportEnum.ERROR:
embedBuilder.WithColor(Color.Orange);
break;
case ReportEnum.GUILD_JOIN:
embedBuilder.WithColor(Color.Green);
break;
case ReportEnum.GUILD_LEFT:
embedBuilder.WithColor(Color.Red);
break;
case ReportEnum.KONNONEWS:
embedBuilder.WithColor(Color.Blue);
break;
case ReportEnum.USER_BANNED_FROM_GUILD:
embedBuilder.WithColor(Color.Red);
break;
case ReportEnum.LOG:
embedBuilder.WithColor(Color.DarkGreen);
break;
}
var embed = embedBuilder.Build();
Console.WriteLine(msg);
await log.SendMessageAsync(embed : embed).ConfigureAwait(false);
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream Export(DataTable dtSource, string strHeaderText, List <ReportParametersDto> paramList, ReportEnum reportEnum)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
#region 取得每列的列宽（最大宽度）
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
//GBK对应的code page是CP936
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
#endregion
int rowIndex = 0;
ICellStyle contentStyle = workbook.CreateCellStyle();
contentStyle.Alignment = HorizontalAlignment.Left;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);//第一列表头名称
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
if (reportEnum == ReportEnum.门诊居民月统计 && paramList.Any())
{
var hospilalName = paramList.FirstOrDefault(c => c.Key == "医院");
var timeMonth = paramList.FirstOrDefault(c => c.Key == "汇总月份");
IRow headerRowNew = sheet.CreateRow(1);
headerRowNew.CreateCell(0).SetCellValue("医院:" + hospilalName.Value);
headerRowNew.CreateCell(2).SetCellValue("汇总月份:" + timeMonth.Value);
headerRowNew.CreateCell(3).SetCellValue("单位：元");
ICellStyle headStyleNew = workbook.CreateCellStyle();
headStyleNew.Alignment = HorizontalAlignment.Left;
IFont fontNew = workbook.CreateFont();
fontNew.FontHeightInPoints = 10;
fontNew.Boldweight = 700;
headStyleNew.SetFont(fontNew);
headerRowNew.GetCell(0).CellStyle = headStyleNew;
headerRowNew.GetCell(2).CellStyle = headStyleNew;
headerRowNew.GetCell(3).CellStyle = headStyleNew;
sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, 1));
}
}
#endregion
#region 列头及样式
{
int rowIndexNew = 1;
if (reportEnum == ReportEnum.门诊居民月统计)
{
rowIndexNew = 2;
}
//也可自定义标题名称，填写到 headerRow.CreateCell(1).SetCellValue();需使用directory<string,string>先填写标题，然后遍历操作中即可
IRow headerRow = sheet.CreateRow(rowIndexNew);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
}
#endregion
rowIndex = reportEnum == ReportEnum.门诊居民月统计 ? 3 : 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
newCell.CellStyle = contentStyle;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
/// <summary>
/// DataTable导出到Excel文件
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="strFileName">保存位置</param>
/// <param name="paramList">参数列表</param>
/// <param name="reportEnum">报表类型</param>
public static void Export(DataTable dtSource, string strHeaderText, string strFileName, List <ReportParametersDto> paramList, ReportEnum reportEnum)
{
using (MemoryStream ms = Export(dtSource, strHeaderText, paramList, reportEnum))
{
using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
{
//数据填写
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
}
}
public ActionResult ManagerReport(ReportEnum RE = ReportEnum.Flow)
{
ViewBag.RE = RE;
return(View());
}
