This project has moved. For the latest updates, please go here.

Populate existing Table with data?

Jan 31, 2012 at 5:29 PM

It would be really nice to be able to start with an existing workbook containing a table (complete with headers, style, column formulas, footer formulas, etc.), and then populate it with data by saying something like:

table.SetData(myGridOfData);

I've tried doing:

table.FirstRow().FirstCell().Value = myGridOfData;

...which sort-of works in that it inserts the values in the correct cells - but it does not expand the table to include the new cells.

(BTW, "table.FirstCell.Value = myGridOfData" completely corrupts the workbook...)

Is there any way to automatically extend the table to be a particular size?

Coordinator
Feb 1, 2012 at 3:47 AM

Hold that thought. We'll talk about this one once we're done with the other one...

Feb 1, 2012 at 10:03 AM

Further to this...

So I'm trying to cobble together something that will work with the existing library. 

I first clear the existing data using table.Rows(n).Delete - that "works" in that it shifts up the cells under the table (and only under the table).

I then insert enough rows for my data using table.InsertRowsBelow(numRows) - that also "works" in that it only affects rows under the table.

Having done this, when I open the workbook in Excel I can see that the table has indeed been expanded (since Excel automatically applies formatting to the rows). 

HOWEVER, the cell contents for the totals row that I had in my original table is now sitting halfway up the table. The table still *has* a totals row, but it's blank.

It seems that the totals row is correctly getting pulled up when I delete rows from the table, but is not getting pushed back down again by table.InsertRowsBelow.

I have since discovered that table.LastRow().InsertRowsBelow works as I needed. Should this be the default behaviour of table.InsertRowsBelow?

Coordinator
Feb 4, 2012 at 3:37 AM

Pick up the latest source code and try the following:

<!-- code formatted by http://manoli.net/csharpformat/ --> <!-- .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: Consolas, "Courier New", Courier, Monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #a31515; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } -->
            var wb = new XLWorkbook();
            var ws = wb.AddWorksheet("Sheet1");

            ws.FirstCell().SetValue("Category")
              .CellRight().SetValue("Value");

            var table = ws.Range("A1:B2").CreateTable(); // Leaving one row for the data
            table.ShowTotalsRow = true;
            table.Field("Value").TotalsRowFunction = XLTotalsRowFunction.Sum;

            var row = table.DataRange.FirstRow();
            for (Int32 i = 1; i <= 5; i++)
            {
                row.Field("Category").Value = "Cat" + i;
                row.Field("Value").Value = i;

                row = row.InsertRowsBelow(1).First(); // This is one way of adding rows
                // Another way would be to add all the rows you need and then populate them
            }
            row.Delete(); // Cleanup the last empty row

            wb.SaveAs(target);