Adding Title to worksheet using XLWorkbook class in C# .Net

Hi waabro,

Here I have created sample that will help you out.

HTML

<asp:Button ID="Button2" Text="Export" OnClick="ExportExcel" runat="server" />

Code

protected void ExportExcel(object sender, EventArgs e)
{
    var aCode = 65;
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT * FROM Customers"))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    using (XLWorkbook wb = new XLWorkbook())
                    {
                        var ws = wb.Worksheets.Add("Customers");
                        var wsReportNameHeaderRange = ws.Range(string.Format("A{0}:{1}{0}", 1, Char.ConvertFromUtf32(aCode + dt.Columns.Count)));
                        wsReportNameHeaderRange.Style.Font.Bold = true;
                        wsReportNameHeaderRange.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                        wsReportNameHeaderRange.Merge();
                        wsReportNameHeaderRange.Value = "Customer Report";
                        var wsReportDateHeaderRange = ws.Range(string.Format("A{0}:{1}{0}", 2, Char.ConvertFromUtf32(aCode + dt.Columns.Count)));
                        wsReportDateHeaderRange.Merge();
                        wsReportDateHeaderRange.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                        wsReportDateHeaderRange.Value = string.Format("Report Generated Date :{0}", DateTime.Now.ToString("dd/MM/yyyy"));
                        var wsReportCreatedByHeaderRange = ws.Range(string.Format("A{0}:{1}{0}", 3, Char.ConvertFromUtf32(aCode + dt.Columns.Count)));
                        wsReportCreatedByHeaderRange.Merge();
                        wsReportCreatedByHeaderRange.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                        wsReportCreatedByHeaderRange.Value = string.Format("Generated By: {0}", "Shashikant");
                        ws.Row(3).InsertRowsBelow(1);
                        ws.Row(4).Style.Border.OutsideBorder = XLBorderStyleValues.None;
                        ws.Row(4).Style.Border.RightBorder = XLBorderStyleValues.None;
                        ws.Row(4).Style.Border.LeftBorder = XLBorderStyleValues.None;
                        int rowIndex = 5;
                        int columnIndex = 0;
                        foreach (DataColumn column in dt.Columns)
                        {
                            ws.Cell(string.Format("{0}{1}", Char.ConvertFromUtf32(aCode + columnIndex), rowIndex)).Value = column.ColumnName;
                            ws.Cell(string.Format("{0}{1}", Char.ConvertFromUtf32(aCode + columnIndex), rowIndex)).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                            columnIndex++;
                        }
                        rowIndex++;
                        foreach (DataRow row in dt.Rows)
                        {
                            int valueCount = 0;
                            foreach (object rowValue in row.ItemArray)
                            {
                                ws.Cell(string.Format("{0}{1}", Char.ConvertFromUtf32(aCode + valueCount), rowIndex)).Value = rowValue;
                                ws.Cell(string.Format("{0}{1}", Char.ConvertFromUtf32(aCode + valueCount), rowIndex)).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                                valueCount++;
                            }
                            rowIndex++;
                        }
                        Response.Clear();
                        Response.Buffer = true;
                        Response.Charset = "";
                        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                        Response.AddHeader("content-disposition", "attachment;filename=SqlExport.xlsx");
                        using (MemoryStream MyMemoryStream = new MemoryStream())
                        {

                            wb.SaveAs(MyMemoryStream);
                            MyMemoryStream.WriteTo(Response.OutputStream);
                            Response.Flush();
                            Response.End();
                        }
                    }
                }
            }
        }
    }
}

Screenshot