private void btnDownload_Click(object sender, EventArgs e) { if (!this.rbtnConsumption.Checked && !this.rbtnOriginalGoods.Checked) { MessageBox.Show("Please select consumption or original goods to download first.", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } if (this.dgvConsumption.RowCount == 0 && this.rbtnConsumption.Checked) { MessageBox.Show("The consumption data not exist.", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Stop); this.btnDownload.Focus(); return; } if (this.dgvOriginalGoods.RowCount == 0 && this.rbtnOriginalGoods.Checked) { MessageBox.Show("The original goods data not exist.", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Stop); this.btnDownload.Focus(); return; } DialogResult dlgR = MessageBox.Show("Please select a document format:\n[Yes] Generate Excel as new version;\n[No] Generate Excel as old version;\n[Cancel] Reject to handle.", "Prompt", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Information); if (dlgR == DialogResult.Yes) { #region //new version if (this.rbtnConsumption.Checked && this.dgvConsumption.RowCount > 0) { Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbooks workbooks = excel.Workbooks; Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(true); Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]; string[] strFiledName = { "成品备件号" }; SqlLib lib = new SqlLib(); DataTable dt = lib.SelectDistinct(middleTable, strFiledName); lib.Dispose(0); for (int i = 0; i < dt.Rows.Count; i++) { string strEHB = dt.Rows[i][0].ToString().Trim(); DataRow[] datarow = middleTable.Select("[成品备件号] = '" + strEHB + "' AND ([重复备件号] IS NULL OR [重复备件号] = '')"); if (datarow.Length > 0) { worksheet.Name = strEHB.Replace("/", "X"); worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[datarow.Length + 1, 10]).NumberFormatLocal = "@"; for (int j = 0; j < datarow.Length; j++) { worksheet.Cells[j + 2, 1] = datarow[j][9].ToString().Trim(); worksheet.Cells[j + 2, 2] = datarow[j][0].ToString().Trim(); worksheet.Cells[j + 2, 3] = datarow[j][3].ToString().Trim(); worksheet.Cells[j + 2, 4] = datarow[j][4].ToString().Trim(); worksheet.Cells[j + 2, 5] = datarow[j][5].ToString().Trim(); worksheet.Cells[j + 2, 6] = "0"; worksheet.Cells[j + 2, 7] = datarow[j][2].ToString().Trim(); worksheet.Cells[j + 2, 8] = datarow[j][7].ToString().Trim(); worksheet.Cells[j + 2, 9] = string.Empty; } worksheet.Cells[1, 1] = "手册号"; worksheet.Cells[1, 2] = "成品货物备件号"; worksheet.Cells[1, 3] = "净耗数量"; worksheet.Cells[1, 4] = "数量损耗率"; worksheet.Cells[1, 5] = "重量损耗率"; worksheet.Cells[1, 6] = "总耗重量"; worksheet.Cells[1, 7] = "原料货物备件号"; worksheet.Cells[1, 8] = "废料货物备件号"; worksheet.Cells[1, 9] = "备注"; worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 9]).Font.Bold = true; worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[datarow.Length + 1, 9]).Font.Name = "Verdana"; worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[datarow.Length + 1, 9]).Font.Size = 9; worksheet.Cells.EntireColumn.AutoFit(); if (i < dt.Rows.Count - 1) { object missing = System.Reflection.Missing.Value; worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(missing, missing, missing, missing); } } } dt.Clear(); dt.Dispose(); excel.Visible = true; System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); excel = null; } if (this.rbtnOriginalGoods.Checked && this.dgvOriginalGoods.RowCount > 0) { Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbooks workbooks = excel.Workbooks; Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(true); Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]; worksheet.Name = "OriginalGoods"; worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[this.dgvOriginalGoods.Rows.Count + 1, 6]).NumberFormatLocal = "@"; int iActualRow = 0; for (int k = 0; k < this.dgvOriginalGoods.Rows.Count; k++) { if (String.IsNullOrEmpty(this.dgvOriginalGoods[14, k].Value.ToString().Trim())) { iActualRow++; worksheet.Cells[iActualRow + 1, 1] = this.dgvOriginalGoods[15, k].Value.ToString().Trim(); worksheet.Cells[iActualRow + 1, 2] = this.dgvOriginalGoods[2, k].Value.ToString().Trim(); worksheet.Cells[iActualRow + 1, 3] = this.dgvOriginalGoods[16, k].Value.ToString().Trim(); worksheet.Cells[iActualRow + 1, 4] = "//"; worksheet.Cells[iActualRow + 1, 5] = this.dgvOriginalGoods[17, k].Value.ToString().Trim(); worksheet.Cells[iActualRow + 1, 6] = "1"; } } worksheet.Cells[1, 1] = "货主十位数编码"; worksheet.Cells[1, 2] = "原始货物备件号"; worksheet.Cells[1, 3] = "合并货物备件号"; worksheet.Cells[1, 4] = "规格型号"; worksheet.Cells[1, 5] = "仓库号"; worksheet.Cells[1, 6] = "单位净重"; worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 6]).Font.Bold = true; worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[this.dgvOriginalGoods.Rows.Count + 1, 6]).Font.Name = "Verdana"; worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[this.dgvOriginalGoods.Rows.Count + 1, 6]).Font.Size = 9; worksheet.Cells.EntireColumn.AutoFit(); excel.Visible = true; System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); excel = null; } #endregion } else if (dlgR == DialogResult.No) { #region //old version if (this.rbtnConsumption.Checked && this.dgvConsumption.RowCount > 0) { Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); excel.Application.Workbooks.Add(true); excel.get_Range(excel.Cells[1, 1], excel.Cells[this.dgvConsumption.RowCount + 1, this.dgvConsumption.ColumnCount - 2]).NumberFormatLocal = "@"; int iActualRow = 0, iLineNo = 0; string strBatchFg = String.Empty; for (int i = 0; i < this.dgvConsumption.RowCount; i++) { string strBOM1 = this.dgvConsumption[0, i].Value.ToString().Trim(); if (String.Compare(strBOM1, strBatchFg) == 0) { iLineNo++; } else { iLineNo = 1; strBatchFg = strBOM1; } string strBOM2 = this.dgvConsumption[8, i].Value.ToString().Trim(); //Mapping 'BOM In Customs' decimal dConsump = Convert.ToDecimal(this.dgvConsumption[3, i].Value.ToString().Trim()); //Mapping 'Consumption' if (String.IsNullOrEmpty(strBOM2) && dConsump > 0.0M) { iActualRow++; for (int j = 0; j < this.dgvConsumption.ColumnCount - 2; j++) { if (j == 1) { excel.Cells[iActualRow + 1, j + 1] = iLineNo; } else { excel.Cells[iActualRow + 1, j + 1] = this.dgvConsumption[j, i].Value.ToString().Trim(); } } } } for (int k = 0; k < this.dgvConsumption.ColumnCount - 2; k++) { excel.Cells[1, k + 1] = this.dgvConsumption.Columns[k].HeaderText.ToString().Trim(); } excel.get_Range(excel.Cells[1, 1], excel.Cells[1, this.dgvConsumption.ColumnCount - 2]).Font.Bold = true; excel.get_Range(excel.Cells[1, 1], excel.Cells[1, this.dgvConsumption.ColumnCount - 2]).AutoFilter(1, Type.Missing, Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true); excel.get_Range(excel.Cells[1, 1], excel.Cells[iActualRow + 1, this.dgvConsumption.ColumnCount - 2]).Font.Name = "Verdana"; excel.get_Range(excel.Cells[1, 1], excel.Cells[iActualRow + 1, this.dgvConsumption.ColumnCount - 2]).Font.Size = 9; excel.Cells.EntireColumn.AutoFit(); excel.Visible = true; System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); excel = null; } if (this.rbtnOriginalGoods.Checked && this.dgvOriginalGoods.RowCount > 0) { Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); excel.Application.Workbooks.Add(true); excel.get_Range(excel.Cells[1, 1], excel.Cells[this.dgvOriginalGoods.RowCount + 1, this.dgvOriginalGoods.ColumnCount - 5]).NumberFormatLocal = "@"; //Set the excel cells format as text int iActualRow = 0; for (int m = 0; m < this.dgvOriginalGoods.RowCount; m++) { if (String.IsNullOrEmpty(this.dgvOriginalGoods[14, m].Value.ToString().Trim())) //The same mapping 'BOM In Customs' { iActualRow++; for (int n = 1; n < this.dgvOriginalGoods.ColumnCount - 4; n++) { excel.Cells[iActualRow + 1, n + 1] = this.dgvOriginalGoods[n + 1, m].Value.ToString().Trim(); } } } for (int y = 1; y <= iActualRow; y++) { excel.Cells[y + 1, 1] = y; } //Regenerate the first column value, since it is the serial number for (int x = 1; x < this.dgvOriginalGoods.ColumnCount - 4; x++) { excel.Cells[1, x] = this.dgvOriginalGoods.Columns[x].HeaderText.ToString().Trim(); } excel.get_Range(excel.Cells[1, 1], excel.Cells[1, this.dgvOriginalGoods.ColumnCount - 5]).Font.Bold = true; excel.get_Range(excel.Cells[1, 1], excel.Cells[1, this.dgvOriginalGoods.ColumnCount - 5]).AutoFilter(1, Type.Missing, Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true); excel.get_Range(excel.Cells[1, 1], excel.Cells[iActualRow + 1, this.dgvOriginalGoods.ColumnCount - 5]).Font.Name = "Verdana"; excel.get_Range(excel.Cells[1, 1], excel.Cells[iActualRow + 1, this.dgvOriginalGoods.ColumnCount - 5]).Font.Size = 9; excel.Cells.EntireColumn.AutoFit(); excel.Visible = true; System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); excel = null; } #endregion } }
private void btnApprove_Click(object sender, EventArgs e) { if (this.dgvGongDan.RowCount == 0) { MessageBox.Show("There is no data.", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } if (this.dgvGongDanList.Columns[0].HeaderText == "Select") { MessageBox.Show("Please select GongDan No.", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Stop); return; } this.GetGongDanData(); SqlLib lib = new SqlLib(); string[] strFieldName = { "工单号" }; //dtGongDanList.Columns[0].ColumnName.Trim() DataTable dtGD = lib.SelectDistinct(dtGongDanList, strFieldName); lib.Dispose(0); SqlConnection apprGdConn = new SqlConnection(SqlLib.StrSqlConnection); if (apprGdConn.State == ConnectionState.Closed) { apprGdConn.Open(); } SqlCommand apprGdComm = new SqlCommand(); apprGdComm.Connection = apprGdConn; string strGongDanRecord = null; apprGdComm.CommandText = "SELECT * FROM B_MultiUser"; string strUserName = Convert.ToString(apprGdComm.ExecuteScalar()); if (!String.IsNullOrEmpty(strUserName)) { if (String.Compare(strUserName.Trim().ToUpper(), funcLib.getCurrentUserName().Trim().ToUpper()) != 0) { MessageBox.Show(strUserName + " is handling RM Balance/Drools Balance data. Please wait for him/her to finish the process.", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Stop); apprGdComm.Dispose(); apprGdConn.Dispose(); return; } } else { apprGdComm.Parameters.Add("@UserName", SqlDbType.NVarChar).Value = funcLib.getCurrentUserName().ToUpper(); apprGdComm.CommandText = "INSERT INTO B_MultiUser([UserName]) VALUES(@UserName)"; apprGdComm.ExecuteNonQuery(); apprGdComm.Parameters.Clear(); } DateTime dtApproved = Convert.ToDateTime(System.DateTime.Now.ToString("M/d/yyyy HH:mm")); if (!String.IsNullOrEmpty(this.dtpApprovedDate.Text.Trim())) { dtApproved = Convert.ToDateTime(this.dtpApprovedDate.Value.ToString("M/d/yyyy HH:mm")); } for (int x = 0; x < dtGD.Rows.Count; x++) { string strGongDan = dtGD.Rows[x][0].ToString().Trim(); apprGdComm.Parameters.Add("@GongDanNo", SqlDbType.NVarChar).Value = strGongDan; apprGdComm.CommandText = "SELECT DISTINCT [Actual Start Date], [Actual End Date], [Batch No], [BOM In Customs], [GongDan No], [FG No], [FG Description], " + "[IE Type], [Order No], [GongDan Qty], [Order Price], [Order Currency], CAST([Total RM Qty] AS decimal(18, 6)) AS [Total RM Qty], " + "[Total RM Cost(USD)], CAST([Drools Rate] AS decimal(18,6)) AS [DroolsRate], [CHN Name], [Destination], '" + funcLib.getCurrentUserName() + "' AS [Creater], [Created Date], '" + dtApproved + "' AS [Approval Date] FROM M_DailyGongDan WHERE [GongDan No] = @GongDanNo"; SqlDataAdapter apprGdAdap = new SqlDataAdapter(); apprGdAdap.SelectCommand = apprGdComm; DataTable dtApprGongDanM = new DataTable(); apprGdAdap.Fill(dtApprGongDanM); string strFG = dtApprGongDanM.Rows[0]["FG No"].ToString().Trim(); string strGongDanQty = dtApprGongDanM.Rows[0]["GongDan Qty"].ToString().Trim(); apprGdComm.CommandText = "SELECT [Batch Path], [GongDan No], [Line No], [Item No], [Item Description], [Lot No], [Inventory Type], [RM Category], " + "[RM EHB], [BGD No], CAST([RM Used Qty] AS decimal(18, 6)) AS [RM Used Qty], [RM Currency], [RM Price], " + "CAST([Consumption] AS decimal(18, 6)) AS [Consumption], CAST([Drools Quota] AS decimal(18, 8)) AS [Drools Quota], " + "[Drools EHB] FROM M_DailyGongDan WHERE [GongDan No] = @GongDanNo"; apprGdAdap.SelectCommand = apprGdComm; DataTable dtApprGongDanD = new DataTable(); apprGdAdap.Fill(dtApprGongDanD); apprGdComm.CommandText = "SELECT [GongDan No] AS [工单号], N'加工' AS [生产类型], [FG No] + '/' + [Batch No] AS [成品备件号], [GongDan Qty] AS [工单数量], " + "ROW_NUMBER() OVER (ORDER BY [RM EHB], [BGD No]) AS [项号], [RM EHB] AS [物料备件号], [RM Used Qty] AS [物料耗用数量], " + "[CN] AS [原产国], [BGD No] AS [批次号], [BOM In Customs], '" + dtApproved + "' AS [Created Date] FROM (" + "SELECT M.[GongDan No], SUBSTRING(M.[FG Description],0,CHARINDEX('-',M.[FG Description],CHARINDEX('-',M.[FG Description],0)+1)) AS [FG No], " + "M.[Batch No], M.[GongDan Qty], M.[RM EHB], CAST(SUM(M.[RM Used Qty]) AS decimal(18, 6)) AS [RM Used Qty], N.[CN], " + "M.[BGD No], M.[BOM In Customs] FROM M_DailyGongDan AS M LEFT OUTER JOIN (SELECT C.[Item No], C.[Lot No], B.[CN] FROM C_RMReceiving AS C " + "INNER JOIN B_Country AS B ON C.[Country of Origin] = B.[EN]) AS N ON M.[Item No] = N.[Item No] AND M.[Lot No] = N.[Lot No] " + "WHERE [RM Used Qty] > 0.0 GROUP BY M.[GongDan No], M.[FG Description], M.[Batch No], M.[GongDan Qty], M.[RM EHB], N.[CN], " + "M.[BGD No], M.[BOM In Customs] HAVING [GongDan No] = @GongDanNo) tbgdd"; apprGdAdap.SelectCommand = apprGdComm; DataTable dtApprGongDanDoc = new DataTable(); apprGdAdap.Fill(dtApprGongDanDoc); /*------ check and update 'RM-D' IEType to 'RMB-1418' or 'RMB-D' ------*/ //apprGdComm.CommandText = "SELECT [GongDan No], [IsAllocated] FROM (SELECT M.[GongDan No], B.[IsAllocated], MAX(M.[Line No]) Line, " + // "COUNT(M.[GongDan No]) CountNo FROM M_DailyGongDan AS M LEFT JOIN (SELECT DISTINCT [Grade], [IsAllocated] FROM B_HsCode) AS B " + // "ON SUBSTRING(M.[FG Description], 0, CHARINDEX('-', M.[FG Description], 0)) = B.[Grade] " + // "WHERE M.[IE Type] = 'RM-D' AND M.[RM Category] = 'USD' AND M.[Consumption] > 0.0 GROUP BY M.[GongDan No], " + // "B.[IsAllocated] HAVING [GongDan No] = @GongDanNo) AS dtie WHERE Line = CountNo"; //apprGdAdap.SelectCommand = apprGdComm; //DataTable dtChangeIE = new DataTable(); //apprGdAdap.Fill(dtChangeIE); //apprGdAdap.Dispose(); //foreach(DataRow drChangeIE in dtChangeIE.Rows) //{ // string strGD = drChangeIE[0].ToString().Trim(); // string strAllocated = drChangeIE[1].ToString().Trim(); // DataRow[] drow = dtApprGongDanM.Select("[GongDan No] = '" + strGD + "'"); // if (drow.Length > 0) // { // if (String.Compare(strAllocated, "True") == 0) // { // //foreach (DataRow dr in drow) { dr["IE Type"] = "RMB-D"; } // //strGongDanRecord += strGD + " : RMB-D\n"; // } // else // { // foreach (DataRow dr in drow) { dr["IE Type"] = "RMB-1418"; } // strGongDanRecord += strGD + " : RMB-1418\n"; // } // } //} //dtChangeIE.Dispose(); dtApprGongDanM.AcceptChanges(); #region //Update C_BOM table's column: GongDan Used Qty apprGdComm.Parameters.Clear(); apprGdComm.CommandType = CommandType.StoredProcedure; apprGdComm.CommandText = @"usp_UpdateGongDanUsedQty"; apprGdComm.Parameters.AddWithValue("@GongDanNo", strGongDan); apprGdComm.Parameters.AddWithValue("@BatchNo", strGongDan.Split('-')[0].Trim()); apprGdComm.Parameters.AddWithValue("@Judge", "ADD"); apprGdComm.ExecuteNonQuery(); apprGdComm.Parameters.Clear(); #endregion #region //Handle C_GongDan, C_GongDanDetail, E_GongDan table's data apprGdComm.CommandText = @"usp_InsertGongDan"; apprGdComm.Parameters.AddWithValue("@tvp_GongDanMaster", dtApprGongDanM); apprGdComm.ExecuteNonQuery(); apprGdComm.Parameters.Clear(); dtApprGongDanM.Dispose(); apprGdComm.CommandText = @"usp_InsertGongDanDetail"; apprGdComm.Parameters.AddWithValue("@tvp_GongDanDetail", dtApprGongDanD); apprGdComm.ExecuteNonQuery(); apprGdComm.Parameters.Clear(); dtApprGongDanD.Dispose(); apprGdComm.CommandText = @"usp_InsertGongDanDoc"; apprGdComm.Parameters.AddWithValue("@tvp_GongDanDoc", dtApprGongDanDoc); apprGdComm.ExecuteNonQuery(); apprGdComm.Parameters.Clear(); dtApprGongDanDoc.Dispose(); #endregion #region //Update C_RMBalance table's column: Available RM Balance, GongDan Pending apprGdComm.CommandText = "usp_UpdateRMBalanceByGongDan"; apprGdComm.Parameters.AddWithValue("@GongDanNo", strGongDan); apprGdComm.Parameters.AddWithValue("@Action", "ADD"); apprGdComm.ExecuteNonQuery(); apprGdComm.Parameters.Clear(); #endregion apprGdComm.CommandType = CommandType.Text; apprGdComm.Parameters.Add("@GongDanNo", SqlDbType.NVarChar).Value = strGongDan; apprGdComm.CommandText = "DELETE FROM M_DailyGongDan WHERE [GongDan No] = @GongDanNo"; apprGdComm.ExecuteNonQuery(); apprGdComm.Parameters.Clear(); } dtGD.Dispose(); apprGdComm.CommandText = "DELETE FROM B_MultiUser"; apprGdComm.ExecuteNonQuery(); apprGdComm.Dispose(); if (apprGdConn.State == ConnectionState.Open) { apprGdConn.Close(); apprGdConn.Dispose(); } string strComment = String.IsNullOrEmpty(strGongDanRecord) ? string.Empty : "\nBelow GongDan's (IE Type) convert to RMB-D or RMB-1418 from RM-D.\nPlease inform Logistics team and XFZ !!!" + strGongDanRecord; if (MessageBox.Show("Successfully approve." + strComment, "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Information) == DialogResult.OK) { dtGongDanList.Clear(); this.btnGongDan_Click(sender, e); } }
private void btnGongDan_Click(object sender, EventArgs e) { SqlConnection gdConn = new SqlConnection(SqlLib.StrSqlConnection); if (gdConn.State == ConnectionState.Closed) { gdConn.Open(); } string strSQL = "SELECT [GongDan No] AS [工单号], N'加工' AS [生产类型], [FG No] + '/' + [Batch No] AS [成品备件号], [GongDan Qty] AS [工单数量], " + "0 AS [项号], [RM EHB] AS [物料备件号], [RM Used Qty] AS [物料耗用数量], [CN] AS [原产国], [BGD No] AS [批次号], [BOM In Customs] FROM ( " + "SELECT M.[GongDan No], SUBSTRING(M.[FG Description],0,CHARINDEX('-',M.[FG Description],CHARINDEX('-',M.[FG Description],0)+1)) AS [FG No], " + "M.[Batch No], M.[GongDan Qty], M.[RM EHB], N.[CN], SUM(CAST(M.[RM Used Qty] AS decimal(18,6))) AS [RM Used Qty], M.[BGD No], " + "M.[BOM In Customs] FROM M_DailyGongDan AS M LEFT OUTER JOIN (SELECT C.[Item No], C.[Lot No], B.[CN] FROM C_RMReceiving AS C INNER JOIN B_Country AS B ON C.[Country of Origin] = B.[EN]) AS N ON M.[Item No] = N.[Item No] AND M.[Lot No] = N.[Lot No] " + "WHERE M.[RM Used Qty] > 0.0 GROUP BY M.[GongDan No], M.[FG Description], M.[Batch No], M.[GongDan Qty], M.[RM EHB], N.[CN], " + "M.[BGD No], M.[BOM In Customs]) AS tbgd"; SqlDataAdapter gdAdapter = new SqlDataAdapter(strSQL, gdConn); dtGongDanDoc.Clear(); gdAdapter.Fill(dtGongDanDoc); gdAdapter.Dispose(); SqlLib sqlLib = new SqlLib(); string[] strFieldName = { "工单号" }; DataTable dtGongDanNo = sqlLib.SelectDistinct(dtGongDanDoc, strFieldName); sqlLib.Dispose(0); string strGongDan = ""; int iLine = 0; foreach (DataRow drow in dtGongDanDoc.Rows) { string strGD = drow[0].ToString().Trim(); if (String.Compare(strGongDan, strGD) == 0) { iLine++; drow["项号"] = iLine; } else { strGongDan = strGD; iLine = 1; drow["项号"] = iLine; } } dtGongDanDoc.AcceptChanges(); if (dtGongDanDoc.Rows.Count > 0) { this.dgvGongDan.DataSource = dtGongDanDoc; this.dgvGongDanList.DataSource = dtGongDanNo; } else { this.dgvGongDan.DataSource = DBNull.Value; this.dgvGongDanList.DataSource = DBNull.Value; } this.dgvGongDanList.Columns[0].HeaderText = "Select"; if (gdConn.State == ConnectionState.Open) { gdConn.Close(); gdConn.Dispose(); } }
private void btnDownload_Click(object sender, EventArgs e) { if (!this.rbtnConsumption.Checked && !this.rbtnOriginalGoods.Checked) { MessageBox.Show("Please select consumption or original goods to download first.", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } if (this.dgvConsmpt.RowCount == 0 && this.rbtnConsumption.Checked) { MessageBox.Show("There is no data for consumption.", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } if (this.dgvOrgGoods.RowCount == 0 && this.rbtnOriginalGoods.Checked) { MessageBox.Show("There is no data for Original Goods.", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } DialogResult dlgR = MessageBox.Show("Please select a document format:\n[Yes] Generate Excel as old version to upload to Customs system;\n[No] Generate Excel as new version to keep record and print out paper for Customs declaration;\n[Cancel] Reject to handle.", "Prompt", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Information); if (dlgR == DialogResult.Yes) { #region //Generate Excel as old version to upload to Customs system if (this.rbtnConsumption.Checked && this.dgvConsmpt.RowCount > 0) { Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbooks workbooks = excel.Workbooks; Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(true); Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]; string[] strFiledName = { "成品备件号" }; SqlLib lib = new SqlLib(); DataTable dt = lib.SelectDistinct(dtBomDoc, strFiledName); lib.Dispose(0); for (int i = 0; i < dt.Rows.Count; i++) { string strEHB = dt.Rows[i][0].ToString().Trim(); //if this batch no has no single bonded material, it has to be stopped to submit data for customs declaration. June.29.2017 if (strBatchListWithoutUSDcomponent.IndexOf(strEHB.Substring(strEHB.IndexOf("/") + 1)) > 0) { strEHB = ""; } ; DataRow[] datarow = dtBomDoc.Select("[成品备件号] = '" + strEHB + "' AND ([BOM In Customs] IS NULL OR [BOM In Customs] = '')"); if (datarow.Length > 0) { worksheet.Name = strEHB.Replace("/", "|"); //worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[datarow.Length + 1, 10]).NumberFormatLocal = "@"; for (int j = 0; j < datarow.Length; j++) { worksheet.Cells[j + 2, 1] = datarow[j][9].ToString().Trim(); worksheet.Cells[j + 2, 2] = datarow[j][0].ToString().Trim(); worksheet.Cells[j + 2, 3] = datarow[j][3].ToString().Trim(); worksheet.Cells[j + 2, 4] = datarow[j][4].ToString().Trim(); worksheet.Cells[j + 2, 5] = datarow[j][5].ToString().Trim(); worksheet.Cells[j + 2, 6] = "0"; worksheet.Cells[j + 2, 7] = datarow[j][2].ToString().Trim(); worksheet.Cells[j + 2, 8] = datarow[j][7].ToString().Trim(); worksheet.Cells[j + 2, 9] = string.Empty; } worksheet.Cells[1, 1] = "手册号"; worksheet.Cells[1, 2] = "成品货物备件号"; worksheet.Cells[1, 3] = "净耗数量"; worksheet.Cells[1, 4] = "数量损耗率"; worksheet.Cells[1, 5] = "重量损耗率"; worksheet.Cells[1, 6] = "总耗重量"; worksheet.Cells[1, 7] = "原料货物备件号"; worksheet.Cells[1, 8] = "废料货物备件号"; worksheet.Cells[1, 9] = "备注"; //worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 9]).Font.Bold = true; //worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[datarow.Length + 1, 9]).Font.Name = "Verdana"; //worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[datarow.Length + 1, 9]).Font.Size = 9; worksheet.Cells.EntireColumn.AutoFit(); excel.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignJustify; if (i < dt.Rows.Count - 1) { object missing = System.Reflection.Missing.Value; worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(missing, missing, missing, missing); } } } dt.Clear(); dt.Dispose(); excel.Visible = true; System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); excel = null; } if (this.rbtnOriginalGoods.Checked && this.dgvOrgGoods.RowCount > 0) { Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbooks workbooks = excel.Workbooks; Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(true); Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]; worksheet.Name = "OriginalGoods"; //worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[this.dgvOrgGoods.Rows.Count + 1, 6]).NumberFormatLocal = "@"; int iActualRow = 0; for (int k = 0; k < this.dgvOrgGoods.Rows.Count; k++) { //if this batch no has no single bonded material, it has to be stopped to submit data for customs declaration. June.29.2017 if (strBatchListWithoutUSDcomponent.IndexOf(this.dgvOrgGoods[2, k].Value.ToString().Substring(this.dgvOrgGoods[2, k].Value.ToString().IndexOf("/") + 1)) >= 0) { continue; } ; // Just skip for this iteration if (String.IsNullOrEmpty(this.dgvOrgGoods[14, k].Value.ToString().Trim())) { iActualRow++; worksheet.Cells[iActualRow + 1, 1] = this.dgvOrgGoods[15, k].Value.ToString().Trim(); worksheet.Cells[iActualRow + 1, 2] = this.dgvOrgGoods[2, k].Value.ToString().Trim(); worksheet.Cells[iActualRow + 1, 3] = this.dgvOrgGoods[16, k].Value.ToString().Trim(); worksheet.Cells[iActualRow + 1, 4] = "//"; worksheet.Cells[iActualRow + 1, 5] = this.dgvOrgGoods[17, k].Value.ToString().Trim(); worksheet.Cells[iActualRow + 1, 6] = "1"; } } worksheet.Cells[1, 1] = "货主十位数编码"; worksheet.Cells[1, 2] = "原始货物备件号"; worksheet.Cells[1, 3] = "合并货物备件号"; worksheet.Cells[1, 4] = "规格型号"; worksheet.Cells[1, 5] = "仓库号"; worksheet.Cells[1, 6] = "单位净重"; //worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 6]).Font.Bold = true; //worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[this.dgvOrgGoods.Rows.Count + 1, 6]).Font.Name = "Verdana"; //worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[this.dgvOrgGoods.Rows.Count + 1, 6]).Font.Size = 9; worksheet.Cells.EntireColumn.AutoFit(); excel.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignJustify; excel.Visible = true; System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); excel = null; } #endregion } else if (dlgR == DialogResult.No) { #region //Generate new version of Spreadsheet files for filing purpose if (this.rbtnConsumption.Checked && this.dgvConsmpt.RowCount > 0) { Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); excel.Application.Workbooks.Add(true); // excel.get_Range(excel.Cells[1, 1], excel.Cells[this.dgvConsmpt.RowCount + 1, this.dgvConsmpt.ColumnCount - 2]).NumberFormatLocal = "@"; int iActualRow = 0, iLineNo = 0; string strBatchFg = String.Empty; for (int i = 0; i < this.dgvConsmpt.RowCount; i++) { //if this batch no has no single bonded material, it does not need to be saved for filing purpose. July.20.2017 // Get the value of the third column in the grid, the value looks like 1000-GY7G350T/0006337069 if (strBatchListWithoutUSDcomponent.IndexOf(this.dgvConsmpt[3, i].Value.ToString().Trim().Substring(this.dgvConsmpt[3, i].Value.ToString().Trim().IndexOf("/") + 1)) > 0) { continue; } ; // Just skip for this iteration; string strBOM1 = this.dgvConsmpt[0, i].Value.ToString().Trim(); if (String.Compare(strBOM1, strBatchFg) == 0) { iLineNo++; } else { iLineNo = 1; strBatchFg = strBOM1; } string strBOM2 = this.dgvConsmpt[8, i].Value.ToString().Trim(); //Mapping 'BOM In Customs' decimal dConsump = Convert.ToDecimal(this.dgvConsmpt[3, i].Value.ToString().Trim()); //Mapping 'Consumption' if (String.IsNullOrEmpty(strBOM2) && dConsump > 0.0M) { iActualRow++; for (int j = 0; j < this.dgvConsmpt.ColumnCount - 2; j++) { if (j == 1) { excel.Cells[iActualRow + 1, j + 1] = iLineNo; } else { excel.Cells[iActualRow + 1, j + 1] = this.dgvConsmpt[j, i].Value.ToString().Trim(); } } } } for (int k = 0; k < this.dgvConsmpt.ColumnCount - 2; k++) { excel.Cells[1, k + 1] = this.dgvConsmpt.Columns[k].HeaderText.ToString().Trim(); } //excel.get_Range(excel.Cells[1, 1], excel.Cells[1, this.dgvConsmpt.ColumnCount - 2]).Font.Bold = true; //excel.get_Range(excel.Cells[1, 1], excel.Cells[1, this.dgvConsmpt.ColumnCount - 2]).AutoFilter(1, Type.Missing, Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true); //excel.get_Range(excel.Cells[1, 1], excel.Cells[iActualRow + 1, this.dgvConsmpt.ColumnCount - 2]).Font.Name = "Verdana"; //excel.get_Range(excel.Cells[1, 1], excel.Cells[iActualRow + 1, this.dgvConsmpt.ColumnCount - 2]).Font.Size = 9; excel.Cells.EntireColumn.AutoFit(); excel.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignJustify; excel.Visible = true; System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); excel = null; } if (this.rbtnOriginalGoods.Checked && this.dgvOrgGoods.RowCount > 0) { Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); excel.Application.Workbooks.Add(true); //excel.get_Range(excel.Cells[1, 1], excel.Cells[this.dgvOrgGoods.RowCount + 1, this.dgvOrgGoods.ColumnCount - 5]).NumberFormatLocal = "@"; //Set the excel cells format as text int iActualRow = 0; for (int m = 0; m < this.dgvOrgGoods.RowCount; m++) { //if this batch no has no single bonded material, it does not need to be saved for filing purpose. July.20.2017 // Get the value of the second column in the grid, the value looks like 1000-GY7G350T/0006337069 if (strBatchListWithoutUSDcomponent.IndexOf(this.dgvConsmpt[2, m].Value.ToString().Trim().Substring(this.dgvConsmpt[2, m].Value.ToString().Trim().IndexOf("/") + 1)) > 0) { continue; } ; // Just skip for this iteration; if (String.IsNullOrEmpty(this.dgvOrgGoods[14, m].Value.ToString().Trim())) //The same mapping 'BOM In Customs' { iActualRow++; for (int n = 1; n < this.dgvOrgGoods.ColumnCount - 4; n++) { excel.Cells[iActualRow + 1, n + 1] = this.dgvOrgGoods[n + 1, m].Value.ToString().Trim(); } } } for (int y = 1; y <= iActualRow; y++) { excel.Cells[y + 1, 1] = y; } //Regenerate the first column value, since it is the serial number for (int x = 1; x < this.dgvOrgGoods.ColumnCount - 4; x++) { excel.Cells[1, x] = this.dgvOrgGoods.Columns[x].HeaderText.ToString().Trim(); } //excel.get_Range(excel.Cells[1, 1], excel.Cells[1, this.dgvOrgGoods.ColumnCount - 5]).Font.Bold = true; //excel.get_Range(excel.Cells[1, 1], excel.Cells[1, this.dgvOrgGoods.ColumnCount - 5]).AutoFilter(1, Type.Missing, Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true); //excel.get_Range(excel.Cells[1, 1], excel.Cells[iActualRow + 1, this.dgvOrgGoods.ColumnCount - 5]).Font.Name = "Verdana"; //excel.get_Range(excel.Cells[1, 1], excel.Cells[iActualRow + 1, this.dgvOrgGoods.ColumnCount - 5]).Font.Size = 9; excel.Cells.EntireColumn.AutoFit(); excel.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignJustify; excel.Visible = true; System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); excel = null; } #endregion } }