pruiz / epplus Goto Github PK

Test case:
//_ws is some worksheet.
var deptColumn = 59;
var startRow = 5;
var k = 5;
var i = 70;

var formulaStr = $@"=SUMIF(R5C{deptColumn}:R{i + startRow}C{deptColumn};""="" & RC{deptColumn};R5C{k}:R{i + startRow}C{k})";

What I Expect:
Cell formula should be:
SUMIF(R5C59:R75C59;"=" & RC59;R5C5:R75C5)";

What I get in case _ws.Cells[i,k].FormulaR1C1 = formulaStr;
SUMIF(R5C59:"="R81C59; & RC59;R5C6:R81C6)"

What I get in case _ws.Cells[i,k].Formula = formulaStr;
"=SUMIF(R5C59:R81C59;"=" & RC59;R5C6:R81C6)"

Somehow "=" moved left if I use FormulaR1C1 parser

First of all I'd like to thank you for this great plugin.

In the following file,

EPPlus\ExcelRangeBase.cs

the following method,

public ExcelRangeBase LoadFromCollection<T>(IEnumerable<T> Collection, bool PrintHeaders, TableStyles TableStyle, BindingFlags memberFlags, MemberInfo[] Members)

There is this following code which is throwing an exception when passing a dynamic object T with known MemberInfos.

var type = typeof(T);
if (Members == null)
{
    Members = type.GetProperties(memberFlags);
}
else
{
    foreach (var t in Members)
    {
        if (t.DeclaringType != type)
        {
            throw (new Exception("Supplied properties in parameter Properties must be of the same type as T"));
        }
    }
}

Currently I have commented this code and importing the whole source code in my solution. If the 'else' part is commented out all is working well even with dynamic objects.

Do you think you could comment the else part and update the nuget? if not why do you have to check the types of individual properties? This would not allow dynamic objects to be passed using dynamic keyword.

Please let me know.

Thank you.

When using R1C1 notation, EPPlus transforms the formula and references the original column number minus one. For example, if column in original formula references 3 then EPPlus references column 2. If original formula references column -2, then EPPlus references column -3.

(1) Below we can see the formula from my original excel template:
=IFERROR( INDEX(ResourceCost, MATCH([@Level]&[@[Resource Office]],ResourceCost[Level]&ResourceCost[Office],0),3), 0)

(2) EPPlus transforms the above formula as:
=IF(ISNA(VLOOKUP(B16,ResourceCost,2)), 0, VLOOKUP(B16,ResourceCost,2))

(3) We can see it is referencing 2 when it should reference 3. Manually changing to correct column via excel "fixes" the issue. EPPlus should transform (1) as follows:
=IF(ISNA(VLOOKUP(B16,ResourceCost,2)), 0, VLOOKUP(B16,ResourceCost,2))

Note: ResouceCost, Level, Resource Office are named values i.e. ranges/table/array.

Hello,

We are using EPPlus for export excel, while we are doing this. We observed one issue.

we have column with underscore. when it create ExcelWorksheet it replace _ with space.
e.g.

public class table{
public guid RulesEngineRule_Id;
}
when we export it, we see _ is replace by space.

Please check.

There is a VBA project if I open the file and look but the following method is showing VbaProject as null.

using (ExcelPackage excelpack = new ExcelPackage(utfile))
            {
                if (excelpack.Workbook.VbaProject == null) throw new ApplicationException("Error on getting the project");
                excelpack.Workbook.VbaProject.Protection.SetPassword("EncryptedLockCode");
                excelpack.Save();
            }

I have implemented importer that use PLINQ to improve import.

var result = fileIds.AsParallel()
                .Select(a => a.Guid)
                .Select(GetUserFile)
                //cannot use PLINQ anymore, because their are some 
                //static properties/fields that will be overwritten by each thread
                .ToList()
                .Select(ExtractData)
                .SelectMany(a => a)
                .Where(HasEntry)
                .ToList();

