private static void SimpleVba()
{
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
FileInfo fi = FileOutputUtil.GetFileInfo("21.1-SimpleVba.xlsm");
pck.SaveAs(fi);
}
public static void Run(string connectionString)
{
using (var p = new ExcelPackage())
{
CreateTableStyles(p);
CreatePivotTableStyles(p);
CreateSlicerStyles(p);
p.SaveAs(FileOutputUtil.GetFileInfo("27-TableAndSlicerStyles.xlsx"));
}
}
public static async Task RunAsync(string connectionString)
{
using (var p = new ExcelPackage())
{
await CreateTableWithACalculatedColumnAsync(connectionString, p).ConfigureAwait(false);
await StyleTablesAsync(connectionString, p).ConfigureAwait(false);
await CreateTableFilterAndSlicerAsync(connectionString, p).ConfigureAwait(false);
p.SaveAs(FileOutputUtil.GetFileInfo("28-Tables.xlsx"));
}
}
public static void Run(string connectionString)
{
using (var p = new ExcelPackage())
{
//A sample with a table and several slicers.
TableSlicerSample(p, connectionString);
//Creates the source data for the pivot tables in a separate sheet.
CreatePivotTableSourceWorksheet(p, connectionString);
//Create a pivot table with a slicer connected to one field.
PivotTableSlicerSample(p);
PivotTableOneSlicerToMultiplePivotTables(p);
p.SaveAs(FileOutputUtil.GetFileInfo("24-Slicers.xlsx"));
}
}
private static void AddABubbleChart()
{
FileInfo sample1File = FileOutputUtil.GetFileInfo("01-GettingStarted.xlsx", false);
//Open Sample 1 again
ExcelPackage pck = new ExcelPackage(sample1File);
var p = new ExcelPackage();
//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(StoreName.My, StoreLocation.CurrentUser);
//store.Open(OpenFlags.ReadOnly);
//pck.Workbook.VbaProject.Signature.Certificate = store.Certificates[0];
//And Save as xlsm
FileInfo fi = FileOutputUtil.GetFileInfo("21.2-AddABubbleChartVba.xlsm");
pck.SaveAs(fi);
}
static async Task Main(string[] args)
{
try
{
//EPPlus 5 uses a dual licens model. This requires you to specifiy the License you are using to be able to use the library.
//This sample sets the LicenseContext in the appsettings.json file. An alternative is the commented row below.
//ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
//See https://epplussoftware.com/Developers/LicenseException for more info.
string connectionStr = "Data Source=EPPlusSample.sqlite;Version=3;";
//Set the output directory to the SampleApp folder where the app is running from.
FileOutputUtil.OutputDir = new DirectoryInfo($"{AppDomain.CurrentDomain.BaseDirectory}SampleApp");
// Sample 1 - simply creates a new workbook from scratch
// containing a worksheet that adds a few numbers together
Console.WriteLine("Running sample 1");
string sample1Path = GettingStartedSample.Run();
Console.WriteLine("Sample 1 created: {0}", sample1Path);
Console.WriteLine();
// Sample 2 - simply reads some values from the file generated by sample 1
// and outputs them to the console
Console.WriteLine("Running sample 2");
ReadWorkbookSample.Run();
Console.WriteLine();
//Sample 3 - Load and save using async methods
Console.WriteLine("Running sample 3-Async-Await");
await UsingAsyncAwaitSample.RunAsync(connectionStr);
Console.WriteLine("Sample 3 created {0}", FileOutputUtil.OutputDir.Name);
Console.WriteLine();
//Sample 4 - Shows a few ways to load data (Datatable, IEnumerable and more).
Console.WriteLine("Running sample 4 - LoadingDataWithTables");
LoadingDataWithTablesSample.Run();
Console.WriteLine("Sample 4 (LoadingDataWithTables) created {0}", FileOutputUtil.OutputDir.Name);
Console.WriteLine();
//Sample 4 - Shows how to load dynamic/ExpandoObject
LoadingDataWithDynamicObjects.Run();
Console.WriteLine("Sample 4 (LoadingDataWithDynamicObjects) created {0}", FileOutputUtil.OutputDir.Name);
Console.WriteLine();
//Sample 5 Loads two csv files into tables and creates an area chart and a Column/Line chart on the data.
//This sample also shows how to use a secondary axis.
Console.WriteLine("Running sample 5");
var output = await ImportAndExportCsvFilesSample.Run();
Console.WriteLine("Sample 5 created: {0}", output);
Console.WriteLine();
//Sample 6 Calculate - Shows how to calculate formulas in the workbook.
Console.WriteLine("Sample 6 - Calculate formulas");
CalculateFormulasSample.Run();
Console.WriteLine("Sample 6 created {0}", FileOutputUtil.OutputDir.Name);
Console.WriteLine();
//Sample 7
//Open sample 1 and add a pie chart.
Console.WriteLine("Running sample 7 - Open a workbook and add data and a pie chart");
output = OpenWorkbookAndAddDataAndChartSample.Run();
Console.WriteLine("Sample 7 created:", output);
Console.WriteLine();
// Sample 8 - creates a workbook from scratch
//Shows how to use Ranges, Styling, Namedstyles and Hyperlinks
Console.WriteLine("Running sample 8");
output = SalesReportFromDatabase.Run(connectionStr);
Console.WriteLine("Sample 8 created: {0}", output);
Console.WriteLine();
//Sample 9
//This sample shows the performance capabilities of the component and shows sheet protection.
//Load X(param 2) rows with five columns
Console.WriteLine("Running sample 9");
output = PerformanceAndProtectionSample.Run(65534);
Console.WriteLine("Sample 9 created:", output);
Console.WriteLine();
//Sample 10 - Linq
//Opens Sample 9 and perform some Linq queries
Console.WriteLine("Running sample 10-Linq");
ReadDataUsingLinq.Run();
Console.WriteLine();
//Sample 11 - Conditional Formatting
Console.WriteLine("Running sample 11");
ConditionalFormatting.Run();
Console.WriteLine("Sample 11 created {0}", FileOutputUtil.OutputDir.Name);
Console.WriteLine();
//Sample 12 - Data validation
Console.WriteLine("Running sample 12");
output = DataValidationSample.Run();
Console.WriteLine("Sample 12 created {0}", output);
Console.WriteLine();
//Sample 13 - Filter
Console.WriteLine("Running sample 13-Filter");
await Filter.RunAsync(connectionStr);
Console.WriteLine("Sample 13 created {0}", FileOutputUtil.OutputDir.Name);
Console.WriteLine();
//Sample 14 - Shapes & Images
Console.WriteLine("Running sample 14-Shapes & Images");
ShapesAndImagesSample.Run();
Console.WriteLine("Sample 14 created {0}", FileOutputUtil.OutputDir.Name);
Console.WriteLine();
//Sample 15 - Themes and Chart styling
Console.WriteLine("Running sample 15-Theme and Chart styling");
//Run the sample with the default office theme
await ChartsAndThemesSample.RunAsync(connectionStr,
FileOutputUtil.GetFileInfo("15-ChartsAndThemes.xlsx"), null);
//Run the sample with the integral theme. Themes can be exported as thmx files from Excel and can then be applied to a package.
await ChartsAndThemesSample.RunAsync(connectionStr,
FileOutputUtil.GetFileInfo("15-ChartsAndThemes-IntegralTheme.xlsx"),
FileInputUtil.GetFileInfo("15-ChartsAndThemes", "integral.thmx"));
Console.WriteLine("Sample 15 created {0}", FileOutputUtil.OutputDir.Name);
Console.WriteLine();
//Sample 16 - Shows how to add sparkline charts.
Console.WriteLine("Running sample 16-Sparklines");
SparkLinesSample.Run();
Console.WriteLine("Sample 16 created {0}", FileOutputUtil.OutputDir.Name);
Console.WriteLine();
// Sample 17 - Creates a workbook based on a template.
// Populates a range with data and set the series of a linechart.
Console.WriteLine("Running sample 17");
output = FxReportFromDatabase.Run(connectionStr);
Console.WriteLine("Sample 17 created: {0}", output);
Console.WriteLine();
//Sample 18 - Pivottables
Console.WriteLine("Running sample 18");
output = PivotTablesSample.Run(connectionStr); //TODO: Fix database
Console.WriteLine("Sample 18 created {0}", output);
Console.WriteLine();
//Sample 19 Swedish Quiz : Shows Encryption, workbook- and worksheet protection.
Console.WriteLine("Running sample 19");
DrawingsSample.Run();
Console.WriteLine("Sample 19 created: {0}", FileOutputUtil.OutputDir.FullName);
Console.WriteLine();
//Sample 20
//Creates an advanced report on a directory in the filesystem.
//Parameter 2 is the directory to report. Parameter 3 is how deep the scan will go. Parameter 4 Skips Icons if set to true (The icon handling is slow)
//This example demonstrates how to use outlines, tables,comments, shapes, pictures and charts.
Console.WriteLine("Running sample 20");
output = CreateAFileSystemReport.Run(new DirectoryInfo(System.Reflection.Assembly.GetEntryAssembly().Location).Parent, 5, true);
Console.WriteLine("Sample 20 created:", output);
Console.WriteLine();
//Sample 21 - Shows how to work with macro-enabled workbooks(VBA).
Console.WriteLine("Running sample 21-VBA");
WorkingWithVbaSample.Run();
Console.WriteLine("Sample 21 created {0}", FileOutputUtil.OutputDir.Name);
Console.WriteLine();
//Sample 22 - Ignore cell errors using the IngnoreErrors Collection
Console.WriteLine("Running sample 22-Suppress Errors");
IgnoreErrorsSample.Run();
Console.WriteLine("Sample 22 created {0}", FileOutputUtil.OutputDir.Name);
Console.WriteLine();
}
catch (Exception ex)
{
Console.WriteLine("Error: {0}", ex.Message);
}
var prevColor = Console.ForegroundColor;
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine($"Genereted sample workbooks can be found in {FileOutputUtil.OutputDir.FullName}");
Console.ForegroundColor = prevColor;
Console.WriteLine();
Console.WriteLine("Press the return key to exit...");
Console.ReadKey();
}
/// <summary>
/// Sample 1 - Simply creates a new workbook from scratch.
/// The workbook contains one worksheet with a simple invertory list
/// Data is loaded manually via the Cells property of the Worksheet.
/// </summary>
public static string Run()
{
using (var package = new ExcelPackage())
{
//Add a new worksheet to the empty workbook
var worksheet = package.Workbook.Worksheets.Add("Inventory");
//Add the headers
worksheet.Cells[1, 1].Value = "ID";
worksheet.Cells[1, 2].Value = "Product";
worksheet.Cells[1, 3].Value = "Quantity";
worksheet.Cells[1, 4].Value = "Price";
worksheet.Cells[1, 5].Value = "Value";
//Add some items...
worksheet.Cells["A2"].Value = 12001;
worksheet.Cells["B2"].Value = "Nails";
worksheet.Cells["C2"].Value = 37;
worksheet.Cells["D2"].Value = 3.99;
worksheet.Cells["A3"].Value = 12002;
worksheet.Cells["B3"].Value = "Hammer";
worksheet.Cells["C3"].Value = 5;
worksheet.Cells["D3"].Value = 12.10;
worksheet.Cells["A4"].Value = 12003;
worksheet.Cells["B4"].Value = "Saw";
worksheet.Cells["C4"].Value = 12;
worksheet.Cells["D4"].Value = 15.37;
//Add a formula for the value-column
worksheet.Cells["E2:E4"].Formula = "C2*D2";
//Ok now format the values;
using (var range = worksheet.Cells[1, 1, 1, 5])
{
range.Style.Font.Bold = true;
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);
range.Style.Font.Color.SetColor(Color.White);
}
worksheet.Cells["A5:E5"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
worksheet.Cells["A5:E5"].Style.Font.Bold = true;
worksheet.Cells[5, 3, 5, 5].Formula = string.Format("SUBTOTAL(9,{0})", new ExcelAddress(2,3,4,3).Address);
worksheet.Cells["C2:C5"].Style.Numberformat.Format = "#,##0";
worksheet.Cells["D2:E5"].Style.Numberformat.Format = "#,##0.00";
//Create an autofilter for the range
worksheet.Cells["A1:E4"].AutoFilter = true;
worksheet.Cells["A2:A4"].Style.Numberformat.Format = "@"; //Format as text
//There is actually no need to calculate, Excel will do it for you, but in some cases it might be useful.
//For example if you link to this workbook from another workbook or you will open the workbook in a program that hasn't a calculation engine or
//you want to use the result of a formula in your program.
worksheet.Calculate();
worksheet.Cells.AutoFitColumns(0); //Autofit columns for all cells
// Lets set the header text
worksheet.HeaderFooter.OddHeader.CenteredText = "&24&U&\"Arial,Regular Bold\" Inventory";
// Add the page number to the footer plus the total number of pages
worksheet.HeaderFooter.OddFooter.RightAlignedText =
string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);
// Add the sheet name to the footer
worksheet.HeaderFooter.OddFooter.CenteredText = ExcelHeaderFooter.SheetName;
// Add the file path to the footer
worksheet.HeaderFooter.OddFooter.LeftAlignedText = ExcelHeaderFooter.FilePath + ExcelHeaderFooter.FileName;
worksheet.PrinterSettings.RepeatRows = worksheet.Cells["1:2"];
worksheet.PrinterSettings.RepeatColumns = worksheet.Cells["A:G"];
// Change the sheet view to show it in page layout mode
worksheet.View.PageLayoutView = true;
// Set some document properties
package.Workbook.Properties.Title = "Invertory";
package.Workbook.Properties.Author = "Jan Källman";
package.Workbook.Properties.Comments = "This sample demonstrates how to create an Excel workbook using EPPlus";
// Set some extended property values
package.Workbook.Properties.Company = "EPPlus Software AB";
// Set some custom property values
package.Workbook.Properties.SetCustomPropertyValue("Checked by", "Jan Källman");
package.Workbook.Properties.SetCustomPropertyValue("AssemblyName", "EPPlus");
var xlFile = FileOutputUtil.GetFileInfo("01-GettingStarted.xlsx");
// Save our new workbook in the output directory and we are done!
package.SaveAs(xlFile);
return xlFile.FullName;
}
}
public static void Run()
{
using (var package = new ExcelPackage())
{
//First create the sheet containing the data for the check box and the list box.
var dataSheet = CreateDataSheet(package);
//Create the form-sheet and set headers and som basic properties.
ExcelWorksheet formSheet = CreateFormSheet(package);
//Add texts and format the text fields style
formSheet.Cells["A3"].Value = "Name";
formSheet.Cells["A4"].Value = "Gender";
formSheet.Cells["B3,B5,B11"].Style.Border.BorderAround(ExcelBorderStyle.Dotted);
formSheet.Cells["B3,B5,B11"].Style.Fill.SetBackground(eThemeSchemeColor.Background1);
//Controls are added via the worksheets drawings collection.
//Each type has its typed method returning the specific control class.
//Optionally you can use the AddControl method specifying the control type via the eControlType enum
var dropDown = formSheet.Drawings.AddDropDownControl("DropDown1");
dropDown.InputRange = dataSheet.Cells["A1:A2"]; //Linkes to the range of items
dropDown.LinkedCell = formSheet.Cells["G4"]; //The cell where the selected index is updated.
dropDown.SetPosition(3, 1, 1, 0);
dropDown.SetSize(451, 31);
formSheet.Cells["A5"].Value = "Number of guests";
//Add a spin button for the number of guests cell
var spinnButton = formSheet.Drawings.AddSpinButtonControl("SpinButton1");
spinnButton.SetPosition(4, 0, 2, 1);
spinnButton.SetSize(30, 35);
spinnButton.Value = 0;
spinnButton.Increment = 1;
spinnButton.MinValue = 0;
spinnButton.MaxValue = 3;
spinnButton.LinkedCell = formSheet.Cells["B5"];
spinnButton.Value = 1;
//Add a group box and four option buttons to select room type
var grpBox = formSheet.Drawings.AddGroupBoxControl("GroupBox 1");
grpBox.Text = "Room types";
grpBox.SetPosition(5, 8, 1, 1);
grpBox.SetSize(150, 150);
var r1 = formSheet.Drawings.AddRadioButtonControl("OptionSingleRoom");
r1.Text = "Single Room";
r1.FirstButton = true;
r1.LinkedCell = formSheet.Cells["G7"];
r1.SetPosition(5, 15, 1, 5);
var r2 = formSheet.Drawings.AddRadioButtonControl("OptionDoubleRoom");
r2.Text = "Double Room";
r2.LinkedCell = formSheet.Cells["G7"];
r2.SetPosition(6, 15, 1, 5);
r2.Checked = true;
var r3 = formSheet.Drawings.AddRadioButtonControl("OptionSuperiorRoom");
r3.Text = "Superior";
r3.LinkedCell = formSheet.Cells["G7"];
r3.SetPosition(7, 15, 1, 5);
var r4 = formSheet.Drawings.AddRadioButtonControl("OptionSuite");
r4.Text = "Suite";
r4.LinkedCell = formSheet.Cells["G7"];
r4.SetPosition(8, 15, 1, 5);
//Group the groupbox together with the radio buttons, so they act as one unit.
//You can group drawings via the Group method on one of the drawings, here using the group box...
var grp = grpBox.Group(r1, r2, r3); //This will group the groupbox and three of the radio buttons. You would normally include r4 here as well, but we add it in the next statement to demonstrate how group shapes work.
//...Or add them to a group drawing returned by the Group method.
grp.Drawings.Add(r4); //This will add the fourth radio button to the group
//Add a scroll bar to control the number of nights
formSheet.Cells["A11"].Value = "Number of nights";
var scrollBar = formSheet.Drawings.AddScrollBarControl("Scrollbar1");
scrollBar.Horizontal = true; //We want a horizontal scrollbar
scrollBar.SetPosition(10, 1, 2, 1);
scrollBar.SetSize(200, 30);
scrollBar.LinkedCell = formSheet.Cells["B11"];
scrollBar.MinValue = 1;
scrollBar.MaxValue = 365;
scrollBar.Increment = 1;
scrollBar.Page = 7; //How much a page click should increase.
scrollBar.Value = 1;
//Add a listbox and connect it to the input range in the data sheet
formSheet.Cells["A12"].Value = "Requests";
var listBox = formSheet.Drawings.AddListBoxControl("Listbox1");
listBox.InputRange = dataSheet.Cells["B1:B3"];
listBox.LinkedCell = formSheet.Cells["G12"];
listBox.SetPosition(11, 5, 1, 0);
listBox.SetSize(200, 100);
//Last, add a button and connect it to a macro appending the data to a text file.
var button = formSheet.Drawings.AddButtonControl("ExportButton");
button.Text = "Make Reservation";
button.Macro = "ExportButton_Click";
button.SetPosition(15, 0, 1, 0);
button.AutomaticSize = true;
formSheet.Select(formSheet.Cells["B3"]);
package.Workbook.CreateVBAProject();
var module = package.Workbook.VbaProject.Modules.AddModule("ControlEvents");
var code = new StringBuilder();
code.AppendLine("Sub ExportButton_Click");
code.AppendLine("Msgbox \"Here you can place the code to handle the form\"");
code.AppendLine("End Sub");
module.Code = code.ToString();
package.SaveAs(FileOutputUtil.GetFileInfo("26-FormControls.xlsm"));
}
}
private static string GetCodeModule(DirectoryInfo codeDir, string fileName)
{
return(File.ReadAllText(FileOutputUtil.GetFileInfo(codeDir, fileName, false).FullName));
}
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);
}
/// <summary>
/// Sample 14 - Conditional formatting example
/// </summary>
public static string Run()
{
FileInfo newFile = FileOutputUtil.GetFileInfo("11-ConditionalFormatting.xlsx");
using (ExcelPackage package = new ExcelPackage(newFile))
{
// add a new worksheet to the empty workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Conditional Formatting");
// Create 4 columns of samples data
for (int col = 1; col < 10; col++)
{
// Add the headers
worksheet.Cells[1, col].Value = "Sample " + col;
for (int row = 2; row < 21; row++)
{
// Add some items...
worksheet.Cells[row, col].Value = row;
}
}
// -------------------------------------------------------------------
// TwoColorScale Conditional Formatting example
// -------------------------------------------------------------------
ExcelAddress cfAddress1 = new ExcelAddress("A2:A10");
var cfRule1 = worksheet.ConditionalFormatting.AddTwoColorScale(cfAddress1);
// Now, lets change some properties:
cfRule1.LowValue.Type = eExcelConditionalFormattingValueObjectType.Num;
cfRule1.LowValue.Value = 4;
cfRule1.LowValue.Color = Color.FromArgb(0xFF, 0xFF, 0xEB, 0x84);
cfRule1.HighValue.Type = eExcelConditionalFormattingValueObjectType.Formula;
cfRule1.HighValue.Formula = "IF($G$1=\"A</x:&'cfRule>\",1,5)";
cfRule1.StopIfTrue = true;
cfRule1.Style.Font.Bold = true;
// But others you can't (readonly)
// cfRule1.Type = eExcelConditionalFormattingRuleType.ThreeColorScale;
// -------------------------------------------------------------------
// ThreeColorScale Conditional Formatting example
// -------------------------------------------------------------------
ExcelAddress cfAddress2 = new ExcelAddress(2, 2, 10, 2); //="B2:B10"
var cfRule2 = worksheet.ConditionalFormatting.AddThreeColorScale(cfAddress2);
// Changing some properties again
cfRule2.Priority = 1;
cfRule2.MiddleValue.Type = eExcelConditionalFormattingValueObjectType.Percentile;
cfRule2.MiddleValue.Value = 30;
cfRule2.StopIfTrue = true;
// You can access a rule by its Priority
var cfRule2Priority = cfRule2.Priority;
var cfRule2_1 = worksheet.ConditionalFormatting.RulesByPriority(cfRule2Priority);
// And you can even change the rule's Address
cfRule2_1.Address = new ExcelAddress("Z1:Z3");
// -------------------------------------------------------------------
// Adding another ThreeColorScale in a different way (observe that we are
// pointing to the same range as the first rule we entered. Excel allows it to
// happen and group the rules in one <conditionalFormatting> node)
// -------------------------------------------------------------------
var cfRule3 = worksheet.Cells[cfAddress1.Address].ConditionalFormatting.AddThreeColorScale();
cfRule3.LowValue.Color = Color.LemonChiffon;
// -------------------------------------------------------------------
// Change the rules priorities to change their execution order
// -------------------------------------------------------------------
cfRule3.Priority = 1;
cfRule1.Priority = 2;
cfRule2.Priority = 3;
// -------------------------------------------------------------------
// Create an Above Average rule
// -------------------------------------------------------------------
var cfRule4 = worksheet.ConditionalFormatting.AddAboveAverage(
new ExcelAddress("B11:B20"));
cfRule4.Style.Font.Bold = true;
cfRule4.Style.Font.Color.Color = Color.Red;
cfRule4.Style.Font.Strike = true;
// -------------------------------------------------------------------
// Create an Above Or Equal Average rule
// -------------------------------------------------------------------
var cfRule5 = worksheet.ConditionalFormatting.AddAboveOrEqualAverage(
new ExcelAddress("B11:B20"));
// -------------------------------------------------------------------
// Create a Below Average rule
// -------------------------------------------------------------------
var cfRule6 = worksheet.ConditionalFormatting.AddBelowAverage(
new ExcelAddress("B11:B20"));
// -------------------------------------------------------------------
// Create a Below Or Equal Average rule
// -------------------------------------------------------------------
var cfRule7 = worksheet.ConditionalFormatting.AddBelowOrEqualAverage(
new ExcelAddress("B11:B20"));
// -------------------------------------------------------------------
// Create a Above StdDev rule
// -------------------------------------------------------------------
var cfRule8 = worksheet.ConditionalFormatting.AddAboveStdDev(
new ExcelAddress("B11:B20"));
cfRule8.StdDev = 0;
// -------------------------------------------------------------------
// Create a Below StdDev rule
// -------------------------------------------------------------------
var cfRule9 = worksheet.ConditionalFormatting.AddBelowStdDev(
new ExcelAddress("B11:B20"));
cfRule9.StdDev = 2;
// -------------------------------------------------------------------
// Create a Bottom rule
// -------------------------------------------------------------------
var cfRule10 = worksheet.ConditionalFormatting.AddBottom(
new ExcelAddress("B11:B20"));
cfRule10.Rank = 4;
// -------------------------------------------------------------------
// Create a Bottom Percent rule
// -------------------------------------------------------------------
var cfRule11 = worksheet.ConditionalFormatting.AddBottomPercent(
new ExcelAddress("B11:B20"));
cfRule11.Rank = 15;
// -------------------------------------------------------------------
// Create a Top rule
// -------------------------------------------------------------------
var cfRule12 = worksheet.ConditionalFormatting.AddTop(
new ExcelAddress("B11:B20"));
// -------------------------------------------------------------------
// Create a Top Percent rule
// -------------------------------------------------------------------
var cfRule13 = worksheet.ConditionalFormatting.AddTopPercent(
new ExcelAddress("B11:B20"));
cfRule13.Style.Border.Left.Style = ExcelBorderStyle.Thin;
cfRule13.Style.Border.Left.Color.Theme = eThemeSchemeColor.Text2;
cfRule13.Style.Border.Bottom.Style = ExcelBorderStyle.DashDot;
cfRule13.Style.Border.Bottom.Color.SetColor(ExcelIndexedColor.Indexed8);
cfRule13.Style.Border.Right.Style = ExcelBorderStyle.Thin;
cfRule13.Style.Border.Right.Color.Color = Color.Blue;
cfRule13.Style.Border.Top.Style = ExcelBorderStyle.Hair;
cfRule13.Style.Border.Top.Color.Auto = true;
// -------------------------------------------------------------------
// Create a Last 7 Days rule
// -------------------------------------------------------------------
ExcelAddress timePeriodAddress = new ExcelAddress("D21:G34 C11:C20");
var cfRule14 = worksheet.ConditionalFormatting.AddLast7Days(
timePeriodAddress);
cfRule14.Style.Fill.PatternType = ExcelFillStyle.LightTrellis;
cfRule14.Style.Fill.PatternColor.Color = Color.BurlyWood;
cfRule14.Style.Fill.BackgroundColor.Color = Color.LightCyan;
// -------------------------------------------------------------------
// Create a Last Month rule
// -------------------------------------------------------------------
var cfRule15 = worksheet.ConditionalFormatting.AddLastMonth(
timePeriodAddress);
cfRule15.Style.NumberFormat.Format = "YYYY";
// -------------------------------------------------------------------
// Create a Last Week rule
// -------------------------------------------------------------------
var cfRule16 = worksheet.ConditionalFormatting.AddLastWeek(
timePeriodAddress);
cfRule16.Style.NumberFormat.Format = "YYYY";
// -------------------------------------------------------------------
// Create a Next Month rule
// -------------------------------------------------------------------
var cfRule17 = worksheet.ConditionalFormatting.AddNextMonth(
timePeriodAddress);
// -------------------------------------------------------------------
// Create a Next Week rule
// -------------------------------------------------------------------
var cfRule18 = worksheet.ConditionalFormatting.AddNextWeek(
timePeriodAddress);
// -------------------------------------------------------------------
// Create a This Month rule
// -------------------------------------------------------------------
var cfRule19 = worksheet.ConditionalFormatting.AddThisMonth(
timePeriodAddress);
// -------------------------------------------------------------------
// Create a This Week rule
// -------------------------------------------------------------------
var cfRule20 = worksheet.ConditionalFormatting.AddThisWeek(
timePeriodAddress);
// -------------------------------------------------------------------
// Create a Today rule
// -------------------------------------------------------------------
var cfRule21 = worksheet.ConditionalFormatting.AddToday(
timePeriodAddress);
// -------------------------------------------------------------------
// Create a Tomorrow rule
// -------------------------------------------------------------------
var cfRule22 = worksheet.ConditionalFormatting.AddTomorrow(
timePeriodAddress);
// -------------------------------------------------------------------
// Create a Yesterday rule
// -------------------------------------------------------------------
var cfRule23 = worksheet.ConditionalFormatting.AddYesterday(
timePeriodAddress);
// -------------------------------------------------------------------
// Create a BeginsWith rule
// -------------------------------------------------------------------
ExcelAddress cellIsAddress = new ExcelAddress("E11:E20");
var cfRule24 = worksheet.ConditionalFormatting.AddBeginsWith(
cellIsAddress);
cfRule24.Text = "SearchMe";
// -------------------------------------------------------------------
// Create a Between rule
// -------------------------------------------------------------------
var cfRule25 = worksheet.ConditionalFormatting.AddBetween(
cellIsAddress);
cfRule25.Formula = "IF(E11>5,10,20)";
cfRule25.Formula2 = "IF(E11>5,30,50)";
// -------------------------------------------------------------------
// Create a ContainsBlanks rule
// -------------------------------------------------------------------
var cfRule26 = worksheet.ConditionalFormatting.AddContainsBlanks(
cellIsAddress);
// -------------------------------------------------------------------
// Create a ContainsErrors rule
// -------------------------------------------------------------------
var cfRule27 = worksheet.ConditionalFormatting.AddContainsErrors(
cellIsAddress);
// -------------------------------------------------------------------
// Create a ContainsText rule
// -------------------------------------------------------------------
var cfRule28 = worksheet.ConditionalFormatting.AddContainsText(
cellIsAddress);
cfRule28.Text = "Me";
// -------------------------------------------------------------------
// Create a DuplicateValues rule
// -------------------------------------------------------------------
var cfRule29 = worksheet.ConditionalFormatting.AddDuplicateValues(
cellIsAddress);
// -------------------------------------------------------------------
// Create an EndsWith rule
// -------------------------------------------------------------------
var cfRule30 = worksheet.ConditionalFormatting.AddEndsWith(
cellIsAddress);
cfRule30.Text = "EndText";
// -------------------------------------------------------------------
// Create an Equal rule
// -------------------------------------------------------------------
var cfRule31 = worksheet.ConditionalFormatting.AddEqual(
cellIsAddress);
cfRule31.Formula = "6";
// -------------------------------------------------------------------
// Create an Expression rule
// -------------------------------------------------------------------
var cfRule32 = worksheet.ConditionalFormatting.AddExpression(
cellIsAddress);
cfRule32.Formula = "E11=E12";
// -------------------------------------------------------------------
// Create a GreaterThan rule
// -------------------------------------------------------------------
var cfRule33 = worksheet.ConditionalFormatting.AddGreaterThan(
cellIsAddress);
cfRule33.Formula = "SE(E11<10,10,65)";
// -------------------------------------------------------------------
// Create a GreaterThanOrEqual rule
// -------------------------------------------------------------------
var cfRule34 = worksheet.ConditionalFormatting.AddGreaterThanOrEqual(
cellIsAddress);
cfRule34.Formula = "35";
// -------------------------------------------------------------------
// Create a LessThan rule
// -------------------------------------------------------------------
var cfRule35 = worksheet.ConditionalFormatting.AddLessThan(
cellIsAddress);
cfRule35.Formula = "36";
// -------------------------------------------------------------------
// Create a LessThanOrEqual rule
// -------------------------------------------------------------------
var cfRule36 = worksheet.ConditionalFormatting.AddLessThanOrEqual(
cellIsAddress);
cfRule36.Formula = "37";
// -------------------------------------------------------------------
// Create a NotBetween rule
// -------------------------------------------------------------------
var cfRule37 = worksheet.ConditionalFormatting.AddNotBetween(
cellIsAddress);
cfRule37.Formula = "333";
cfRule37.Formula2 = "999";
// -------------------------------------------------------------------
// Create a NotContainsBlanks rule
// -------------------------------------------------------------------
var cfRule38 = worksheet.ConditionalFormatting.AddNotContainsBlanks(
cellIsAddress);
// -------------------------------------------------------------------
// Create a NotContainsErrors rule
// -------------------------------------------------------------------
var cfRule39 = worksheet.ConditionalFormatting.AddNotContainsErrors(
cellIsAddress);
// -------------------------------------------------------------------
// Create a NotContainsText rule
// -------------------------------------------------------------------
var cfRule40 = worksheet.ConditionalFormatting.AddNotContainsText(
cellIsAddress);
cfRule40.Text = "NotMe";
// -------------------------------------------------------------------
// Create an NotEqual rule
// -------------------------------------------------------------------
var cfRule41 = worksheet.ConditionalFormatting.AddNotEqual(
cellIsAddress);
cfRule41.Formula = "14";
ExcelAddress cfAddress43 = new ExcelAddress("G2:G10");
var cfRule42 = worksheet.ConditionalFormatting.AddThreeIconSet(cfAddress43, eExcelconditionalFormatting3IconsSetType.TrafficLights1);
ExcelAddress cfAddress44 = new ExcelAddress("H2:H10");
var cfRule43 = worksheet.ConditionalFormatting.AddDatabar(cfAddress44, Color.DarkBlue);
// -----------------------------------------------------------
// Removing Conditional Formatting rules
// -----------------------------------------------------------
// Remove one Rule by its object
//worksheet.ConditionalFormatting.Remove(cfRule1);
// Remove one Rule by index
//worksheet.ConditionalFormatting.RemoveAt(1);
// Remove one Rule by its Priority
//worksheet.ConditionalFormatting.RemoveByPriority(2);
// Remove all the Rules
//worksheet.ConditionalFormatting.RemoveAll();
// set some document properties
package.Workbook.Properties.Title = "Conditional Formatting";
package.Workbook.Properties.Author = "Eyal Seagull";
package.Workbook.Properties.Comments = "This sample demonstrates how to add Conditional Formatting to an Excel 2007 worksheet using EPPlus";
// set some custom property values
package.Workbook.Properties.SetCustomPropertyValue("Checked by", "Eyal Seagull");
package.Workbook.Properties.SetCustomPropertyValue("AssemblyName", "EPPlus");
//Getting a rule from the collection as a typed rule
if (worksheet.ConditionalFormatting[41].Type == eExcelConditionalFormattingRuleType.ThreeIconSet)
{
var iconRule = worksheet.ConditionalFormatting[41].As.ThreeIconSet; //Type cast the rule as an iconset rule.
//Do something with the iconRule...
}
if (worksheet.ConditionalFormatting[42].Type == eExcelConditionalFormattingRuleType.DataBar)
{
var dataBarRule = worksheet.ConditionalFormatting[42].As.DataBar; //Type cast the rule as an iconset rule.
//Do something with the databarRule...
}
// save our new workbook and we are done!
package.Save();
}
return(newFile.FullName);
}
/// <summary>
/// This sample shows how to use Linq with the Cells collection
/// </summary>
/// <param name="outputDir">The path where sample7.xlsx is</param>
public static void Run()
{
Console.WriteLine("Now open sample 9 again and perform some Linq queries...");
Console.WriteLine();
FileInfo existingFile = FileOutputUtil.GetFileInfo("09-PerformanceAndProtection.xlsx", false);
using (ExcelPackage package = new ExcelPackage(existingFile))
{
ExcelWorksheet sheet = package.Workbook.Worksheets[0];
//Select all cells in column d between 9990 and 10000
var query1 = (from cell in sheet.Cells["d:d"] where cell.Value is double && (double)cell.Value >= 9990 && (double)cell.Value <= 10000 select cell);
Console.WriteLine("Print all cells with value between 9990 and 10000 in column D ...");
Console.WriteLine();
int count = 0;
foreach (var cell in query1)
{
Console.WriteLine("Cell {0} has value {1:N0}", cell.Address, cell.Value);
count++;
}
Console.WriteLine("{0} cells found ...", count);
Console.WriteLine();
//Select all bold cells
Console.WriteLine("Now get all bold cells from the entire sheet...");
var query2 = (from cell in sheet.Cells[sheet.Dimension.Address] where cell.Style.Font.Bold select cell);
//If you have a clue where the data is, specify a smaller range in the cells indexer to get better performance (for example "1:1,65536:65536" here)
count = 0;
foreach (var cell in query2)
{
if (!string.IsNullOrEmpty(cell.Formula))
{
Console.WriteLine("Cell {0} is bold and has a formula of {1:N0}", cell.Address, cell.Formula);
}
else
{
Console.WriteLine("Cell {0} is bold and has a value of {1:N0}", cell.Address, cell.Value);
}
count++;
}
//Here we use more than one column in the where clause. We start by searching column D, then use the Offset method to check the value of column C.
var query3 = (from cell in sheet.Cells["d:d"]
where cell.Value is double &&
(double)cell.Value >= 9500 && (double)cell.Value <= 10000 &&
cell.Offset(0, -1).GetValue <DateTime>().Year == DateTime.Today.Year + 1
select cell);
Console.WriteLine();
Console.WriteLine("Print all cells with a value between 9500 and 10000 in column D and the year of Column C is {0} ...", DateTime.Today.Year + 1);
Console.WriteLine();
count = 0;
foreach (var cell in query3) //The cells returned here will all be in column D, since that is the address in the indexer. Use the Offset method to print any other cells from the same row.
{
Console.WriteLine("Cell {0} has value {1:N0} Date is {2:d}", cell.Address, cell.Value, cell.Offset(0, -1).GetValue <DateTime>());
count++;
}
}
}