Post
Ask Question

Merge Multiple Headers In Excel Using C#

In this blog, I am going to explain how to customize headers in Excel using C#. Excel is one of the most powerful microsoft office package which plays vital role in each office when it comes to dealing with numbers and data. In software development, we often use Excel to display and export data and reports. We can customize headers in Excel according to requirements dynamically from our code. I have also customized and merged headers in excel according to my requirements. I have also described with code and screenshot of output below,
 
First of all, we need to install "ClosedXML" dll from Mange NuGet Packages,
 
As shown in figure,
 
Merge Multiple Headers In Excel Using C#
 
Code Sample 
  1. Use Namespace:  
  2. using ClosedXML.Excel;  
This is the function for export to Excel.
  1. public void ExportToExcel() {  
  2.         SaveFileDialog saveDialog = new SaveFileDialog();  
  3.         saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";  
  4.         DialogResult result = saveDialog.ShowDialog();  
  5.         if (result == DialogResult.OK) {  
  6.             XLWorkbook workbook = new XLWorkbook();  
  7.             var ws = workbook.Worksheets.Add("MOnthly Sales Report");  
  8.             var headerRow = ws.Row(7);  
  9.             headerRow.Height = 20;  
  10.             ws.Cell("D1").Value = "Company Name";  
  11.             var titlerange = ws.Range("D1:H1");  
  12.             titlerange.Merge().Style.Font.SetBold().Font.FontSize = 13;  
  13.             ws.Cell("D1").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;  
  14.             ws.Cell(1, 7).Style.Border.BottomBorder = XLBorderStyleValues.Thick;  
  15.             ws.Cell("D2").Value = "Theater Name";  
  16.             var addressrange = ws.Range("D2:H2");  
  17.             addressrange.Merge().Style.Font.SetBold().Font.FontSize = 12;  
  18.             ws.Cell("D2").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;  
  19.             ws.Cell("D3").Value = "Kathmandu";  
  20.             var reportrange = ws.Range("D3:H3");  
  21.             reportrange.Merge().Style.Font.SetBold().Font.FontSize = 10;  
  22.             ws.Cell("D3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;  
  23.             ws.Cell("D4").Value = "MONTHLY SALES REPORT";  
  24.             var monthlyReportrange = ws.Range("D4:H4");  
  25.             monthlyReportrange.Merge().Style.Font.SetBold().Font.FontSize = 12;  
  26.             ws.Cell("D4").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;  
  27.             ws.Cell("A3").Value = "PAN: " + "12345";  
  28.             var panRange = ws.Range("A3:B3");  
  29.             panRange.Merge().Style.Font.SetBold().Font.FontSize = 10;  
  30.             ws.Cell("A3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;  
  31.             ws.Cell("A4").Value = "Month: " + "February";  
  32.             var monthRange = ws.Range("A4:C4");  
  33.             monthRange.Merge().Style.Font.SetBold().Font.FontSize = 10;  
  34.             ws.Cell("A4").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;  
  35.             ws.Cell("A5").Value = "VAT Office: " + "IRD Chabahil";  
  36.             var vatofficeRange = ws.Range("A5:C5");  
  37.             vatofficeRange.Merge().Style.Font.SetBold().Font.FontSize = 10;  
  38.             ws.Cell("A5").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;  
  39.             ws.Cell("A6").Value = "Date";  
  40.             var daterange = ws.Range("A6:A7");  
  41.             daterange.Merge().Style.Font.SetBold().Font.FontSize = 9;  
  42.             ws.Cell("A6").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;  
  43.             ws.Cell("B6").Value = "????";  
  44.             var mitirange = ws.Range("B6:B7");  
  45.             mitirange.Merge().Style.Font.SetBold().Font.FontSize = 9;  
  46.             ws.Cell("B6").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;  
  47.             string startAlpha = "C";  
  48.             string rangeAlpha = "C";  
  49.             int nextIndex = 6;  
  50.             for (int i = 0; i < 2; i++) {  
  51.                 rangeAlpha = IndexToColumn(nextIndex);  
  52.                 ws.Cell(startAlpha + "6").Value = "SCREEN: " + "Screen 1";  
  53.                 var screenrange = ws.Range(startAlpha + "6" + ":" + rangeAlpha + "6");  
  54.                 screenrange.Merge().Style.Font.SetBold().Font.FontSize = 9;  
  55.                 ws.Cell(startAlpha + "6").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;  
  56.                 ws.Cell(startAlpha + "7").Value = "Entry Fee";  
  57.                 var entryFeerange = ws.Range(startAlpha + "7" + ":" + startAlpha + "7");  
  58.                 entryFeerange.Merge().Style.Font.SetBold().Font.FontSize = 8;  
  59.                 ws.Cell(startAlpha + "7").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;  
  60.                 startAlpha = GetNextColumn(startAlpha);  
  61.                 nextIndex = nextIndex + 1;  
  62.                 ws.Cell(startAlpha + "7").Value = "VAT(13%)";  
  63.                 var vatperrange = ws.Range(startAlpha + "7" + ":" + startAlpha + "7");  
  64.                 vatperrange.Merge().Style.Font.SetBold().Font.FontSize = 8;  
  65.                 ws.Cell(startAlpha + "7").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;  
  66.                 startAlpha = GetNextColumn(startAlpha);  
  67.                 nextIndex = nextIndex + 1;  
  68.                 ws.Cell(startAlpha + "7").Value = "SLET";  
  69.                 var sletrange = ws.Range(startAlpha + "7" + ":" + startAlpha + "7");  
  70.                 sletrange.Merge().Style.Font.SetBold().Font.FontSize = 8;  
  71.                 ws.Cell(startAlpha + "7").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;  
  72.                 startAlpha = GetNextColumn(startAlpha);  
  73.                 nextIndex = nextIndex + 1;  
  74.                 ws.Cell(startAlpha + "7").Value = "Total";  
  75.                 var totalrange = ws.Range(startAlpha + "7" + ":" + startAlpha + "7");  
  76.                 totalrange.Merge().Style.Font.SetBold().Font.FontSize = 8;  
  77.                 ws.Cell(startAlpha + "7").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;  
  78.                 startAlpha = GetNextColumn(startAlpha);  
  79.                 nextIndex = nextIndex + 1;  
  80.             }  
  81.             string firstlastalpha = startAlpha;  
  82.             ws.Cell(startAlpha + "6").Value = "Total Entry Fee";  
  83.             var totalentryrange = ws.Range(startAlpha + "6" + ":" + startAlpha + "7");  
  84.             totalentryrange.Merge().Style.Font.SetBold().Font.FontSize = 9;  
  85.             ws.Cell(startAlpha + "6").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;  
  86.             ws.Cell(startAlpha + "6").Style.Alignment.WrapText = true;  
  87.             startAlpha = GetNextColumn(startAlpha);  
  88.             ws.Cell(startAlpha + "6").Value = "Total VAT";  
  89.             var totalvatrange = ws.Range(startAlpha + "6" + ":" + startAlpha + "7");  
  90.             totalvatrange.Merge().Style.Font.SetBold().Font.FontSize = 9;  
  91.             ws.Cell(startAlpha + "6").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;  
  92.             startAlpha = GetNextColumn(startAlpha);  
  93.             ws.Cell(startAlpha + "6").Value = "Total SLET";  
  94.             var totalsletrange = ws.Range(startAlpha + "6" + ":" + startAlpha + "7");  
  95.             totalsletrange.Merge().Style.Font.SetBold().Font.FontSize = 9;  
  96.             ws.Cell(startAlpha + "6").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;  
  97.             startAlpha = GetNextColumn(startAlpha);  
  98.             ws.Cell(startAlpha + "6").Value = "Remarks";  
  99.             var remarksrange = ws.Range(startAlpha + "6" + ":" + startAlpha + "7");  
  100.             remarksrange.Merge().Style.Font.SetBold().Font.FontSize = 9;  
  101.             ws.Cell(startAlpha + "6").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;  
  102.             string lastalpha = startAlpha;  
  103.             ws.Cell(firstlastalpha + "5").Value = "FOREIGN (HINDI/ENGLISH)";  
  104.             var movieTypeRange = ws.Range(firstlastalpha + "5" + ":" + lastalpha + "5");  
  105.             movieTypeRange.Merge().Style.Font.SetBold().Font.FontSize = 10;  
  106.             ws.Cell(firstlastalpha + "5").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;  
  107.             ws.Columns().Width = 10;  
  108.             ws.Rows().AdjustToContents();  
  109.             ws.Columns().AdjustToContents();  
  110.             //Setting borders to each used cell in excel  
  111.             ws.CellsUsed().Style.Border.BottomBorder = ClosedXML.Excel.XLBorderStyleValues.Thin;  
  112.             ws.CellsUsed().Style.Border.BottomBorderColor = ClosedXML.Excel.XLColor.Black;  
  113.             ws.CellsUsed().Style.Border.TopBorder = ClosedXML.Excel.XLBorderStyleValues.Thin;  
  114.             ws.CellsUsed().Style.Border.TopBorderColor = ClosedXML.Excel.XLColor.Black;  
  115.             ws.CellsUsed().Style.Border.LeftBorder = ClosedXML.Excel.XLBorderStyleValues.Thin;  
  116.             ws.CellsUsed().Style.Border.LeftBorderColor = ClosedXML.Excel.XLColor.Black;  
  117.             ws.CellsUsed().Style.Border.RightBorder = ClosedXML.Excel.XLBorderStyleValues.Thin;  
  118.             ws.CellsUsed().Style.Border.RightBorderColor = ClosedXML.Excel.XLColor.Black;  
  119.             workbook.SaveAs(saveDialog.FileName);  
  120.             MessageBox.Show("Report Generate On Excel Successfully..", "Success!", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  121.         }  
The output is,
 
Merge Multiple Headers In Excel Using C#
 

Conclusion

 
We can customize or merge headers in Excel according to client's requirements. We can customize header's row, column width, height, Font, BorderStyle etc. Hope this will help you guys.
 
Thanks...