WorkBook.GetText()

Aspose.Cells Product Family
1

Hi

I’m using Aspose for replacing in Word documents and Excel workbooks.

The Word part is working just great, but I’m using a trick that I cannot find in cells.

To make a quick search of the presence of my placeholders, in Words I call:

string searchStr = aDoc.GetText();

This makes it easy to search for the presence of a placeholder in the document before calling Replace().

if (searchStr.Contains("##placeholder##"))
aDoc.Replace("##placeholder##", “ToReplaceWith”)

How do I do the same with Cells and a workbook or worksheet ?

Thanks in advance :slight_smile:

2

@farouche,

Thanks for your query.

Well, if you need to simply find/replace options to replace some value with other value in a worksheet or workbook, we provide Worksheet.Replace() and Workbook.Replace() methods to replace the values in a worksheet or even in the whole workbook, see the sample code below:
e.g
Sample code:

ReplaceOptions cellReplaceOption = new ReplaceOptions();
            cellReplaceOption.CaseSensitive = false;
            cellReplaceOption.MatchEntireCellContents = false;  

            LoadOptions lOption = new LoadOptions(LoadFormat.Excel97To2003);
            Workbook work = new Workbook("e:\\test2\\anyfile.xls", lOption);

            work.Replace("cpf", "anyword",cellReplaceOption);

Also, see the document on how to search or find data in the worksheet for your reference:

3

Hi

Thanks for the reply

I know about the Replace functionality as you describe, and was intending to use it as I do in Words.

I just thought it might be more efficient to check for the existence of placeholders in the workbook using a String.Contains() before starting the replace procedure, but perhaps I’m mistaken :slight_smile:

Without it, I will potentially call the Workbook.Replace 250 times for placeholders that does not even exist in the sheets. I do not know the internal coding of the Replace method, perhaps it will be more efficient to skip the Contains() check.

I will make a test .

4

Hi Again .

Just made a little test…

searchStr = aDoc.GetText();
containsMergeCodes = searchStr.Contains("##");

Completion time of 0,00207 sec including the GetText call that I in this way only need to to once.

Then using a Replace with a CallBack that stops at first match

Completion time of 0,007885 sec

Not saying that it means the world of difference, but in is almost times 4 faster using a string Contains :slight_smile:

And I will only have to call GetText once. Using the Replace Callback I will have to do it for every test…

The reason why this is important is that I call the Database to get merge data for each merge group, and I do not want to do that if the document doesn’t contain any placeholders from that group :slight_smile:

5

@farouche,

Well, it is completely different for processing string values in MS Word and MS Excel. For your information, in MS Excel, the string values are stored separately for every cell. Also, to get the results for GetText(), we have to concatenate all values of cells to one single string object but that operation itself is inefficient. Also, we are still not very clear for your scenario. To replace string values, we still need to and have to process every single cell, and check the string value itself again to know whether one cell’s value contains given placeholder or not.

6

The scenario is, that I’m replacing a lot of Placeholders that might be in a document/workbook.

Lets say: ##customer.email

To be able to do that merge I have to first get the Customer Record from the database, and I do not want to do that if the document does not contain any ##customer placeholders.

Therefore I first need to search for “##customer.” before calling the 23 different customer related replace actions.

This works fint using the GetText in Words, and I understand why this is not an option i Cells.

So now I’m looking at the WorkSheet.Cells.Find() operation…

Is there any way of getting a Cells collection representing the entire WorkBook instead of a single WorkShee, or will I have to traverse the sheets and call Cells.Find() for each ?

7

@farouche,

As we told you that both MS Word and MS Excel have diverse architectures targeting different file formats. Also, in MS Excel string values are stored separately for each and every cell in the worksheet. So, you might not accomplish the tasks of Word in MS Excel and vice versa.

You may try Worksheet.Cells.Find() method to search your desired strings in the worksheet. Also, I am afraid, you have to traverse each sheet to evaluate Cells.Find() method for the task as Cells collection comes under Worksheet (a worksheet consist of cells) instead of Workbook.

8

Hi again

Thanks for the explanation.

I believe I have what I need to get on with it.

Thanks again :slight_smile: