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);
}
