How do you add text wrapping to a cell using OpenXml when creating excel files?

Question

How do you set the format of a text cell to wrap when generating .xlsx files with OpenXml? Here is the code I have currently:

public void Excel()
    {
        var viewModel = new RequirementIndexData();
        viewModel.Requirements = db.Requirement;

        MemoryStream ms = new MemoryStream();

        SpreadsheetDocument dc = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook);

        WorkbookPart mdp = dc.AddWorkbookPart();
        WorksheetPart wsp = mdp.AddNewPart<WorksheetPart>();
        Workbook wb = new Workbook();
        FileVersion fv = new FileVersion();
        fv.ApplicationName = "Microsoft Office Excel";
        Worksheet ws = new Worksheet();

        SheetData sd = new SheetData();

        Columns columns = new Columns();
        columns.Append(CreateColumnData(1, 1, 95));
        ws.Append(columns);


        Row r1 = new Row() { RowIndex = (UInt32Value)1u };

        Cell c1 = new Cell();
        c1.DataType = CellValues.SharedString;
        c1.CellValue = new CellValue(OpenSoft.AgileAnalytics.EF.App_LocalResources.GlobalRes.VersionNumber + " " + viewModel.Requirements.OrderBy(r => r.CreatedOn).Select(r => r.CurentVersion).First().ToString());

        r1.Append(c1);
        sd.Append(r1);

        for (int i = 2; i < viewModel.Requirements.Count() + 2; i++)
        {
            Row row2;
            row2 = new Row()
            {
                RowIndex = (UInt32)i,

                Height = 25,
                DyDescent = 1.50D,
                Hidden = false,
                Collapsed = false
            };
            Cell cell2 = new Cell() { StyleIndex = Convert.ToUInt32(1) };
            cell2.DataType = CellValues.SharedString;
            cell2.CellValue = new CellValue((i - 1).ToString() + ". " + viewModel.Requirements.OrderBy(r => r.CreatedOn).Select(r => r.Definition).ElementAt(i - 2).ToString());

            row2.Append(cell2);
            sd.Append(row2);
        }

        ws.Append(sd);
        wsp.Worksheet = ws;
        wsp.Worksheet.Save();
        Sheets sheets = new Sheets();
        Sheet sheet = new Sheet();
        sheet.Name = "specification1";
        sheet.SheetId = 1;
        sheet.Id = mdp.GetIdOfPart(wsp);
        sheets.Append(sheet);
        wb.Append(fv);
        wb.Append(sheets);            

        dc.WorkbookPart.Workbook = wb;
        dc.WorkbookPart.Workbook.Save();
        dc.Close();
        string filename = "specification1.xlsx";
        Response.Clear();
        byte[] dt = ms.ToArray();

        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", filename));
        Response.BinaryWrite(dt);
        Response.End();
    }

    private WorkbookStylesPart AddStyleSheet(SpreadsheetDocument spreadsheet)
    {
        WorkbookStylesPart stylesheet = spreadsheet.WorkbookPart.AddNewPart<WorkbookStylesPart>();

        Stylesheet workbookstylesheet = new Stylesheet();

        // <Fonts>
        Font font0 = new Font();         // Default font

        Fonts fonts = new Fonts();      // <APENDING Fonts>
        fonts.Append(font0);

        // <Fills>
        Fill fill0 = new Fill();        // Default fill

        Fills fills = new Fills();      // <APENDING Fills>
        fills.Append(fill0);

        // <Borders>
        Border border0 = new Border();     // Defualt border

        Borders borders = new Borders();    // <APENDING Borders>
        borders.Append(border0);

        // <CellFormats>
        CellFormat cellformat0 = new CellFormat() { FontId = 0, FillId = 0, BorderId = 0 }; // Default style : Mandatory

        CellFormat cellformat1 = new CellFormat(new Alignment() { WrapText = true }); // Style with textwrap set


        // <APENDING CellFormats>
        CellFormats cellformats = new CellFormats();
        cellformats.Append(cellformat0);
        cellformats.Append(cellformat1);


        // Append FONTS, FILLS , BORDERS & CellFormats to stylesheet <Preserve the ORDER>
        workbookstylesheet.Append(fonts);
        workbookstylesheet.Append(fills);
        workbookstylesheet.Append(borders);
        workbookstylesheet.Append(cellformats);

        // Finalize
        stylesheet.Stylesheet = workbookstylesheet;
        stylesheet.Stylesheet.Save();

        return stylesheet;
    }

Answer 1

You need to define styles for this. Styles are defined inside a stylesheet. Each style has an ID and when you create Cells you can refer defined style ID.

Defining stylesheet for the spreadsheet:

private WorkbookStylesPart AddStyleSheet(SpreadsheetDocument spreadsheet)
{
    WorkbookStylesPart stylesheet = spreadsheet.WorkbookPart.AddNewPart<WorkbookStylesPart>();
    Stylesheet workbookstylesheet = new Stylesheet();

    // <Fonts>
    Font font0 = new Font();            // Default font
    Fonts fonts = new Fonts();          // <APPENDING Fonts>
    fonts.Append(font0);

    // <Fills>
    Fill fill0 = new Fill();            // Default fill
    Fills fills = new Fills();          // <APPENDING Fills>
    fills.Append(fill0);

    // <Borders>
    Border border0 = new Border();      // Defualt border
    Borders borders = new Borders();    // <APPENDING Borders>
    borders.Append(border0);

    // <CellFormats>
    CellFormat cellformat0 = new CellFormat()   // Default style : Mandatory
    { 
        FontId = 0, 
        FillId = 0, 
        BorderId = 0 
    };
    CellFormat cellformat1 = new CellFormat(new Alignment() { WrapText = true });          // Style with textwrap set

    // <APPENDING CellFormats>
    CellFormats cellformats = new CellFormats();
    cellformats.Append(cellformat0);
    cellformats.Append(cellformat1);

    // Append FONTS, FILLS , BORDERS & CellFormats to stylesheet <Preserve the ORDER>
    workbookstylesheet.Append(fonts);
    workbookstylesheet.Append(fills);
    workbookstylesheet.Append(borders);
    workbookstylesheet.Append(cellformats);

    // Finalize
    stylesheet.Stylesheet = workbookstylesheet;
    stylesheet.Stylesheet.Save();

    return stylesheet;
}

Now when you add cells, use the defined style ID as follows:

// Assign our defined style with text wrap.
Cell c1 = new Cell(){ StyleIndex = Convert.ToUInt32(1) };

EDIT: You need to add stylesheet after adding workbookpart.