Retrieve Excel file cell value after AutoFilter/Calculate operation using Aspose.Cells for .NET C# API

Aspose.Cells Product Family
1

Hi Aspose Team,
I’m trying to get a value from a cell AFTER AutoFilter has been applied to the range. The formula in the cell Subtotals values depending on which column has been filtered. The AutoFilter only seems to work after the workbook is opened rather than returning a filtered value back to my web page without the need to open the workbook (which is what I want). Does the workbook have to open in order to trigger the AutoFilter? I’ve attached a sample of the C# code hoping that you may be able to help.
Many Thanks,
Lee

var workbook = new Aspose.Cells.Workbook(MapPath(“PHG Maintenance Cost Estimator Rev0.xlsm”)); // set the workbook to be used
var worksheet = workbook.Worksheets[“PHG250 Maint. Task”]; // set the worksheet
var cells = worksheet.Cells; // set cells as variable

worksheet.AutoFilter.Range = “B4:AU1202”; // Filter range
worksheet.AutoFilter.AddFilter(1, “0 BASE”);// Value in column to be filtered
worksheet.AutoFilter.AddFilter(5, “I/A COMPRESSOR”);// Value in another column to be filtered
worksheet.AutoFilter.Refresh();

// cells[“C2”].Formula = “=SUBTOTAL(3,C5:C1202)”; // THIS FORMULA IS IN CELL C2 in the WORKBOOK

workbook.CalculateFormula(); // CALCULATE ALL FORMULAS IN WORKBOOK

var setCountRows = cells[“C2”]; //set the cell in the workbook to be used
var getCountRows = setCountRows.Value; //get the value from the cell
txt_Count.Text = getCountRows.ToString(); // put get value into the textbox

// workbook.Save(HttpContext.Current.Response, “PHG Maintenance Cost Estimator Rev0.xlsm”, ContentDisposition.Attachment, new OoxmlSaveOptions(SaveFormat.Xlsm));

2

Hi,

Thanks for your posting and using Aspose.Cells.

Please provide us your source xlsm (i.e PHG Maintenance Cost Estimator Rev0.xlsm) file which you are using in the code so that we could some idea from the nature of your file.

Please also provide us your sample code again because the code you provided is jumbled up. You can copy code in some text file and attach it with your post.

Please also download and try the latest version: Aspose.Cells for .NET (Latest Version) and see if it makes any difference in resolving your issue. Let us know your feedback.

3

Sorry about the jumbled up code. Is there a way to format messages on the forum? In the meantime I have attached a modified version of the problem C# in a text file and the workbook being used.

Thanks,

Lee

4

Hi,


Thanks for the template file and sample code.

After an initial test, I observed the issue as you mentioned by using the following sample code and your provided file. I found I could not calculate the formula to get the updated value (dynamically) based on the filtered values in the worksheet. When I opened the output file into Ms Excel, it does filter the values fine for Auto-filters applied in the worksheet.
e.g
Sample code:

var workbook = new Aspose.Cells.Workbook(“e:\test2\TestFiltering.xlsm”); // set the workbook to be used

var worksheet = workbook.Worksheets[“FilterSheet”]; // set the worksheet

var cells = worksheet.Cells; // set cells as variable


worksheet.AutoFilter.Filter(0, “0 BASE”);// Value in column to be filtered
worksheet.AutoFilter.Filter(1, “I/A COMPRESSOR”);// Value in another column to be filtered
worksheet.AutoFilter.Refresh();

workbook.CalculateFormula(); // CALCULATE ALL FORMULAS IN WORKBOOK

var setCountRows = cells[“C2”]; //set the cell in the workbook to be used
var getCountRows = setCountRows.Value; //get the value from the cell
Console.WriteLine(getCountRows.ToString()); // 1196 - Not Ok
workbook.Save(“e:\test2\outTestFiltering1.xlsm”); //The output file is fine

I have logged a ticket with an id “CELLSNET-44039” for your issue. We will look into it soon.

Once we have any update on it, we will let you know here.

Thank you.

5

Hi Lee,


This is to update you that we have fixed the problem logged earlier as CELLSNET-44039. We will shortly provide the fix here after ensuring the quality and incorporating other enhancements.
6

The issues you have found earlier (filed as CELLSNET-44039) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.