How to expand the Range of a Work Table?

May 26, 2015 at 6:51 AM
Edited May 26, 2015 at 6:54 AM
Hi Team,

I have a work table in a worksheet that has items and being used by other worksheet, however the calculations are wrong since I am not able to extend range of the data table. Can somebody shed me some light using closedXML, as my only work around at the moment is use excel interop, Resize like this
var range = xlSheet.get_Range("A1", oMissing).get_Resize(xlListItem.ListRows.Count + 1, xlListItem.ListColumns.Count);
xlListItem.Resize(range);
var workbook = new clsXML.XLWorkbook(localPath);
var worksheet = workbook.Worksheet("Raw Metrics Data");
var worktable = worksheet.Table("MetricsLogging");

Console.WriteLine("Number of items in Excel at START: " + worktable.RowCount());
if (data.Count > 0)
{
       var lastrow = worktable.LastRow().Cell("F").Value; // Created Date
       DateTime lastUpdate = DateTime.Now.AddYears(-2); // Default Value, 2 years before last run
       if (lastrow.ToString().Length > 0)
              lastUpdate = (DateTime)lastrow;
       Console.WriteLine("Last Updated: " + lastUpdate.ToLongDateString());

       var metrics = from m in data where m.CreatedDate > lastUpdate orderby m.CreatedDate ascending select new { m.LogType, m.Category, m.Item, m.UDC, m.CreatedBy.LookupValue, m.CreatedDate, m.VisitingUrl.Url, region = m.CreatedBy.LookupValue.Substring(0, m.CreatedBy.LookupValue.IndexOf("\\")) };
        var tableWithData = worktable.Cell(worktable.RowCount() + 1, 1).InsertData(metrics.AsEnumerable()); // Insert the Data in Worksheet (not in worktable)


         workbook.SaveAs(localPath);

         Console.WriteLine("Number of items in Excel at END: " + worktable.RowCount());
}
Thanks in advance

Regards,
Wendel
May 26, 2015 at 11:24 AM
Did a little workaround, but not sure if this is the best practice... anyway.. :) hopefully somebody will make good use of this in the future. :)
var metrics = from m in data where m.CreatedDate > lastUpdate orderby m.CreatedDate ascending select new { m.LogType, m.Category, m.Item, m.UDC, m.CreatedBy.LookupValue, m.CreatedDate, m.VisitingUrl.Url, region = m.CreatedBy.LookupValue.Substring(0, m.CreatedBy.LookupValue.IndexOf("\\")) };
var nextRow = worktable.RowCount() + 1;
Console.WriteLine("Number of items to be added: " + metrics.Count());
if (metrics.Count() != 0)
{
         worktable.InsertRowsBelow(metrics.Count(), true);
         var tableWithData = worktable.Cell(nextRow, 1).InsertData(metrics.AsEnumerable());
         workbook.SaveAs(localPath);
}
Marked as answer by rwnuevas on 5/26/2015 at 4:26 AM