ButtonReadExcelFile

gistfile1.txt

private void btnGenerate_Click(object sender, EventArgs e)
        {
            Action<string> WriteLine = str => rchText.Text += "\n" + str;
            
            WriteLine("Data generate............");
            
            // инстанция за прочитане на Excel файл
            Microsoft.Office.Interop.Excel.Application xlApplication = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApplication.Workbooks.Open(fileName);
            Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = (Worksheet)xlWorkbook.Worksheets.get_Item(1);
            Microsoft.Office.Interop.Excel.Range startCells = xlWorksheet.Cells[2, 1];
            Microsoft.Office.Interop.Excel.Range endCells = xlWorksheet.Cells[1300, 10];
            Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange.get_Range(startCells, endCells).Cells;

            int rowCount = 0; // xlRange.Rows.Count;
            int colCount = 8; // xlRange.Columns.Count;

            WriteLine($"Rows for read.......{xlRange.Rows.Count}");
            WriteLine($"Column for read.......{xlRange.Columns.Count}");
            
            if (xlRange.Count > 0)
            {
                rowCount = xlRange.Rows.Count;
                colCount = xlRange.Columns.Count;
            }

            const double okis = 0.96;
            var readDataTime = new DateTime();
            var diff = 0.00;
            var metal1 = 0.00;
            var metal1Okis = 0.00;
            var metal2 = 0.00;
            var metal2Okis = 0.00;
            var metal3 = 0.00;
            var metal3Okis = 0.00;
            var totalSmianaOne = 0.0;
            var totalSmianaTwo = 0.0;
            var totalSmianaThree = 0.0;
            List<ExcelTable> excelTable = new List<ExcelTable>();
            var dt = string.Empty;

            for (int i = 1; i <= rowCount; i++)
            {
                for (int j = 2; j <= 2; j++)
                {
                    if (xlRange.Cells[1, 1].Value2 != null)
                    {
                        readDataTime = xlWorksheet.Cells[1 + 1, 1].Value; // взима датата от всяка клетка
                        dt = xlWorksheet.Cells[1 + 1, 3].Value.ToString(); // проверка на изхода от клетката

                    }
                    if (xlRange.Cells[i, 2].Value2 == "1")
                    {
                        ExcelTable curentTable = new ExcelTable();
                        var curentRuda = xlWorksheet.Cells[i + 1, 6].Value;
                        var sumParametur1 = xlWorksheet.Cells[i + 1, 8].Value;
                        var sumParametur6 = xlWorksheet.Cells[i + 1, 10].Value;
                        // колекция от клетки на таблицата
                        curentTable.DataRows = readDataTime = xlWorksheet.Cells[i + 1, 1].Value;
                        curentTable.SmianaRows = xlWorksheet.Cells[i + 1, 2].Value;
                        curentTable.TimeRows = xlWorksheet.Cells[i + 1, 3].Value;
                        curentTable.RudaRows = xlWorksheet.Cells[i + 1, 6].Value;
                        curentTable.Parameter1 = xlWorksheet.Cells[i + 1, 8].Value;
                        curentTable.Parameter5 = xlWorksheet.Cells[i + 1, 9].Value;
                        curentTable.Parameter6 = xlWorksheet.Cells[i + 1, 10].Value;
                        excelTable.Add(curentTable);
                        // изчисление на данни от таблицата
                        metal1 += (curentRuda * sumParametur1) / 100;
                        metal1Okis += (sumParametur6 * (curentRuda * sumParametur1 / 100)) / 100;
                        totalSmianaOne += (int)curentRuda;
                    }
                    else if (xlRange.Cells[i, 2].Value2 == "2")
                    {
                        ExcelTable curentTable = new ExcelTable();
                        var curentRuda = xlWorksheet.Cells[i + 1, 6].Value;
                        var sumParametur1 = xlWorksheet.Cells[i + 1, 8].Value;
                        var sumParametur6 = xlWorksheet.Cells[i + 1, 10].Value;
                        // колекция от клетки на таблицата
                        curentTable.DataRows = readDataTime = xlWorksheet.Cells[i + 1, 1].Value;
                        curentTable.SmianaRows = xlWorksheet.Cells[i + 1, 2].Value;
                        curentTable.TimeRows = xlWorksheet.Cells[i + 1, 3].Value;
                        curentTable.RudaRows = xlWorksheet.Cells[i + 1, 6].Value;
                        curentTable.Parameter1 = xlWorksheet.Cells[i + 1, 8].Value;
                        curentTable.Parameter5 = xlWorksheet.Cells[i + 1, 9].Value;
                        curentTable.Parameter6 = xlWorksheet.Cells[i + 1, 10].Value;
                        excelTable.Add(curentTable);
                        // изчисление на данни от таблицата
                        metal2 += (curentRuda * sumParametur1) / 100;
                        metal2Okis += (sumParametur6 * (curentRuda * sumParametur1 / 100)) / 100;
                        totalSmianaTwo += (int)curentRuda;
                    }
                    else if (xlRange.Cells[i, 2].Value2 == "3")
                    {
                        ExcelTable curentTable = new ExcelTable();
                        var curentRuda = xlWorksheet.Cells[i + 1, 6].Value;
                        var sumParametur1 = xlWorksheet.Cells[i + 1, 8].Value;
                        var sumParametur6 = xlWorksheet.Cells[i + 1, 10].Value;
                        // колекция от клетки на таблицата
                        curentTable.DataRows = readDataTime = xlWorksheet.Cells[i + 1, 1].Value;
                        curentTable.SmianaRows = xlWorksheet.Cells[i + 1, 2].Value;
                        curentTable.TimeRows = xlWorksheet.Cells[i + 1, 3].Value;
                        curentTable.RudaRows = xlWorksheet.Cells[i + 1, 6].Value;
                        curentTable.Parameter1 = xlWorksheet.Cells[i + 1, 8].Value;
                        curentTable.Parameter5 = xlWorksheet.Cells[i + 1, 9].Value;
                        curentTable.Parameter6 = xlWorksheet.Cells[i + 1, 10].Value;
                        excelTable.Add(curentTable);
                        // изчисление на данни от таблицата
                        metal3 += (curentRuda * sumParametur1) / 100;
                        metal3Okis += (sumParametur6 * (curentRuda * sumParametur1 / 100)) / 100;
                        totalSmianaThree += (int)curentRuda;
                    }
                }
            }
            
            // Изписвам изчисленията в datagreedview1
            dtGrid.Rows[0].Cells[0].Value = readDataTime;
            dtGrid.Rows[1].Cells[0].Value = readDataTime;
            dtGrid.Rows[2].Cells[0].Value = readDataTime;
            dtGrid.Rows[3].Cells[0].Value = dt;
            dtGrid.Rows[0].Cells[1].Value = "Смяна 1";
            diff = (metal1 / totalSmianaOne) * 100;
            dtGrid.Rows[0].Cells[2].Value = diff;
            dtGrid.Rows[0].Cells[3].Value = totalSmianaOne;
            dtGrid.Rows[0].Cells[4].Value = metal1Okis * okis;
            dtGrid.Rows[0].Cells[5].Value = (totalSmianaOne * diff) / 100;
            dtGrid.Rows[1].Cells[1].Value = "Смяна 2";
            diff = (metal2 / totalSmianaTwo) * 100;
            dtGrid.Rows[1].Cells[2].Value = diff;
            dtGrid.Rows[1].Cells[3].Value = totalSmianaTwo;
            dtGrid.Rows[1].Cells[4].Value = metal2Okis * okis;
            dtGrid.Rows[1].Cells[5].Value = (totalSmianaTwo * diff) / 100;
            dtGrid.Rows[2].Cells[1].Value = "Смяна 3";
            diff = (metal3 / totalSmianaThree) * 100;
            dtGrid.Rows[2].Cells[2].Value = diff;
            dtGrid.Rows[2].Cells[3].Value = totalSmianaThree;
            dtGrid.Rows[2].Cells[4].Value = metal3Okis * okis;
            dtGrid.Rows[2].Cells[5].Value = (totalSmianaThree * diff) / 100;
            
            // Изписвам uf.eu[rsjd в datagreedview2
            dtgList.DataSource = excelTable;

            WriteLine($"Data is generated...........");
            
            // изчистване на паметта
            GC.Collect();
            GC.WaitForPendingFinalizers();
            WriteLine("Cash is cleaned.............");
            //xlWorkbook.Save();
            xlWorkbook.Close(true, null, null);
            WriteLine("Workbook is closed..........");

            xlApplication.Quit();
            WriteLine("Excel Aplication is closed..........");
            Marshal.ReleaseComObject(xlRange);
            Marshal.ReleaseComObject(xlWorksheet);
            Marshal.ReleaseComObject(xlWorkbook);
            Marshal.ReleaseComObject(xlApplication);
            WriteLine("Release the all resources...........");
            WriteLine("Generate is complete.........");
        }