public void FormattingSheetsAndColumns()
        {
            using (var package = new ExcelPackage())
            {
                var sheet = package.Workbook.Worksheets.Add("Victor");
                sheet.TabColor = Color.Ivory;

                // Freeze the top row and left 4 columns when scrolling
                sheet.View.FreezePanes(2, 5);

                sheet.View.ShowGridLines = false;
                sheet.View.ShowHeaders   = false;

                //sheet.DeleteColumn();
                //sheet.InsertColumn();

                // Default selected cells when opening the xslx
                sheet.Select("B6");

                var colE = sheet.Column(5);
                //ExcelStyle colStyle = colE.Style; // See FormattingCells
                colE.AutoFit(); // or colE.Width

                // Who likes A's
                sheet.Column(1).Hidden = true;

                package.SaveAs(new FileInfo(BinDir.GetPath()));
            }
        }
        public void BasicUsage()
        {
            using (var package = new ExcelPackage())
            {
                ExcelWorksheet sheet = package.Workbook.Worksheets.Add("MySheet");

                // Setting & getting values
                ExcelRange firstCell = sheet.Cells[1, 1];
                firstCell.Value           = "will it work?";
                sheet.Cells["A2"].Formula = "CONCATENATE(A1,\" ... Ofcourse it will!\")";
                Assert.That(firstCell.Text, Is.EqualTo("will it work?"));

                // Numbers
                var moneyCell = sheet.Cells["A3"];
                moneyCell.Style.Numberformat.Format = "$#,##0.00";
                moneyCell.Value = 15.25M;

                // Easily write any Enumerable to a sheet
                // In this case: All Excel functions implemented by EPPlus
                var funcs = package.Workbook.FormulaParserManager.GetImplementedFunctions()
                            .Select(x => new { FunctionName = x.Key, TypeName = x.Value.GetType().FullName });
                sheet.Cells["A4"].LoadFromCollection(funcs, true);

                // Styling cells
                var someCells = sheet.Cells["A1,A4:B4"];
                someCells.Style.Font.Bold = true;
                someCells.Style.Font.Color.SetColor(Color.Ivory);
                someCells.Style.Fill.PatternType = ExcelFillStyle.Solid;
                someCells.Style.Fill.BackgroundColor.SetColor(Color.Navy);

                sheet.Cells.AutoFitColumns();
                //package.SaveAs(new FileInfo(@"basicUsage.xslx"));
                package.SaveAs(new FileInfo(BinDir.GetPath()));
            }
        }
        public void ExcelPrinting()
        {
            using (var package = new ExcelPackage())
            {
                var sheet = package.Workbook.Worksheets.Add("Printing");
                sheet.Cells["A1"].Value = "Check the print preview (Ctrl+P)";

                var header = sheet.HeaderFooter.OddHeader;
                // &24: Font size
                // &U: Underlined
                // &"": Font name
                header.CenteredText     = "&24&U&\"Arial,Regular Bold\" YourTitle";
                header.RightAlignedText = ExcelHeaderFooter.CurrentDate;
                header.LeftAlignedText  = ExcelHeaderFooter.SheetName;

                ExcelHeaderFooterText footer = sheet.HeaderFooter.OddFooter;
                footer.RightAlignedText = $"Page {ExcelHeaderFooter.PageNumber} of {ExcelHeaderFooter.NumberOfPages}";
                footer.CenteredText     = ExcelHeaderFooter.SheetName;
                footer.LeftAlignedText  = ExcelHeaderFooter.FilePath + ExcelHeaderFooter.FileName;

                sheet.PrinterSettings.RepeatRows    = sheet.Cells["1:2"];
                sheet.PrinterSettings.RepeatColumns = sheet.Cells["A:G"];

                // Change the sheet view
                // (Did not work in LibreOffice5)
                sheet.View.PageLayoutView = true;

                package.SaveAs(new FileInfo(BinDir.GetPath()));
            }
        }
        public void ConditionalFormatting()
        {
            using (var package = new ExcelPackage())
            {
                var sheet = package.Workbook.Worksheets.Add("ConditionalFormatting");

                #region Prepare Data
                sheet.Cells["B4"].Value = 5;
                sheet.Cells["B5"].Value = 10;
                sheet.Cells["B6"].Value = 20;
                sheet.Cells["B7"].Value = 40;
                sheet.Cells["B8"].Value = 50;
                sheet.Cells["B9"].Value = 30;
                #endregion

                ExcelAddress cfAddress1 = new ExcelAddress("B4:B9");
                var          cfRule1    = sheet.ConditionalFormatting.AddTwoColorScale(cfAddress1);

                cfRule1.LowValue.Type  = eExcelConditionalFormattingValueObjectType.Num;
                cfRule1.LowValue.Value = 0;
                cfRule1.LowValue.Color = Color.Green;

                cfRule1.HighValue.Type    = eExcelConditionalFormattingValueObjectType.Formula;
                cfRule1.HighValue.Formula = "MAX(B4:B9)";
                cfRule1.HighValue.Color   = Color.Red;

                cfRule1.StopIfTrue      = true;
                cfRule1.Style.Font.Bold = true;

                package.SaveAs(new FileInfo(BinDir.GetPath()));
            }
        }
