Can my excel to excel data copy code with some with data manipulation be more efficient?

Question

I'm trying to copy data from a worksheet to another worksheet with some modifications made on the fly. Here are the details of the stuff I'm trying to do:

1. Open the file from where I'm going to copy data from (xlsPath) using OpenFileDialog

2. Check the sheet's cell value V6. If it is empty then write something e.g. "na"

3. Open the destination worksheet (if already exists) or create a new one (dbFilePath)

4. Get the last filled row of both the worksheets

5. Set some cell formatting in both the sheets

6. Then copy data from xlsPath to dbFilePath. Some columns data are just simply copied but the first column data in the xlsPath is modified according to what the cell V6 in that worksheet contains

7. Then in the destination worksheet dbFilePath a search & replace is performed to remove the word "M/s", then all the cells are checked for leading or trailing whitespaces and performed a Trim.

8. Then the destination worksheet is checked for duplicate rows and removed them if any.

9. Then the destination worksheet is checked for near duplicate rows i.e. row where the values from column 1 to column 5 are same just the value in column 6 and onwards is different, then in that case find those near identical rows (if any) and then replace them with only one row where the values from column 1 to column 5 are same as before just the value in column 6 is the sum of all the column 6 values of those near identical rows.

10. Finally do a autofit column, sort and save.

Here's my code

