private static void KillObject(object o)
    {
        System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
        o = null;
    }

    

    public static int UpdateRetailPrice(string ExcelFile) //Внесение изменений в прайс-лист OLP (в БД)
    {
        int retVal = 0; //0 - обновилось удачно, 1 - ошибка обновления

        Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application();
        try
        {
            dbFunctions db1 = new dbFunctions();
            Excel.Workbook ObjWorkBook;
            Excel.Worksheet ObjWorkSheet;

            ObjWorkBook = ObjExcel.Workbooks.Open(ExcelFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            ObjWorkSheet = (Excel.Worksheet)ObjWorkBook.ActiveSheet;
            Excel.PivotTable pt = (Excel.PivotTable)ObjWorkSheet.PivotTables(1);


            Excel.PivotField pf_PartNumber = (Excel.PivotField)pt.PivotFields("PartNumber");
            Excel.PivotField pf_ProductUnit = (Excel.PivotField)pt.PivotFields("ProductUnit");
            Excel.PivotField pf_PartDesc = (Excel.PivotField)pt.PivotFields("PartDesc");
            Excel.PivotField pf_UnitCount = (Excel.PivotField)pt.PivotFields("UnitCount");
            Excel.PivotField pf_Pool = (Excel.PivotField)pt.PivotFields("Pool");
            Excel.PivotField pf_DealerC = (Excel.PivotField)pt.PivotFields("Dealer C");
            Excel.PivotField pf_License = (Excel.PivotField)pt.PivotFields("License");
            Excel.PivotField pf_Language = (Excel.PivotField)pt.PivotFields("Language");
            Excel.PivotField pf_PackageType = (Excel.PivotField)pt.PivotFields("PackageType");
            Excel.PivotField pf_ProductType = (Excel.PivotField)pt.PivotFields("ProductType");



            ShowPivotField(pf_ProductUnit, 5);
            ShowPivotField(pf_DealerC, 6);
            ShowPivotField(pf_License, 7);
            ShowPivotField(pf_Language,<img src="http://www.aspnetmania.com/images/em/shades_smile.gif" border="0" alt="Круто" />;
            ShowPivotField(pf_PackageType, 9);
            ShowPivotField(pf_ProductType, 10);

            UsePivotFieldFilter(pf_Language, "Russian", "English", "English/MultiLanguage");
            UsePivotFieldFilter(pf_License, "Single User License");
            UsePivotFieldFilter(pf_PackageType, "Retail");


            bool f = true;
            int k = 1;

            //Отсюда и до конца надо убрать обращения к БД в транзакцию
            //1. Пометить весь прайс старым

            db1.execSQL("update Retail set id_status_price_pos=4");

            

            DateTime tmp = DateTime.Now;
            string date_updated = tmp.ToShortDateString() + " " + tmp.ToShortTimeString();

            int margin = appSettings.Margin;

            while (f)
            {
                try
                {
                    Excel.Range r_PartNumber = (Microsoft.Office.Interop.Excel.Range)pf_PartNumber.DataRange.Cells[k, 1];
                    Excel.Range r_ProductUnit = (Microsoft.Office.Interop.Excel.Range)pf_ProductUnit.DataRange.Cells[k, 1];
                    Excel.Range r_PartDesc = (Microsoft.Office.Interop.Excel.Range)pf_PartDesc.DataRange.Cells[k, 1];
                    Excel.Range r_UnitCount = (Microsoft.Office.Interop.Excel.Range)pf_UnitCount.DataRange.Cells[k, 1];
                    Excel.Range r_Pool = (Microsoft.Office.Interop.Excel.Range)pf_Pool.DataRange.Cells[k, 1];
                    Excel.Range r_DealerC = (Microsoft.Office.Interop.Excel.Range)pf_DealerC.DataRange.Cells[k, 1];
                    Excel.Range r_License = (Microsoft.Office.Interop.Excel.Range)pf_License.DataRange.Cells[k, 1];
                    Excel.Range r_Language = (Microsoft.Office.Interop.Excel.Range)pf_Language.DataRange.Cells[k, 1];
                    Excel.Range r_PackageType = (Microsoft.Office.Interop.Excel.Range)pf_PackageType.DataRange.Cells[k, 1];
                    Excel.Range r_ProductType = (Microsoft.Office.Interop.Excel.Range)pf_ProductType.DataRange.Cells[k, 1];



                    if (r_PartNumber.Text.ToString() == "")
                        f = false;
                    else
                    {
                        float dealerSupplier = allFunctions.getParamFloat(r_DealerC.Text.ToString());
                        float dealerC = dealerSupplier + (dealerSupplier * margin / 100);


                        RetailDetails RD = new RetailDetails(0, r_PartNumber.Text.ToString(), r_ProductUnit.Text.ToString(), r_PartDesc.Text.ToString(),
                                                        "", allFunctions.getParamInt(r_UnitCount.Text.ToString()), r_Pool.Text.ToString(),
                                                        dealerC.ToString(), dealerSupplier.ToString(),
                                                        true, r_License.Text.ToString(), r_Language.Text.ToString(), r_PackageType.Text.ToString(), r_ProductType.Text.ToString(),
                                                        date_updated, 1, "ЛАНИТ", 0, "none.gif", "", true, 0, 0, "", "", false, 0, 0);

                        

                        db1.Cmd = db1.createCommand("sp_update_Retail_position");

                        db1.addParam("@PartNumber", "str", RD.PartNumber);
                        db1.addParam("@ProductUnit", "str", RD.ProductUnit);
                        db1.addParam("@PartDesc", "str", RD.PartDesc);
                        db1.addParam("@Description", "text", RD.Description);
                        db1.addParam("@UnitCount", "int", allFunctions.getParamInt(RD.UnitCount.ToString()));
                        db1.addParam("@Pool", "str", RD.Pool);
                        db1.addParam("@DealerC", "float", allFunctions.getParamFloat(RD.DealerC.ToString()));
                        db1.addParam("@DealerSupplier", "float", allFunctions.getParamFloat(RD.DealerSupplier.ToString()));
                        db1.addParam("@disabled", "int", 1);
                        db1.addParam("@License", "str", RD.License);
                        db1.addParam("@Language", "str", RD.Language);
                        db1.addParam("@PackageType", "str", RD.PackageType);
                        db1.addParam("@ProductType", "str", RD.ProductType);
                        db1.addParam("@date_updated", "str", RD.Date_updated);
                        db1.addParam("@id_status_price_pos", "int", allFunctions.getParamInt(RD.Id_status_price_pos.ToString()));
                        db1.addParam("@supplier", "str", RD.Supplier);
                        db1.addParam("@id_retail_category", "int", allFunctions.getParamInt(RD.Id_retail_category.ToString()));
                        db1.addParam("@image_file_name", "str", RD.Image_file_name);

                        int ret = db1.execCommand();

                        k += 6;
                    }

                    KillObject(r_PartNumber);
                    KillObject(r_ProductUnit);
                    KillObject(r_PartDesc);
                    KillObject(r_UnitCount);
                    KillObject(r_Pool);
                    KillObject(r_DealerC);
                    KillObject(r_License);
                    KillObject(r_Language);
                    KillObject(r_PackageType);
                    KillObject(r_ProductType);


                }
                catch
                {
                    f = false;
                }
            }



            KillObject(pf_PartNumber);
            KillObject(pf_ProductUnit);
            KillObject(pf_PartDesc);
            KillObject(pf_UnitCount);
            KillObject(pf_Pool);
            KillObject(pf_DealerC);
            KillObject(pf_License);
            KillObject(pf_License);
            KillObject(pf_PackageType);
            KillObject(pf_ProductType);
            KillObject(pt);

            db1.execSQL("update Retail set disabled=1 where id_status_price_pos=4");
            ObjWorkBook.Close(false, false, Type.Missing);
            KillObject(ObjWorkBook);

        }
        catch
        {
            retVal = 1;
        }
        finally
        {

            ObjExcel.Quit();
            KillObject(ObjExcel);
        }

        GC.Collect();
        return retVal;
    }