UsedRange.RowCount is not updated after an insert

Question

I'm trying to write an Extension methods that adds a generic Item T to the workbook, I've got problem since the UsedRange.RowCount is not incremented after InsertMethod is called

    public static RowItem<T> AddItem<T>(this SpreadsheetGear.IWorksheet worksheet, T item) where T : class
    {
        int currentRow = worksheet.UsedRange.RowCount;
        //int currentRow = worksheet.UsedRange.RowCount;
        RowItem<T> newItem = new RowItem<T>
        {
            Item = item,
            RowIndex = currentRow
        };

        var reflected = item.GetType().GetProperties();

        for (int i = 0; i < reflected.Length; i++)
        {
            object value = reflected[i].GetValue(item);
            worksheet.Cells[currentRow, i].Value = value;
        }

        worksheet.UsedRange.Insert(SpreadsheetGear.InsertShiftDirection.Down);
        worksheet.WorkbookSet.CalculateFull();

        return newItem;
    }

 public static IEnumerable<RowItem<T>> AddItems<T>(this SpreadsheetGear.IWorksheet worksheet, IEnumerable<T> items) where T : class
    {
        var lst = new List<RowItem<T>>();

        foreach (var item in items)
        {
            var newItem = AddItem<T>(worksheet, item);

            lst.Add(newItem);
        }

        return lst;
    }

It's always 1 ...what am I doing wrong? my dummy class is

public class Dummy
{
    public string Desciption { get; set; }
    public double Value { get; set; }
    public DateTime Data { get; set; }
}

And I add items as

  using (var _ = new WorkbookViewLock(workbookView1))
  {
     var worksheet = workbookView1.ActiveSheet.Workbook.Worksheets[0];

     worksheet.AddItem<Dummy>(dummy);
     worksheet.AddItem<Dummy>(dummy2);
  }

score -1 · Answer 1 · answered Dec 18 '14 at 14:56

-1

If you are starting out with a blank worksheet (are you?), then it would make sense that the first two checks you make to worksheet.UsedRange.RowCount in your code would have a value of 1.

This is because the UsedRange of a blank worksheet will always be A1, which would correspond to a UsedRange.RowCount value of 1. That accounts for a value of 1 for your first AddItem(...) call.

The second AddItem(...) call is now looking at a worksheet that is populated with data, but still only 1 row since you've only added a single Dummy object at this point.

If you were to add a third Dummy object, you would see that the UsedRange increments to a value of 2.

FYI: You might have an additional issue with with your worksheet.UsedRange.Insert(...) line, since this will insert the number of rows that UsedRange currently consists of. It seems to me that if you are adding just a single Dummy object with this extension method, you should only insert, at most, one row...and that depends on where you want each new Dummy row to get added to the worksheet--the top or bottom of the used range. If you are inserting the new Dummy object at the top of the UsedRange, you should only apply Insert(...) on the top row of the UsedRange. If you are inserting the new Dummy object at the bottom of the UsedRange, no Insert(...) call is necessary at all since there's nothing below the UsedRange to shift down.

answered Dec 18 '14 at 14:56

Tim Andersen

3,014
1
15
11

Hello Tim, I'm using also the IEnumerable version... the fact that I've got 1 for the first and second it's quite a problem to me...I know that the spreadsheetgear has no knowledge of the current items bound but I need a some sort of helper to keep track of which row corresponds to which item of my source – advapi Dec 18 '14 at 15:24
I don't think your current code/description is clear enough to provide a more elaborate response. For example, as I mentioned in my answer, you give no indication about whether new rows go at the top or bottom of your `UsedRange` and the `Insert(...)` call doesn't seem to make this any more clear (have you tried running the code above?). Regardless, it seems to me you should be able to workaround this currentRow==1 issue in some way since you no know *why* it is occurring. I can't say exactly *how* you should go about this since there's just too little information on the overall requirement. – Tim Andersen Dec 18 '14 at 15:41
consider this scenario : I get some data from a source and have them in a List I wish to iterate on it and to add a Spreadsheetrow each time I pass a foreach step . since I need to update the value cell of the dummy object after it has been added (suppose a Value change on DB) I need to update that specific cell so I need to knwo which row correspond to which item – advapi Dec 18 '14 at 15:57
I understand that. However, your original question was why UsedRange.CurrentRow is always 1. Asking how to essentially "synchronize" a data source and worksheet data is an entirely different question and not a trivial task, especially depending on exactly how you are inserting this data in the sheet (i.e., are existing data rows getting shifted around due to new data coming in, which might invalidate your RowItem.RowIndex value for those existing data rows?). You may just consider starting a new SO Question, providing much more information on particular requirements and current code. – Tim Andersen Dec 18 '14 at 16:14

UsedRange.RowCount is not updated after an insert

1 Answers1