How to get formatting date value based on culture in C#, VB.NET?
Syncfusion Excel (XlsIO) 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 NuGet package to the project
Step 3: Include the following namespaces in 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 based on culture in C#, VB.NET.
You can refer to our WinForms Excel’s feature tour page to know about its other groundbreaking feature representations. You can also explore our WinForms Excel documentation to understand how to present and manipulate data.
For current customers, you can check out our WinForms components from the License and Downloads page. If you are new to Syncfusion, you can try our 30-day free trial to check out our WinForms Excel and other WinForms components.
If you have any queries or require clarifications, please let us know in comments below. You can also contact us through our support forums, Direct-Trac, or feedback portal. We are always happy to assist you!
