CODEWITHSUNDEEP
c#closedxml
BUDDHIKA
BUDDHIKA

Reputation: 316

Writing a excel using closed XML - adding custom column names

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

enter image description here

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

Can anyone help?

Upvotes: 1

Views: 17408

Answers (3)

nandox
nandox

Reputation: 101

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

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

Upvotes: 3

asherber
asherber

Reputation: 2713

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.

Upvotes: 0

BUDDHIKA
BUDDHIKA

Reputation: 316

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).

Upvotes: 0

Related Questions