Open XML SDK: formatting part of an Excel cell

Question

Using Open XML for Excel with DocumentFormat.OpenXml.Spreadsheet , how to set only part of selected text?

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

The commented code that was used now, but should or may need to be changed.

Answer 1

To just highlight some of the text in bold, you will need to control this by pasting the text into a SharedStringTable and making the data type of your cell SharedString , not InlineString . This will cause CellValue to be a reference to this table, e.g. 0, 1, 2, etc. And it will allow more control, and then do an inline string.

Here is an example of how to make the second part of the frieze "Normal text ... bold text ..." in 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 SharedStringTable , and then insert a 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(); }