Format date column in excel

Aspose.Cells Product Family
1
hi,

I have data returned and saved into excel fine.
I have 6 columns in the excel file from A to F. I want the F column has date format.

How do I accomplish it?

Here is my code snippet

DataLayer dl = new DataLayer();

DataSet ds = dl.PriceAdjuster(investorListID, yesterday, today);

if (ds.Tables.Count > 0)
{
//Create a datatable with respect to dataset table.
DataTable dt = ds.Tables["PriceAdjuster"];

Workbook workbook = new Workbook();
// Get the first default worksheet.
Worksheet worksheet = workbook.Worksheets[0];

//worksheet.Cells["A1"].PutValue("ImportData");

worksheet.Cells.ImportDataTable(dt, true, "A1");

// Save the excel file.
workbook.Save(pathPriceAdjuster);

}

Thanks
2

Hi,

Thanks for your posting and using Aspose.Cells.

You can use ApplyStyle() method for this purpose. Please see the following sample code and its output excel file. As you can see in the output excel file, the entire column F has now Date format.

I have attached both the source and output excel files for your reference.

C#
string filePath = @“D:\Downloads\sample.xlsx”;

//Load excel workbook
Workbook workbook = new Workbook(filePath);

//Access first worksheet
Worksheet worksheet = workbook.Worksheets[0];

//Create style object
Style style = workbook.CreateStyle();

//Number 14 means Date format
style.Number = 14;

StyleFlag flag = new StyleFlag();
flag.NumberFormat = true;


//Apply style on column F
worksheet.Cells.Columns[5].ApplyStyle(style, flag);

//Save the workbook
workbook.Save(“output.xlsx”);


3

Thanks Shakeel, it works.


what is the flag number for format date and time?

sample: 12/02/2015 1:40PM


4

Hi,

Thanks for your feedback and using Aspose.Cells.

Please let me explain how you can find it for yourself. There are two properties relating to it. Style.Number and Style.Custom. The number property deals with built-in Excel formats like number, currency, date etc. The custom property deals with the customize formats which you set yourself using Microsoft Excel.

Whenever you want to find the values of any Number or Custom property, then please first create your desired format in your excel file manually using Microsoft Excel and then load it using Aspose.Cells and observe the values of these properties in Quick Watch or Debug Windows.

Suppose you have set the cell A1 format manually using Microsoft Excel now you can find the values of custom and number properties like the following.

For StyleFlag, all the properties names explain itself. If you are not sure which of them you need to use, then please use StyleFlag.All property.

Please also see the following documentation relating to it for your reference.

( Format cells|Documentation )

C#
Cell cell = worksheet.Cells[“A1”];
Style style = cell.GetStyle();
//Now observe the style properties in Quick Watch