public static void Run()
{
var filePath = FileInputUtil.GetFileInfo("02-ReadWorkbook", "ReadWorkbook.xlsx").FullName;
Console.WriteLine("Reading column 2 of {0}", filePath);
Console.WriteLine();
FileInfo existingFile = new FileInfo(filePath);
using (ExcelPackage package = new ExcelPackage(existingFile))
{
//Get the first worksheet in the workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
int col = 2; //Column 2 is the item description
for (int row = 2; row < 5; row++)
{
Console.WriteLine("\tCell({0},{1}).Value={2}", row, col, worksheet.Cells[row, col].Value);
}
//Output the formula from row 3 in A1 and R1C1 format
Console.WriteLine("\tCell({0},{1}).Formula={2}", 3, 5, worksheet.Cells[3, 5].Formula);
Console.WriteLine("\tCell({0},{1}).FormulaR1C1={2}", 3, 5, worksheet.Cells[3, 5].FormulaR1C1);
//Output the formula from row 5 in A1 and R1C1 format
Console.WriteLine("\tCell({0},{1}).Formula={2}", 5, 3, worksheet.Cells[5, 3].Formula);
Console.WriteLine("\tCell({0},{1}).FormulaR1C1={2}", 5, 3, worksheet.Cells[5, 3].FormulaR1C1);
} // the using statement automatically calls Dispose() which closes the package.
Console.WriteLine();
Console.WriteLine("Read workbook sample complete");
Console.WriteLine();
}
private static async Task AddAreaFromChartTemplate(string connectionString, ExcelPackage package)
{
var ws = package.Workbook.Worksheets.Add("Area chart from template");
var range = await LoadFromDatabase(connectionString, ws);
//Add an Area chart from a template file. The crtx file has it's own theme, so it does not change if you apply another theme.
var template = FileInputUtil.GetFileInfo("15-ChartsAndThemes", "AreaChartStyle3.crtx");
var areaChart = (ExcelAreaChart)ws.Drawings.AddChartFromTemplate(template, "areaChart");
var areaSerie = areaChart.Series.Add(ws.Cells[2, 2, 16, 2], ws.Cells[2, 1, 16, 1]);
areaSerie.Header = "Order Value";
areaChart.SetPosition(1, 0, 6, 0);
areaChart.SetSize(1200, 400);
areaChart.Title.Text = "Area Chart";
range.AutoFitColumns(0);
}
public static async Task AddAreaChart(string connectionString, ExcelPackage package)
{
var ws = package.Workbook.Worksheets.Add("Area chart from template");
var range = await LoadFromDatabase(connectionString, ws);
//Adds an Area chart from a template file. The crtx file has it's own theme, so it does not change with the theme.
//The As property provides an easy type cast for drawing objects
var areaChart = ws.Drawings.AddChartFromTemplate(FileInputUtil.GetFileInfo("15-ChartsAndThemes", "AreaChartStyle3.crtx"), "areaChart")
.As.Chart.AreaChart;
var areaSerie = areaChart.Series.Add(ws.Cells[2, 2, 16, 2], ws.Cells[2, 1, 16, 1]);
areaSerie.Header = "Order Value";
areaChart.SetPosition(1, 0, 6, 0);
areaChart.SetSize(1200, 400);
areaChart.Title.Text = "Area Chart";
range.AutoFitColumns(0);
}
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();
}
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);
}
