closedxml / closedxml

ClosedXML is a .NET library for reading, manipulating and writing Excel 2007+ (.xlsx, .xlsm) files. It aims to provide an intuitive and user-friendly interface to dealing with the underlying OpenXML API.

💾 Download unstable CI build

ClosedXML is a .NET library for reading, manipulating and writing Excel 2007+ (.xlsx, .xlsm) files. It aims to provide an intuitive and user-friendly interface to dealing with the underlying OpenXML API.

For more information see the documentation or the wiki.

Release notes & migration guide

The public API is still not stable and it is a very good idea to read release notes and migration guide before each update.

• Release notes for 0.100
• Migration guide for 0.100
• Release notes for 0.97

Performance

Performance matters mostly for large files. For small files, few ms here or there doesn't matter. The presented data are from generally develop branch (currently 0.103-beta).

Runtime details

``` BenchmarkDotNet v0.13.8, Windows 11 (10.0.22621.2283/22H2/2022Update/SunValley2) AMD Ryzen 5 5500U with Radeon Graphics, 1 CPU, 12 logical and 6 physical cores .NET SDK 8.0.100-rc.1.23463.5 [Host] : .NET 7.0.11 (7.0.1123.42427), X64 RyuJIT AVX2 ```

Save

Description	Rows	Columns	Time/Memory to insert data	Save workbook	Total time/memory
Mixed (text/number) workbook. Gist	250 000	15	1.619 sec / 117 MiB	6.343 sec	7.962 sec / 477 MiB
Text only workbook. Gist	1 000 000	10	6.302 sec / 402 MiB	17.134 sec	23.436 sec / 1880 MiB

Load

Description	Rows	Columns	Time to load data	Used memory
Load mixed workbook (10 text/5 number columns).	250 000	15	15.648 sec	236 MiB
Text only workbook.	1 000 000	10	49.046 sec	801 MiB

Load tests used files created during save test.

Frequent answers

• If you get an exception Unable to find font font name or fallback font fallback font name. Install missing fonts or specify a different fallback font through ‘LoadOptions.DefaultGraphicEngine = new DefaultGraphicEngine(“Fallback font name”)’, see help page about missing fonts.
• ClosedXML is not thread-safe. There is no guarantee that parallel operations will work. The underlying OpenXML library is also not thread-safe.
• If you get an exception The type initializer for 'Gdip' threw an exception. on Linux, you have to upgrade to 0.97+.

Install ClosedXML via NuGet

If you want to include ClosedXML in your project, you can install it directly from NuGet

To install ClosedXML, run the following command in the Package Manager Console

PM> Install-Package ClosedXML

What can you do with this?

ClosedXML allows you to create Excel files without the Excel application. The typical example is creating Excel reports on a web server.

Example:

using (var workbook = new XLWorkbook())
{
    var worksheet = workbook.Worksheets.Add("Sample Sheet");
    worksheet.Cell("A1").Value = "Hello World!";
    worksheet.Cell("A2").FormulaA1 = "=MID(A1, 7, 5)";
    workbook.SaveAs("HelloWorld.xlsx");
}

Extensions

Be sure to check out our ClosedXML extension projects

• https://github.com/ClosedXML/ClosedXML.Report
• https://github.com/ClosedXML/ClosedXML.Extensions.AspNet
• https://github.com/ClosedXML/ClosedXML.Extensions.Mvc
• https://github.com/ClosedXML/ClosedXML.Extensions.WebApi

Developer guidelines

The OpenXML specification is a large and complicated beast. In order for ClosedXML, the wrapper around OpenXML, to support all the features, we rely on community contributions. Before opening an issue to request a new feature, we'd like to urge you to try to implement it yourself and log a pull request.

Please read the full developer guidelines.

Credits

• Project originally created by Manuel de Leon
• Current maintainer: Jan Havlíček
• Former maintainer and lead developer: Francois Botha
• Master of Computing Patterns: Aleksei Pankratev
• Logo design by @Tobaloidee

closedxml's Issues

Large Files - Performance & Memory consumption

Hi, this has been reported since a while. A lot of issues and discussion can be found on Codeplex.

Writing a files with 50K could lead to oom exception on 32bit. 64bit will run, but i could take minutes.
Once i was able to consume all my 16GB memory ... writing the file took ages.

It would be great if memory consumption and writing a file could be improved.

Thx

Getting format exception and not getting proper value of a cell which shows properly on excel

I am getting a '((ClosedXML.Excel.XLCell)timeCell).Value' threw an exception of type 'System.FormatException' when trying to access the value of a cell which contains a date with a custom format of [$-en-US]dddd, mm/dd .

Appreciate all the help.

reading empty cell issue

sorry but this is not working :
this is my code
Stream stream = new MemoryStream(ReadFile(File));
using (XLWorkbook workBook = new XLWorkbook(stream))
{
//Read the first Sheet from Excel file.
IXLWorksheet workSheet = workBook.Worksheet(1);

            //Create a new DataTable.

            //Loop through the Worksheet rows.
            bool firstRow = true;
            bool error = false;
            foreach (IXLRow row in workSheet.Rows())
            {
                foreach (IXLCell cell in row.Cells())
                            {
                                if (cellCnt < 11)
                                {
                                    dt.Columns.Add(cell.Value.ToString());
                                }
                                else
                                {
                                    break;
                                }
                                cellCnt++;
                                firstRow = false;
                            }

}

what is happening here when i go for header its showing 20 cells
but in data row it showing 19 . as one contain blank value . But we need the blank value also.Please suggest where to modify it..

"Pivot Tables Values Problem"

File with Pivot Table fails to open

https://closedxml.codeplex.com/workitem/8466

Multi-targeting: net40, net461, netstandard2.0

is it meant to be?

Vlookup using wrong row numbers

Hi Guys, I have run across a little bug in the VLOOKUP in Lookup.cs.
The code
r.RowNumber()
should be
r.RowNumber()-range.FirstRow().RowNumber()+1
?

        IXLRangeRow matching_row;
        matching_row = range.FindRow(r => new Expression(r.Cell(1).Value).CompareTo(lookup_value) == 0);
        if (range_lookup && matching_row == null)
        {
            matching_row = range.FindRow(r =>
            {
                if (r.RowNumber()-range.FirstRow().RowNumber()+1 < range.RowsUsed().Count() &&
                    new Expression(r.Cell(1).Value).CompareTo(lookup_value) <= 0 && 
                    new Expression(r.RowBelow().Cell(1).Value).CompareTo(lookup_value) > 0)
                    return true;
                else if (r.RowNumber()-range.FirstRow().RowNumber()+1 == range.RowsUsed().Count() &&
                    new Expression(r.Cell(1).Value).CompareTo(lookup_value) <= 0)
                    return true;
                else
                    return false;
            });
        }

Please verify that I am not (completely) crazy?
Thanks!

Problem with R1C1 Regex

Ran into this while making something for work, leaving a note here for anyone who cares:

ClosedXML will mangle formulas in which a cell reference is immediately followed by a minus sign and then a number, when said formulas have been generated by a copy paste operation in Excel.

For example, in cell B1, type "10", then in B2, "=B1-1", then drag B2 down so that you get a series of cells counting down from 10. Save the book, open it in ClosedXML, and try reading the copy pasted cells' FormulaA1 string. The strings will show up as references to the A column and one row above. If you save the book, they will be saved this way as well, thus breaking any such formulas in the book.

I haven't investigated this thoroughly, but my hunch is that Excel probably saves copy pasted formulas internally in R1C1 format. Thus the formula in B2, "=B1-1", becomes "=R[-1]C-1". This should be parsed as "=(R[-1]C) - 1", however there is a bug in the regex used to parse R1C1 formulas which causes the "-1" after the cell reference to be parsed as if it were part of the reference itself, shifting the reference one column to the left and losing the minus 1 in the formula.

The regex in question is as follows, from the XLCell.cs file:

(?<=\W)([Rr]\[?-?\d{0,7}\]?[Cc]\[?-?\d{0,7}\]?)(?=\W)

The question marks after the brackets here allow the regex to eat a negative number even when there are no brackets around it, which is the cause of the bug. I have changed the regex to the following in my local copy:

(?<=\W)([Rr](?:\[-?\d{0,7}\]|\d{0,7})?[Cc](?:\[-?\d{0,7}\]|\d{0,7})?)(?=\W)

This way it either matches a pair of brackets with either a negative or positive number inside, or just a positive number (for absolute references), but not just a negative number.

The above fix seems to be working for me and has solved my immediate problem.

Hopefully this is enough information for someone with more time/dedication than I to make sure the above fix won't break any existing features and commit it.

Regards.

Data at root level is invalid, generating xlsx from linux with mono

I tried to gen a simple xlsx

var wb = new XLWorkbook();

var ws = wb.Worksheets.Add("test");
ws.Cell(1, 1).Value = 101.23;

wb.SaveAs("test.xlsx");

running the above sample under windows I get a working xlsx file, if I generate that using latest mono under linux I get an error opening the xlsx that ask me to repair.

I tried to open that using OpenXML Tool v2.5 and results follow error

"Cannot open the file. Data at the root level is invalid. Line 1, position 681."

More, I tried to generate a basic xlsx file directly using Documentformat.OpenXml and that error not happens. I found this blog describing this error in another situation but may useful to investigate in a possible solution.

Attached two generated xlsx files.
win-test.xlsx
linux-test.xlsx

I'm available todo tests from any branch of the ClosedXML repository if needed agains execution under linux, by the way its easy to try that using ubuntu 16.04 lts after an apt-get install mono-complete just run mono my-test.exe to see the behavior.

DocumentFormat.OpenXml nuget dependency

Please consider switching from DocumentFormat.OpenXml to OpenXMLSDK-MOT package.
The second one is official and evolving.

checkboxes support

is it possible to check checkboxes when manipulating a document ?

Query table support

Charting support

After the first 4 sheets, Border is assigned only to specific cells

VB.NET 2013, ClosedXML 0.80.0.0, OpenXML 2.5.5631.0.

From the first 4 sheets, only the cell N12 (Row: 12, Column: 14), Border is given phenomenon occurs.

Problem with the conditional formatting

Hi.
I have a problem with the conditional formatting and Excel 2013. Reprosteps:

• Create a new .xlsx file using Excel 2013.
• Add to the first cell some number and then apply to this cell conditional formatting, for example color scales.
• Save this file.
• Then I open this file using ClosedXML and save it without any changes. When I try to open this file using Excel 2013 I will get an error:
  “Excel found unreadable content in 'FILENAME.xlsx'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes.”

If I don’t apply conditional formatting to that cell, I won’t get an error after resaving file using ClosedXML.
Any suggestions?

PS: I validated the content of resaved .xlsx file using OpenXmlValidator.Validate() method and got this error:

PPS: ConditionalFormatting node when file saved using Excel:
<x:conditionalFormatting xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main" sqref="A1:C1"> <x:cfRule type="colorScale" priority="1"> <x:colorScale> <x:cfvo type="min" /> <x:cfvo type="max" /> <x:color rgb="FFFF7128" /> <x:color rgb="FFFFEF9C" /> </x:colorScale> </x:cfRule> </x:conditionalFormatting>

ConditionalFormatting node when file resaved using ClosedXML:
<x:conditionalFormatting xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main" sqref="A1:C1"> <x:cfRule type="colorScale" priority="1"> <x:colorScale /> </x:cfRule> </x:conditionalFormatting>

Object Creation Validation

We need an interface that we can implement that will process the object and make sure all needed things are there and meet other validation requirements. The result can be an ienumerable<ClosedXML.ObjectValidationFailure>. When saving iterate through all objects implementing the interface. The result objects need to express the issue and how to fix. Example pivot tables need row labels.

Formulas like FIND and COUNTIF get removed

I created an xlsx file like this:

var workbook = new XLWorkbook();
var worksheet = workbook.Worksheets.Add("Sample Sheet");
worksheet.Cell("A1").FormulaA1 = "=FIND(\"test\";H5)";    // gets removed
worksheet.Cell("A2").FormulaA1 = "=COUNTIF(H5;\"asdf\")"; // gets removed
worksheet.Cell("A3").FormulaA1 = "=SUM(B1:B10)";          // works
workbook.SaveAs("FormulaTest.xlsx");

when opening the created file using Excel 2016 I get the message:

We found a problem with some content in 'FormulaTest.xlsx'. Do you want us to try to recover as much as we can?

then:

Excel was able to open the file by repairing or removing the unreadable content.
Removed Records: Formula from /xl/worksheets/sheet.xml part
Removed Records: Formula from /xl/calcChain.xml part (Calculation properties)

only the value in A3 is left....

Full pivot table support

Copying sheet doesn't bring graphs over

Hi,

I'm copying a sheet using CopyTo, however it's not copying over graphs that are on that worksheet. How do I go about copying objects programmatically?

Thanks,
Brett

There is a phenomenon that the setting of Border does not apply.

VB.NET 2013, ClosedXML 0.80.0.0, OpenXML 2.5.5631.0.

While processing the DataTable in the For Each loop, there is a phenomenon that the Border setting is not always applied only in the case of the second DataRow from the beginning.

It occurs only in the first sheet of the book. From the second onward, the Border setting is applied normally.

Read or retrieve value from spreadsheet using existing rangenames

i have an existing excel file and it has already defined cell name or cell range names.

I am able to get all cell range names using Openxml sdk. my sample code is given below:

 var path = @"D:\test.xlsx";
        using (var document = SpreadsheetDocument.Open(path, true))
        {
            var workbookPart = document.WorkbookPart;
              var wb = workbookPart.Workbook;
            var definedNames = wb.DefinedNames;
            if (definedNames != null)
            {
                System.Console.WriteLine("Name\tText\tName.Value");
                foreach (DefinedName dn in definedNames)
                {
                    System.Console.WriteLine(dn.Name + "\t" + dn.Text + "\t" + dn.Name.Value);

                }
            }

        }

Is there any way to retrieve or read cell value from defined name using this OpenXml or any other SDK in c#?
Any Help would be highly appreciable.
Thanks

cell.RichText.Text v/s cell.Value.Tostring()

Hi,

I'm trying to read an excel file to get the formatted values for each cell and i'm having issues getting the correct value.
I have a cell with the value : 549.78
cell.RichText.Text is returning : _(* 549.78) rather than 549.78

So shall i use cell.RichText.Text to get the formatted value or just use cell.Value.ToString()?
What is the proper way to get the displayed (formatted value of a cell)?

Thanks,
SanjeevM.

Excel found unreadable content - strip invalid XML characters?

Using a DataTable to build XLWorkbook and SaveAs MemoryStream.
Code works fine on small data sets but receiving following error on large data sets.

"Excel found unreadable content in Filename.xlsx" Do you want to recover the contents of this workbook?

Am already stripping and replacing with "" space the following characters:
string r = "[\x00-\x08\x0B\x0C\x0E-\x1F\x26]";

Are there additional invalid XML characters I should be looking for and removing?
Thanks!

Reading value from empty cell

Allow Access to lower level xml

Wrong bar proportions in DataBar

Official example (4th example).
Replacing .LowestValue() to .Minimum(XLCFContentType.Number, 0) not help.
If in saved .xlsx add to section <x14:dataBar minLength="0" maxLength="100"...
by hand - all fine.

Additional Cell Formatting Options

Is there any way to apply cell formatting options outside of the values found in the XLCellValues enum, such as percentage or custom? If not, is there any plan to implement this functionality?

SetRichText(object value)

I cannot copy a RichText from an existing cell to another. Setting IXLCell.Value to a RichText copies only the first element of the rich text as a string. I suspect if (SetEnumerable(value)) return; is being called before SetRichText.

Can SetRichText(object value) be made public without negative side effects?

Defined Name Visibility

Please add visiblity property to defined names.

Seems like it should be easy to implement for reading
var visible = definedName.Hidden == null;

Thanks!

Can't set header on non empty header

I am using this code to set the header on a document that already contains headers(first page and odd even) and it does not work.

pageSetup.Header.Center.AddText("first", XLHFOccurrence.FirstPage);
pageSetup.Header.Center.AddText("odd", XLHFOccurrence.OddPages);
pageSetup.Header.Center.AddText("even", XLHFOccurrence.EvenPages);

if i delete the headers, it does work.

using pageSetup.Header.Center.Clear(); does not work either.

Columns().AdjustToContents ignored AutoFilter

When setting both table.ShowAutoFilter = true and Worksheet.Columns().AdjustToContents(), the output shows the auto filter arrow not being factored in by the AdustToContents logic, resulting in column headers being partially hidden.

Copying a range will alter the worksheet names in a Formula

I found an issue related to copying a range.

When you copy a range to another cell and within this range there is an cell containing a formula that has cells within another worksheet the name is chaged.
For example, if the formula contains "='S10 Sample'!A4" and the range will be copied by 1 column to the right then the formula in the copied cell is "='T10 Sample'!B4". In change in the cell address is as expected but the change of the worksheet name is not expected and should not change.

It would also be nice if there would be an option to not adjust the cell address when copying.

Here is also a code snippet: (The excel contains formulas within it cells)

FileInfo file = new FileInfo("SampleInput.xlsx");
using (FileStream stream = file.OpenRead())
{
    using (XLWorkbook workbook = new XLWorkbook(stream))
    {
        IXLWorksheet workSheet = workbook.Worksheet("S0 Summary");

        var cellRange = workSheet.Range("E1:B10");
        cellRange .Select();

        workSheet.Cell(1, 3).Value = cellRange 
 
        workbook.SaveAs("SampleOutput.xlsx");
    }
}

Thanks for any help in advance.

StackOverflowException due to long chain of formula dependencies

I'm using ClosedXML version 0.80.1 (NuGet package) to read a .xlsx file provided by a customer. The cells in column A contain formulas where each cell references the cell above it. For example the formula for cell A1001 is
=A1000+1
and the formula for A1000 is
=A999+1
Thus, each cell in column A has a chain of dependencies all the way back to cell A1. The file has about 9300 rows.

My program is basically reading the contents of every cell by calling IXLCell.GetString(). When it gets to (approximately) cell A1015 a StackOverflowException occurs. Based on some experimentation I'm pretty sure ClosedXML is recursively evaluating all those formulas.

Is there some way to get ClosedXML to cache the result when it evaluates a given cell's formula so it doesn't have to recalculate it when it encounters another cell that references the given cell?

Also, I know recursion is the easiest implementation but you might consider a non-recursive implementation in a future release.

Here's what the top 30-40 stack entries look like, copied from the Visual Studio debugger.

mscorlib.dll!System.Globalization.CompareInfo.IndexOf(string source, string value, int startIndex, int count, System.Globalization.CompareOptions options)
mscorlib.dll!string.IndexOf(string value, int startIndex, int count, System.StringComparison comparisonType)
mscorlib.dll!string.Contains(string value)
ClosedXML.dll!ClosedXML.Excel.XLRangeAddress.XLRangeAddress(ClosedXML.Excel.XLWorksheet worksheet, string rangeAddress)
ClosedXML.dll!ClosedXML.Excel.XLWorksheet.Range(string rangeAddressStr)
ClosedXML.dll!ClosedXML.Excel.XLWorksheet.ClosedXML.Excel.IXLWorksheet.Range(string rangeAddress)
ClosedXML.dll!ClosedXML.Excel.CalcEngine.XLCalcEngine.GetExternalObject(string identifier)
ClosedXML.dll!ClosedXML.Excel.CalcEngine.CalcEngine.ParseAtom()
ClosedXML.dll!ClosedXML.Excel.CalcEngine.CalcEngine.ParseUnary()
ClosedXML.dll!ClosedXML.Excel.CalcEngine.CalcEngine.ParsePower()
ClosedXML.dll!ClosedXML.Excel.CalcEngine.CalcEngine.ParseMulDiv()
ClosedXML.dll!ClosedXML.Excel.CalcEngine.CalcEngine.ParseAddSub()
ClosedXML.dll!ClosedXML.Excel.CalcEngine.CalcEngine.ParseCompare()
ClosedXML.dll!ClosedXML.Excel.CalcEngine.CalcEngine.ParseExpression()
ClosedXML.dll!ClosedXML.Excel.CalcEngine.CalcEngine.Parse(string expression)
ClosedXML.dll!ClosedXML.Excel.CalcEngine.ExpressionCache.this[string].get(string expression)
ClosedXML.dll!ClosedXML.Excel.CalcEngine.CalcEngine.Evaluate(string expression)
ClosedXML.dll!ClosedXML.Excel.CalcEngine.CellRangeReference.GetValue(ClosedXML.Excel.IXLCell cell)
ClosedXML.dll!ClosedXML.Excel.CalcEngine.CellRangeReference.GetValue()
ClosedXML.dll!ClosedXML.Excel.CalcEngine.XObjectExpression.Evaluate()
ClosedXML.dll!ClosedXML.Excel.CalcEngine.Expression.implicit operator double(ClosedXML.Excel.CalcEngine.Expression x)
ClosedXML.dll!ClosedXML.Excel.CalcEngine.BinaryExpression.Evaluate()
ClosedXML.dll!ClosedXML.Excel.CalcEngine.CalcEngine.Evaluate(string expression)
ClosedXML.dll!ClosedXML.Excel.CalcEngine.CellRangeReference.GetValue(ClosedXML.Excel.IXLCell cell)
ClosedXML.dll!ClosedXML.Excel.CalcEngine.CellRangeReference.GetValue()
ClosedXML.dll!ClosedXML.Excel.CalcEngine.XObjectExpression.Evaluate()
ClosedXML.dll!ClosedXML.Excel.CalcEngine.Expression.implicit operator double(ClosedXML.Excel.CalcEngine.Expression x)
ClosedXML.dll!ClosedXML.Excel.CalcEngine.BinaryExpression.Evaluate()
ClosedXML.dll!ClosedXML.Excel.CalcEngine.CalcEngine.Evaluate(string expression)
ClosedXML.dll!ClosedXML.Excel.CalcEngine.CellRangeReference.GetValue(ClosedXML.Excel.IXLCell cell)
ClosedXML.dll!ClosedXML.Excel.CalcEngine.CellRangeReference.GetValue()
ClosedXML.dll!ClosedXML.Excel.CalcEngine.XObjectExpression.Evaluate()
ClosedXML.dll!ClosedXML.Excel.CalcEngine.Expression.implicit operator double(ClosedXML.Excel.CalcEngine.Expression x)
ClosedXML.dll!ClosedXML.Excel.CalcEngine.BinaryExpression.Evaluate()
ClosedXML.dll!ClosedXML.Excel.CalcEngine.CalcEngine.Evaluate(string expression)
ClosedXML.dll!ClosedXML.Excel.CalcEngine.CellRangeReference.GetValue(ClosedXML.Excel.IXLCell cell)
ClosedXML.dll!ClosedXML.Excel.CalcEngine.CellRangeReference.GetValue()

Number formatting does not work

Hi
I generate a spreadsheet with SSIS and I am trying to format it in the last step with ClosedXML

I set a table style and it works, but when I try to set the number format in several columns does not work.
I mean, when I open the file the values are not formatted but if I check the format column in the excel file it is set. If I chage the value manually the new value is formatted formatted

I simplified the code and it does not work

The background color changes but the values are not properly formatted

var workbook = new XLWorkbook(Dts.Variables["User::ExportPathFile"].Value.ToString());
foreach (var ws in workbook.Worksheets)
{          
    var range = ws.RangeUsed(false);
    range.Clear(XLClearOptions.Formats);
    ws.Cell(3, 5).Style.NumberFormat.SetFormat("#0.00");
    ws.Cell(3, 5).Style.Fill.BackgroundColor = XLColor.Orange;

    ws.Columns().AdjustToContents();    
}

workbook.Save();

Any idea??

PROPER function handling

If a cell uses PROPER function which refers to another cell with empty value then ClosedXML fails with ArgumentOutOfRangeException.

e.g. I have A1 cell and it's value is "=PROPER(B1)". Value of B1 is empty.

Reading such a spreadsheet with ClosedXML results in error:

System.ArgumentOutOfRangeException: Index and length must refer to a location within the string.
Parameter name: length
at System.String.Substring(Int32 startIndex, Int32 length)
at ClosedXML.Excel.CalcEngine.Text.Proper(List`1 p)
at ClosedXML.Excel.XLCell.get_Value()
at ...

I would expect A1 cell to return empty value.

Support for lookup functions

https://support.office.com/en-us/article/VLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1

installed ClosedXml nuget from nuget manager but can't load an existing excel file

I installed ClosedXml nuget from nuget manager but can't load an existing excel file.

string file = "test.xlsx";
            if (System.IO.File.Exists(Server.MapPath("~/App_Data/" + file)))
            {
                var path = Server.MapPath("~/App_Data/" + file);

                //var workbook = new XLWorkbook(Server.MapPath("~/App_Data/" + file));
                var workbook = new XLWorkbook(path);

or i did like

var stream = ExcelHelper.getExcelFileAsStream();
var workbook = new XLWorkbook(stream);

nothing works

getting object instance not set an instance of an object on line

 var workbook = new XLWorkbook(stream);

any help would be highly appreciable,

as discussed in stackoverflow, sample file attached.

Test.xlsx

Implement all Excel functions

Sort does not work properly when using Formula's

An excel doc is generated with columns A thru O. The headers are there and sized right.
In column A there is just an item number, in column K there is a formula to compare against 2 other cells.

After the iterations of filling in the values from every record in the table, the next part of the code builds the headers, then the sorting done by: Sort("A");

The result is that in my first data row, which is row 4 (the first 3 rows are header or other info) A4 is right, it has the first item, it sorted right. but K4 ? totally wrong value. Click and it says: =(C$2-E33/(F33-E33) and then K5? is for row 65, not 5.

Is it not possible to do a Sort if the Excel spreadsheet uses Formula's for values? I thought that the reference C$2 means C2 absolute; so when the others are in there without '$' then they would be relative indexers and would update?

reference info:
Problem:
Microsoft Excel: What are Absolute and Relative Cell References?

Solution:
Absolute means that the reference in a formula will always look for the value in a specific cell, even if you copy the cell to a different location. The format for an absolute reference is $A$1 (referring to cell A1).

Relative references are in the format A1 and if copied to a different cell the cell reference changes relative to the new location. For example, if the formula specifies A1 and is copied to a new location two rows down the new formula would specify A3.

http://help.isu.edu/index.php?action=faq&catid=38&docid=410

More than one Value to Pivot table not working

base on your example with pastries if I would have another column like price and I would like to have in my pivot table two values like :

pt.Values.Add("NumberOfOrders");
pt.Values.Add("Price");

if you add two Values to that pivot table it crashes.

Parsing of rich text inside inline string fails

Hello. Try to read xlsx file, problem with multiline cell. My code:

            var workbook = new XLWorkbook(path);
            var ws1 = workbook.Worksheet(3);
            workbook.SaveAs("tmp.xlsx");
            
            int rowCount = 20; //example
            //first row headers
            for(int i = 1 ;i<rowCount;i++)
            {
                var row = ws1.Row(i);
                IXLCell cell = row.Cell("E");     //.Cell(5); 
                bool isEmpty = cell.IsEmpty();      //always empty on this column, but in original have 6 lines
                var val = cell.Value;   //cell.
                //vals += val + Environment.NewLine;
            }

Values at file:

If i open workbook and resave it from code (.SaveAs) - values not lost. But in code (.Value , .GetString etc) it seens like empty cell.
What i do wrong?

Remove Autofilter problem

worksheet.AutoFilter.Enabled=false;

Throws Exception

Sort(3); does not work on sheets

Created an Excel file with 11 worksheets, had set the worksheets

` var wb = new XLWorkbook();
var ws1 = wb.Worksheets.Add("Da
var ws2 = wb.Worksheets.Add("S
var ws3 = wb.Worksheets.Add("H
var ws4 = wb.Worksheets.Add("W
var ws5 = wb.Worksheets.Add("W
var ws6 = wb.Worksheets.Add("R
var ws7 = wb.Worksheets.Add("B
//and set some values
ws.Cell("A" + c).Value = Pr
ws.Cell("B" + c).Value = Pr
ws.Cell("C" + c).Value = Na
ws.Cell("D" + c).Value = pr
ws.Cell("E" + c).Value = St
ws.Cell("F" + c).Value = Sc
ws.Cell("G" + c).Value = t
ws.Cell("H" + c).Value = Pa
ws.Cell("I" + c).Value = Pw
ws.Cell("J" + c).Value = Nu
ws.Cell("K" + c).FormulaR1C
ws.Cell("L" + c).Value = Wo
ws.Cell("M" + c).Value = Mo
ws.Cell("N" + c).Value = Pw
ws.Cell("O" + c).Value = Pw
ws.Cell("P" + c).Value = "-
ws.Cell("Q" + c).Value = "-
ws.Cell("G" + c).Style.Numb
ws.Cell("K" + c).Style.Numb
ws.Cell("L" + c).Style.Numb

// and some cells have formatting
ws.Cell("K" + c).Style.NumberFormat.Format = "0%"; // formats for percent, zero decimal places
ws.Cell("L" + c).Style.NumberFormat.Format = "0%";
ws.Cell("M" + c).Style.NumberFormat.Format = "0%";
ws.Cell("N" + c).Style.NumberFormat.Format = "0%";
ws.Cell("O" + c).Style.NumberFormat.Format = "0%";

                ws.Cell("H" + c).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                ws.Cell("I" + c).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                ws.Cell("J" + c).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                ws.Cell("K" + c).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                ws.Cell("L" + c).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                ws.Cell("M" + c).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                ws.Cell("N" + c).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                ws.Cell("O" + c).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;

//then color the cells based on something else
if ( StartDate.Contains("2009"))

                {

                    ws2.Cell("A"  + c).Style.Fill.BackgroundColor = XLColor.AliceBlue;
                    ws2.Cell("B"  + c).Style.Fill.BackgroundColor = XLColor.AliceBlue;
                    ws2.Cell("C"  + c).Style.Fill.BackgroundColor = XLColor.AliceBlue;
                    ws2.Cell("D"  + c).Style.Fill.BackgroundColor = XLColor.AliceBlue;
                    ws2.Cell("E"  + c).Style.Fill.BackgroundColor = XLColor.AliceBlue;
                    ws2.Cell("F"  + c).Style.Fill.BackgroundColor = XLColor.AliceBlue;
                    ws2.Cell("G"  + c).Style.Fill.BackgroundColor = XLColor.AliceBlue;

//covering dates there are different colors.
ws2.Sort("E");
ws2.Sort(5, XLSortOrder.Descending, false, true);

here, 2 sort commands added the first then second. Previously this worked fine, several worksheets would color in row data and the sheets would sort right.

`

Now, all worksheets sort by column 1 no matter what Sort(n) command is there or where its placed, appears the syntax is correct yet it will not sort?

number format

Hi
I generate a spreadsheet with SSIS and I am trying to format it in the last step with ClosedXML

I set a table style and it works, but when I try to set the number format in several columns does not work.
I mean, when I open the file the values are not formatted but if I check the format column in the excel file it is set. If I chage the value manually the new value is formatted formatted

Any idea??

thanks

Image support

Cannot set horizontal alignment on merged cells

I'm using ClosedXML to create and format an Excel document. In that document I have some merged cells, which I'm finding it impossible to format to have a left text alignment.

ws.Cell(rowCounter, 1).Value = "Some test text";
ws.Cell(rowCounter, 1).Style.Font.Bold = true;
ws.Cell(rowCounter, 1).Style.Font.FontColor = XLColor.White;
ws.Cell(rowCounter, 1).Style.Font.FontSize = 14;
ws.Cell(rowCounter, 1).Style.Fill.BackgroundColor = XLColor.AirForceBlue;
ws.Cell(rowCounter, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
string titleRange = String.Format("A{0}:H{0}", rowCounter);
ws.Range(titleRange).FirstCell().Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; //doesn't work, also tried it after the Merge() call, didn't work there either
ws.Range(titleRange).Merge();

ws.Range(titleRange).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; //doesn't work
ws.Range(titleRange).Cells().Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; //doesn't work

Is there a way of left aligning text in merged cells?

AHA :)

Please any one help me out to read blank cell using Closed XML.

Theme colors

Can you implement real theme colors?

dSpreadsheet.WorkbookPart.ThemePart.Theme.ThemeElements.ColorScheme.Accent1Color

ClosedXML/ClosedXML/Excel/XLWorkbook.cs

Line 330 in daa413d

private void InitializeTheme()

Investigate "Unreadable content" Issue

https://closedxml.codeplex.com/workitem/8466

Sheet->Right-to-Left does not work

It looks ClosedXML does not load sheet options properly.

Symptom: Open an excel file including right-to-left sheet, and save it again by Save() or SaveAs() method, then open the file by excel, now the right-to-left sheet gets left to right.

ClosedXML.Excel.XLWorkbook _excel;
_excel = new XLWorkbook("full file path");
....
do somting.
...
_excel.SaveAs("full new file path");

Copying cell with certain formula results in corrupt cell

Hi

I found an issue with while trying to copy a cell with a valid FormulaA1 to a different cell. Then copying works just fine but when I try to access formula in the copied cell I get an System.FormatException.
This exception is thrown on the FormulaA1, HasFormula and the Value property of the cell. Intrestingly the FormualR1C1 returns a value.

My guess there might be an issue in manipulating the formula on copy.

Here my code for reproduction:

FileInfo file = new FileInfo("Testfile.xlsx");
using (FileStream stream = file.OpenRead())
{
    using (XLWorkbook workbook = new XLWorkbook(stream))
    {
        IXLWorksheet workSheet = workbook.Worksheet("Sheet1");

        workSheet.Cell("B44").Value = workSheet.Cell("A44");

        Debug.WriteLine(workSheet.Cell("B44").HasFormula);
    }
}

The Excelfile is an empty file with this formula in the cell A44:
=IF(A9="Tender";0;(A72-(A62+A63+A65+A67+A69))*A17)

Thanks for your help in advance.