This project has moved and is read-only. For the latest updates, please go here.

Text formatting in cell

Apr 4, 2011 at 9:18 AM
Edited Apr 4, 2011 at 9:25 AM

Hi,

I want to format text in a cell, e.g. set font weight to bold in text's 2nd row. Is this possible with ClosedXML? If not, were you be able to help me how can I make it with OpenXML SDK 2.0 after I processed the file with ClosedXML?

Thanks in advance.

Apr 4, 2011 at 1:58 PM
Edited Apr 4, 2011 at 2:22 PM

I solved the problem by this way. Cell text formats are: "<b>Title: <b>Text".

internal string Export(string fileName)
{
  XLWorkbook workbook = new XLWorkbook(fileName);
  // Fill with data
  // ...
  workbook.Save();

  OpenXmlExcelService oxmlService = new OpenXmlExcelService();
  oxmlService.FormatCellValues(fileName);
}
internal class OpenXmlExcelService
{
  private readonly string BoldFormatString = "<b>";

  internal void FormatCellValues(string fileName)
  {
    using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, true))
    {
      SetFontStyles(document);
    }
  }

  private void SetFontStyles(SpreadsheetDocument document)
  {
    SharedStringTable table = document.WorkbookPart.SharedStringTablePart.SharedStringTable;
    foreach (SharedStringItem item in table.Elements<SharedStringItem>())
    {
      if (item.InnerText.Contains(BoldFormatString))
      {
        SetTextBold(item);
      }
    }
  }

  private void SetTextBold(SharedStringItem item)
  {
    Text origText = item.GetFirstChild<Text>();
    string[] array = origText.InnerText.Split(new string[] { BoldFormatString }, StringSplitOptions.RemoveEmptyEntries);

    // If string starts with "<b>" even indexes are pointing to the bold text fragments, else odd indexes
    int firstBoldIndex = origText.InnerText.StartsWith(BoldFormatString) ? 0 : 1;

    for (int i = 0; i < array.Length; i++)
    {
      Run run = new Run();
      RunProperties runProperties = new RunProperties();
      FontSize fontSize = new FontSize() { Val = 10D };
      runProperties.Append(fontSize);

      if ((i - firstBoldIndex) % 2 == 0) // Bold?
      {
        Bold bold = new Bold();
        runProperties.Append(bold);
      }

      Text text = new Text { Space = SpaceProcessingModeValues.Preserve };
      text.Text = array[i];
      run.Append(runProperties);
      run.Append(text);
      item.InsertBefore(run, origText);
    }

    origText.Remove();
  }
}
FontSize settings are not necessary when using default font size.
Apr 5, 2011 at 5:13 AM

Thanks for the heads up. I'll add it to the to-do-list...