Example #1
0
        static void Main(string[] args)
        {
            try
            {
                //If you use Net.Core, add the conditional complilation symbol "Core" under Project Properties, Build-Tab
                //Sample 3, 4 and 12 uses the Adventureworks database. Enter then name of your SQL server into the variable below...
                //Leave this blank if you don't have access to the Adventureworks database
                string SqlServerName = "";

                //FileInfo templateFile = new FileInfo(@"C:\Users\QP-107\Desktop\test.xlsx");
                FileInfo templateFile = new FileInfo(@"C:\Users\QP-107\Desktop\test - Copy.xlsx");
                //FileInfo saveFile = new FileInfo(@"C:\Users\QP-107\Desktop\test1.xlsx");
                FileInfo saveFile = new FileInfo(@"C:\Users\QP-107\Desktop\test1 - Copy.xlsx");
                using (ExcelPackage package = new ExcelPackage(templateFile, "abc", tempFolder: @"C:\Users\QP-107\Desktop\abc"))
                //, "abc"
                {
                    package.SaveAs(saveFile, "xyz");
                    //, "xyz"
                }
                return;

                // change this line to contain the path to the output folder
                DirectoryInfo outputDir = new DirectoryInfo(@"c:\temp\SampleApp");
                if (!outputDir.Exists)
                {
                    throw new Exception("outputDir does not exist!");
                }

                // Sample 1 - simply creates a new workbook from scratch
                // containing a worksheet that adds a few numbers together
                Console.WriteLine("Running sample 1");
                string output = Sample1.RunSample1(outputDir);
                Console.WriteLine("Sample 1 created: {0}", output);
                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");
                Sample2.RunSample2(output);
                Console.WriteLine();
                output = "";

                if (SqlServerName != "")
                {
                    string connectionStr = string.Format(@"server={0};database=AdventureWorks;Integrated Security=true;", SqlServerName);
                    // Sample 3 - creates a workbook from scratch
                    // This is the same sample as the original Excelpackage, sample 4, but without the template
                    // This sample requires the AdventureWorks database.
                    //Shows how to use Ranges, Styling, Namedstyles and Hyperlinks
                    Console.WriteLine("Running sample 3");
                    output = Sample3.RunSample3(outputDir, connectionStr);
                    Console.WriteLine("Sample 3 created: {0}", output);
                    Console.WriteLine();

                    // Sample 4 - creates a workbook based on a template.
                    // Populates a range with data and set the series of a linechart.
                    // This sample requires the AdventureWorks database.
                    Console.WriteLine("Running sample 4");
                    output = Sample4.RunSample4(connectionStr, new FileInfo("..\\..\\GraphTemplate.xlsx"), outputDir);      //Template path from /bin/debug or /bin/release
                    Console.WriteLine("Sample 4 created: {0}", output);
                    Console.WriteLine();
                }

                //Sample 5
                //Open sample 1 and add a pie chart.
                Console.WriteLine("Running sample 5");
                output = Sample5.RunSample5(outputDir);
                Console.WriteLine("Sample 5 created:", output);
                Console.WriteLine();

                //Sample 6
                //Creates an advanced report on a directory in the filesystem.
                //Parameter 2 is the directory to report. Paramter 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 6");
                output = Sample6.RunSample6(outputDir, new DirectoryInfo(System.Reflection.Assembly.GetEntryAssembly().Location).Parent, 5,
#if Core
                                            true
#else
                                            false
#endif
                                            );
                Console.WriteLine("Sample 6 created:", output);
                Console.WriteLine();

                //Sample 7
                //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 7");
                output = Sample7.RunSample7(outputDir, 65534);
                Console.WriteLine("Sample 7 created:", output);
                Console.WriteLine();

                //Sample 8 - Linq
                //Opens Sample 7 and perform some Linq queries
                Console.WriteLine("Running sample 8-Linq");
                LinqSample.RunLinqSample(outputDir);
                Console.WriteLine();

                //Sample 9 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 9");
                output = Sample9.RunSample9(outputDir);
                Console.WriteLine("Sample 9 created: {0}", output);
                Console.WriteLine();

                //Sample 10 Swedish Quiz : Shows Encryption and workbook and sheet protection.
                Console.WriteLine("Running sample 10");
                Sample10.RunSample10(outputDir);
                Console.WriteLine("Sample 10 created: {0}", outputDir.FullName);
                Console.WriteLine();

                //Sample 11 - Data validation
                Console.WriteLine("Running sample 11");
                output = Sample11.RunSample11(outputDir);
                Console.WriteLine("Sample 11 created {0}", output);
                Console.WriteLine();

                //Sample 12 - Pivottables
                Console.WriteLine("Running sample 12");
                output = Sample12.RunSample12(SqlServerName, outputDir);
                Console.WriteLine("Sample 12 created {0}", output);
                Console.WriteLine();

                //Sample 13 - Shows a few ways to load data (Datatable, IEnumerable and more).
                Console.WriteLine("Running sample 13");
                Sample13.RunSample13(outputDir);
                Console.WriteLine("Sample 13 created {0}", outputDir.Name);
                Console.WriteLine();
                //Sample 14 - Conditional Formatting
                Console.WriteLine("Running sample 14");
                Sample14.RunSample14(outputDir);
                Console.WriteLine("Sample 14 created {0}", outputDir.Name);
                Console.WriteLine();

                //Sample 15 - Shows how to work with macro-enabled workbooks(VBA).
                Console.WriteLine("Running sample 15-VBA");
                Sample15.VBASample(outputDir);
                Console.WriteLine("Sample 15 created {0}", outputDir.Name);
                Console.WriteLine();

                //Sample FormulaCalc - Shows how to calculate formulas in the workbook.
                Console.WriteLine("Running Sample_FormulaCalc");
                Sample_FormulaCalc.RunSampleFormulaCalc();
                Console.WriteLine();

                //Sample AddFormulaFunction - Shows how to add your own implementations of excel functions to EPPlus.
                Console.WriteLine("Running Sample_AddFormulaFunction");
                Sample_AddFormulaFunction.RunSample_AddFormulaFunction();
                Console.WriteLine();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: {0}", ex.Message);
            }
            Console.WriteLine();
            Console.WriteLine("Press the return key to exit...");
            Console.Read();
        }
