private List <ScoreCardsPerYear> GetDataToExport(int year, List <int> listSupplierId, List <int> listMonthId)
{
List <ScoreCardsPerYear> listScoreCardPerYear = new List <ScoreCardsPerYear>();
foreach (var supplierId in listSupplierId)
{
ScoreCardsPerYear scoreCardPerYear = new ScoreCardsPerYear();
scoreCardPerYear.SupplierName = _supplierService.GetByIdAsync(supplierId).Result.Name;
var scoreCardData = _scoreCardService.GetScoreCardData(year, supplierId);
if (scoreCardData != null)
{
scoreCardPerYear.ScoreCards = scoreCardData;
listScoreCardPerYear.Add(scoreCardPerYear);
}
}
return(listScoreCardPerYear);
}
private void CreateScoreCardPerYearWordSheet(ExcelWorksheet worksheet, int year, ScoreCardsPerYear scoreCardPerYear, List <int> listMonthId)
{
//new list monthId when it null
if (listMonthId == null || listMonthId.Count == 0)
{
listMonthId = new List <int>();
for (int i = 0; i <= 11; i++)
{
listMonthId.Add(i);
}
}
else
{
//order accending
listMonthId = listMonthId.OrderBy(x => x).ToList();
}
var monthHeader = new List <string> {
"Jul", "Aug", "Sep", "Oct", "Nov", "Dec", "Jan", "Feb", "Mar", "Apr", "May", "Jun"
};
var colHeader = new List <string> {
"C2", "D2", "E2", "F2", "G2", "H2", "I2", "J2", "K2", "L2", "M2", "N2"
};
var header = new Dictionary <string, string>
{
{ "A2", "MSQ Measure" },
{ "B2", "YTD" }
};
//Add header
int count = 0;
foreach (var monthId in listMonthId)
{
var month = monthHeader[monthId];
header.Add(colHeader[count], monthHeader[monthId]);
count++;
}
//create header
foreach (var cell in header)
{
HeaderStyle(worksheet.Cells[cell.Key], cell.Value);
}
worksheet.Cells[1, 1].Value = "Year";
worksheet.Cells[1, 2].Value = year;
#region format header width
count = 3;
worksheet.Column(1).Width = 40;
worksheet.Column(2).Width = 20;
foreach (var monthId in listMonthId)
{
worksheet.Column(count).Width = 10;
count++;
}
#endregion
#region fill data
for (int i = 0; i < scoreCardPerYear.ScoreCards.Count; i++)
{
worksheet.Cells[i + 3, 1].Value = scoreCardPerYear.ScoreCards[i].MsqMeasure;
worksheet.Cells[i + 3, 2].Value = scoreCardPerYear.ScoreCards[i].Ytd;
int index = 3;
var valueFormat = scoreCardPerYear.ScoreCards[i].MsqMeasure.Contains("%") ? "{0}%" : "{0}";
foreach (var monthId in listMonthId)
{
worksheet.Cells[i + 3, index].Value = String.Format(valueFormat, scoreCardPerYear.ScoreCards[i].Data[monthId]);
index++;
}
}
#endregion
}
