none
Setting cell using excel introp changes date RRS feed

  • Question

  • I'm trying to enter a date value into an excel file.

    For e.g.

    "05/07/19" (July 5th of 2019) and the date formate of the cell in the excel is set to "dd-MM-yy" (regional setting of the computer is also the same). But when my code enters the value into excel, value is getting changed to "07-05-19" (May 7th of 2019).

    I want it to stay as "05-07-19" and also excel should consider it as a date.

    I have tried different ways to set the value into an excel,

            const string DATE = "05/07/19";
            Application app = new Application();
            Workbook workBook = app.Application.Workbooks.Open(FILE_PATH);
    
            Worksheet sheet = workBook.Sheets[1];
            Range range = sheet.get_Range("A1");
            range.set_Value(XlRangeValueDataType.xlRangeValueDefault, DATE);
    
            Range range2 = app.get_Range("A1", "A2");
            range2.Cells[2, 1] = DATE;
    
            Range range3 = app.get_Range("A3", "A3");
            range3.Value = DATE;
    
            Range range4 = app.get_Range("A4", "A4");
            //Of course, It is not setting the value as a Date but text.
            range4.Value = new string[] { DATE };

    I want my code to behave as per the formate of the cell. If the input can be fit as a date (based on the cell formate for date), it should be considered as a date, else just a text. (The way excel behaves when the user manually input the data)

    Monday, December 9, 2019 7:05 AM

All replies

  • The problem is not with your requested display formats.  The problem is at the very beginning, when your string is converted to a date ordinal.  Excel interprets your text in the US format, so that 05/07/19 is May 7th, not July 5th.  I don't know of any way to override that.  Perhaps you should reformat your dates into the =DATE() function, so you can enter the pieces unambiguously.

    Tim Roberts | Driver MVP Emeritus | Providenza & Boekelheide, Inc.

    Monday, December 9, 2019 7:19 AM
  • Thanks Tim,

    This suggestion works if I have a DateTime object rather than a string (maybe I would need to put parsing logic for this).

    But can you go through this update? :

    "So far I got to know that runtime version of excel interop (File version: 15.0.4420.1017) is v2.0 and If I run my code with target framework .Net 3.5, it is working as expected.

    But the same code is having the problem if I change the target framework to .Net 4.6"

    can this be a problem with Excel Interop's compatibility ?


    Monday, December 9, 2019 8:34 AM
  • You're recommanded to always store Date value in ISO 8601 format (i.e.: yyyy-MM-dd)

    When you open the Excel file, you should see whatever the default date format you're using in Excel if no format is specified.

    Now if you want to display in particular format, you can set it with cell.NumberFormat.

    Monday, December 9, 2019 9:46 AM
    Answerer
  • Hello,

    If open to using a free library then continue.

    The following uses SpreadSheetLight which works on .xlsx, not .xls.

    NuGet SpreadSheetLight package. And also this Microsoft package version 2.5

    Code showing creating a new Excel file, rename sheet1 to Demo. Add cell values including dates.

    using System;
    using System.Windows.Forms;
    using SpreadsheetLight;
    
    namespace SpreadSheetLightProject
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                var excelDocument = new SLDocument();
                
                excelDocument.RenameWorksheet(SLDocument.DefaultFirstSheetName, "Demo");
                excelDocument.SetCellValue("A1", 123456789.12345);
                excelDocument.SetCellValue(2, 1, -123456789.12345);
                excelDocument.SetCellValue(3, 1, new DateTime(2019, 7, 5));
                excelDocument.SetCellValue(4, 1, 12.3456);
                excelDocument.SetCellValue(5, 1, 12.3456);
                excelDocument.SetCellValue("A6", 123456789.12345);
    
                SLStyle style = excelDocument.CreateStyle();
                style.FormatCode = "dd-MM-yy";
    
                excelDocument.SetCellStyle(3, 1, style);
                var d1DateTime = new DateTime(2019,7,5);
    
                var cellRef = SLConvert.ToCellReference(1, 3);
                excelDocument.SetCellStyle(cellRef, style);
                excelDocument.SetCellValue(cellRef, d1DateTime);
    
    
                excelDocument.SaveAs("Demo.xlsx");
    
            }
        }
    }
    

    You can also do this with existing Excel file rather than create a new file.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, December 9, 2019 3:08 PM
    Moderator