Set DateTime formatting to a column in MS Excel worksheet in .NET

Aspose.Cells Product Family
1

Hello,


I have been using the style.custom method to format cells to numeric value with decimal places. Is there a way to format cells to a DateTime datatype? I tried it with style.custom but it doesn’t work.

string ColHeader = row[“MyColHeader”].ToString();

Cell cell = worksheet.Cells.FindString(ColHeader, null);

Style style = workbook.Styles[workbook.Styles.Add()];

StyleFlag flag = new StyleFlag();

style.Custom = "m/d/yy"

flag.NumberFormat = false;

worksheet.Cells.Columns[cell.Column].ApplyStyle(style, flag);
2

Hi Paam,


Thank you for contacting Aspose support.

We have found two problems with your code that could be the reason of not getting the desired results on your end.

  1. You are using the obsoleted method Cells.FindString, instead you should be using the Cells.Find method.
  2. You have to set the StyleFlag.NumberFormat to true in order to set the custom style.

Please check the below provided code snippet that we have used to evaluate your presented scenario, and attached output that reflects the correct/desired results. Please note, we have used the latest version of Aspose.Cells for .NET 8.0.2 for our testing. For reference, please have a look at the detailed article on Setting the Number & Date Formats.

C#

Workbook book = new Workbook(myDir + “Book1.xlsx”);
Worksheet sheet = book.Worksheets[0];

Cells cells = sheet.Cells;
Cell cell = sheet.Cells.Find(“Column A”, null, new FindOptions() { CaseSensitive = false});
Style style = book.Styles[book.Styles.Add()];
StyleFlag flag = new StyleFlag();
style.Custom = “m/d/yy”;
flag.NumberFormat = true;
Column col = sheet.Cells.Columns[cell.Column];
col.ApplyStyle(style, flag);
book.Save(myDir + “output.xlsx”);


In case you face any difficulty, please feel free to write back.
3

Hi Babar,





This does not solve the issue. I apologize for not being clear with my question. The suggested solution only put the string in a datetime format, it does not change the datatype to System.DateTime.




I wanted to mention that I tried the suggested solution and exported the data to a datatable using ExportToDataTable method, however my custom column is showing up as System.String not System.DataTime.




Please advise.



Thanks.

4

Hi Paam,


Sorry for the confusion earlier.

As I have analyzed your presented scenario, you wish to change the format of the worksheet column and export the cells to DataTable while assigning the appropriate DataType. For instance, if you have a column formatted as Date in the spreadsheet, you wish it to be of DateTime DataType when you export the cells to DataTable.

Unfortunately, I was unable to find a solution for this scenario therefore I have logged a Feature Request under the ticket CELLSNET-42660 to provide such functionality in future releases of Aspose.Cells for .NET API. Please spare us little time to properly analyze the request on our end then we will be able to share an estimated release schedule for the implementation. In the meanwhile, please refer to your other thread on same subject where we have shared a workaround for a similar situation.

Please let me know if my understanding is correct.

5

Hi Paam,


Thank you for your patience.

We have probed further into your presented scenario. The requested feature is already supported by Aspose.Cells APIs, and is available with Aspose.Cells for .NET 8.0.2. Please execute the below provided code snippet against the attached spreadsheet, and you will notice that the API has assigned the correct DataType for the column in DataTable.

C#

Workbook workbook = new Workbook(myDir + “book2.xlsx”);
ExportTableOptions options = new ExportTableOptions();
options.ExportColumnName = false;

DataTable dt = workbook.Worksheets[0].Cells.ExportDataTable(0, 0, 3, 1, options);
Console.WriteLine(dt.Columns[0].DataType);

Moreover, if you haven't specified the appropriate format to the column in the spreadsheet, you can still specify the desired DataType using the ExportTableOptions.DataTable property as follow, and export the data into a DataTable while passing the instance of ExportTableOptions to the ExportDataTable method.

C#

ExportTableOptions options = new ExportTableOptions();
options.DataTable = new DataTable(); options.DataTable.Columns.Add("Column1", typeof(DateTime));

In case you still do not get desired results with any of your sample spreadsheets, please share it with us for further analysis.

6

Thank you for the info. I have one question with this feature. Do I have to specify all the columns and its type in the ExportTableOptions? For example, I have 10 columns in the spreadsheet and only 1 of them is a datetime column. Can I only specify “options.DataTable.Columns.Add(“Column1”, typeof(DateTime));”?

Or I have to specify the column and its datatype for all 10?

7

Hi Paam,


Thank you for your inquiry and using Aspose.Cells.

You will have to specify all the columns data type inside your data table in sequence. So if you have 10 columns and 3rd is datetime, then you will have to specify all first 2 as string and 3rd as datetime and rest of 4-10 as strings again.