Open XML SDK: Format part of an Excel-cell

Question

Using Open XML for Excel with DocumentFormat.OpenXml.Spreadsheet, how do I set only part of a text to bold?

var cell = new Cell {
    //DataType = CellValues.InlineString,
    CellReference = "A" + 1
};

// TODO: Set "bold text" to bold style
//var inlineString = new InlineString();
//inlineString.AppendChild(new Text { Text = "Normal text... bold text..." });
//cell.AppendChild(inlineString);

Commented out code that's used now, but should or maybe should be changed.

Answer 1

To only set part of the text to bold you will want to control that by inserting your text into the SharedStringTable and making your cell's data type be SharedString and not InlineString. This will make the CellValue be a reference into this table, like 0, 1, 2, etc. and allow more control then doing an inline string.

Here is some sample code on how to make the second part of the pharse "Normal text... bold text..." bold:

       // Creates an SharedStringItem instance and adds its children.
        public SharedStringItem GenerateSharedStringItem()
        {
            SharedStringItem sharedStringItem1 = new SharedStringItem();

            Run run1 = new Run();
            Text text1 = new Text(){ Space = SpaceProcessingModeValues.Preserve };
            text1.Text = "Normal text… ";

            run1.Append(text1);

            Run run2 = new Run();

            RunProperties runProperties1 = new RunProperties();
            Bold bold1 = new Bold();
            FontSize fontSize1 = new FontSize(){ Val = 11D };
            Color color1 = new Color(){ Theme = (UInt32Value)1U };
            RunFont runFont1 = new RunFont(){ Val = "Calibri" };
            FontFamily fontFamily1 = new FontFamily(){ Val = 2 };
            FontScheme fontScheme1 = new FontScheme(){ Val = FontSchemeValues.Minor };

            runProperties1.Append(bold1);
            runProperties1.Append(fontSize1);
            runProperties1.Append(color1);
            runProperties1.Append(runFont1);
            runProperties1.Append(fontFamily1);
            runProperties1.Append(fontScheme1);
            Text text2 = new Text();
            text2.Text = "bold text…";

            run2.Append(runProperties1);
            run2.Append(text2);

            sharedStringItem1.Append(run1);
            sharedStringItem1.Append(run2);
            return sharedStringItem1;
        }

To use this method you want to first find an instance of the SharedStringTable and then insert your new ShareStringItem into it:

            using (MemoryStream stream = new MemoryStream())
            {
                // create in-memory copy of the Excel template file
                byte[] byteArray = File.ReadAllBytes(TEMPLATE_FILE_NAME);
                stream.Write(byteArray, 0, (int)byteArray.Length);

                using (SpreadsheetDocument document = SpreadsheetDocument.Open(stream, true))
                {
                    // Set private variable template component references (for reuse between methods)
                    mExcelWorkbookPart = document.WorkbookPart;
                    mSharedStringTablePart = mExcelWorkbookPart.SharedStringTablePart;

                    mSharedStringTablePart.SharedStringTable.AppendChild(GenerateSharedStringItem());
                }

                return stream.ToArray();
            }