Extend Excel Cell Text and keep formatting C#

Question

Hi, what i am trying to do sounds quite simple, but it is really not ! I need to add some text to a existing cell in Excel and keep all the formatting.

e.g.:
before: Cell Value = "Some Text"
after: Cell Value = "Some Text and some more text" 

When u do the following in C#

cellRange.Value2 += " and some more text"

the result is:
Cell Value = "Some Text and some more text". (without formatting)

So i started playing arround with the Range.Characters property and found out that i could copy all the format in front off changing the text and then re-assign it afterwards.:

// Create a dictionary to save the font settings for each character;
Dictionary<int, ExcelFont> fontDictionary = new Dictionary<int, ExcelFont>();

ExcelFont excelFont;

// Iterate the characters and get their settings:
for (int i = 0; i < cellRange.Characters.Count; i++)
{
   excelFont = new ExcelFont();
   excelFont.characters = cellRange.Characters[i, 1];
   excelFont.Bold = excelFont.characters.Font.Bold;
   excelFont.Color = excelFont.characters.Font.Color;
   excelFont.FontStyle = excelFont.characters.Font.FontStyle;
   excelFont.Size = excelFont.characters.Font.Size;
   excelFont.Italic = excelFont.characters.Font.Italic;
   excelFont.Name = excelFont.characters.Font.Name;
   excelFont.Strikethrough = excelFont.characters.Font.Strikethrough;
   excelFont.Subscript = excelFont.characters.Font.Subscript;
   excelFont.Superscript = excelFont.characters.Font.Superscript;
   excelFont.ThemeFont = excelFont.characters.Font.ThemeFont;
   excelFont.Underline = excelFont.characters.Font.Underline;

   fontDictionary.Add(i, excelFont);
}

// Assign the text:
cellRange.Value2 = cellRange.Value2 + "and some more text";
                
 // Re assign the font for each character:
for (int i = 0; i < fontDictionary.Count; i++)
{
    fontDictionary[i].characters.Font.Bold = fontDictionary[i].Bold;
    fontDictionary[i].characters.Font.Color = fontDictionary[i].Color;
    fontDictionary[i].characters.Font.FontStyle = fontDictionary[i].FontStyle;
    fontDictionary[i].characters.Font.Size = fontDictionary[i].Size;
    fontDictionary[i].characters.Font.Italic = fontDictionary[i].Italic;
    fontDictionary[i].characters.Font.Name = fontDictionary[i].Name;
    fontDictionary[i].characters.Font.Strikethrough = fontDictionary[i].Strikethrough;
    fontDictionary[i].characters.Font.Subscript = fontDictionary[i].Subscript;
    fontDictionary[i].characters.Font.Superscript = fontDictionary[i].Superscript;
    fontDictionary[i].characters.Font.ThemeFont = fontDictionary[i].ThemeFont;
    fontDictionary[i].characters.Font.Underline = fontDictionary[i].Underline; 
}

But this is getting really slow when the text is long. There must be a better solution.
 

Answer 1

After seeing your source, I thought it’s possible with Word. So, I created the source that I referred Cindy Meister’s posts. Please, refer to below source and understand me to not good at VSTO Word.
Plus, I couldn’t create the part of paste because of my insufficient knowledge on VSTO Word.
You can see the result you want when pasting Ctrl + V in Excel.
If other people who know well about this part correct it to better one, it helps me to study VSTO. Thanks.

I created with VS2010 Consoleapplication

 static void Main(string[] args)
    {
      var excel = new Excel.Application();
      excel.Visible = true;
      var workbook = excel.Workbooks.Open("g:\\test2.xlsx");
      excel.get_Range("a1", "c1").Copy();

      object test = Word.WdPasteDataType.wdPasteText;
      object missing = System.Type.Missing;

      var word = new Word.Application();
      word.Visible = true;
      var document = word.Documents.Add();
      word.Activate();

      object s = 0;
      Word.Range rngstart = document.Range(0, 0);

      rngstart.Paste();

      document.Tables[1].ConvertToText();
      Word.Range rng = document.Range(0, Type.Missing);
      rng.Find.Execute(FindText: "-", ReplaceWith: "", Replace: Word.WdReplace.wdReplaceAll);
      rng.Copy();   
     }

