public FileDto ExportToFile(List <GetTimeProfileForViewDto> timeProfiles)
{
return(CreateExcelPackage(
"TimeProfiles.xlsx",
excelPackage =>
{
var sheet = excelPackage.Workbook.Worksheets.Add(L("TimeProfiles"));
sheet.OutLineApplyStyle = true;
AddHeader(
sheet,
L("DescriptionAr"),
L("DescriptionEn"),
L("StartDate"),
L("EndDate"),
(L("User")) + L("Name")
);
AddObjects(
sheet, 2, timeProfiles,
_ => _.TimeProfile.DescriptionAr,
_ => _.TimeProfile.DescriptionEn,
_ => _timeZoneConverter.Convert(_.TimeProfile.StartDate, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _timeZoneConverter.Convert(_.TimeProfile.EndDate, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _.UserName
);
var startDateColumn = sheet.Column(3);
startDateColumn.Style.Numberformat.Format = "yyyy-mm-dd";
startDateColumn.AutoFit();
var endDateColumn = sheet.Column(4);
endDateColumn.Style.Numberformat.Format = "yyyy-mm-dd";
endDateColumn.AutoFit();
}));
}
public FileDto ExportToFile(List <GetEmployeeTempTransferForViewDto> employeeTempTransfers)
{
return(CreateExcelPackage(
"EmployeeTempTransfers.xlsx",
excelPackage =>
{
var sheet = excelPackage.Workbook.Worksheets.Add(L("EmployeeTempTransfers"));
sheet.OutLineApplyStyle = true;
AddHeader(
sheet,
L("FromDate"),
L("ToDate"),
(L("OrganizationUnit")) + L("DisplayName"),
(L("User")) + L("Name")
);
AddObjects(
sheet, 2, employeeTempTransfers,
_ => _timeZoneConverter.Convert(_.EmployeeTempTransfer.FromDate, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _timeZoneConverter.Convert(_.EmployeeTempTransfer.ToDate, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _.OrganizationUnitDisplayName,
_ => _.UserName
);
var fromDateColumn = sheet.Column(1);
fromDateColumn.Style.Numberformat.Format = "yyyy-mm-dd";
fromDateColumn.AutoFit();
var toDateColumn = sheet.Column(2);
toDateColumn.Style.Numberformat.Format = "yyyy-mm-dd";
toDateColumn.AutoFit();
}));
}
public FileDto ExportToFile(List <GetUserDelegationForViewDto> userDelegations)
{
return(CreateExcelPackage(
"UserDelegations.xlsx",
excelPackage =>
{
var sheet = excelPackage.Workbook.Worksheets.Add(L("UserDelegations"));
sheet.OutLineApplyStyle = true;
AddHeader(
sheet,
L("FromDate"),
L("ToDate"),
(L("User")) + L("Name"),
(L("User")) + L("Name")
);
AddObjects(
sheet, 2, userDelegations,
_ => _timeZoneConverter.Convert(_.UserDelegation.FromDate, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _timeZoneConverter.Convert(_.UserDelegation.ToDate, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _.UserName,
_ => _.UserName2
);
var fromDateColumn = sheet.Column(1);
fromDateColumn.Style.Numberformat.Format = "yyyy-mm-dd";
fromDateColumn.AutoFit();
var toDateColumn = sheet.Column(2);
toDateColumn.Style.Numberformat.Format = "yyyy-mm-dd";
toDateColumn.AutoFit();
}));
}
public FileDto ExportToFile(List <GetHolidayForViewDto> holidays)
{
return(CreateExcelPackage(
"holidays.xlsx",
excelPackage =>
{
var sheet = excelPackage.Workbook.Worksheets.Add(L("Holidays"));
sheet.OutLineApplyStyle = true;
AddHeader(
sheet,
L("NameAr"),
L("NameEn"),
L("StartDate"),
L("EndDate")
);
AddObjects(
sheet, 2, holidays,
_ => _.Holiday.NameAr,
_ => _.Holiday.NameEn,
_ => _timeZoneConverter.Convert(_.Holiday.StartDate, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _timeZoneConverter.Convert(_.Holiday.EndDate, _abpSession.TenantId, _abpSession.GetUserId())
);
var startDateColumn = sheet.Column(3);
startDateColumn.Style.Numberformat.Format = "yyyy-mm-dd";
startDateColumn.AutoFit();
var endDateColumn = sheet.Column(4);
endDateColumn.Style.Numberformat.Format = "yyyy-mm-dd";
endDateColumn.AutoFit();
}));
}
public FileDto ExportToFile(List <GetAssetOwnershipForViewDto> assetOwnerships)
{
return(CreateExcelPackage(
"AssetOwnerships.xlsx",
excelPackage =>
{
var sheet = excelPackage.Workbook.Worksheets.Add(L("AssetOwnerships"));
sheet.OutLineApplyStyle = true;
AddHeader(
sheet,
L("StartDate"),
L("FinishDate"),
L("PercentageOwnership"),
(L("Asset")) + L("Reference"),
(L("AssetOwner")) + L("Name")
);
AddObjects(
sheet, 2, assetOwnerships,
_ => _timeZoneConverter.Convert(_.AssetOwnership.StartDate, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _timeZoneConverter.Convert(_.AssetOwnership.FinishDate, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _.AssetOwnership.PercentageOwnership,
_ => _.AssetReference,
_ => _.AssetOwnerName
);
var startDateColumn = sheet.Column(1);
startDateColumn.Style.Numberformat.Format = "yyyy-mm-dd";
startDateColumn.AutoFit();
var finishDateColumn = sheet.Column(2);
finishDateColumn.Style.Numberformat.Format = "yyyy-mm-dd";
finishDateColumn.AutoFit();
}));
}
public FileDto ExportToFile(List <GetCustomerGroupMembershipForViewDto> customerGroupMemberships)
{
return(CreateExcelPackage(
"CustomerGroupMemberships.xlsx",
excelPackage =>
{
var sheet = excelPackage.Workbook.Worksheets.Add(L("CustomerGroupMemberships"));
sheet.OutLineApplyStyle = true;
AddHeader(
sheet,
L("DateJoined"),
L("DateLeft"),
(L("CustomerGroup")) + L("Name"),
(L("Customer")) + L("Name")
);
AddObjects(
sheet, 2, customerGroupMemberships,
_ => _timeZoneConverter.Convert(_.CustomerGroupMembership.DateJoined, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _timeZoneConverter.Convert(_.CustomerGroupMembership.DateLeft, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _.CustomerGroupName,
_ => _.CustomerName
);
var dateJoinedColumn = sheet.Column(1);
dateJoinedColumn.Style.Numberformat.Format = "yyyy-mm-dd";
dateJoinedColumn.AutoFit();
var dateLeftColumn = sheet.Column(2);
dateLeftColumn.Style.Numberformat.Format = "yyyy-mm-dd";
dateLeftColumn.AutoFit();
}));
}
public FileDto ExportToFile(List <GetRamadanDateForViewDto> ramadanDates)
{
return(CreateExcelPackage(
"RamadanDates.xlsx",
excelPackage =>
{
var sheet = excelPackage.Workbook.Worksheets.Add(L("RamadanDates"));
sheet.OutLineApplyStyle = true;
AddHeader(
sheet,
L("Year"),
L("FromDate"),
L("ToDate")
);
AddObjects(
sheet, 2, ramadanDates,
_ => _.RamadanDate.Year,
_ => _timeZoneConverter.Convert(_.RamadanDate.FromDate, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _timeZoneConverter.Convert(_.RamadanDate.ToDate, _abpSession.TenantId, _abpSession.GetUserId())
);
var fromDateColumn = sheet.Column(2);
fromDateColumn.Style.Numberformat.Format = "yyyy-mm-dd";
fromDateColumn.AutoFit();
var toDateColumn = sheet.Column(3);
toDateColumn.Style.Numberformat.Format = "yyyy-mm-dd";
toDateColumn.AutoFit();
}));
}
public FileDto ExportToFile(List <GetWorkOrderForViewDto> workOrders)
{
return(CreateExcelPackage(
"WorkOrders.xlsx",
excelPackage =>
{
var sheet = excelPackage.Workbook.Worksheets.Add(L("WorkOrders"));
sheet.OutLineApplyStyle = true;
AddHeader(
sheet,
L("Loc8GUID"),
L("Subject"),
L("Description"),
L("Location"),
L("StartDate"),
L("EndDate"),
L("Remarks"),
L("Attachments"),
(L("WorkOrderPriority")) + L("Priority"),
(L("WorkOrderType")) + L("Type"),
(L("Vendor")) + L("Name"),
(L("Incident")) + L("Description"),
(L("SupportItem")) + L("Description"),
(L("User")) + L("Name"),
(L("Customer")) + L("Name"),
(L("AssetOwnership")) + L("AssetFk"),
(L("WorkOrderStatus")) + L("Status")
);
AddObjects(
sheet, 2, workOrders,
_ => _.WorkOrder.Loc8GUID,
_ => _.WorkOrder.Subject,
_ => _.WorkOrder.Description,
_ => _.WorkOrder.Location,
_ => _timeZoneConverter.Convert(_.WorkOrder.StartDate, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _timeZoneConverter.Convert(_.WorkOrder.EndDate, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _.WorkOrder.Remarks,
_ => _.WorkOrder.Attachments,
_ => _.WorkOrderPriorityPriority,
_ => _.WorkOrderTypeType,
_ => _.VendorName,
_ => _.IncidentDescription,
_ => _.SupportItemDescription,
_ => _.UserName,
_ => _.CustomerName,
_ => _.AssetOwnershipAssetDisplayName,
_ => _.WorkOrderStatusStatus
);
var startDateColumn = sheet.Column(5);
startDateColumn.Style.Numberformat.Format = "yyyy-mm-dd";
startDateColumn.AutoFit();
var endDateColumn = sheet.Column(6);
endDateColumn.Style.Numberformat.Format = "yyyy-mm-dd";
endDateColumn.AutoFit();
}));
}
public FileDto ExportToFile(List <GetMenuForViewDto> menus)
{
return(CreateExcelPackage(
"Menus.xlsx",
excelPackage =>
{
var sheet = excelPackage.Workbook.Worksheets.Add(L("Menus"));
sheet.OutLineApplyStyle = true;
AddHeader(
sheet,
L("Name"),
L("Title"),
L("Icon"),
L("Description"),
L("Parent"),
//L("IsParent"),
L("Link"),
//L("Type"),
L("CreationTime"),
L("LastModificationTime"),
L("IsDeleted"),
L("DeletionTime"),
L("Index"),
//L("IsDelimiter"),
L("RequiredPermissionName")
);
AddObjects(
sheet, 2, menus,
_ => _.Menu.Name,
_ => _.Menu.Title,
_ => _.Menu.Icon,
_ => _.Menu.Description,
_ => _.Menu.Parent,
//_ => _.Menu.IsParent,
_ => _.Menu.Link,
//_ => _.Menu.Type,
_ => _timeZoneConverter.Convert(_.Menu.CreationTime, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _timeZoneConverter.Convert(_.Menu.LastModificationTime, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _.Menu.IsDeleted,
_ => _timeZoneConverter.Convert(_.Menu.DeletionTime, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _.Menu.Index,
//_ => _.Menu.IsDelimiter,
_ => _.Menu.RequiredPermissionName
);
var creationTimeColumn = sheet.Column(9);
creationTimeColumn.Style.Numberformat.Format = "yyyy-mm-dd";
creationTimeColumn.AutoFit();
var lastModificationTimeColumn = sheet.Column(10);
lastModificationTimeColumn.Style.Numberformat.Format = "yyyy-mm-dd";
lastModificationTimeColumn.AutoFit();
var deletionTimeColumn = sheet.Column(12);
deletionTimeColumn.Style.Numberformat.Format = "yyyy-mm-dd";
deletionTimeColumn.AutoFit();
}));
}
public FileDto ExportToFile(List <GetQuanLyNghiPhepForViewDto> quanLyNghiPheps)
{
return(CreateExcelPackage(
"QuanLyNghiPheps.xlsx",
excelPackage =>
{
var sheet = excelPackage.Workbook.Worksheets.Add(L("QuanLyNghiPheps"));
sheet.OutLineApplyStyle = true;
AddHeader(
sheet,
L("TenNhanVien"),
L("DonViCongTacID"),
L("MaNV"),
L("NghiPhep"),
L("CongTac"),
L("NgayBatDau"),
L("NgayKetThuc"),
L("LyDo"),
L("QuanLyTrucTiepID"),
L("TruongBoPhanID"),
L("TepDinhKem"),
L("TenCTY"),
L("NguoiDuyetID"),
L("NgayDuyet"),
L("TrangThai")
);
AddObjects(
sheet, 2, quanLyNghiPheps,
_ => _.QuanLyNghiPhep.TenNhanVien,
_ => _.QuanLyNghiPhep.DonViCongTacID,
_ => _.QuanLyNghiPhep.MaNV,
_ => _.QuanLyNghiPhep.NghiPhep,
_ => _.QuanLyNghiPhep.CongTac,
_ => _timeZoneConverter.Convert(_.QuanLyNghiPhep.NgayBatDau, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _timeZoneConverter.Convert(_.QuanLyNghiPhep.NgayKetThuc, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _.QuanLyNghiPhep.LyDo,
_ => _.QuanLyNghiPhep.QuanLyTrucTiepID,
_ => _.QuanLyNghiPhep.TruongBoPhanID,
_ => _.QuanLyNghiPhep.TepDinhKem,
_ => _.QuanLyNghiPhep.TenCTY,
_ => _.QuanLyNghiPhep.NguoiDuyetID,
_ => _timeZoneConverter.Convert(_.QuanLyNghiPhep.NgayDuyet, _abpSession.TenantId, _abpSession.GetUserId())
);
var ngayBatDauColumn = sheet.Column(6);
ngayBatDauColumn.Style.Numberformat.Format = "yyyy-mm-dd";
ngayBatDauColumn.AutoFit();
var ngayKetThucColumn = sheet.Column(7);
ngayKetThucColumn.Style.Numberformat.Format = "yyyy-mm-dd";
ngayKetThucColumn.AutoFit();
var ngayDuyetColumn = sheet.Column(14);
ngayDuyetColumn.Style.Numberformat.Format = "yyyy-mm-dd";
ngayDuyetColumn.AutoFit();
}));
}
public FileDto ExportToFile(List <GetCustomerInvoiceForViewDto> customerInvoices)
{
return(CreateExcelPackage(
"CustomerInvoices.xlsx",
excelPackage =>
{
var sheet = excelPackage.Workbook.Worksheets.Add(L("CustomerInvoices"));
sheet.OutLineApplyStyle = true;
AddHeader(
sheet,
L("CustomerReference"),
L("Description"),
L("DateIssued"),
L("DateDue"),
L("TotalTax"),
L("TotalPrice"),
L("TotalNet"),
L("TotalDiscount"),
L("TotalCharge"),
L("InvoiceRecipient"),
L("Remarks"),
(L("Customer")) + L("Name"),
(L("Currency")) + L("Code"),
(L("BillingRule")) + L("Name"),
(L("BillingEvent")) + L("Purpose"),
(L("InvoiceStatus")) + L("Status")
);
AddObjects(
sheet, 2, customerInvoices,
_ => _.CustomerInvoice.CustomerReference,
_ => _.CustomerInvoice.Description,
_ => _timeZoneConverter.Convert(_.CustomerInvoice.DateIssued, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _timeZoneConverter.Convert(_.CustomerInvoice.DateDue, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _.CustomerInvoice.TotalTax,
_ => _.CustomerInvoice.TotalPrice,
_ => _.CustomerInvoice.TotalNet,
_ => _.CustomerInvoice.TotalDiscount,
_ => _.CustomerInvoice.TotalCharge,
_ => _.CustomerInvoice.InvoiceRecipient,
_ => _.CustomerInvoice.Remarks,
_ => _.CustomerName,
_ => _.CurrencyCode,
_ => _.BillingRuleName,
_ => _.BillingEventPurpose,
_ => _.InvoiceStatusStatus
);
var dateIssuedColumn = sheet.Column(3);
dateIssuedColumn.Style.Numberformat.Format = "yyyy-mm-dd";
dateIssuedColumn.AutoFit();
var dateDueColumn = sheet.Column(4);
dateDueColumn.Style.Numberformat.Format = "yyyy-mm-dd";
dateDueColumn.AutoFit();
}));
}
public FileDto ExportToFile(List <GetLeaseItemForViewDto> leaseItems)
{
return(CreateExcelPackage(
"LeaseItems.xlsx",
excelPackage =>
{
var sheet = excelPackage.Workbook.Worksheets.Add(L("LeaseItems"));
sheet.OutLineApplyStyle = true;
AddHeader(
sheet,
L("DateAllocated"),
L("AllocationPercentage"),
L("Terms"),
L("UnitRentalRate"),
L("UnitDepositRate"),
L("StartDate"),
L("EndDate"),
L("Attachments"),
L("RentalUomRefId"),
L("DepositUomRefId"),
L("Item"),
L("Description"),
(L("AssetClass")) + L("Class"),
(L("Asset")) + L("Reference"),
(L("LeaseAgreement")) + L("Title")
);
AddObjects(
sheet, 2, leaseItems,
_ => _timeZoneConverter.Convert(_.LeaseItem.DateAllocated, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _.LeaseItem.AllocationPercentage,
_ => _.LeaseItem.Terms,
_ => _.LeaseItem.UnitRentalRate,
_ => _.LeaseItem.UnitDepositRate,
_ => _timeZoneConverter.Convert(_.LeaseItem.StartDate, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _timeZoneConverter.Convert(_.LeaseItem.EndDate, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _.LeaseItem.RentalUomRefId,
_ => _.LeaseItem.DepositUomRefId,
_ => _.LeaseItem.Item,
_ => _.LeaseItem.Description,
_ => _.AssetClassClass,
_ => _.AssetReference,
_ => _.LeaseAgreementTitle
);
var dateAllocatedColumn = sheet.Column(1);
dateAllocatedColumn.Style.Numberformat.Format = "yyyy-mm-dd";
dateAllocatedColumn.AutoFit();
var startDateColumn = sheet.Column(6);
startDateColumn.Style.Numberformat.Format = "yyyy-mm-dd";
startDateColumn.AutoFit();
var endDateColumn = sheet.Column(7);
endDateColumn.Style.Numberformat.Format = "yyyy-mm-dd";
endDateColumn.AutoFit();
}));
}
public FileDto ExportToFile(List <AuditLogListDto> auditLogListDtos)
{
return(CreateExcelPackage(
"AuditLogs.xlsx",
excelPackage =>
{
var sheet = excelPackage.Workbook.Worksheets.Add(L("AuditLogs"));
sheet.OutLineApplyStyle = true;
AddHeader(
sheet,
L("Time"),
L("UserName"),
L("Service"),
L("Action"),
L("Parameters"),
L("Duration"),
L("IpAddress"),
L("Client"),
L("Browser"),
L("ErrorState")
);
AddObjects(
sheet, 2, auditLogListDtos,
_ => _timeZoneConverter.Convert(_.ExecutionTime, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _.UserName,
_ => _.ServiceName,
_ => _.MethodName,
_ => _.Parameters,
_ => _.ExecutionDuration,
_ => _.ClientIpAddress,
_ => _.ClientName,
_ => _.BrowserInfo,
_ => _.Exception.IsNullOrEmpty() ? L("Success") : _.Exception
);
//Formatting cells
var timeColumn = sheet.Column(1);
timeColumn.Style.Numberformat.Format = "yyyy-mm-dd hh:mm:ss";
for (var i = 1; i <= 10; i++)
{
if (i.IsIn(5, 10)) //Don't AutoFit Parameters and Exception
{
continue;
}
sheet.Column(i).AutoFit();
}
}));
}
public FileDto ExportToFile(List <AuditLogListDto> auditLogListDtos)
{
return(CreateExcelPackage(
"AuditLogs.xlsx",
excelPackage =>
{
var sheet = excelPackage.CreateSheet(L("AuditLogs"));
AddHeader(
sheet,
L("Time"),
L("UserName"),
L("Service"),
L("Action"),
L("Parameters"),
L("Duration"),
L("IpAddress"),
L("Client"),
L("Browser"),
L("ErrorState")
);
AddObjects(
sheet, 2, auditLogListDtos,
_ => _timeZoneConverter.Convert(_.ExecutionTime, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _.UserName,
_ => _.ServiceName,
_ => _.MethodName,
_ => _.Parameters,
_ => _.ExecutionDuration,
_ => _.ClientIpAddress,
_ => _.ClientName,
_ => _.BrowserInfo,
_ => _.Exception.IsNullOrEmpty() ? L("Success") : _.Exception
);
for (var i = 1; i <= auditLogListDtos.Count; i++)
{
//Formatting cells
SetCellDataFormat(sheet.GetRow(i).Cells[0], "yyyy-mm-dd hh:mm:ss");
}
for (var i = 0; i < 10; i++)
{
if (i.IsIn(4, 9)) //Don't AutoFit Parameters and Exception
{
continue;
}
sheet.AutoSizeColumn(i);
}
}));
}
public FileDto ExportToFile(List <GetProjectForViewDto> projects)
{
return(CreateExcelPackage(
"Projects.xlsx",
excelPackage =>
{
var sheet = excelPackage.CreateSheet(L("Projects"));
AddHeader(
sheet,
L("Name"),
L("Description"),
L("StartDate"),
L("EndDate"),
L("IsApprove"),
L("IsActive"),
L("Publish"),
L("Remark"),
(L("Account")) + L("Name"),
(L("Team")) + L("Name"),
(L("SysStatus")) + L("Name")
);
AddObjects(
sheet, 2, projects,
_ => _.Project.Name,
_ => _.Project.Description,
_ => _timeZoneConverter.Convert(_.Project.StartDate, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _timeZoneConverter.Convert(_.Project.EndDate, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _.Project.IsApprove,
_ => _.Project.IsActive,
_ => _.Project.Publish,
_ => _.Project.Remark,
_ => _.AccountName,
_ => _.TeamName,
_ => _.SysStatusName
);
for (var i = 1; i <= projects.Count; i++)
{
//Formatting cells
SetCellDataFormat(sheet.GetRow(i).Cells[2], "yyyy-mm-dd");
SetCellDataFormat(sheet.GetRow(i).Cells[3], "yyyy-mm-dd");
}
for (var i = 0; i < 11; i++)
{
sheet.AutoSizeColumn(i);
}
}));
}
public FileDto ExportToFile(List <GetEmailForViewDto> emails)
{
return(CreateExcelPackage(
"Emails.xlsx",
excelPackage =>
{
var sheet = excelPackage.CreateSheet(L("Emails"));
AddHeader(
sheet,
L("ReferenceId"),
L("EmailFrom"),
L("EmailTo"),
L("EmailCC"),
L("EmailBCC"),
L("Subject"),
L("Body"),
L("RequestDate"),
L("SentDate"),
(L("SysRef")) + L("TenantId"),
(L("SysStatus")) + L("Name")
);
AddObjects(
sheet, 2, emails,
_ => _.Email.ReferenceId,
_ => _.Email.EmailFrom,
_ => _.Email.EmailTo,
_ => _.Email.EmailCC,
_ => _.Email.EmailBCC,
_ => _.Email.Subject,
_ => _.Email.Body,
_ => _timeZoneConverter.Convert(_.Email.RequestDate, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _timeZoneConverter.Convert(_.Email.SentDate, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _.SysRefTenantId,
_ => _.SysStatusName
);
for (var i = 1; i <= emails.Count; i++)
{
//Formatting cells
SetCellDataFormat(sheet.GetRow(i).Cells[7], "yyyy-mm-dd");
SetCellDataFormat(sheet.GetRow(i).Cells[8], "yyyy-mm-dd");
}
for (var i = 0; i < 11; i++)
{
sheet.AutoSizeColumn(i);
}
}));
}
public FileDto ExportToFile(List <UserListDto> userListDtos)
{
return(CreateExcelPackage(
"UserList.xlsx",
excelPackage =>
{
var sheet = excelPackage.Workbook.Worksheets.Add(L("Users"));
sheet.OutLineApplyStyle = true;
AddHeader(
sheet,
1,
L("Name"),
L("Surname"),
L("UserName"),
L("PhoneNumber"),
L("EmailAddress"),
L("EmailConfirm"),
L("Roles"),
L("LastLoginTime"),
L("Active"),
L("CreationTime")
);
AddObjects(
sheet, 2, userListDtos,
_ => _.Name,
_ => _.Surname,
_ => _.UserName,
_ => _.PhoneNumber,
_ => _.EmailAddress,
_ => _.IsEmailConfirmed,
_ => _.Roles.Select(r => r.RoleName).JoinAsString(", "),
_ => _timeZoneConverter.Convert(_.LastLoginTime, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _.IsActive,
_ => _timeZoneConverter.Convert(_.CreationTime, _abpSession.TenantId, _abpSession.GetUserId())
);
//Formatting cells
var lastLoginTimeColumn = sheet.Column(8);
lastLoginTimeColumn.Style.Numberformat.Format = "yyyy-mm-dd";
var creationTimeColumn = sheet.Column(10);
creationTimeColumn.Style.Numberformat.Format = "yyyy-mm-dd";
for (var i = 1; i <= 10; i++)
{
sheet.Column(i).AutoFit();
}
}));
}
public FileDto ExportToFile(List <GetSupportContractForViewDto> supportContracts)
{
return(CreateExcelPackage(
"SupportContracts.xlsx",
excelPackage =>
{
var sheet = excelPackage.Workbook.Worksheets.Add(L("SupportContracts"));
sheet.OutLineApplyStyle = true;
AddHeader(
sheet,
L("Title"),
L("Reference"),
L("Description"),
L("StartDate"),
L("EndDate"),
L("Attachments"),
L("IsRFQTemplate"),
L("IsAcknowledged"),
L("AcknowledgedBy"),
L("AcknowledgedAt"),
(L("Vendor")) + L("Name"),
(L("AssetOwner")) + L("Name")
);
AddObjects(
sheet, 2, supportContracts,
_ => _.SupportContract.Title,
_ => _.SupportContract.Reference,
_ => _.SupportContract.Description,
_ => _timeZoneConverter.Convert(_.SupportContract.StartDate, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _timeZoneConverter.Convert(_.SupportContract.EndDate, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _.SupportContract.IsRFQTemplate,
_ => _.SupportContract.IsAcknowledged,
_ => _.SupportContract.AcknowledgedBy,
_ => _timeZoneConverter.Convert(_.SupportContract.AcknowledgedAt, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _.VendorName,
_ => _.AssetOwnerName
);
var startDateColumn = sheet.Column(4);
startDateColumn.Style.Numberformat.Format = "yyyy-mm-dd";
startDateColumn.AutoFit();
var endDateColumn = sheet.Column(5);
endDateColumn.Style.Numberformat.Format = "yyyy-mm-dd";
endDateColumn.AutoFit();
var acknowledgedAtColumn = sheet.Column(10);
acknowledgedAtColumn.Style.Numberformat.Format = "yyyy-mm-dd";
acknowledgedAtColumn.AutoFit();
}));
}
/// <summary>
/// 导出订单表到EXCEL文件
/// <param name="biz_CardInfoListDtos">导出信息的DTO</param>
/// </summary>
public FileDto ExportBiz_CardInfoToFile(List <Biz_CardInfoListDto> biz_CardInfoListDtos)
{
var file = CreateExcelPackage("biz_CardInfoList.xlsx", excelPackage =>
{
var sheet = excelPackage.Workbook.Worksheets.Add(L("Biz_CardInfo"));
sheet.OutLineApplyStyle = true;
AddHeader(
sheet,
L("OrderNum"),
L("IdCard"),
L("RealName"),
L("Birthday"),
L("Status"),
L("StatusMsg"),
L("Province"),
L("City"),
L("Prefecture"),
L("Address"),
L("Sex"),
L("AddrCode"),
L("CheckTime"),
L("Money"),
L("CurrMoney"),
L("RequestUrl")
);
AddObjects(sheet, 2, biz_CardInfoListDtos,
_ => _.OrderNum,
_ => _.IdCard,
_ => _.RealName,
_ => _timeZoneConverter.Convert(_.Birthday, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _.Status,
_ => _.StatusMsg,
_ => _.Province,
_ => _.City,
_ => _.Prefecture,
_ => _.Address,
_ => _.Sex,
_ => _.AddrCode,
_ => _timeZoneConverter.Convert(_.CheckTime, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _.Money,
_ => _.CurrMoney,
_ => _.RequestUrl
);
for (var i = 1; i <= 16; i++)
{
sheet.Column(i).AutoFit();
}
});
return(file);
}
public FileDto ExportToFile(List <GetIncidentForViewDto> incidents)
{
return(CreateExcelPackage(
"Incidents.xlsx",
excelPackage =>
{
var sheet = excelPackage.Workbook.Worksheets.Add(L("Incidents"));
sheet.OutLineApplyStyle = true;
AddHeader(
sheet,
L("Description"),
L("IncidentDate"),
L("Location"),
L("Remarks"),
L("Attachments"),
L("ResolvedAt"),
(L("IncidentPriority")) + L("Priority"),
(L("IncidentStatus")) + L("Status"),
(L("Customer")) + L("Name"),
(L("Asset")) + L("Reference"),
(L("SupportItem")) + L("Description"),
(L("IncidentType")) + L("Type"),
(L("User")) + L("Name")
);
AddObjects(
sheet, 2, incidents,
_ => _.Incident.Description,
_ => _timeZoneConverter.Convert(_.Incident.IncidentDate, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _.Incident.Location,
_ => _.Incident.Remarks,
_ => _timeZoneConverter.Convert(_.Incident.ResolvedAt, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _.IncidentPriorityPriority,
_ => _.IncidentStatusStatus,
_ => _.CustomerName,
_ => _.AssetReference,
_ => _.SupportItemDescription,
_ => _.IncidentTypeType,
_ => _.UserName
);
var incidentDateColumn = sheet.Column(2);
incidentDateColumn.Style.Numberformat.Format = "yyyy-mm-dd";
incidentDateColumn.AutoFit();
var resolvedAtColumn = sheet.Column(6);
resolvedAtColumn.Style.Numberformat.Format = "yyyy-mm-dd";
resolvedAtColumn.AutoFit();
}));
}
public FileDto ExportToFile(List <GetRfqForViewDto> rfqs)
{
return(CreateExcelPackage(
"Rfqs.xlsx",
excelPackage =>
{
var sheet = excelPackage.Workbook.Worksheets.Add(L("Rfqs"));
sheet.OutLineApplyStyle = true;
AddHeader(
sheet,
L("Title"),
L("RequestDate"),
L("RequiredBy"),
L("Description"),
L("Requirements"),
(L("RfqType")) + L("Type"),
(L("AssetOwner")) + L("Name"),
(L("Customer")) + L("Name"),
(L("AssetClass")) + L("Class"),
(L("Incident")) + L("Description"),
(L("Vendor")) + L("Name"),
(L("User")) + L("Name")
);
AddObjects(
sheet, 2, rfqs,
_ => _.Rfq.Title,
_ => _timeZoneConverter.Convert(_.Rfq.RequestDate, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _timeZoneConverter.Convert(_.Rfq.RequiredBy, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _.Rfq.Description,
_ => _.Rfq.Requirements,
_ => _.RfqTypeType,
_ => _.AssetOwnerName,
_ => _.CustomerName,
_ => _.AssetClassClass,
_ => _.IncidentDescription,
_ => _.VendorName,
_ => _.UserName
);
var requestDateColumn = sheet.Column(2);
requestDateColumn.Style.Numberformat.Format = "yyyy-mm-dd";
requestDateColumn.AutoFit();
var requiredByColumn = sheet.Column(3);
requiredByColumn.Style.Numberformat.Format = "yyyy-mm-dd";
requiredByColumn.AutoFit();
}));
}
public FileDto ExportToFile(List <GetIncidentUpdateForViewDto> incidentUpdates)
{
return(CreateExcelPackage(
"IncidentUpdates.xlsx",
excelPackage =>
{
var sheet = excelPackage.Workbook.Worksheets.Add(L("IncidentUpdates"));
sheet.OutLineApplyStyle = true;
AddHeader(
sheet,
L("Updated"),
L("Update"),
(L("User")) + L("Name"),
(L("Incident")) + L("Description")
);
AddObjects(
sheet, 2, incidentUpdates,
_ => _timeZoneConverter.Convert(_.IncidentUpdate.Updated, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _.IncidentUpdate.Update,
_ => _.UserName,
_ => _.IncidentDescription
);
var updatedColumn = sheet.Column(1);
updatedColumn.Style.Numberformat.Format = "yyyy-mm-dd";
updatedColumn.AutoFit();
}));
}
public FileDto ExportToFile(List <GetServicePriceForViewDto> servicePrices)
{
return(CreateExcelPackage(
"ServicePrices.xlsx",
excelPackage =>
{
var sheet = excelPackage.CreateSheet(L("ServicePrices"));
AddHeader(
sheet,
L("Price"),
L("Validity"),
(L("Service")) + L("Name")
);
AddObjects(
sheet, 2, servicePrices,
_ => _.ServicePrice.Price,
_ => _timeZoneConverter.Convert(_.ServicePrice.Validity, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _.ServiceName
);
for (var i = 1; i <= servicePrices.Count; i++)
{
//Formatting cells
SetCellDataFormat(sheet.GetRow(i).Cells[1], "yyyy-mm-dd");
}
for (var i = 0; i < 3; i++)
{
sheet.AutoSizeColumn(i);
}
}));
}
public FileDto ExportToFile(List <GetManualTransactionForViewDto> manualTransactions)
{
return(CreateExcelPackage(
"ManualTransactions.xlsx",
excelPackage =>
{
var sheet = excelPackage.Workbook.Worksheets.Add(L("ManualTransactions"));
sheet.OutLineApplyStyle = true;
AddHeader(
sheet,
L("TransDate"),
L("TransType"),
(L("User")) + L("Name"),
(L("Machine")) + L("NameEn")
);
AddObjects(
sheet, 2, manualTransactions,
_ => _timeZoneConverter.Convert(_.ManualTransaction.TransDate, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _.ManualTransaction.TransType,
_ => _.UserName,
_ => _.MachineNameEn
);
var transDateColumn = sheet.Column(1);
transDateColumn.Style.Numberformat.Format = "yyyy-mm-dd";
transDateColumn.AutoFit();
}));
}
public FileDto ExportToFile(List <GetUserShiftForViewDto> userShifts)
{
return(CreateExcelPackage(
"UserShifts.xlsx",
excelPackage =>
{
var sheet = excelPackage.Workbook.Worksheets.Add(L("UserShifts"));
sheet.OutLineApplyStyle = true;
AddHeader(
sheet,
L("Date"),
(L("User")) + L("Name"),
(L("Shift")) + L("NameEn")
);
AddObjects(
sheet, 2, userShifts,
_ => _timeZoneConverter.Convert(_.UserShift.Date, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _.UserName,
_ => _.ShiftNameEn
);
var dateColumn = sheet.Column(1);
dateColumn.Style.Numberformat.Format = "yyyy-mm-dd";
dateColumn.AutoFit();
}));
}
public FileDto ExportToExcel(List <UserListDto> userListDtos)
{
var excel =
CreateExcelPackage("UserList.xlsx", excelpackage =>
{
var sheet = excelpackage.Workbook.Worksheets.Add(L("Users"));
sheet.OutLineApplyStyle = true;
AddHeader(sheet,
L("UserName"),
L("EmailAddress"), L("CreationTime"));
AddObject(sheet, 2, userListDtos,
ex => ex.UserName,
ex => ex.EmailAddress
, ex => _timeZoneConverter.Convert(ex.CreationTime, AbpSession.TenantId, AbpSession.GetUserId()));
var createTimeColumn = sheet.Column(3);
createTimeColumn.Style.Numberformat.Format = "yyyy-mm-dd";
});
return(excel);
}
public FileDto ExportToFile(List <GetAgedReceivablesPeriodForViewDto> agedReceivablesPeriods)
{
return(CreateExcelPackage(
"AgedReceivablesPeriods.xlsx",
excelPackage =>
{
var sheet = excelPackage.Workbook.Worksheets.Add(L("AgedReceivablesPeriods"));
sheet.OutLineApplyStyle = true;
AddHeader(
sheet,
L("Period"),
L("Current"),
L("Over30"),
L("Over60"),
L("Over90"),
L("Over120")
);
AddObjects(
sheet, 2, agedReceivablesPeriods,
_ => _timeZoneConverter.Convert(_.AgedReceivablesPeriod.Period, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _.AgedReceivablesPeriod.Current,
_ => _.AgedReceivablesPeriod.Over30,
_ => _.AgedReceivablesPeriod.Over60,
_ => _.AgedReceivablesPeriod.Over90,
_ => _.AgedReceivablesPeriod.Over120
);
var periodColumn = sheet.Column(1);
periodColumn.Style.Numberformat.Format = "yyyy-mm-dd";
periodColumn.AutoFit();
}));
}
public FileDto ExportToFile(List <ChatMessageExportDto> messages)
{
var tenancyName = messages.Count > 0 ? messages.First().TargetTenantName : L("Anonymous");
var userName = messages.Count > 0 ? messages.First().TargetUserName : L("Anonymous");
return(CreateExcelPackage(
$"Chat_{tenancyName}_{userName}.xlsx",
excelPackage =>
{
var sheet = excelPackage.Workbook.Worksheets.Add(L("Messages"));
sheet.OutLineApplyStyle = true;
AddHeader(
sheet,
L("ChatMessage_From"),
L("ChatMessage_To"),
L("Message"),
L("ReadState"),
L("CreationTime")
);
AddObjects(
sheet, 2, messages,
_ => _.Side == ChatSide.Receiver ? (_.TargetTenantName + "/" + _.TargetUserName) : L("You"),
_ => _.Side == ChatSide.Receiver ? L("You") : (_.TargetTenantName + "/" + _.TargetUserName),
_ => _.Message,
_ => _.Side == ChatSide.Receiver ? _.ReadState : _.ReceiverReadState,
_ => _timeZoneConverter.Convert(_.CreationTime, _abpSession.TenantId, _abpSession.GetUserId())
);
//Formatting cells
var timeColumn = sheet.Column(5);
timeColumn.Style.Numberformat.Format = "yyyy-mm-dd hh:mm:ss";
}));
}
public FileDto ExportToFile(List <GetEmployeePermitForViewDto> employeePermits)
{
return(CreateExcelPackage(
"EmployeePermits.xlsx",
excelPackage =>
{
var sheet = excelPackage.Workbook.Worksheets.Add(L("EmployeePermits"));
sheet.OutLineApplyStyle = true;
AddHeader(
sheet,
L("FromTime"),
L("PermitDate"),
L("Description"),
L("Status"),
(L("User")) + L("Name"),
(L("Permit")) + L("DescriptionAr")
);
AddObjects(
sheet, 2, employeePermits,
_ => _.EmployeePermit.FromTime,
_ => _timeZoneConverter.Convert(_.EmployeePermit.PermitDate, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _.EmployeePermit.Description,
_ => _.EmployeePermit.Status,
_ => _.UserName,
_ => _.PermitDescriptionAr
);
var permitDateColumn = sheet.Column(2);
permitDateColumn.Style.Numberformat.Format = "yyyy-mm-dd";
permitDateColumn.AutoFit();
}));
}
public FileDto ExportToFile(List <GetPbDownloadEbookForViewDto> pbDownloadEbooks)
{
return(CreateExcelPackage(
"PbDownloadEbooks.xlsx",
excelPackage =>
{
var sheet = excelPackage.Workbook.Worksheets.Add(L("PbDownloadEbooks"));
sheet.OutLineApplyStyle = true;
AddHeader(
sheet,
L("Number"),
L("Month"),
(L("PbEbook")) + L("EbookName")
);
AddObjects(
sheet, 2, pbDownloadEbooks,
_ => _.PbDownloadEbook.Number,
_ => _timeZoneConverter.Convert(_.PbDownloadEbook.Month, _abpSession.TenantId, _abpSession.GetUserId()),
_ => _.PbEbookEbookName
);
var monthColumn = sheet.Column(2);
monthColumn.Style.Numberformat.Format = "yyyy-mm-dd";
monthColumn.AutoFit();
}));
}