public void CompileSheet(ExcelPackage package)
{
if (_sheetTables.Count() == 0)
{
return;
}
List <string> sheetCodeColumnStatements = new List <string>();
ExcelWorksheet worksheet = null;
if (_isReferenceSheet == true)
{
worksheet = package.Workbook.Worksheets.Add("Reference");
worksheet.Hidden = eWorkSheetHidden.VeryHidden;
}
else
{
worksheet = package.Workbook.Worksheets.Add(_currentTable.TableName);
}
int rowCount = ShouldAddHeaderRow ? 3 : 1;
foreach (var table in _sheetTables)
{
if (_isReferenceSheet == true)
{
var mergeTitleCell = worksheet.Cells[rowCount, 1, rowCount, 3];
mergeTitleCell.Value = table.TableName;
mergeTitleCell.Merge = true;
mergeTitleCell.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
mergeTitleCell.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightGreen);
rowCount++;
}
if (ShouldAddHeaderRow == true)
{
worksheet.DefaultColWidth = 13;
var headerRow = worksheet.Row(1);
headerRow.Height = 45.60;
#region Logo and Tab name
var logoAndTabTitleCells = worksheet.Cells[1, 1, 1, 6];
logoAndTabTitleCells.Merge = true;
//Picture
var embeddedProvider = new EmbeddedFileProvider(Assembly.Load("SQAD.MTNext.Resources"));
var sqadLogo = Image.FromStream(embeddedProvider.GetFileInfo("Resources/SQADLogo.png").CreateReadStream());
var picture = worksheet.Drawings.AddPicture("SQADLogo", sqadLogo);
picture.SetPosition(0, 2, 0, 5);
//Tab name text
var tabName = logoAndTabTitleCells.RichText.Add($"{CurrentTableName.ToUpper()} ");
var staticNameTabText = logoAndTabTitleCells.RichText.Add("DATA FIELDS");
tabName.Size = staticNameTabText.Size = 40;
tabName.Color = staticNameTabText.Color = System.Drawing.Color.FromArgb(0, 159, 220);
tabName.FontName = staticNameTabText.FontName = "Calibri";
tabName.Bold = true;
logoAndTabTitleCells.Style.Indent = 7;
#endregion Logo and Tab name
#region Notice text
var noticeTextCells = worksheet.Cells[1, 7, 1, 8];
noticeTextCells.Merge = true;
var noticeImportantText = noticeTextCells.RichText.Add("IMPORTANT: ");
noticeImportantText.Bold = true;
noticeImportantText.Size = 11;
noticeImportantText.FontName = "Calibri";
noticeImportantText.Color = System.Drawing.Color.White;
var noticeWarningText = noticeTextCells.RichText.Add("Text instructions for how to complete this section of the page. (the first two lines of this sheet will be omitted from the import).");
noticeWarningText.Size = 11;
noticeWarningText.FontName = "Calibri";
noticeWarningText.Bold = false;
noticeWarningText.Color = System.Drawing.Color.White;
noticeTextCells.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
noticeTextCells.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(132, 151, 176));
noticeTextCells.Style.WrapText = true;
noticeTextCells.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Top;
#endregion Notice text
var fullLogoCells = worksheet.Cells[1, 9, 1, 12];
fullLogoCells.Merge = true;
var fullLogo = worksheet.Drawings.AddPicture("SQADLogoFull", sqadLogo);
fullLogo.SetPosition(0, 2, 9, 0);
}
foreach (DataColumn col in table.Columns)
{
if (worksheet.Name.Equals("Reference"))
{
break;
}
var colName = worksheet.Cells[rowCount, col.Ordinal + 1].RichText.Add(col.ColumnName);
colName.Bold = true;
colName.Size = 13;
worksheet.Cells[rowCount, col.Ordinal + 1].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
worksheet.Cells[rowCount, col.Ordinal + 1].Style.Border.Right.Color.SetColor(System.Drawing.Color.Black);
if ((col.Ordinal + 1) % 2 == 0)
{
int maxRows = rowCount + table.Rows.Count;
worksheet.Cells[rowCount, col.Ordinal + 1, maxRows, col.Ordinal + 1].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
worksheet.Cells[rowCount, col.Ordinal + 1, maxRows, col.Ordinal + 1].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(242, 242, 242));
worksheet.Cells[rowCount, col.Ordinal + 1, maxRows, col.Ordinal + 1].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
worksheet.Cells[rowCount, col.Ordinal + 1, maxRows, col.Ordinal + 1].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
worksheet.Cells[rowCount, col.Ordinal + 1, maxRows, col.Ordinal + 1].Style.Border.Right.Color.SetColor(System.Drawing.Color.Black);
worksheet.Cells[rowCount, col.Ordinal + 1, maxRows, col.Ordinal + 1].Style.Border.Left.Color.SetColor(System.Drawing.Color.Black);
}
if (col.ColumnMapping == MappingType.Hidden)
{
worksheet.Column(col.Ordinal + 1).Hidden = true;
}
}
foreach (DataRow row in table.Rows)
{
rowCount++;
foreach (DataColumn col in table.Columns)
{
var colObject = row[col];
int excelColumnIndex = col.Ordinal + 1; //adjustment for excel column count index (start from 1. nondevelopment count, duh!)
if (!(colObject is ExcelCell))
{
worksheet.Cells[rowCount, excelColumnIndex].Value = colObject;
}
else if (colObject is ExcelCell)
{
ExcelCell cell = colObject as ExcelCell;
worksheet.Cells[rowCount, excelColumnIndex].Value = cell.CellValue;
if (!string.IsNullOrEmpty(cell.DataValidationSheet))
{
var dataValidation = worksheet.DataValidations.AddListValidation(worksheet.Cells[rowCount, excelColumnIndex].Address);
dataValidation.ShowErrorMessage = true;
string validationAddress = $"'Reference'!{worksheet.Cells[cell.DataValidationBeginRow, cell.DataValidationNameCellIndex, (cell.DataValidationBeginRow + cell.DataValidationRowsCount) - 1, cell.DataValidationNameCellIndex]}";
dataValidation.Formula.ExcelFormula = validationAddress;
string code = string.Empty;
code += $"If Target.Column = {excelColumnIndex} Then \n";
code += $" matchVal = Application.Match(Target.Value, Worksheets(\"Reference\").Range(\"{worksheet.Cells[cell.DataValidationBeginRow, cell.DataValidationNameCellIndex, (cell.DataValidationBeginRow + cell.DataValidationRowsCount) - 1, cell.DataValidationNameCellIndex].Address}\"), 0) \n";
code += $" selectedNum = Application.Index(Worksheets(\"Reference\").Range(\"{worksheet.Cells[cell.DataValidationBeginRow, cell.DataValidationValueCellIndex, (cell.DataValidationBeginRow + cell.DataValidationRowsCount) - 1, cell.DataValidationValueCellIndex].Address}\"), matchVal, 1) \n";
code += " If Not IsError(selectedNum) Then \n";
code += " Target.Value = selectedNum \n";
code += " End If \n";
code += "End If \n";
sheetCodeColumnStatements.Add(code);
}
else if (cell.CellValue != null && bool.TryParse(cell.CellValue.ToString(), out var result))
{
var dataValidation = worksheet.DataValidations.AddListValidation(worksheet.Cells[rowCount, excelColumnIndex].Address);
dataValidation.ShowErrorMessage = true;
dataValidation.Formula.Values.Add("True");
dataValidation.Formula.Values.Add("False");
}
}
}
}
rowCount += __ROWS_BETWEEN_REFERENCE_SHEETS__;
if (worksheet.Dimension != null && ShouldAutoFit)
{
worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();
foreach (DataColumn col in table.Columns)
{
if (worksheet.Name.Equals("Reference"))
{
break;
}
if (col.ColumnMapping == MappingType.Hidden)
{
worksheet.Column(col.Ordinal + 1).Hidden = true;
}
}
}
}
//if (worksheet.Dimension != null && ShouldAutoFit)
//{
//worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();
worksheet.Cells[3, 1, 3, worksheet.Dimension.Columns].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
worksheet.Cells[3, 1, 3, worksheet.Dimension.Columns].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(242, 242, 242));
worksheet.Cells[3, 1, 3, worksheet.Dimension.Columns].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
worksheet.Cells[3, 1, 3, worksheet.Dimension.Columns].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thick;
worksheet.Cells[3, 1, 3, worksheet.Dimension.Columns].Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black);
// }
#region sheet code to resolve reference column
if (sheetCodeColumnStatements.Count() > 0)
{
string worksheetOnChangeCode = string.Empty;
worksheetOnChangeCode += "Private Sub Worksheet_Change(ByVal Target As Range) \n";
worksheetOnChangeCode += " If Target.Value = Empty Then \n";
worksheetOnChangeCode += " Exit Sub \n";
worksheetOnChangeCode += " End If \n";
foreach (var codePiece in sheetCodeColumnStatements)
{
worksheetOnChangeCode += codePiece;
}
worksheetOnChangeCode += "End Sub";
if (worksheet.Workbook.VbaProject == null)
{
worksheet.Workbook.CreateVBAProject();
}
worksheet.CodeModule.Code = worksheetOnChangeCode;
}
#endregion sheet code to resolve reference column
}
private void Cnx_InfoMessage_Controller(object sender, SqlInfoMessageEventArgs e)
{
var fileName = $"{CurrentTableName.FirstCharToUpper()}Controller.cs";
System.IO.File.WriteAllText($"{txtRutaDestinoControllers.Text}\\{fileName}", e.Message);
}
