CODEWITHSUNDEEP
c#asp.netexcelclosedxml
LearningPal
LearningPal

Reputation: 584

How to add multiple datatable to one worksheet using closedxml?

I have multiple datatables with data and I would like to add those in single sheet with space. I am using ClosedXML to develop export excel utility.

Upvotes: 6

Views: 6272

Answers (2)

C. Graham
C. Graham

Reputation: 358

Adding how to do this dynamically (assuming a List<DataTable>):

int currentRow = 1;//counter to keep track of what row we're on
IXLWorksheet worksheet = wb.AddWorksheet(sheetName: settings.sheetName);
foreach (DataTable table in tables)
{
    //Use the table name, and add it to the first cell above the table before insert
    worksheet.Cell(currentRow, 1).Value = table.TableName;
    worksheet.Cell(currentRow, 1).Style.Font.FontSize = 20;
    worksheet.Cell(currentRow, 1).Style.Font.SetBold();
    currentRow++;
    //now that the title is inserted and formatted, insert table
    worksheet.Cell(currentRow, 1).InsertTable(table);
    currentRow += table.Rows.Count + 3;
}
//optional for adjusting columns to their contents and setting wrap text
var cols = worksheet.Columns();
cols.AdjustToContents();
foreach(var a in cols)
{//set mas width to 50
    a.Width = a.Width > 50 ? 50 : a.Width;
}
cols.Style.Alignment.WrapText = true;

Upvotes: 2

LearningPal
LearningPal

Reputation: 584

I am through with below code

 wb.Worksheets.Add(dt);
 wb.Worksheet(1).Cell(5, 1).InsertTable(dt1);

Upvotes: 5

Related Questions