private void button2_Click(object sender, EventArgs e)
{
                string xlsPath = "";
                string btnTxt = btnUpdate.Text;
                var err = new List<string>();
                    comboBox1.Text = "";

                    OpenFileDialog openFileDialog1 = new OpenFileDialog
                    {
                            InitialDirectory = @"D:\",
                            Title = "Browse Excel Files",

                            CheckFileExists = true,
                            CheckPathExists = true,

                            DefaultExt = "xlsx",
                            Filter = "Excel files (*.xlsx)|*.xlsx",
                            FilterIndex = 2,
                            RestoreDirectory = true,

                            ReadOnlyChecked = true,
                            ShowReadOnly = true
                    };

                    btnUpdate.Text = "Processing...";

                    if (openFileDialog1.ShowDialog() == DialogResult.OK)
                    {
                            xlsPath = openFileDialog1.FileName;


                            Workbook workbook = new Workbook();

                            workbook.LoadFromFile(xlsPath);
                            Worksheet sheet = workbook.Worksheets[0];

                            //check the value of the cell V6

                            if (sheet.Range["V6"].Value2 == "")
                            {
                                    sheet.Range["V6"].Text = "na";
                            }

                            
                            //check if the destination file already exists or not. If not create one in the else statement

                            if (File.Exists(dbFilePath))

                            {
                                    Workbook newBook = new Workbook();
                                    newBook.LoadFromFile(dbFilePath);
                                    Worksheet newSheet = newBook.Worksheets[0];

                                    //find the last filled rows of both the worksheets

                                    int lFilledRow = newSheet.LastRow;
                                    for (int j = newSheet.LastRow; j >= 0; j--)
                                    {
                                            CellRange lcr = newSheet.Rows[j - 1].Columns[1];
                                            if (!lcr.IsBlank)
                                            {
                                                    lFilledRow = j + 1;
                                                    break;
                                            }
                                    }
                                    int lastFilledRow = sheet.LastRow;
                                    for (int i = sheet.LastRow; i >= 0; i--)
                                    {
                                            CellRange cr = sheet.Rows[i - 1].Columns[1];
                                            if (!cr.IsBlank)
                                            {
                                                    lastFilledRow = i;
                                                    break;
                                            }
                                    }


                                    //set some number formatting in the source sheet

                                    sheet.Range["B5:B" + lastFilledRow].NumberFormat = "dd-MM-yyyy";
                                    sheet.Range["N5:N" + lastFilledRow].NumberFormat = "dd-MM-yyyy";
                                    sheet.Range["W5:W" + lastFilledRow].NumberFormat = "0.00";


                                    //replace the first column values depending on the cell V6 value i.e. if cell V6 starts with "RAW MATERIAL"
                                    //then change all the rows column 1 value from something like '1' to 'RM-2021-22/1'
                                    //if it starts with "PACKING MATERIAL" then change to 'PM-2021-22/1' & so on
                                    //----------------------------------------------------------------------
                                    if (sheet.Range["V6"].Text.StartsWith("RAW MATERIAL"))
                                    {
                                            for (int y = 6; y <= lastFilledRow; y++)
                                            {
                                                    string myFormula = "=IF(MONTH($B" + y + ")>3,YEAR($B" + y + ")&\"-\"&RIGHT((YEAR($B" + y + ")+1),2), YEAR($B" + y + ")-1&\"-\"&RIGHT(YEAR($B" + y + "),2)";

                                                    object formulaResult = workbook.CaculateFormulaValue(myFormula);

                                                    string value = "RM-" + formulaResult.ToString() + "/" + sheet.Range[y, 1].Value.ToString();

                                                    sheet.Range[y, 1].Text = value;
                                            }

                                    }

                                    else if (sheet.Range["V6"].Text.StartsWith("PACKING MATERIAL"))
                                    {
                                            for (int y = 6; y <= lastFilledRow; y++)
                                            {
                                                    string myFormula = "=IF(MONTH($B" + y + ")>3,YEAR($B" + y + ")&\"-\"&RIGHT((YEAR($B" + y + ")+1),2), YEAR($B" + y + ")-1&\"-\"&RIGHT(YEAR($B" + y + "),2)";

                                                    object formulaResult = workbook.CaculateFormulaValue(myFormula);

                                                    string value = "PM-" + formulaResult.ToString() + "/" + sheet.Range[y, 1].Value.ToString();

                                                    sheet.Range[y, 1].Text = value;
                                            }
                                    }

                                    else if (sheet.Range["V6"].Text.StartsWith("PURCHASE"))
                                    {
                                            for (int y = 6; y <= lastFilledRow; y++)
                                            {
                                                    string myFormula = "=IF(MONTH($B" + y + ")>3,YEAR($B" + y + ")&\"-\"&RIGHT((YEAR($B" + y + ")+1),2), YEAR($B" + y + ")-1&\"-\"&RIGHT(YEAR($B" + y + "),2)";

                                                    object formulaResult = workbook.CaculateFormulaValue(myFormula);

                                                    string value = "FG-" + formulaResult.ToString() + "/" + sheet.Range[y, 1].Value.ToString();

                                                    sheet.Range[y, 1].Text = value;
                                            }
                                    }


                                    else
                                    {
                                            for (int y = 6; y <= lastFilledRow; y++)
                                            {
                                                    string myFormula = "=IF(MONTH($B" + y + ")>3,YEAR($B" + y + ")&\"-\"&RIGHT((YEAR($B" + y + ")+1),2), YEAR($B" + y + ")-1&\"-\"&RIGHT(YEAR($B" + y + "),2)";

                                                    object formulaResult = workbook.CaculateFormulaValue(myFormula);

                                                    string value = "OTH-" + formulaResult.ToString() + "/" + sheet.Range[y, 1].Value.ToString();

                                                    sheet.Range[y, 1].Text = value;
                                            }
                                    }
                                    //----------------------------------------------------------------------
                                    //simply copy data from one worksheet to another
                                    
                                    sheet.Copy(sheet.Range["B6:B" + lastFilledRow], newSheet.Range["B" + lFilledRow], true);
                                    sheet.Copy(sheet.Range["A6:A" + lastFilledRow], newSheet.Range["A" + lFilledRow], true);
                                    sheet.Copy(sheet.Range["D6:D" + lastFilledRow], newSheet.Range["C" + lFilledRow], true);
                                    sheet.Copy(sheet.Range["K6:K" + lastFilledRow], newSheet.Range["D" + lFilledRow], true);
                                    sheet.Copy(sheet.Range["M6:M" + lastFilledRow], newSheet.Range["E" + lFilledRow], true);
                                    sheet.Copy(sheet.Range["N6:N" + lastFilledRow], newSheet.Range["F" + lFilledRow], true);
                                    sheet.Copy(sheet.Range["W6:W" + lastFilledRow], newSheet.Range["G" + lFilledRow], true);

                                    //set some number formatting in the destination sheet

                                    newSheet.Range["G2:G" + lastFilledRow].NumberFormat = "0.00";

                                    //Remove the word "M/S"

                                    CellRange[] ranges = newSheet.FindAllString("M/S", false, false);

                                    foreach (CellRange range in ranges)
                                    {
                                            range.Text = range.Text.Replace("M/S", "");
                                    }


                                    //trim leading & trailing whitespaces

                                    for (int i = 1; i <= newSheet.LastRow; i++)
                                    {
                                            for (int j = 1; j <= newSheet.LastColumn; j++)
                                            {
                                                    CellRange range = newSheet.Range[i, j];
                                                    String str = range.Value;
                                                    String strings = str.Trim();
                                                    range.Value = strings;
                                            }
                                    }


                                    //remove duplicate rows
                                    //----------------------------------------------------------------------
                                    for (int i = 0; i < newSheet.Columns[0].Count - 1; i++)
                                    {
                                            for (int j = i + 1; j < newSheet.Columns[0].Count; j++)
                                            {
                                                    if (newSheet.Columns[0].CellList[i].Value == newSheet.Columns[0].CellList[j].Value)
                                                    {
                                                            for (int col = 0; col < newSheet.Rows[0].Count; col++)
                                                            {
                                                                    if (col == newSheet.Rows[0].Count - 1 && newSheet.Rows[i].CellList[col].Value == newSheet.Rows[j].CellList[col].Value)
                                                                    {
                                                                            newSheet.DeleteRow(j + 1);
                                                                            j--;
                                                                    }
                                                                    else if (col != newSheet.Rows[0].Count - 1 && newSheet.Rows[i].CellList[col].Value == newSheet.Rows[j].CellList[col].Value)
                                                                    {
                                                                            continue;
                                                                    }
                                                                    else
                                                                    {
                                                                            break;
                                                                    }
                                                            }
                                                    }
                                            }
                                    }
                                    //----------------------------------------------------------------------


                                    //sort & autofit columns and save the file

                                    newSheet.AllocatedRange.AutoFitColumns();

                                    newBook.DataSorter.SortColumns.Add(1, OrderBy.Ascending);
                                    newBook.DataSorter.Sort(newSheet.AllocatedRange);

                                    newBook.SaveToFile(dbFilePath, ExcelVersion.Version2013);

                                    newBook.Dispose();
                            }
                            else
                            {       //same as in the above if statement
                                    //----------------------------------------------------------------------
                                    Workbook newBook = new Workbook();
                                    Worksheet newSheet = newBook.Worksheets[0];

                                    int lastFilledRow = sheet.LastRow;
                                    for (int i = sheet.LastRow; i >= 0; i--)
                                    {
                                            CellRange cr = sheet.Rows[i - 1].Columns[1];
                                            if (!cr.IsBlank)
                                            {
                                                    lastFilledRow = i;
                                                    break;
                                            }
                                    }

                                    newSheet.Range[1, 1].Value = "BP No./REF.No.";
                                    newSheet.Range[1, 2].Value = "BP Date";
                                    newSheet.Range[1, 3].Value = "NAME";
                                    newSheet.Range[1, 4].Value = "GSTIN";
                                    newSheet.Range[1, 5].Value = "Bill No.";
                                    newSheet.Range[1, 6].Value = "Bill Date";
                                    newSheet.Range[1, 7].Value = "Bill Value";
                                    newSheet.Range[1, 8].Value = "Remarks";

                                    sheet.Range["B5:B" + lastFilledRow].NumberFormat = "dd-MM-yyyy";
                                    sheet.Range["N5:N" + lastFilledRow].NumberFormat = "dd-MM-yyyy";
                                    sheet.Range["Y5:Y" + lastFilledRow].NumberFormat = "0.00";

                                    if (sheet.Range["V6"].Text.StartsWith("RAW MATERIAL"))
                                    {
                                            for (int y = 6; y <= lastFilledRow; y++)
                                            {
                                                    string myFormula = "=IF(MONTH($B" + y + ")>3,YEAR($B" + y + ")&\"-\"&RIGHT((YEAR($B" + y + ")+1),2), YEAR($B" + y + ")-1&\"-\"&RIGHT(YEAR($B" + y + "),2)";

                                                    object formulaResult = workbook.CaculateFormulaValue(myFormula);

                                                    string value = "RM-" + formulaResult.ToString() + "/" + sheet.Range[y, 1].Value.ToString();

                                                    sheet.Range[y, 1].Text = value;
                                            }

                                    }

                                    else if (sheet.Range["V6"].Text.StartsWith("PACKING MATERIAL"))
                                    {
                                            for (int y = 6; y <= lastFilledRow; y++)
                                            {
                                                    string myFormula = "=IF(MONTH($B" + y + ")>3,YEAR($B" + y + ")&\"-\"&RIGHT((YEAR($B" + y + ")+1),2), YEAR($B" + y + ")-1&\"-\"&RIGHT(YEAR($B" + y + "),2)";

                                                    object formulaResult = workbook.CaculateFormulaValue(myFormula);

                                                    string value = "PM-" + formulaResult.ToString() + "/" + sheet.Range[y, 1].Value.ToString();

                                                    sheet.Range[y, 1].Text = value;
                                            }
                                    }

                                    else if (sheet.Range["V6"].Text.StartsWith("PURCHASE"))
                                    {
                                            for (int y = 6; y <= lastFilledRow; y++)
                                            {
                                                    string myFormula = "=IF(MONTH($B" + y + ")>3,YEAR($B" + y + ")&\"-\"&RIGHT((YEAR($B" + y + ")+1),2), YEAR($B" + y + ")-1&\"-\"&RIGHT(YEAR($B" + y + "),2)";

                                                    object formulaResult = workbook.CaculateFormulaValue(myFormula);

                                                    string value = "FG-" + formulaResult.ToString() + "/" + sheet.Range[y, 1].Value.ToString();

                                                    sheet.Range[y, 1].Text = value;
                                            }
                                    }

                                    else
                                    {
                                            for (int y = 6; y <= lastFilledRow; y++)
                                            {
                                                    string myFormula = "=IF(MONTH($B" + y + ")>3,YEAR($B" + y + ")&\"-\"&RIGHT((YEAR($B" + y + ")+1),2), YEAR($B" + y + ")-1&\"-\"&RIGHT(YEAR($B" + y + "),2)";

                                                    object formulaResult = workbook.CaculateFormulaValue(myFormula);

                                                    string value = "OTH-" + formulaResult.ToString() + "/" + sheet.Range[y, 1].Value.ToString();

                                                    sheet.Range[y, 1].Text = value;
                                            }
                                    }


                                    sheet.Copy(sheet.Range["B6:B" + lastFilledRow], newSheet.Range["B2"], true);
                                    sheet.Copy(sheet.Range["A6:A" + lastFilledRow], newSheet.Range["A2"], true);
                                    sheet.Copy(sheet.Range["D6:D" + lastFilledRow], newSheet.Range["C2"], true);
                                    sheet.Copy(sheet.Range["K6:K" + lastFilledRow], newSheet.Range["D2"], true);
                                    sheet.Copy(sheet.Range["M6:M" + lastFilledRow], newSheet.Range["E2"], true);
                                    sheet.Copy(sheet.Range["N6:N" + lastFilledRow], newSheet.Range["F2"], true);
                                    sheet.Copy(sheet.Range["W6:W" + lastFilledRow], newSheet.Range["G2"], true);

                                    newSheet.Range["G2:G" + lastFilledRow].NumberFormat = "0.00";

                                    for (int i = 0; i < newSheet.Columns[0].Count - 1; i++)
                                    {
                                            for (int j = i + 1; j < newSheet.Columns[0].Count; j++)
                                            {
                                                    if (newSheet.Columns[0].CellList[i].Value == newSheet.Columns[0].CellList[j].Value)
                                                    {
                                                            for (int col = 0; col < newSheet.Rows[0].Count; col++)
                                                            {
                                                                    if (col == newSheet.Rows[0].Count - 1 && newSheet.Rows[i].CellList[col].Value == newSheet.Rows[j].CellList[col].Value)
                                                                    {
                                                                            newSheet.DeleteRow(j + 1);
                                                                            j--;
                                                                    }
                                                                    else if (col != newSheet.Rows[0].Count - 1 && newSheet.Rows[i].CellList[col].Value == newSheet.Rows[j].CellList[col].Value)
                                                                    {
                                                                            continue;
                                                                    }
                                                                    else
                                                                    {
                                                                            break;
                                                                    }
                                                            }
                                                    }
                                            }
                                    }


                                    CellRange[] ranges = newSheet.FindAllString("M/S", false, false);

                                    foreach (CellRange range in ranges)
                                    {
                                            range.Text = range.Text.Replace("M/S", "");
                                    }


                                    for (int i = 1; i <= newSheet.LastRow; i++)
                                    {
                                            for (int j = 1; j <= newSheet.LastColumn; j++)
                                            {
                                                    CellRange range = newSheet.Range[i, j];
                                                    String str = range.Value;
                                                    String strings = str.Trim();
                                                    range.Value = strings;
                                            }
                                    }
                                    newSheet.AllocatedRange.AutoFitColumns();


                                    newBook.DataSorter.SortColumns.Add(1, OrderBy.Ascending);
                                    newBook.DataSorter.Sort(newSheet.AllocatedRange);

                                    newBook.SaveToFile(dbFilePath, ExcelVersion.Version2013);

                                    newBook.Dispose();
                            }


                            workbook.Dispose();

                            //----------------------------------------------------------------------
                    }


                    //add the items of the above saved worksheets 3rd column (unique ones) to the combo box
                    //----------------------------------------------------------------------
                    try
                    {
                            Workbook wrkbk = new Workbook();

                            wrkbk.LoadFromFile(dbFilePath);
                            Worksheet sh = wrkbk.Worksheets[0];

                            List<CellRange> crs = sh.Columns[2].CellList;

                            foreach (CellRange cr in crs)
                            {
                                    if (!String.IsNullOrEmpty(cr.Value2.ToString()) && !comboBox1.Items.Contains(cr.Value))
                                    {
                                            comboBox1.Items.Add(cr.Value);
                                    }
                            }
                            comboBox1.Items.Remove("NAME");


                            int index = 0;
                            for (int i = 0; i < sh.LastColumn; i++)
                            {
                                    if (sh.Rows[0].Cells[i].DisplayedText.Equals(headers[index]))
                                    {
                                            columnsIndexs[index] = i;
                                            index++;
                                            if (index == 5)
                                            {
                                                    break;
                                            }
                                    }
                            }

                            wrkbk.Dispose();

                    }
                    catch (Exception ex)
                    {
                            MessageBox.Show(ex.Message);
                    }
                    //----------------------------------------------------------------------

                    //find near duplicate rows and replace all near identical ones with one row with the column 6 data sum
                    //----------------------------------------------------------------------
                    Workbook nwrkbk = new Workbook();

                    nwrkbk.LoadFromFile(dbFilePath);
                    Worksheet nsh = nwrkbk.Worksheets[0];

                    int colCount = nsh.Rows[0].Count;
                    float sum;
                    for (int i = 1; i < nsh.Columns[0].Count - 1; i++)
                    {
                            //Delete blank row
                            if (nsh.Rows[i].IsBlank)
                            {
                                    nsh.DeleteRow(i + 1);
                                    i--;
                            }
                            sum = (float)nsh.Columns[colCount - 2].Rows[i].NumberValue;
                            for (int j = i + 1; j < nsh.Columns[0].Count; j++)
                            {

                                    if (nsh.Columns[0].CellList[i].Value == nsh.Columns[0].CellList[j].Value)
                                    {

                                            for (int col = 1; col < nsh.Rows[0].Count - 1; col++)
                                            {

                                                    if (col == nsh.Rows[0].Count - 3 && nsh.Rows[i].CellList[col].Value == nsh.Rows[j].CellList[col].Value)
                                                    {
                                                            sum += (float)nsh.Columns[colCount - 2].Rows[j].NumberValue;
                                                            nsh.DeleteRow(j + 1);
                                                            j--;


                                                    }
                                                    else if (col != nsh.Rows[0].Count - 3 && nsh.Rows[i].CellList[col].Value == nsh.Rows[j].CellList[col].Value)
                                                    {
                                                            continue;
                                                    }
                                                    else
                                                    {
                                                            break;
                                                    }
                                            }
                                    }
                            }

                            nsh.Columns[colCount - 2].Rows[i].NumberValue = sum;
                    }

                    nwrkbk.Save();
                    nwrkbk.Dispose();
                    //----------------------------------------------------------------------
                    //check the worksheet if any cell in the 5th column has a date in it
                    //----------------------------------------------------------------------
                    Workbook _nwrkbk = new Workbook();

                    _nwrkbk.LoadFromFile(dbFilePath);
                    Worksheet wksh = _nwrkbk.Worksheets[0];

                    int _lastFilledRow = wksh.LastRow;
                    for (int i = wksh.LastRow; i >= 0; i--)
                    {
                            CellRange cr = wksh.Rows[i - 1].Columns[1];
                            if (!cr.IsBlank)
                            {
                                    _lastFilledRow = i;
                                    break;
                            }
                    }


                    foreach (CellRange rng in wksh.Range["E2:E" + _lastFilledRow])
                    {
                            if (rng.HasDateTime)
                            {
                                    err.Add(rng.Row.ToString());
                            }
                    }


                    if (err.Any())
                    {
                            MessageBox.Show("Some Invoice/Bill numbers are in DATE format!!\nPlease check the following rows:\n" + string.Join(", ", err));
                    }


                    MessageBox.Show("Updated!");
                    //----------------------------------------------------------------------
                    btnUpdate.Text = btnTxt;
            }

I know there is a lot of Repetitive code but struggling to make them shorter, faster & more efficient.

NOTE: I'm using spire.xls for worksheet data manipulation

The source excel file looks like

The destination file looks like

Can anyone help me on this...

Answer 1

It's not a good way to group all things in one method. You should really work with the best practices such as dividing the work into smaller parts, and group all related parts together based on role and responsibility. Then, check wither each group needs a method or class, or which design patter would be best fit for your work. So, you need to think of a better way to refactor your code.

For your struggling, this condition if (File.Exists(dbFilePath)) is what made you repeated the same process (with some minor changes). So, the first thing we should focus on, is how we can handle this condition better. From what I've understood, you are trying to load the file from disk, if it's not existed, then you create a new instance of Workbook and apply the default layout (adding headers, and formatting). In both scenarios, you'll have to get the last row number, and then copy the source sheet into the new sheet. Then, you do the rest of work.

having this, we can do this :

Workbook sourceWorkbook = new Workbook();
Workbook targetWorkbook = new Workbook();
Worksheet targetSheet; 
int targetLastFilledRow = 2;
string WOrYRange = "W5:W";

// Working with the Source
sourceWorkbook.LoadFromFile(xlsPath);
Worksheet sourceSheet = sourceWorkbook.Worksheets[0];

