none
Change font color of text in CellValue in Excel - OpenXml RRS feed

  • Question

  • How i can change text color of CellValue in excel, i am able to change forgeround color of Cell but it changes color of all text present inside the cell which i dont want , i want to highlight only particular Text in side the Cell i.e CellValue text.

    Example - if a cell contains "Microsoft is great" , i want to only highlight Microsoft not whole cell.

    I am using below code to highlight Cell Text as , how it can be done for CellValue ? -

    foreach (DocumentFormat.OpenXml.Spreadsheet.Cell currentCell in allCells)
    {    
    Fill fill = new Fill()
        {
             PatternFill = new PatternFill
             {
                 PatternType = PatternValues.Solid,
                 ForegroundColor = new ForegroundColor() { Rgb = "FFFF00" }
              }
        };
        styleSheet.Fills.AppendChild(fill);
    
       //Adding the  CellFormat which uses the Fill element 
        CellFormats cellFormats = styleSheet.CellFormats;
        CellFormat cf = new CellFormat();
        cf.FillId = styleSheet.Fills.Count;
        cellFormats.AppendChild(cf);
        currentCell.StyleIndex = styleSheet.CellFormats.Count;
    }

    I dont see any property of Style in CellValue

    CellValue currentCellValue = currentCell.GetFirstChild<CellValue>();
    if (currentCell.DataType == CellValues.SharedString) // cell has a cell value that is a string, thus, stored else where
        {
                 data = doc.WorkbookPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault().SharedStringTable.ElementAt(int.Parse(currentCellValue.Text)).InnerText;
        }
    Friday, November 9, 2018 5:03 PM

All replies

  • Hi Aryahi,

    Please refer to the following code:

     private void button1_Click(object sender, EventArgs e)
            {
                Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
                excelApp.Workbooks.Add();
                // single worksheet
                Microsoft.Office.Interop.Excel._Worksheet workSheet = excelApp.ActiveSheet;
                excelApp.Visible = true;
                for (int i = 0; i < 10; i++)
                {
                    Microsoft.Office.Interop.Excel.Range rng = (Microsoft.Office.Interop.Excel.Range)workSheet.Cells[1, (i + 1)];
                    rng.Value = "Green Red";
                    rng.Characters[1, 5].Font.Color = Color.Green;
                    rng.Characters[7, 9].Font.Color = Color.Red;
                }
            }

    For more information, please see the links as below:

    How can I change color of text in cell of Excel?

    Changing font color of selected text inside a cell

    How can I change color of text in a cell of MS Excel?

    Hopefully it helps you.

    Best Regards,

    Lina


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Monday, November 12, 2018 2:18 AM
  • Hi,

    I am using OpenXML as i mentioned above, the solution you provided does not work in OpenXML instead it is of Interop.

    Please provide me the solution in OpenXML.

    Thanks.

    Monday, November 12, 2018 3:16 PM
  • Hi Aryahi,

    Please try the following code:

    Run run1 = new Run();
    
                RunProperties runProperties1 = new RunProperties();
                Bold bold1 = new Bold();
                Underline underline1 = new Underline();
                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(underline1);
                runProperties1.Append(fontSize1);
                runProperties1.Append(color1);
                runProperties1.Append(runFont1);
                runProperties1.Append(fontFamily1);
                runProperties1.Append(fontScheme1);
                Text text1 = new Text();
                text1.Text = "Project Name:";

    For more information, please see the link as below:

    Two CellValues with Different Styles in a cell in OPEN XML SDK 2.0

    Hopefully it helps you.

    Best Regards,

    Lina


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Tuesday, November 13, 2018 5:56 AM
  • Hi Aryahi,

    Thanks for your asking. Please remember to mark the replies as answers if they helped and please help us close the thread.

     

    Thank you for understanding. If you have any question, or update, please feel free to let us know.

     

    I wish you a happy life!

     

    Best Regards,

     

    Lina


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Thursday, November 15, 2018 6:49 AM