none
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.
     

    Friday, May 27, 2011 12:58 PM

Answers

  • 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
    • Proposed as answer by Bruce Song Tuesday, May 31, 2011 8:22 AM
    • Marked as answer by Bruce Song Thursday, June 02, 2011 3:12 AM
    Monday, May 30, 2011 2:36 AM

All replies

  • 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
    • Proposed as answer by Bruce Song Tuesday, May 31, 2011 8:22 AM
    • Marked as answer by Bruce Song Thursday, June 02, 2011 3:12 AM
    Monday, May 30, 2011 2:36 AM
  • 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 

    Monday, May 30, 2011 12:10 PM
  • 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.
    Monday, May 30, 2011 1:04 PM
  • 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!

    Friday, July 15, 2011 3:57 PM
  • 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
    Thursday, September 08, 2011 8:07 PM
  • 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

    Monday, November 19, 2012 11:03 AM
  • 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

    Tuesday, November 20, 2012 7:50 AM