1

I use an Excel 2016 *.xlsx file as a template and fill some data using EPPlus 4.1.1. In order to do so I use named ranges that I retrieve from the excelPackage with

var namedRanges = excelPackage.Workbook.Names; //ExcelNamedRangeCollection

var namedRange = namedRanges["myRangeName"];
var ws = namedRange.Worksheet;
var numberOfExcelColumns = namedRange.End.Column - namedRange.Start.Column + 1;
var numberOfExcelRows = namedRange.End.Row - namedRange.Start.Row + 1;
var absoluteRowIndex = ...;
var absoluteColumnIndex = ...;
var valueToWrite = ...;
...
ws.Cells[absoluteRowIndex, absoluteColumnIndex].Value=valueToWrite;

While the insertion of the values works fine, I see some unwanted side effects:

  • The default cell template looks as if it has been changed to have a blue background color instead the white background color of the original Excel file.

  • Some formulas of the original Excel file are adapted. I did not change the cells that contain the formulas (e.g. C7) at all. (I just entered some data in the cells that are referenced by the formulas (e.g. F47).)

I did not set any style nor any cell formula. Therefore I guess that this is an EPPlus or Excel 2016 bug or that I use a constellation (e.g. wrong excel file format, excel version) that is not supported.

=>Any suggestions on how to avoid those strange side effects are welcome.

Edit:

I reported a bug at https://github.com/JanKallman/EPPlus/issues/98

enter image description here enter image description here enter image description here

enter image description here enter image description here

Stefan
  • 10,010
  • 7
  • 61
  • 117

2 Answers2

0

A. I installed EPPlus version 4.5.0.1-beta (not available with nuget package manager but with package manager console:

Install-Package EPPlus -Version 4.5.0.1-beta

Also see https://www.nuget.org/packages/EPPlus/4.5.0.1-beta

This seems to resolve the formula issue.

B. The color issue is still present. This might be related to

"Fill cell formatting does not render correctly after adding a header in Page Layout view": https://support.office.com/en-us/article/Fixes-or-workarounds-for-recent-issues-in-Excel-for-Windows-49d932ce-0240-49cf-94df-1587d9d97093

(I am currently not able to update Excel. My current build number is 16.0.4591.1000)

Stefan
  • 10,010
  • 7
  • 61
  • 117
0

The color issue workaround is described in https://github.com/JanKallman/EPPlus/issues/98 . You need to create your own style called "Normal" (the localized Excel name DE "Standard" or CZ "Normální" are not understood by the EPP library).

Ondras
  • 323
  • 3
  • 8