ClosedXml Excel Process Examples C#

Introduction

Example of Read an Excel file into a DataTable and Process Datatable. Finally SaveDatatable into excel file. 

SAMPLE CODE

using ClosedXML.Excel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

public class ClosedXmlExcelProcess
{
    public bool GenerateOutput(DataTable  dt, string OutputFilePath)
    {
       return  ProcessOutput(dt, OutputFilePath);
    }
    public bool GenerateOutput(string InputFilePath, string OutputFilePath)
    {
        DataTable DtInput = ProcessInputFile(InputFilePath);
        return ProcessOutput(DtInput, OutputFilePath);
    }
    private bool ProcessOutput(DataTable DtInput, string OutputFilePath)
    {

     

        if (DtInput.Rows.Count <= 0 || DtInput.Columns.Count < 8)
        {
            return false;
        }

        //Get Campaign Name from DataTable
        string _CampaignColumnName = DtInput.Columns[2].ColumnName;
        string _GroupColumnName = DtInput.Columns[3].ColumnName;
        string _ClickColumnName = DtInput.Columns[4].ColumnName;
        string _SalaryColumnName = DtInput.Columns[6].ColumnName;


        //Get Distinct Column Values of Campaign
        DataTable mDistinctdt = DtInput.DefaultView.ToTable(true, _CampaignColumnName);
        if (mDistinctdt.Rows.Count > 0)
        {
            List<string> lstCampaignName = mDistinctdt.AsEnumerable().Select(r => r.Field<string>(_CampaignColumnName)).ToList();
            if (lstCampaignName.Count > 0)
            {

                XLWorkbook wb = new XLWorkbook();

                foreach (string CampaignVal in lstCampaignName)
                {
                    DataView vwFilter = DtInput.DefaultView;
                    vwFilter.RowFilter = "[" + _CampaignColumnName + "] =  '" + CampaignVal + "'";
                    DataTable dtFilteredRows = vwFilter.ToTable();


                    //Filter Value like Excel Pivot Table using Linq
                    List<OutputResult> lstCampaignSheet = new List<OutputResult>();

                    lstCampaignSheet = dtFilteredRows.AsEnumerable()
                          .Select(x =>
                           new
                           {
                               GroupName = x[_GroupColumnName],
                               ClickCount = x[_ClickColumnName],
                               Salary = x[_SalaryColumnName]
                           }
                         )
                         .GroupBy(s => new { s.GroupName })
                         .Select(g =>
                               new OutputResult
                               {
                                   AddGroupView = g.Key.GroupName.ToString(),
                                   Clicks = g.Sum(x => Convert.ToInt32(x.ClickCount)),
                                   Salary = g.Sum(x => Convert.ToInt32(x.Salary)),
                               }
                         ).ToList();


                    IXLWorksheet ws = wb.Worksheets.Add(CampaignVal);

                    ws.Cell(1, 1).Value = "Add Group View";
                    ws.Cell(1, 2).Value = "Clicks";
                    ws.Cell(1, 3).Value = "Views";
                    ws.Cell(1, 4).Value = "CR";

                    ws.Cell(1, 1).Style.Fill.BackgroundColor = XLColor.Yellow;

                    lstCampaignSheet = lstCampaignSheet.Where(d => d.Clicks > 0).OrderByDescending(d => d.CR).ToList();
                    IXLRange range = ws.Cell(2, 1).InsertData(lstCampaignSheet.AsEnumerable());

                    ws.Range(range.FirstRow().RangeAddress.LastAddress, range.LastCellUsed().Address).AddConditionalFormat().WhenEqualOrGreaterThan(6)
                    .Fill.SetBackgroundColor(XLColor.Red);
                    ws.Range(range.FirstRow().RangeAddress.LastAddress, range.LastCellUsed().Address).AddConditionalFormat().WhenEqualOrLessThan(6)
                    .Fill.SetBackgroundColor(XLColor.Yellow);

                    ws.Columns().AdjustToContents();
                }

                wb.SaveAs(OutputFilePath);
            }
        }

        return true;
    }
    private DataTable ProcessInputFile(string filePath)
    {
        //Create a new DataTable.
        DataTable dt = new DataTable();


        //Open the Excel file using ClosedXML.
        using (XLWorkbook workBook = new XLWorkbook(filePath))
        {
            //Read the first Sheet from Excel file.
            IXLWorksheet workSheet = workBook.Worksheet(1);


            //Loop through the Worksheet rows.
            bool firstRow = true;
            foreach (IXLRow row in workSheet.Rows())
            {
                //Use the first row to add columns to DataTable.
                if (firstRow)
                {
                    foreach (IXLCell cell in row.Cells())
                    {
                        dt.Columns.Add(cell.Value.ToString());
                    }
                    firstRow = false;
                }
                else
                {
                    //Add rows to DataTable.
                    dt.Rows.Add();
                    int i = 0;
                    foreach (IXLCell cell in row.Cells())
                    {
                        dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
                        i++;
                    }
                }
            }
        }

        return dt;
    }
}
public class OutputResult
{
    public string AddGroupView { get; set; }
    public int Clicks { get; set; }
    public int Salary { get; set; }
     public decimal CR { get { return Salary / Clicks; } }
}