// This part has been extracted into a new method
int sourceLastFilledRow = GetLastFilledRowNumber(sourceSheet);

sourceSheet.Range["B5:B" + sourceLastFilledRow].NumberFormat = "dd-MM-yyyy";
sourceSheet.Range["N5:N" + sourceLastFilledRow].NumberFormat = "dd-MM-yyyy";

// This part has been extracted into a new method
ProcessCellV6(sourceSheet, sourceLastFilledRow);


// Working with Target 

if (File.Exists(dbFilePath))
{
     targetWorkbook.LoadFromFile(dbFilePath);    
     targetSheet = targetWorkbook.Worksheets[0];
     targetLastFilledRow = GetLastFilledRowNumber(targetSheet);
}
else 
{
    targetSheet = targetWorkbook.Worksheets[0];

    targetSheet.Range[1, 1].Value = "BP No./REF.No.";
    targetSheet.Range[1, 2].Value = "BP Date";
    targetSheet.Range[1, 3].Value = "NAME";
    targetSheet.Range[1, 4].Value = "GSTIN";
    targetSheet.Range[1, 5].Value = "Bill No.";
    targetSheet.Range[1, 6].Value = "Bill Date";
    targetSheet.Range[1, 7].Value = "Bill Value";
    targetSheet.Range[1, 8].Value = "Remarks";

    WOrYRange = "Y5:Y";
}

 sourceSheet.Range[WOrYRange + sourceLastFilledRow].NumberFormat = "0.00";

//simply copy data from one worksheet to another                                 
CopySheets(sourceSheet, targetSheet, sourceLastFilledRow, targetLastFilledRow); 

//set some number formatting in the destination sheet
targetSheet.Range["G2:G" + sourceLastFilledRow].NumberFormat = "0.00";

The last row number has been extracted into this method :

private int GetLastFilledRowNumber(Worksheet sheet)
{
    for (int i = sheet.LastRow; i >= 0; i--)
    {
        CellRange cr = sheet.Rows[i - 1].Columns[1];
        
        if (!cr.IsBlank)
        {
            return i;
        }
    }
    
    return sheet.LastRow;
}

This part :

if (sheet.Range["V6"].Text.StartsWith("RAW MATERIAL"))
{
    for (int y = 6; y <= lastFilledRow; y++)
    {
            string myFormula = "=IF(MONTH($B" + y + ")>3,YEAR($B" + y + ")&\"-\"&RIGHT((YEAR($B" + y + ")+1),2), YEAR($B" + y + ")-1&\"-\"&RIGHT(YEAR($B" + y + "),2)";

            object formulaResult = workbook.CaculateFormulaValue(myFormula);

            string value = "RM-" + formulaResult.ToString() + "/" + sheet.Range[y, 1].Value.ToString();

            sheet.Range[y, 1].Text = value;
    }

}

else if (sheet.Range["V6"].Text.StartsWith("PACKING MATERIAL"))
{
    for (int y = 6; y <= lastFilledRow; y++)
    {
            string myFormula = "=IF(MONTH($B" + y + ")>3,YEAR($B" + y + ")&\"-\"&RIGHT((YEAR($B" + y + ")+1),2), YEAR($B" + y + ")-1&\"-\"&RIGHT(YEAR($B" + y + "),2)";

            object formulaResult = workbook.CaculateFormulaValue(myFormula);

            string value = "PM-" + formulaResult.ToString() + "/" + sheet.Range[y, 1].Value.ToString();

            sheet.Range[y, 1].Text = value;
    }
}
else if (sheet.Range["V6"].Text.StartsWith("PURCHASE"))
{
    for (int y = 6; y <= lastFilledRow; y++)
    {
            string myFormula = "=IF(MONTH($B" + y + ")>3,YEAR($B" + y + ")&\"-\"&RIGHT((YEAR($B" + y + ")+1),2), YEAR($B" + y + ")-1&\"-\"&RIGHT(YEAR($B" + y + "),2)";

            object formulaResult = workbook.CaculateFormulaValue(myFormula);

            string value = "FG-" + formulaResult.ToString() + "/" + sheet.Range[y, 1].Value.ToString();

            sheet.Range[y, 1].Text = value;
    }
}
else
{
    for (int y = 6; y <= lastFilledRow; y++)
    {
            string myFormula = "=IF(MONTH($B" + y + ")>3,YEAR($B" + y + ")&\"-\"&RIGHT((YEAR($B" + y + ")+1),2), YEAR($B" + y + ")-1&\"-\"&RIGHT(YEAR($B" + y + "),2)";

            object formulaResult = workbook.CaculateFormulaValue(myFormula);

            string value = "OTH-" + formulaResult.ToString() + "/" + sheet.Range[y, 1].Value.ToString();

            sheet.Range[y, 1].Text = value;
    }
}

it's been moved into a method I called it ProcessCellV6 :

