private static void CreateABattleShipsGame() { //Now, lets do something a little bit more fun. //We are going to create a simple battleships game from scratch. ExcelPackage pck = new ExcelPackage(); //Add a worksheet. var ws = pck.Workbook.Worksheets.Add("Battleship"); ws.View.ShowGridLines = false; ws.View.ShowHeaders = false; ws.DefaultColWidth = 3; ws.DefaultRowHeight = 15; int gridSize = 10; //Create the boards var board1 = ws.Cells[2, 2, 2 + gridSize - 1, 2 + gridSize - 1]; var board2 = ws.Cells[2, 4 + gridSize - 1, 2 + gridSize - 1, 4 + (gridSize - 1) * 2]; CreateBoard(board1); CreateBoard(board2); ws.Select("B2"); ws.Protection.IsProtected = true; ws.Protection.AllowSelectLockedCells = true; //Create the VBA Project pck.Workbook.CreateVBAProject(); //Password protect your code pck.Workbook.VbaProject.Protection.SetPassword("EPPlus"); var codeDir = FileInputUtil.GetSubDirectory("21-VBA", "VBA-Code"); //Add all the code from the textfiles in the Vba-Code sub-folder. pck.Workbook.CodeModule.Code = GetCodeModule(codeDir, "ThisWorkbook.txt"); //Add the sheet code ws.CodeModule.Code = GetCodeModule(codeDir, "BattleshipSheet.txt"); var m1 = pck.Workbook.VbaProject.Modules.AddModule("Code"); string code = GetCodeModule(codeDir, "CodeModule.txt"); //Insert your ships on the right board. you can changes these, but don't cheat ;) var ships = new string[] { "N3:N7", "P2:S2", "V9:V11", "O10:Q10", "R11:S11" }; //Note: For security reasons you should never mix external data and code(to avoid code injections!), especially not on a webserver. //If you deside to do that anyway, be very careful with the validation of the data. //Be extra careful if you sign the code. //Read more here http://en.wikipedia.org/wiki/Code_injection code = string.Format(code, ships[0], ships[1], ships[2], ships[3], ships[4], board1.Address, board2.Address); //Ships are injected into the constants in the module m1.Code = code; //Ships are displayed with a black background string shipsaddress = string.Join(",", ships); ws.Cells[shipsaddress].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; ws.Cells[shipsaddress].Style.Fill.BackgroundColor.SetColor(Color.Black); var m2 = pck.Workbook.VbaProject.Modules.AddModule("ComputerPlay"); m2.Code = GetCodeModule(codeDir, "ComputerPlayModule.txt"); var c1 = pck.Workbook.VbaProject.Modules.AddClass("Ship", false); c1.Code = GetCodeModule(codeDir, "ShipClass.txt"); //Add the info text shape. var tb = ws.Drawings.AddShape("txtInfo", eShapeStyle.Rect); tb.SetPosition(1, 0, 27, 0); tb.Fill.Color = Color.LightSlateGray; var rt1 = tb.RichText.Add("Battleships"); rt1.Bold = true; tb.RichText.Add("\r\nDouble-click on the left board to make your move. Find and sink all ships to win!"); //Set the headers. ws.SetValue("B1", "Computer Grid"); ws.SetValue("M1", "Your Grid"); ws.Row(1).Style.Font.Size = 18; AddChart(ws.Cells["B13"], "chtHitPercent", "Player"); AddChart(ws.Cells["M13"], "chtComputerHitPercent", "Computer"); ws.Names.Add("LogStart", ws.Cells["B24"]); ws.Cells["B24:X224"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black); ws.Cells["B25:X224"].Style.Font.Name = "Consolas"; ws.SetValue("B24", "Log"); ws.Cells["B24"].Style.Font.Bold = true; ws.Cells["B24:X24"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black); var cf = ws.Cells["B25:B224"].ConditionalFormatting.AddContainsText(); cf.Text = "hit"; cf.Style.Font.Color.Color = Color.Red; //If you have a valid certificate for code signing you can use this code to set it. ///*** Try to find a cert valid for signing... ***/ //X509Store store = new X509Store(StoreLocation.CurrentUser); //store.Open(OpenFlags.ReadOnly); //foreach (var cert in store.Certificates) //{ // if (cert.HasPrivateKey && cert.NotBefore <= DateTime.Today && cert.NotAfter >= DateTime.Today) // { // pck.Workbook.VbaProject.Signature.Certificate = cert; // break; // } //} var fi = FileOutputUtil.GetFileInfo(@"21.3-CreateABattleShipsGameVba.xlsm"); pck.SaveAs(fi); }