如何使用linq从excelsheet中的数据库导出数据？ [英] How to export data from database in excelsheet using linq?

问题描述

使用linq查询 - ：

using linq query-:

public IEnumerable<EmployeeModel> GetAllEmployeeInExcel()
   {
       try
       {
           using (var ContextDb = new FourMagazineEntities())
           {
               var employee = (from itm in ContextDb.Employees
                               where itm.Role == 3
                               select new EmployeeModel
                               {
                                   EmpID = itm.EmpID,
                                   Code = itm.Code,
                                   Name = itm.Name,
                                   Email = itm.Email,
                                   Role = itm.Role.Value,
                                   RoleName = (from c in ContextDb.Roles.Where(d => d.RoleID == itm.Role) select c.RoleName).FirstOrDefault(),
                                   Address = itm.Address,
                                   City = itm.City,
                                   Country = itm.Country,
                                   PhoneNo = itm.PhoneNo,
                                   MobileNo = itm.MobileNo,
                                   CreateDate = itm.CreatedDate.Value,

                                   //EmpImage = itm.EmpImage,

                                   VatNo = itm.VatNo,
                                   CompanyID = itm.CompanyID.Value,
                                   CompanyName = (from c in ContextDb.Employees.Where(d => d.EmpID == itm.CompanyID) select c.Name).FirstOrDefault(),
                                   Status = itm.Status.Value == true ? true : false,
                               }).OrderByDescending(x => x.EmpID).ToList();
               return employee;
           }
       }
       catch (Exception ex)
       {
           throw ex;
       }
   }


protected void btnExport_Click(object sender, EventArgs e)
        {
            ExportToExcel();
        }

        public void ExportToExcel()
        {
            var emp = objEmployee.GetAllEmployeeInExcel();
            if (emp != null)
            {
                string filename = "Employee.xls";
                System.IO.StringWriter tw = new System.IO.StringWriter();
                System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
                DataGrid dgGrid = new DataGrid();
                dgGrid.DataSource = emp;
                dgGrid.DataBind();

                //Get the HTML for the control.
                dgGrid.RenderControl(hw);
                //Write the HTML back to the browser.
                //Response.ContentType = application/vnd.ms-excel;
                Response.ContentType = "application/vnd.ms-excel";
                Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename + "");
                this.EnableViewState = false;
                Response.Write(tw.ToString());
                Response.End();
            }
        }

从这个代码上方我已经获取excel表中的所有数据，但不需要一些列数据。所以，我无法理解获取一些列数据。

from above this code i already fetch all data in excel sheet but some column data are not required. so, i can't understand to fetch some column data.

SalesName	  Password	Role	Address    EmpImge    EmpStatus
raj			         1       sdf
abhi			         2       abc

我想从数据库中获取SalesName不需要其他角色地址。

I want to fetch from database SalesName Role Address other are not required.

推荐答案

只需使用下面提到的自定义模型和 GetAllEmployeeInExcel（）方法。

Just use below mentioned Custom Model with your GetAllEmployeeInExcel() method.

using (var ContextDb = new FourMagazineEntities())
          {
              var employee = (from itm in ContextDb.Employees
                              where itm.Role == 3
                              select new EmployeeModel
                              {
                                  Name = itm.Name,
                                  Role = itm.Role.Value,
                                  Address = itm.Address,

                              }).OrderByDescending(x => x.EmpID).ToList();
              return employee;
          }

这篇关于如何使用linq从excelsheet中的数据库导出数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！