Implementation of some new Formula parser function for EPPlus 5.

Add a new overload to the ExcelPivotTable.Add method to supply a table as source.

EPPlus misses a way to set the TopLeftCell in a worksheet. Add a new property to ExcelWorksheetView

TextBody should be added to Shapes

I have an excel that has some formatting (merged cells, formulas...etc.). You can find a copy of this excel attached. Im restricted with this format as its provided by the client and cannot be changed. Im using the EPPlus (version 4.5.3.3) to populate rows starting from row 8 but Im trying to use the worksheet.InsertRow(8,recordCounter,8) to create a new row for each inserted record and copy all styles as well from the first row, however I keep getting the following exception:
Row cannot be less than 1. Parameter name: value
MyExcel.xlsx

Paragraphs in richtext should line break in the text. Paragraphs can not be added using EPPlus today.

JanKallman/EPPlus#389

This issue will cover some bugs, features with the formula parser of EPPlus (tokenizing and calculation).

• Handle double negators (--) correctly.
• Value matcher should compare DateTime and double
• CountIf does not work properly with multiple conditions.

For example:
MySqlDateTime will return null using
Convert.ToString(mySqlDate)
but a value using
mySqlDate.ToString()

JanKallman/EPPlus#560

JanKallman/EPPlus#464

Setting Cell Text Rotation to 255 should be allowed to align the text vertically

To reproduce:

        [TestMethod]
        public void DeleteIssue()
        {
            using (var p = new ExcelPackage())
            {
                var ws1 = p.Workbook.Worksheets.Add("Sheet1");
                var ws2 = p.Workbook.Worksheets.Add("Sheet2");
                ws1.Cells["A10"].Value = 1;
                ws1.Cells["A11"].Formula = "A10*2";
                ws1.Cells["A12"].Value = 3;
                ws2.Cells["A1"].Formula = "SUM(Sheet1!A10:A12)";
                ws1.DeleteRow(1);
                Assert.AreEqual("A9*2",ws1.Cells["A10"].Formula);
                Assert.AreEqual("SUM(Sheet1!A9:A11)",ws2.Cells["A1"].Formula);
            }
        }

JanKallman/EPPlus#496

DataValidations refering to another worksheet should be placed separatly under the extLst element.

Password protection for a readonly copy of a package can be added using the workbookXml - fileSharing tag
Example:
<fileSharing spinCount="100000" saltValue="Yvt/UWxzbiieUg7qdhlAng==" hashValue="os7WY7CE0OjFKJ4p2bDsl7O8R6JtlEE6tKrHjPEAYwxMB1YYz9MZ9Yj45loZ1sxEjiZ6yEk0iBnY8JvPEKpj8w==" algorithmName="SHA-512" userName="Jan Källman" readOnlyRecommended="1"/>
This can be set in the save dialog in Excel --> Tools --> General options.

Original issue: JanKallman/EPPlus#628

The TokenHandler class calls the static TokenSeparatorHandler.Handle method inside its Handle method

        private void Handle()
        {
            var c = _context.FormulaChars[_tokenIndex];
            Token tokenSeparator;
            if (CharIsTokenSeparator(c, out tokenSeparator))
            {
                if (TokenSeparatorHandler.Handle(c, tokenSeparator, _context, this))
                {
                    return;
                }

The Handle method uses a static collection which contains a MultipleCharSeparatorHandler instance

        private static SeparatorHandler[] _handlers = new SeparatorHandler[]
        { 
            new StringHandler(),
            new BracketHandler(),
            new SheetnameHandler(),
            new MultipleCharSeparatorHandler()
        };

This causes the constructor of MultipleCharSeparatorHandler to use the default TokenSeparatorProvider anyway

        public MultipleCharSeparatorHandler()
            : this(new TokenSeparatorProvider())
        {

        }

This means that it does not use the ITokenSeparatorProvider that was provided initially to the TokenHandler. e.g.

   ITokenSeparatorProvider myTokenSeparatorProvider = new MyTokenSeparatorProvider();

   var tokenFactory = new TokenFactory(myTokenSeparatorProvider, NameValueProvider.Empty, 

   FunctionNameProvider.Empty, false);
   var tokenizer = new SourceCodeTokenizer(tokenFactory, myTokenSeparatorProvider);

Suggested solution:

TokenSeparatorHandler should be non-static and accept a ITokenSeparatorProvider in its constructor to pass on to MultipleCharSeparatorHandler.

TokenHandler should initialize it inside its constructor also passing the ITokenSeparatorProvider
e.g.

         public TokenSeparatorHandler(ITokenSeparatorProvider tokenSeparatorProvider)
	 {
		    _handlers = new SeparatorHandler[]
			{
				new StringHandler(),
				new BracketHandler(),
				new SheetnameHandler(),
				new MultipleCharSeparatorHandler(tokenSeparatorProvider)
			};
	 }

         public TokenHandler(TokenizerContext context, ITokenFactory tokenFactory, 
         ITokenSeparatorProvider tokenProvider)
        {
            _context = context;
            _tokenFactory = tokenFactory;
            _tokenProvider = tokenProvider;
            _tokenSeparatorHandler = new TokenSeparatorHandler(_tokenProvider);

Implementation of some text functions that are missing in the formula engine.

JanKallman/EPPlus#483

EPPlus crashes on load if a workbook or worksheet has more than one defined name with the same name. This should not happen, but libre office can generate duplicate print areas in a package.
JanKallman/EPPlus#625

Row styles are not copied when inserting rows and the row to copied are below the inserted range.

Chart series will from version 5 handles both addresses and arrays. Arrays are handled in the StringLiteralsX, NumberLiteralsX and NumberLiteralsY arrays when a series is set to an Array ( for example {1,2,3} ).

Add support for BringToFront and SendToBack methods on the Drawings Collection.
This can be done by moving the Drawing elements first and last in the wsDr element of the DrawingsXml.

Drawing objects like shapes, pictures and charts are not moved and/or resized when a row or column is inserted/deleted.

The handling of circular references needs a redesign for the following reasons:

• Currently a CircularReferenceException is always thrown when the formula engine detects a circular reference during construction of the dependency chain. We want to move the logic for throwing this exception downstream to the compiler so that it only is thrown when Calculate is called.

• Some functions should be able to ignore if a circular reference occurs.

Add a new enum ePresetMultiSeriesChartStyle for charts with more than one series. Preset Chart styles can differ depending if the chart has a single data serie or multiple data series.
Add a new overload to ChartStyleManager.SetChartStyle with the new enum

I have a worksheet that has been working perfectly for a few years. We just upgraded to 4.5.3.3 and now SUM formulas that contain a reference to a cell in column 'R' are being modified at some point in the writing of the spreadsheet.

I'm using Worksheet.Cells(Row, Column).Address to get the values for my SUM ranges. However, in testing, I'm just hard-coding the values and the same behavior occurs.

Examples:
sum(R16:R35) => sum($16:$35) when I write to the formula of the cell
sum(S16:S35) is correctly written
sum(R16:S16) => causes error and excel removes from spreadsheet. Assuming it is written as sum($16:S16)
sum(Q16:S16) is correctly written
Average(R16:R35) => Average($16:$35)

If I write my formulas to the value property of the cell it is written correctly.

What is so magical about the value of 'R'? Can I escape the value so it works? I cannot exclude that column because our data might required that we use that column.

Looks like our option at this time to roll back to the previous version?

URI's for styles and shared strings are hardcoded by name when fetched (/xl/sharedStrings.xml). Should be fetched by relationshipType.

JanKallman/EPPlus#553

Overwriting a sharedformulas first cell when setting multiple cells of the Cells indexer throws an exception.

JanKallman/EPPlus#506

The Normal style is found using the "Normal" Text instead of using BuildInId=0.

See issue JanKallman/EPPlus#554

Conditional formatting can be associated with a PivotTable.
Set the pivot attribute to 1 on the ConditionalFormatting element.

Original issue reported here: JanKallman/EPPlus#619 and applies to both 4.2 and 5.0.1-beta

When loading a package with cell inline richtext, EPPlus trims whitespaces in the t tag causing corruption of the package.

Refers JanKallman/EPPlus#551

When the validations ExcelFormula is set the Validate() method throws an InvalidOperationException

Its not exactly corrupt, but excel complains when opening.
"We found a problem with some content in yourfile.xlsx. Do you want us to try to recover as much as we can? if you trust the source of this workbook, click yes"
After repairing a window reports
"Repaired Records: String properties from /xl/sharedStrings.xml part (Strings)"
Version 5.0.3 (the current nuget)
It looks similar to 559 on the original git, and seen similar issues when googling but no solution.

I use this code to generate the spreadsheet, and it is when the richtext section is used that the problem occurs. (it generated 18 sections of rich text in that cell)

the two different sharedstring.xml files are attached
sharedstrings.zip

`var text = log.Text.Replace('\t', ' ');
var textCell = worksheet.Cells[rowindex, 4];

                    if (regexHighlight.Any(r => r.IsMatch(text)))
                    {
                        textCell.IsRichText = true;
                        foreach (var s in text.Split(' '))
                        {
                            var highlight = regexHighlight.Any(r => r.IsMatch(s));
                            var richText = textCell.RichText.Add(s);
                            richText.Bold = highlight;
                            richText.Color = highlight ? Color.Red : Color.Black;
                            textCell.RichText.Add(" ");
                        }
                    }
                    else
                    {
                        textCell.Value = log.Text.Replace('\t', ' ');
                    }`

JanKallman/EPPlus#491

Custom properties should not be case insensitive.

JanKallman/EPPlus#508