First, thanks for the awesome project.
I try this code with couple different images, all the same result

          using (var p = new ExcelPackage())
            {
                var ws = p.Workbook.Worksheets.Add("1");
                ws.Cells.Style.Font.Size = 9; // set 11 or more, picture will be fine
                var pic = ws.Drawings.AddPicture("1", Image.FromFile("1.jpg"));
                pic.SetSize(50);
                p.SaveAs(new System.IO.FileInfo("1.xlsx"));
            }

ws.Cells.Style.Font.Size = 9; // set 11 or more, picture will be fine

I didn't see where this project was only for v4.6.1 of the Standard .NET Framework so I get the following warning in Error List.

Severity Code Description Project File Line Suppression State
Warning NU1701 Package 'EPPlus 4.1.1' was restored using '.NETFramework,Version=v4.6.1' instead of the project target framework '.NETCoreApp,Version=v2.0'. This package may not be fully compatible with your project. myproject.csproj 1

The library seems to work, at least for what I am doing in ASPNET Core 2, other than this warning in the error section. Can you update it so that the target works with Core 2?

Whenever I use .LoadFromCollection(...) with a dynamic type, the exception “Index was outside the bounds of the array” is thrown. Is there any fix for this, yet?

Is possible to change wiki permissions to allow us to contribute to it?

Just wanted to know if a particular scenario is supported: the query returns no data but you still want to output the column headers in the sheet. I have confirmed my DataTable contains the necessary column metadata but the sheet that is produced is entirely empty (with no column headers).

I am using code similar to :worksheet.Cells["A1"].LoadFromDataTable(data, true); (where the "data" is my datatable).

Thank you.

When you try to access to the Text properties of the cell A1 (in MonoCell.xlsx), there is an exception.

It seems to be caused by this code into the ExcelNumberFormatXml class.

        internal static void AddBuildIn(XmlNamespaceManager NameSpaceManager, ExcelStyleCollection<ExcelNumberFormatXml> NumberFormats)
        {
...
            NumberFormats.Add("#,##0.00;(#,##0.00)", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 39, Format = "#,##0.00;(#,##0.00)" });
            NumberFormats.Add("#,##0.00;[Red](#,##0.00)", new ExcelNumberFormatXml(NameSpaceManager, true) { NumFmtId = 40, Format = "#,##0.00;[Red](#,#)" });

...
            NumberFormats.NextId = 164; //Start for custom formats.
        }

Strings of the format 40 has to be equal, like for the format 39.

Regards and thanks for your job,

Pierre Epinat

Exception is raised when ExcelPivotTable constructor's name parameter contains invalid xml symbols (&, <, >).

This happens because ExcelPivotTable constructor calls ExcelPivotTable.GetStartXml, which simply inserts passed name into xml string.

Sample code:

using OfficeOpenXml;
using System.IO;

public static class Bug
{
    public static void Run(DirectoryInfo outputDir)
    {
        var filename = new FileInfo(Path.Combine(outputDir.FullName, "bug.xlsx"));
        using (ExcelPackage pck = new ExcelPackage(filename))
        {
            var data = pck.Workbook.Worksheets.Add("data");
            data.Cells["A1"].Value = "Product";
            data.Cells["B1"].Value = "Quantity";
            data.Cells["A2"].Value = "Nails";
            data.Cells["B2"].Value = 37;
            data.Cells["A3"].Value = "Hammer";
            data.Cells["B3"].Value = 5;
            data.Cells["A4"].Value = "Saw";
            data.Cells["B4"].Value = 12;

            var dataRange = data.Cells["A1:B4"];

            var pivot = pck.Workbook.Worksheets.Add("pivot");
            var pivotTable = pivot.PivotTables.Add(pivot.Cells["A1"], dataRange, "a&b");

            pck.Save();
        }
    }
}

I have an Excel file which I tried to load and Following error Occured related to the EPPlus while trying to access the package Worksheet immediately after load.

at System.ThrowHelper.ThrowArgumentException(ExceptionResource resource)
at System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add)
at OfficeOpenXml.ExcelNamedRangeCollection.AddName(String Name, ExcelNamedRange item)
at OfficeOpenXml.ExcelNamedRangeCollection.Add(String Name, ExcelRangeBase Range)
at OfficeOpenXml.ExcelWorkbook.GetDefinedNames()
at OfficeOpenXml.ExcelPackage.get_Workbook()
Error Text was: An item with the same key has already been added.

the file that was being loaded in the Excepackage via Stream (public ExcelPackage(Stream newStream);) and I instantly tried to access the Worksheet. When I debugged it with F10 (Visual Studio), It didn't trigger the error. So The Loading might be taking some time and locad data might be returning before it completely loads or something like that.

