Workbook save problem

Jun 30, 2011 at 9:54 AM
Edited Jun 30, 2011 at 11:35 AM

Hi,

I switched to the version 0.56 and now I have a problem when I save one excel file (which was ok before)

 

Must be more than 0
Parameter name: column
   at ClosedXML.ExcelHelper.GetColumnLetterFromNumber(Int32 column) in C:\MyProject\ExternalSources\ClosedXML\ExcelHelper.cs:line 60
   at ClosedXML.Excel.XLCell.GetA1Column(String columnPart, Int32 columnsToShift) in C:\MyProject\ExternalSources\ClosedXML\Excel\Cells\XLCell.cs:line 1198
   at ClosedXML.Excel.XLCell.GetA1Address(String r1C1Address, Int32 rowsToShift, Int32 columnsToShift) in C:\MyProject\ExternalSources\ClosedXML\Excel\Cells\XLCell.cs:line 1181
   at ClosedXML.Excel.XLCell.GetFormula(String strValue, FormulaConversionType conversionType, Int32 rowsToShift, Int32 columnsToShift) in C:\MyProject\ExternalSources\ClosedXML\Excel\Cells\XLCell.cs:line 1136
   at ClosedXML.Excel.XLCell.GetFormulaA1(String value) in C:\MyProject\ExternalSources\ClosedXML\Excel\Cells\XLCell.cs:line 1112
   at ClosedXML.Excel.XLCell.get_FormulaA1() in C:\MyProject\ExternalSources\ClosedXML\Excel\Cells\XLCell.cs:line 704
   at ClosedXML.Excel.XLCell.get_InnerText() in C:\MyProject\ExternalSources\ClosedXML\Excel\Cells\XLCell.cs:line 103
   at ClosedXML.Excel.XLWorkbook.GenerateSharedStringTablePartContent(SharedStringTablePart sharedStringTablePart, SaveContext context) in C:\MyProject\ExternalSources\ClosedXML\Excel\XLWorkbook_Save.cs:line 605
   at ClosedXML.Excel.XLWorkbook.CreateParts(SpreadsheetDocument document) in C:\MyProject\ExternalSources\ClosedXML\Excel\XLWorkbook_Save.cs:line 122
   at ClosedXML.Excel.XLWorkbook.CreatePackage(Stream stream, Boolean newStream) in C:\MyProject\ExternalSources\ClosedXML\Excel\XLWorkbook_Save.cs:line 93
   at ClosedXML.Excel.XLWorkbook.SaveAs(String file) in C:\MyProject\ExternalSources\ClosedXML\Excel\XLWorkbook.cs:line 330

 

I throws the exception in ExcelHelper.GetColumnLetterFromNumber because the parameter is 0.

I searched a bit and I saw that in GetA1Column method from the XLCell class the part

 

 if (bIndex >= 0)
                {
                    columnToReturn = ExcelHelper.GetColumnLetterFromNumber(
                        Address.ColumnNumber +
                        Int32.Parse(columnPart.Substring(bIndex + 1, columnPart.Length - bIndex - 2)) + columnsToShift
                        );
                }

 

is posing problem

The columnPart parameter value is C[-2]

and the columnsToShift parameter value is 0

 

The Address.ColumnNumber value is 2

 

So the value of "Address.ColumnNumber + Int32.Parse(columnPart.Substring(bIndex + 1, columnPart.Length - bIndex - 2)) + columnsToShift" is equal to 0 and that's the problem.

The formula that causes the problem before the conversion to formulaA1 :

IF(ISERROR(IF(R5C[-2]="Yes",RC[-3]/R[-1]C[-3]-1,(RC[-5]/RC[-4])/(R[-1]C[-5]/R[-1]C[-4])-1)),"",IF(R5C[-2]="Yes",RC[-3]/R[-1]C[-3]-1,(RC[-5]/RC[-4])/(R[-1]C[-5]/R[-1]C[-4])-1))

Can I directly use the Address.ColumnLetter value instead of this calculation ? or is it something different?

Coordinator
Jun 30, 2011 at 1:57 PM

Please post a piece of code that reproduces the error...

Jul 1, 2011 at 10:23 AM

Hi,

I just get the 0.56.1 version and it works.

But I had a little problem with the  public static T[,] ResizeArray<T>(T[,] original, int rows, int cols) method in the ExcelHelper class.

I doesn't want to use a visualbasic assembly, so I replaced

Utils.CopyArray(original, newArray); by original.CopyTo(newArray, 0);

and it seems to work.

Coordinator
Jul 1, 2011 at 11:44 PM

I know... (I was in a hurry to fix the 10K rows problem so while testing I included the VB reference)

This has been fixed.

