/// <summary> /// Creates an Excel document from any IEnumerable returns a memory stream /// </summary> /// <param name="rows">IEnumerable that will be converted into an Excel worksheet</param> /// <param name="sheetName">Name of the Ecel Sheet</param> /// <returns></returns> public static FileStreamResult ToExcel(this IEnumerable <object> rows, string sheetName) { // Create a new workbook and a sheet named by the sheetName variable var workbook = new HSSFWorkbook(); var sheet = workbook.CreateSheet(sheetName); //these indexes will be used to track to coordinates of data in our IEnumerable var rowIndex = 0; var cellIndex = 0; var excelRow = sheet.CreateRow(rowIndex); //Get a collection of names for the header by grabbing the name field of the display attribute var headerRow = from p in rows.First().GetType().GetProperties() select rows.First().GetAttributeFrom <DisplayAttribute>(p.Name).Name; //Add headers to the file foreach (string header in headerRow) { excelRow.CreateCell(cellIndex).SetCellValue(header); cellIndex++; } //reset the cells and go to the next row cellIndex = 0; rowIndex++; //Inset the data row foreach (var contentRow in rows) { excelRow = sheet.CreateRow(rowIndex); var Properties = rows.First().GetType().GetProperties(); //Go through each property and inset it into a single cell foreach (var property in Properties) { var cell = excelRow.CreateCell(cellIndex); var value = property.GetValue(contentRow); if (value != null) { var dataType = value.GetType(); //Set the type of excel cell for different data types if (dataType == typeof(int) || dataType == typeof(double) || dataType == typeof(decimal) || dataType == typeof(float) || dataType == typeof(long)) { cell.SetCellType(CellType.NUMERIC); cell.SetCellValue(Convert.ToDouble(value)); } if (dataType == typeof(bool)) { cell.SetCellType(CellType.BOOLEAN); cell.SetCellValue(Convert.ToDouble(value)); } else { cell.SetCellValue(value.ToString()); } } cellIndex++; } cellIndex = 0; rowIndex++; } //Set the width of the columns foreach (string header in headerRow) { sheet.AutoSizeColumn(cellIndex); cellIndex++; } return(workbook.GetDownload(sheetName)); }