Sample Error File.xlsx
File being loaded contains sensitive data so could not share it... When I tried to Edit the sheet... the error could not be reproduced with new sheet. I am Attaching the sheet with changed data (could not reproduce error with it, I can't send original sheet as its data is not my property).

If you could not detect the issue.. Let me know.. I will try harder to generate a sample error excel file for this...

Note: Please don't file an issue to ask a support related question. We are a small team and can't handle all questions. Questions are referred to https://stackoverflow.com/questions/tagged/epplus
If you have a bug, request of a new feature, make sure you follow these guidelines
[] Write a detailed description of your issue.
[] Attach a test to reproduce your issue, if it is a bug or unexpected behaviour. This is very important.
[] If your issue requires a template xlsx file to be reproduced, make sure that you attach it to the issue.

Reading array formulas is not yet supported (according to the comment in LoadCells(Xmlreader) at line 1358 else if (t == "array")), but there is some basic support.

Unfortunately, however, at this point there is no way to recognize that a formula was originally an array formula. I propose to solve this by setting the CellFlags.ArrayFormula flag for the relevant cell.

PR follows.

In the first example code of the Getting Started page on the wiki:

ws.Cells["A1"] = "This is cell A1";

This doesn't compile: Property or indexer 'ExcelRange.this[string]' cannot be assigned to -- it is read only
Maybe instead it should read:

ws.Cells["A1"].Value = "This is cell A1";

I'm not sure though, because I'm just Getting Started with the library.

It appears that the string part of the TEXT operation is uppercased, meaning that the dd/mm/yyyy is evaluated as DD/MM/YYYY which results in the MM being interpreted as minutes rather than month.

To reproduce create a sheet with =NOW() in A1, and =TEXT(A1,"dd/mm/yyyy") in another cell, and cause the cells to be calculated cell.Calculate()

Pls add code in file ExcelHeaderFooter.cs for support flag Headers and Footers scale with document

const string scaleWithDocPath = "@scaleWithDoc";

public bool scaleWithDoc
{
get
{
return GetXmlNodeBool(scaleWithDocPath);
}
set
{
SetXmlNodeString(scaleWithDocPath, value ? "1" : "0");
}
}

Hi,

EPPlus raise an Exception if issue.xlsx file is saved with this code :

        FileInfo issueFile = new FileInfo(@"D:\issue.xlsx");

        ExcelPackage pck = new ExcelPackage(issueFile);
        
        pck.SaveAs(new FileInfo(@"D:\Test.xlsx"));

Looking the source code, uncommenting this patch (in the file OfficeOpenXml.Style.XmlAccess.ExcelFontXml.cs) seems to fix the issue.

    private static float GetHeightByName(string name, float size)
    {
        if (FontSize.FontHeights[name].ContainsKey(size))
        {
            return FontSize.FontHeights[name][size].Height;
        }
        else
        {
            float min = -1, max = 500;
            foreach (var h in FontSize.FontHeights[name])
            {
                if (min < h.Key && h.Key < size)
                {
                    min = h.Key;
                }
                if (max > h.Key && h.Key > size)
                {
                    max = h.Key;
                }
            }
            //if( min < 0 )
            //{
            //    min = FontSize.FontHeights[name].Min(f => f.Key);
            //}
            if (min == max)
            {
                return Convert.ToSingle(FontSize.FontHeights[name][min].Height);
            }
            else
            {
                return Convert.ToSingle(FontSize.FontHeights[name][min].Height  + (FontSize.FontHeights[name][max].Height - FontSize.FontHeights[name][min].Height) * ((size - min) / (max - min)));
            }
        }
    }

BTW, thank you for this package,

Pierre

I am testing the pre-release 4.1.1.0 dll in a C++ project and am getting the build error below (worked fine with 4.1.0.0)

1>c:\path...\epplus.dll : error C3252: 'CellStore::Finalize' : cannot reduce accessibility of a virtual method in a managed type
1> This diagnostic occurred while importing type 'CellStore ' from assembly 'EPPlus, Version=4.1.1.0, Culture=neutral, PublicKeyToken=ea159fdaa78159a1'.
1> This diagnostic occurred while importing type 'OfficeOpenXml::ExcelWorksheet ' from assembly 'EPPlus, Version=4.1.1.0, Culture=neutral, PublicKeyToken=ea159fdaa78159a1'.
1>....\excel.cpp(265): error C3252: 'CellStore::Finalize' : cannot reduce accessibility of a virtual method in a managed type
1> with
1> [
1> T=OfficeOpenXml::ExcelCoreValue
1> ]
1> ....\excel.cpp(265) : see reference to class generic instantiation 'CellStore' being compiled
1> with
1> [
1> T=OfficeOpenXml::ExcelCoreValue
1> ]
1> This diagnostic occurred while importing type 'OfficeOpenXml::ExcelWorksheet ' from assembly 'EPPlus, Version=4.1.1.0, Culture=neutral, PublicKeyToken=ea159fdaa78159a1'.

The line in question is testing a reference to ExcelWorksheet with nullptr:

bool CreateWorksheet (ExcelWorksheet^ %ws) {
if (ws != nullptr) { <---- Error C3252
do stuff to ws...
}
}

The error goes away if CellStore class is changed from internal to public. I hope this can get into next release, or whatever remaining C++ folks out there will be vexed!

I got Error "Requested value 'baseline' was not found." while saving XLSX file after editing file.
The error is because "baseline" is not available in enum ExcelVerticalAlignmentFont. I have attached screenshot for error line.
I observe this error occurs only while editing xlsx file created by some other library.

public enum ExcelVerticalAlignmentFont
{
    None,
    Subscript,
    Superscript
}

Hello EPPLUS team,

I am having an issue while copying a ExcelWorkSheet and the corresponding CodeModule of the copied sheet in 4.1 EPPLUS version.

I have an excel file with macro's (.xlsm) and I use this excel file as a template to create another excel file.

I open the excel template (.xlsm) file and fetch some data from a database which needs to be written in new file.

Some time I need to copy a worksheet in template file, this is where the 'WorkSheets.Add()' comes in.

var newSheet = workbook.Worksheets.Add("someName", templateSheet);

After copying the template sheet and writing data on it, I am experiencing problems with VBA code. When I try to open the new excel file, it gives me below error.

The Visual Basic for applications (VBA) macros in this workbook are corrupted and have been deleted. The macro corruption most likely exists in the current file.

Below is the code from 'WorkSheets.Add("someName", templateSheet)' method:
//Copy the VBA code #if !MONO if (_pck.Workbook.VbaProject != null) { var name = _pck.Workbook.VbaProject.GetModuleNameFromWorksheet(added); _pck.Workbook.VbaProject.Modules.Add(new ExcelVBAModule(added.CodeNameChange) { Name = name, Code = Copy.CodeModule.Code, Attributes = _pck.Workbook.VbaProject.GetDocumentAttributes(Name, "0{00020820-0000-0000-C000-000000000046}"), Type = eModuleType.Document, HelpContext = 0 }); Copy.CodeModuleName = name; } #endif

I am not able to understand why in the last line epplus is changing the code module name of template sheet instead of newly added sheet?

Copy.CodeModuleName = name;

Is this a bug which causes the VBA code to be corrupted?

Also, when trying to change the code module name through below code

templateSheet.CodeModule.Name = "oldName";

causes templateSheet.CodeModule set to null.

Please look into it and resolve it.

Thanks,
Prameet

When trying to load password protected file exception is thrown with message "Unable to read beyond the end of the stream."
This is because in ExcelPackage.cs file, line 1138 when DecryptPackage is called encrStream is at the end (CopyStream caused position to move to the end).
Because of that Read in CompundDocumentFile fails.
Moving encrStream to beginning before calling DecryptPackage fixes problem and file is decrypted correctly.

                Stream encrStream = new MemoryStream();
                CopyStream(input, ref encrStream);
                EncryptedPackageHandler eph = new EncryptedPackageHandler();
                Encryption.Password = Password;
                if (encrStream.CanSeek)
                    encrStream.Seek(0, SeekOrigin.Begin);
                ms = eph.DecryptPackage((MemoryStream)encrStream, Encryption);

Hi ,

We are able to generate pivot table successfully using the following code
`
ExcelWorksheet OneWeekPivotSheet = objExcelPackage.Workbook.Worksheets.Add("Test");

                    var OneWeekTable = OneWeekPivotSheet.PivotTables.Add(OneWeekPivotSheet.Cells["A3"], dataRange, "table ");

                    //The page field
                    OneWeekTable.PageFields.Add(OneWeekTable.Fields["modifiedDate"]);
                    OneWeekTable.UseAutoFormatting = true;
                    OneWeekTable.ShowMemberPropertyTips = false;

                    //The label row field
                    OneWeekTable.RowFields.Add(OneWeekTable.Fields["person"]);
                    var bookingStatuscolumnField = OneWeekTable.ColumnFields.Add(OneWeekTable.Fields["status"]);

                    var BookingStatus = OneWeekTable.DataFields.Add(OneWeekTable.Fields["status"]);
                    BookingStatus.Function = OfficeOpenXml.Table.PivotTable.DataFieldFunctions.Count;
                    BookingStatus.Name = "Distinct count of statuses";

`
Now I want to select multiple filter items inside modifiedDate field as shown in the below screenshot

The static method ExcelCellBase.IsValidAddress(string address) accepts as a valid Range address the following strings:

1. $A12:XY1:3
2. $A12:X$Y$13

for the first string the problem is that there is no check if the chracter ':' that is found is the only one.
I would suggest the following solution:
near line 763 change the following code:
else if (address[i] == ':') { isSecond=true; }
to
else if (address[i] == ':') { if (isSecond) return false; isSecond=true; }

For the second string more checks must be added before deciding that it is safe to ignore the '$' character.

Best regards,
Ivan Temelkov

Using version 4.0.3 I used to use a template excel file and reproduce a new file. Since I updated to version >4.1.0 or higher, the output file is resulting in a corrupted file.

using (ExcelPackage package = new ExcelPackage(templateFile)) { bytes = package.GetAsByteArray(); }

Hi,

I need to use the ConditionalFormatting.eExcelconditionalFormatting3IconsSetType.Arrows but I can't get exactly what I want.
I just need this:

• Green arrows if >0
• Orange arrows if == 0
• Red arrows if < 0

In XML this is what I get from Excel if I set the right settings:

   <conditionalFormatting sqref="I33 I6 I20 I15 I8">
      <cfRule type="iconSet" priority="7">
         <iconSet iconSet="3Arrows">
            <cfvo type="percent" val="0" />
            <cfvo type="num" val="0" />
            <cfvo type="num" val="0" gte="0" />
         </iconSet>
      </cfRule>
   </conditionalFormatting>

I think the attribute gte is missing from the EPPlus Library (the property "Operator" is missing with 2 enum: Greater Than OR Greater Than Or Equal To)

Thanks a lot for your great work !

I have C# code that inserts 4000 rows into an Excel worksheet. EPPlus version 4.0.4 worked well.
But when I upgraded to 4.1.0, the performance was very very slow.

Thanks for an excellent product.
However when I add more than one ProtectedRange to a worksheet, only the last added range is in the resulting file. I have attached a sample-solution that can re-produce the bad behavior. After adding range2 it looks like the name of the range1 object is modified to the name of range2.
ConsoleApp4.zip

If you have a shared formula in a column and you try to update a range where the end row is larger than that of the shared formula, you get an address out of range exception that is caused by ExcelRangeBase.SplitFormula.

This problem can be simply replayed with the following code:

var package = new ExcelPackage();
var sheet = package.Workbook.Worksheets.Add("Test");
sheet.Cells["A2:A4"].Formula = "NOW()";
// The line below will cause an exception.
sheet.Cells["A2:A5"].Value = null;

I noticed that the current .NET core beta targets netcoreapp2.0, instead of the preferred netstandard2.0 (See https://stackoverflow.com/questions/45867239/advantages-of-netcoreapp2-0-vs-netstandard2-0-for-a-library-project )

Is it not possible to target the more generic library instead? Or even .netstandard < 2?

Should work cross platform

Hi there,
I noticed EPPlus creates corrupt excel files when starting from an existing Excel template.

Steps to reproduce

• Install latest office 365 (office 2016)

• I am using: 1705 (Build 8201.2193 CLick-to-Run) / Deferred Channel

• Create folder c:\temp

• Right click in windows explorer in c:\temp empty space, New file -> Microsoft Excel file

• Name file "template.xlsx"
  Here is the blank file I then get:
  template.xlsx

• Modify Sample12.cs as following:

string fileInput = @"c:\temp\template.xlsx";
string fileOutput = @"c:\temp\output.xlsx";
File.Copy(fileInput, fileOutput);
FileInfo newFile = new FileInfo(fileOutput);
using (ExcelPackage pck = new ExcelPackage(newFile))
{
<snip>
pck.Save();
}
return fileOutput;

• Run sample application

• Try to open c:\temp\output.xlsx

• Excel will say file is corrupted
  Here is the corrupt file I'm getting
  output.xlsx

Workaround:

• Take Sample12.xlsx from the default sample app without modifications
• Copy the worksheets from your Excel 2016 template to the Sample12.xlsx file
• Save using Excel
• Use that as your new template

We recently upgraded our EPPlus to latest from 4.0.4, and the library seems to have introduced a regression.
It happens when deleting a row that has a comment in it.

System.NullReferenceException : Object reference not set to an instance of an object.
   at OfficeOpenXml.ExcelCommentCollection.Delete(Int32 fromRow, Int32 fromCol, Int32 rows, Int32 columns)
   at OfficeOpenXml.ExcelWorksheet.DeleteRow(Int32 rowFrom, Int32 rows)

Minimal repro:

var documentPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"..\..\..\workbook with comment.xlsx");
var outputPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"..\..\..\WorkbookWithCommentOutput.xlsx");
var fileInfo = new FileInfo(documentPath);
Assert.IsTrue(fileInfo.Exists);
using (var workbook = new ExcelPackage(fileInfo))
{
    var ws = workbook.Workbook.Worksheets.First();
    ws.DeleteRow(3); // NRE thrown here
    workbook.SaveAs(new FileInfo(outputPath));
}