private void ProcessCellV6(Worksheet sheet, int lastFilledRow)
{
    string cellV6Value = sheet.Range["V6"].Text.Trim();

    string prefix = "";

    if(cellV6Value == "")
    {
        sheet.Range["V6"].Text = "na"; 
    }
    
    if (cellV6Value.StartsWith("RAW MATERIAL"))
    {
        prefix = "RM";
    }
    else if (cellV6Value.StartsWith("PACKING MATERIAL"))
    {
        prefix = "PM";
    }
    else if (cellV6Value.StartsWith("PURCHASE"))
    {
        prefix = "FG";
    }
    else 
    {
        prefix = "OTH";
    }

    for (int y = 6; y <= lastFilledRow; y++)
    {
        string myFormula = $"=IF(MONTH($B{y})>3,YEAR($B{y})&\"-\"&RIGHT((YEAR($B{y})+1),2), YEAR($B{y})-1&\"-\"&RIGHT(YEAR($B{y}),2)";

        object formulaResult = workbook.CaculateFormulaValue(myFormula);

        string value = $"{prefix}-{formulaResult.ToString()}/{sheet.Range[y, 1].Value.ToString()}";

        sheet.Range[y, 1].Text = value;
    }
    
}

Also, copying the sheets has been moved into CopySheets method :

private void CopySheets(Worksheet source, Worksheet target, int sourceLastFilledRow, int targetLastFilledRow)
{
    source.Copy(source.Range["B6:B" + sourceLastFilledRow], target.Range["B" + targetLastFilledRow], true);
    source.Copy(source.Range["A6:A" + sourceLastFilledRow], target.Range["A" + targetLastFilledRow], true);
    source.Copy(source.Range["D6:D" + sourceLastFilledRow], target.Range["C" + targetLastFilledRow], true);
    source.Copy(source.Range["K6:K" + sourceLastFilledRow], target.Range["D" + targetLastFilledRow], true);
    source.Copy(source.Range["M6:M" + sourceLastFilledRow], target.Range["E" + targetLastFilledRow], true);
    source.Copy(source.Range["N6:N" + sourceLastFilledRow], target.Range["F" + targetLastFilledRow], true);
    source.Copy(source.Range["W6:W" + sourceLastFilledRow], target.Range["G" + targetLastFilledRow], true);
}

Now from here, you can replicate the work by moving some parts into methods to make things much readable and reusable like RemoveString which can be :

public void RemoveString(Worksheet sheet, string str)
{
    CellRange[] ranges = sheet.FindAllString(str, false, false);

    foreach (CellRange range in ranges)
    {
        range.Text = range.Text.Replace(str, string.Empty);
    }
}

and use it to remove M/S from the cells like :

//Remove the word "M/S"
RemoveString(targetSheet, "M/S"); 

you can do the same for removing white spaces, duplicates and sorting & autofit columns as well.

Also, all the code after this line :

//add the items of the above saved worksheets 3rd column (unique ones) to the combo box

it is unclear for me to know why are you reloading a disposed file three times ? in other word, you processed the files, and created a new sheet with the final results. You should use that instance, and don't dispose it until you're completely done with it.

Final thoughts, try to use using blocks with Disposable objects, and I think you will need a class to work with the Excel files specially if your application using Workbook in deferent Forms. also, make a good use of StringComparison as all comparing conditions in your code lake that.

UPDATE FROM Comments

I'm not familiar with Spire.Xls as I'm used to ClosedXML for excels work. However, I've reviewed the Spire.Xls nuget and documentation, and it seems its documentation is not up-to-date with the nuget version. I found it a bit complicated than ClosedXML.

Anyhow, when I tested your remove duplicates code, it takes longer than it should, which slows down your overall code.

From your code your cells range is "A2:G" + targetLastFilledRow

So you can do this :

// working with the finalized sheet 
var cellRanges = targetSheet.Range["A2:G" + targetLastFilledRow];


// get the duplicated row numbers 
var duplicatedRows = cellRanges.Rows
    .GroupBy(x=> x.Columns[0].Value)
    .Where(x=> x.Count() > 1)
    .Select(x=> x.Last().Columns[0].Row)
    .ToList();

this would return a list of duplicated row numbers, however since Spire.Xls doesn't have an overload to DeleteRow that accepts IEnumerable<int> and since you're going to delete blank rows as well, you can do this :

var primaryKeys = new HashSet<string>();
foreach(var row in cellRanges.Rows)
{
    if(row.IsBlank)
    {
        sheet.DeleteRow(row.Row);
    }
    else
    {
        var primaryKey = row.Columns[0].Value;

        if(primaryKeys.Contains(primaryKey))
        {
            sheet.DeleteRow(row.Row);
        }
        else
        {
            primaryKeys.Add(primaryKey);
        }
    }
}

this would remove blank rows, and also would check the BP No./REF.No. column rows, if there is a duplicate it would remove that entire row.

I'll share some updates on the code to give you more visual on the things that should be considered :

Methods :

private string GetCellV6Prefix(string cellV6Value)
{
    switch(cellV6Value)
    {
        case "RAW MATERIAL":
            return "RM";
        case "PACKING MATERIAL":
            return "PM";
        case "PURCHASE":
            return "FG";
        default:
            return "OTH";
    }

}

private void ProcessCellV6(Workbook workbook , int lastFilledRow)
{
    var sheet = workbook.Worksheets[0];

    string cellV6Value = sheet.Range["V6"].Text.Trim().ToUpperInvariant();

    if(cellV6Value?.Length == 0)
    {
        sheet.Range["V6"].Text = "na";
    }

    string prefix = GetCellV6Prefix(cellV6Value);

    for(int y = 6; y <= lastFilledRow; y++)
    {
        string myFormula = $"=IF(MONTH($B{y})>3,YEAR($B{y})&\"-\"&RIGHT((YEAR($B{y})+1),2), YEAR($B{y})-1&\"-\"&RIGHT(YEAR($B{y}),2)";

        object formulaResult = workbook.CaculateFormulaValue(myFormula);

        string value = $"{prefix}-{formulaResult.ToString()}/{sheet.Range[y, 1].Value.ToString()}";

        sheet.Range[y , 1].Text = value;
    }

}


/// <summary>
/// retuns the number of non-empty rows
/// </summary>
/// <param name="sheet"></param>
/// <returns></returns>
private static int GetRowCount(Worksheet sheet)
{
    int rowsCount = 0;

    for(int x = 0; x < sheet.LastRow; x++)
    {
        if(sheet.Rows[x].IsBlank)
        {
            break;
        }

        rowsCount++;
    }

    return rowsCount;
}

/// <summary>
/// Copy Sheets
/// </summary>
/// <param name="source"></param>
/// <param name="target"></param>
/// <param name="sourceRowsCount"></param>
/// <param name="targetRowsCount"></param>
private void CopySheets(Worksheet source , Worksheet target , int sourceRowsCount , int targetRowsCount)
{
    source.Copy(source.Range["B6:B" + sourceRowsCount] , target.Range["B" + targetRowsCount] , true);
    source.Copy(source.Range["A6:A" + sourceRowsCount] , target.Range["A" + targetRowsCount] , true);
    source.Copy(source.Range["D6:D" + sourceRowsCount] , target.Range["C" + targetRowsCount] , true);
    source.Copy(source.Range["K6:K" + sourceRowsCount] , target.Range["D" + targetRowsCount] , true);
    source.Copy(source.Range["M6:M" + sourceRowsCount] , target.Range["E" + targetRowsCount] , true);
    source.Copy(source.Range["N6:N" + sourceRowsCount] , target.Range["F" + targetRowsCount] , true);
    source.Copy(source.Range["W6:W" + sourceRowsCount] , target.Range["G" + targetRowsCount] , true);
}

