Setting Date format for a cell not working in .NET?

Aspose.Cells Product Family
1

Hi ,
We are mostly certain of migrating from interop to Aspose cells but getting issues more often.
We have an issue when setting a number format for a cell having date value.

I have attached a sample project(PARis_Excel_Requirement.zip (463.5 KB)) where i have replicated the above issue.
It will generate two excel files(“test_AsposeImplmn.xlsx” and “test_InteropImplmn.xlsx”), one uses aspose cells dll and other uses interop. Please compare and see the output is different.

The aspose generated excel output should match with the excel generated using interop(“test_InteropImplmn.xlsx”)

Please provide a solution as soon as you can.

FYI: The format setting related code is present in file ExcelOpsAspose.cs Line No: 395 , (public static void SetNumberFormat(string numberFormat, Range range)) method.

Note: I have removed aspose.cells dll because of size issue.

Regards,
Prathap

2

@PrathapSV,

Thanks for the sample project, template files and details.

I have checked your sample project a bit. I think the issue might be with your code, so you should debug it and fix it by yourselves. I tried to evaluate your project but could not figure it out as it uses dependencies. For confirmation that it is not an issue with Aspose.Cells by any means, I tested your scenario/case using a simple sample code, it works fine and the values are formatted precisely:
e.g
Sample code:

var testDateValue = "3/4/2019";
            const string customFormat = "mmm-yyyy";

            var datevalue1 = "24/3/2019";
            const string customFormat1 = "General";


            var workbook = new Workbook();

            var worksheet = workbook.Worksheets[0];

            var cell = worksheet.Cells[0, 0];

           
            cell.PutValue(testDateValue, true);
            var style = cell.GetStyle();
            style.SetCustom(customFormat, false);
            cell.SetStyle(style);

            var cell1 = worksheet.Cells[0, 1];
            var date1 = DateTime.Parse(datevalue1);
            cell1.PutValue(date1);
            var style1 = cell1.GetStyle();
            style1.SetCustom(customFormat1, true);
            cell1.SetStyle(style1);


            workbook.Save("e:\\test2\\out1.xlsx");

Please give it a try to the above code segment and you will find it works as expected. If you still think it is an issue with Aspose.Cells, kindly separate the issue to simplify your project (runnble) and make it short (also do not use any dependencies or other APIs but Aspose.Cells) a bit, zip the project and post us here, we will check it soon. Even if you could write a short code segment (runnble) same as above to reproduce the issue, this will help us really to evaluate your issue efficiently and to consequently figure it out soon.

3

Hi,

Thanks for the above code, using which i was able to figure out the issue myself.
The issue happens because of the way we are assigning the value for the cell/range.
I was just using
range.Value=“some date string” approach to assign the value which i modified to
range.PutValue(“some date string”,true,false); which works fine.(Note: we have to pass 2nd parameter [isConverted] to true always or else it wont work).

I am not sure, what’s the purpose of this “[.Value](Cell.Value | Aspose.Cells for .NET API Reference)” property and it’s pretty much confusing to user to not to assign the values using this property and use PutValue method.

Can you let me know, is this your internal bug or are we not supposed to use that? Also will it cause any other issues if i use PutValue() method the above way ? as i need to make this change all over in our class wherever we are setting the value using .Value property.

4

@PrathapSV,

For the setter of Cell.Value, it is just same with Cell.PutValue(object). The prior one is the original API we provided for the users to get/set cell value (and it is more convenient for coding). However, in the evolution of our component, our customers required more and more complex and flexible features to manipulate the cell value. So we add other overloaded methods for “PutValue” to provide more flexibility. At the same time, we have to keep the older APIs to make our component compatible with existing codes of other users.

For performance and compatibility consideration, we do not parse and convert the user input values when user is just setting the object to a cell object directly. If a user needs the object to be parsed automatically, he should use the “convert” parameter. Alternatively he can just parse the object by himself at first, then set the parsed object to cell/cells. Which may give him better performance.

Hope, this helps a bit.