epplussoftware/epplus

EPPlus-Excel spreadsheets for .NET

epplus's Issues

Packages with a prefix for the main xml namespace for a worksheet gets corrupted.

Refers JanKallman/EPPlus#551

InsertRow / DeleteRow / InsertColumn / DeleteColum do not update formulas on other sheets

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

Formulas containing 'R' column cells being re-written with '$'

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?

Table source overload to PivotTable.Add method

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

Overwriting a shared formulas first cell causes an exception

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

Custom document properties are case insensitive.

Custom properties should not be case insensitive.

Add pivot attribute to conditional formatting

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

RichText causes xml "corruption"

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', ' ');
                    }`

Conditional formatting styles crashes when copying a worksheet from another package

JanKallman/EPPlus#464

Drawing objects are not moved and/or resized when a row or column is inserted/deleted

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

Setting a cell value to a char datatype will result i "0" when saved

JanKallman/EPPlus#483

Workbooks with Empty series for Scatter- and Doughnut- charts crashes on load.

Pictures-wmf and emf are converted to png

JanKallman/EPPlus#496

.NET Core 3/foreach-loops generate unseen data

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

Structs in a cell can result in a null value when converted to string on save

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

Can not read gradient cell styles properties

JanKallman/EPPlus#506

Add support for fileSharing tag to protect workbooks (set readonly).

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.

TextRotation 255 to adjust text vertically

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

Formula parser fixes

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.

Rich text flag and formulas are not cleared when setting a value by an array

JanKallman/EPPlus#491

Chart series validation

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} ).

TokenHandler uses static TokenSeparatorHandler which ignores custom ITokenSeparatorProvider

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

The Normal style does not work correctly if not named Normal

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

Support for BringToFront and SendToBack on the ExcelWorksheet.DrawingsCollection

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.

Handling of worksheet extLst element for Data Validations.

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

ExcelRangeBase.AutofitColumns() unhides hidden columns

Datavalidations throws InvalidOperationException on CustomValidation

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

Implement new text functions

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

Bug: Sheet with rich text inline string can not handle whitespaces only

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

Row styles are not copied when inserting rows.

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

Pivottables with SubTotalFunctions set to None causes crash

JanKallman/EPPlus#389

Row cannot be less than 1. Parameter name: value

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

Add TextBody properties to ExcelShapeBase

TextBody should be added to Shapes

RichText on drawings do not handle paragraphs correctly

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

URI for sharedstrings and styles part are not fetched by RelationshipType

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

ExcelWorksheet.Copy corrupts package if a relationship to drawing.xml with no drawings exists

Pivot table referencing table gets corrupted on save

JanKallman/EPPlus#508

ExcelRangeBase.Copy sets full row and full column addresses to #REF!

Add async await and stream support for AddPicture method

misc new functions

Implementation of some new Formula parser function for EPPlus 5.

Workbooks with Pivottables fail to open

Add TopLeftCell to ExcelWorksheetView

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

Handling of circular references

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.

Copying formulas in ranges can lead to invalid #REF! for fixed addresses.

See issue JanKallman/EPPlus#554

FileStream for compound documents are not closed.

Table column names are validated on a non encoded value

JanKallman/EPPlus#553

StackOverflowException when calling Clear on a comma separated Range

JanKallman/EPPlus#560

EPPlus crashes on load if package contains duplicate defined names

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

Chart styles differ for single and multi series charts

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

Tables do not load TableStyle property for an opened package