Exemple #5
0
        public void DataValidation_IntAndDateTime()
        {
            using (var package = new ExcelPackage())
            {
                var sheet = package.Workbook.Worksheets.Add("intsAndSuch");

                // Integer validation
                IExcelDataValidationInt intValidation = sheet.DataValidations.AddIntegerValidation("A1");
                intValidation.Prompt         = "Value between 1 and 5";
                intValidation.Operator       = ExcelDataValidationOperator.between;
                intValidation.Formula.Value  = 1;
                intValidation.Formula2.Value = 5;

                // DateTime validation
                IExcelDataValidationDateTime dateTimeValidation = sheet.DataValidations.AddDateTimeValidation("A2");
                dateTimeValidation.Prompt        = "A date greater than today";
                dateTimeValidation.Operator      = ExcelDataValidationOperator.greaterThan;
                dateTimeValidation.Formula.Value = DateTime.Now.Date;

                // Time validation
                IExcelDataValidationTime timeValidation = sheet.DataValidations.AddTimeValidation("A3");
                timeValidation.Operator = ExcelDataValidationOperator.greaterThan;
                var time = timeValidation.Formula.Value;
                time.Hour   = 13;
                time.Minute = 30;
                time.Second = 10;

                // Existing validations
                var validations = package.Workbook.Worksheets.SelectMany(sheet1 => sheet1.DataValidations);

                package.SaveAs(new FileInfo(BinDir.GetPath()));
            }
        }
        public void PasswordProtectionFromManualEditing()
        {
            using (var package = new ExcelPackage())
            {
                var sheet = package.Workbook.Worksheets.Add("Secret");

                // Block entire sheet except D5
                sheet.Cells["D5"].Value        = "Can't touch this";
                sheet.Cells["D5"].Style.Locked = false;

                sheet.Protection.AllowDeleteRows = false;
                sheet.Protection.SetPassword("Secret");

                //sheet.Protection.IsProtected = true;

                // Or if you need more serious locking
                //var book = package.Workbook;
                //book.Protection.LockWindows = true;
                //book.Protection.LockStructure = true;
                //book.View.ShowHorizontalScrollBar = false;
                //book.View.ShowVerticalScrollBar = false;
                //book.View.ShowSheetTabs = false;

                package.SaveAs(new FileInfo(BinDir.GetPath()));
            }
        }
