How to Find and Replace Text by Regular Expressions

[psanchezhogares]

Hello I need to find and replace text by Regular Expressions in a worksheet. Is there any way to do that just like Document.Replace function in Spire.doc?

[rachel.lei]

Hello,

Thanks for your inquiry.
Sorry our Spire.XLS doesn't have a method to find text by regular expressions, but you can achieve your requirment by getting all the cells and matching their text with regular expressions, and then replace them. Please refer to the following code. If there is any problem, please feel free to write back.

Code: Select all

 static void Main(string[] args)
        {
            Workbook wb = new Workbook();
            wb.LoadFromFile("test.xlsx");
            Worksheet sheet = wb.Worksheets[0];
            List<CellRange> listRanges = new List<CellRange>();
            foreach (var row in sheet.Rows)
            {
                foreach (var cell in row.Cells)
                {
                    listRanges.Add(cell);
                }
            }
           
            Regex regex = new Regex("R[\\w]+", RegexOptions.None);

            //match all cells with regular expressions and replace text
            Replace(regex, listRanges.ToArray());

            wb.SaveToFile("result.xlsx",ExcelVersion.Version2013);
        }
        static void Replace(Regex pattern, CellRange[] ranges)
        {
            List<CellRange> matchedRanges = new List<CellRange>();

            foreach (CellRange range in ranges)
            {
                if (range.Text != null)
                {
                    if (pattern.IsMatch(range.Text))
                    {
                        //replace text
                        range.Text = pattern.Replace(range.Text, "replaceString");
                    }
                }
            }
        }


Sincerely,
Rachel
E-iceblue support team

[rachel.lei]

Hello,

Greetings form E-iceblue!
Have you tested my code? Could you please give us some feedback at your convenience?

Sincerely,
Rachel
E-iceblue support team

[alvesmarc]

Hello,
You proposed code is working well on debug mode, step by step I can see the CellRange Text updated with my replacement value...
However when I try to save the file as proposed on your code and open the new created XLS file, the value are still the old ones and has not been replaced by the new values...
Very strange...
Can you help me please ?
I'm using the FreeSpire.Office V4.3.1 with .Net 5.0.
Thx

[Annika.Zhou]

Hello alvesmarc,

Thank you for your inquiry.
I tested the code you mentioned with FreeSpire.Office V4.3.1 and the latest version (Spire.Office Platinum Version:7.1), but none of them reproduced the issue you mentioned. For your situation,I suggest you first try again with the latest version(Spire.Office Platinum Version:7.1). If the issue still exists after trying, please provide the following information for our reference. Thanks in advance.
1) Your input Excel file (you could attach it here or send it to us via email (support@e-iceblue.com)).
2) Your test environment, such as OS info (E.g. Windows 7, 64-bit) and region setting (E.g. China, Chinese).
3) Your application type, such as Console app.

Sincerely,
Annika
E-iceblue support team