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() + ")";
}
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);
}