Styjun

This project is an ASP.Net Api project with Angular. What I'm trying to do is export data from a database table and into an excel file. So far, I've managed to export all the table data into an excel file, but struggle to select 2 or 3 fields in the table to export.

 [HttpGet("download")]
 public IActionResult DownloadExcel(string field)
 
 string dbFileName = "DbTableName.xlsx";


 FileInfo file = new FileInfo(dbFileName);
 byte[] fileContents;

 var stream = new MemoryStream();
 using (ExcelPackage package = new ExcelPackage(file))
 

 IList<UserTable> userList = _context.UserTable.ToList();

 ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("DbTableName");
 int totalUserRows = userList.Count();


 


 return File(fileContents, fileType, dbFileName);
 

There's no need to write so many if ... else if ... else if ... else if ... to get the related field names.

A nicer way is to

1. Use a field list (IList<string>)as a parameter.


2. And then generate a required field list by intersect.


3. Finally, we could use reflection to retrieve all the related values.

Implementation

 public IActionResult DownloadExcel(IList<string> fields)
 
 // get the required field list
 var userType = typeof(UserTable);
 fields = userType.GetProperties().Select(p => p.Name).Intersect(fields).ToList();

 if(fields.Count == 0) return BadRequest(); 

 using (ExcelPackage package = new ExcelPackage())
 
 IList<UserTable> userList = _context.UserTable.ToList();
 ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("DbTableName");

 // generate header line
 for(var i= 0; i< fields.Count; i++ )
 var fieldName = fields[i];
 var pi= userType.GetProperty(fieldName);
 var displayName = pi.GetCustomAttribute<DisplayNameAttribute>()?.DisplayName;
 worksheet.Cells[1,i+1].Value = string.IsNullOrEmpty(displayName ) ? fieldName : displayName ;
 

 // generate row lines
 int totalUserRows = userList.Count();
 for(var r=0; r< userList.Count(); r++)
 var row = userList[r];
 for(var c=0 ; c< fields.Count;c++)
 var fieldName = fields[c];
 var pi = userType.GetProperty(fieldName);
 // because the first row is header 
 worksheet.Cells[r+2, c+1].Value = pi.GetValue(row);
 
 
 var stream = new MemoryStream(package.GetAsByteArray());
 return new FileStreamResult(stream,"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
 
 

You could configure the display name using the DsiplayNameAttribute:

 public class UserTable
 
 public int Idget;set;

 [DisplayName("First Name")]
 public string fName get; set; 

 [DisplayName("Last Name")]
 public string lName get; set; 

 [DisplayName("Gender")]
 public string gender get; set; 

 

It's possible to add any properties as you like without hard-coding in your DownloadExcel method.

Demo :

passing a field list fields[0]=fName&fields[1]=lName&fields[2]=Non-Exist will generate an excel as below:

[Update]

To export all the fields, we could assume the client will not pass a fields parameter. That means when the fields is null or if the fields.Count==0, we'll export all the fields:

 [HttpGet("download")]
 public IActionResult DownloadExcel(IList<string> fields)
 
 // get the required field list
 var userType = typeof(UserTable);
 var pis= userType.GetProperties().Select(p => p.Name);

 if(fields?.Count >0)
 fields = pis.Intersect(fields).ToList();
 else
 fields = pis.ToList();
 

 using (ExcelPackage package = new ExcelPackage())
 ....
 
 

if you want to use the datatable then we can define which you need to select from the datatable in this way

string[] selectedColumns = new[] "Column1","Column2";
DataTable dt= new DataView(fromDataTable).ToTable(false, selectedColumns);

or else if you wanna you list then you can use linq for selection of particular columns

var xyz = from a in prod.Categories
 where a.CatName.EndsWith("A")
 select new CatName=a.CatName, CatID=a.CatID, CatQty = a.CatQty;