How to Get Formatting Date Value in WinForms Excel?
Syncfusion® WinForms Excel library is a .NET Excel library used to create, read, and edit Excel documents. Also, converts Excel documents to PDF files. Using this library, you can format the date value based on culture.
Steps to format the date value based on culture, programmatically:
Step 1: Create a new C# console application project.
Create a new C# console application project
Step 2: Install the Syncfusion.XlsIO.WinForms NuGet package as reference to your .NET Framework application from NuGet.org.
Install the NuGet package to the project
Step 3: Include the following namespaces in the Program.cs file:
C#
using Syncfusion.XlsIO; using System; using System.Globalization; using System.Threading;
VB.NET
Imports Syncfusion.XlsIO Imports System.Globalization Imports System.Threading
As per MS-Excel behavior, the display text varies according to machine culture only when default date format is applied. Otherwise, it serializes the cell value to custom format. Syncfusion Essential® XlsIO does the same.
Step 4: Include the following code in main method of Program.cs file to format the date value based on culture.
C#
using (ExcelEngine excelEngine = new ExcelEngine())
{
// Set CurrentCulture and CurrentUICulture to required culture
// English - UK culture is used here
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-GB");
Thread.CurrentThread.CurrentUICulture = new CultureInfo("en-GB");
// Instantiate the Excel application object
IApplication application = excelEngine.Excel;
// Create a new Excel workbook
IWorkbook workbook = application.Workbooks.Create(1);
// Get the first worksheet in workbook into IWorksheet
IWorksheet worksheet = workbook.Worksheets[0];
// Assign date time values in worksheet
worksheet.Range["D1"].DateTime = new DateTime(2009, 7, 30);
worksheet.Range["D3"].DateTime = new DateTime(2010, 8, 31);
worksheet.Range["D5"].DateTime = new DateTime(2011, 9, 30);
worksheet.Range["D7"].DateTime = new DateTime(2012, 10, 31);
// Assign number format for the cells
worksheet.Range["D5"].NumberFormat = "m/d/yyyy";
worksheet.Range["D7"].NumberFormat = "m/d/yyyy";
// Autofit the columns
worksheet.UsedRange.AutofitColumns();
// Save the Excel workbook
workbook.SaveAs("Output.xlsx");
}
VB.NET
Using excelEngine As ExcelEngine = New ExcelEngine()
'Set CurrentCulture And CurrentUICulture to required culture
'English - UK culture Is used here
Thread.CurrentThread.CurrentCulture = New CultureInfo("en-GB")
Thread.CurrentThread.CurrentUICulture = New CultureInfo("en-GB")
'Instantiate the Excel application object
Dim application As IApplication = excelEngine.Excel
'Create a new Excel workbook
Dim workbook As IWorkbook = application.Workbooks.Create(1)
'Get the first worksheet in workbook into IWorksheet
Dim worksheet As IWorksheet = workbook.Worksheets(0)
'Assign date time values in worksheet
worksheet.Range("D1").DateTime = New DateTime(2009, 7, 30)
worksheet.Range("D3").DateTime = New DateTime(2010, 8, 31)
worksheet.Range("D5").DateTime = New DateTime(2011, 9, 30)
worksheet.Range("D7").DateTime = New DateTime(2012, 10, 31)
'Assign number format for the cells
worksheet.Range("D5").NumberFormat = "m/d/yyyy"
worksheet.Range("D7").NumberFormat = "m/d/yyyy"
'Autofit the columns
worksheet.UsedRange.AutofitColumns()
'Save the Excel workbook
workbook.SaveAs("Output.xlsx")
End Using
A complete working sample to format the date value based on culture can be downloaded from Formatting-Date.zip.
By executing the program, you will get the output Excel document as follows.
Output Excel document
Take a moment to peruse the documentation, where you will find other options like move or copy a worksheet, freeze, unfreeze and split panes, show or hide worksheet and worksheet tabs, page setup settings, worksheet row and column manipulations and more with code examples.
Click here to explore the rich set of Syncfusion® Excel (XlsIO) library features.
Note:
Starting with v16.2.0.x, if you reference Syncfusion® assemblies from trial setup or from the NuGet feed, include a license key in your projects. Refer the link to learn about generating and registering Syncfusion® license key in your application to use the components without trail message.
Conclusion
I hope you enjoyed learning about how to get formatting date value in WinForms Excel.
You can refer to our XIsIO’s feature tour page to learn about its other groundbreaking features. Explore our UG documentation and online demos to understand how to manipulate data in Excel documents.
If you are an existing user, you can access our latest components from the License and Downloads page. For new users, you can try our 30-day free trial to check out XlsIO and other Syncfusion components.
If you have any queries or require clarification, please let us know in the comments below or contact us through our support forums, Support Tickets, or feedback portal. We are always happy to assist you!
