Find cell


#1

Hi.

I have a database based application which exports some data in excel format. For this, when I first generate the excel file I create two worksheets one for layout and one for data. The layout worksheet’s cells are referencing the data worksheet’s cells. The layout sheet is meant for the client to arrange the way he wishes the cells (set its own colors, borders, even move the cells but not altering its content). After he has the template, I only fill the data worksheet and he can see the new data in the layout worksheet through the cells references. The data can be different measurement values, which I evaluate being valid or not. For non I valid values I would like to set the background color of the cell to red, for example. The problem is how do I find in the layout worksheet’s cell that references the “invalid” data worksheet’s cell, to change its background color?
Is there an API function to quick search a cell by its content, or do I have to scan all the cells in this layout worksheet?

Thanks
Calin


#2

Dear Calin,

Thanks for your consideration.

Currently Aspose.Excel does not supply an API function to search a cell by its content and I suggest not to scan all the cells. These two operation are time-consuming and the second approach will make the file size much larger.

Could you set conditional formatting in your designer file? I think that can also meet your need.


#3

Dear Laurence,

Thank you for your prompt answer. I still have two questions:

1. Will it in the future supply such an API function?
2. With the current version (1.8.4.0), if I have a cell referencing another cell, how ca I read the referencing formula from the first cell?

Thank you
Calin


#4

Dear Calin,

Sure. I can add a FindCell function to enable you search by content. I plan to supply it before the mid of March. Will that meet your need?

About your second question, did you mean:
1. In your designer file, you set a formula to A1, such as “=D1”. Then cell A1 refers to D1.
2. You import the file
3. You want to read the formula from cell A1

If so, I have to say Aspose.Excel now can only convert formula into spreadsheet. For example,

Cell cell = cells[“A1”];
cell.Formula = “=D1”;

Then you can get the formula and it can be saved into result excel file.

About formulas in designer file, Aspose.Excel can import them and export to result file. But currently Aspose.Excel doesn’t support to convert them into a string.

Now I suggest you can use Cell.Formula to set and get formula at run time.

If you really need the feature that can convert formula in design file to string, please let me know. I will place it in my future work plan.


#5

Thank you for your answer Laurence.

To answer to your first question I say: YES it would help me a lot a FindCell like API function to find a cell. At the moment I have to search for a cell in a worksheet by its formula (it references a cell in another worksheet). And the middle of March is ok for me to have the function.


I still have some questions:


1. Right now I making a “hand made” search, by searching a range of 100x200 cells (the speed is ok for my prototype I’m working on). For each row I try to find whether is the cell that contains the formula I set before (e.g. =OtherSheet!A5). The problem is that the Formula property of the cell is always null. I use the Visual Studio’s Quick Watch function to see the Cell object at run time, and the Formula member is set to null, but the StringValue IS the content of the referenced cell. I also see a ?IsStringFormula? member, that is always set on true on cells that I set to reference the other cells, but I can’t use it as public member. What does this property? How can I use it?


2. I use the following sample code, but I can’t see any colored cell. What am I doing wrong?

Dim excel As New Aspose.Excel.Excel

Dim style As Aspose.Excel.Style
Dim styleIndex = excel.Styles.Add()
style = excel.Styles(styleIndex)

style.BackgroundColor = System.Drawing.Color.AliceBlue

excel.Worksheets.Add()
excel.Worksheets(0).Cells(“A2”).Style = style
excel.Worksheets(0).Cells(“A2”).PutValue(“Test”)
excel.Save(“x.xls”, Aspose.Excel.SaveType.OpenInBrowser, Aspose.Excel.FileFormatType.Default, Response)

I hope you understand my english.

Thank you
Calin


#6

Dear Calin,

About your two questions:

1. MS Excel converts formula to it’s internal records. Aspose.Excel can import and export them. But it can’t convert it back to formula string now for it has a lot of work to do.

2. Because AliceBlue is not a standard color in Excel palette, you should use Excel.ChangePalette to add it to palette. When the cell’s fill pattern is not set or set to Solid, the background color will not take effect. Please set the foreground color to meet your need.

Please change your code as following and have a try.
Dim excel As New Aspose.Excel.Excel

Dim style As Aspose.Excel.Style
Dim styleIndex = excel.Styles.Add()

excel.ChangePalette(Color.AliceBlue, 55)

style = excel.Styles(styleIndex)

style.ForegroundColor = System.Drawing.Color.AliceBlue

excel.Worksheets.Add()
excel.Worksheets(0).Cells(“A2”).Style = style
excel.Worksheets(0).Cells(“A2”).PutValue(“Test”)
excel.Save(“x.xls”, Aspose.Excel.SaveType.OpenInBrowser, Aspose.Excel.FileFormatType.Default, Response)


#7

Hello Laurence.

First I want to apologize for answering so late.

It helped the sample code you sent me before (for changing the colors in the cells) and now it works for me too.

For the search, I need an API function to search a cell in the worksheet. Now I set a formula (e.g. =OtherWorksheet!A5) in a cell, and when I open again this designer file I want to find the cell that contains the formula “=OtherWorksheet!A5”.

Do you think you can supply a FindCell function that could provide me this functionality by the mid of March? This time-frame is ok for me, even later, but please let me know when you plan its release.

Thank you for your support

Calin


#8

Hi Calin,

I plan to supply a Cells.Find function to enable you search a cell in the worksheet. But
by the mid of March, it can only search data in a cell, not a formula. Parsing cell formula in designer file to formula string is much more complicated. So my plan date to provide this featrue is about the mid of April.

Thanks for your understanding.


#9

Hello Laurence

I find the mid of April OK for us to have the mentioned functionality.

Thank you for the support
Calin


#10

Do you have an update on the release date for this feature? I couldn’t find it in version 1.9.4.0.


#11

Sorry, this feature is delayed to the end of May.


#12

Hello

Is this feature available? I just downloaded version 2.8.0.0.

I only found FindFormula (not Find as you mentioned above) method, but id doesn't work for me. Maybe I don't know how to use it.

I tried to use it like this:

Worksheet tempWS - the worksheet I want to search in
referencingCell = tempWS.Cells.FindFormula("=D1", null);
if (referencingCell != null)
...

but I always get null [^o)]

What am I doing wrong?

Thanks
Calin


#13

Dear Calin,

We already provide several Find methods. Please check http://www.aspose.com/Products/Aspose.Excel/Api/Aspose.Excel.CellsMembers.html.

I will check your mention issue right now.