Hi,
This is with reference to the post 272424 .
I can create a named range if i know exact range details in the worksheet as shown below.
//Creating a named range
Range range = worksheet.Cells.CreateRange(“B4”, “G14”);
//Setting the name of the named range
range.Name = “TestRange”;
My requirement is to apply autofilter for the used Range in the selected work sheet. Since Excel file has been generated by some other process, User Range details are not available.
This can be achieved through excel interop as shown below.
objXL.Sheets[ “Sheet1” ].Select();
objXL.Selection.AutoFilter();
How can i achieve the same in Aspose?
Please Advise.
Thanks,
Ujwal.
Hi,
Thanks for your posting and considering Aspose.Cells for .NET.
If your range has already been created, then you can access it using Aspose.Cells APIs.
Please use the Workbook.Worksheets.Names property to retrieve all the named ranges in your workbook having workbook or worksheet scope.
It has RefersTo property that will give you the area of cells that range is covering.
Once, you will access your named range, you can then apply autofilter using the following code.
C#
worksheet.AutoFilter.Range = range.RefersTo;
Thanks for your reply.
I don’t have any available named ranges.
How can i proceed in this scenario?
Thanks,
Ujwal.
Hi,
Thanks for your feedback.
In order to apply AutoFilter, you must know the range to apply it on. In case of Interop, I think, Ms-Excel file gets open for a human user and then he selects or specifies a range and then autofilter is applied on using your mentioned code.
But Aspose.Cells is not GUI component, it will not open the Ms-Excel file and let the human user specify the range. In order to get the input from human user, you need to find a way like prompting him for input range or provide him some web interface.
Anyway, could you please elaborate your requirements more with screenshots and actual or expected output xls/xlsx files?
You can create these manually using Ms-Paint and Ms-Excel and post here. It will help us dig into your requirements more and if there is some solution or workaround for you, we will let you know asap.
Hi,
Ujwal.challa:
I don't have any available named ranges.
How can i proceed in this scenario?
Thanks,
Ujwal.
I think you may refer to the following example code to set auto-filters for all the populated area/cells in the worksheet.
Sample code:
//I used a sample Excel file that contains some data list in the first worksheet, you may use your file accordingly
string filePath = @"e:\test2\Book1.xlsx";
Workbook workbook = new Workbook(filePath, new Aspose.Cells.LoadOptions(Aspose.Cells.LoadFormat.Xlsx));
Worksheet sourceSheet = workbook.Worksheets[0];
Aspose.Cells.Range sourceRange = sourceSheet.Cells.MaxDisplayRange;
// Create an AutoFilter object.
AutoFilter autofilter = sourceSheet.AutoFilter;
autofilter.SetRange(sourceRange.FirstRow,sourceRange.FirstColumn, sourceRange.ColumnCount - sourceRange.FirstColumn - 1);
autofilter.Refresh();
workbook.Save("e:\\test2\\output.xlsx");
Thank you.
I tried something similar. PFB the code.
string strStartCell = activeWorkSheet.Cells[activeWorkSheet.Cells.MinRow, activeWorkSheet.Cells.MinColumn].Name;
string strEndCell = activeWorkSheet.Cells[activeWorkSheet.Cells.MinRow, activeWorkSheet.Cells.MaxDataColumn].Name;
activeWorkSheet.AutoFilter.Range = (strStartCell + (":" + strEndCell));
Where activeWorkSheet is the current worksheet. These approaches will always apply filter on the first row of worksheet irrespective of Minimum Data Row.
PFB some code in VB6 by using Excel Interop (We are migrating from VB6 to DOT NET).
objXL.Cells(2, 1).Value = "Execution mode : "
objXL.Cells(2, 2).Value = “strModeExec”
objXL.Cells(3, 1).Value = "Period : "
objXL.Cells(3, 2).Value = “Format(datDebutPeriode,”
objXL.Cells(4, 1).Value = "Data Base : "
objXL.Cells(4, 2).Value = “strEvtDB”
objXL.Cells(5, 1).Value = "Results path : "
objXL.Cells(5, 2).Value = “strResultsSheetsPath”
objXL.Cells(6, 1).Value = "LogFile path : "
objXL.Cells(6, 2).Value = “strLogFile”
objXL.Cells(7, 1).Value = "Type Promote : "
objXL.Cells(7, 2).Value = “strLibPromote”
objXL.Selection.AutoFilter
Here the starting data row is 2 (In VB6 indexes will start from 1).
Autofilter will be applied on Second row in this case by Excel Interop.
PFA the screenshot of applied Auto filter by excel interop (AutoFilter.png) and by aspose (AutoFilter_Aspose.png).
So in order to achieve the same behavior i need MinDataColumn and MinDataRow, such that i can set the auto filter range.
Please Advise.
Many Thanks,
Ujwal.
Hi,
Thanks for your feedback, sample code and screenshot.
Unlike Ms-Excel, in Aspose.Cells, the index starts from 0, so MinDataColumn and MinDataRow will be 0 in this case.
You will have to subtract 1 from current row or column to access the correct row/column.
It means, 3rd row in Ms-Excel will be accessed using index position 2 and 5th column will be accessed using index position 4.
Also, you can use the following properties to access minimum column and minimum row in the current worksheet.
- Worksheet.Cells.MinColumn
- Worksheet.Cells.MinRow
PS: If MinColumn will return you 4 then it is the 5th column i.e Column E.
Let me know if it is helpful for you. If you have any other question, please feel free to post, we will help you asap.
There is some confusion. Issue is not with Index.
In both the cases data is being written from second row. That means minimum data row is Second Row. First row is empty.
With the example that you have provided, filter is crated on First Row where as Excel interop is creating on Second row. (Only difference in the screenshots)
Question:
Is it possible to get the minimum data row and minimum data column using Aspose?
Many Thanks,
Ujwal.
Hi,
Thanks for your input.
The following properties should be implemented to fulfill your needs.
Worksheet.Cells.MinDataColumn
Worksheet.Cells.MinDataRow
In the attached sample file, MinColum is 1 but MinDataCoulmn should be 4. Similarly there should be MinDataRow property.
We have logged a New Feature Request for it in our database. Once the issue is resolved or we have some other update for you, we will let you know asap.
This issue has been logged as CELLSNET-40877.
Screenshot:
Hi,
Please download and try this fix: Aspose.Cells for .NET v7.3.0.2
We have supported MinDataRow and MinDataColumn properties.
Hi shakeel,
With the version you have provided Aspose.Cells for .NET v7.3.0.2, we are able to achieve our required output.
As we works for a client we cannot directly use it, we need to get a offically release for the fix you have made based on our discussion.
So can you please let me know when are you planning to release this fix during your offical releases and at what date and version will this fix be available.
Many Thanks,
Ujwal.
Hi,
You can use this release for your production use. It is called minor release.
Both minor and major releases are equally same except major release is published with release notes and both are meant for production use.
Please see the sample release notes for Major release at the following link. I have also posted a screenshot for your reference.
Release Notes - Aspose.Cells for .NET
Screenshot:
Hi Ujwal,
Well, you may use this minor fix version (as we shared its link), it will work like an official release of the product and include all the fixes, enhancements and features.
However, we normally publish our official release of the product (Aspose.Cells for .NET) once in a month in the Download module for the users, you may expect it in the mid of this month too. Moreover, our next official release of the product would include all the fixes and enhancements of all the previous fixes/versions.
The issues you have found earlier (filed as CELLSNET-40877) have been fixed in this update.
This message was posted using Notification2Forum from Downloads module by aspose.notifier.
The issues you have found earlier (filed as CELLSNET-40877) have also been fixed in this latest release (Aspose.Cells for Java).
Download it if you need the functionality in Java version.