workbook with comment.xlsx

I have the following test code:

class PivotTest
{
	private static string PIVOT_WS_NAME = "Pivot";
	private static string DATA_WS_NAME = "Data";
	internal void RunTest()
	{
		using (ExcelPackage xlp = new ExcelPackage())
		{
			PrepareDoc(xlp);
			GenPivot(xlp);

			FileStream fs = File.Create("pivot.xlsx");
			xlp.SaveAs(fs);
			fs.Close();
		}
	}

	private void PrepareDoc(ExcelPackage xlp)
	{
		//generate date/value pairs for October 2017
		var series = Enumerable.Range(0, 31);
		var data = from x in series
				   select new { d = new DateTime(2017, 10, x + 1), x = x };
		//put data in table
		ExcelWorksheet ws = xlp.Workbook.Worksheets.Add(DATA_WS_NAME);
		int col = 1;
		ws.Cells[1, col++].Value = "Date";
		ws.Cells[1, col].Value = "Value";
		int row = 2;
		foreach (var line in data)
		{
			col = 1;
			ws.Cells[row, col++].Value = line.d;
			ws.Cells[row, col - 1].Style.Numberformat.Format = DateTimeFormatInfo.CurrentInfo.ShortDatePattern;
			ws.Cells[row, col].Value = line.x;
			row++;
		}
	}