/// <summary>
/// 1. Remove Duplicates 
/// 2. Remove the word "M/S"
/// 3. Remove Blank Rows 
/// 4. Trim leading & Trailing Whitespaces
/// </summary>
/// <param name="range"></param>
/// <param name="searchFor"></param>
/// <param name="replaceWith"></param>
private void RemoveAndTrimStrings(IEnumerable<CellRange> range , string searchFor = null , string replaceWith = null)
{
    var useReplace = searchFor != null && replaceWith != null;

    // loop over row cells 
    foreach(var cell in range)
    {
        // Remove "M/S" word
        if(useReplace && !string.IsNullOrWhiteSpace(cell.Text))
        {
            cell.Text = cell.Text.Replace(searchFor , replaceWith);
        }

        // Trim cell
        if(!string.IsNullOrWhiteSpace(cell.Value))
        {
            cell.Value = cell.Value.Trim();
        }
    }
}

/// <summary>
/// Create Default Headers
/// </summary>
/// <param name="sheet"></param>
private void CreateDefaultHeaders(Worksheet sheet)
{
    sheet.Range[1 , 1].Value = "BP No./REF.No.";
    sheet.Range[1 , 2].Value = "BP Date";
    sheet.Range[1 , 3].Value = "NAME";
    sheet.Range[1 , 4].Value = "GSTIN";
    sheet.Range[1 , 5].Value = "Bill No.";
    sheet.Range[1 , 6].Value = "Bill Date";
    sheet.Range[1 , 7].Value = "Bill Value";
    sheet.Range[1 , 8].Value = "Remarks";
}

/// <summary>
/// 1. Remove Duplicates 
/// 2. Remove the word "M/S"
/// 3. Remove Blank Rows 
/// 4. Trim leading & Trailing Whitespaces
/// </summary>
/// <param name="range"></param>
private void ApplyContentModifications(Worksheet sheet, CellRange range)
{
    var primaryKeys = new HashSet<string>();

    foreach(var row in range.Rows)
    {
        if(row.IsBlank)
        {
            // delete blank row
            sheet.DeleteRow(row.Row);
        }
        else
        {
            var primaryKey = row.Columns[0].Value;

            if(primaryKeys.Contains(primaryKey))
            {
                // delete duplicated row
                sheet.DeleteRow(row.Row);
            }
            else
            {
                RemoveAndTrimStrings(row.CellList , "M/S" , string.Empty);

                primaryKeys.Add(primaryKey);
            }
        }
    }

}

now, using the above methods, your code should be similar to this :

using(var sourceWorkbook = new Workbook())
using(var targetWorkbook = new Workbook())
{
    Worksheet targetSheet; 
    int targetRowCount = 2;
    string WOrYRange = "W5:W";

    // Working with the Source
    sourceWorkbook.LoadFromFile(xlsPath);
    Worksheet sourceSheet = sourceWorkbook.Worksheets[0];

    // This part has been extracted into a new method
    int sourceRowCount = GetRowCount(sourceSheet);

    sourceSheet.Range["B5:B" + sourceRowCount].NumberFormat = "dd-MM-yyyy";
    sourceSheet.Range["N5:N" + sourceRowCount].NumberFormat = "dd-MM-yyyy";

    // This part has been extracted into a new method
    ProcessCellV6(sourceWorkbook, sourceRowCount);

    // Working with Target 
    if (File.Exists(dbFilePath))
    {
        targetWorkbook.LoadFromFile(dbFilePath);     
        targetSheet = targetWorkbook.Worksheets[0];
        targetRowCount = GetRowCount(targetSheet);
    }
    else 
    {
        targetSheet = targetWorkbook.Worksheets[0];
        CreateDefaultHeaders(targetSheet);
        WOrYRange = "Y5:Y";
    }

    sourceSheet.Range[WOrYRange + sourceRowCount].NumberFormat = "0.00";

    //simply copy data from one worksheet to another                                 
    CopySheets(sourceSheet, targetSheet, sourceRowCount, targetRowCount); 

    //set some number formatting in the destination sheet
    targetSheet.Range["G2:G" + sourceRowCount].NumberFormat = "0.00";

    var cellRange = sheet.Range["A2:G" + targetRowCount];

    ApplyContentModifications(targetSheet, cellRange);

    targetSheet.AllocatedRange.AutoFitColumns();
    targetWorkbook.DataSorter.SortColumns.Add(1 , OrderBy.Ascending);
    targetWorkbook.DataSorter.Sort(targetSheet.AllocatedRange);
    targetWorkbook.SaveToFile(path , ExcelVersion.Version2013);
    
}

the rest is on your hands !

UPDATE #2

For some reason when applying any modifications on CellList values, some values are not updated, I think it's a bug in the NuGet itself. As it's more like a maze using this NuGet. The only way worked for me its using sheet.Range, but when using sheet.Columns or sheet.Rows using CellList I always find some values that are not updated, also this applies to direct updating range.Value as for some reason if i do this

cell.Value = cell.Value.Replace("M/S" , "");

some values won't be updated, but if I do this :

var value = cell.Value;
var str = value?.Replace("M/S", "").Trim() ?? "";
cell.Value = str;

the value will be updated!.

Although, FindAllStrings are not very helpful, because it iterates over the cells, this applies to the rest of the Findxx methods. Which in our case is adding more iterations than it supposed to.

you can remove RemoveAndTrimStrings and update ApplyContentModifications to this :

/// <summary>
/// 1. Remove Duplicates
/// 2. Remove the word "M/S"
/// 3. Remove Blank Rows
/// 4. Trim leading & Trailing Whitespaces
/// </summary>
/// <param name="range"></param>
private void ApplyContentModifications(Worksheet sheet)
{
    var primaryKeys = new HashSet<string>();

    //// first row is the header
    for(int i = 1; i <= sheet.LastRow; i++)
    {
        for(int j = 1; j <= sheet.LastColumn; j++)
        {
            
            var primaryKey = !string.IsNullOrWhiteSpace(sheet.Range[i, 1].Value) ? sheet.Range[i, 1].Value.Trim() : "";

            var range = sheet.Range[i, j];

            if(range.IsBlank || primaryKeys.Contains(primaryKey))
            {
                sheet.DeleteRow(i);
                continue;
            }

            primaryKeys.Add(primaryKey);

            var value = range.Value;

            var str = !stirng.IsNullOrWhiteSpace(value) ? value.Replace("M/S", "").Trim() : "";

            range.Value = str;
        }
    }
}

this should fix it.

