I’m trying to perform a search and replace in my program and I wish to replace a text with another along with its text formatting. I came across the methods getStyle and setStyle but they don’t work in my case. To give you a little background on what I’m trying to achieve, I have a worksheet with a column containing a set of questions, and some of these questions need to update with a newer version of it. Now, I have another workbook with worksheet that contains the questions that need to be replaced in one column and the updated questions in the corresponding cell in the adjacent column. The issue I’ve been facing is that say the replace text is “This is a sample question” and only a few words have any formatting to it such as underline, italic or bold, when its being relpaced in the destination worksheet, it does the replacing correctly but it doesn’t get the formatting right. Either it formats the entire text or it doesn’t do anything at all. Would anyone know how I can achieve this?
Here is the code I’m using:
// Create a workbook using output file
outputWB = new Workbook(outputFile);
outputWS = outputWB.Worksheets[0];
// Create a workbook using input file
inputWB = new Workbook(replaceAllFile);
inputWS = inputWB.Worksheets[0];
// Set Replace Options
ReplaceOptions replaceOptions = new ReplaceOptions();
replaceOptions.CaseSensitive = false;
replaceOptions.MatchEntireCellContents = true;
// Accessing all the cells of replaceAll File
Cells inputcells = inputWS.Cells;
// get column that contains the search text
int searchTxtCol = CellsHelper.ColumnNameToIndex("A");
// get the last row index in A column
int searchTxtLastRow = inputWS.Cells.GetLastDataRow(searchTxtCol);
// get column that contains the replace text
int replaceTxtCol = CellsHelper.ColumnNameToIndex("B");
// get the last row index in B column
int replaceTxtLastRow = inputWS.Cells.GetLastDataRow(replaceTxtCol);
//Accessing all the cells of output file
Cells outputCells = outputWS.Cells;
// get column that contains the destination text
int destTxtCol = CellsHelper.ColumnNameToIndex("D");
// get the last row index in D column of destination file
int destTxtLastRow = outputWS.Cells.GetLastDataRow(destTxtCol);
// Set Find Options
FindOptions findOptions = new FindOptions();
// Setting area
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.StartColumn = destTxtCol;
ca.EndRow = destTxtLastRow;
ca.EndColumn = destTxtCol;
findOptions.SetRange(ca);
findOptions.CaseSensitive = false;
Cell nextDestCell = null;
// defining a Style object
Aspose.Cells.Style inputStyle;
for (int i = 0; i <= searchTxtLastRow; i++)
{
// Get the search string
string searchStr = string.IsNullOrEmpty(inputcells[i, searchTxtCol].Value.ToString()) ? "" : inputcells[i, searchTxtCol].Value.ToString();
// Get the replace string
string replaceStr = string.IsNullOrEmpty(inputcells[i, replaceTxtCol].Value.ToString()) ? "" : inputcells[i, replaceTxtCol].Value.ToString();
inputStyle = inputcells[i, replaceTxtCol].GetStyle();
if (!string.IsNullOrWhiteSpace(searchStr))
{
outputWB.Replace(searchStr, replaceStr, replaceOptions);
// Apply input style to all replaced cells
do
{
nextDestCell = outputWS.Cells.Find(replaceStr, nextDestCell, findOptions);
if (nextDestCell == null)
{
break;
}
else
{
nextDestCell.SetStyle(inputStyle);
}
} while (true);
}
}
@JThomas98
Is the text of the cell rich formatted?
Can you post a sample file here? We will check it soon.
image.png (46.5 KB)
I cant seem to upload a .xlsm file. I hope this screenshot helps.
In the screen shot, Column A is the text to be replaced and column B is the replacing text.
@JThomas98,
Because the content you need to update is rich text, we are afraid Workbook.Replace() methods cannot fit your requirement. However, we think you may get the expected result by using Cells.Find(object, Cell, FindOptions) together with Cell.Copy(Cell). I modified your code a bit:
...
// Set Find Options
FindOptions findOptions = new FindOptions();
// Setting area
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.StartColumn = destTxtCol;
ca.EndRow = destTxtLastRow;
ca.EndColumn = destTxtCol;
//findOptions.SetRange(ca); //here I remove the area to replace all cells as demo
findOptions.CaseSensitive = false;
findOptions.LookAtType = LookAtType.EntireContent;
findOptions.LookInType = LookInType.Values;
Cell nextDestCell = null;
// defining a Style object
Aspose.Cells.Style inputStyle;
for (int i = 0; i <= searchTxtLastRow; i++)
{
// Get the search string
string searchStr = string.IsNullOrEmpty(inputcells[i, searchTxtCol].Value.ToString()) ? "" : inputcells[i, searchTxtCol].Value.ToString();
// Get the replace string
Cell inputCell = inputcells[i, replaceTxtCol];
string replaceStr = string.IsNullOrEmpty(inputCell.Value.ToString()) ? "" : inputCell.Value.ToString();
inputStyle = inputcells[i, replaceTxtCol].GetStyle();
if (!string.IsNullOrWhiteSpace(searchStr))
{
//outputWB.Replace(searchStr, replaceStr, replaceOptions);
// Apply input style to all replaced cells
do
{
nextDestCell = outputWS.Cells.Find(searchStr, nextDestCell, findOptions);
if (nextDestCell == null)
{
break;
}
else
{
nextDestCell.Copy(inputCell);
//nextDestCell.SetStyle(inputStyle); //also you can apply style here if it is needed
}
} while (true);
}
}
If it is not what you want to get, please zip your template file and upload it here so we can investigate it and provide solution for you.
This is exactly what i was looking for. Thank you so much! You all are amazing!
@JThomas98,
You are welcome. It is nice to know that the suggested code segment works for your needs.
Feel free to write us back if you have further queries or comments, we will be happy to assist you soon.
1 Like
Hi @amjad.sahi,
I’ve been trying something new with aspose and somewhat related to my earlier question. Say I have a cell that contains a formatted text “Hi my name is XYZ” and I want to replace just one part of the text (in this case XYZ) with another formatted text “ABC”(let this text also have a color). How could I do that with aspose?
@JThomas98,
You may easily do that via Cell.HtmlString attribute to replace a part of formatted text with another. See the following sample code for your reference, I have used a simple Excel file (attached) to accomplish the task.
e.g.
Sample code:
// Create a workbook using input file
Workbook inputWB = new Workbook("g:\\test2\\Bk_test1_copy.xlsx");
Worksheet inputWS = inputWB.Worksheets[0];
// Accessing all the cells of replaceAll File
Cells inputcells = inputWS.Cells;
//input your last row and column range indices
int destTxtCol = 10;
int destTxtLastRow = 20;
// Set Find Options
FindOptions findOptions = new FindOptions();
// Setting area
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.StartColumn = 0;
ca.EndRow = destTxtLastRow;
ca.EndColumn = destTxtCol;
findOptions.CaseSensitive = false;
findOptions.LookAtType = LookAtType.Contains;
findOptions.LookInType = LookInType.Values;
Cell nextDestCell = null;
string searchStr = "XYZ";
string replaceStr = "ABC";
Cell fCell = null;
if (!string.IsNullOrWhiteSpace(searchStr))
{
do
{
fCell = inputcells.Find(searchStr, nextDestCell, findOptions);
if (fCell == null)
{
break;
}
else
{
string val = fCell.HtmlString.Replace(searchStr, replaceStr);
fCell.HtmlString = val;
}
} while (true);
}
inputWB.Save("g:\\test2\\out1.xlsx");
Also, see the document on find or search data for your reference.
Hope, this helps a bit.
@amjad.sahi
Thank you for the quick reply and I’m sorry for responding this late.
I’ve attached some files here so that you get a better understanding of what I’m trying to achieve.
In the zip folder, the file called Replace_All_File is the input file that contains the text that needs to be replaced in column A and and the corresponding replacing text in column B. The file TB_Questions_AAH1 is the output file where we need to search and replace. I want the replace by keep the formatting of the replacing text(such as underline, bold, italic, color, etc.) after replacing it in the output file.
Files.zip (44.3 KB)
@JThomas98
We do not support replacing some characters of rich formatted text in the cell now.
We have logged it with id CELLSNET-53871. We will look it soon.
Now you have to try Cell.HtmlString as Amjad said in the previous post.
@simon.zhao
Okay, thanks for letting me know. Appreciate it.
@JThomas98
There are three “needed care right away”. One does not have underline, we only can replace it though the formatting is not same as “Replace_All_File.xlsm” because it’s hard to compare string value with formatting.
@simon.zhao
I see, I wanted to replace the ones that only match the text and formatting in the replace all file. I tried the copy method available in Aspose cells and it works perfectly when I’m replacing the entire contents of a cell. But I wanted to create a method that also replace a part of a rich formatted text with another rich formatted text. For example, a cell containing text “Did you have an illness, injury, or condition that needed care right away” replace “needed care right away” with “needed care right away” there by the cell’s value turning out to be “Did you have an illness, injury, or condition that needed care right away”. Or replace “needed care right away” with “required care right away”.
@JThomas98,
We will evaluate such kind of requirement and get back to you soon.
@JThomas98
CELLSNET53874.zip (16.1 KB)
Please check the attached file which is generated with internal hotfix 23.7.8:
Workbook workbook = new Workbook(Constants.sourcePath + "CELLSNET53874.xlsm");
Workbook replaceW = new Workbook(Constants.sourcePath + "CELLSNET53874_r.xlsm");
foreach (Row row in replaceW.Worksheets[0].Cells.Rows)
{
Cell c1 = row[0];
if (string.IsNullOrEmpty(c1.StringValue))
{
continue;
}
Cell c2 = row[1];
ReplaceOptions options1 = new ReplaceOptions();
options1.CaseSensitive = false;
options1.MatchEntireCellContents = false;
options1.FontSettings = c2.GetCharacters();
foreach (Cell c in workbook.Worksheets[0].Cells)
{
c.Replace(c1.StringValue, c2.StringValue, options1);
}
}
But we also replaced D8 because we do not support matching formatting and text together, we only compare whether the value contains searched text.
Does it fit your need?
@johnson.shi
Thank you very much!
This works for now. I understand its not possible to match both formatting and text at the same time at this point. But this definitely solve half my problem. Thank you @simon.zhao.
@JThomas98,
Thanks for your understanding. And, good to know it works to some extent for your needs.
The issues you have found earlier (filed as CELLSNET-53871) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi