public static XLWorkbook GenerateExcelGSF(XLWorkbook workbook, int progid, int docid, string cdsid, OXODoc OXODoc, OXODocDataStore ods, bool popDoc)
{
Stopwatch stopWatch = new Stopwatch();
try
{
stopWatch.Reset();
stopWatch.Start();
IXLWorksheet worksheet = workbook.Worksheets.Add("Global Standard Features");
worksheet.Protect("Password123")
.SetFormatColumns()
.SetFormatRows()
.SetAutoFilter();
worksheet.Style.Font.SetFontSize(11)
.Font.SetFontName("Arial");
worksheet.TabColor = XLColor.Orange;
// car models
ModelDataStore mds = new ModelDataStore(cdsid);
var carModels = mds.GSFModelGetMany(progid, docid);
string modelIds = string.Join(",", carModels.Select(p => string.Format("[{0}]", p.GSFId.ToString())));
int modelCount = carModels.Count();
// data
string make = OXODoc.VehicleMake;
var OXOData = ods.OXODocGetItemData(make, docid, progid, "GSF", "g", -1, modelIds);
int rowCount = OXOData.Count();
// page title
worksheet.Cell("A1").Value = "GLOBAL STANDARD FEATURES";
worksheet.Cell("A2").Value = (OXODoc.VehicleName + " (" + OXODoc.VehicleAKA + ") " + OXODoc.ModelYear + " " + OXODoc.Gateway + " V" + OXODoc.VersionId + " " + OXODoc.Status).ToUpper();
worksheet.Cell("A3").Value = DateTime.Now;
worksheet.Cell("A4").Value = cdsid.ToUpper();
worksheet.Range("A1:A4").Style.Font.SetBold()
.Font.SetFontSize(14)
.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
// heading row
worksheet.Cell("A5").Value = "FEATURE GROUP";
worksheet.Cell("B5").Value = "FEATURE CODE";
worksheet.Cell("C5").Value = "MARKETING FEATURE DESCRIPTION";
worksheet.Cell("D5").Value = "COMMENTS";
worksheet.Range(5, 1, 5, modelCount + 4)
.Style.Font.SetBold(true)
.Font.SetFontColor(XLColor.White)
.Fill.SetBackgroundColor(XLColor.Black)
.Alignment.Vertical = XLAlignmentVerticalValues.Center;
worksheet.Range(1, 5, 4, modelCount + 4)
.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center)
.Alignment.SetVertical(XLAlignmentVerticalValues.Center);
worksheet.Cell("D2").Value = "BODY STYLE";
worksheet.Cell("D3").Value = "ENGINE";
worksheet.Cell("D4").Value = "MODEL CODE";
worksheet.Range("D1:D4").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right)
.Alignment.SetVertical(XLAlignmentVerticalValues.Center);
// key
worksheet.Cell("C1").Value = "Key: S = Standard Feature, NA = Not Available";
worksheet.Cell("C1").Style.Font.SetFontSize(10);
// gray top area
worksheet.Range(1, 1, 4, modelCount + 4).Style.Font.SetBold(true)
.Fill.SetBackgroundColor(XLColor.LightGray);
// car models heading
int col = 5; // column E
worksheet.Range(1, col, 4, col + modelCount - 1).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center)
.Alignment.SetVertical(XLAlignmentVerticalValues.Center)
.Alignment.SetWrapText();
foreach (var carModel in carModels)
{
worksheet.Cell(2, col).Value = carModel.GSFBody;
worksheet.Cell(3, col).Value = carModel.GSFEngine;
worksheet.Cell(4, col).Value = carModel.BMC;
col = col + 1;
}
// output data
int row = 6;
string groupName = "";
foreach (var item in OXOData)
{
// feature family grouping
if (groupName != item[1].ToString())
{
worksheet.Cell("A" + row).SetValue(item[1].ToString().ToUpper())
.Style.Font.SetFontColor(XLColor.White)
.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left)
.Font.SetBold(true)
.Font.SetUnderline(XLFontUnderlineValues.Single);
worksheet.Range(row, 1, row, modelCount + 4).Style.Fill.SetBackgroundColor(XLColor.Black);
row = row + 1;
}
groupName = item[1].ToString();
worksheet.Cell("A" + row).Value = item[1];
worksheet.Cell("B" + row).Value = item[3];
worksheet.Cell("C" + row).Value = item[4];
worksheet.Cell("D" + row).Value = item[6];
var columnOffSet = 9;
if (popDoc == true)
{
for (var n = 0; n < modelCount; n++)
{
var j = n + columnOffSet;
if (String.IsNullOrEmpty("" + item[j]))
{
worksheet.Cell(row, j - 4).Value = "";
}
else
{
if (item[j].ToString() == "S")
{
worksheet.Cell(row, j - 4).Value = "S";
}
else
{
worksheet.Cell(row, j - 4).Value = "NA";
worksheet.Cell(row, j - 4).Style.Fill.SetBackgroundColor(XLColor.LightGray);
}
}
}
}
row = row + 1;
}
// set border styles
worksheet.RangeUsed().Style
.Border.SetTopBorder(XLBorderStyleValues.Thin)
.Border.SetRightBorder(XLBorderStyleValues.Thin)
.Border.SetOutsideBorder(XLBorderStyleValues.Thin);
worksheet.Range("A1:D4").Style
.Border.SetTopBorder(XLBorderStyleValues.None)
.Border.SetRightBorder(XLBorderStyleValues.None)
.Border.SetOutsideBorder(XLBorderStyleValues.Thin);
// center align all of the data columns
worksheet.Range(7, 5, worksheet.LastRowUsed().RowNumber(), modelCount + 4).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
// allow car model headings to wrap
worksheet.Range(1, 5, 4, modelCount + 3).Style.Alignment.SetWrapText();
// adjust column widths
worksheet.Columns().AdjustToContents();
worksheet.Column("A").Width = 32;
worksheet.Column("B").Width = 20;
worksheet.Column("B").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
worksheet.Columns("C:D").Width = 90;
worksheet.Columns("C:D").Style.Alignment.SetWrapText();
worksheet.Columns(5, modelCount + 4).Width = 14;
// apply filter
worksheet.Range(5, 1, worksheet.LastRowUsed().RowNumber(), 1).SetAutoFilter();
// split the screen
worksheet.SheetView.Freeze(5, 2);
stopWatch.Stop();
var executionTime = stopWatch.ElapsedMilliseconds;
}
catch (Exception ex)
{
Log.Error(ex);
throw;
}
return workbook;
}
