public int[] importWorkbook(string FileName) { int supplier_id = ExcelSunpower.Default.supplier_id; //int prod_id = 101; int n = 0, m = 0; int[] result = new int[2]; string[] excelPackageList = ExcelSunpower.Default.sp_packeList.Split(','); string[] excelQuantity = ExcelSunpower.Default.sp_quantity.Split(','); string[] excelProdName = ExcelSunpower.Default.sp_prodName.Split(','); System.Globalization.NumberFormatInfo decimalSeperator = new System.Globalization.NumberFormatInfo(); decimalSeperator.NumberDecimalSeparator = "."; decimalSeperator.NumberGroupSeparator = ","; List<object[]> dataArray = new List<object[]>(); //Initialize Excel InterOp and open workbook Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(FileName); //Worksheet count Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1]; Excel.Range xlRange = xlWorksheet.UsedRange; SQLQuery addData = new SQLQuery(); //Add order decimalSeperator to _dataArray object[] tmpArr0 = new object[3]; //total quantity tmpArr0[0] = xlRange.Cells[excelQuantity[0], excelQuantity[1]].Value2; //packing list number tmpArr0[1] = xlRange.Cells[excelPackageList[0], excelPackageList[1]].Value2; //product name tmpArr0[2] = xlRange.Cells[excelProdName[0], excelProdName[1]].Value2; for (int i = 17; i <= xlRange.Rows.Count; i++) { object[] tmpArr1 = new object[xlRange.Columns.Count]; for (int j = 1; j <= xlRange.Columns.Count; j++) { switch (j) { case 1: tmpArr1[j - 1] = Convert.ToInt32(xlRange.Cells[i, j].Value2); break; case 2: tmpArr1[j - 1] = Convert.ToInt32(xlRange.Cells[i, j].Value2); break; case 3: tmpArr1[j - 1] = DateTime.FromOADate(xlRange.Cells[i, j].Value2); break; default: tmpArr1[j - 1] = Convert.ToDouble(xlRange.Cells[i, j].Value2, decimalSeperator); break; } } object[] newArray = new object[tmpArr0.Length + tmpArr1.Length]; Array.Copy(tmpArr0, newArray, tmpArr0.Length); Array.Copy(tmpArr1, 0, newArray, tmpArr0.Length, tmpArr1.Length); if (addData.sql_getPanel(newArray[4]) == false) { //mangler at blive tilrettet værdier vil komme ind forkert addData.loadFlashDataSP(newArray, supplier_id, getProdIDByName(tmpArr0[2].ToString())); n++; } else { string tmpStr = newArray[4].ToString() + " findes allerede i databasen"; m++; } } //Release Excel file and COM Object xlWorkbook.Close(); InterOpMarshal.ReleaseComObject(xlWorkbook); InterOpMarshal.ReleaseComObject(xlApp); InterOpMarshal.FinalReleaseComObject(xlApp); xlApp = null; //Kill excel.exe since it will not exit by releasing COM objectes KillExcel(); result[0] = n; result[1] = m; return result; }
private int getProdIDByName(string supplierName) { SQLQuery getNo = new SQLQuery(); return getNo.getProdNoByName(supplierName); }