Click here to Skip to main content
15,889,116 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
 i am binding an excel coloumn using c#.I need to bind the date coloumn in my excel.There is a constant year alredy in my excel cell like 1980. I need to append random "mm/dd "to it.I am using  Microsoft.Office.Interop.Excel here. i have tried the following code but it lost the constant year value.how can i fix? My cell format in excel is General.I tried diffrent formath though.



i am getting like  8/6/System.__ComObject instead of 08/06/1


What I have tried:

for (int i = 2; i <= totalRows; i++)
            {
               
                xlWorksheet.Cells[i, 8] = GenerateRandomDates()+ xlWorksheet.Cells[i, 8] ; //not working expected 02/03/1980
              

            }



  public string GenerateRandomDates()
        {
            DateTime start = new DateTime(1995, 1, 1);
            int range = (DateTime.Today - start).Days;
            DateTime dt = start.AddDays(gen.Next(range));
            int month = dt.Month;
            int day = dt.Day;
            string formatted = string.Format("{0}/{1}/", month, day);
            return formatted;

        }
Posted
Updated 19-Aug-21 23:43pm

1 solution

The Worksheet.Cells indexer returns a Cell object not the cell content: try adding ".Value" to the indexer:
C#
xlWorksheet.Cells[i, 8] = GenerateRandomDates()+ xlWorksheet.Cells[i, 8].Value;
 
Share this answer
 
Comments
[no name] 20-Aug-21 7:08am    
but coming in default date format-11-08-1989.How i will get 11/08/1989
i couldnt find this format in cell format also
OriginalGriff 20-Aug-21 7:23am    
You are going to have to explain that in much better detail - remember I can't see your screen, and you GenerateRandomDates method explicitly uses "/" not "-" so I have no idea what you are talking about !
[no name] 20-Aug-21 7:32am    
GenerateRandomDates()= this function will return random string suppose '02/03'.And xlWorksheet.Cells[i, 8].Value in excel is 1989.so noramaly it should be like 02/03/1989.but when i concatinate excel displaying 02-03-1989. i need in the format of 02/03/1989
OriginalGriff 20-Aug-21 8:35am    
If you mean "but it doesn't look like that when I open the file in Excel" that's because the column doesn't have any date formatting applied - so Excel displays it in the system wide standard date format. You can mess with that in the Sheet Column properties, but generally what you will do is annoy all those whose system date is not set to your prefered format (Americans, for example, who use mm/dd/yy, or Japanese who prefer yy/mm/dd (The ISO standard) or Europeans who like dd-mm-yy, or ... you get the idea!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900