While Pivoting the excel sheet some of the rows in the date column are showing as number

Aspose.Cells Product Family
1

I was trying to pivot the excel and am getting a pivoted sheet and from that am trying to remove the header “sum of value” row. For that i copied the source sheet to another destination sheet neglecting the first row in the source sheet. So while copying am getting date in the first row as date itself in the second row it is coming as number.
example “2023-12-17” converted as “45277”. how can i prevent this?. Your support will be much appreciated.

example of my data

ID PivotColumn Value Date Created Type PayAmount
234 code1 123.4 2023-12-17 s1 123.4
234 code2 567.8 2023-12-17 s1 567.8
235 code1 123.4 2023-12-17 s1 123.4
235 code2 567.8 2023-12-17 s1 567.8
238 code5 123.4 2023-12-19 b1
239 code6 123.4 2023-12-19 b1
240 code7 123.4 2023-11-11 m1
241 code8 123.4 2022-12-12 m1
242 code9 123.4 2023-12-12 s1
243 code10 123.4 2023-12-12 m1
244 code11 123.4 2023-12-12 s1

output goes below .see the “Date Created” column

ID Date Created Type Pay Amount code1 code2 code5 code6 code7 code8 code9 code10 code11
234 2023-12-17 s1 123.4 123.4
234 45277 s1 567.8 567.8
235 2023-12-17 s1 123.4 123.4
235 45277 s1 567.8 567.8
238 2023-12-19 b1 (blank) 123.4
239 2023-12-19 b1 (blank) 123.4
240 2023-11-11 m1 (blank) 123.4
241 2022-12-12 m1 (blank) 123.4
242 2023-12-12 s1 (blank) 123.4
243 2023-12-12 m1 (blank) 123.4
244 2023-12-12 s1 (blank) 123.4
2

@shijinmk2006,

Thanks for the details.

To evaluate your issue precisely, we need your resource files and sample code snippet. Could you please share sample files (input file(s) (if any) and output file(s), etc.) and sample (runnable) code that you are using to reproduce the issue on our end? We will check your issue soon.

PS. please zip the resource files prior attaching here.

4

Hi @amjad.sahi ,

Thanks for your quick reply. Much appreciated on that. Hope below solution will help you find the solution for that.

Please see the attached sample code in c#.
I have attached the Input and output file in that solution so it will be easier for you.
In the output file you can see the “sheet3” date column having numbers.
PivotExcelFile.zip (22.6 KB)

Thanks

5

@shijinmk2006,

After an initial testing, I was able to reproduce the issue as you mentioned by using your template file and sample code segment. I found while pivoting the Excel sheet, some of the rows in the date column are showing as numbers.

We require thorough evaluation of the issue. We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-55115

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

6

Thanks @amjad.sahi for the quick reply.

Please update this conversation once a solution for the above scenario is found.

Thanks

7

@shijinmk2006,

You are welcome. And, sure, once we figure out your issue or we have an update on your issue, we will let you know here.

8

@shijinmk2006,

We are pleased to inform you that your issue (logged earlier as “CELLSNET-55115” ) has been resolved. The fix will be included in the upcoming release (Aspose.Cells v24.3) that we plan to release in the next month. You will be notified when the next version is released.

9

@amjad.sahi Thanks for fixing the issue.

Is there any work around that we can do in the existing version itself. something like any custom code or function that we can call and resolve for the time being?

Thanks

10

@shijinmk2006,

I’m sorry, but it appears that there may not be a workaround to fix your issue. Nonetheless, we will continue to investigate and keep you updated if a workaround becomes available. This issue was a bug in the APIs, which has now been fixed. You can either wait for the next release or consider obtaining paid support if the issue is critical to you.

11

@shijinmk2006
Please try the following two solutions to set style :
1, Set style to the column:

Worksheet pivotSheet = workbook.Worksheets[workbook.Worksheets.Add()];
            Column column = pivotSheet.Cells.Columns[1];
            Style style = column.GetStyle();
            style.Number = 14;
            column.SetStyle(style);

2,Select the pivot area and format it:

     PivotArea pivotArea = new PivotArea(pivotTable);
            pivotArea.Select(PivotFieldType.Row, 1, PivotTableSelectionType.LabelOnly);
            Style style = workbook.CreateStyle();
            style.Number = 14;
            pivotTable.Format(pivotArea, style);
12

Hi @simon.zhao ,

we already tried this applying style to the column. But the problem encountered is, suppose if we have any other numeric column that have “45621” as cell value when we apply style it will also be converted to “2024-11-25”. We don’t know the specific column type and also, we don’t want to apply style manually to each column.

Is there any way to keep the original style while we copy data from pivotTable to destination sheet, without any change in the format .

Thanks

13

@shijinmk2006,

You may try using the second workaround suggested by @simon.zhao if it meets your needs. However, you need to know about the problematic cells (values) beforehand. If you are unable to use the workarounds we provided, unfortunately, you will have to wait for the next release which is scheduled for next month.

14

Thanks @amjad.sahi

15

@shijinmk2006,

You are welcome.

16

The issues you have found earlier (filed as CELLSNET-55115) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi