Writing a excel using closed XML - adding custom column names

Question

I need to write a list of objects to excel sheet as a table, in which each column represents object attributes or values. To the below method, Im passing column names in a separate List and data objects in a List, I managed to get the data displayed like below, but still I could not get the columns to display properly.

I referred the below documentation, but I could not find a way to get the titles display properly. https://github.com/closedxml/closedxml/wiki/Inserting-Tables

Code

public string CreateExcelFile<T>(IEnumerable<T> list, string sheetName, string headerTitle, List<string> titles, string fileName, string savedPath)
{
    var wb = new XLWorkbook();
    var ws = wb.Worksheets.Add(sheetName);

    ws.Cell(1, 1).Value = headerTitle;                  // sets excel sheet header

    var rangeTitle = ws.Range(3, 1, 3, titles.Count);   // range for row 3, column 1 to row 3, column titles.Count
    rangeTitle.AddToNamed("Titles");

    // Need to add columns names with in rangeTitle 
    //rangeTitle.InsertData(titles);

    // write data from row 4 onwards
    if (list != null && list.Any())
    {
        ws.Cell(4, 1).InsertData(list);                
    }
    else
    {
        ws.Cell(4, 1).Value = "No data to show";
    }

    // styles
    var titlesStyle = wb.Style;
    titlesStyle.Font.Bold = true;
    titlesStyle.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
    titlesStyle.Fill.BackgroundColor = XLColor.Amber;

    // style titles row
    wb.NamedRanges.NamedRange("Titles").Ranges.Style = titlesStyle;

    ws.Columns().AdjustToContents();
    var filePath = savedPath + string.Format("{0}.xlsx", fileName);
    wb.SaveAs(filePath);

    return filePath;
}

Output excel

Output Im trying to get - I need to get values stored in titles in the Yellow highlighted row.

Can anyone help?

nandox · Accepted Answer · 2020-10-15 16:15:07Z

3

You could use InsertTable. The data is inserted as an Excel Table:

ws.Cell(1, 1).InsertTable(list.AsEnumerable());

answered Oct 15, 2020 at 16:15

nandox

1015 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

BUDDHIKA · Accepted Answer · 2017-10-06 05:16:43Z

0

I managed to get the columns to display by doing below.

// Need to add columns names with in rangeTitle 
for (int i = 0; i < titles.Count; i++)
{
    var columnNumber = i + 1;
    ws.Cell(3, columnNumber).Value = titles[i];
}

This works for now. But, I wonder is there a better way to doing things (without manually assigning column names like above).

answered Oct 6, 2017 at 5:16

BUDDHIKA

3162 gold badges9 silver badges25 bronze badges

Comments

asherber · Accepted Answer · 2019-11-01 19:00:46Z

0

ws.Cell(3, 1).Value = new [] { titles };

If you set Value to an array, ClosedXML will write each object in the array to its own row, with one property of the object per column. (See https://github.com/ClosedXML/ClosedXML/wiki/Copying-IEnumerable-Collections)

In this case, the array we're passing in has only one object – an array of titles. That inner array gets written to the target row, and each item in the inner array gets written to a column in that row.

answered Nov 1, 2019 at 19:00

asherber

2,7331 gold badge16 silver badges12 bronze badges

Collectives™ on Stack Overflow

Writing a excel using closed XML - adding custom column names

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related