public FrmSearchResult(string fieldSearch, SearchBy searchBy) : this()
        {
            dgvDB.DataSource    = SkladBase.SearchForAdd(fieldSearch, searchBy);
            dgvPrice.DataSource = ExcelSearch.Search(fieldSearch, searchBy);

            this.Text += string.Format($"  ({fieldSearch})");

            gbBD.Text     += "  (" + (dgvDB.DataSource as DataTable).Rows.Count.ToString() + ")";
            gbPrices.Text += "  (" + (dgvPrice.DataSource as DataTable).Rows.Count.ToString() + ")";
        }
Exemple #2
0
        static DataTable GetDataFromXLSX(string fileName)
        {
            DataTable dt = CreateDataTable();

            try
            {
                using (ExcelPackage excel = new ExcelPackage(new FileInfo(fileName)))
                {
                    int startRow;
                    int startCol;
                    if (!ExcelSearch.SearchStartCell(excel, out startRow, out startCol))
                    {
                        MessageBox.Show("В файле \"" + fileName + "\" не найдены данные");
                        return(dt);
                    }

                    ExcelWorksheet ws       = excel.Workbook.Worksheets[1];
                    int            totalRow = ws.Dimension.End.Row;

                    int errorCount = 0;
                    for (int i = startRow; i <= totalRow; i++)
                    {
                        DataRow row = dt.NewRow();

                        //Проверяем на валидность все поля в XLSX и добавляем в DataTable
                        // code
                        int code;
                        if (ws.Cells[i, 1].Value == null || !int.TryParse(ws.Cells[i, 1].Text, out code))
                        {
                            errorCount++;
                            continue;
                        }
                        else
                        {
                            row[0] = code;
                        }

                        // name
                        if (ws.Cells[i, 2].Value == null || ws.Cells[i, 2].Text.Length == 0)
                        {
                            errorCount++;
                            continue;
                        }
                        else
                        {
                            if (ws.Cells[i, 2].Text.Length > 80)
                            {
                                row[1] = ws.Cells[i, 2].Text.Substring(0, 80);
                            }
                            else
                            {
                                row[1] = ws.Cells[i, 2].Text;
                            }
                        }

                        // priceDC
                        decimal priceDC;
                        if (ws.Cells[i, 3].Value == null || !decimal.TryParse(ws.Cells[i, 3].Text.Replace('.', ','), out priceDC))
                        {
                            errorCount++;
                            continue;
                        }
                        else
                        {
                            row[2] = priceDC;
                        }

                        // pricePC
                        decimal pricePC;
                        if (ws.Cells[i, 4].Value == null || !decimal.TryParse(ws.Cells[i, 4].Text.Replace('.', ','), out pricePC))
                        {
                            errorCount++;
                            continue;
                        }
                        else
                        {
                            row[3] = pricePC;
                        }

                        // Quantity
                        int quantity;
                        if (ws.Cells[i, 5].Value == null || !int.TryParse(ws.Cells[i, 5].Text, out quantity))
                        {
                            errorCount++;
                            continue;
                        }
                        else
                        {
                            row[4] = quantity;
                        }

                        // Discont
                        if (ws.Cells[i, 6].Value == null || ws.Cells[i, 6].Text.ToLower() != "да" & ws.Cells[i, 6].Text.ToLower() != "нет")
                        {
                            errorCount++;
                            continue;
                        }
                        else
                        {
                            if (ws.Cells[i, 6].Text.ToLower() == "да")
                            {
                                row[5] = true;
                            }
                            else
                            {
                                row[5] = false;
                            }
                        }

                        // Period
                        int period;
                        if (ws.Cells[i, 7].Value == null || !int.TryParse(ws.Cells[i, 7].Text, out period))
                        {
                            errorCount++;
                            continue;
                        }
                        else
                        {
                            row[6] = period;
                        }

                        // Year
                        int year;
                        if (ws.Cells[i, 8].Value == null || !int.TryParse(ws.Cells[i, 8].Text, out year))
                        {
                            errorCount++;
                            continue;
                        }
                        else
                        {
                            row[7] = year;
                        }

                        // type of catalog
                        if (ws.Cells[i, 9].Value == null || ws.Cells[i, 9].Text.Length == 0)
                        {
                            errorCount++;
                            continue;
                        }
                        else
                        {
                            if (ws.Cells[i, 9].Text.Length > 30)
                            {
                                row[8] = ws.Cells[i, 9].Text.Substring(0, 30);
                            }
                            else
                            {
                                row[8] = ws.Cells[i, 9].Text;
                            }
                        }

                        // category
                        if (ws.Cells[i, 10].Value == null || ws.Cells[i, 10].Text.Length == 0)
                        {
                            errorCount++;
                            continue;
                        }
                        else
                        {
                            if (ws.Cells[i, 10].Text.Length > 25)
                            {
                                row[9] = ws.Cells[i, 10].Text.Substring(0, 25);
                            }
                            else
                            {
                                row[9] = ws.Cells[i, 10].Text;
                            }
                        }

                        // description
                        if (ws.Cells[i, 11].Text.Length > 200)
                        {
                            row[10] = ws.Cells[i, 11].Text.Substring(0, 200);
                        }
                        else
                        {
                            row[10] = ws.Cells[i, 11].Text;
                        }



                        // если всё ок, то добавляем строку
                        dt.Rows.Add(row);
                    }
                    MessageBox.Show($"Добавленно: {totalRow - startRow - errorCount + 1} из {totalRow - startRow + 1} записей" + Environment.NewLine +
                                    $"Пропущенно {errorCount} записей",
                                    (totalRow - startRow - errorCount + 1 != 0) ? "Импорт выполнен успешно" : "Импорт не выполнен", MessageBoxButtons.OK,
                                    (totalRow - startRow - errorCount + 1 != 0) ? MessageBoxIcon.Information : MessageBoxIcon.Error);

                    //Form1 f1 = new Form1();
                    //f1.dataGridView1.DataSource = dt;
                    //f1.ShowDialog();
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            return(dt);
        }