Single cell with multiple font colours

Hi,

I need to format text within a single cell with different colours. Is this doable in Aspose? For example, I need a SINGLE cell to display the following:

HEADING (TESTING123)

Thanks,

P

Hi P,


Thank you for contacting Aspose support. Please check the following article for your requirement.

Aha thank you!

Hi,

Thanks for considering Aspose.Cells.

Here is the short example that illustrates the usage of this feature. Please check the source excel file and the output excel file generated by it for your reference.

C#
Workbook wb = new Workbook("s1.xlsx");

Worksheet ws = wb.Worksheets[0];

Cell a1 = ws.Cells["A1"];
Cell e6 = ws.Cells["E6"];

FontSetting[] fnts = a1.GetCharacters();

e6.SetCharacters(fnts);

wb.Save("output.xlsx");

Hi,

How can I combine text formatting from two cells into one cell?

Example :- One excel has two cells with formatted text (bold and colored in one cell and strike through in another cell). Let’s say the texts are ABC - bold and colors and in the other cell - XYZ - strikethrough. Let the two cells be A1 and B1

I want to create an output excel with one single cell (let’s say C1) where the content would be ABC (bold and colored) and XYZ (strike through ) in the same cell with a line break.

Is that possible?

@Blackpanther8584,

See the following sample code to accomplish the task for your reference:
e.g.
Sample code:

// Instantiating a Workbook object
            Workbook workbook = new Workbook();

            // Obtaining the reference of the first(default) worksheet by passing its sheet index
            Worksheet worksheet = workbook.Worksheets[0];

            // Accessing the "A1" cell from the worksheet
            Cell cell = worksheet.Cells["A1"];

            // Adding some value to the "A1" cell
            cell.PutValue("ABC\nXYZ");

            // Setting the font of selected characters to bold and colored.
            cell.Characters(0, 3).Font.IsBold = true;
            cell.Characters(0, 3).Font.Color = Color.Green;

            // Setting strikeout of the specific chars.
            cell.Characters(3, 3).Font.IsStrikeout = true;


            //Define style to Wrap text in the cell.
            Style style = workbook.CreateStyle();
            style.IsTextWrapped = true;

            //apply the style.
            cell.SetStyle(style);


            // Saving the Excel file
            workbook.Save("e:\\test2\\book1.out1.xlsx");

Hope, this helps a bit.

Thanks. However, my requirement is different. I have an excel which already has those specially formatted text in different cells.

I need to read those cells (along with the text format) copy them into a new excel in a single cell and keep the different formatting preserved

@Blackpanther8584,

Please note, MS Excel does not support to directly merge two styles from two different cells into one cell, so, you got to adopt a kind of manual approach for the purpose. See the sample code with template file (attached) for your reference. It will accomplish the task accordingly. You need to copy formatting attributes/settings of source cells to target cell for your needs.
e.g
Sample code:

Workbook workbook = new Workbook("e:\\test2\\Bk_multiple_format1.xlsx");

            // Obtaining the reference of the first worksheet by passing its sheet index
            Worksheet worksheet = workbook.Worksheets[0];

            //Get a cell with its value chars length 
            Cell cell1 = worksheet.Cells["D5"];//it has ABC (bold and colored)
            string val1 = cell1.StringValue;
            int v1length = val1.Length; 
            //Get the style of the cell1.
            Style cell1Style = cell1.GetStyle();

            //Get second cell with its value chars length 
            Cell cell2 = worksheet.Cells["D6"];//it has XYZ (strikethrough)
            string val2 = cell2.StringValue;
            int v2length = val2.Length;
            //Get the style of the cell2.
            Style cell2Style = cell2.GetStyle();


            //target cell
            Cell mcell = worksheet.Cells["G7"];
            //insert the value from both cells.
            mcell.PutValue(val1 + "\n" + val2);
                

            // Copy style Settings of selected characters from source cell1 to target cell
            mcell.Characters(0, v1length).Font.IsBold = cell1Style.Font.IsBold;
            mcell.Characters(0, v1length).Font.Color = cell1Style.Font.Color;
            // Copy style Settings of selected characters from source cell2 to target cell 
            mcell.Characters(v1length, v2length).Font.IsStrikeout = cell2Style.Font.IsStrikeout;
            //...
            //You got to define other formatting attributes one by one if there are some other possible formattings related to fonts here.
            //.....

            //Define style to Wrap text in the target cell.
            Style style = workbook.CreateStyle();
            style.IsTextWrapped = true;

            //apply the style.
            mcell.SetStyle(style);


            // Saving the Excel file
            workbook.Save("e:\\test2\\book1.out1.xlsx");

files1.zip (6.0 KB)

Hope, this helps a bit.

Thanks. This is helpful. Couple of points.

The attached file doesn’t work - says it’s private.
Also, the code assumes I know what style to apply and for which characters. But in my case, that’s dynamic as well. Example - the cell D5 can be strikethrough and D6 can simply be bold. How do I read that dynamically as well and apply the style?

image.png (5.8 KB)

Hi,

Attached is an example of what I am trying to achieve. Is this possible?

Note, the content and style of the text in “content -1” & “content-2” are dynamic and can be any style

image.png (13.8 KB)

@Blackpanther8584,
We have understood your requirement and will share our feedback soon.

@Blackpanther8584,

Please download the file (which I attached previously) from the link here.

I think you have not read my reply in previous post. As I told you that in MS Excel you cannot directly merge two styles from two different cells into one cell, so, you have to adopt kind of manual approach. By the way, if you know some options to accomplish the task in MS Excel manually, let us know with details and screenshots, we can check it. In short, you need to specify/set every possible formatting attributes/settings (regarding Font) of source cells to target cell in code manually for your needs. I provided sample code just for demonstration so you may take reference/hints from it and write your own code by yourselves accordingly.

Thanks. I will check the details. Is there a way to iterate through each character in a cell, read the style of each character individually and apply them to the destination characters one by one? That might be one way to accomplish this - not very performance friendly, but can be a way

@Blackpanther8584,

Yes, this can be done, you may iterate each character (character by character) in the cell value and check each character’s font settings accordingly. Here you have to devise your own logic and code to check each and every font settings from the cell value. See the sample code segment for your reference:
e.g.
Sample code:

.........
int length = v1length;
for (int index = 0; index < length; index++)
            {
               bool isBold = cell.Characters(index, 1).Font.IsBold;
               //.............. 
               //your code goes here.
               //...........
            } 
........

We will also check if we could prepare a sample for you in coming days.