---

http://vsto.tistory.com

Answer 2

Hi VSTO_Begginer,

thanks for your reply, your solution could really work ;)  but there must be a better solution then just copying values to Word and back to Excel by users clipboard.

This is a really basic Excel automation usecase so why is there no VSTO implementation for it ?
I tryed this also via Visual Basic without success, its not a c# related issue.

Thanks 

Answer 3

Hi again,

I found out that the following does the job like expected:

cellRange.Characters[cellRange.Value2.ToString().Length, 1].Insert(" and some more text");

but only if there are not more then 265 characters inside the cell. :(

I can use the characters collection only if there are not more then 256 items in it.

Answer 4

Did you find a workaround to allow you to add text and retain the original formatting on the original text?  

I am dealing with this limitation right now trying to replace some tagged text and losing the formatting.  

ws.Range("A3").Text = CStr(ws.Range("A3").Text).Replace("[CompanyName]", strCompanyName)

The above code will modify the cell, but lose the original formatting.

When I use the Characters collection it retains the formatting, but only if the cell contains less than 256 characters..

Dim repl As String = "[CompanyName]"
Dim idx As Integer
rng = ws.Range("A3")
idx = CStr(rng.Text).LastIndexOf(repl) + 1

rng.Characters(idx, Len(repl)).Delete()
rng.Characters(idx, 1).Insert(strCompanyName)

If the cell contains more than 256 characters, this code will throw an error at the .Delete()

System.Runtime.InteropServices.COMException (0x800A03EC): Delete method of Characters class failed

So, is there a workaround for this?

Thanks!

Answer 5

I found very useful your code because I need to read text from a excell cell with several formats, but I have some doubts:

How I declare or make reference to ExcelFont?,  I need to add a asambly?

 

Thanks,

Alejandro

 

 

 

---

Alejandro Castrejon

Answer 6

Hello.

Here is an example of a function that replaces 1 to many placeholders in a formatted Excel range and keeping the cell formatting attributes. The placeholders are defined in { }. In this example the placeholders can be set anywhere in the XL sheet.

Example: "Your name is {Name} and you phone number is {PhoneNumber}". In this example Name and PhoneNumber are replaced by actual values that are returned by the FindPlaceHolderValue function (in the real program there is a list of named values).

private static void ReplaceValuesInFormattedCell(Worksheet sheet) {
    Range formattedCell = sheet.Cells.Find(What: "{", LookIn: XlFindLookIn.xlValues, SearchDirection: XlSearchDirection.xlNext, LookAt: XlLookAt.xlPart);
    while (formattedCell != null) {
        // Get placeholder name
        int phEnd = formattedCell.Value.IndexOf("}");
        if (phEnd < 0)
            throw new SystemException(String.Format("Syntax error in placeholder: {0}", formattedCell.Value));
        int phStart = formattedCell.Value.IndexOf("{");
        string phName = formattedCell.Value.Substring(phStart + 1, phEnd - (phStart + 1));
        // Get the placeholder value
        string phValue = FindPlaceHolderValue(phName);
        // Delete {placeholdername}
        formattedCell.Characters[phStart + 1, phName.Length + 1].Delete();
        // Insert placeholder value (keep formatting atrributes)
        formattedCell.Characters[phStart + 1, 1].Insert(phValue);
        // Next placeholder
        formattedCell = sheet.Cells.Find(What: "{", LookIn: XlFindLookIn.xlValues, SearchDirection: XlSearchDirection.xlNext, LookAt: XlLookAt.xlPart);
    }
}

Hope this can help.

Cheers
Augusto

Answer 7

Hi Augusto,

it's been quite a while since i posted on here.

Is your code working for cells with more than 256 characters?

Thanks