	private void GenPivot(ExcelPackage xlp)
	{
		ExcelWorksheet ws = xlp.Workbook.Worksheets.Add(PIVOT_WS_NAME);
		ExcelWorksheet srcws = xlp.Workbook.Worksheets[DATA_WS_NAME];
		ExcelPivotTable piv = ws.PivotTables.Add(ws.Cells[1, 1], srcws.Cells[1, 1, 32, 2], "Pivot1");
		piv.DataFields.Add(piv.Fields["Value"]);
		ExcelPivotTableField dt = piv.RowFields.Add(piv.Fields["Date"]);
		dt.AddDateGrouping(eDateGroupBy.Days | eDateGroupBy.Months);
	}
}

When I open the pivot.xlsx file, I see the following (note that my system is setup with the Greek locale):

I un-zipped the xlsx file and the files seem all right; however, if I save the file from Excel without making any changes, the pivotTable1.xml file has the items in the pivotField in the wrong order. From that, I'm guessing that Excel messes-up the data when it reads the file.

Currently, the latest version of the package fails to install on .NET Core:

Install-Package : Package EPPlus 4.1.1 is not compatible with netcoreapp1.0 (.NETCoreApp,Version=v1.0). Package EPPlus 4.1.1 supports:
  - net35 (.NETFramework,Version=v3.5)
  - net40 (.NETFramework,Version=v4.0)

