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()));
}
}
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()));
}
}
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()));
}
}
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()));
}
}
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()));
}
}
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()));
}
}
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()));
}
}
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()));
}
}
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()));
}
}
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()));
}
}
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()));
}
}
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();
}
}
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()));
}
}
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()));
}
}