BTW, I have played with the samples you gave me, and I created a class builder for this report, it should give you more maintainability to build the report, and you can also use generics to enhance it and make it more useable on other reports as well. The basic idea is to use data model to design the data results, which would flatten your data objects, and give you more extendibility specially with Linq.

Here is the classes :

public class PurchaseRegister
{
    [Display(Name = "BP No./REF.No." , Order = 1)]
    public string ReferenceNumber { get; set; }

    [Display(Name = "BP Date" , Order = 2)]
    public DateTime BPDate { get; set; }

    [Display(Name = "NAME" , Order = 3)]
    public string Name { get; set; }

    [Display(Name = "GSTIN" , Order = 4)]
    public string Gstin { get; set; }

    [Display(Name = "Bill No." , Order = 5)]
    public string BillNumber { get; set; }

    [Display(Name = "Bill Date" , Order = 6)]
    public DateTime BillDate { get; set; }

    [Display(Name = "Bill Value" , Order = 7)]
    public double BillValue { get; set; }

    [Display(Name = "Remarks" , Order = 8)]
    public string Remarks { get; set; }

}

public class PurchaseRegisterExcelReportBuilder : IDisposable
{
    private static readonly PropertyInfo[] _properties = typeof(PurchaseRegister).GetProperties();

    // to be used on cell V6 to get the proper prefix
    private static readonly IReadOnlyDictionary<string, string> _ledgerMapper = new Dictionary<string, string>
    {
        {"RAW MATERIAL", "RM"},
        {"PACKING MATERIAL", "PM"},
        {"PURCHASE", "FG"},
        {"OTHER", "OTH"}
    };

    // Mapping columns between model and source excel file
    private static readonly IReadOnlyDictionary<string, int> _sourceColumnsMapper = new Dictionary<string, int>
    {
        {"ReferenceNumber", 1},
        {"BPDate", 2},
        {"Name", 4},
        {"Gstin", 11},
        {"BillNumber", 13},
        {"BillDate", 14},
        {"Ledger", 22},
        {"BillValue", 23},
    };

    // Mapping columns between model and target excel file (database)
    private static readonly IReadOnlyDictionary<string, int> _targetColumnsMapper = new Dictionary<string, int>
    {
        {"ReferenceNumber", 1},
        {"BPDate", 2},
        {"Name", 3},
        {"Gstin", 4},
        {"BillNumber", 5},
        {"BillDate", 6},
        {"BillValue", 7}
    };

    private string _primaryKeyPrefix;
    private readonly string _sourceFilePath;
    private readonly string _targetFilePath;

    private bool _useAutoFit;
    private bool _useTrimStrings;
    private bool _useRemoveDuplicatedRows;
    private bool _useReplaceStrings;
    private string _replaceSearchFor;
    private string _replaceWithStr;

    private OrderBy _orderBy;

    private Workbook _sourceWorkbook;
    private Workbook _targetWorkbook;


    private List<PurchaseRegister> _entries;

    public Workbook Results { get; private set; }

    public PurchaseRegisterExcelReportBuilder(string sourceFilePath , string targetFilePath)
    {
        if(string.IsNullOrWhiteSpace(sourceFilePath))
        {
            throw new ArgumentNullException(nameof(sourceFilePath));
        }

        if(!File.Exists(sourceFilePath))
        {
            throw new FileNotFoundException(nameof(sourceFilePath));
        }

        if(string.IsNullOrWhiteSpace(targetFilePath))
        {
            throw new ArgumentNullException(nameof(targetFilePath));
        }

        _sourceFilePath = sourceFilePath;
        _targetFilePath = targetFilePath;
        Results = GetNewInstanceOfWorkbook();
        _orderBy = Spire.Xls.OrderBy.Ascending;

    }

    private Workbook GetNewInstanceOfWorkbook()
    {
        var workbook = new Workbook();

        var sheet = workbook.Worksheets[0];

        int order = 1;

        foreach(var property in _properties)
        {
            var display = property.GetCustomAttribute<DisplayAttribute>();
            var name = display?.Name ?? property.Name;
            order = display?.Order ?? ++order;

            var range = sheet.Range[1 , order];

            range.Value = name;

            if(property.PropertyType == typeof(DateTime))
            {
                range.EntireColumn.NumberFormat = "dd-MM-yyyy";
            }
            else if(property.PropertyType == typeof(double))
            {
                range.EntireColumn.NumberFormat = "0.00";
            }
        }

        return workbook;

    }

    private void SetSourceWorkbook()
    {
        // Working with the Source
        _sourceWorkbook = new Workbook();
        _sourceWorkbook.LoadFromFile(_sourceFilePath);
        Worksheet sheet = _sourceWorkbook.Worksheets[0];

        // check V6 value
        var v6Text = sheet.Range["V6"].Text;

        if(string.IsNullOrWhiteSpace(v6Text))
        {
            sheet.Range["V6"].Text = v6Text = "na na";
        }

        if(!_ledgerMapper.TryGetValue(v6Text , out _primaryKeyPrefix))
        {
            _primaryKeyPrefix = _ledgerMapper["OTHER"];
        }

        // Format 
        sheet.Range["B6:B" + sheet.LastRow].NumberFormat = "dd-MM-yyyy";
        sheet.Range["N6:N" + sheet.LastRow].NumberFormat = "dd-MM-yyyy";
        sheet.Range["W6:W" + sheet.LastRow].NumberFormat = "0.00";

        var source = GetEntires(sheet,"A6:W" + sheet.LastRow, 6, _sourceColumnsMapper);

        if(source?.Any() == true)
        {
            if(_entries == null)
            {
                _entries = new List<PurchaseRegister>();
            }

            _entries.AddRange(source);
        }


    }

    private void SetTargetWorkbook()
    {
        if(!File.Exists(_targetFilePath))
        { return; }

        // Working with the Source
        _targetWorkbook = new Workbook();
        _targetWorkbook.LoadFromFile(_targetFilePath);
        Worksheet sheet = _targetWorkbook.Worksheets[0];

        // Format target
        sheet.Range["B2:B" + sheet.LastRow].NumberFormat = "dd-MM-yyyy";
        sheet.Range["F2:F" + sheet.LastRow].NumberFormat = "dd-MM-yyyy";
        sheet.Range["G2:G" + sheet.LastRow].NumberFormat = "0.00";


        var target = GetEntires(sheet,"A2:G" + sheet.LastRow, 2, _targetColumnsMapper);

        if(target?.Any() == true)
        {
            if(_entries == null)
            {
                _entries = new List<PurchaseRegister>();
            }

            _entries.AddRange(target);
        }

    }

