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,
Aha thank you!
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?
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
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)
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
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.