Free Support Forum - aspose.com

Excel's "Create list" feature?

Hi,

Using Aspose.Cells v4.3.0.2, I’m trying to find out how to mimic the “Create list (CTRL-L)” functionality in MS Excel 2003. The one which creates AutoFilters and (optional) Totals Rows for the range of data. The total row for each column can be Sum, Count, Average,

Can Aspose.Cells do that?

Basically, I import a DataTable and wish to use that range as a Excel list to let the end-user filter and see the sum of the filtered data.

I’ve tried using AutoFilter as such :
xlsWorkbook.Worksheets[0].Cells.ImportDataTable(dt, true, 0, 0);
xlsWorkbook.Worksheets[0].AutoFilter.Range = “A1:”+ Aspose.Cells.CellsHelper.CellIndexToName(xlsWorkbook.Worksheets[0].Cells.MaxDataRow, xlsWorkbook.Worksheets[0].Cells.MaxDataColumn);

but that’s not quite what I want.

Any way I can achieve the list feature?

Thanks,

Dominic.

Aspose.Cells doesn’t support this feature. However, AutoFilter settings can mimic this setting. Could you please post your expected Excel file and the file created with your code? Thank you.

Here’s a zip file containing the Aspose result, the Expected result and an image of what the list feature allows in the total row.

What I’m especially looking for in the list feature is that when your filter your data, the total row remains visible and is updated to match the new selection. When using Aspose, the total rows are “part” of the data to be filtered and are hidden if the filters are not set to “All”.

I created the total rows with the following code :

string rangeBeg;
string rangeEnd;
string totalCell;
int lastDataRow = xlsWorkbook.Worksheets[0].Cells.MaxDataRow;

totalCell = Aspose.Cells.CellsHelper.CellIndexToName(lastDataRow+1, idxCatalogName);
rangeBeg = Aspose.Cells.CellsHelper.CellIndexToName(2, idxCatalogName);
rangeEnd = Aspose.Cells.CellsHelper.CellIndexToName(lastDataRow, idxCatalogName);
xlsWorkbook.Worksheets[0].Cells[totalCell].Formula = “=COUNTA(” + rangeBeg +":" + rangeEnd+ “)”;

totalCell = Aspose.Cells.CellsHelper.CellIndexToName(lastDataRow+1, idxAmount);
rangeBeg = Aspose.Cells.CellsHelper.CellIndexToName(2, idxAmount);
rangeEnd = Aspose.Cells.CellsHelper.CellIndexToName(lastDataRow, idxAmount);
xlsWorkbook.Worksheets[0].Cells[totalCell].Formula = “=SUM(” + rangeBeg +":" + rangeEnd+ “)”;

If you can mimic this feature, that would be great since I bought Aspose.Cells primarily to generate lists such as this…

Thanks,

Dominic.

Any ideas? I really need this feature…
Thanks.

Hi Dominic,

We will make this feature in the future version. Hopefully it will be available in the end of next month.

Hi Dominic,

Please try this fix.See following demo codes:

ListObjects listObjects = sheet.ListObjects;
listObjects.Add(1, 0, 3, 2, false);

The following list is the params of ListObjects.Add() method.

///


/// Adds a ListObject to the worksheet.
///

/// The start row of the list range.
/// The start row of the list range.
/// The start row of the list range.
/// The start row of the list range.
/// Whether the range has headers.
/// The index of the new ListObject

Hi,

I’ve done some rapid tests and the feature looks perfect and very simple to use. Thank you very much for this ! Can you tell me when the official hot fix will be released? Is the version you sent me unstable in any way?

FYI, I’ve used the following code for my tests:
string lastDataCell = Aspose.Cells.CellsHelper.CellIndexToName(xlsWorkbook.Worksheets[0].Cells.MaxDataRow, xlsWorkbook.Worksheets[0].Cells.MaxDataColumn);
Aspose.Cells.ListObjects listObjects = xlsWorkbook.Worksheets[0].ListObjects;
listObjects.Add(“A1”, lastDataCell, true);
listObjects[0].ShowTotals = true;
listObjects[0].ListColumns[idxAmount].TotalsCalculation = Aspose.Cells.TotalsCalculation.Sum;

Thanks again,

Dominic.

Hi Dominic,

Thanks for considering Aspose.

Well, You may use this version without any problem as the version is stable. Our next official release will include this feature. Normally we enhance the fucntionality, fixes bugs (if found) and add features to the latest existing release of the component to make a new version.

Thank you.

Hi,

I’ve done some more tests and the feature seems very fonctional! Thanks. But I noticed that the total row is outside of the list area. This is different from Excel 2003’s behaviour where the total row is a special row inside the list area.

Also, when right-clicking on the list area, the “Total Row” option is not selected when generated by Aspose.Cells, so I understand that the total row created by Aspose.Cells is an ordinary row using the SUBTOTAL function and not using Excel built-in functionnalities.

This makes it hard to let the end-user play around with the total row functions. In the default Excel behaviour, it is easily done using the drop down list as shown in the image in my previous message.

Do you believe that the exact behaviour can be mimicked?

Thank you again,

Dominic.

Hi Dominic,

Please try the attached fix.

I tested "Total Row" and it worked fine with following codes :

Workbook workbook = new Workbook();
workbook.Open("F:\\FileTemp\\abc.xls");

ListObjects listObjects = workbook.Worksheets[0].ListObjects;
listObjects.Add("A2", "B5", true);
listObjects[0].ShowTotals = true;
workbook.Save(@"F:\FileTemp\dest.xls");

If this fix still does not work, please post your codes and created file.

Hi,

I’ve tested with the fix you sent me and here are my findings.

Using the following code works (the total row in inside) :
listObjects[0].ListColumns[xlsWorkbook.Worksheets[0].Cells.MaxDataColumn].TotalsCalculation = Aspose.Cells.TotalsCalculation.Max;

Using the following code does NOT work (the total row is outside) :
listObjects[0].ListColumns[xlsWorkbook.Worksheets[0].Cells.MaxDataColumn].TotalsCalculation
= Aspose.Cells.TotalsCalculation.CountNums;

So, it seems the problem is caused by the total function used…

Dominic.

Hi Dominic,

Thanks for your info.

Please try this fix.

Hi,

It all seems to be working now, thanks a lot for your support.

Dominic.