خروجی اکسل با استفاده از InteropServices

یکی از کارهای پر دردسر در گرفتن خروجی اکسل استفاده از InteropServices است. از ساخت گرفته تا بستن آبجکت.
کد زیر نمونه‌ای از این موضوع را نمایش می‌دهد.

private string Create_ExcelFile(List<ExcelExport_COM> result)
        {
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Add();
            Microsoft.Office.Interop.Excel._Worksheet xlWorkSheet = xlWorkbook.ActiveSheet;
            xlWorkSheet.DisplayRightToLeft = true;
            xlWorkSheet.Name = "لیست خروجی";
            string FinalFileName = string.Empty;

            try
            {
                //Get export folder name from setting
                Setting_BLL setting_BLL = new Setting_BLL();
                var exportAddress = setting_BLL.GetSetting_ById((int)CommonLayer.Setting_COM.enmSetting.Export_Excel_Folder).ItemValue;

                //Check directory exist
                if (!System.IO.Directory.Exists(Server.MapPath(exportAddress)))
                {
                    //Create directory
                    System.IO.Directory.CreateDirectory(Server.MapPath(exportAddress));
                }

                //Readdress from root
                exportAddress = Server.MapPath(exportAddress);

                //Create full details of export file
                FinalFileName = System.IO.Path.Combine(exportAddress, Guid.NewGuid().ToString("n"));

                int row = 1;

                xlApp.Cells[row, 1] = "ردیف";
                xlApp.Cells[row, 2] = "استان";
                xlApp.Cells[row, 3] = "نام بیمه شده";
                xlApp.Cells[row, 4] = "نام خانوادگی بیمه شده";
                xlApp.Cells[row, 5] = "نسبت با فرد";
                xlApp.Cells[row, 6] = "کدملی";
                xlApp.Cells[row, 7] = "نوع وسیله";
                xlApp.Cells[row, 8] = "شماره پلاک";
                xlApp.Cells[row, 9] = "کل مبلغ حق بیمه(ريال)";

                for (int i = 0; i < result.Count; i++)
                {
                    xlApp.Cells[(row + i + 1), 1] = i + 1;
                    xlApp.Cells[(row + i + 1), 2] = result[i].RegionTitle;
                    xlApp.Cells[(row + i + 1), 3] = result[i].PersonFirstName;
                    xlApp.Cells[(row + i + 1), 4] = result[i].PersonLastName;
                    xlApp.Cells[(row + i + 1), 5] = result[i].EligibilityReasonTitle;
                    xlApp.Cells[(row + i + 1), 6] = result[i].PersonNationalId;
                    xlApp.Cells[(row + i + 1), 7] = result[i].CarModel;
                    xlApp.Cells[(row + i + 1), 8] = result[i].Pelaque;
                    xlApp.Cells[(row + i + 1), 9] = result[i].Price;
                }


                xlWorkbook.SaveAs(FinalFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel12,
                    System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, false,
                    Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, false, false, System.Reflection.Missing.Value,
                    System.Reflection.Missing.Value, System.Reflection.Missing.Value);
                xlWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
                xlApp.Application.Quit();



                //Check new file exist
                if (System.IO.File.Exists(FinalFileName + ".xlsb"))
                {
                    FinalFileName = FinalFileName + ".xlsb";
                }
                else
                {
                    FinalFileName = string.Empty;
                }
            }
            catch (Exception err)
            {
                //Log
            }
            finally
            {
                //System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkSheet);
                xlWorkSheet = null;

                //System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkbook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook);
                xlWorkbook = null;

                //System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                xlApp = null;

                GC.Collect();
                GC.WaitForPendingFinalizers();

            }
            return FinalFileName;
        }