Just want to mention this. The project is not maintaining anymore.

Bug reproduce steps:

1. open any folder on windows, right click menu, [NEW] -> [Microsoft Excel Worksheet], rename it to anything, let's say xxx.xlsx
2. open this xxx.xlsx with EPPlus in c# code, like

try
{
   string file = @".........\ppp.xlsx";
                FileInfo newFile = new FileInfo(file);
                using (ExcelPackage xlPackage = new ExcelPackage(newFile))
                {.....}
}
catch(Excpetion ex)
{}

3. we will get exception
   "Unexpected size (0x0004) for NTFS times extra field at position 0x0000000000000B14"
   detailed stack trace is:
   at OfficeOpenXml.Packaging.Ionic.Zip.ZipEntry.ProcessExtraFieldWindowsTimes(Byte[] buffer, Int32 j, Int16 dataSize, Int64 posn)
   at OfficeOpenXml.Packaging.Ionic.Zip.ZipEntry.ProcessExtraField(Stream s, Int16 extraFieldLength)
   at OfficeOpenXml.Packaging.Ionic.Zip.ZipEntry.ReadHeader(ZipEntry ze, Encoding defaultEncoding)
   at OfficeOpenXml.Packaging.Ionic.Zip.ZipEntry.ReadEntry(ZipContainer zc, Boolean first)
   at Ionic.Zip.ZipInputStream.GetNextEntry()
   at OfficeOpenXml.Packaging.ZipPackage..ctor(Stream stream)
   at OfficeOpenXml.ExcelPackage.ConstructNewFile(String password)
   at OfficeOpenXml.ExcelPackage..ctor(FileInfo newFile)
   at SimpleWinFormTest.Form1.button1_Click(Object sender, EventArgs e) in .......\Form1.cs:line 116
4. that's all
   

Hi Guys,
I have a problem with the DateTime format.
A cell has the following NumberFormat "[$-F800]dddd,\ mmmm\ dd,\ yyyy" but it is not recognized as DateTime.

if (cells.Value is DateTime) { var dateTime = DateTime.Parse(cells.Value.ToString()); }

How can i Get the DataType of Cell?

Best Regards

Severity Code Description Project File Line Source Suppression State
Error NU1002 The dependency EPPlus 4.1.0 does not support framework .NETCoreApp,Version=v1.0.

Hi,

I am using EPPlus in ASP.Net MVC Core 2.0 and trying to create an excel file with password protected. I am using below code:

excelPackage.Encryption.Password = "sddd";
excelPackage.SaveAs(new FileInfo("<filepath>"));

It gives me error as below and unable to save file.

{System.TypeLoadException: Could not load type 'System.Security.Cryptography.RIPEMD160Managed' from assembly 'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.
   at OfficeOpenXml.Encryption.EncryptedPackageHandler.GetHashProvider(EncryptionKeyEncryptor encr)
   at OfficeOpenXml.Encryption.EncryptedPackageHandler.EncryptPackageAgile(Byte[] package, ExcelEncryption encryption)
   at OfficeOpenXml.ExcelPackage.Save()}

Please help me.

Our project is upgraded from 4.0.5 to 4.1.0 and noticed this error
The following code results in exception "row cannot be less than 1"

int xlRow = 18;
using (ExcelPackage xl = new ExcelPackage(pfReport, template))
{
var wb = xl.Workbook;
ExcelWorksheet sht = wb.Worksheets[1];
--> sht.InsertRow(xlRow, 1);
xl.Save();
}

Note that the template file contains text in cells below row 18

When rolling back to version 4.0.5 the code runs normally

Comments: In our case in turned out that it was an invalid NamedRange in the Excel template. You can check this by hitting CTRL+F3 in Excel. Search for #REF! in Value column. After deleting this NamedRange the problem was solved. Error message is misleading because it suggests your parameters are invalid.