    private IEnumerable<PurchaseRegister> GetEntires(Worksheet sheet , string cellsRange , int startAtRowNumber , IReadOnlyDictionary<string , int> columnsMapper)
    {

        var range = sheet.Range[cellsRange];

        var unique = new HashSet<string>();

        for(var x = startAtRowNumber; x <= sheet.LastRow; x++)
        {
            var bPDateValue = range[x , 2].Value?.Trim();
            var refernceValue = range[x , 1].Value?.Trim();

            if(string.IsNullOrWhiteSpace(refernceValue))
            { continue; }


            DateTime.TryParse(bPDateValue , out DateTime bPDate);

            var referenceNumber = GetReferenceNumber(refernceValue, bPDate);


            var entry = new PurchaseRegister();

            foreach(PropertyInfo property in _properties)
            {
                if(columnsMapper.ContainsKey(property.Name))
                {
                    var index = columnsMapper[property.Name];

                    var value = range[x , index].Value;

                    if(string.IsNullOrWhiteSpace(value))
                    { continue; }


                    if(property.PropertyType == typeof(DateTime))
                    {
                        if(property.Name == "BPDate")
                        {
                            property.SetValue(entry , bPDate);
                        }
                        else
                        {
                            DateTime.TryParse(value , out DateTime result);
                            property.SetValue(entry , result);
                        }
                    }
                    else if(property.PropertyType == typeof(double) && double.TryParse(value , out double doubleResult))
                    {
                        property.SetValue(entry , doubleResult);
                    }
                    else
                    {
                        if(property.Name == "ReferenceNumber")
                        {
                            property.SetValue(entry , referenceNumber);
                        }
                        else
                        {
                            if(_useTrimStrings)
                            {
                                value = value.Trim();
                            }

                            if(_useReplaceStrings)
                            {
                                value = value.Replace(_replaceSearchFor , _replaceWithStr).Trim();
                            }

                            property.SetValue(entry , value);
                        }
                    }


                }
            }

            if(_useRemoveDuplicatedRows)
            {
                if(unique.Contains(referenceNumber))
                {
                    continue;
                }
                else
                {
                    unique.Add(referenceNumber);
                    yield return entry;
                }

            }
        }
    }

    private string GetReferenceNumber(string referenceNumber , DateTime bPDate)
    {
        if(!long.TryParse(referenceNumber , out long result) || bPDate.Year.ToString().Length < 4)
        {
            return referenceNumber;
        }

        var leftYear  = bPDate.Year;

        var rightYear = leftYear;

        if(bPDate.Month > 3)
        {
            rightYear++;
        }
        else
        {
            leftYear--;
        }

        return $"{_primaryKeyPrefix}-{leftYear}-{rightYear.ToString().Substring(2)}/{referenceNumber}";
    }


    public PurchaseRegisterExcelReportBuilder OrderBy(OrderBy order)
    {
        _orderBy = order;
        return this;
    }

    public PurchaseRegisterExcelReportBuilder UseAutoFitColumns()
    {
        _useAutoFit = true;
        return this;
    }

    public PurchaseRegisterExcelReportBuilder UseTrimStrings()
    {
        _useTrimStrings = true;
        return this;
    }

    public PurchaseRegisterExcelReportBuilder UseReplaceStrings(string search , string str)
    {
        if(string.IsNullOrWhiteSpace(search))
        {
            throw new ArgumentNullException(nameof(search));
        }

        _replaceSearchFor = search;
        _replaceWithStr = str ?? string.Empty;
        _useReplaceStrings = true;
        return this;
    }

    public PurchaseRegisterExcelReportBuilder UseRemoveDuplicatedRows()
    {
        _useRemoveDuplicatedRows = true;
        return this;
    }

    /// <summary>
    /// Bulid ResultWorkbook
    /// </summary>
    public void Build()
    {
        SetSourceWorkbook();
        SetTargetWorkbook();

        var sheet = Results.Worksheets[0];
        // insert rows 
        var rowIndex = 2;

        for(int x = 0; x < _entries.Count; x++, rowIndex++)
        {
            var model = _entries[x];

            sheet.Range[rowIndex , 1].Value = model.ReferenceNumber;
            sheet.Range[rowIndex , 2].Value = model.BPDate.ToString("dd-MM-yyyy");
            sheet.Range[rowIndex , 3].Value = model.Name;
            sheet.Range[rowIndex , 4].Value = model.Gstin;
            sheet.Range[rowIndex , 5].Value = model.BillNumber;
            sheet.Range[rowIndex , 6].Value = model.BillDate.ToString("dd-MM-yyyy");
            sheet.Range[rowIndex , 7].Value = model.BillValue.ToString("0.00");

        }

        //apply cells formats 
        sheet.Range["B2:B" + rowIndex].NumberFormat = "dd-MM-yyyy";
        sheet.Range["F2:F" + rowIndex].NumberFormat = "dd-MM-yyyy";
        sheet.Range["G2:G" + rowIndex].NumberFormat = "0.00";

        //apply font style 
        var style = sheet.Range["A1:G" + rowIndex].Style;
        style.Font.FontName = "Calibri";
        style.Font.IsBold = true;
        style.Font.Size = 8;
    }

    /// <summary>
    /// Save Current ResultWorkbook to a File
    /// </summary>
    /// <param name="destinationFilePath"></param>
    /// <param name="version"></param>
    public void SaveToFile(string destinationFilePath , ExcelVersion version)
    {
        if(string.IsNullOrWhiteSpace(destinationFilePath))
        {
            throw new ArgumentNullException(nameof(destinationFilePath));
        }

        var sheet = Results.Worksheets[0];

        // finalize and save
        if(_useAutoFit)
        {
            sheet.AllocatedRange.AutoFitColumns();
        }

        Results.DataSorter.SortColumns.Add(1 , _orderBy);
        Results.DataSorter.Sort(sheet.AllocatedRange);
        Results.SaveToFile(destinationFilePath , version);

    }

    /// <summary>
    /// Build ResultWorkbook and Save it to file directly.
    /// </summary>
    /// <param name="destinationFilePath"></param>
    /// <param name="version"></param>
    public void BuildAndSave(string destinationFilePath , ExcelVersion version)
    {
        if(string.IsNullOrWhiteSpace(destinationFilePath))
        {
            throw new ArgumentNullException(nameof(destinationFilePath));
        }

        Build();

        SaveToFile(destinationFilePath , version);
    }
    
    private bool _disposed;
    
    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize(this);
    }

    private void Dispose(bool disposing)
    {
        if(!_disposed)
        {
            if(disposing)
            {
                _entries = null;
                _sourceWorkbook.Dispose();
                _targetWorkbook.Dispose();
                Results.Dispose();                  
            }

            _disposed = true;
        }


    }

    ~PurchaseRegisterExcelReportBuilder()
    {
        Dispose(false);
    }
}

and you can use it like this :

// simple usage 
using(var reportBuilder = new PurchaseRegisterExcelReportBuilder(sourcePath , targetPath))
{
    // if you need to work with the results before saving them
    reportBuilder
        .UseTrimStrings()
        .UseReplaceStrings("M/S" , "")
        .UseRemoveDuplicatedRows()
        .UseAutoFitColumns()
        .OrderBy(OrderBy.Ascending)
        .Build(); // or call BuildAndSave if there is no more modifications on the report.Results.

    // do stuff with the `report.Results`
    // when you are done just do 
    reportBuilder.SaveToFile(resultPath , ExcelVersion.Version2013);
}

I hope this example gives you the help you need to rich your code ;).