Oct 30, 2011 at 12:21 PM
Edited Oct 30, 2011 at 12:22 PM

I'm facing the same issue as pointed above.

Earlier, I had just 1 worksheet with no formulae and workbook.Save() was not throwing this error.

However, when I added another worksheet which contained a formula, it couldn't workbook.Save() successfully anymore.
Just to test it out, i removed the formulae and tried to save it again. This time it saved successfully.

Note : I'm not attaching the stack trace or error details as they have already been mentioned above. 

Is there any other solution to this, instead of downgrading to 0.56.1 version ? 

Jul 26, 2012 at 7:41 AM

Hi MDeLeon,

I am facing the same error with respect version 0.66.0 of .net 3.5.

Please let me know on what steps to follow, other than downgrading my version to 0.56.1

I am coping a range (AO1 to CK21) from the template sheet to my actual sheet.

This range contains many merged cells and most of them have formulas.

Please accept my request and please do the needful.

 

Thanks,  

Krishna.

Coordinator
Jul 26, 2012 at 2:32 PM

kkvid007,

How can I reproduce your error?

Jul 27, 2012 at 8:06 AM

Hi MDeLeon,

Can you please share your email id, so that i can send you the excel file that i am using.

I will also explain what exactly i am trying to achieve.

 

Thanks,

Krishna

Coordinator
Jul 28, 2012 at 2:28 AM

Send me a PM

Jul 28, 2012 at 10:55 AM

Can you please let me know on how to send a PM in codeplex?

Coordinator
Jul 28, 2012 at 3:48 PM

People tab -> MDeLeon

Jul 28, 2012 at 8:29 PM

I have sent a message to you MDeLeon. It will be from my hotmail account (kkvid / Krishna Kumar N).

Please accept my request and please do the needful.

I couldn't attach the desired excel file as there were no options for attachment in People tab -> MDeLeon -> Contact.

I kindly request you to reply to my email.

Thanks in advance.

Coordinator
Jul 29, 2012 at 3:03 AM

Check your email.

Jul 30, 2012 at 7:38 AM

Hi MDeLeon,

I have sent the email with the attachment.

Please check your email.

Thanks,

Krishna

Jul 31, 2012 at 4:36 AM

Hi MDeLeon,

Were you able to reproduce the error with the sample excel file that i had mailed you?

Thanks,

Krishna

Jul 31, 2012 at 7:43 AM
Edited Jul 31, 2012 at 7:44 AM

Hi MDeLeon,

I am facing the same issue with column delete.

System.ArgumentOutOfRangeException: Must be more than 0
Parameter name: column
at ClosedXML.Excel.XLHelper.GetColumnLetterFromNumber(Int32 column)
at ClosedXML.Excel.XLCell.GetA1Column(String columnPart, Int32 columnsToShift)
at ClosedXML.Excel.XLCell.GetA1Address(String r1C1Address, Int32 rowsToShift, Int32 columnsToShift)
at ClosedXML.Excel.XLCell.GetFormula(String strValue, FormulaConversionType conversionType, Int32 rowsToShift, Int32 columnsToShift)
at ClosedXML.Excel.XLCell.GetFormulaA1(String value)
at ClosedXML.Excel.XLCell.get_FormulaA1()
at ClosedXML.Excel.XLRangeBase.<Delete>b__6a(IXLCell c)
at ClosedXML.Excel.XLCellsCollection.<GetCells>d__18.MoveNext()
at System.Linq.Enumerable.<SelectManyIterator>d__14`2.MoveNext()
at ClosedXML.Excel.XLRangeBase.Delete(XLShiftDeletedCells shiftDeleteCells)
at ClosedXML.Excel.XLColumn.Delete()

I am deleting the excess columns depending on the data. 

When i do 

var col = Sheet.Column(colIndex);

I get the column reference.

But once i execute  

col.Delete();

I landed up with the above mentioned error. I am facing this issue since i upgraded my library reference from 0.66 to 0.66.1

Coordinator
Jul 31, 2012 at 2:51 PM

Dude you really need to chill. You don't have to write here and send me an email too. I'll get to this problem when I can.

Jul 31, 2012 at 3:15 PM

Hi MDeLeon,

I understand but I am on a very tight deadline and it end on Friday.

So please please please accept my request and please fix this as soon as possible.

Thanks,

Krishna

Coordinator
Jul 31, 2012 at 6:52 PM

If your deadline if Friday then you'd better start thinking of a contingency plan.

Coordinator
Aug 1, 2012 at 3:46 PM

I'm having a hard time following all your emails and posts. I honestly have no idea what the problem is or how to reproduce it. Please create a new issue on the Issue Tracker tab and attach a project that reproduces the error.