/// <summary>
/// Sets the index of the status color.
/// </summary>
/// <param name="exportExcelInfo">The export excel information.</param>
private void SetStatusColorIndex(ref ExportExcelInfo exportExcelInfo)
{
var closedBackgroundColor = Color.FromArgb(221, 221, 221);
NpoiExcelExtensions.SetColorAtIndex(exportExcelInfo, closedBackgroundColor, _closedBackgroundColorIndex);
var checkedBackgroundColor = Color.FromArgb(242, 242, 242);
NpoiExcelExtensions.SetColorAtIndex(exportExcelInfo, checkedBackgroundColor, _checkedBackgroundColorIndex);
var pendingBackgroundColor = Color.FromArgb(240, 237, 196);
NpoiExcelExtensions.SetColorAtIndex(
exportExcelInfo, pendingBackgroundColor, _pendingBackgroundColorIndex);
}
private void ExportDynamicReportBody(
ref ExportExcelInfo exportExcelInfo,
GetDynamicReportCriteria criteria,
DynamicReportInfo dynamicReport)
{
if (dynamicReport == null || dynamicReport.ChildReports.Count == 0)
{
exportExcelInfo.Worksheet = exportExcelInfo.ExcelFile.CreateSheet(Labels.DynamicReport_SheetName);
ExportDynamicReportHeader(ref exportExcelInfo, criteria, dynamicReport);
NpoiExcelExtensions.RenderNoInfoMessage(exportExcelInfo, exportExcelInfo.CurrentRow);
return;
}
var periodGroupBackgroundColor = Color.FromArgb(215, 215, 215);
var periodGroupBackgroundColorIndex = (short)HSSFColor.DarkYellow.Index;
NpoiExcelExtensions.SetColorAtIndex(exportExcelInfo, periodGroupBackgroundColor, periodGroupBackgroundColorIndex);
ICellStyle periodGroupTitleStyle = NpoiExcelExtensions.GetTextStyle(
exportExcelInfo.ExcelFile,
periodGroupBackgroundColorIndex,
true,
CRMHorizontalAlignment.Left);
var font = exportExcelInfo.ExcelFile.CreateFont();
font.FontHeightInPoints = NpoiExcelExtensions.FontSize;
font.FontName = NpoiExcelExtensions.FontName;
font.Color = (short)HSSFColor.Blue.Index;
font.Boldweight = (short)FontBoldWeight.Bold;
periodGroupTitleStyle.SetFont(font);
NpoiExcelExtensions.SetBorderToCell(periodGroupTitleStyle);
CellRangeAddress cellRangeAddress;
var totalItems = dynamicReport.TotalChildReports;
var normalRowWriter = new CommunicationLogRowExcelWriter(
exportExcelInfo,
NpoiExcelExtensions.BackgroundColorIndex);
var closedRowWriter = new CommunicationLogRowExcelWriter(
exportExcelInfo,
_closedBackgroundColorIndex);
var checkedRowWriter = new CommunicationLogRowExcelWriter(
exportExcelInfo,
_checkedBackgroundColorIndex);
var pendingRowWriter = new CommunicationLogRowExcelWriter(
exportExcelInfo,
_pendingBackgroundColorIndex);
var maxRowPerSheet = NpoiExcelExtensions.MaxRowPerSheet;
var numberOfSheet = (totalItems / maxRowPerSheet) +
(totalItems % maxRowPerSheet != 0 ? 1 : 0);
var mainCategoryCount = dynamicReport.ChildReports.Count;
var currentIndexOfMainCategory = 0;
var currentIndexOfDetailItem = 0;
var startContentIndex = 4;
var actualMaxRowPerSheet = (maxRowPerSheet * 2) + startContentIndex;
for (var sheetIndex = 0; sheetIndex < numberOfSheet; sheetIndex++)
{
var sheetName = numberOfSheet > 1 ?
string.Format("{0} - {1}", Labels.DynamicReport_SheetName, sheetIndex + 1) :
Labels.DynamicReport_SheetName;
exportExcelInfo.Worksheet = exportExcelInfo.ExcelFile.CreateSheet(sheetName);
exportExcelInfo.CurrentRow = 0;
ExportDynamicReportHeader(ref exportExcelInfo, criteria, dynamicReport);
for (var i = currentIndexOfMainCategory; i < mainCategoryCount && exportExcelInfo.CurrentRow < actualMaxRowPerSheet; i++)
{
var periodReport = dynamicReport.ChildReports[i];
var childsLength = periodReport.ChildReports.Count;
if (exportExcelInfo.CurrentRow > startContentIndex)
{
currentIndexOfDetailItem = 0;
}
exportExcelInfo.Worksheet.CreateRow(exportExcelInfo.CurrentRow);
exportExcelInfo.CurrentColumn = exportExcelInfo.StartColumn;
cellRangeAddress = new CellRangeAddress(
exportExcelInfo.CurrentRow,
exportExcelInfo.CurrentRow + 1,
exportExcelInfo.StartColumn,
exportExcelInfo.MaxColumnIndex);
NpoiExcelExtensions.RenderMergedCells(
exportExcelInfo,
cellRangeAddress,
string.Format("{0} [{1}]", periodReport.Name, childsLength),
periodGroupTitleStyle);
exportExcelInfo.CurrentRow += 2;
for (var j = currentIndexOfDetailItem; j < childsLength && exportExcelInfo.CurrentRow < actualMaxRowPerSheet; j++)
{
exportExcelInfo.Worksheet.CreateRow(exportExcelInfo.CurrentRow);
exportExcelInfo.CurrentColumn = exportExcelInfo.StartColumn;
var info = periodReport.ChildReports[j];
if (string.Equals(info.StatusName, Labels.Closed, StringComparison.OrdinalIgnoreCase))
{
closedRowWriter.WriteRow(exportExcelInfo, info, j);
}
else if (string.Equals(info.StatusName, Labels.Checked, StringComparison.OrdinalIgnoreCase) ||
string.Equals(info.StatusName, Labels.Solved, StringComparison.OrdinalIgnoreCase))
{
checkedRowWriter.WriteRow(exportExcelInfo, info, j);
}
else if (string.Equals(info.StatusName, Labels.Pending, StringComparison.OrdinalIgnoreCase))
{
pendingRowWriter.WriteRow(exportExcelInfo, info, j);
}
else
{
normalRowWriter.WriteRow(exportExcelInfo, info, j);
}
currentIndexOfDetailItem++;
}
if (currentIndexOfDetailItem == childsLength)
{
currentIndexOfMainCategory++;
}
}
}
}
public void ExportDynamicReport(GetDynamicReportCriteria criteria)
{
string reportName = string.Format(
Labels.DynamicReport_ExcelTitle,
SettingService.GetPeriodTypeName(criteria.ReportType),
criteria.FromDate.ToShortFormatedDateWithSlash().Replace("/", "_"),
criteria.ToDate.ToShortFormatedDateWithSlash().Replace("/", "_"));
criteria.IsGetFullReport = true;
var dynamicReport = GetDynamicReport(criteria);
var exportExcelInfo = new ExportExcelInfo
{
ExcelFile = new HSSFWorkbook(),
StartRow = 0,
StartColumn = 0,
CurrentRow = 0,
MaxColumnIndex = 9
};
SetStatusColorIndex(ref exportExcelInfo);
ExportDynamicReportBody(ref exportExcelInfo, criteria, dynamicReport);
NpoiExcelExtensions.ResponseStreamExcel(exportExcelInfo.ExcelFile, reportName);
}
private void ExportDynamicReportHeader(
ref ExportExcelInfo exportExcelInfo,
GetDynamicReportCriteria criteria,
DynamicReportInfo dynamicReport)
{
NpoiExcelExtensions.SetHeaderColorIndex(exportExcelInfo);
ICellStyle headerStyle = NpoiExcelExtensions.GetHeaderStyle(
exportExcelInfo.ExcelFile,
new ExcelStyleParam());
var currentColumn = exportExcelInfo.StartColumn;
exportExcelInfo.Worksheet.SetColumnWidth(currentColumn++, 80 * NpoiExcelExtensions.FontWidth);
exportExcelInfo.Worksheet.SetColumnWidth(currentColumn++, 100 * NpoiExcelExtensions.FontWidth);
exportExcelInfo.Worksheet.SetColumnWidth(currentColumn++, 120 * NpoiExcelExtensions.FontWidth);
exportExcelInfo.Worksheet.SetColumnWidth(currentColumn++, 300 * NpoiExcelExtensions.FontWidth);
exportExcelInfo.Worksheet.SetColumnWidth(currentColumn++, 160 * NpoiExcelExtensions.FontWidth);
exportExcelInfo.Worksheet.SetColumnWidth(currentColumn++, 100 * NpoiExcelExtensions.FontWidth);
exportExcelInfo.Worksheet.SetColumnWidth(currentColumn++, 80 * NpoiExcelExtensions.FontWidth);
exportExcelInfo.Worksheet.SetColumnWidth(currentColumn++, 140 * NpoiExcelExtensions.FontWidth);
exportExcelInfo.Worksheet.SetColumnWidth(currentColumn++, 140 * NpoiExcelExtensions.FontWidth);
exportExcelInfo.Worksheet.SetColumnWidth(currentColumn++, 140 * NpoiExcelExtensions.FontWidth);
string reportTitle = string.Format(
Labels.DynamicReport_TitleWithNumberOfLog,
SettingService.GetPeriodTypeName(criteria.ReportType),
DateTimeUtilize.ChangeTimeZoneToClientDateTime(criteria.FromDate).ToShortFormatedDate(),
DateTimeUtilize.ChangeTimeZoneToClientDateTime(criteria.ToDate).ToShortFormatedDate(),
dynamicReport != null ? "[" + dynamicReport.TotalChildReports + "]" : string.Empty);
CellRangeAddress cellRangeAddress;
cellRangeAddress = new CellRangeAddress(
exportExcelInfo.CurrentRow,
exportExcelInfo.CurrentRow + 1,
exportExcelInfo.StartColumn,
exportExcelInfo.MaxColumnIndex);
NpoiExcelExtensions.RenderMergedCells(exportExcelInfo, cellRangeAddress, reportTitle, headerStyle);
exportExcelInfo.CurrentRow += 2;
NpoiExcelExtensions.RenderEmptyRow(exportExcelInfo, exportExcelInfo.CurrentRow);
exportExcelInfo.CurrentRow++;
IRow row = exportExcelInfo.Worksheet.CreateRow(exportExcelInfo.CurrentRow);
currentColumn = exportExcelInfo.StartColumn;
NpoiExcelExtensions.RenderCell(row, currentColumn, Labels.Symbol_No, headerStyle);
currentColumn++;
NpoiExcelExtensions.RenderCell(row, currentColumn, Labels.Running_No, headerStyle);
currentColumn++;
NpoiExcelExtensions.RenderCell(row, currentColumn, Labels.Feedback_Date, headerStyle);
currentColumn++;
NpoiExcelExtensions.RenderCell(
row,
currentColumn,
string.Format("{0}/{1}", Labels.Main_Category, Labels.Sub_Category),
headerStyle);
currentColumn++;
NpoiExcelExtensions.RenderCell(row, currentColumn, Labels.Subject, headerStyle);
currentColumn++;
NpoiExcelExtensions.RenderCell(row, currentColumn, Labels.Status, headerStyle);
currentColumn++;
NpoiExcelExtensions.RenderCell(row, currentColumn, Labels.Duration, headerStyle);
currentColumn++;
NpoiExcelExtensions.RenderCell(row, currentColumn, Labels.Username, headerStyle);
currentColumn++;
NpoiExcelExtensions.RenderCell(row, currentColumn, Labels.Handler, headerStyle);
currentColumn++;
NpoiExcelExtensions.RenderCell(row, currentColumn, Labels.Reviewer, headerStyle);
currentColumn++;
exportExcelInfo.CurrentRow++;
}
public void Export(int pageIndex = 1, string keyword = "", int ptId = 0, bool isPaging = true)
{
try
{
var logs = from l in db.ES_ExamLog
where l.IsDel == false
join u in db.ES_User on l.UserId equals u.UserId
join pt in db.ES_ExamPart on l.EmPtId equals pt.EmPtId
join pr in db.ES_ExamPaper on l.EmPaperId equals pr.EmPaperId
where pt.IsDel == false && pr.IsDel == false && u.IsDel == false
select new
{
u.UserId,
u.UserName,
l.LogId,
l.IsSubmit,
l.ExercisesId,
l.Answers,
l.ExamScore,
l.EmPtId,
l.EmPaperId,
pt.EmPtStart,
pt.EmPtEnd,
pr.EmPaperName
};
if (ptId != null && ptId != 0)
{
logs = logs.Where(l => l.EmPtId == ptId);
}
if (keyword != "")
{
logs = logs.Where(l => l.UserName.Contains(keyword));
}
int totalCount = logs.Count();
if (isPaging)
{
logs = logs.OrderBy(l => l.LogId).Skip((pageIndex - 1) * 10).Take(10);
}
List <ExportExcelInfo> exportExcelList = new List <ExportExcelInfo>();
foreach (var log in logs)
{
int userId = log.UserId;
string userName = log.UserName;
int logId = log.LogId;
string isSubmit = log.IsSubmit == false ? "否" : "是";
string exercisesId = log.ExercisesId;
string answers = log.Answers;
double score = log.ExamScore;
int partId = Convert.ToInt32(log.EmPtId);
int paperId = Convert.ToInt32(log.EmPaperId);
string tempDate = log.EmPtStart.GetDateTimeFormats('o')[0];
string date = tempDate.Substring(0, tempDate.IndexOf('T')); // 将日期转为2020-05-06
string start = log.EmPtStart.ToString("T"); // 开始时间
string end = log.EmPtEnd.ToString("T"); // 结束时间
string title = start.Substring(0, start.Length - 3) + "到" + end.Substring(0, end.Length - 3) + "场";
string examTitle = log.EmPaperName;
ExportExcelInfo exportExcel = new ExportExcelInfo()
{
UserName = userName,
LogId = logId,
IsSubmit = log.IsSubmit,
Part = title,
Title = examTitle,
Score = score,
Date = date
};
exportExcelList.Add(exportExcel);
}
MemoryStream ms = ExcelHelper.ExportExcel(exportExcelList);
//asp.net输出的Excel文件名
//如果文件名是中文的话，需要进行编码转换，否则浏览器看到的下载文件是乱码。
string fileName = HttpUtility.UrlEncode($"{TimeHelper.GetTimeStamp()}.xls");
Response.ContentType = "application/vnd.ms-excel";
//Response.ContentType = "application/download"; //也可以设置成download
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", fileName));
Response.Buffer = true;
Response.Clear();
Response.BinaryWrite(ms.GetBuffer());
Response.End();
}
catch (Exception ex)
{
Response.Write("ERROR" + ex.Message);
}
}
