c# - 创建 excel 文件时，如何使用 OpenXml 将文本换行添加到单元格？

Question

在使用 OpenXml 生成 .xlsx 文件时，如何将文本单元格的格式设置为换行？这是我目前的代码:

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

您需要为此定义样式。样式在样式表中定义。每个样式都有一个 ID，当您创建单元格时，您可以引用定义的样式 ID。

为电子表格定义样式表:

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

现在当您添加单元格时，使用定义的样式 ID，如下所示:

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

编辑:您需要在添加工作簿部分后添加样式表。