Would be great if the package was built for .NET Standard so it can be used by .NET Core.

Add support for reading and writing Custom XML Parts including the ability to read/write by namespace.
e.g.
XElement customPart = package.Workbook.CustomXMLParts.SelectByNamespace(XNamespace ns)

package.Workbook.CustomXMLParts.Add(XElement customPart)
package.Workbook.CustomXMLParts.Delete(XNamespace ns)

See the Microsoft.Office.Core.CustomXMLParts as well.

I attached a small sample to demonstrate this problem. I am reading data in from a .txt file. For both rows of data after the header, I have an empty string in column 5. For the first row, the text "column5" is in that cell. All later rows will correctly have an empty string.

Please use the TestFile.txt file in the solution and put it at the root of your C drive. Put a break point in the code inside default.aspx.cs at the bottom of the for loop. Launch the web site. Click the single button on the page. At the break point, if you inspect the values of all the string variables you'll find all the data imports correctly except for column 5 of the first row, where the text "column5" appears.

There is a work around. If I set format.SkipLinesBeginning to 0 and start my for loop on row 2, the problem doesn't happen. But I figured I'd let you know about this bug.

TextImportError.zip

Our previous version is 2.9. Trying version 4.1 because of unresolved errors trying to open XLSX files. Anyway, when saving data to sheets now (EPPlus v4.1), via

currentSheet.Cells("A1").LoadFromDataTable(Data, True)

when the "Data" datatable contains no rows (but still contains named columns), the headers are no longer saved to the sheet.

Lines ~1867-1870 in ExcelRangeBase.cs confirm this change was made. Can we still have an option to write column headers to a sheet even if there are no rows in the datatable?

Note: Clone of stackoverflow ticket: Link

When you run the following code on a file that has any VBA macro's whatsoever(C#):

