A simple, generic Excel file exporter

A simple, generic Excel file exporter

I recently needed to write a routine that exports a query to an Excel file, and came up with a generic solution I thought might be helpful to share. The code is in an AspNetCore project, but the same principles would work in other .NET scenarios.

Here, fileBytes is the result from my export. By supplying the correct MIME type and a filename, the Angular web app downloading this ends up just dropping a nicely named Excel file in your Downloads folder.

Going back to the method declaration, the first parameter is a List<T> of data rows. You can pass in whatever list of data you want. In my case, I am passing the result of an EntityFrameworkCore query:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

vardata=await(

fromrin_tripRepo.Context.Routes

jointin_tripRepo.Context.Trips onr.Id equalst.Route.Id

joinsin_tripRepo.Context.Stops ont.Id equalss.TripId

joinain_tripRepo.Context.Activities ons.Id equalsa.StopId

wherer.TenantId==CurrentTenantId&&t.Date==localDate

orderbyr.Name,s.Sequence

selectnew

{

RouteName=r.Name,

TripDate=t.Date,

t.EstimatedMiles,

t.EstimatedTime,

t.EstimatedTrafficTime,

t.EstimatedTravelTime,

s.Sequence,

s.EstimatedArrival,

s.EstimatedDeparture,

LocationName=s.Location.Name,

LocationTypeName=s.Location.LocationType.Name,

s.Location.Address.StreetAddress1,

s.Location.Address.City,

s.Location.Address.PostalCodeZIP,

State=s.Location.Address.State.ShortName,

s.Location.ServiceWindowStartHHMM,

s.Location.ServiceWindowEndHHMM,

ActivityTypeName=a.ActivityType.Name,

a.DurationMinutes

}

).ToListAsync();

You’ll notice that the select statement is using an anonymous type (new { …. ). I can still pass the list to my exporter func… I dont have to declare the type in code. In the anonymous type, some of the assignments specify a property name, for instance TripDate = t.Date, others don’t, for instance t.EstimatedTravelTime. When a name is specified, the anonymous type uses that property name, otherwise the assigned identifier.

The generic exporter will use these property names as the headers in the first row of the Excel file, but I also allow you to supply a custom list of headers to use in the second optional parameter of the call. The exporter will write the values of each property to the file, using whatever default formatting that gives you. If you want to be in more control, you can specify a function that does the formatting. In my code, I supply both:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

varfileBytes=_fp.WriteToExcel(

data,

new[]

{

"Route",

"Date",

"Est. Miles",

"Est. Time",

"Est. Traffic Time",

"Est. Travel Time",

"Sequence",

"Est. Arrival",

"Est. Departure",

"Location",

"Location Type",

"Street Address",

"City",

"ZIP",

"State",

"Service Window Start",

"Service Window End",

"Activity Type",

"Duration (mins)"

},

r=>newobject[]

{

r.RouteName,

r.TripDate.ToShortDateString(),

$"{r.EstimatedMiles:F1}",

FormatTime(r.EstimatedTime),

FormatTime(r.EstimatedTrafficTime),

FormatTime(r.EstimatedTravelTime),

r.Sequence,

r.EstimatedArrival?.LocalDateTime.ToShortTimeString(),

r.EstimatedDeparture?.LocalDateTime.ToShortTimeString(),

r.LocationName,

r.LocationTypeName,

r.StreetAddress1,

r.City,

r.PostalCodeZIP,

r.State,

r.ServiceWindowStartHHMM,

r.ServiceWindowEndHHMM,

r.ActivityTypeName,

r.DurationMinutes

});

Here, FormatTime is a local function that converts a nullable Double representing time in seconds to a more pleasant hours and minutes string:

1

2

stringFormatTime(double?seconds)=>

!seconds.HasValue?"":$"{(int)seconds/3600}h {(int)seconds/60%60}m";

The result of this formatting is an Excel file that looks something like this:

OK – now that we have seen the signature of the method, what I pass in, and the results… let’s delve into the magic in the black box!

This package makes reading and writing Excel (XLSX) files a breeze. The first few rows of code in this method just create a new Worksheet called “Export”. I use a MemoryStream as the ‘backing storage’ for writing the file in memory. The rows and columns both start at 1, so I set that up.

1

2

3

4

5

6

7

8

9

10

11

12

publicbyte[]WriteToExcel<T>(

List<T>rows,

string[]headers=null,

Func<T,object[]>formatterFunc=null)

{

using(varms=newMemoryStream())

{

using(varp=newExcelPackage(ms))

{

varrow=1;

varcol=1;

varws=p.Workbook.Worksheets.Add("Export");

Now I need to write the header row. If I supplied headers, I use those, otherwise I get the names of the properties of the data type using typeof(T).GetProperties(), and pis.Select( pi => pi.Name).

1

2

3

4

5

6

varpis=typeof(T).GetProperties();

foreach(varhinheaders??pis.Select(pi=>pi.Name).ToArray())

{

ws.Cells[row,col++].Value=h;

}

Now I need to iterate over the data and write it out. For each row, I either use the raw property values, using pis.Select( pi => pi.GetValue(0)), or I pass the row to the supplied formatter function, which returns an array of objects to use instead.

1

2

3

4

5

6

7

8

9

10

11

foreach(varoinrows)

{

row++;

col=1;

varvalues=formatterFunc==null?

pis.Select(pi=>pi.GetValue(o)):formatterFunc(o);

foreach(varvinvalues)

{

ws.Cells[row,col++].Value=v;

}

}

Finally, I Auto Fit all the columns to the data in them, and return the file contents as an array of bytes: