The content of a range cannot be clear completely?

[XLCYun]

Here is the code I wrote to append a new column and fill it will button, **it won't work in the second time I call it. It will throw a System.NullReferenceException Exception. **

this.DefaultWorksheet.AppendCols(1);
NewCol = this.DefaultWorksheet.MaxContentCol + 1;
this.DefaultWorksheet.ColumnHeaders[NewCol].Text = HeaderName; // this line will cause a exception in the second time. 
// set header.text of the new col
// add buttons to the cells of this new column

I will give as much as details as I can.
the call track is:

1. QueryButtonClick(): Fill a DataTable
2. Fill():

• use SetRows and SetCols to resize the worksheet to match the the size of the DataTable
• then I clear the whole(use ColumnCount and RowCount to generate a range) worksheet by ClearRangeContent() passing the flag CellElementFlag.All. It work just find when I test it, it will clear all the data inside the worksheet. I don't clear the header.text of all column in here.
• use worksheet["A1"] = DataTable to fill the worksheet

3. set filter to all columns
4. AppendButtonColumn(), as the code you can see above, it will append a new column in worksheet, I assume the new column shoule be empty(I clear the whole sheet and resize it to match the size of my DataTable), so it won't be counted into the MaxContentCol, so the index of the new column will be NewCol = MaxContentCol + 1; then I set this new column's text, and add button to every cell of this column. The first time I click the QueryButton, it can work. But the second time I got a System.NullReferenceException exception.
   I debug it and watch the change of three concerned variable: the ColumnCount, MaxContentCount and the NewCol. It will change like this:

• The first time:

Variable Name	At beginning	Call AppendCols	NewCol = MaxContentCol + 1
ColumnCount	17	18	18
MaxContentCol	16	16	16
NewCol	0	0	17

• The second time:

Variable Name	At beginning	Call AppendCols	NewCol = MaxContentCol + 1
ColumnCount	17	18	ERROR
MaxContentCol	16	17	ERROR
NewCol	0	0	ERROR

** When I call AppendCols(1) the second time, ColumnCount and MaxContentCount both increase one.
Is there something I did wrong?
Maybe it is because I didn't delete the header.text, so it will regard the new line as been used? If so, then SetRows and SetCols Method will reserved the old data so it actully is the method to determind how many rows and cols can be see? **

Right now already replace the AppendCol code with the Resize. It works.

NewCol = this.DefaultWorksheet.MaxContentCol + 1;
this.DefaultWorksheet.Resize(this.DefaultWorksheet.MaxContentRow + 1, NewCol + 1);

[jingwood]

A quick tip: if you still face trouble about resizing and clear content, remove a worksheet and create new empty one might be a faster workaround. But anyway this should be fixed.

[XLCYun]

Thanks for your advice. I use resize instead of append to add another column, it works now.