In Aspose cell 5.0 version-how to set a column to dateformat

Aspose.Cells Product Family
1

Hi,

I have added this line in my code template for export to excel ,

to convert the column to date format ,below lines is added,but it is not getting filtered in date format.

Aspose.Cells.Cells exportCells = excel.Worksheets[0].Cells;

exportCells.Columns[9].Style.Number = 14;

Please let me know where the issue is????

Thanks!!

Chandini.N

2

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Since you are using the older version, please download and use the latest version:
Aspose.Cells
for .NET v7.2.0.8 it will work fine.

Please use Column.ApplyStyle method for your needs.

Please see the following code. I have also attached the output file generated by this code.

Please also see the screenshot for your understanding.

C#

Workbook workbook = new Workbook();

Worksheet worksheet = workbook.Worksheets[0];

Cell cellA1 = worksheet.Cells["A1"];

StyleFlag flag= new StyleFlag(){ All = true };

Style style = new Style();
style.Number = 14;

worksheet.Cells.Columns[0].ApplyStyle(style, flag);

workbook.Save("output.xlsx");

Screenshot:

3

StyleFlag flag= new StyleFlag(){ All = true };

This line getting error?????

4

Hi,

Please download and use the latest version. You will not get any error.

You can also try changing the above line like this.

StyleFlag flag= new StyleFlag();

flag.All = true;


5

Ok! Thanks for your quick reply.I will impliment this code in my template.

6

I have implimented the code what you have sent.but Iam getting in date format but date filter option not getting ,i want my file output will be as the attachedfile resultfile.jpg ,but iam getting the file as gettingfile.jpg

Code:


Aspose.Cells.Workbook workBookPrj = new Aspose.Cells.Workbook();

workBookPrj = objRpt.ExportProjects(dsReport.Tables[0]);

workBookPrj.Worksheets.ActiveSheetIndex = 0;

workBookPrj.Save(path + filename + ".xlsx");

Worksheet worksheet = workBookPrj.Worksheets[0];

StyleFlag flag = new StyleFlag();

flag.All = true;

Aspose.Cells.Style style = workBookPrj.Styles[workBookPrj.Styles.Add()];

style.Number = 14;

worksheet.Cells.Columns[9].ApplyStyle(style, flag);

worksheet.Cells.Columns[16].ApplyStyle(style, flag);

worksheet.Cells.Columns[18].ApplyStyle(style, flag);

Thanks!!!
chandini

7

Hi,

Please refer to this article for your help.



8

Hi,


Please try setting the style number to 17, i.e.

style.Number = 17;

I hope this gives you what you want.
9

No use!!,same text filter only getting…I want date filter option…

10

Hi,


style.Number = 15;

should work in your case. Can you please quickly give it a try and let us know your feedback? I can achieve this with the following lines of code:

Workbook wb = new Workbook();
Worksheet sheet = wb.Worksheets[0];

Cells cells = sheet.Cells;
Cell cell = cells[“E2”];
cell.PutValue(“May 03,2012”, true);

Style style = cell.GetStyle();

style.Number = 15;

cell.SetStyle(style);
cell = cells[“E3”];

cell.PutValue(“May 04,2012”, true);

cell.SetStyle(style);

sheet.AutoFilter.Range = “E2:E3”;

//save
wb.Save(“C://Mout.xls”);

I am attaching the screenshot for your reference.
11

Salam Shakeel Bhai

I am also using the 5.1.2.0 version of this library. However, i have managed to fix that problem by using the following syntax:

theCells["E1"].Value = String.Format("{0:dd MMM yyyy}", theCells["E1"].Value);

Please let me know if this method has some drawbacks.
Thanks
12

Walikum Salam,

Since, you are using older version, so I am not sure if there is any method like Cell.PutValue().

Mostly, we recommend you to use Cell.PutValue() method rather than the property Cell.Value. Cell.PutValue() also defines the type of the cell’s data.

However, since you are formatting the date yourself, so when you will insert it in your worksheet, it will be inserted as a text not as a date.

Please use the approach mentioned in this article to set the display formats of your numbers and date values.


You can also download and use the latest version from this link:
Aspose.Cells
for .NET v7.3.2.1