This project has moved and is read-only. For the latest updates, please go here.

Formula issue after worksheet copy

Oct 8, 2013 at 8:50 AM

First of all, thank you for a great library.

I have a worksheet, with the following formula i cell A3:
=((A1 /100)^(1/8))*(112+(0,9* A2))+0,1* A2-112
Please notice: Comma is used as decimal seperator (da-DK).

If i copy the worksheet, and try to access the formula in cell A3 of the copied worksheet, an exception is thrown.

Hopefully someone will be able to point out, if I am doing something wrong.

Test code:
using ClosedXML.Excel;
using System;
using System.Globalization;
using System.Linq;
using System.Threading;

namespace FormularTest
    class Program
        static void Main(string[] args)
            Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
            Thread.CurrentThread.CurrentUICulture = CultureInfo.InvariantCulture;

            using (var wbk = new XLWorkbook(@"c:\temp\formular-test.xlsx"))
                var firstSheet = wbk.Worksheets.First();
                var cell = firstSheet.Cell("A3");

                Console.WriteLine("HasFormula: {0}", cell.HasFormula);
                Console.WriteLine("FormulaA1: {0}", cell.FormulaA1);

                var sheetNew = firstSheet.CopyTo("New");
                cell = sheetNew.Cell("A3");

                Console.WriteLine("HasFormula: {0}", cell.HasFormula);
                Console.WriteLine("FormulaA1: {0}", cell.FormulaA1);
Details of the exception:
System.ArgumentOutOfRangeException was unhandled
  Message=Must be more than 0
Parameter name: column
       at ClosedXML.Excel.XLHelper.GetColumnLetterFromNumber(Int32 column) in c:\temp\ClosedXML\XLHelper.cs:line 94
       at ClosedXML.Excel.XLCell.GetA1Column(String columnPart, Int32 columnsToShift) in c:\temp\ClosedXML\Excel\Cells\XLCell.cs:line 1777
       at ClosedXML.Excel.XLCell.GetA1Address(String r1C1Address, Int32 rowsToShift, Int32 columnsToShift) in c:\temp\ClosedXML\Excel\Cells\XLCell.cs:line 1753
       at ClosedXML.Excel.XLCell.GetFormula(String strValue, FormulaConversionType conversionType, Int32 rowsToShift, Int32 columnsToShift) in c:\temp\ClosedXML\Excel\Cells\XLCell.cs:line 1708
       at ClosedXML.Excel.XLCell.GetFormulaA1(String value) in c:\temp\ClosedXML\Excel\Cells\XLCell.cs:line 1684
       at ClosedXML.Excel.XLCell.get_FormulaA1() in c:\temp\ClosedXML\Excel\Cells\XLCell.cs:line 896
       at ClosedXML.Excel.XLCell.get_HasFormula() in c:\temp\ClosedXML\Excel\Cells\XLCell.cs:line 2539
       at FormularTest.Program.Main(String[] args) in c:\temp\FormularTest\FormularTest\Program.cs:line 30
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
Oct 8, 2013 at 8:59 AM
Forgot to mention, tested against commit: 79843 (Sep 9, 2013)
Nov 21, 2013 at 4:50 AM
        private static readonly Regex R1C1Regex = new Regex(
            //@"(?<=\W)([Rr]\[?-?\d{0,7}\]?[Cc]\[?-?\d{0,7}\]?)(?=\W)" // R1C1
            //+ @"|(?<=\W)([Rr]\[?-?\d{0,7}\]?:[Rr]\[?-?\d{0,7}\]?)(?=\W)" // R:R
            //+ @"|(?<=\W)([Cc]\[?-?\d{0,5}\]?:[Cc]\[?-?\d{0,5}\]?)(?=\W)"); // C:C
             @"(?<=\W)([Rr](\[-?\d{0,7}\])?[Cc](\[-?\d{0,7}\])?)(?=\W)" // R1C1
            + @"|(?<=\W)([Rr](\[-?\d{0,7}\])?:[Rr](\[-?\d{0,7}\])?)(?=\W)" // R:R
            + @"|(?<=\W)([Cc](\[-?\d{0,5}\])?:[Cc](\[-?\d{0,5}\])?)(?=\W)"); // C:C
Nov 27, 2013 at 12:14 PM
Edited Nov 27, 2013 at 12:16 PM
First of all i like to appriciate for such a useful functionality.
I like to use it, My requirement is as below..
(1)Working on a wpf application with mvvm design pattern. Having the requirement to export to excel(independent of excel installation, should be compatible with 32bit and 64 bit both) the nested(master and child) datagrid data.
(2)I also have the requirement to format the header cells and also merge the cells from where child data suppose to start. as following

I think i can achieve all this using closed xml with fast speed, am i right?

I have few doubts.
(1)i am not able to run the examples. While i am trying to run ClosedXML_Examples.exe it throws error of object reference and redirects to debug point "f (opCell.FormulaReference.FirstAddress.Equals(opCell.Address))". Before it, i have corrected the excel file locations as D:\drive does not exist on my machine. Can you help me how can i make it running.
(2)I read somewhere in reviews that it doesn't work properly or gives out of memory exception when no. of record is more then 1000, is this true...any correction made in the latest version?

Please help me with the above so i can start using codeplex and meet my deadlines.
Nov 29, 2013 at 8:05 AM
Edited Nov 29, 2013 at 8:06 AM
I am facing the problem with the following
            var workbook = new XLWorkbook();
            var worksheet = workbook.Worksheets.Add("Sample Sheet");
            worksheet.Cell("A1").Value = "Hello World!";
it throws the exception at saveas, no matter what address i have been given there, please help it is just the start?

Delay in reply is great loss at my project end, please reply my both the posts?
Dec 2, 2013 at 8:40 PM
(1) The file that defines where the excel files get created is CreateFiles.cs in the Creating directory of the ClosedXML_Examples project.
(2) I have created Excel files with over 100,000 rows no problem.
(3) You have to give a valid file name for the SaveAs. @"C:\" is not a file name. Try using @"C:\Filename.xlsx" Also if you are running this via a web application, the server will not access directory like C:\ which is outside of the application location. Try using relative address @".\Filename.xlsx"