262 lines
12 KiB
C#
262 lines
12 KiB
C#
using _0_Framework.Application;
|
||
using CompanyManagement.Infrastructure.Excel.CWS;
|
||
using CompanyManagment.App.Contracts.InstitutionContract;
|
||
using OfficeOpenXml;
|
||
using OfficeOpenXml.Style;
|
||
using System.Drawing;
|
||
using System.Text.RegularExpressions;
|
||
|
||
namespace CompanyManagement.Infrastructure.Excel.InstitutionContract;
|
||
|
||
public class InstitutionContractExcelGenerator
|
||
{
|
||
|
||
public static byte[] GenerateExcel(List<InstitutionContractViewModel> institutionContractViewModels)
|
||
{
|
||
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
|
||
using var package = new ExcelPackage();
|
||
var allWorksheet = package.Workbook.Worksheets.Add("همه");
|
||
|
||
var blueWorksheet = package.Workbook.Worksheets.Add("آبی");
|
||
blueWorksheet.TabColor = Color.LightBlue;
|
||
|
||
var grayWorksheet = package.Workbook.Worksheets.Add("خاکستری");
|
||
grayWorksheet.TabColor = Color.LightGray;
|
||
|
||
var redWorksheet = package.Workbook.Worksheets.Add("قرمز");
|
||
redWorksheet.TabColor = Color.LightCoral;
|
||
|
||
var purpleWorksheet = package.Workbook.Worksheets.Add("بنفش");
|
||
purpleWorksheet.TabColor = Color.MediumPurple;
|
||
|
||
var blackWorksheet = package.Workbook.Worksheets.Add("مشکی");
|
||
blackWorksheet.TabColor = Color.DimGray;
|
||
|
||
var yellowWorksheet = package.Workbook.Worksheets.Add("زرد");
|
||
yellowWorksheet.TabColor = Color.Yellow;
|
||
|
||
var whiteWorksheet = package.Workbook.Worksheets.Add("سفید");
|
||
whiteWorksheet.TabColor = Color.White;
|
||
|
||
|
||
CreateExcelSheet(institutionContractViewModels, allWorksheet);
|
||
|
||
var blueContracts = institutionContractViewModels.Where(x=>x.ExpireColor == "blue").ToList();
|
||
CreateExcelSheet(blueContracts, blueWorksheet);
|
||
institutionContractViewModels = institutionContractViewModels.Except(blueContracts).ToList();
|
||
|
||
var grayContracts = institutionContractViewModels.Where(x => x.IsContractingPartyBlock == "true").ToList();
|
||
CreateExcelSheet(grayContracts, grayWorksheet);
|
||
institutionContractViewModels = institutionContractViewModels.Except(grayContracts).ToList();
|
||
|
||
var redContracts = institutionContractViewModels.Where(x=>x.ExpireColor == "red").ToList();
|
||
CreateExcelSheet(redContracts, redWorksheet);
|
||
institutionContractViewModels = institutionContractViewModels.Except(redContracts).ToList();
|
||
|
||
var purpleContracts = institutionContractViewModels.Where(x=>x.ExpireColor == "purple").ToList();
|
||
CreateExcelSheet(purpleContracts, purpleWorksheet);
|
||
institutionContractViewModels = institutionContractViewModels.Except(purpleContracts).ToList();
|
||
|
||
var blackContracts = institutionContractViewModels.Where(x=>x.ExpireColor == "black").ToList();
|
||
CreateExcelSheet(blackContracts, blackWorksheet);
|
||
institutionContractViewModels = institutionContractViewModels.Except(blackContracts).ToList();
|
||
|
||
var yellowContracts = institutionContractViewModels
|
||
.Where(x => string.IsNullOrWhiteSpace(x.ExpireColor) && x.WorkshopCount == "0").ToList();
|
||
CreateExcelSheet(yellowContracts, yellowWorksheet);
|
||
institutionContractViewModels = institutionContractViewModels.Except(yellowContracts).ToList();
|
||
|
||
var otherContracts = institutionContractViewModels;
|
||
CreateExcelSheet(otherContracts, whiteWorksheet);
|
||
|
||
return package.GetAsByteArray();
|
||
}
|
||
|
||
private static void CreateExcelSheet(List<InstitutionContractViewModel> institutionContractViewModels, ExcelWorksheet worksheet)
|
||
{
|
||
// Headers
|
||
worksheet.Cells[1, 1].Value = "شماره قرارداد";
|
||
worksheet.Cells[1, 2].Value = "طرف حساب";
|
||
worksheet.Cells[1, 3].Value = "شماره کارفرما";
|
||
worksheet.Cells[1, 4].Value = "کارفرما ها";
|
||
worksheet.Cells[1, 5].Value = "کارگاه ها";
|
||
worksheet.Cells[1, 6].Value = "مجبوع پرسنل";
|
||
worksheet.Cells[1, 7].Value = "شروع قرارداد";
|
||
worksheet.Cells[1, 8].Value = "پایان قرارداد";
|
||
worksheet.Cells[1, 9].Value = "مبلغ قرارداد (بدون کارگاه)";
|
||
worksheet.Cells[1, 10].Value = "مبلغ قرارداد";
|
||
worksheet.Cells[1, 11].Value = "وضعیت مالی";
|
||
|
||
using (var range = worksheet.Cells[1, 1, 1, 11])
|
||
{
|
||
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
|
||
range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
|
||
range.Style.Font.Bold = true;
|
||
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
|
||
range.Style.Fill.BackgroundColor.SetColor(Color.LightGray); // رنگ پس زمینه خاکستری
|
||
|
||
// اعمال بوردر به همه خطوط
|
||
range.Style.Border.Top.Style = ExcelBorderStyle.Thin;
|
||
range.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
|
||
range.Style.Border.Left.Style = ExcelBorderStyle.Thin;
|
||
range.Style.Border.Right.Style = ExcelBorderStyle.Thin;
|
||
|
||
// اعمال رنگ مشکی برای بوردرها
|
||
range.Style.Border.Top.Color.SetColor(Color.Black);
|
||
range.Style.Border.Bottom.Color.SetColor(Color.Black);
|
||
range.Style.Border.Left.Color.SetColor(Color.Black);
|
||
range.Style.Border.Right.Color.SetColor(Color.Black);
|
||
}
|
||
|
||
int row = 2;
|
||
|
||
for (int i = 0; i < institutionContractViewModels.Count; i++)
|
||
{
|
||
var contract = institutionContractViewModels[i];
|
||
var employers = contract.EmployerViewModels?.ToList() ?? new();
|
||
var workshops = contract.WorkshopViewModels?.ToList() ?? new();
|
||
|
||
int maxRows = Math.Max(employers.Count, workshops.Count);
|
||
maxRows = Math.Max(1, maxRows);
|
||
|
||
int startRow = row;
|
||
int endRow = row + maxRows - 1;
|
||
|
||
// 🎨 دریافت رنگ پسزمینه از مقدار رنگ موجود در داده
|
||
string colorName = contract.ExpireColor.ToLower();
|
||
var fillColor = GetColorByName(colorName, contract.WorkshopCount, contract.IsContractingPartyBlock);
|
||
|
||
for (int j = 0; j < maxRows; j++)
|
||
{
|
||
int currentRow = row + j;
|
||
|
||
worksheet.Cells[currentRow, 4].Value = j < employers.Count ? employers[j].FullName : null;
|
||
worksheet.Cells[currentRow, 5].Value = j < workshops.Count ? workshops[j].WorkshopFullName : null;
|
||
|
||
for (int col = 1; col <= 11; col++)
|
||
{
|
||
var cell = worksheet.Cells[currentRow, col];
|
||
|
||
// 📏 بوردرهای داخلی نازک / نقطهچین
|
||
cell.Style.Border.Top.Style = ExcelBorderStyle.Dotted;
|
||
cell.Style.Border.Bottom.Style = ExcelBorderStyle.Dotted;
|
||
cell.Style.Border.Left.Style = ExcelBorderStyle.Thin;
|
||
cell.Style.Border.Right.Style = ExcelBorderStyle.Thin;
|
||
|
||
// 🎯 تراز متن
|
||
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
|
||
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
|
||
|
||
// 🎨 اعمال رنگ پسزمینه
|
||
cell.Style.Fill.PatternType = ExcelFillStyle.Solid;
|
||
cell.Style.Fill.BackgroundColor.SetColor(fillColor);
|
||
}
|
||
}
|
||
|
||
// 🧱 مرج و مقداردهی ستونهای اصلی
|
||
worksheet.Cells[startRow, 1, endRow, 1].Merge = true;
|
||
worksheet.Cells[startRow, 1].Value = contract.ContractNo;
|
||
|
||
worksheet.Cells[startRow, 2, endRow, 2].Merge = true;
|
||
worksheet.Cells[startRow, 2].Value = contract.ContractingPartyName;
|
||
|
||
worksheet.Cells[startRow, 3, endRow, 3].Merge = true;
|
||
worksheet.Cells[startRow, 3].Value = contract.ArchiveCode;
|
||
|
||
worksheet.Cells[startRow, 6, endRow, 6].Merge = true;
|
||
worksheet.Cells[startRow, 6].Value = contract.EmployeeCount;
|
||
|
||
worksheet.Cells[startRow, 7, endRow, 7].Merge = true;
|
||
worksheet.Cells[startRow, 7].Value = contract.ContractStartFa;
|
||
|
||
worksheet.Cells[startRow, 8, endRow, 8].Merge = true;
|
||
worksheet.Cells[startRow, 8].Value = contract.ContractEndFa;
|
||
|
||
worksheet.Cells[startRow, 9, endRow, 9].Merge = true;
|
||
var contractWithoutWorkshopAmountCell = worksheet.Cells[startRow, 9];
|
||
contractWithoutWorkshopAmountCell.Value = contract.WorkshopCount == "0" ? MoneyToDouble(contract.ContractAmount) : "";
|
||
contractWithoutWorkshopAmountCell.Style.Numberformat.Format = "#,##0";
|
||
|
||
|
||
worksheet.Cells[startRow, 10, endRow, 10].Merge = true;
|
||
var contractAmountCell = worksheet.Cells[startRow, 10];
|
||
contractAmountCell.Value = contract.WorkshopCount != "0" ? MoneyToDouble(contract.ContractAmount) : "";
|
||
contractAmountCell.Style.Numberformat.Format = "#,##0";
|
||
|
||
|
||
worksheet.Cells[startRow, 11, endRow, 11].Merge = true;
|
||
var balance = MoneyToDouble(contract.BalanceStr);
|
||
var balanceCell = worksheet.Cells[startRow, 11];
|
||
balanceCell.Value = balance;
|
||
balanceCell.Style.Numberformat.Format = "#,##0";
|
||
|
||
if (balance > 0)
|
||
balanceCell.Style.Font.Color.SetColor(Color.Red);
|
||
else if (balance < 0)
|
||
balanceCell.Style.Font.Color.SetColor(Color.Green);
|
||
|
||
// 📦 بوردر ضخیم خارجی برای هر سطر
|
||
var boldRange = worksheet.Cells[startRow, 1, endRow, 11];
|
||
boldRange.Style.Border.BorderAround(ExcelBorderStyle.Medium);
|
||
|
||
row += maxRows;
|
||
}
|
||
|
||
|
||
|
||
worksheet.PrinterSettings.PaperSize = ePaperSize.A4;
|
||
worksheet.PrinterSettings.Orientation = eOrientation.Landscape;
|
||
worksheet.PrinterSettings.FitToPage = true;
|
||
worksheet.PrinterSettings.FitToWidth = 1;
|
||
worksheet.PrinterSettings.FitToHeight = 0;
|
||
worksheet.PrinterSettings.Scale = 85;
|
||
int contractNoCol = 1;
|
||
int contractingPartyNameCol = 2;
|
||
int archiveNoCol = 3;
|
||
int employersCol = 4;
|
||
int workshopsCol = 5;
|
||
int employeeCountCol = 6;
|
||
int startContractCol = 7;
|
||
int endContractCol = 8;
|
||
int contractWithoutWorkshopAmountCol = 9;
|
||
int contractAmountCol = 10;
|
||
int balanceCol = 11;
|
||
worksheet.Columns[contractNoCol].Width = 17;
|
||
worksheet.Columns[contractingPartyNameCol].Width = 40;
|
||
worksheet.Columns[archiveNoCol].Width = 10;
|
||
worksheet.Columns[employersCol].Width = 40;
|
||
worksheet.Columns[workshopsCol].Width = 45;
|
||
worksheet.Columns[employeeCountCol].Width = 12;
|
||
worksheet.Columns[startContractCol].Width = 12;
|
||
worksheet.Columns[endContractCol].Width = 12;
|
||
worksheet.Columns[contractWithoutWorkshopAmountCol].Width = 18;
|
||
worksheet.Columns[contractAmountCol].Width = 12;
|
||
worksheet.Columns[balanceCol].Width = 12;
|
||
worksheet.View.RightToLeft = true; // فارسی
|
||
//worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();
|
||
}
|
||
|
||
private static double MoneyToDouble(string value)
|
||
{
|
||
Console.WriteLine(value);
|
||
var min = value.Length > 1 ? value.Substring(0, 2) : "";
|
||
var test = min == "\u200e\u2212" ? value.MoneyToDouble() * -1 : value.MoneyToDouble();
|
||
|
||
Console.WriteLine(test);
|
||
return test;
|
||
}
|
||
private static Color GetColorByName(string name, string workshopCount, string IsContractingPartyBlock)
|
||
{
|
||
return name switch
|
||
{
|
||
"blue" => Color.LightBlue,
|
||
_ when IsContractingPartyBlock == "true" => Color.LightGray,
|
||
"red" => Color.LightCoral,
|
||
"purple" => Color.MediumPurple,
|
||
"black" => Color.DimGray,
|
||
var n when string.IsNullOrWhiteSpace(n) && workshopCount == "0" => Color.Yellow,
|
||
_ => Color.White
|
||
};
|
||
}
|
||
|
||
} |