Performing count subtotal on Date column displays as date

Aspose.Cells Product Family
1

While doing a count on a Date column, the count value is shown in DateTime format (the formatting used in the column) rather than Number format.


Steps to reproduce:
  1. Create two columns, one for string values (column 0), one for date values (column 1), and populate with test data.
  2. Sort column 0 ascending.
  3. Format column 1 in Custom format (using Date string).
  4. Perform a subtotal on change in column 0, using ConsolidationFunction CountNums on column 1.
  5. Examine the worksheet.

2

Hi,


I can observe the issue you have mentioned in your posting. I have reproduced the issue with the following lines of code with “TestSubT.xls” as my input file:

Workbook wb = new Workbook("C://TestSubT.xls");

Worksheet sheet = wb.Worksheets[0];

CellArea ca = new CellArea()
{
StartRow = 4,
StartColumn = 0,
EndRow = sheet.Cells.MaxDataRow,
EndColumn = sheet.Cells.MaxDataColumn
};

sheet.Cells.Subtotal(ca, 0, ConsolidationFunction.CountNums, new int[] {1}, false, false, true);
wb.Save(@"c:\output.xls");

I am forwarding this issue to our development team for further investigation and resolution. We will update you once we get any update regarding this from our development team via this thread. This issue has been logged as: CELLSNET-40708
3

Hi,

We have fixed this issue.

Please download and use the latest fix: Aspose.Cells for .NET v7.2.1.6

4

This almost works. It actually does turn the subtotal into an integer, but it does so indiscriminately. In other words, if I use ConsolidationFunction.Min or .Max on a column formatted for a date, I expect the subtotal to appear as a date. If I use ConsolidationFunction.Count or .CountNums, I expect the subtotal to appear as an integer. This update fixed the Count but broke the Min/Max.

5

Hi,


Actually Dates are stored in numeric notations in Ms Excel, that’s why you are getting numeric values (for dates) although when you do the formatting (format to date/time in Ms Excel manually), it works as expected.

Anyways, I have logged your requirements, we will look into it if we can support it. Once we have any update, we will let you know here.

Thank you.
6

That’s understandable. What was happening before the update posted earlier in this thread was that the new row for the subtotal would use the same format as the previous row (which had already been formatted for a custom date string). While that worked for Min and Max, when performing a Count on that column, the count would appear in that date format. The update above changed the behavior such that, when adding a new row, the format would be reverted to General format regardless of which function was used. This makes the counts display properly, but I lose the formatting when doing subtotals on min and max. I could go through and reformat the entire column for the date format again, but that wouldn’t work if I used both count and min/max on the date, because the counts would again show as dates. I could code it myself to check subtotal rows and fix the formatting, but that is something I believe should be done by the API, as the automatic formatting I described is native to Excel.

7

Hi,


Thanks for giving us further details.

Sure, we will look into your requirement and check it if it should be supported by the product instead of the manual way of formatting.

Thank you.
8

Hi,



Please download and try the latest fix: Aspose.Cells for .NET v7.2.1.7

9

Better, but not quite there =). Thanks for all ya’ll’s hard work on this!


There is still a minor issue where, when performing a Count, Max, and Min causes the “Grand Min” and “Grand Max” to use the General formatting, as opposed to the Custom formatting. Presumably, because the Count is being done first, the Min and Max are being inserted with the General formatting specified by the Count row. I can work around that by doing the Count last, after doing the Min and Max, but this is something you might want to look at for later. As it stands, I’m satisfied that I can generate reports with subtotals to meet my customer’s requests.

Again, thanks for the hard work and effort in fixing these little bugs!
10

Hi,

Thanks for your appreciation.

We will look into your remaining issues and help you asap.

Your comments has been logged against the issue id: CELLSNET-40708

Could you please also provide us a sample project and screenshots replicating the problem. It will help us quickly sort out your issue.

11

Hi,

We have fixed this issue.

Please download and try the latest fix: Aspose.Cells for .NET v7.2.1.9

12

Hi,


Please try the attached latest fix v7.2.1.9

Your issue should should be fixed in it.

Thank you.
13

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

This message was posted using Notification2Forum from Downloads module by aspose.notifier.