Excel file creation

Question

User-2012457684 posted

I am creating an Excel file to for users that want to download reports.   To do so I am using information found on this site.

https://social.technet.microsoft.com/wiki/contents/articles/35603.asp-net-mvc-export-to-excel.aspx

I was able to make some changes to allow for me to designate the column headings and everything works great, except that one column returns SQL Server date data type  and Excel formats dates  as General which displays as such  39356, 39440, 39443 etc  (the number of days from 1/1/1900)  instead of as 10/1/2007, 12/24/2007, 12/27/2007  etc.    

If I send the data as datetime data type it will show properly, but not if it is just date data type.   Does anyone know what I need to do to make this format date columns correctly?   If I were the end user pulling this for myself I wouldn't care but my users will not know what the number displayed in that column stands for and may not know that to get to see the proper info all they need to do is change format of the column  from General to be Date.   

 

Answer 1

User-943250815 posted

Try to set worksheet.cell.value = new datetime(year, month, day)

Answer 2

User1520731567 posted

Hi mj1223,

I suggest you could set the field as string,it works fine.

The following code is what i modified,please refer to:

  public class Technology
        {
            public string Name { get; set; }
            public int Project { get; set; }
            public int Developer { get; set; }
            public int TeamLeader { get; set; }
            public string datatime { get; set; }

        }

 public class StaticData
        {
            public static List<Technology> Technologies
            {
                get
                {

                    return new List<Technology>{
                     new Technology{Name="ASP.NET", Project=12,Developer=50, TeamLeader=6,datatime="2017/01/01"},
                    new Technology{Name="Php", Project=40,Developer=60, TeamLeader=9,datatime= "2017/01/01"},
                    new Technology{Name="iOS", Project=11,Developer=5, TeamLeader=1,datatime= "2017/01/01"},
                     new Technology{Name="Android", Project=20,Developer=26, TeamLeader=2,datatime="2017/01/01"}
                };
                }
            }
        }

 public FileContentResult ExportToExcel2()
        {
            List<Technology> technologies = StaticData.Technologies;
            string[] columns = { "Name", "Project", "Developer", "datatime" };
            byte[] filecontent = ExcelExportHelper.ExportExcel(technologies, "Technology", true, columns);
            return File(filecontent, ExcelExportHelper.ExcelContentType, "Technologies.xlsx");
        }

How it works:

Best Regards.

Yuki Tao

Answer 3

User-2012457684 posted

Thanks.    I converted it in my SPROC to a varchar(10)   and now it shows as a date in Excel

CONVERT(varchar(10), AssessDate, 110) AS AssessDate