Exemple #7
0
 public void TroubleshootingFormulas()
 {
     using (var package = new ExcelPackage())
     {
         var logfile = new FileInfo(BinDir.GetPath("TroubleshootingFormulas.txt"));
         package.Workbook.FormulaParserManager.AttachLogger(logfile);
         package.Workbook.Calculate();
         package.Workbook.FormulaParserManager.DetachLogger();
     }
 }
        public void FormattingCells()
        {
            using (var package = new ExcelPackage())
            {
                var sheet = package.Workbook.Worksheets.Add("Styling");

                // Cells with style
                ExcelFont font = sheet.Cells["A1"].Style.Font;
                sheet.Cells["A1"].Value           = "Bold and proud";
                sheet.Cells["A1"].Style.Font.Name = "Arial";
                font.Bold = true;
                font.Color.SetColor(Color.Green);
                // ExcelFont also has: Size, Italic, Underline, Strike, ...

                sheet.Cells["A3"].Style.Font.SetFromFont(new Font(new FontFamily("Arial"), 15, FontStyle.Strikeout));
                sheet.Cells["A3"].Value = "SetFromFont(Font)";

                // Borders need to be made
                sheet.Cells["A1:A2"].Style.Border.BorderAround(ExcelBorderStyle.Dotted);
                sheet.Cells[5, 5, 9, 8].Style.Border.BorderAround(ExcelBorderStyle.Dotted);

                // Merge cells
                sheet.Cells[5, 5, 9, 8].Merge = true;

                // More style
                sheet.Cells["D14"].Style.ShrinkToFit = true;
                sheet.Cells["D14"].Style.Font.Size   = 24;
                sheet.Cells["D14"].Value             = "Shrinking for fit";

                sheet.Cells["D15"].Style.WrapText = true;
                sheet.Cells["D15"].Value          = "A wrap, yummy!";
                sheet.Cells["D16"].Value          = "No wrap, ouch!";

                // Setting a background color requires setting the PatternType first
                sheet.Cells["F6:G8"].Style.Fill.PatternType = ExcelFillStyle.Solid;
                sheet.Cells["F6:G8"].Style.Fill.BackgroundColor.SetColor(Color.Red);

                // Horizontal Alignment needs a little workaround
                // http://stackoverflow.com/questions/34660560/epplus-isnt-honoring-excelhorizontalalignment-center-or-right
                var centerStyle = package.Workbook.Styles.CreateNamedStyle("Center");
                centerStyle.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                sheet.Cells["B5"].StyleName           = "Center";
                sheet.Cells["B5"].Value = "I'm centered";

                // MIGHT NOT WORK:
                sheet.Cells["B6"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                sheet.Cells["B6"].Value = "I'm not centered? :(";

                // Check for an example of Conditional formatting:
                // https://github.com/JanKallman/EPPlus/wiki/Conditional-formatting

                package.SaveAs(new FileInfo(BinDir.GetPath()));
            }
        }
Exemple #9
0
        public void Math()
        {
            using (var package = new ExcelPackage())
            {
                var sheet = package.Workbook.Worksheets.Add("Math");
                sheet.Cells["A1"].Value = Fox;
                sheet.Cells["B1"].Value = "15,32";
                sheet.Cells["C1"].Value = 15.32;

                // Formatting:
                // =TEXT(1554543,5154; "#.###,00")
                // =FIXED(1554543,5154; 2) - format number (number, decimals) <see cref="QuickTutorial.WritingValues"/>

                // Convert
                sheet.Assert("VALUE(B1)", Is.EqualTo(15.32));
                sheet.Assert("INT(\"15.62\")", Is.EqualTo(15)); // or FLOOR(number, significance) or ROUNDDOWN or TRUNC
                // CEILING(number, significance) or ROUNDUP
                // ROUND(number, significance)

                // ISNUMBER, ISEVEN, ISODD
                // MAX, MIN
                // COUNT(range) - Counts all numeric cell values
                // COUNTA(range) - Counts all non empty cell values
                // COUNTBLANK(range)
                // COUNTIF(range, citeria)
                // COUNTIFS(range1, criteria1, range2, criteria2, ...)

                // Criteria possibilities for IF:
                // A literal, another cell, ">=10", "<>0"
                // "<>"&A1 - not equal to A1
                // "gr?y" - single letter wildcard
                // "cat*" - 0..x wildcard

                // SUM, SUMIF, SUMIFS
                // AVERAGE, AVERAGEIF, AVERAGEIFS

                // ABS(number) - abolute value
                // SIGN(number) - returns -1 or 1
                // PRODUCT(range...) - returns arg1 * arg2 * ...
                // POWER(base, exponent) - Or base^exp. Also: SQRT
                // MOD(divident, divisor) - modulo. Also: QUOTIENT
                // RAND() - between 0 and 1
                // RANDBETWEEN(lowest, highest) - both params inclusive
                // LARGE(range, xth) - returns xth largest number; also SMALL()

                // PI, SIN, COS, ASIN, ASINH, TAN, ATAN, ...
                // EXP, LOG, LOG10, LN
                // MEDIAN, STDEV, RANK, VAR

                sheet.Column(1).Width = 50;
                package.SaveAs(new FileInfo(BinDir.GetPath()));
            }
        }
Exemple #10
0
        public void BasicFormulas()
        {
            using (var package = new ExcelPackage())
            {
                var sheet = package.Workbook.Worksheets.Add("Formula");

                // SetHeaders is an extension method
                sheet.Cells["A1"].SetHeaders("Product", "Quantity", "Price", "Base total", "Discount", "Total", "Special discount", "Payup");
                sheet.Cells["H5"].AddComment("Special discount for our most valued customers", "evil corp");

                // Turn filtering on for the headers
                sheet.Cells[1, 1, 1, sheet.Dimension.End.Column].AutoFilter = true;

                var data = AddThreeRowsDataAndFormat(sheet);

                // Do not start formula with =
                sheet.Cells["A5"].Formula = "COUNTA(A2:A4)";
                // Hide the formula (when the sheet.IsProtected)
                sheet.Cells["A5"].Style.Hidden = true;

                // Total column
                sheet.Cells["D2:D4"].Formula = "B2*C2"; // quantity * price
                Assert.That(sheet.Cells["D2"].FormulaR1C1, Is.EqualTo("RC[-2]*RC[-1]"));
                Assert.That(sheet.Cells["D4"].FormulaR1C1, Is.EqualTo("RC[-2]*RC[-1]"));

                // Total - discount column
                // Calculate formulas before they are available in the sheet
                // (Opening an Excel with Office will do this automatically)
                sheet.Cells["F2:F4"].Formula = "IF(ISBLANK(E2),D2,D2*(1-E2))";
                Assert.That(sheet.Cells["F2"].Text, Is.Empty);
                sheet.Calculate();
                Assert.That(sheet.Cells["F2"].Text, Is.Not.Empty);

                // Total row
                // R1C1 reference style
                sheet.Cells["D5"].FormulaR1C1 = "SUBTOTAL(9,R[-3]C:R[-1]C)"; // total
                Assert.That(sheet.Cells["D5"].Formula, Is.EqualTo("SUBTOTAL(9,D2:D4)"));
                sheet.Cells["F5"].FormulaR1C1 = "SUBTOTAL(9,R[-3]C:R[-1]C)"; // total - discount
                Assert.That(sheet.Cells["F5"].Formula, Is.EqualTo("SUBTOTAL(9,F2:F4)"));

                sheet.Calculate();
                sheet.Cells["H2:H5"].Formula = "F2*(1-$G$5)"; // Pin G5 with $

                // SUBTOTAL(9 = SUM) // 109 = Sum excluding manually hidden rows
                // AVERAGE (1), COUNT (2), COUNTA (3), MAX (4), MIN (5)
                // PRODUCT (6), STDEV (7), STDEVP (8), SUM (9), VAR (10)

                sheet.Cells.AutoFitColumns();
                package.SaveAs(new FileInfo(BinDir.GetPath()));
            }
        }
Exemple #11
0
 public void LoadFromArrays()
 {
     using (var package = new ExcelPackage())
     {
         var sheet = package.Workbook.Worksheets.Add("Arrays");
         var data  = new[]
         {
             new[] { "A1", "B1", "C1" },
             new[] { "A2", "B2", "C3" },
         };
         sheet.Cells["A1"].LoadFromArrays(data);
         package.SaveAs(new FileInfo(BinDir.GetPath()));
     }
 }
Exemple #12
0
        public void LoadFromDataTable()
        {
            // Also: LoadFromDataReader()

            using (var package = new ExcelPackage())
            {
                var sheet = package.Workbook.Worksheets.Add("DataTable");
                var data  = new DataTable();



                sheet.Cells["A1"].LoadFromDataTable(data, true);
                package.SaveAs(new FileInfo(BinDir.GetPath()));
            }
        }
Exemple #13
0
 public void LoadFromCollection()
 {
     using (var package = new ExcelPackage())
     {
         var sheet = package.Workbook.Worksheets.Add("Collection");
         var data  = new[]
         {
             new { Name = "A", Value = 1 },
             new { Name = "B", Value = 2 },
             new { Name = "C", Value = 3 },
         };
         sheet.Cells["A2"].LoadFromCollection(data);
         sheet.Cells["A1"].SetHeaders("Name", "Value");
         package.SaveAs(new FileInfo(BinDir.GetPath()));
     }
 }
Exemple #14
0
        public void DataValidation_FromOtherSheet()
        {
            using (var package = new ExcelPackage())
            {
                var sheet = package.Workbook.Worksheets.Add("Validation");

                var otherSheet = package.Workbook.Worksheets.Add("OtherSheet");
                otherSheet.Cells["A1"].Value = "Kwan";
                otherSheet.Cells["A2"].Value = "Nancy";
                otherSheet.Cells["A3"].Value = "Tonya";

                var list1 = sheet.Cells["C7"].DataValidation.AddListDataValidation();
                list1.Formula.ExcelFormula = "OtherSheet!A1:A4";

                package.SaveAs(new FileInfo(BinDir.GetPath()));
            }
        }
Exemple #15
0
        public void ImplementedFormulaFunctions()
        {
            using (var package = new ExcelPackage())
            {
                var sheet = package.Workbook.Worksheets.Add("Funcs");

                var funcs = package.Workbook.FormulaParserManager.GetImplementedFunctions()
                            .OrderBy(x => x.Value.GetType().FullName)
                            .ThenBy(x => x.Key)
                            .Select(x => new { FunctionName = x.Key, TypeName = x.Value.GetType().FullName, x.Value.IsErrorHandlingFunction, x.Value.IsLookupFuction });

                sheet.Cells.LoadFromCollection(funcs, true);

                sheet.Cells.AutoFitColumns();
                package.SaveAs(new FileInfo(BinDir.GetPath()));
            }
        }
        public void SettingWorkbookProperties()
        {
            using (var package = new ExcelPackage())
            {
                package.Workbook.Properties.Title    = "EPPlus Tutorial Series";
                package.Workbook.Properties.Author   = "Wouter Van Schandevijl";
                package.Workbook.Properties.Comments = "";
                package.Workbook.Properties.Keywords = "";
                package.Workbook.Properties.Category = "";

                package.Workbook.Properties.Company = "itenium";
                package.Workbook.Properties.SetCustomPropertyValue("Checked by", "Jan Källman");

                package.Workbook.Worksheets.Add("Sheet1");
                package.SaveAs(new FileInfo(BinDir.GetPath()));
            }
        }
Exemple #17
0
        public void LoadFromText()
        {
            using (var package = new ExcelPackage())
            {
                var sheet = package.Workbook.Worksheets.Add("CSV");
                var file  = new FileInfo(BinDir.GetPath("LoadFromText.csv"));

                var format = new ExcelTextFormat()
                {
                    Delimiter     = ',',
                    Culture       = CultureInfo.InvariantCulture,
                    TextQualifier = '"'
                                    // EOL, DataTypes, Encoding, SkipLinesBeginning/End
                };
                sheet.Cells["A1"].LoadFromText(file, format);
                package.SaveAs(new FileInfo(BinDir.GetPath()));
            }
        }
        public void SelectingCells()
        {
            using (var package = new ExcelPackage())
            {
                var sheet = package.Workbook.Worksheets.Add("MySheet");

                // One cell
                ExcelRange cellA2     = sheet.Cells["A2"];
                var        alsoCellA2 = sheet.Cells[2, 1];
                Assert.That(cellA2.Address, Is.EqualTo("A2"));
                Assert.That(cellA2.Address, Is.EqualTo(alsoCellA2.Address));

                // Get the column from a cell
                // ExcelRange.Start is the top and left most cell
                Assert.That(cellA2.Start.Column, Is.EqualTo(1));
                // To really get the column: sheet.Column(1)

                // A range
                ExcelRange ranger     = sheet.Cells["A2:C5"];
                var        sameRanger = sheet.Cells[2, 1, 5, 3];
                Assert.That(ranger.Address, Is.EqualTo(sameRanger.Address));

                //sheet.Cells["A1,A4"] // Just A1 and A4
                //sheet.Cells["1:1"] // A row
                //sheet.Cells["A:B"] // Two columns

                // Linq
                var l = sheet.Cells["A1:A5"].Where(range => range.Comment != null);

                // Dimensions used
                Assert.That(sheet.Dimension, Is.Null);

                ranger.Value = "pushing";
                var usedDimensions = sheet.Dimension;
                Assert.That(usedDimensions.Address, Is.EqualTo(ranger.Address));

                // Offset: down 5 rows, right 10 columns
                var movedRanger = ranger.Offset(5, 10);
                Assert.That(movedRanger.Address, Is.EqualTo("K7:M10"));
                movedRanger.Value = "Moved";

                package.SaveAs(new FileInfo(BinDir.GetPath()));
            }
        }
        public void AddingCommentsWithRichText()
        {
            using (var package = new ExcelPackage())
            {
                var sheet = package.Workbook.Worksheets.Add("Rich Comments");

                ExcelComment comment = sheet.Cells["A1"].AddComment("Bold title:\r\n", "evil corp");
                comment.Font.Bold = true;
                comment.AutoFit   = true;

                ExcelRichText rt = comment.RichText.Add("Unbolded subtext");
                rt.Bold = false;

                // A more extensive example can be found in Sample6.cs::AddComments of the official examples project
                // https://github.com/JanKallman/EPPlus/blob/master/SampleApp/Sample6.cs

                package.SaveAs(new FileInfo(BinDir.GetPath()));
            }
        }
Exemple #20
0
        public void TableStyles()
        {
            var data = new[]
            {
                new { Name = "A", Value = 1 },
                new { Name = "B", Value = 2 },
                new { Name = "C", Value = 3 },
            };

            var tableStyles = Enum.GetValues(typeof(TableStyles)).OfType <TableStyles>();

            using (var package = new ExcelPackage())
                foreach (var tableStyle in tableStyles)
                {
                    var sheet = package.Workbook.Worksheets.Add(tableStyle.ToString());
                    sheet.Cells["A1"].LoadFromCollection(data, true, tableStyle);
                    package.SaveAs(new FileInfo(BinDir.GetPath()));
                }
        }
        public void LoadingAndSaving()
        {
            // Open an existing Excel
            // Or if the file does not exist, create a new one
            using (var package = new ExcelPackage(new FileInfo(BinDir.GetPath()), "optionalPassword"))
                using (var basicUsageExcel = File.Open(BinDir.GetPath(nameof(BasicUsage)), FileMode.Open))
                {
                    var sheet = package.Workbook.Worksheets.Add("Sheet1");
                    sheet.Cells["D1"].Value = "Everything in the package will be overwritten";
                    sheet.Cells["D2"].Value = "by the package.Load() below!!!";

                    // Loads the worksheets from BasicUsage
                    // (MySheet with A1 = will it work?)
                    package.Load(basicUsageExcel);

                    // See 3-Import for more loading techniques

                    package.Compression = CompressionLevel.BestSpeed;
                    package.Save("optionalPassword");
                    //package.SaveAs(FileInfo / Stream)
                    //Byte[] p = package.GetAsByteArray();
                }
        }
Exemple #22
0
        public void DataValidation_DropDownComboCell()
        {
            using (var package = new ExcelPackage())
            {
                var sheet = package.Workbook.Worksheets.Add("Validation");

                var list1 = sheet.Cells["C7"].DataValidation.AddListDataValidation();
                list1.Formula.Values.Add("Apples");
                list1.Formula.Values.Add("Oranges");
                list1.Formula.Values.Add("Lemons");

                list1.ShowErrorMessage = true;
                list1.Error            = "We only have those available :(";

                list1.ShowInputMessage = true;
                list1.PromptTitle      = "Choose your juice";
                list1.Prompt           = "Apples, oranges or lemons?";

                list1.AllowBlank = true;

                sheet.Cells["C7"].Value = "Pick";
                package.SaveAs(new FileInfo(BinDir.GetPath()));
            }
        }
        public void WritingValues()
        {
            Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
            using (var package = new ExcelPackage())
            {
                var sheet = package.Workbook.Worksheets.Add("MySheet");

                // Format as text
                sheet.Cells["A1"].Style.Numberformat.Format = "@";

                // Numbers
                sheet.SetValue("A1", "Numbers");
                Assert.That(sheet.GetValue <string>(1, 1), Is.EqualTo("Numbers"));
                sheet.Cells["B1"].Value = 15.32;
                sheet.Cells["B1"].Style.Numberformat.Format = "#,##0.00";
                // Alternatively: sheet.Cells["B1"].Formula = "FIXED(B1; 2)";
                Assert.That(sheet.Cells["B1"].Text, Is.EqualTo("15.32"));

                // Percentage
                sheet.Cells["C1"].Value = 0.5;
                sheet.Cells["C1"].Style.Numberformat.Format = "0%";
                Assert.That(sheet.Cells["C1"].Text, Is.EqualTo("50%"));

                // Money
                sheet.Cells["A2"].Value    = "Moneyz";
                sheet.Cells["B2,D2"].Value = 15000.23D;
                sheet.Cells["C2,E2"].Value = -2000.50D;
                sheet.Cells["B2:C2"].Style.Numberformat.Format = "#,##0.00 [$€-813];[RED]-#,##0.00 [$€-813]";
                sheet.Cells["D2:E2"].Style.Numberformat.Format = "[$$-409]#,##0";

                // DateTime
                sheet.Cells["A3"].Value = "Timey Wimey";
                sheet.Cells["B3"].Style.Numberformat.Format = "yyyy-mm-dd";
                sheet.Cells["B3"].Formula = $"=DATE({DateTime.Now:yyyy,MM,dd})";
                sheet.Cells["C3"].Style.Numberformat.Format = DateTimeFormatInfo.CurrentInfo.FullDateTimePattern;
                sheet.Cells["C3"].Value = DateTime.Now;
                sheet.Cells["D3"].Style.Numberformat.Format = "dd/MM/yyyy HH:mm";
                sheet.Cells["D3"].Value = DateTime.Now;

                // Write a sortable date
                //TimeSpan diff = new DateTime(1900, 1, 1) - DateTime.Now;
                //sheet.Cells["D3"].Value = diff;
                // TODO: Still need to check which would be the best/most convenient way to handle dates


                // An external hyperlink
                sheet.Cells["C24"].Hyperlink = new Uri("https://itenium.be", UriKind.Absolute);
                sheet.Cells["C24"].Value     = "Visit us";
                sheet.Cells["C24"].Style.Font.Color.SetColor(Color.Blue);
                sheet.Cells["C24"].Style.Font.UnderLine = true;

                //sheet.Cells["C25"].Formula = "HYPERLINK(\"mailto:[email protected]\",\"Contact support\")";
                //package.Workbook.Properties.HyperlinkBase = new Uri("");

                // An internal hyperlink
                package.Workbook.Worksheets.Add("Data");
                sheet.Cells["C26"].Hyperlink = new ExcelHyperLink("Data!A1", "Goto data sheet");

                sheet.Cells["Z1"].Clear();

                sheet.Cells.AutoFitColumns();
                package.SaveAs(new FileInfo(BinDir.GetPath()));
            }
        }
Exemple #24
0
        public void StringManipulation()
        {
            using (var package = new ExcelPackage())
            {
                var sheet = package.Workbook.Worksheets.Add("StringManipulation");
                sheet.Cells["A1"].Value = Fox;

                sheet.Cells["A2"].Value   = "=LEN(A1)";
                sheet.Cells["B2"].Formula = "LEN(A1)"; // Formulas do not start with =
                sheet.Where("B2", Is.EqualTo(Fox.Length));

                sheet.Cells["A3"].Value   = "=UPPER(A1)";
                sheet.Cells["B3"].Formula = "UPPER(A1)";  // also LOWER()
                sheet.Where("B3", Is.EqualTo(Fox.ToUpper()));
                sheet.Cells["C3"].Formula = "PROPER(A1)"; // = ToTitleCase()
                sheet.Where("C3", Is.EqualTo("The Quick Brown Fox Jumps Over The Lazy Dog"));

                // LibreOffice uses ; as function argument separation
                sheet.Cells["A4"].Value = "=LEFT(A1; 3)";
                // But EPPlus works with ,
                sheet.Cells["B4"].Formula = "LEFT(A1, 3)"; // also RIGHT()
                sheet.Where("B4", Is.EqualTo(Fox.Substring(0, 3)));

                sheet.Cells["A5"].Value   = "=MID(A1; 5; 5)";
                sheet.Cells["B5"].Formula = "MID(A1, 5, 5)"; // !! String indexes are 1 based !!
                sheet.Where("B5", Is.EqualTo(Fox.Substring(4, 5)));

                sheet.Cells["A6"].Value   = "=REPLACE(A1; 1; 3; \"A\")"; // Replace text with indexes
                sheet.Cells["B6"].Formula = "REPLACE(A1, 1, 3, \"A\")";
                sheet.Where("B6", Is.EqualTo("A quick brown fox jumps over the lazy dog"));

                sheet.Cells["A7"].Value   = "=SUBSTITUTE(LOWER(A1); \"the\"; \"a\")"; // Replace text (case sensitive - but LOWER(A1))
                sheet.Cells["B7"].Formula = "SUBSTITUTE(LOWER(A1), \"the\", \"a\")";
                sheet.Where("B7", Is.EqualTo(Regex.Replace(Fox, "the", "a", RegexOptions.IgnoreCase)));

                sheet.Cells["A8"].Value   = "=REPT(A1; 1; 3; \"A\")"; // Repeat
                sheet.Cells["B8"].Formula = "REPT(\"A\", 3)";
                sheet.Where("B8", Is.EqualTo("AAA"));

                sheet.Cells["A9"].Value   = "=CONCATENATE(A1; \" over and\"; \" over again\")"; // accepts x parameters
                sheet.Cells["B9"].Formula = "CONCATENATE(A1, \" over and over again\")";        // or use & to concatenate
                sheet.Where("B9", Is.EqualTo(Fox + " over and over again"));

                // FIND/SEARCH: return index of needle in text
                sheet.Cells["A10"].Value   = "=FIND(\"fox\"; A1)"; // find_text, text, startingPosition (case sensitive)
                sheet.Cells["B10"].Formula = "FIND(\"fox\", A1)";
                sheet.Where("B10", Is.EqualTo(Fox.IndexOf("fox") + 1));
                sheet.Cells["C10"].Formula = "FIND(\"FOX\", A1)";   // Not found: #VALUE!
                sheet.Cells["D10"].Formula = "SEARCH(\"FOX\", A1)"; // not case sensitive
                sheet.Where("D10", Is.EqualTo(Fox.IndexOf("fox") + 1));

                // returns the text itself, if it is a string
                sheet.Cells["A11"].Value   = "=T(A1)"; // typeof A1 === "string" ? A1 : ""
                sheet.Cells["B11"].Formula = "T(A1)";
                sheet.Where("B11", Is.EqualTo(Fox));

                // returns true if the strings are the same (case sensitive)
                sheet.Cells["A12"].Value   = $"=EXACT(A1, \"{Fox}\")";
                sheet.Cells["B12"].Formula = $"EXACT(A1; \"{Fox}\")";;
                sheet.Where("B12", Is.EqualTo(true));

                // HYPERLINK: Also see <see cref="QuickTutorial.WritingValues"/> for a hyperlink?

                sheet.Column(1).Width = 50;
                package.SaveAs(new FileInfo(BinDir.GetPath()));
            }
        }