We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Replace whitespaces in Aspose.cells in cell in excel sheet

Hello, is there any ability to replace all \n\r in cells string values in aspose sheet object?
I see there is replace function in sheet but it apllies to strings only - no regexp or special characters there as far as I see

@komloshij,
Aspose.Cells provides facility to search the worksheet cells using Regex. Please give a try to the following example and modify according to your requirements as once the required characters are found, you may replace them with your required data. If your issue is not resolved, please share your template file with us for testing. Also try to perform the same operation using MS Excel and share the steps with us for our analysis.

//string testStr = "[hello], [list3], [another_example]. However";

//string regEx = "\\[[a-zA-Z0-9_]+]";

string testStr = @"[hello], [list3
], [another_example]. 
However";

string regEx = "[\r\n]+";

Regex reg = new Regex(regEx);
MatchCollection v = reg.Matches(testStr);

//regEx works fine and finds 3 matches
foreach (Match m in v)
{
    Console.WriteLine(m.ToString());
}

Workbook workbook = new Workbook(filePath + "sample.xlsx");

Worksheet worksheet = workbook.Worksheets[0];

FindOptions options = new FindOptions();
options.RegexKey = true;
options.LookInType = LookInType.Values;
options.LookAtType = LookAtType.Contains;
options.SetRange(new CellArea() { StartColumn = 0, EndColumn = 0, StartRow = 0, EndRow = 20 });
//options.IsRangeSet = true;

//Cells.Find method does not find any cell with regEx
Aspose.Cells.Cell found = null;

do
{
    found = worksheet.Cells.Find(regEx, found, options);
    if (found == null)
        break;
    Console.WriteLine($"Name = {found.Name}, {found.Value}");

} while (found != null);

workbook.Save(filePath + "output.xlsx");

Thank you! That was what I was searching for

@komloshij,

Good to know that the suggested code segment figures out your issue now. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.