Easy Excel Export for .Net

A common user request that never fails to annoy me is that they would like data exported to an Excel file. This bothersome process has been made far simpler in recent times with the advent of excellent tools such as GemBox and EPPlus which make the process of dealing with Excel files more like a nasty sunburn than dipping your hand into boiling oil.

In an effort to simplify this process further I have created a small extension method for IEnumberable which will dump them to an Excel file using EPPlus:

(Disclaimer: This code is a bit rough and can be improved)

public static ExcelPackage ExportAsExcelFile<T>(this IEnumerable<T> listToExport, bool includeHeaderLine)  
        {
            IList<PropertyInfo> propertyInfos = typeof(T).GetProperties();

            var package = new ExcelPackage();

            var workbook = package.Workbook;
            var workSheet = workbook.Worksheets.Add("Sheet1");

            if(includeHeaderLine)
            {
                int headerCharCounter = 65;

                foreach(var propertyInfo in propertyInfos)
                {
                    string cellAddress = String.Format("{0}{1}",
                    (char)headerCharCounter, "1");

                    if (headerCharCounter > 90)
                    {
                        int numberOfCharacters = (headerCharCounter - 65) / 26;
                        var character = (char)(headerCharCounter - (numberOfCharacters * 26));

                        string columnIdetifierCharacters = string.Empty;

                        for (int i = 0; i < numberOfCharacters; i++)
                        {
                            columnIdetifierCharacters += character;
                        }

                        cellAddress = columnIdetifierCharacters + "1";
                    }

                    workSheet.Cells[cellAddress].Value = propertyInfo.Name;
                    workSheet.Cells[cellAddress].Style.Font.Bold = true;
                    workSheet.Cells[cellAddress].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;

                    headerCharCounter++;
                }
            }

            int rowCounter = 2;
            foreach (var row in listToExport)
            {
                var localRow = row;

                var values = propertyInfos.Select(x => x.GetValue(localRow, null));

                int characterCounter = 65;

                foreach(object propertyValue in values)
                {

                    string cellAddress = String.Format("{0}{1}",
                    (char)characterCounter, rowCounter);

                    if (characterCounter > 90)
                    {
                        int numberOfCharacters = 
                                (characterCounter - 65) / 26;

                        var character = 
                        (char)(characterCounter - (numberOfCharacters * 26));

                        string columnIdetifierCharacters = string.Empty;

                        for (int i = 0; i < numberOfCharacters; i++)
                        {
                            columnIdetifierCharacters += character;
                        }

                        cellAddress = columnIdetifierCharacters + rowCounter;
                    }

                    workSheet.Cells[cellAddress].Value = propertyValue;

                    characterCounter++;
                }

                rowCounter++;
            }

            workSheet.Cells[workSheet.Dimension.Address].AutoFitColumns();
            workSheet.DataValidations.Clear();

            return package;
        }

This can be used with .Net MVC like this:

public FileResult DumpListToExcelFile<T>(IEnumerable<T> listOfObjects, string fileName, bool includeHeaderLine)  
        {
            var memoStream = new MemoryStream();

            BinaryWriter writer = new BinaryWriter(memoStream);

            writer.Write(listOfObjects.ExportAsExcelFile(includeHeaderLine).GetAsByteArray());
            writer.Flush();
            memoStream.Position = 0;

            if (!fileName.ToLower().EndsWith(".xlsx"))
            {
                fileName += ".xlsx";
            }

            return this.File(memoStream, "text/vnd.ms-excel", fileName);

        }

Please leave any comments or suggestions for improvements below!