How can I generate an Excel file with a 90 degree letter rotated down header in C#?

Question

I am working on a Human Resources app, that needs to show a working environment excel report, like it's demonstrated in this image.

Now I am looking for help with this topic, I am using ClosedXML.Excel, currently I am generating my Excel files with a method i create myself, it enters a List of objects and creates an excel file in the response of a http request. Here is the code:

public static bool ConvertToExcel<T>(IList<T> data, string excelName, string sheetName)
    {
        PropertyDescriptorCollection properties =

        TypeDescriptor.GetProperties(typeof(T));

        DataTable table = new DataTable();

        foreach (PropertyDescriptor prop in properties)
            table.Columns.Add(prop.Name.Replace("_"," "), Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);

        foreach (T item in data)
        {
            DataRow row = table.NewRow();
            foreach (PropertyDescriptor prop in properties)
                row[prop.Name.Replace("_", " ")] = prop.GetValue(item) ?? DBNull.Value;
            table.Rows.Add(row);
        }

        try
        {
            using (XLWorkbook wb = new XLWorkbook())
            {
                wb.Worksheets.Add(table, sheetName);

                System.Web.HttpContext.Current.Response.Clear();
                System.Web.HttpContext.Current.Response.Buffer = true;
                System.Web.HttpContext.Current.Response.Charset = "";
                string FileName = excelName + ".xlsx";

                System.Web.HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                System.Web.HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + FileName);
                using (MemoryStream MyMemoryStream = new MemoryStream())
                {
                    wb.SaveAs(MyMemoryStream);
                    MyMemoryStream.WriteTo(System.Web.HttpContext.Current.Response.OutputStream);
                    System.Web.HttpContext.Current.Response.Flush();
                    System.Web.HttpContext.Current.Response.End();
                }
            }
        }
        catch (Exception e) 
        {
            throw e;
            return false;
        }
        return true;
    }

Because of the long names of the bosses it extends a lot the excel column for a simple percentage, I want to know if I can achieve to rotate down (90 degrees) the letter of the header. It's possible to achieve this with my current library ClosedXML.Excel? I want to used the same method to generate this working environment report.

Thanks beforehand :)

maybe [this post](https://stackoverflow.com/questions/44568593/rotate-text-only-in-specific-excel-row) can help you — blaze_125, Oct 17 '18 at 02:15
Well,in that post they used Microsoft.Office.Interop, but I am trying to use ClosedXML.Excel, because i used this library for my previous excel generated files. @blaze_125 — Johnny-Co Ruyzo, Oct 17 '18 at 02:23
then [this one](https://stackoverflow.com/questions/9998122/openxml-spreadsheetml-sideways-text) — blaze_125, Oct 17 '18 at 02:35

score 7 · Accepted Answer · answered Oct 17 '18 at 10:02

7

You can do that by using the aligment text rotation styles:

cell.Style.Alignment.SetTextRotation(90);

answered Oct 17 '18 at 10:02

Raidri

17,258
9
62
65

score 0 · Answer 2 · answered Oct 17 '18 at 13:35

Thanks @Raidri.

I changed the code:

   wb.Worksheets.Add(table, sheetName);

to:

   var ws = wb.Worksheets.Add(table, sheetName);
   ws.Row(1).Style.Alignment.SetTextRotation(180);
   ws.Tables.FirstOrDefault().ShowAutoFilter = false;

that way the text is rotated 90 degrees down and i remove the filters.

How can I generate an Excel file with a 90 degree letter rotated down header in C#?

2 Answers2