You must Sign In to post a response.
  • Category: ASP.NET

    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
    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
    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

  • #767631
    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
    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);

  • #767634


Sign In to post your comments