locked
Excel file creation RRS feed

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

     

    Friday, January 11, 2019 10:11 PM

Answers

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 14, 2019 10:30 AM

All replies

  • User-943250815 posted

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

    Friday, January 11, 2019 10:57 PM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 14, 2019 10:30 AM
  • 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

    Monday, January 14, 2019 5:03 PM