Exporting to excel #5698

5 Answer(s)

• 

  0

  Anjali_Musmade created 8 months ago

  Support Team Support Team Member

  Hello darutter,

  I have followed the below steps to get the desired output:

  1. I have created Entity Person -> Name, Address, City, State, PostalCode
  2. Second Entity ItemSet1 -> Name, Data
  3. Third Entity ItemDataSet2 -> Name,Data
  4. I have added Navigation (1tomany) from Person to ItemSet1 and ItemDataSet2.
  5. I have used below API to exporttoexcel

   public virtual async Task<IRemoteStreamContent> GetListAsExcelFileAsync(PersonExcelDownloadDto input)
          {
              var downloadToken = await _excelDownloadTokenCache.GetAsync(input.DownloadToken);
              if (downloadToken == null || input.DownloadToken != downloadToken.Token)
              {
                  throw new AbpAuthorizationException("Invalid download token: " + input.DownloadToken);
              }
  
              var people = await _personRepository.GetListWithNavigationPropertiesAsync(input.FilterText, input.Name, input.Address, input.City, input.State, input.PostalCodeMin, input.PostalCodeMax);
              var items = people.Select(item => new
              {
                  Name = item.Person.Name,
                  Address = item.Person.Address,
                  City = item.Person.City,
                  State = item.Person.State,
                  PostalCode = item.Person.PostalCode,
  
                  Itemset1 = item.Itemset1?.Item1Name,
                  ItemDataset2 = item.ItemDataset2?.Item2Name,
  
              });
  
              var memoryStream = new MemoryStream();
              await memoryStream.SaveAsAsync(items);
              memoryStream.Seek(0, SeekOrigin.Begin);
  
              return new RemoteStreamContent(memoryStream, "People.xlsx", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
          }
  

  6. After running application I am getting output like

  

  7. I am getting following excel file after exporting

  

  Please do let me know if anything else is needed.

  Thanks, Anjali

  Save Cancel
• 

  0

  darutter created 8 months ago

  That works fine if there is only one element in the associated datasets. My issue is there are many items in each of the associated datasets. For example in your case, Anjali might have 3 elements in ItemSet1 and 2 elements in ItemDataset2. The results I want to get would be:

  Anjali | Plot1 | City 1 | MH | 123456 | element 1 of item1 | element 2 of item1 | element 3 of item1 | element 1 of item2 | element 2 of item2

  where the elements of each dataset would be added to the row

  Save Cancel
• 

  0

  Anjali_Musmade created 8 months ago

  Support Team Support Team Member

  Hello darutter,

  I got your point, I will check and get back to you asap.

  Thank you, Anjali

  Save Cancel
• 

  0

  Anjali_Musmade created 8 months ago

  Support Team Support Team Member

  Hello darutter,

  Please try with below example:-

  I have created first Entity as Employee -> EmployeeName, Age Second Entity as Items -> ItemName, ItemInfo I have added Navigation (manytomany) from Employee to Items.

  1. Create a DTO

   public class EmployeeExcelNewDto
      {
          public String EmplyeeName { get; set; }
  
          public int Age { get; set; }
  
          public String ItemName { get; set; }
      }
  

  2. Add code in EfCoreEmployeeRepository

   protected virtual async Task<IQueryable<EmployeeWithNavigationProperties>> GetQueryForNavigationPropertiesAsync()
          {
  
              var dbContext = await GetDbContextAsync();
              return from employee in (await GetDbSetAsync())
  
                     select new EmployeeWithNavigationProperties
                     {
                         Employee = employee,
                         Items = (from itm in dbContext.Items
                                  join empitm in employee.Items on itm.Id equals empitm.ItemId
                                  select itm).ToList()
                     };
          }
  

  3. Add in EmployeesAppService

   public virtual async Task<IRemoteStreamContent> GetListAsExcelFileAsync(EmployeeExcelDownloadDto input)
          {
              var downloadToken = await _excelDownloadTokenCache.GetAsync(input.DownloadToken);
              if (downloadToken == null || input.DownloadToken != downloadToken.Token)
              {
                  throw new AbpAuthorizationException("Invalid download token: " + input.DownloadToken);
              }
  
              var employees = await _employeeRepository.GetListWithNavigationPropertiesAsync(input.FilterText, input.EmployeeName, input.AgeMin, input.AgeMax);
              var List = new List<EmployeeExcelNewDto>();
              employees.ForEach(eitem =>
              {
                  foreach (var i in eitem.Items)
                  {
                      var emp = new EmployeeExcelNewDto
                      {
                          EmplyeeName = eitem.Employee.EmployeeName,
                          Age = eitem.Employee.Age,
                          ItemName = i.ItemName
                      };                
                      List.Add(emp);
                  };
              });
              var memoryStream = new MemoryStream();
               await memoryStream.SaveAsAsync(List);
              memoryStream.Seek(0, SeekOrigin.Begin);
              return new RemoteStreamContent(memoryStream, "Employees.xlsx", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
          }
  

  Output excel will be like

  

  Please do let me know if this helps you,

  Thank you, Anjali

  Save Cancel
• 

  0

  darutter created 7 months ago

  Thank you for your assistance.

  Save Cancel