SampleApp/Sample15.cs

/* 
 * You may amend and distribute as you like, but don't remove this header!
 * 
 * EPPlus provides server-side generation of Excel 2007 spreadsheets.
 * See http://www.codeplex.com/EPPlus for details.
 * 
 * All rights reserved.
 * 
 * EPPlus is an Open Source project provided under the 
 * GNU General Public License (GPL) as published by the 
 * Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
 * 
 * The GNU General Public License can be viewed at http://www.opensource.org/licenses/gpl-license.php
 * If you unfamiliar with this license or have questions about it, here is an http://www.gnu.org/licenses/gpl-faq.html
 * 
 * The code for this project may be used and redistributed by any means PROVIDING it is 
 * not sold for profit without the author's written consent, and providing that this notice 
 * and the author's name and all copyright notices remain intact.
 * 
 * All code and executables are provided "as is" with no warranty either express or implied. 
 * The author accepts no liability for any damage or loss of business that this product may cause.
 *
 *  Code change notes:
 * 
 * Author							Change						Date
 * ******************************************************************************
 * Jan Källman                      Added       		        2012-05-01
 *******************************************************************************/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using OfficeOpenXml;
using System.Security.Cryptography.X509Certificates;
using System.Drawing;
using OfficeOpenXml.Style;
using OfficeOpenXml.Drawing.Chart;
namespace EPPlusSamples
{
    class Sample15
    {
        public static void VBASample(DirectoryInfo outputDir)
        {
            //Create a macro-enabled workbook from scratch.
            VBASample1(outputDir);
            
            //Open Sample 1 and add code to change the chart to a bubble chart.
            VBASample2(outputDir);

            //Simple battleships game from scratch.
            VBASample3(outputDir);
        }
        private static void VBASample1(DirectoryInfo outputDir)
        {
            ExcelPackage pck = new ExcelPackage();

            //Add a worksheet.
            var ws=pck.Workbook.Worksheets.Add("VBA Sample");
            ws.Drawings.AddShape("VBASampleRect", eShapeStyle.RoundRect);
            
            //Create a vba project             
            pck.Workbook.CreateVBAProject();

            //Now add some code to update the text of the shape...
            var sb = new StringBuilder();

            sb.AppendLine("Private Sub Workbook_Open()");
            sb.AppendLine("    [VBA Sample].Shapes(\"VBASampleRect\").TextEffect.Text = \"This text is set from VBA!\"");
            sb.AppendLine("End Sub");
            pck.Workbook.CodeModule.Code = sb.ToString();            

            //And Save as xlsm
            pck.SaveAs(new FileInfo(outputDir.FullName + @"\sample15-1.xlsm"));
        }
        private static void VBASample2(DirectoryInfo outputDir)
        {
            //Open Sample 1 again
            ExcelPackage pck = new ExcelPackage(new FileInfo(outputDir.FullName + @"\sample1.xlsx"));
            //Create a vba project             
            pck.Workbook.CreateVBAProject();

            //Now add some code that creates a bubble chart...
            var sb = new StringBuilder();

            sb.AppendLine("Public Sub CreateBubbleChart()");
            sb.AppendLine("Dim co As ChartObject");
            sb.AppendLine("Set co = Inventory.ChartObjects.Add(10, 100, 400, 200)");
            sb.AppendLine("co.Chart.SetSourceData Source:=Range(\"'Inventory'!$B$1:$E$5\")");
            sb.AppendLine("co.Chart.ChartType = xlBubble3DEffect         'Add a bubblechart");
            sb.AppendLine("End Sub");

            //Create a new module and set the code
            var module = pck.Workbook.VbaProject.Modules.AddModule("EPPlusGeneratedCode");
            module.Code = sb.ToString();

            //Call the newly created sub from the workbook open event
            pck.Workbook.CodeModule.Code = "Private Sub Workbook_Open()\r\nCreateBubbleChart\r\nEnd Sub";

            //Optionally, Sign the code with your company certificate.
            /*            
            X509Store store = new X509Store(StoreLocation.CurrentUser);
            store.Open(OpenFlags.ReadOnly);
            pck.Workbook.VbaProject.Signature.Certificate = store.Certificates[0];
            */

            //And Save as xlsm
            pck.SaveAs(new FileInfo(outputDir.FullName + @"\sample15-2.xlsm"));
        }
        private static void VBASample3(DirectoryInfo outputDir)
        {
            //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");

            //Add all the code from the textfiles in the Vba-Code sub-folder.
            pck.Workbook.CodeModule.Code = File.ReadAllText("..\\..\\VBA-Code\\ThisWorkbook.txt");
            
            //Add the sheet code
            ws.CodeModule.Code = File.ReadAllText("..\\..\\VBA-Code\\BattleshipSheet.txt");
            var m1=pck.Workbook.VbaProject.Modules.AddModule("Code");
            string code = File.ReadAllText("..\\..\\VBA-Code\\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 carefull 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 = File.ReadAllText("..\\..\\VBA-Code\\ComputerPlayModule.txt");

            var c1 = pck.Workbook.VbaProject.Modules.AddClass("Ship",false);
            c1.Code = File.ReadAllText("..\\..\\VBA-Code\\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;
            //    }
            //}

            pck.SaveAs(new FileInfo(outputDir.FullName + @"\sample15-3.xlsm"));
        }

        private static void AddChart(ExcelRange rng,string name, string prefix)
        {
            var chrt = (ExcelPieChart)rng.Worksheet.Drawings.AddChart(name, eChartType.Pie);
            chrt.SetPosition(rng.Start.Row-1, 0, rng.Start.Column-1, 0);
            chrt.To.Row = rng.Start.Row+9;
            chrt.To.Column = rng.Start.Column + 9;
            chrt.Style = eChartStyle.Style18;
            chrt.DataLabel.ShowPercent = true;

            var serie = chrt.Series.Add(rng.Offset(2, 2, 1, 2), rng.Offset(1, 2, 1, 2));
            serie.Header = "Hits";
            
            chrt.Title.Text = "Hit ratio";
            
            var n1 = rng.Worksheet.Names.Add(prefix + "Misses", rng.Offset(2, 2));
            n1.Value = 0;
            var n2 = rng.Worksheet.Names.Add(prefix + "Hits", rng.Offset(2, 3));
            n2.Value = 0;
            rng.Offset(1, 2).Value = "Misses";
            rng.Offset(1, 3).Value = "Hits";            
        }

        private static void CreateBoard(ExcelRange rng)
        {
            //Create a gradiant background with one dark and one light blue color
            rng.Style.Fill.Gradient.Color1.SetColor(Color.FromArgb(0x80, 0x80, 0XFF));
            rng.Style.Fill.Gradient.Color2.SetColor(Color.FromArgb(0x20, 0x20, 0XFF));
            rng.Style.Fill.Gradient.Type = ExcelFillGradientType.None;
            for (int col = 0; col <= rng.End.Column - rng.Start.Column; col++)
            {
                for (int row = 0; row <= rng.End.Row - rng.Start.Row; row++)
                {
                    if (col % 4 == 0)
                    {
                        rng.Offset(row, col, 1, 1).Style.Fill.Gradient.Degree = 45;
                    }
                    if (col % 4 == 1)
                    {
                        rng.Offset(row, col, 1, 1).Style.Fill.Gradient.Degree = 70;
                    }
                    if (col % 4 == 2)
                    {
                        rng.Offset(row, col, 1, 1).Style.Fill.Gradient.Degree = 110;
                    }
                    else
                    {
                        rng.Offset(row, col, 1, 1).Style.Fill.Gradient.Degree = 135;
                    }
                }
            }
            //Set the inner cell border to thin, light gray
            rng.Style.Border.Top.Style = ExcelBorderStyle.Thin;
            rng.Style.Border.Top.Color.SetColor(Color.Gray);
            rng.Style.Border.Right.Style = ExcelBorderStyle.Thin;
            rng.Style.Border.Right.Color.SetColor(Color.Gray);
            rng.Style.Border.Left.Style = ExcelBorderStyle.Thin;
            rng.Style.Border.Left.Color.SetColor(Color.Gray);
            rng.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
            rng.Style.Border.Bottom.Color.SetColor(Color.Gray);

            //Solid black border around the board.
            rng.Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black);
        }
    }
}