Export Excel with header in c# using XLWorkbook

Question

I want to export DataTable to excel with some heading. I am using XLWorkbook for exporting to excel. My code exporting DataTable to excel perfectly but i dont know how to add some header.

I want Excel like Below

here is my c# Code

   public void ExportDataToExcel(DataTable dt, string fileName)
    {                                          
            using (XLWorkbook wb = new XLWorkbook())
            {
                wb.Worksheets.Add(dt);                    
                wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                wb.Style.Font.Bold = true;

                Response.Clear();
                Response.Buffer = true;
                Response.Charset = "";
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AddHeader("content-disposition", "attachment;filename=" + fileName + ".xlsx");

                using (MemoryStream MyMemoryStream = new MemoryStream())
                {
                    wb.SaveAs(MyMemoryStream);
                    MyMemoryStream.WriteTo(Response.OutputStream);
                    Response.Flush();
                    Response.End();
                }
            }                      
    }

Tamwe · Accepted Answer · 2015-12-04 18:03:41Z

1

This is how to merge the cells of the first row of an excel-worksheet:

    //select first row 
    Microsoft.Office.Interop.Excel.Range firstRow = Worksheet.UsedRange.Rows[1];
    //merge the cells of the first row
    firstRow.Merge();
    //set the first rows backcolor to gray
    firstRow.Interior.Color = System.Drawing.Color.Gray;enter code here
    //set the textcolor of the first row to white
    firstRow.Font.Color = System.Drawing.Color.White;
    //set the text to the center of the merged cells
    firstRow.HorizontalAlignment = XlHAlign.xlHAlignCenter;
    firstRow.VerticalAlignment = XlVAlign.xlVAlignCenter;

I hope this helps!

answered Dec 4, 2015 at 18:03

Tamwe

3492 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Chanchal Prajapat · Accepted Answer · 2015-12-02 11:36:32Z

0

Add row and merge cells according to datatable column size then add datatable rows after that.

answered Dec 2, 2015 at 11:36

Chanchal Prajapat

16 bronze badges

Comments

Ganesh Nikam · Accepted Answer · 2019-01-07 19:59:58Z

   if(callrec !=null)
               {
                 DataTable dt = new DataTable();
                  dt.Columns.AddRange(new DataColumn[7] {

                    new DataColumn("RECORDING FILE NAME",typeof(string)),
                    new DataColumn("ACCOUNT NUMBER",typeof(string)),
                    new DataColumn("CALL START TIME",typeof(string)),
                    new DataColumn("AGENT NAME",typeof(string)),
                    new DataColumn("AGENT RESULT",typeof(string)),
                    new DataColumn("DURATION SECONDS",typeof(string)),
                    new DataColumn("PHONE DIALED",typeof(string))

                });
                foreach (var item in callrec)
                {
                    dt.Rows.Add(item.RecFileName,
                        item.AccountNo == "NULL" ? "":item.AccountNo,
                       item.CallStartTime == null ? null : item.CallStartTime,
                       item.agentName=="NULL"? "":item.agentName,
                       item.agentResults=="NULL"?"":item.agentResults,
                       item.DurationSecs,
                       item.phoneDialed=="NULL"?"":item.phoneDialed
                    );
                };
                //Code For Closed XML
                using ( XLWorkbook wb= new XLWorkbook())
                {
                    var ws = wb.Worksheets.Add(dt, "CallRecordingData");
                    ws.Tables.FirstOrDefault().ShowAutoFilter = false;
                    MemoryStream stream = GetStream(wb);
                   // Response.Clear();
                   // Response.Buffer = true;
                   // Response.Charset = "";
                   // Response.ContentType = "application/vnd.openxmlformats- 
    officedocument.spreadsheetml.sheet";
                   // Response.AddHeader("content-disposition", 
       "attachment;filename=" + fileName + ".xlsx");


                }
            }

Welcome to Stackoverflow, please consider updating your answer with a summary of what your code does.

Collectives™ on Stack Overflow

Export Excel with header in c# using XLWorkbook

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related