Multiple tables in same worksheet

[Aaron]

I have a need to create an Excel Workbook with multiple worksheets and many of those worksheets will have multiple different tables inside of them.

I've learned how to create multiple worksheets but can't seem to figure out how to add multiple tables.  In the below code, you'll see I'm doing that by appending two stringbuilders together.  If I try to append the 2nd set of records ("DashboardFees") to anything other than a TableElement, I can't get it to run without an error.  Any assistance would be appreciated.  Here is what I have.

protected void rgDashboardProduction_GridExporting(object source, GridExportingArgs e)
        {
            IEnumerable<Record> productionRecords = GetDashboardProduction();
            IEnumerable<Record> feeRecords = GetDashboardFees();
 
            StringBuilder sb = new StringBuilder();
            WorksheetElement workSheet = new WorksheetElement("DashboardProduction"); //create new worksheet
            workSheet.Table = ParseRecord(productionRecords);
            
            workSheet.Render(sb); //generate xmlss code
            
            StringBuilder sb2 = new StringBuilder();
            WorksheetElement workSheet2 = new WorksheetElement("DashboardFees"); //create new worksheet
            workSheet2.Table = ParseRecord(feeRecords);
            workSheet2.Render(sb2); //generate xmlss code
 
            string output = string.Concat(sb.ToString(), sb2.ToString());
            e.ExportOutput = e.ExportOutput.Replace("</Styles>", "</Styles>" + output); //add the rendered worksheet to the output
        }



private TableElement ParseRecord(IEnumerable<Record> records)
        {
            TableElement table = new TableElement();
            RowElement headerRow;
            RowElement row;
            CellElement cell;
            ColumnElement column;
 
            PropertyInfo[] properties = records.First().Properties;
            int numColumns = properties.Length;
 
            for (int col = 1; col <= numColumns; col++)
            {
                column = new ColumnElement();
                column.Attributes.Add("ss:Width", (100 * col).ToString());
                table.Columns.Add(column);
            }
 
            //Add Header Row
            headerRow = new RowElement();
            foreach (PropertyInfo property in properties)
            {
                cell = new CellElement();
                cell.StyleValue = "headerStyle";
                cell.Data.DataItem = property.Name;
                headerRow.Cells.Add(cell);
            }
 
            //Add Data Rows
            foreach (Record record in records)
            {
                row = new RowElement();
                foreach (PropertyInfo property in record.Properties)
                {
                    cell = new CellElement();
                    cell.Data.DataItem = property.GetValue(record, null);
                    row.Cells.Add(cell);
                }
                table.Rows.Add(row);
            }
 
            return table;
        }

[Daniel]

Hello Aaron,

Every worksheet must have exactly one table element. You have to merge the data to the same table if you need to add more information in a later point. I understand that this approach is cumbersome but this is a limitation in the XMLSS format.

By the way, starting from Q1 2011 you can use the OnExcelMLWorkBookCreated event which will allow that you add the worksheets directly to the XMLSS structure.


Kind regards,
Daniel
the Telerik team