Category: ASP.NET

25 Aug 2016 03:34

Joined:	06/02/2008
Level:	Silver
Points:	394

Points:	4

How to keep export excel date format irrespect to region settings in vb.net

i have an requirement.need to keep export excel date format dd-MMM-yyyy format in export excel .its asp.net web application here is the code

vSheet.Rows(vRowIndex + 2).Cells(vColumnIndex).Value = DateTime.ParseExact(vCellText, "dd-MMM-yyyy", CultureInfo.InvariantCulture)

i am getting the expected format on my local machine. when i deploy the same in server the date format changing to dd-mm-yyyy

any idea how to keep date format should not read region settings while exporting excel

#767626

25 Aug 2016 04:26

Joined:	03/05/2013
Level:	Gold
Points:	15822

Points:	2

Hi,

Did you format your entire column with your desired date format.

Ex:


Range rng = (Excel.Range)worksheetobject.Cells[1,1];
rng.EntireColumn.NumberFormat = "dd-MMM-yyyy";

Hope this helps you,....

--------------------------------------------------------------------------------
Give respect to your work, Instead of trying to impress your boss.

N@veen
Blog : http://naveens-dotnet.blogspot.in/

#767627

25 Aug 2016 06:18

Joined:	13/01/2015
Level:	Silver
Points:	230

Points:	2

Hi,

You can do it like :

int r = worksheet.UsedRange.Count;
worksheet.Cells["A1:A" + r].NumberFormat = "MM/DD/YYYY"; // the column that u want to format.

Best Regards,
Anjali Bansal

--------Give ur best and leave rest

Thanks!
Anjali Bansal

~Give your best and lead the world

#767631

25 Aug 2016 21:05

Joined:	29/05/2009
Level:	Gold
Points:	10380

Points:	2

Hi
you can try this Date format for your machine format



string sysFormat = CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern;

string sysUIFormat = CultureInfo.CurrentUICulture.DateTimeFormat.ShortDatePattern;

Name : Dotnet Developer-2015
Email Id : kumaraspcode2009@gmail.com

'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

#767633

26 Aug 2016 01:35

Joined:	15/02/2016
Level:	Bronze
Points:	75

Points:	3

Hi,

I just tried this free .net excel library https://visualstudiogallery.msdn.microsoft.com/81c33294-eee9-4683-9cee-a5714cfebf34 and use the following code to set the number format to dd-MMM-yyyy format, it works fine:

Workbook workbook = new Workbook();
workbook.LoadFromFile(strPath+strName);
Worksheet sheet = workbook.Worksheets[0];
sheet.Range["A2:B5"].NumberFormat = "dd/MMM/yyyy";
workbook.SaveToFile(strName);

How to keep export excel date format irrespect to region settings in vb.net

About dotnetspider.com

Quick Links

General

Services