Example #2
0
        static void Main(string[] args)
        {
            try
            {
                //Sample 3, 4 and 12 uses the Adventureworks database. Enter the connectionstring to the Adventureworks database(2016 CTP3) into the variable below...
                //Leave this blank if you don't have access to the Adventureworks database
                string connectionStr = "";      //for example "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=AdventureWorks2016CTP3;Data Source=MySqlServer"

                //Set the output directory to the SampleApp folder where the app is running from.
                Utils.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 = Sample1.RunSample1();
                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");
                Sample2.RunSample2(sample1Path);
                Console.WriteLine();

                if (connectionStr != "")
                {
                    // Sample 3 - creates a workbook from scratch
                    // This is the same sample as the original Excelpackage, sample 4, but without the template
                    // This sample requires the AdventureWorks database.
                    //Shows how to use Ranges, Styling, Namedstyles and Hyperlinks
                    Console.WriteLine("Running sample 3");
                    var sample3Path = Sample3.RunSample3(connectionStr);
                    Console.WriteLine("Sample 3 created: {0}", sample3Path);
                    Console.WriteLine();

                    // Sample 4 - creates a workbook based on a template.
                    // Populates a range with data and set the series of a linechart.
                    // This sample requires the AdventureWorks database.
                    Console.WriteLine("Running sample 4");
                    var sample4Path = Sample4.RunSample4(connectionStr, new FileInfo($"{AppDomain.CurrentDomain.BaseDirectory}GraphTemplate.xlsx"));      //Template path from /bin/debug or /bin/release
                    Console.WriteLine("Sample 4 created: {0}", sample4Path);
                    Console.WriteLine();
                }

                //Sample 5
                //Open sample 1 and add a pie chart.
                Console.WriteLine("Running sample 5");
                var output = Sample5.RunSample5();
                Console.WriteLine("Sample 5 created:", output);
                Console.WriteLine();

                //Sample 6
                //Creates an advanced report on a directory in the filesystem.
                //Parameter 2 is the directory to report. Paramter 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 6");
                output = Sample6.RunSample6(new DirectoryInfo(System.Reflection.Assembly.GetEntryAssembly().Location).Parent, 5, true);
                Console.WriteLine("Sample 6 created:", output);
                Console.WriteLine();

                //Sample 7
                //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 7");
                output = Sample7.RunSample7(65534);
                Console.WriteLine("Sample 7 created:", output);
                Console.WriteLine();

                //Sample 8 - Linq
                //Opens Sample 7 and perform some Linq queries
                Console.WriteLine("Running sample 8-Linq");
                LinqSample.RunLinqSample();
                Console.WriteLine();

                //Sample 9 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 9");
                output = Sample9.RunSample9();
                Console.WriteLine("Sample 9 created: {0}", output);
                Console.WriteLine();

                //Sample 10 Swedish Quiz : Shows Encryption, workbook- and worksheet protection.
                Console.WriteLine("Running sample 10");
                Sample10.RunSample10();
                Console.WriteLine("Sample 10 created: {0}", Utils.OutputDir.FullName);
                Console.WriteLine();

                //Sample 11 - Data validation
                Console.WriteLine("Running sample 11");
                output = Sample11.RunSample11();
                Console.WriteLine("Sample 11 created {0}", output);
                Console.WriteLine();

                //Sample 12 - Pivottables
                Console.WriteLine("Running sample 12");
                output = Sample12.RunSample12(connectionStr);
                Console.WriteLine("Sample 12 created {0}", output);
                Console.WriteLine();

                //Sample 13 - Shows a few ways to load data (Datatable, IEnumerable and more).
                Console.WriteLine("Running sample 13");
                Sample13.RunSample13();
                Console.WriteLine("Sample 13 created {0}", Utils.OutputDir.Name);
                Console.WriteLine();

                //Sample 14 - Conditional Formatting
                Console.WriteLine("Running sample 14");
                Sample14.RunSample14();
                Console.WriteLine("Sample 14 created {0}", Utils.OutputDir.Name);
                Console.WriteLine();

                //Sample 15 - Shows how to work with macro-enabled workbooks(VBA).
                Console.WriteLine("Running sample 15-VBA");
                Sample15.VBASample();
                Console.WriteLine("Sample 15 created {0}", Utils.OutputDir.Name);
                Console.WriteLine();

                //Sample 16 - Shows how to add sparkline charts.
                Console.WriteLine("Running sample 16-Sparklines");
                Sample16.RunSample16();
                Console.WriteLine("Sample 16 created {0}", Utils.OutputDir.Name);
                Console.WriteLine();

                //Sample FormulaCalc - Shows how to calculate formulas in the workbook.
                Console.WriteLine("Running Sample_FormulaCalc");
                Sample_FormulaCalc.RunSampleFormulaCalc();
                Console.WriteLine();

                //Sample AddFormulaFunction - Shows how to add your own implementations of excel functions to EPPlus.
                Console.WriteLine("Running Sample_AddFormulaFunction");
                Sample_AddFormulaFunction.RunSample_AddFormulaFunction();
                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 {Utils.OutputDir.FullName}");
            Console.ForegroundColor = prevColor;

            Console.WriteLine();
            Console.WriteLine("Press the return key to exit...");
            Console.Read();
        }