Excel export in Multiple Tab

//tab wise excel export from dataset with multiple table

    if (yarnDS != null && yarnDS.Tables.Count > 0)
    {
        var wb = new XLWorkbook();
        var wsCom = wb.Worksheets.Add("Company Job");
        wsCom.Cell(1, 1).InsertTable(yarnDS.Tables[0].AsEnumerable());
        var wsB = wb.Worksheets.Add("Buyer Company Job");
        wsB.Cell(1, 1).InsertTable(yarnDS.Tables[1].AsEnumerable());
        var wsR = wb.Worksheets.Add("Rejected Yarns");
        wsR.Cell(1, 1).InsertTable(yarnDS.Tables[2].AsEnumerable());
        var wsAll = wb.Worksheets.Add("All Job");
        wsAll.Cell(1, 1).InsertTable(yarnDS.Tables[3].AsEnumerable());
        wb.SaveAs(fileName);

       // ExcelLibrary.DataSetHelper.CreateWorkbook(fileName, yarnDS);
        MessageBox.Show("Yarn Report Exported Successfully at \n" + fileName + "\n Please Open the Excel file for see the report.");
    }

Export to Excel using ClosedXML

//Applied in :
//DMSDestop NEW\trunk\Store\Reports\ParamForms\HR_Payroll\frmRpt_EmployeeAttendRegisterall.cs
//DMSDestop NEW\trunk\Store\Reports\ParamForms\frmRptCostingSheet.cs


string ShipDateFrom = dtpShipDateFrom.Value.ToString("yyyy/MM/dd");
string ShipDateTo = dtpShipDateTo.Value.ToString("yyyy/MM/dd");

string sp = "Rpt_CostSheetSummaryWithShipmentDate '01','" + ShipDateFrom + "','" + ShipDateTo + "'";
DataSet dtset = iManipulator.GetDataSet(sp).Copy();

//// Exporting to Excel
XLWorkbook wb = new XLWorkbook();
DataTable dt = dtset.Tables[0];
//string fileName = "Shipment Date wise CST Summary.xlsx";
string fileName = "Shipment Date wise CST Summary_" + CompanyCode + dtpShipDateFrom.Value.ToString("dd-MMM-yyyy") 
                                                + " To " + System.DateTime.Now.ToString("dd-MMM-yyyy") + ".xlsx";

//// Get path of user Downloads                           
string folderPath = "C:\\Downloads\\";

wb.Worksheets.Add(dt, "CST Summary");
wb.SaveAs(folderPath + fileName);
//var directory = Path.GetFullPath(fileName);
MessageBox.Show("Data saved in excel sheet successfully. " + "\n Path: " + folderPath + " AS " + fileName, "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
            

Export to Excel using for loop

private void btnExport_Click(object sender, EventArgs e)
{
    var companyCode = compCode.Split(',');
    var date = strtDate.Split('/');
    int year = int.Parse(date[2]);
    int month = int.Parse(date[1]);

    DataTable dt = iDBUtility.GetDataSet("[dbo].[Employee_Wise_Daily_Compliance_OT]'" + companyCode[0] + "','" + year + "','" + month + "','" + comBranchCode + "','" + unitCode + "','" + lineCode + "'").Tables[0].Copy();
    if (dt.Rows.Count > 0)
    {

        DialogResult result = MessageBox.Show("Data is going to export in Excel Sheet. ", "Information",
        MessageBoxButtons.OK, MessageBoxIcon.Information);
        if (result == DialogResult.OK)
        {
            exportToExcel(dt);
            return;
        }
    }
}


public void exportToExcel(System.Data.DataTable dt)
{
    ////==== Select file location & name for saving ====

    SaveFileDialog saveFile = new SaveFileDialog();
    saveFile.Title = "Save text Files";
    saveFile.CheckFileExists = false;
    saveFile.CheckPathExists = false;
    saveFile.FilterIndex = 2;
    saveFile.RestoreDirectory = true;
    saveFile.FileName = "Daily Compliance OT Report.xlsx";

    saveFile.Filter = "Text files (*.txt)|*.txt|All files (*.*)|*.*";
    string fileName = "";
    if (saveFile.ShowDialog() == DialogResult.OK)
    {
        fileName = saveFile.FileName;


        ///===== Bilding Data in Excel at a time ====
    DataSet ds = new DataSet();
    ds.Tables.Add(dt);
    var Workbook = new XLWorkbook();
    for (int intSheetCount = 0; intSheetCount < ds.Tables.Count; intSheetCount++)
    {
        var dataTable = ds.Tables[intSheetCount];
        var ws = Workbook.AddWorksheet(dataTable);
        ws.Name = "Compliance OT Report";
        ws.Rows(1, 1).Style.Font.Bold = true;
        //ws.Columns(1, 8).Style.Fill.BackgroundColor = XLColor.AliceBlue;
    }

    Workbook.SaveAs(fileName);
   MessageBox.Show("Data saved in excel sheet successfully. ", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
}

Import with CloseXML

//---- Check method: ReadExcelByStream() in frmMasterBuyerOrderNew in DMS Desktop for better understanding
/// Also check link:  https://www.aspsnippets.com/Articles/Read-and-import-Excel-file-data-to-DataGridView-using-ClosedXml-using-C-and-VBNet.aspx

private void btnImport_Click(object sender, EventArgs e)
{
    txtFilterSize.Text = "";
    txtFilterColor.Text = "";
   
    string filePath = string.Empty;
    string fileExt = string.Empty;
    OpenFileDialog file = new OpenFileDialog(); //open dialog to choose file  
    if (file.ShowDialog() == System.Windows.Forms.DialogResult.OK) //if there is a file choosen by the user  
    {
        filePath = file.FileName; //get the path of the file  
        fileExt = Path.GetExtension(filePath); //get the file extension  
        if (fileExt.CompareTo(".xls") == 0 || fileExt.CompareTo(".xlsx") == 0)
        {
          var dtTmpExcel = ImportExcel(filePath, fileExt);
          dataGridView1.DataSource = dtTmpExcel;
        }
    }
}

private void ImportExcel(string filePath, string fileExtension)
{
    //string filePath = openFileDialog1.FileName; 
    DataTable dtExcel = 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())
                  {
                      dtExcel.Columns.Add(cell.Value.ToString());
                  }
                  firstRow = false;
              }
              else
              {
                  //Add rows to DataTable.
                  dtExcel.Rows.Add();
                  int i = 0;
                  foreach (IXLCell cell in row.Cells())
                  {
                      dtExcel.Rows[dtExcel.Rows.Count - 1][i] = cell.Value.ToString();
                      i++;
                  }
              }
          }
      }

   return dtExcel;
}