copy a list to a SpreadSheetGear Irange

Question

I have the following code:

        using (CPASEntities ctx = new CPASEntities())
        {
            IWorksheet ws = wb.Worksheets[0];
            ws.Name = "Summary";
            var tsm = (from x in ctx.tblTimesheetMasters
                       where x.TSID == TSID
                       select new
                       {
                           TimesheetID = x.TSID,
                           Comments = x.TSComments,
                           Vendor = x.tblVendor.Vendor_Name,
                           StartDate = x.TSStartDate,
                           Author = x.TSAuthor,
                           Approver = x.TSApprover,
                           Override_Approver = x.TSOverrideApprover,
                           Status = x.tblTimesheetStatu.TSStatusDesc
                       }
                      ).ToList();
            SpreadsheetGear.IRange range = ws.Cells["A1"];
            // I want to copy the entire tsm list to this range, including headings.

        }

As the comment states, I want to put that entire list into the ws worksheet starting at A1. I include the code in case it's easier to use a different construct. FWIW, there will be only one entry...TSID is the primary key. I can, of course, use the .FirstorDefault() construct if that is important. I thought it not important.

score 1 · Accepted Answer · answered Mar 16 '13 at 22:46

1

Your range is only one cell. You need a range big enough to contain all the cells the list would populate.

To populate your worksheet with the list, you could do something like this.

    int iRow = 0;
    int iCol = 0;
    if (tsm.Count() > 0)
    {
      foreach (var prop in tsm[0].GetType().GetProperties())
      {
        ws.Cells[iRow, iCol].Value = prop.Name;
        iCol++;
      }
      iRow++;
      foreach (var t in tsm)
      {
        iCol = 0;
        foreach (var prop in t.GetType().GetProperties())
        {
          ws.Cells[iRow, iCol].Value = prop.GetValue(t, null);
          iCol++;
        }
        iRow++;
      }
    }

If you want a range, you could add this line.

SpreadsheetGear.IRange range = ws.Cells[0, 0, iRow - 1, iCol - 1];

answered Mar 16 '13 at 22:46

Daniel

5,602
4
33
36

The issue is unrelated to the definition of the range. If you use my definition as shown (and copied from the SSG web site...) everything works fine using IRange.copyfromDatatable method. I wanted to avoid setting up a datatable from EF, since the constructs are similar, but looks like it is what I'll have to do. It's easier than what you've proposed (which certainly will work....) Thank you. – JimS-CLT Mar 21 '13 at 12:46
Right, it would be nice if IRange had a copyFromList method. Another approach would be to convert the list to a datatable using a ToDataTable method like the one described here: http://stackoverflow.com/questions/564366/convert-generic-list-enumerable-to-datatable. However, that approach is similar to the answer I gave. – Daniel Mar 21 '13 at 16:21
Yeah, I gave up and used a SQL-sourced DataTable. When I embarked on this ASP.NET journey, I felt like my best strategy would be to use the latest techniques, since they would become mainstream during my long learning curve. Well, the learning curve is indeed long, but the technology (EF) shows many signs of extreme youth and limited acceptance. I've learned much about EF, including lots of ways around it...lol. Thanks for your help.... – JimS-CLT Mar 21 '13 at 20:04

copy a list to a SpreadSheetGear Irange

1 Answers1