Return ClosedXML Excel file from ASP.NET MVC controller

<form class="form-horizontal" id="form-create-employee" method="post" action="~/Admin/Home/ExcelReport">
            <input type="text" name="fromdate" placeholder="from date" />
            <input type="text" name="todate" placeholder="to date" />
            <input type="submit" value="Download" class="btn btn-info btn-lg"/>
        </form>
public ActionResult ExcelReport()
        {
            DataTable dtData = new DataTable();
            string fileName = Guid.NewGuid().ToString();
            String reportHeader = "This is a header.";
            try
            {
                String conString = WebConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
                string sql = "";
                using (SqlConnection connection = new SqlConnection(conString))
                {
                    using (SqlCommand command = new SqlCommand(sql, connection))
                    {
                        using (SqlDataAdapter da = new SqlDataAdapter(command))
                        {
                            connection.Open();
                            da.Fill(dtData); connection.Close();
                        }
                    }
                }
 
                //More details- http://closedxml.codeplex.com/
                var MyWorkBook = new XLWorkbook();
                var MyWorkSheet = MyWorkBook.Worksheets.Add("Sheet 1");
                int TotalColumns = dtData.Columns.Count;
 
                //-->headline
                //first row is intentionaly left blank.
                var headLine = MyWorkSheet.Range(MyWorkSheet.Cell(2, 1).Address, MyWorkSheet.Cell(2, TotalColumns).Address);
                headLine.Style.Font.Bold = true;
                headLine.Style.Font.FontSize = 15;
                headLine.Style.Font.FontColor = XLColor.White;
                headLine.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                headLine.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                headLine.Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.25);
                headLine.Style.Border.TopBorder = XLBorderStyleValues.Medium;
                headLine.Style.Border.BottomBorder = XLBorderStyleValues.Medium;
                headLine.Style.Border.LeftBorder = XLBorderStyleValues.Medium;
                headLine.Style.Border.RightBorder = XLBorderStyleValues.Medium;
 
                headLine.Merge();
                headLine.Value = reportHeader;
                //<-- headline
 
                //--> column settings
                for (int i = 1; i < dtData.Columns.Count + 1; i++)
                {
                    String combinedHeaderText = dtData.Columns[i - 1].ColumnName.ToString();
                    string separatedColumnHeader = "";
                    foreach (char letter in combinedHeaderText)
                    {
                        if (Char.IsUpper(letter) && separatedColumnHeader.Length > 0)
                            separatedColumnHeader += " " + letter;
                        else
                            separatedColumnHeader += letter;
                    }
                    MyWorkSheet.Cell(4, i).Value = separatedColumnHeader;
                    MyWorkSheet.Cell(4, i).Style.Alignment.WrapText = true;
                }
 
                var columnRange = MyWorkSheet.Range(MyWorkSheet.Cell(4, 1).Address, MyWorkSheet.Cell(4, TotalColumns).Address);
                columnRange.Style.Font.Bold = true;
                columnRange.Style.Font.FontSize = 10;
                columnRange.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                columnRange.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                columnRange.Style.Fill.BackgroundColor = XLColor.FromArgb(171, 195, 223); 
                columnRange.Style.Border.TopBorder = XLBorderStyleValues.Thin;
                columnRange.Style.Border.BottomBorder = XLBorderStyleValues.Thin;
                columnRange.Style.Border.LeftBorder = XLBorderStyleValues.Thin;
                columnRange.Style.Border.RightBorder = XLBorderStyleValues.Thin;
                //<-- column settings
 
                //--> row data & settings
                for (int i = 0; i < dtData.Rows.Count; i++)
                {
                    DataRow row = dtData.Rows[i];
                    for (int j = 0; j < dtData.Columns.Count; j++)
                    {
                        MyWorkSheet.Cell(i + 5, j + 1).Value = row[j].ToString();
                    }
                }
 
                var dataRowRange = MyWorkSheet.Range(MyWorkSheet.Cell(5, 1).Address, MyWorkSheet.Cell(dtData.Rows.Count + 4, TotalColumns).Address);
                dataRowRange.Style.Font.Bold = false;
                dataRowRange.Style.Font.FontSize = 10;
                dataRowRange.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                dataRowRange.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                dataRowRange.Style.Fill.BackgroundColor = XLColor.FromArgb(219,229,241);
                dataRowRange.Style.Border.TopBorder = XLBorderStyleValues.Thin;
                dataRowRange.Style.Border.BottomBorder = XLBorderStyleValues.Thin;
                dataRowRange.Style.Border.LeftBorder = XLBorderStyleValues.Thin;
                dataRowRange.Style.Border.RightBorder = XLBorderStyleValues.Thin;
                //<-- row data & settings
 
                // Prepare the response
                Response.Clear();
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AddHeader("content-disposition", "attachment;filename=\""+ reportHeader +".xlsx\"");
 
                // Flush the workbook to the Response.OutputStream
                using (MemoryStream memoryStream = new MemoryStream())
                {
                    MyWorkBook.SaveAs(memoryStream);
                    memoryStream.WriteTo(Response.OutputStream);
                    memoryStream.Close();
                }
 
                Response.End();
                return View();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


Advertisements

Create ClosedXML Excel

using ClosedXML;
using ClosedXML.Excel;
using System.IO;
        private void button1_Click(object sender, EventArgs e)
        {
            var workbook = new XLWorkbook();
            var ws = workbook.Worksheets.Add("Merge Cells");

            DateTime fromDate = new DateTime(2015, 03, 20);
            DateTime toDate = new DateTime(2015, 03, 30);
            Int32 columnPosition = 2;
            for (DateTime date = fromDate; date <= toDate; date = date.AddDays(1))
            {
                var range = ws.Range(ws.Cell(1, columnPosition).Address, ws.Cell(1, columnPosition+1).Address);
                range.Cell(1, 1).Value = date.ToString("yyyy-MM-dd");
                range.Merge();
                range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                range.Style.Fill.BackgroundColor = XLColor.Almond;

                var firstCell = ws.Cell(2, columnPosition);
                firstCell.Value = "1st"; firstCell.Style.Fill.BackgroundColor = XLColor.Amethyst;
                var secondCell = ws.Cell(2, columnPosition + 1);
                secondCell.Value = "2nd"; secondCell.Style.Fill.BackgroundColor = XLColor.Amethyst;

                columnPosition += 2;
              
            }

          
            workbook.SaveAs("MergeCells.xlsx");
        }

Wokring with ClosedXML

//Path to the existing excel file (if console app)
string path = System.Reflection.Assembly.GetExecutingAssembly().Location;
var directory = System.IO.Path.GetDirectoryName(path);
//Path if MVC
//var directory = HttpContext.Server.MapPath("~/ExcelFiles");


//Get excel file
var excelFile = new XLWorkbook(Path.Combine(directory,  "Book1.xlsx")); 
//Define the worksheet
var sheet = excelFile.Worksheet(1);
//Get cell value
var cellValue = sheet.Cell(1, 1).Value;
//Get last row position with data
String row = sheet.LastRowUsed().RowNumber().ToString();