Detect cell in excel sheet if it has a vlook formula

Aspose.Cells Product Family
1

Dear,


I am using 7.0.0.0 version of aspose.cells. I am new to aspose so it might be a basic question. Can you please tell me is there any way we can detect that in an excel file, do we have a vlookup formula on any cell? I have uploaded an excel file and in the basic salary column, we have a vlookup formula.

Kind Regards
Naveed Anjum
2

Hi Naveed,


Thank you for contacting Aspose support.

Please try the following piece of code that tries to search the formula VLOOKUP in the first worksheet of your provided spreadsheet. The results are B2, B3, B4 & B5.

C#

var book = new Workbook(“C:/temp/SampleFile.xls”);
var cells = book.Worksheets[0].Cells;
FindOptions opts = new FindOptions();
opts.LookInType = LookInType.OnlyFormulas;
opts.LookAtType = LookAtType.Contains;
opts.RegexKey = true;
Cell cell = null;
do
{
cell = cells.Find("=VLOOKUP", cell, opts);
if (cell != null)
{
Console.WriteLine(cell.Name);
}
} while (cell != null);
3

Hi Baber,


The solution that you provided is working fine. Actually we are creating an excel file using aspose from another excel file that the client has uploaded. When we open the client uploaded file, it shows us in protected view and it also has v lookup formulas. When we edit the file with protected view, some of the values in the v lookup columns are removed and shows hyphen. This might be due to the reason that it is looking for another file for v lookup formulas. Then we are using the below line of code to create the file.

Workbook workBook = new Workbook(“C:/123.xls”);

The problem is that the values in some of the v lookup formula column are gone and showing hyphen when we save the workbook. With above code, the cell that has hyphen(having some v lookup formula) is showing the calculated value. Can you please suggest why it is not showing the values when we create the workbook from client uploaded file but shows when we use the above code to read the cell?

Kind Regards
Naveed Anjum




4

Hi Naveed,


I am afraid, I am not sure if the presented behavior is caused due to some problem in the API because you are using an old revision (7.0.0.0) of Aspose.Cells for .NET. Please give a try to the latest version of the API, that is; Aspose.Cells for .NET (Latest Version) at the moment to see if you get similar results. In case the problem persists with latest release as well then we require a sample application (covering your complete scenario) along with the problematic sample spreadsheet to properly investigate the matter on our side.

5

Hi Baber,


Thanks for quick response. I tried to create the file using 8.6.1.6 version of aspose but unfortunately the issue still persists. I am going to attach the file. Please suggest something to fix the issue.

Kind Regards
Naveed Anjum
6

Hi Naveed,


Thank you for the sample spreadsheet.

I have checked it on my side while using the latest version of Aspose.Cells for .NET 8.6.1.6 to search for the VLOOKUP formula, and I am getting correct results, that is; cells G6~G15. What other steps are you performing to make the suggested code return incorrect values? Are we still discussing the same issue (search for particular formula in worksheet)?
7

Hi Baber,


you are right the formulas are showing correctly. But as i said previously, when we are saving another file that is created from above spreadsheet, it is loosing values for formula columns. Can you please check this issue that why it is loosing the values. Below is the sample code.

var book = new Workbook(“D:/MonthlySalaryReport2.xlsx”);
var cells = book.Worksheets[0].Cells;
FindOptions opts = new FindOptions();
opts.LookInType = LookInType.OnlyFormulas;
opts.LookAtType = LookAtType.Contains;
opts.RegexKey = true;
Cell cell = null;
do
{
cell = cells.Find("=VLOOKUP", cell, opts);
if (cell != null)
{
Console.WriteLine(cell.Name);
}
} while (cell != null);

book.Save(“D:/457.xls”);

Console.ReadKey();

Please check the above code with the excel file that is attached with previous post.

Kind Regards
Naveed Anjum
8

Hi Naveed,


Thank you for clarification.

Please note, the provided spreadsheet contains links to external source for the formulas in cells G6~G15. When you re-save the spreadsheet with Aspose.Cells APIs or Excel application and try to re-open it in Excel application, it will ask you to update the links (as Excel considers them to be broken because it cannot find the specified cell references). This is the default behavior of Excel application and has nothing to do with Aspose.Cells APIs. You can confirm that same warning/prompt is activated when you open the source spreadsheet in Excel (and click Enable Contents).

C#

var book = new Workbook(“C:/temp/MonthlySalaryReport.xls”);
book.Settings.CalcMode = CalcModeType.Manual;
var cells = book.Worksheets[0].Cells;
FindOptions opts = new FindOptions();
opts.LookInType = LookInType.OnlyFormulas;
opts.LookAtType = LookAtType.Contains;
opts.RegexKey = true;
Cell cell = null;
do
{
cell = cells.Find("=VLOOKUP", cell, opts);
if (cell != null)
{
Console.WriteLine(cell.Name);
}
} while (cell != null);
book.Save(“C:/temp/output.xls”);
9

Dear Baber,


Yes you are right that the file contains external source for the formulas. We have different customers who upload the files that contains these kind of external resources although its very rare. Can you please tell me what could be the workaround for processing this kind of spreadsheets and save them in such a way they they don’t loose values. Actually we are processing the files uploaded from the customers, performing different calculations, validations, sorting, rearranging the columns etc. Kindly suggest.

Kind Regards
Naveed Anjum
10

Hi Naveed,


You can only avoid the problem by not updating the links. Please check the attached XLS file that I have generated using the code snippet from my previous post. Please also check the attached snapshots that shows how I managed to keep the calculated values intact without updating the links.
11

Hi Baber,


Thanks for your kind help. It works for us and also we are going to update the aspose.cells version. I am facing another issue with national id column. I have attached the worksheet. We are using custom format with national id. When we are saving the file after processing, we want to display the nationalId without custom format. You can see how it looks like when we double click on nationalId.

Kind Regards
Naveed Anjum
12

Hi Naveed,

It is good to know that you are able to bypass the problem discussed earlier. Regarding your recent concerns, if you wish to change the format of any cell, row or column, you should use the Style.Number or Style.Custom properties for it. Please check the following piece of code that changes the format of the column C to text using Style.Custom property. Please also go through the articles on Setting Display Formats for Numbers & Applying Style on a Row or Column.

C#

var book = new Workbook(“C:/temp/Excel1.xlsx”);
var sheet = book.Worksheets[0];
var columns = sheet.Cells.Columns;
var columnc = columns[CellsHelper.ColumnNameToIndex(“C”)];
var style = book.CreateStyle();
style.Custom = “@”;
columnc.ApplyStyle(style, new StyleFlag() { NumberFormat = true });
book.Save(“C:/temp/output.xlsx”);