Open Closed

Exporting to excel #5698


User avatar
0
darutter created
  • ABP Framework version: v7.3.2
  • UI Type: MVC
  • Database System: EF Core (SQL Server
  • Tiered (for MVC) or Auth Server Separated (for Angular): no

I have a DTO that has a main set of data and each "row" of that data set has two additional sets of data similar to this: Person (main data set) Itemset1 (first associated data set) Itemset2 (second associated data set) The resulting excel spreadsheet should look like this: Bill Jones | address1 | city | state | postalcode Item1Elem1Name | Item1Elem1other data Item1Elem2Name | Item1Elem2other data Item2Elem1Name | Item2Elem1otherdata . . etc.

I need to export this data to Excel and can't figure out how to accomplish it. I can't find any example of how to accomplish this. Can you provide direction?


5 Answer(s)
  • User Avatar
    0
    Anjali_Musmade created
    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");
            }
    
    1. After running application I am getting output like

    1. I am getting following excel file after exporting

    Please do let me know if anything else is needed.

    Thanks, Anjali

  • User Avatar
    0
    darutter created

    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

  • User Avatar
    0
    Anjali_Musmade created
    Support Team Support Team Member

    Hello darutter,

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

    Thank you, Anjali

  • User Avatar
    0
    Anjali_Musmade created
    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; }
        }
    
    1. 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()
                       };
            }
    
    1. 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

  • User Avatar
    0
    darutter created

    Thank you for your assistance.

Made with ❤️ on ABP v9.1.0-preview. Updated on December 10, 2024, 06:38