using (ExcelPackage xlPackage = new ExcelPackage(new FileInfo("Test.xlsm")))
{
xlPackage.Workbook.Worksheets.Add("TestTab");
xlPackage.Save();
}

You get the following issue(upon opening the edited file):
We found a problem with some content in 'Test.xlsm'. Do you want us to recover as much as we can? If you trust the source of this workbook, click Yes. enter image description here

(if you click yes)

Removed Part: /xl/vbaProject.bin part. (Visual Basic for Applications (VBA)) enter image description here

Then your VBA Marco is gone... I also have received:

The Visual Basic for Applications (VBA) macros in the workbook are corrupted and have been deleted. The macro corruption most likely exists in the current file. To recover the macros, open a backup copy of this file if you have one. enter image description here

I only see this issue on the latest update(Office 365 Excel 1708 - 8431.2079). When I test on older versions of excel or on versions that have not been upgraded to the latest version I do not experience this issue.

It appears the latest office update has introduced this issue into the opening of files that have been modified(specifically when a new worksheet in inserted) by EPPlus(Latest 4.5.0).

Has anybody else experienced this issue? Maybe someone has a work around of some sort. I was hoping not to have to dig into the source code :(

Test.zip

I have noticed following error: if you name a cell, say "my_value" and give it a value of 1.
In the next three cells you type the same formula =my_value. Save the file

Now open it in EPPlus and call Calculate function on workbook. Now if you check for the value of the third formula - it is #VALUE!

Attached is my file with which I reproduced the error.

TestDoc_NamedRangeInFormula.xlsx

Here is a small test that will throw a "ColumnMax can not span..." exception (VS C++ project). It seems that the VerticalAlignment statement causes the exception if it is set AFTER the AutoFitColumns call.

This worked fine in version 4.0.4.0, I am now having to update to 4.1.1.0 because of the recent horrible VBA issue.

The exception does not occur if the alignment is set BEFORE the autofit call, but the effect will be that ALL columns to the right of column 3 will be hidden, not just 4 and 5. Is this the expected behavior? In 4.0.4.0 the alignment being set AFTER the autofit call ONLY hides columns 4 and 5.

============================================================
FileInfo ^file = gcnew FileInfo("C:\Temp\Test.xlsx");

ExcelPackage ^pck = gcnew ExcelPackage(file);
ExcelWorksheet ^ws = pck->Workbook->Worksheets->Add("Worksheet");

if (ws != nullptr) {
ws->Cells["A1"]->Value = "Cell value 1";
ws->Cells["B1"]->Value = "Cell value 2";
ws->Cells["C1"]->Value = "Cell value 3";
ws->Cells["D1"]->Value = "Cell value 4";
ws->Cells["E1"]->Value = "Cell value 5";
}

//ws->Cells->Style->VerticalAlignment = ExcelVerticalAlignment::Top; // Columns 4 and greater hidden
ws->Cells->AutoFitColumns(0);
ws->Cells->Style->VerticalAlignment = ExcelVerticalAlignment::Top; // 4.1.1.0 - exception, 4.0.4.0 - columns 4 and 5 hidden

ws->Column(4)->Hidden = true;
ws->Column(5)->Hidden = true; // span exception

pck->Save();

Currently EPPlus 4.5.0-beta supports .NETCoreApp 2.0. In this case, It can't be used from a class library project.

Steps to reproduce

dotnet new classlib
dotnet add package EPPlus --version 4.5.0-beta

Output

warn : Package 'EPPlus 4.5.0-beta' was restored using '.NETFramework,Version=v4.6.1' instead of the project target framework '.NETStandard,Version=v2.0'. This package may not be fully compatible with your project.

It can be fixed by changing netcoreapp2.0 in csproj file to netstandard2.0.

Version : 4.5.0-beta
I am getting "Object reference not set to an instance of an object." in below case :

1. Using EPPlus, Create new xlsx with Worksheet name "Sheet1" and data with table name "Table1" and save it
2. Manually I added pivot table base on "Table1" using MS Office 2013
3. Using EPPlus, Delete "Sheet1" and new worksheet with name "Sheet1" and add some data with table name "Table3" and call ExcelPackage.Save(). It will throw error.

It should allow to save excel file even though there is missing reference (Same like "ClosedXML").

System.InvalidOperationException: Error saving file G:\Export\exp_2.xlsx ---> System.NullReferenceException: Object reference not set to an instance of an object.
at OfficeOpenXml.ExcelWorksheet.SavePivotTables()
at OfficeOpenXml.ExcelWorksheet.Save()
at OfficeOpenXml.ExcelWorkbook.Save()
at OfficeOpenXml.ExcelPackage.Save()
--- End of inner exception stack trace ---
at OfficeOpenXml.ExcelPackage.Save()
at Query.CreateExcel(OleDbDataReader dr, String strFile) in G:\Export\Query.aspx.vb:line 4006
at Query.ExportData() in G:\Export\Queryaspx.vb:line 1644

If there is a "%" sign in the format string for a cell, the value of it will be multiplied with factor 100. This is not quite correct.

Say you apply format #,##0.0"text and % sign". The cell value is 0.2.
The Excel shows the value of the cell as 0.2 text and % sign.
If you pick the EPPlus cell value - it gives 20 text % sing back.

Generally multiplying the cell value with 100 is correct only for percent values. Their format is 0.0% (Regex: 0.?0*%)

Hello,

I create my Excel file dynamicaly using Epplus (C#). When I want to get height of a row(feuille.Row(i).Height), I have a wrong result : I got 14.40 (default value), and not the good value (28.80 in my case).

I think that Excel formated visually rows and columns only when the file is opening.

I tried to use CustomHeight = true before get the row height, but it doesn't work.

I published this issue in StackOverFlow (https://stackoverflow.com/questions/46340871/epplus-c-sharp-real-row-height) because is it very urgent, and the new issue link in CodePlex is disabled.

I hope anybody can help me.

Thank you.

Hi, I'm C# developer, I use this DLL a lot and really I would like to help coding or something, but I'm trying to find any documentation about how to help, and I don't find nothing.

Is possible any documentation (word etc..) about the architecture of project? or anything else?

Sorry for use an issue for it, but its the only way to communicate that I find.

Regards

Note: Please don't file an issue to ask a support related question. We are a small team and can't handle all questions. Questions are referred to https://stackoverflow.com/questions/tagged/epplus
If you have a bug, request of a new feature, make sure you follow these guidelines
[] Write a detailed description of your issue.
[] Attach a test to reproduce your issue, if it is a bug or unexpected behaviour. This is very important.
[] If your issue requires a template xlsx file to be reproduced, make sure that you attach it to the issue.

Hi
I'm not sure if this is a bug, future request or I just using the wrong syntax.
I asked at the forums and we did not manage to make it work.

I need to export large amount of data to Excel with PowerShell.
First I used this great module https://github.com/dfinke/ImportExcel that works but is to slow for my data as It's writing the data with EPPlus.dll cell by cell so it's slow.

The dll also has methods to load an array of objects or a DataTable at once which is much faster, I can get the DataTable method to work but I can't figure out how to load an array with "LoadFromArrays"

Example:

# Install and Load
Install-Package EPPlus -Scope CurrentUser -provider Nuget -Source https://www.nuget.org/api/v2
Add-Type -path "$env:LOCALAPPDATA\PackageManagement\NuGet\Packages\EPPlus.4.1.1\lib\net40\EPPlus.dll"
# Initialize Data
$Path = 'C:\Temp\Test.xlsx'
$pkg = [OfficeOpenXml.ExcelPackage]::new($Path)
$ws  = $pkg | Add-WorkSheet -WorkSheetname 'test'


# Single Cell Works
$ws.Cells['A1'].Value = 8

# DataTable Works
$myDataTable = New-Object System.Data.DataTable
$null = $myDataTable.Columns.Add( 'Column1' )
$null = $myDataTable.Columns.Add( 'Column2' )
$null = $myDataTable.Columns.Add( 'Column3' )
$null = $myDataTable.Rows.Add( 'value1','value2','value3' )
$null = $myDataTable.Rows.Add( 'valueA','valueB','valueC' )
$null = $myDataTable.Rows.Add( 'valueD','value2','valueE' )
$null = $ws.Cells['A1'].LoadFromDataTable($myDataTable,$true)

# How to Load an Arrays?
$Process = Get-Process
$null = $ws.Cells['A1'].LoadFromArrays($Process)
$Process = @([PSCustomObject]@{A = 1},[PSCustomObject]@{A = 2})
$null = $ws.Cells['A1'].LoadFromArrays($Process)
# I get the Error:
#[2,1] Cannot convert argument "Data", with value: "System.Object[]", for "LoadFromArrays" to type "System.Collections.Generic.IEnumerable`1[System.Object[]]": "Cannot convert the "System.Object[]" value of type "System.Object[]" to type "System.Collections.Generic.IEnumerable`1[System.Object[]]"."


# Save to disk
$pkg.Save()
$pkg.Dispose()

Thank you

Strange error by following code
ExcelPackage pck = new ExcelPackage();
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("test");
ws.Cells["A1"].LoadFromArrays(Enumerable.Empty<object[]>());

Is this meant to throw exception if so it is unclear to user what is he doing wrong.
I am happy to create pull request with fix only i need to know throw an error or do nothing?

Looking at 'LoadFromDataTable' LoadFromArrays with empty collection should return null

Hi.

I won't repeat what others have written, but I'm still experiencing the impact of this reported bug.

https://www.codeproject.com/Tips/1118204/EPPlus-Basics-and-Snippets-Cheatsheet-for-generati

I'm using version 4.1.0. Any workarounds, or bug fixes?

Error opening the file after copying worksheet and copying ranges
Hello!
I found some problem whit coping ranges. Problem is:

1. I copy worksheet
2. I copy ranges from one worksheet to copied worksheet.

If in copied ranges have merged cells, when I open the file in Excel, Excel tells me
"Excel found unreadable content in sample1_copied.xlsx. Do you want to recover the contents of this workbook?
If you trust the source of this workbook,
click Yes."

After I click Yes, I see message "Removed Records: Merge cells from /xl/worksheets/sheet2.xml part" (v. 4.5.0)
and also "Removed Records: Shared formula from /xl/worksheets/sheet2.xml part" (v. 4.1.1)
Test code: Sample1_copyWorksheet
Source/input file: sample.xlsx
Result/output file: sample1_copied.xlsx

PS: if I run the method package.save and reopen the file between copying worksheet and ranges, it works.
Sample1_CopyWorksheet.zip

Hi,

I am creating an Excel file with about 750 rows. Each row contains an unique image which I add via the ExcelWorkSheet.Drawings.AddPicture() method. However, this is getting slow after having added a couple of images.

I was wondering if there is a more efficient method for adding a lot of images to an Excel sheet.

This issue is similar to the question asked here: https://stackoverflow.com/questions/31165922/are-there-alternatives-to-addpicture-c-excel

Hi,

at first sorry for my bad english ;)

if i create a new ExcelPackage with an exist file ( new ExcelPackage( FileInfo file ) ) with a sheet that name contains a "!" and i try get the Workbook property then throw a NullReferenceException.

here the StackTrace:

• ei OfficeOpenXml.ExcelRangeBase..ctor(ExcelWorksheet xlWorksheet, String address) in E:\EPPlus-master\EPPlus-master\EPPlus\ExcelRangeBase.cs:Zeile 112.
• bei OfficeOpenXml.ExcelNamedRange..ctor(String name, ExcelWorksheet nameSheet, ExcelWorksheet sheet, String address, Int32 index) in E:\EPPlus-master\EPPlus-master\EPPlus\ExcelNamedRange.cs:Zeile 53.
• bei OfficeOpenXml.ExcelNamedRangeCollection.Add(String Name, ExcelRangeBase Range) in E:\EPPlus-master\EPPlus-master\EPPlus\ExcelNamedRangeCollection.cs:Zeile 75.
• bei OfficeOpenXml.ExcelWorkbook.GetDefinedNames() in E:\EPPlus-master\EPPlus-master\EPPlus\ExcelWorkbook.cs:Zeile 259.
• bei OfficeOpenXml.ExcelPackage.get_Workbook() in E:\EPPlus-master\EPPlus-master\EPPlus\ExcelPackage.cs:Zeile 689.

the problem is in the "private void SetWbWs(string address)" methode in the ExcelAddress class
at the Row 323

the ExcelAddress class save the wrong sheetname
example: "sheetname!1" would be save as "sheetname"

the result is that in the method "internal void GetDefinedNames ()".
in the ExcelWorkbook class of line 259, an ExcelRangeBase with a null reference to the sheet is created.

if i remove the ! then all works fine.

When targeting UWP Fall Creators Update (v10.0.16299) I get this exception when saving excel file from stream.

System.ArgumentException: 'IBM437' is not a supported encoding name. For information on defining a custom encoding, see the documentation for the Encoding.RegisterProvider method.
Parameter name: name
   at System.Globalization.EncodingTable.internalGetCodePageFromName(String name)
   at System.Globalization.EncodingTable.GetCodePageFromName(String name)
   at System.Text.Encoding.GetEncoding(String name)
   at OfficeOpenXml.Packaging.ZipPackage.Save(Stream stream)
   at OfficeOpenXml.ExcelPackage.Save()
   at OfficeOpenXml.ExcelPackage.SaveAs(Stream OutputStream)
   at HtmlScraper.Helpers.<ExcelExportAsync>d__6.MoveNext()

The code:

var stream = await file.OpenAsync(FileAccessMode.ReadWrite);
using (Stream s = stream.GetOutputStreamAt(0).AsStreamForWrite())
{
    excelPackage.SaveAs(s);
}
stream.Dispose();

I get this warning at compile but nonetheless it should work.

Package 'EPPlus 4.1.1' was restored using '.NETFramework,Version=v4.6.1' instead of the project target framework 'UAP,Version=v10.0.16299'. This package may not be fully compatible with your project.

According to this the same is the problem when using with .NET Standard.