public static List <ExcelPage> OpenExcel()
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "Excel files | *.XLS; *.XLSX; *XLSM;";
ofd.Multiselect = true;
DialogResult result = ofd.ShowDialog();
List <ExcelPage> pages = new List <ExcelPage>();
if (result == DialogResult.OK)
{
foreach (var item in ofd.FileNames)
{
ExcelPage page = new ExcelPage();
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(item);
Excel.Worksheet xlWorksheet = xlWorkbook.Worksheets["Täishinnakiri"];
page.xlApp = xlApp;
page.xlWorkbook = xlWorkbook;
page.xlWorksheet = xlWorksheet;
pages.Add(page);
}
return(pages);
}
else
{
return(null);
}
}
private void WriteExcelToDB(ExcelPage page)
{
Stopwatch watch = new Stopwatch();
watch.Start();
xlApp = page.xlApp;
xlWorkbook = page.xlWorkbook;
xlWorksheet = page.xlWorksheet;
xlRange = xlWorksheet.Cells[3, 4];
string nadalString = Convert.ToString(xlRange.Value);
int nadal = Convert.ToInt32(nadalString.Split(' ')[1]);
watch.Stop();
int SetupTime = watch.Elapsed.Milliseconds;
watch.Reset();
watch.Start();
int arv = 0;
for (int rn = 12; rn < 3000; rn++)
{
xlRange = xlWorksheet.Cells[rn, 4];
if (xlRange.Value != null)
{
//xlRange -- Get information about a certain cell
Ex_mas[arv, 3] = Convert.ToString(xlRange.Value);
xlRange = xlWorksheet.Cells[rn, 1];
Ex_mas[arv, 0] = xlRange.Value;
xlRange = xlWorksheet.Cells[rn, 2];
Ex_mas[arv, 1] = xlRange.Value;
xlRange = xlWorksheet.Cells[rn, 3];
Ex_mas[arv, 2] = xlRange.Value;
if (Ex_mas[arv, 2] != null)
{
arv++;
}
}
JO_Progress_Load.Value++;
}
watch.Stop();
int ExcelTime = watch.Elapsed.Milliseconds;
watch.Reset();
watch.Start();
for (int i = 0; i < arv; i++)
{
//Add Grupp and get ID of it
int GruppID = ExcelQueries.GetGruppID(Ex_mas[i, 0], connection);
if (GruppID == -1)
{
ExcelQueries.InsertGruppToDatbase(Ex_mas[i, 0], connection);
GruppID = ExcelQueries.GetGruppID(Ex_mas[i, 0], connection);
}
//Add Tootja and get ID of it
int TootjaID = ExcelQueries.GetTootjaID(Ex_mas[i, 1], connection);
if (TootjaID == -1)
{
ExcelQueries.InsertTootjaToDatabase(Ex_mas[i, 1], connection);
TootjaID = ExcelQueries.GetTootjaID(Ex_mas[i, 1], connection);
}
//Send toode to database
int TooteID = ExcelQueries.GetTooteID(Ex_mas[i, 2], connection);
if (TooteID == -1)
{
ExcelQueries.InsertToodeToDatabase(Ex_mas[i, 2], GruppID, TootjaID, connection);
TooteID = ExcelQueries.GetTooteID(Ex_mas[i, 2], connection);
}
ExcelQueries.InsertNadalToDatabase(nadal, TooteID, Ex_mas[i, 3], connection);
}
watch.Stop();
int QueriesTime = watch.Elapsed.Milliseconds;
}
