1
Answer

Update Cell Value in Excel using ClosedXML

Karthi Keyan

Karthi Keyan
5y
9.3k
1
Hi Guys,
 
I am generating Excel file by putting some data's from XML files. In that excel, I want to make a Partial text in Cell into <italic> or <bold> (not full text).
 
I used the below code when i used Microsoft.Interop.Excel. it works fine for me. But now i need to do this using CloedXML. I really don't know how to do this. i searched many pages but i didn't get what i required. Can any one tell me the anwser...
  1. Label:  
  2. Excel.Range rn7 = osheet.Cells[i, 6] as Excel.Range;  
  3. string text = rn7.Text;  
  4. rn7.Select();  
  5. if(rn7.Characters.Count <= 256)  
  6. {  
  7. if (text.Contains("<i>"))  
  8. {  
  9. int startindex = rn7.Text.IndexOf("<i>");  
  10. int endindex = rn7.Text.IndexOf("</i>");  
  11. rn7.Characters[startindex+1, 3].Delete();  
  12. rn7.Characters[endindex-2, 4].Delete();  
  13. rn7.Characters[startindex, (endindex - startindex)-2].Font.Italic = 2;  
  14. }  
  15. if (text.Contains("<b>"))  
  16. {  
  17. int startindex = rn7.Text.IndexOf("<b>");  
  18. int endindex = rn7.Text.IndexOf("</b>");  
  19. rn7.Characters[startindex + 1, 3].Delete();  
  20. rn7.Characters[endindex - 2, 4].Delete();  
  21. rn7.Characters[startindex, (endindex - startindex)-2].Font.Bold = 2;  
  22. }  
  23. if (text.Contains("<fi>"))  
  24. {  
  25. int startindex = rn7.Text.IndexOf("<fi>");  
  26. int endindex = rn7.Text.IndexOf("</fi>");  
  27. rn7.Characters[startindex + 1, 4].Delete();  
  28. rn7.Characters[endindex - 3, 5].Delete();  
  29. rn7.Characters[startindex, (endindex - startindex) - 3].Font.Italic = 2;  
  30. }  
  31. if (text.Contains("<fr>"))  
  32. {  
  33. int startindex = rn7.Text.IndexOf("<fr>");  
  34. int endindex = rn7.Text.IndexOf("</fr>");  
  35. rn7.Characters[startindex + 1, 4].Delete();  
  36. rn7.Characters[endindex - 3, 5].Delete();  
  37. rn7.Characters[startindex, (endindex - startindex) - 2].Font.Name = "Times New Roman";  
  38. }  
  39. rn7.VerticalAlignment = Excel.XlVAlign.xlVAlignTop;  
  40. rn7.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;  
  41. text = rn7.Text;  
  42. if (Regex.IsMatch(text,@"\<(b|i|fi|fr)\>"))  
  43. {  
  44. goto Label;  
  45. }  
  46. }  
  47. else  
  48. {  
  49. text = Regex.Replace(text,@"(<(\/)?(fr|fi|b|i)>)","");  
  50. rn7.Value = text;  
  51. }  
Answers (1)
Next Recommended Forum