public class AgencyExportService : IAgencyExportService
    {
        public AgencyExportService(string exportsFolder, string lanId )
        {
            _exportsFolder = exportsFolder;
            _lanId = lanId.Trim().ToUpper();
        }
        private string _exportsFolder;
        private string _lanId;

         public async Task ExportContactSearchResults(string criteria, List<ContactMaster> searchResults)
        {
            try
            {
            var fileName = _exportsFolder + "ContactsSearchResults_" + _lanId + ".xlsx";

            using (FileStream stream = File.OpenWrite(fileName))
            {
                using (IWorkbookExporter workbook = SpreadExporter.CreateWorkbookExporter(SpreadDocumentFormat.Xlsx, stream))
                {
                    // Create a style for the report title and criteria lines
                    SpreadCellStyle style = workbook.CellStyles.Add("MyStyle");
                    style.IsBold = true;
                    style.WrapText = true;
                    style.HorizontalAlignment = SpreadHorizontalAlignment.Center;
                    style.VerticalAlignment = SpreadVerticalAlignment.Center;

                    using (IWorksheetExporter worksheet = workbook.CreateWorksheetExporter("Contacts"))
                    {

                        // Must export the worksheet view state before filling the worksheet with data.
                        using (IWorksheetViewExporter worksheetView = worksheet.CreateWorksheetViewExporter())
                        {
                            worksheetView.SetFirstVisibleCell(0, 0);
                            //worksheetView.AddSelectionRange(0, 0, searchResults.Count + 3, 4);
                            worksheetView.SetActiveSelectionCell(0, 0);
                        }

                        // Must export the column setting before exporting the row and cell data.
                        using (IColumnExporter column = worksheet.CreateColumnExporter()) { column.SetWidthInPixels(250); }
                        using (IColumnExporter column = worksheet.CreateColumnExporter()) { column.SetWidthInPixels(150); }
                        using (IColumnExporter column = worksheet.CreateColumnExporter()) { column.SetWidthInPixels(150); }
                        using (IColumnExporter column = worksheet.CreateColumnExporter()) { column.SetWidthInPixels(200); }
                        using (IColumnExporter column = worksheet.CreateColumnExporter()) { column.SetWidthInPixels(200); }
                        using (IColumnExporter column = worksheet.CreateColumnExporter()) { column.SetWidthInPixels(150); }
                        using (IColumnExporter column = worksheet.CreateColumnExporter()) { column.SetWidthInPixels(200); }


                        // Report Title
                        using (IRowExporter row = worksheet.CreateRowExporter())
                        {
                            row.SetHeightInPixels(25);
                            using (ICellExporter cell = row.CreateCellExporter())
                            {
                                cell.SetValue("Contact Search Results");
                                cell.SetFormat(new SpreadCellFormat()
                                {
                                    CellStyle = style,
                                    FontSize = 18
                                });
                            }
                        }

                        // Criteria
                        using (IRowExporter row = worksheet.CreateRowExporter())
                        {
                            row.SetHeightInPixels(50);
                            using (ICellExporter cell = row.CreateCellExporter())
                            {
                                cell.SetValue(criteria);
                                cell.SetFormat(new SpreadCellFormat()
                                {
                                    CellStyle = style,
                                });
                            }
                        }

                        worksheet.SkipRows(1);

                        // Column Headings
                        var columnNames = new List<string>() { "Agency", "First Name", "Last Name", "Title", "Contact Type", "Phone", "Email" };

                        using (IRowExporter row = worksheet.CreateRowExporter())
                        {
                            foreach (var columnName in columnNames)
                            {
                                using (ICellExporter cell = row.CreateCellExporter())
                                {
                                    cell.SetValue(columnName);
                                    cell.SetFormat(new SpreadCellFormat()
                                    {
                                        IsBold = true,
                                    });
                                }
                            }
                        }

                        // And finally, the data
                        foreach (var result in searchResults)
                        {

                          
                            using (IRowExporter row = worksheet.CreateRowExporter())
                            {
                                using (ICellExporter cell = row.CreateCellExporter())
                                {
                                    cell.SetValue(result.AgencyName ?? "");
                                }
                                using (ICellExporter cell = row.CreateCellExporter())
                                {
                                    cell.SetValue(result.FirstName ?? "");
                                }
                                using (ICellExporter cell = row.CreateCellExporter())
                                {
                                    cell.SetValue(result.LastName ?? "");
                                }
                                using (ICellExporter cell = row.CreateCellExporter())
                                {
                                    cell.SetValue(result.Title ?? "");
                                }
                                using (ICellExporter cell = row.CreateCellExporter())
                                {
                                    cell.SetValue(result.TypeDesc ?? "");
                                }
                                using (ICellExporter cell = row.CreateCellExporter())
                                {
                                    cell.SetValue(result.Phone1 ?? "");
                                }
                                using (ICellExporter cell = row.CreateCellExporter())
                                {
                                    cell.SetValue(result.Email1 ?? "");
                                }
                            }
                           

                            //var columnNames = new List<string>() { "Agency", "First Name", "Last Name", "Title", "Contact Type" };
                          
                        }

                        worksheet.MergeCells(0, 0, 0, 6);
                        worksheet.MergeCells(1, 0, 1, 6);
                    }
                }

//The code succeeds to this point.
                ProcessStartInfo psi = new ProcessStartInfo()
                {
                    FileName = fileName,
                    UseShellExecute = false
                };
                Process.Start(psi);
            }
            }
            catch (Exception ex)
            {

                throw;
            }


        }

        


    }