private void tsbPaste_Click(object sender, EventArgs e) { DataEditTool.PasteClipboard(3, areaDataGridView, areaBindingSource); }
private void tsbPaste_Click(object sender, EventArgs e) { DataEditTool.PasteClipboard(5, vehicleDataGridView, vehicleBindingSource); }
private void tsbPaste_Click(object sender, EventArgs e) { OpenFileDialog dlg = new OpenFileDialog( ); if (dlg.ShowDialog( ) != DialogResult.OK) { return; } bool clearAll = false; if (MessageBox.Show("是否要先清空資料庫內現有貨單資料?", "現有資料保存與否", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) == DialogResult.Yes) { clearAll = true; } // 展示表單確認欄位 string[] titles = { "物料", "收貨人地址", "淨重", "運輸地區", "檢貨日期", "交貨運輸區" }; int[] fields = new int[6]; Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application(); Workbook theBook = Excel.Workbooks.Open(dlg.FileName); // Worksheet theSheet = theBook.Worksheets["工作表1"]; Worksheet theSheet = theBook.Worksheets[1]; for (int c = 0; c < titles.Length; c++) { int k = 1; while (theSheet.Cells[1, k] != null) { if ((string)theSheet.Cells[1, k].Value == titles[c]) { fields[c] = k; break; } k++; } } int start = ordersDataGridView.RowCount - 1; if (clearAll) { for (int i = vehiclePackingDataSet.Orders.Count - 1; i >= 0; i--) { vehiclePackingDataSet.Orders[i].Delete( ); } // this.vehiclePackingDataSet.Orders.Clear( ); ordersBindingSource.ResetBindings(false); // ordersDataGridView.Rows.Clear( ); //ordersBindingSource.Clear( ); start = 0; } int row = 2; while (theSheet.Cells[row, 1].value != null) { string product = (string)theSheet.Cells[row, fields[0]].Value; float weightKG = (float)theSheet.Cells[row, fields[2]].Value; string address = (string)theSheet.Cells[row, fields[1]].Value; DateTime date = (DateTime)theSheet.Cells[row, fields[4]].Value; int areaID = Convert.ToInt32((string)theSheet.Cells[row, fields[3]].Value); string areaName = (string)theSheet.Cells[row, fields[5]].Value; int key; var aid = from a in vehiclePackingDataSet.Area where a.zipCode == areaID select a.Id; if (aid.Count( ) == 0) { key = DataEditTool.AddAnArea(areaName, areaID); updateAreaCombox( ); } else { key = ((int[])aid.ToArray <int>( ))[0]; } ordersBindingSource.AddNew(); //colAddress colProduct colWeight colLongitude colLatitude colArea colRoutingOrder colDate //int k = 0; //while (!ordersDataGridView.Columns[k].Visible) k++; //ordersDataGridView[k++, start].Value = product; //while (!ordersDataGridView.Columns[k].Visible) k++; //ordersDataGridView[k++, start].Value = weightKG; //while (!ordersDataGridView.Columns[k].Visible) k++; //ordersDataGridView[k++, start].Value = aaids[0]; //while (!ordersDataGridView.Columns[k].Visible) k++; //ordersDataGridView[k++, start].Value = date; ordersDataGridView["colProduct", start].Value = product; ordersDataGridView["colWeight", start].Value = weightKG; ordersDataGridView["colArea", start].Value = key; ordersDataGridView["colDate", start].Value = date; ordersDataGridView["colAddress", start].Value = address; start++; row++; ordersDataGridView.RefreshEdit(); } theBook.Close(); Excel.Quit(); Marshal.ReleaseComObject(theBook); Marshal.ReleaseComObject(Excel); //ex.Workbook wk = Ex }