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;
    }

Clance

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.

Afield

Thanks for your help. Unfortunately when you open a document Excel window appears with the message: "In the book "specitication.xlsx" found content that could not be read. Try to restore the contents of the book? If you trust the source of this workbook, click "Yes"." If you select "No", the document does not open. If you choose "Yes", then opens the document without style. – Denis

Did you had this problem previously ? Or did it occur after adding styles – Afield

This message appears after adding changes. I add stylesheet after adding workbookpart. I can insert code changes, but I think it's too much – Denis

This is the only way :) Actually if you need add styles you need to define all of these code .. And you define it only one time so nothing to worry [the correct way] .. btw code works fine.. Did you use correct Stylde ID index ?? Check it please – Afield

At the announcement of the cell, I wrote what you wrote Cell cell2 = new Cell() { StyleIndex = Convert.ToUInt32(1) }; – Denis

I changed the code in question, look, please. I've done everything you've written – Denis

Can you check it now, I updated your code on OP.. :) Run It and Check – Afield

Thank you so much! :) You helped me a lot, now I understood what each section of code – Denis

Couldn't get this to work, either I am adding in the stylesheet at the wrong time, or the ID is wrong (where do you define this?). Would be nice to have a full example as the code you details is never called in the problem statement – Duaneduarchy

Answer 2

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

// pathToExcel is filename of the existing file
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(pathToExcel, true))
{
    // Get workbookpart
    WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
    WorkbookStylesPart wbsp = CreateStylesheet(workbookPart.WorkbookStylesPart);

    // then access to the worksheet part
    List<WorksheetPart> worksheetPart = workbookPart.WorksheetParts.ToList();                 
    foreach (WorksheetPart WSP in worksheetPart)
    {                    
        // find sheet data
        IEnumerable<SheetData> sheetData = WSP.Worksheet.Elements<SheetData>();

        // Iterate through every sheet inside Excel sheet                    
        foreach (SheetData SD in sheetData)
        {                         
            IEnumerable<Row> row = SD.Elements<Row>(); //  Get the row                                                                    
            int rowindex = 1;
            foreach (Row currentrow in row)
            {
                foreach (Cell cell in currentrow.Descendants<Cell>())
                {
                    // Doing Wordwrap in the cell using openxml - in open xml we need to do cell level word wrap
                    cell.StyleIndex = Convert.ToUInt32(1);
                }
                // Here Row AutoFit property is not available, setting the row height manually and skipping 1st two rows
                if (rowindex > 2)
                {
                    currentrow.Height = currentrow.Height + 5;
                    currentrow.CustomHeight = true;                               
                }                         
                rowindex++; 
            }
        }
        WSP.Worksheet.Save();         
    }

    // workbookPart.Workbook.Save();
    spreadsheetDocument.Close();
}

private static WorkbookStylesPart CreateStylesheet(WorkbookStylesPart spreadsheet)
{
    WorkbookStylesPart stylesheet = spreadsheet;
    Stylesheet workbookstylesheet = new Stylesheet(); 

    // <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
    Fills fills = new Fills();
    Fill fill = new Fill();
    PatternFill patternFill = new PatternFill();
    patternFill.PatternType = PatternValues.None;
    fill.PatternFill = patternFill;
    fills.Append(fill);

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

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

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

    return stylesheet;
}

Godart

Please consider adding an explanation in plain English for your answer to improve its quality and describe how your code solves the OP's problem. – Gibbet