private void btnNN_Click(object sender, EventArgs e) { DataTable dtNN = TextUtils.GetDistinctDatatable(dt, "F141"); dtNN.Rows.RemoveAt(0); dtNN.Rows.RemoveAt(0); foreach (DataRow r in dtNN.Rows) { ModuleErrorTypeModel model = new ModuleErrorTypeModel(); model.Name = r["F141"].ToString(); model.Type = 1; ModuleErrorTypeBO.Instance.Insert(model); } }
private void btnFCM_Click(object sender, EventArgs e) { string localPath = ""; FolderBrowserDialog fbd = new FolderBrowserDialog(); if (fbd.ShowDialog() == DialogResult.OK) { localPath = fbd.SelectedPath + "\\FCM - " + thisQuotation.Code + ".xlsx"; } else { return; } string filePath = Application.StartupPath + "\\Templates\\PhongKinhDoanh\\FCM.xlsx"; try { File.Copy(filePath, localPath, true); } catch (Exception ex) { MessageBox.Show("Lỗi: File excel đang được mở." + Environment.NewLine + ex.Message); return; } using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang tạo báo giá...")) { DataTable dtQuotation = LibQLSX.Select("select * from vC_Quotation where ID = " + thisQuotation.ID); DataTable dtAllCost = LibQLSX.Select("exec spReportAllCostWithQuotation " + thisQuotation.ID); DataTable dtGroupP = TextUtils.GetDistinctDatatable(dtAllCost, "GroupCodeP"); System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); Excel.Application app = default(Excel.Application); Excel.Workbook workBoook = default(Excel.Workbook); Excel.Worksheet workSheet = default(Excel.Worksheet); try { app = new Excel.Application(); app.Workbooks.Open(localPath); workBoook = app.Workbooks[1]; workSheet = (Excel.Worksheet)workBoook.Worksheets[1]; workSheet.Cells[3, 4] = TextUtils.ToString(dtQuotation.Rows[0]["Name"]); workSheet.Cells[4, 4] = ""; //workSheet.Cells[5, 4] = TextUtils.ToString(thisQuotation.Code); workSheet.Cells[5, 4] = TextUtils.ToString(thisQuotation.ProjectCode); workSheet.Cells[6, 4] = TextUtils.ToString(thisQuotation.ProjectName); workSheet.Cells[7, 4] = TextUtils.ToString(thisQuotation.CustomerName); workSheet.Cells[8, 4] = TextUtils.ToString(thisQuotation.CustomerName); workSheet.Cells[9, 4] = TextUtils.ToString(dtQuotation.Rows[0]["Name"]); workSheet.Cells[4, 6] = thisQuotation.DeliveryTime; workSheet.Cells[8, 7] = TextUtils.ToString(dtQuotation.Rows[0]["StatusText"]); workSheet.Cells[9, 7] = TextUtils.ToString(thisQuotation.POnumber); workSheet.Cells[13, 7] = TextUtils.ToDecimal(txtTotalHD.EditValue); workSheet.Cells[14, 7] = TextUtils.ToDecimal(txtTotalTPA.EditValue); workSheet.Cells[15, 7] = TextUtils.ToDecimal(txtTax.EditValue); workSheet.Cells[16, 7] = TextUtils.ToDecimal(txtTotalReal.EditValue); workSheet.Cells[17, 7] = TextUtils.ToDecimal(txtTotalTrienKhai.EditValue); workSheet.Cells[18, 7] = TextUtils.ToDecimal(txtTotalNC.EditValue); workSheet.Cells[19, 7] = TextUtils.ToDecimal(txtTotalPB.EditValue); workSheet.Cells[20, 7] = TextUtils.ToDecimal(txtTotalBX.EditValue); workSheet.Cells[21, 7] = TextUtils.ToDecimal(txtCustomerValue.EditValue); workSheet.Cells[22, 7] = TextUtils.ToDecimal(txtTotalProfitHD.EditValue); workSheet.Cells[23, 7] = TextUtils.ToDecimal(txtTotalProfitHD.EditValue); workSheet.Cells[24, 7] = TextUtils.ToDecimal(txtTotalProfitHD.EditValue); workSheet.Cells[26, 7] = TextUtils.ToDecimal(txtTotalVT.EditValue); for (int i = dtGroupP.Rows.Count - 1; i >= 0; i--) { string groupCodeP = TextUtils.ToString(dtGroupP.Rows[i]["GroupCodeP"]); DataRow[] listGroupC = dtAllCost.AsEnumerable() .Where(y => y.Field <string>("GroupCodeP") == groupCodeP) .GroupBy(r => r.Field <string>("GroupCode")) .Select(g => g.First()) .Distinct() .OrderBy(r => r.Field <string>("GroupCode")) .ToArray(); for (int j = listGroupC.Length - 1; j >= 0; j--) { string groupCode = TextUtils.ToString(listGroupC[j]["GroupCode"]); DataRow[] listCost = dtAllCost.AsEnumerable() .Where(y => y.Field <string>("GroupCode") == groupCode) .ToArray(); for (int k = listCost.Length - 1; k >= 0; k--) { //workSheet.Cells[31, 2] = TextUtils.ToString(listCost[k]["Code"]); workSheet.Cells[31, 3] = TextUtils.ToString(listCost[k]["Code"]); workSheet.Cells[31, 4] = TextUtils.ToString(listCost[k]["Name"]); workSheet.Cells[31, 5] = TextUtils.ToString(listCost[k]["Unit"]); workSheet.Cells[31, 6] = TextUtils.ToDecimal(listCost[k]["Qty"]); workSheet.Cells[31, 7] = TextUtils.ToDecimal(listCost[k]["TotalPrice"]); workSheet.Cells[31, 8] = TextUtils.ToDecimal(listCost[k]["TotalPrice"]) / TextUtils.ToDecimal(txtTotalHD.EditValue); ((Excel.Range)workSheet.Rows[31]).Insert(); } workSheet.Cells[31, 2] = TextUtils.ToString(listGroupC[j]["GroupCode"]); workSheet.Cells[31, 3] = TextUtils.ToString(listGroupC[j]["GroupName"]); //workSheet.Cells[31, 4] = TextUtils.ToString(listGroupC[j]["Name"]); workSheet.Cells[31, 5] = "Đồng"; //TextUtils.ToString(listGroupC[j]["Unit"]); workSheet.Cells[31, 6] = 1; // TextUtils.ToDecimal(listGroupC[j]["Qty"]); decimal costC = TextUtils.ToDecimal(dtAllCost.Select("GroupCode = '" + groupCode + "'") .Sum(x => x.Field <decimal>("TotalPrice"))); workSheet.Cells[31, 7] = costC; workSheet.Cells[31, 8] = costC / TextUtils.ToDecimal(txtTotalHD.EditValue); ((Excel.Range)workSheet.Rows[31]).Font.Bold = true; ((Excel.Range)workSheet.Rows[31]).Insert(); } workSheet.Cells[31, 2] = TextUtils.ToString(dtGroupP.Rows[i]["GroupCodeP"]); workSheet.Cells[31, 3] = TextUtils.ToString(dtGroupP.Rows[i]["GroupNameP"]); //workSheet.Cells[31, 4] = TextUtils.ToString(dtGroupP.Rows[i]["Name"]); workSheet.Cells[31, 5] = "Đồng"; //TextUtils.ToString(dtGroupP.Rows[i]["Unit"]); workSheet.Cells[31, 6] = 1; // TextUtils.ToDecimal(dtGroupP.Rows[i]["Qty"]); decimal costP = TextUtils.ToDecimal(dtAllCost.Select("GroupCodeP = '" + groupCodeP + "'") .Sum(x => x.Field <decimal>("TotalPrice")));; workSheet.Cells[31, 7] = costP; workSheet.Cells[31, 8] = costP / TextUtils.ToDecimal(txtTotalHD.EditValue); ((Excel.Range)workSheet.Rows[31]).Font.Bold = true; ((Excel.Range)workSheet.Rows[31]).Insert(); } ((Excel.Range)workSheet.Rows[30]).Delete(); ((Excel.Range)workSheet.Rows[30]).Delete(); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { if (app != null) { app.ActiveWorkbook.Save(); app.Workbooks.Close(); app.Quit(); } } Process.Start(localPath); } }
private void btnFCM_Click(object sender, EventArgs e) { string localPath = ""; FolderBrowserDialog fbd = new FolderBrowserDialog(); if (fbd.ShowDialog() == DialogResult.OK) { localPath = fbd.SelectedPath + "\\FCM-KD-" + Quotation.Code + ".xlsx"; } else { return; } string fileName = ""; if (Quotation.CreatedDepartmentID == 10)//KD1 { fileName = "FCM-KD1.xlsx"; } if (Quotation.CreatedDepartmentID == 16)//KD2 { fileName = "FCM-KD2.xlsx"; } string filePath = Application.StartupPath + "\\Templates\\PhongKinhDoanh\\" + fileName; //string filePath = Application.StartupPath + "\\Templates\\PhongKinhDoanh\\FCM-KD-TEMPLATE.xlsx"; try { File.Copy(filePath, localPath, true); } catch (Exception ex) { MessageBox.Show("Lỗi: File excel đang được mở." + Environment.NewLine + ex.Message); return; } using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang tạo FCM...")) { DataTable dtQuotation = LibQLSX.Select("exec [spGetQuotation] @QuotationID = " + Quotation.ID); DataTable dtAllCost = LibQLSX.Select("exec [spReportCostWithQuotationKD] " + Quotation.ID); DataTable dtGroup = TextUtils.GetDistinctDatatable(dtAllCost, "GroupCode"); System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); Excel.Application app = default(Excel.Application); Excel.Workbook workBoook = default(Excel.Workbook); Excel.Worksheet workSheet = default(Excel.Worksheet); try { app = new Excel.Application(); app.Workbooks.Open(localPath); workBoook = app.Workbooks[1]; workSheet = (Excel.Worksheet)workBoook.Worksheets[1]; workSheet.Cells[4, 4] = "KD"; workSheet.Cells[4, 6] = Quotation.DeliveryTime; workSheet.Cells[5, 4] = TextUtils.ToString(Quotation.ProjectCode); workSheet.Cells[6, 4] = TextUtils.ToString(Quotation.ProjectName); workSheet.Cells[7, 4] = TextUtils.ToString(Quotation.CustomerName); workSheet.Cells[9, 4] = TextUtils.ToString(dtQuotation.Rows[0]["DName"]); workSheet.Cells[8, 7] = TextUtils.ToString(dtQuotation.Rows[0]["StatusText"]); workSheet.Cells[9, 7] = TextUtils.ToString(Quotation.POnumber); //Lợi nhuận workSheet.Cells[13, 7] = TextUtils.ToDecimal(txtTotalProfitTT.EditValue); workSheet.Cells[14, 7] = TextUtils.ToDecimal(txtTotalProfitQD.EditValue); //Giá bán workSheet.Cells[16, 7] = TextUtils.ToDecimal(txtTotalReal.EditValue); //Giá thực thu workSheet.Cells[17, 7] = TextUtils.ToDecimal(txtTotalHD.EditValue); //Giá có VAT thực tế bán trên HĐ workSheet.Cells[18, 7] = TextUtils.ToDecimal(txtTotalHD.EditValue); //Giá chưa VAT thực tế bán trên HĐ workSheet.Cells[19, 7] = TextUtils.ToDecimal(txtTotalTPA.EditValue); //Giá có VAT theo quy định phải bán workSheet.Cells[20, 7] = TextUtils.ToDecimal(txtTotalTPA_PreVAT.EditValue); //Giá chưa VAT theo quy định phải bán //Chi phí workSheet.Cells[22, 7] = TextUtils.ToDecimal(txtTotalVAT.EditValue); //Thuế GTGT phải nộp //Khách hàng gửi if (Quotation.CreatedDepartmentID == 10) //KD1 { workSheet.Cells[23, 7] = Quotation.CustomerCash; //Chênh lệch chi- thu xúc tiền bán hàng workSheet.Cells[24, 7] = TextUtils.ToDecimal(txtTotalXL.EditValue); //Xử lí phần gửi workSheet.Cells[25, 7] = Quotation.CustomerCash + TextUtils.ToDecimal(txtTotalXL.EditValue); //Khách hàng gửi } else { workSheet.Cells[23, 7] = Quotation.CustomerCash - TextUtils.ToDecimal(txtTotalXL.EditValue); //Chênh lệch chi- thu xúc tiền bán hàng workSheet.Cells[24, 7] = TextUtils.ToDecimal(txtTotalXL.EditValue); //Xử lí phần gửi workSheet.Cells[25, 7] = Quotation.CustomerCash; //Khách hàng gửi } workSheet.Cells[26, 7] = Quotation.TotalVC_KD; //Chi phí vận chuyển workSheet.Cells[27, 7] = Quotation.TotalBX_KD; //Chi phí bốc xếp workSheet.Cells[29, 7] = TextUtils.ToDecimal(txtTotalVT_SX.EditValue); //Mua tại TPA workSheet.Cells[30, 7] = TextUtils.ToDecimal(txtTotalVT_MN.EditValue); //Mua ngoài TPA workSheet.Cells[31, 7] = TextUtils.ToDecimal(txtTotalNC.EditValue); //Chi phí cố định - Nhân công gián tiếp workSheet.Cells[32, 7] = TextUtils.ToDecimal(txtTotalNC_KD.EditValue); //Chi phí cố định - Nhân công KD workSheet.Cells[33, 7] = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "GroupCode = 'N08'")); //Chi phí quản lí N08 workSheet.Cells[34, 7] = TextUtils.ToDecimal(txtTotalDP.EditValue); //Chi phí dự phòng workSheet.Cells[38, 7] = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "GroupCode = 'N11'")); //Chi phí bảo hành workSheet.Cells[39, 7] = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "GroupCode = 'N09'")); //Chi phí lãi vay for (int i = dtGroup.Rows.Count - 1; i >= 0; i--) { string groupCode = TextUtils.ToString(dtGroup.Rows[i]["GroupCode"]); DataRow[] listCost = dtAllCost.AsEnumerable() .Where(y => y.Field <string>("GroupCode") == groupCode) .ToArray(); for (int k = listCost.Length - 1; k >= 0; k--) { workSheet.Cells[42, 2] = TextUtils.ToString(listCost[k]["Code"]); workSheet.Cells[42, 3] = TextUtils.ToString(listCost[k]["Name"]); //workSheet.Cells[42, 4] = TextUtils.ToString(listCost[k]["Name"]); workSheet.Cells[42, 5] = "Đồng"; //workSheet.Cells[42, 6] = TextUtils.ToDecimal(listCost[k]["Qty"]); workSheet.Cells[42, 7] = TextUtils.ToDecimal(listCost[k]["TotalPrice"]); ((Excel.Range)workSheet.Rows[42]).Insert(); Excel.Range range = workSheet.get_Range(workSheet.Cells[42, 3], workSheet.Cells[42, 4]); range.Merge(true); } workSheet.Cells[42, 2] = TextUtils.ToString(dtGroup.Rows[i]["GroupCode"]); workSheet.Cells[42, 3] = TextUtils.ToString(dtGroup.Rows[i]["GroupName"]); ((Excel.Range)workSheet.Rows[42]).Font.Bold = true; ((Excel.Range)workSheet.Rows[42]).Insert(); Excel.Range range1 = workSheet.get_Range(workSheet.Cells[42, 3], workSheet.Cells[42, 4]); range1.Merge(true); } ((Excel.Range)workSheet.Rows[41]).Delete(); ((Excel.Range)workSheet.Rows[41]).Delete(); Excel.Worksheet workSheet2 = (Excel.Worksheet)workBoook.Worksheets[2]; DataTable dtItem = LibQLSX.Select("select * from vC_QuotationDetail_KD with(nolock) where ParentID = 0 and C_QuotationID = " + Quotation.ID); for (int i = dtItem.Rows.Count - 1; i >= 0; i--) { int id = TextUtils.ToInt(dtItem.Rows[i]["ID"]); DataTable dtC = LibQLSX.Select("select * from vC_QuotationDetail_KD with(nolock) where ParentID = " + id); for (int j = dtC.Rows.Count - 1; j >= 0; j--) { workSheet2.Cells[5, 1] = (i + 1) + "." + (j + 1); workSheet2.Cells[5, 2] = TextUtils.ToString(dtC.Rows[j]["ModuleName"]); workSheet2.Cells[5, 3] = TextUtils.ToString(dtC.Rows[j]["ModuleCode"]); workSheet2.Cells[5, 4] = TextUtils.ToString(dtC.Rows[j]["Manufacture"]); workSheet2.Cells[5, 5] = TextUtils.ToString(dtC.Rows[j]["Origin"]); workSheet2.Cells[5, 6] = TextUtils.ToString(dtC.Rows[j]["DCode"]); workSheet2.Cells[5, 7] = TextUtils.ToString(dtC.Rows[j]["C_ProductGroupCode"]); workSheet2.Cells[5, 8] = TextUtils.ToString(dtC.Rows[j]["Qty"]); workSheet2.Cells[5, 17] = TextUtils.ToString(dtC.Rows[j]["PriceVT"]); workSheet2.Cells[5, 18] = TextUtils.ToDecimal(dtC.Rows[j]["VAT"]) / 100; workSheet2.Cells[5, 22] = TextUtils.ToString(dtC.Rows[j]["PriceTPA_PreVAT"]); workSheet2.Cells[5, 23] = TextUtils.ToString(dtC.Rows[j]["PriceTPA"]); workSheet2.Cells[5, 24] = TextUtils.ToString(dtC.Rows[j]["PriceVAT_HD"]); workSheet2.Cells[5, 25] = TextUtils.ToDecimal(dtC.Rows[j]["PriceHD"]) * (1 - TextUtils.ToDecimal(dtC.Rows[j]["VAT"]) / 100); workSheet2.Cells[5, 26] = TextUtils.ToDecimal(dtC.Rows[j]["Qty"]) * TextUtils.ToDecimal(dtC.Rows[j]["PriceHD"]); workSheet2.Cells[5, 27] = TextUtils.ToDecimal(dtC.Rows[j]["Qty"]) * TextUtils.ToDecimal(dtC.Rows[j]["PriceVT"]); ((Excel.Range)workSheet2.Rows[5]).Insert(); } workSheet2.Cells[5, 1] = i + 1; workSheet2.Cells[5, 2] = TextUtils.ToString(dtItem.Rows[i]["ModuleName"]); workSheet2.Cells[5, 3] = TextUtils.ToString(dtItem.Rows[i]["ModuleCode"]); workSheet2.Cells[5, 4] = TextUtils.ToString(dtItem.Rows[i]["Manufacture"]); workSheet2.Cells[5, 5] = TextUtils.ToString(dtItem.Rows[i]["Origin"]); workSheet2.Cells[5, 6] = TextUtils.ToString(dtItem.Rows[i]["DCode"]); workSheet2.Cells[5, 8] = TextUtils.ToString(dtItem.Rows[i]["Qty"]); if (dtC.Rows.Count == 0) { workSheet2.Cells[5, 7] = TextUtils.ToString(dtItem.Rows[i]["C_ProductGroupCode"]); workSheet2.Cells[5, 17] = TextUtils.ToString(dtItem.Rows[i]["PriceVT"]); workSheet2.Cells[5, 18] = TextUtils.ToDecimal(dtItem.Rows[i]["VAT"]) / 100; workSheet2.Cells[5, 22] = TextUtils.ToString(dtItem.Rows[i]["PriceTPA_PreVAT"]); workSheet2.Cells[5, 23] = TextUtils.ToString(dtItem.Rows[i]["PriceTPA"]); workSheet2.Cells[5, 24] = TextUtils.ToString(dtItem.Rows[i]["PriceVAT_HD"]); workSheet2.Cells[5, 25] = TextUtils.ToDecimal(dtItem.Rows[i]["PriceHD"]) * (1 - TextUtils.ToDecimal(dtItem.Rows[i]["VAT"]) / 100); workSheet2.Cells[5, 26] = TextUtils.ToDecimal(dtItem.Rows[i]["Qty"]) * TextUtils.ToDecimal(dtItem.Rows[i]["PriceHD"]); workSheet2.Cells[5, 27] = TextUtils.ToDecimal(dtItem.Rows[i]["Qty"]) * TextUtils.ToDecimal(dtItem.Rows[i]["PriceVT"]); } ((Excel.Range)workSheet2.Rows[5]).Font.Bold = true; ((Excel.Range)workSheet2.Rows[5]).Insert(); } ((Excel.Range)workSheet2.Rows[4]).Delete(); ((Excel.Range)workSheet2.Rows[4]).Delete(); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { if (app != null) { app.ActiveWorkbook.Save(); app.Workbooks.Close(); app.Quit(); } } Process.Start(localPath); } }
private void btnFCM_Click(object sender, EventArgs e) { string localPath = ""; FolderBrowserDialog fbd = new FolderBrowserDialog(); if (fbd.ShowDialog() == DialogResult.OK) { localPath = fbd.SelectedPath + "\\FCM-SX- " + Quotation.Code + ".xlsx"; } else { return; } string filePath = Application.StartupPath + "\\Templates\\PhongKinhDoanh\\FCM-SX-TEMPLATE.xlsx"; try { File.Copy(filePath, localPath, true); } catch (Exception ex) { MessageBox.Show("Lỗi: File excel đang được mở." + Environment.NewLine + ex.Message); return; } using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang tạo FCM...")) { DataTable dtQuotation = LibQLSX.Select("exec spGetQuotation @QuotationID = " + Quotation.ID); DataTable dtAllCost = new DataTable(); if (Quotation.StatusNC == 1) { dtAllCost = LibQLSX.Select("exec [spReportCostWithQuotationSX_DN] " + Quotation.ID); } else { dtAllCost = LibQLSX.Select("exec [spReportCostWithQuotationSX_CN] " + Quotation.ID); } decimal totalNC_ThietKe1 = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "Code = 'C09P11' and Code = 'C09P24'")); DataTable dtGroup = TextUtils.GetDistinctDatatable(dtAllCost, "GroupCode"); System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); Excel.Application app = default(Excel.Application); Excel.Workbook workBoook = default(Excel.Workbook); Excel.Worksheet workSheet = default(Excel.Worksheet); try { app = new Excel.Application(); app.Workbooks.Open(localPath); workBoook = app.Workbooks[1]; workSheet = (Excel.Worksheet)workBoook.Worksheets[1]; workSheet.Cells[4, 4] = "KD"; workSheet.Cells[4, 6] = Quotation.DeliveryTime; workSheet.Cells[5, 4] = TextUtils.ToString(Quotation.ProjectCode); workSheet.Cells[6, 4] = TextUtils.ToString(Quotation.ProjectName); workSheet.Cells[7, 4] = TextUtils.ToString(Quotation.CustomerName); workSheet.Cells[8, 8] = TextUtils.ToString(dtQuotation.Rows[0]["StatusText"]); workSheet.Cells[9, 4] = TextUtils.ToString(dtQuotation.Rows[0]["DName"]); workSheet.Cells[9, 7] = TextUtils.ToString(Quotation.POnumber); //Lợi nhuận workSheet.Cells[14, 8] = TextUtils.ToDecimal(txtTotalProfit.EditValue); //Giá bán workSheet.Cells[16, 8] = TextUtils.ToDecimal(txtTotalTPA.EditValue);//Giá bán chưa VAT workSheet.Cells[16, 9] = TextUtils.ToDecimal(txtTotalHD.EditValue); //Chi phí vật tư workSheet.Cells[19, 8] = TextUtils.ToDecimal(txtTotalVT.EditValue);//Giá vật tư workSheet.Cells[19, 9] = TextUtils.ToDecimal(txtTotalVT.EditValue); decimal totalNC_GianTiep = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "GroupCode = 'N01'")); workSheet.Cells[23, 6] = totalNC_GianTiep / TextUtils.ToDecimal(txtTotalTPA.EditValue); workSheet.Cells[23, 8] = totalNC_GianTiep; workSheet.Cells[23, 9] = totalNC_GianTiep;//Chi phí cố định - đầu nhân viên gián tiếp //Chi phí phân bổ khác decimal totalPB_QuanLy = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "GroupCode = 'N08'")); workSheet.Cells[35, 6] = totalPB_QuanLy / TextUtils.ToDecimal(txtTotalTPA.EditValue); workSheet.Cells[35, 9] = totalPB_QuanLy;//Chi phí quản lí decimal totalPB_TaiChinh = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "GroupCode = 'N09'")); workSheet.Cells[36, 6] = totalPB_TaiChinh / TextUtils.ToDecimal(txtTotalTPA.EditValue); workSheet.Cells[36, 9] = totalPB_TaiChinh; //Chi phí tài chính và lãi vay workSheet.Cells[37, 9] = TextUtils.ToDecimal(txtTotalDP.EditValue); //Chi phí dự phòng decimal totalPB_BaoHanh = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "GroupCode = 'N11'")); workSheet.Cells[43, 6] = totalPB_BaoHanh / TextUtils.ToDecimal(txtTotalTPA.EditValue); workSheet.Cells[43, 9] = totalPB_BaoHanh;//Chi phí bảo hành if (Quotation.StatusNC == 1) { //Chi phí nhân công decimal totalNC_ThietKe = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "GroupCode = 'N02'")); workSheet.Cells[24, 6] = totalNC_ThietKe / TextUtils.ToDecimal(txtTotalTPA.EditValue); workSheet.Cells[24, 9] = totalNC_ThietKe;//Chi phí cố định - đầu nhân viên Tkế decimal totalNC_SXLR = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "GroupCode = 'N03'")); workSheet.Cells[25, 6] = totalNC_SXLR / TextUtils.ToDecimal(txtTotalTPA.EditValue); workSheet.Cells[25, 9] = totalNC_SXLR;//Chi phí cố định - đầu nhân viên SXLR //Chi phí kỹ thuật workSheet.Cells[28, 9] = TextUtils.ToDecimal(txtTotalVC.EditValue); //Chi phí vận chuyển hàng bán workSheet.Cells[29, 9] = TextUtils.ToDecimal(txtTotalDiLai.EditValue); // Chi phí đi lại //workSheet.Cells[27, 9] = TextUtils.ToDecimal(txtTotalVC.EditValue);//Chi phí vận chuyển hàng bán workSheet.Cells[30, 9] = TextUtils.ToDecimal(txtTotalBX.EditValue);//Chi phí bốc xếp hàng bán decimal totalKT_Service = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "GroupCode = 'N04'")); workSheet.Cells[31, 6] = totalKT_Service / TextUtils.ToDecimal(txtTotalTPA.EditValue); workSheet.Cells[31, 9] = totalKT_Service;//Chi phí cố bộ phận Service (Lắp đặt, chuyển giao, follow) decimal totalKT_SXLR = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "GroupCode = 'N07.02'")); workSheet.Cells[32, 6] = totalKT_SXLR / TextUtils.ToDecimal(txtTotalTPA.EditValue); workSheet.Cells[32, 9] = totalKT_SXLR;//Chi phí bộ phận SXLR (Lắp đặt, chuyển giao, follow) decimal totalKT_TK = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "GroupCode = 'N07.03'")); workSheet.Cells[33, 6] = totalKT_TK / TextUtils.ToDecimal(txtTotalTPA.EditValue); workSheet.Cells[33, 9] = totalKT_TK;//Chi phí bộ phận thiết kế (Lắp đặt, chuyển giao, follow) } else { //Chi phí nhân công decimal totalNC_ThietKe = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "Code = 'C09P11' and Code = 'C09P24'")); workSheet.Cells[24, 6] = totalNC_ThietKe / TextUtils.ToDecimal(txtTotalTPA.EditValue); workSheet.Cells[24, 9] = totalNC_ThietKe;//Chi phí cố định - đầu nhân viên Tkế decimal totalNC_SXLR = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "Code = 'C09P07'")); workSheet.Cells[25, 6] = totalNC_SXLR / TextUtils.ToDecimal(txtTotalTPA.EditValue); workSheet.Cells[25, 9] = totalNC_SXLR;//Chi phí cố định - đầu nhân viên SXLR //Chi phí kỹ thuật workSheet.Cells[28, 9] = TextUtils.ToDecimal(txtTotalVC.EditValue); //Chi phí vận chuyển hàng bán workSheet.Cells[29, 9] = TextUtils.ToDecimal(txtTotalDiLai.EditValue); // Chi phí đi lại //workSheet.Cells[27, 9] = TextUtils.ToDecimal(txtTotalVC.EditValue);//Chi phí vận chuyển hàng bán //workSheet.Cells[28, 9] = TextUtils.ToDecimal(txtTotalBX.EditValue);//Chi phí bốc xếp hàng bán decimal totalKT_Service = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "Code = 'C09P12'")); workSheet.Cells[31, 6] = totalKT_Service / TextUtils.ToDecimal(txtTotalTPA.EditValue); workSheet.Cells[31, 9] = totalKT_Service;//Chi phí cố bộ phận Service (Lắp đặt, chuyển giao, follow) decimal totalKT_SXLR = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "Code = 'PLD'")); workSheet.Cells[32, 6] = totalKT_SXLR / TextUtils.ToDecimal(txtTotalTPA.EditValue); workSheet.Cells[32, 9] = totalKT_SXLR;//Chi phí bộ phận SXLR (Lắp đặt, chuyển giao, follow) decimal totalKT_TK = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "Code = 'PCG'")); workSheet.Cells[33, 6] = totalKT_TK / TextUtils.ToDecimal(txtTotalTPA.EditValue); workSheet.Cells[33, 9] = totalKT_TK;//Chi phí bộ phận thiết kế (Lắp đặt, chuyển giao, follow) } for (int i = dtGroup.Rows.Count - 1; i >= 0; i--) { string groupCode = TextUtils.ToString(dtGroup.Rows[i]["GroupCode"]); DataRow[] listCost = dtAllCost.AsEnumerable() .Where(y => y.Field <string>("GroupCode") == groupCode) .ToArray(); for (int k = listCost.Length - 1; k >= 0; k--) { workSheet.Cells[46, 2] = TextUtils.ToString(listCost[k]["Code"]); workSheet.Cells[46, 3] = TextUtils.ToString(listCost[k]["Name"]); //workSheet.Cells[46, 4] = TextUtils.ToString(listCost[k]["Name"]); workSheet.Cells[46, 5] = "Đồng"; //workSheet.Cells[46, 6] = TextUtils.ToDecimal(listCost[k]["Qty"]); workSheet.Cells[46, 8] = TextUtils.ToDecimal(listCost[k]["TotalPrice"]); ((Excel.Range)workSheet.Rows[46]).Insert(); Excel.Range range = workSheet.get_Range(workSheet.Cells[46, 3], workSheet.Cells[46, 4]); range.Merge(true); } workSheet.Cells[46, 2] = TextUtils.ToString(dtGroup.Rows[i]["GroupCode"]); workSheet.Cells[46, 3] = TextUtils.ToString(dtGroup.Rows[i]["GroupName"]); ((Excel.Range)workSheet.Rows[46]).Font.Bold = true; ((Excel.Range)workSheet.Rows[46]).Insert(); Excel.Range range1 = workSheet.get_Range(workSheet.Cells[46, 3], workSheet.Cells[46, 4]); range1.Merge(true); } ((Excel.Range)workSheet.Rows[45]).Delete(); ((Excel.Range)workSheet.Rows[45]).Delete(); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { if (app != null) { app.ActiveWorkbook.Save(); app.Workbooks.Close(); app.Quit(); } } Process.Start(localPath); } }
private void btnExportFormBank_Click(object sender, EventArgs e) { bool isTrongNuoc = true; DialogResult result = MessageBox.Show("Bạn muốn xuất các biểu mẫu loại trong nước?", TextUtils.Caption, MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question); if (result == DialogResult.Yes) { isTrongNuoc = true; } else if (result == DialogResult.No) { isTrongNuoc = false; } else { return; } string filePath = ""; OpenFileDialog ofd = new OpenFileDialog(); ofd.Multiselect = false; if (ofd.ShowDialog() == DialogResult.OK) { filePath = ofd.FileName; } else { return; } DataTable dtItem = TextUtils.ExcelToDatatableNoHeader(filePath, "Sheet1"); for (int i = 0; i < 3; i++) { dtItem.Rows.RemoveAt(0); } DataTable dtDistinctItem = TextUtils.GetDistinctDatatable(dtItem, "F4"); string path = ""; FolderBrowserDialog fbd = new FolderBrowserDialog(); if (fbd.ShowDialog() == DialogResult.OK) { path = fbd.SelectedPath; } else { return; } using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang tạo mẫu lệnh chi...")) { #region Mẫu lệnh chi for (int i = 0; i < dtDistinctItem.Rows.Count; i++) //for (int i = 0; i < 1; i++) { string stt = TextUtils.ToString(dtDistinctItem.Rows[i]["F1"]); if (stt == "") { continue; } string filePathS = Application.StartupPath + "\\Templates\\PhongKeToan\\NganHang\\Mau lenh chi.xls"; string currentPath = path + "\\Mau lenh chi-" + TextUtils.ToString(dtDistinctItem.Rows[i]["F2"]) + ".xls"; try { File.Copy(filePathS, currentPath, true); } catch (Exception ex) { MessageBox.Show("Có lỗi khi tạo biểu mẫu lệnh chi!" + Environment.NewLine + ex.Message, TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop); continue; } System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); Excel.Application app = default(Excel.Application); Excel.Workbook workBoook = default(Excel.Workbook); Excel.Worksheet workSheet = default(Excel.Worksheet); try { app = new Excel.Application(); app.Workbooks.Open(currentPath); workBoook = app.Workbooks[1]; workSheet = (Excel.Worksheet)workBoook.Worksheets[1]; string loaiTienTe = TextUtils.ToString(dtDistinctItem.Rows[i]["F13"]); string percentThanhToan = TextUtils.ToString(dtDistinctItem.Rows[i]["F14"]); string soTaiKhoan = TextUtils.ToString(dtDistinctItem.Rows[i]["F4"]); string soHoaDonHopDong = ""; double tienNhanNo = 0; DataRow[] drs = dtItem.Select("F4 = '" + soTaiKhoan + "'"); foreach (DataRow item in drs) { tienNhanNo += TextUtils.ToDouble(item["F9"]); soHoaDonHopDong += TextUtils.ToString(item["F5"]) + ","; } workSheet.Cells[7, 1] = " Số No………Ngày Date ……/" + string.Format("{0:00}", DateTime.Now.Month) + "/" + DateTime.Now.Year; workSheet.Cells[12, 3] = TextUtils.ToString(dtDistinctItem.Rows[i]["F2"]); //Bên thụ hưởng workSheet.Cells[15, 4] = TextUtils.ToString(dtDistinctItem.Rows[i]["F12"]); //địa chỉ bên thụ hưởng if (isTrongNuoc) { workSheet.Cells[13, 3] = "'" + soTaiKhoan; workSheet.Cells[16, 4] = tienNhanNo.ToString("n2") + " " + loaiTienTe; //Số tiền nhận nợ workSheet.Cells[17, 4] = TextUtils.NumericToString(tienNhanNo, loaiTienTe); //tiền nhận nợ bẳng chữ } workSheet.Cells[21, 3] = "Công ty cp tự động hóa Tân phát thanh toán " + tienNhanNo + " " + (loaiTienTe == "" ? "VNĐ" : loaiTienTe) + " tiền hợp đồng " + soHoaDonHopDong.Substring(0, soHoaDonHopDong.Length - 1); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { if (app != null) { app.ActiveWorkbook.Save(); app.Workbooks.Close(); app.Quit(); } } } #endregion Mẫu lệnh chi } using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang tạo Giấy nhận nợ...")) { #region Giấy nhận nợ Word.Application word = new Word.Application(); Word.Document doc = new Word.Document(); try { string localFilePath = ""; if (isTrongNuoc) { localFilePath = Application.StartupPath + "\\Templates\\PhongKeToan\\NganHang\\GiayNhanNo_VND.docx"; } else { localFilePath = Application.StartupPath + "\\Templates\\PhongKeToan\\NganHang\\GiayNhanNo_USD.docx"; } string currentFilePath = path + "\\GiayNhanNo-" + DateTime.Now.ToString("ddMMyyyy") + ".docx"; try { File.Copy(localFilePath, currentFilePath, true); } catch (Exception ex) { MessageBox.Show("Có lỗi khi tạo biểu Giấy nhận nợ!" + Environment.NewLine + ex.Message, TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop); return; } doc = word.Documents.Open(currentFilePath); doc.Activate(); decimal tong1 = 0; decimal tong2 = 0; for (int i = 0; i < dtItem.Rows.Count; i++) { int stt = TextUtils.ToInt(dtItem.Rows[i]["F1"]); decimal tienHoaDon = TextUtils.ToDecimal(dtItem.Rows[i]["F8"]); decimal tienNhanNo = TextUtils.ToDecimal(dtItem.Rows[i]["F9"]); string loaiTienTe = TextUtils.ToString(dtItem.Rows[i]["F13"]); tong1 += tienHoaDon; tong2 += tienNhanNo; TextUtils.RepalaceText(doc, "<thuHuong" + stt + ">", TextUtils.ToString(dtItem.Rows[i]["F2"])); TextUtils.RepalaceText(doc, "<nganHang" + stt + ">", TextUtils.ToString(dtItem.Rows[i]["F3"])); TextUtils.RepalaceText(doc, "<soTaiKhoan" + stt + ">", TextUtils.ToString(dtItem.Rows[i]["F4"])); TextUtils.RepalaceText(doc, "<soHoaDon" + stt + ">", TextUtils.ToString(dtItem.Rows[i]["F5"])); //TextUtils.RepalaceText(doc, "<ngay" + stt + ">", TextUtils.ToDate3(dtItem.Rows[i]["F6"]).ToString("dd/MM/yyyy")); TextUtils.RepalaceText(doc, "<ngay" + stt + ">", TextUtils.ToString(dtItem.Rows[i]["F6"])); TextUtils.RepalaceText(doc, "<matHang" + stt + ">", TextUtils.ToString(dtItem.Rows[i]["F7"])); TextUtils.RepalaceText(doc, "<tienHoaDon" + stt + ">", tienHoaDon.ToString("n2")); TextUtils.RepalaceText(doc, "<tienNhanNo" + stt + ">", tienNhanNo.ToString("n2")); } TextUtils.RepalaceText(doc, "<tong1>", tong1.ToString("n2")); TextUtils.RepalaceText(doc, "<tong2>", tong2.ToString("n2")); TextUtils.RepalaceText(doc, "<bangchu>", TextUtils.NumericToString((double)tong2, isTrongNuoc ? "" : "USD")); TextUtils.RepalaceText(doc, "<month>", string.Format("{0:00}", DateTime.Now.Month)); TextUtils.RepalaceText(doc, "<year>", DateTime.Now.Year.ToString()); doc.Save(); doc.Close(); word.Quit(); } catch (Exception ex) { MessageBox.Show(ex.Message, "Tân Phát", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { TextUtils.ReleaseComObject(doc); TextUtils.ReleaseComObject(word); } #endregion Giấy nhận nợ } if (isTrongNuoc) { return; } using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang tạo Hợp đồng ngoại tệ...")) { #region Hợp đồng ngoại tệ Word.Application word = new Word.Application(); Word.Document doc = new Word.Document(); try { string localFilePath = Application.StartupPath + "\\Templates\\PhongKeToan\\NganHang\\HopDongMuaBanNgoaiTe.docx"; string currentFilePath = path + "\\HopDongMuaBanNgoaiTe-" + DateTime.Now.ToString("ddMMyyyy") + ".docx"; try { File.Copy(localFilePath, currentFilePath, true); } catch (Exception ex) { MessageBox.Show("Có lỗi khi tạo biểu Hợp đồng mua bán ngoại tệ!" + Environment.NewLine + ex.Message, TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop); return; } doc = word.Documents.Open(currentFilePath); doc.Activate(); for (int i = 0; i < dtDistinctItem.Rows.Count; i++) { int f1 = TextUtils.ToInt(dtDistinctItem.Rows[i]["F1"]); if (f1 == 0) { continue; } int stt = f1; //i + 1; string loaiTienTe = TextUtils.ToString(dtDistinctItem.Rows[i]["F13"]); string soTaiKhoan = TextUtils.ToString(dtDistinctItem.Rows[i]["F4"]); double tienNhanNo = 0; string noiDung = ""; DataRow[] drs = dtItem.Select("F4 = '" + soTaiKhoan + "'"); if (drs.Length > 1) { foreach (DataRow item in drs) { tienNhanNo += TextUtils.ToDouble(item["F9"]); string soHoaDonHopDong = TextUtils.ToString(item["F5"]); string ngay = TextUtils.ToDate3(item["F6"]).ToString("dd/MM/yyyy"); string percentThanhToan = TextUtils.ToString(item["F14"]); if (percentThanhToan != "") { noiDung += "Thanh toán trả trước nốt " + percentThanhToan + "% hợp đồng số " + soHoaDonHopDong + " ngày " + ngay + Environment.NewLine; } else { noiDung += "Thanh toán tiền hàng hợp đồng số " + soHoaDonHopDong + " ngày " + ngay + Environment.NewLine; } } } else { tienNhanNo = TextUtils.ToDouble(drs[0]["F9"]); string soHoaDonHopDong = TextUtils.ToString(drs[0]["F5"]); string ngay = TextUtils.ToDate3(drs[0]["F6"]).ToString("dd/MM/yyyy"); string percentThanhToan = TextUtils.ToString(drs[0]["F14"]); if (percentThanhToan != "") { noiDung = "Thanh toán trả trước nốt " + percentThanhToan + "% hợp đồng số " + soHoaDonHopDong + " ngày " + ngay; } else { noiDung = "Thanh toán tiền hàng hợp đồng số " + soHoaDonHopDong + " ngày " + ngay; } } int length = noiDung.Length; if (length <= 200) { TextUtils.RepalaceText(doc, "<noiDung" + stt + ">", noiDung); } TextUtils.RepalaceText(doc, "<benThuHuong" + stt + ">", TextUtils.ToString(dtDistinctItem.Rows[i]["F2"])); TextUtils.RepalaceText(doc, "<NganHang" + stt + ">", TextUtils.ToString(dtDistinctItem.Rows[i]["F3"])); TextUtils.RepalaceText(doc, "<soTaiKhoan" + stt + ">", TextUtils.ToString(dtDistinctItem.Rows[i]["F4"])); TextUtils.RepalaceText(doc, "<tienNhanNo" + stt + ">", tienNhanNo.ToString("n2")); TextUtils.RepalaceText(doc, "<bangChu" + stt + ">", TextUtils.NumericToString(tienNhanNo, "USD")); TextUtils.RepalaceText(doc, "<swiftCode" + stt + ">", TextUtils.ToString(dtDistinctItem.Rows[i]["F10"])); TextUtils.RepalaceText(doc, "<diaChiNganHang" + stt + ">", TextUtils.ToString(dtDistinctItem.Rows[i]["F11"])); TextUtils.RepalaceText(doc, "<diaChiBenThuHuong" + stt + ">", TextUtils.ToString(dtDistinctItem.Rows[i]["F12"])); } TextUtils.RepalaceText(doc, "<month>", string.Format("{0:00}", DateTime.Now.Month)); TextUtils.RepalaceText(doc, "<year>", DateTime.Now.Year.ToString()); doc.Save(); doc.Close(); word.Quit(); } catch (Exception ex) { MessageBox.Show(ex.Message, "Tân Phát", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { TextUtils.ReleaseComObject(doc); TextUtils.ReleaseComObject(word); } #endregion Hợp đồng ngoại tệ } }
void exportLRCN(string selectFolder) { string fileSourcePath = Application.StartupPath + "\\Templates\\PhongSXLR\\ChiThi.xls"; string localPath = selectFolder + "/" + _projectDirection.ProjectCode + ".CTSX." + _projectDirection.ID + "/" + _projectDirection.ProjectCode + "-CTSX.LR"; Directory.CreateDirectory(localPath); string filePath = localPath + "/" + _projectDirection.ProjectCode + "-CTSX.LR.xls"; try { File.Copy(fileSourcePath, filePath, true); } catch (Exception ex) { MessageBox.Show("Lỗi: File excel đang được mở." + Environment.NewLine + ex.Message); return; } DocUtils.InitFTPQLSX(); using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang tạo chỉ thị LR...")) { System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); Excel.Application app = default(Excel.Application); Excel.Workbook workBoook = default(Excel.Workbook); Excel.Worksheet workSheet = default(Excel.Worksheet); try { app = new Excel.Application(); app.Workbooks.Open(filePath); workBoook = app.Workbooks[1]; workSheet = (Excel.Worksheet)workBoook.Worksheets[1]; workSheet.Cells[5, 3] = _projectDirection.ID; workSheet.Cells[2, 7] = _projectDirection.ProjectCode; workSheet.Cells[4, 2] = "PHÒNG/ TỔ: Lắp ráp"; #region Download file DataTable dtModule = TextUtils.GetDistinctDatatable(_dtData, "ModuleCode"); foreach (DataRow item in dtModule.Rows) { string moduleCode = TextUtils.ToString(item["ModuleCode"]); string moduleGroup = moduleCode.Substring(0, 6); DataTable dtStructureFile = TextUtils.Select("select * from vPBDL_Structure_File where [PBDLStructureID] in (13,15)"); DataTable dtDMVT = LibQLSX.Select("select * from MaterialModuleLink where ModuleCode = '" + moduleCode + "'"); foreach (DataRow row in dtStructureFile.Rows) { string pathServer = TextUtils.ToString(row["FolderContain"]).Replace("group", moduleGroup).Replace("code", moduleCode); string folderName = localPath + '/' + moduleCode + "/" + TextUtils.ToString(row["Name"]); string filterDonVi = TextUtils.ToString(row["FilterDonVi"]); string extension = TextUtils.ToString(row["Extension"]); int getType = TextUtils.ToInt(row["GetType"]); Directory.CreateDirectory(folderName); if (getType == 1) { if (DocUtils.CheckExits(pathServer)) { DocUtils.DownloadFile(folderName, Path.GetFileName(pathServer), pathServer); } } else { DataRow[] listDonVi = dtDMVT.Select("Unit = '" + filterDonVi + "'"); foreach (DataRow rowMVL in listDonVi) { string fileName = TextUtils.ToString(rowMVL["Code"]); try { if (DocUtils.CheckExits(pathServer + "/" + fileName + extension)) { DocUtils.DownloadFile(folderName, fileName + extension, pathServer + "/" + fileName + extension); } } catch { } } } } } #endregion #region Export Excel DataRow[] drsIn = _dtData.Select("ProjectDirectionTypeID > 4 and IsDeleted = 0", "PartsCode ASC"); for (int i = 0; i < drsIn.Length; i++) { string moduleCode1 = TextUtils.ToString(drsIn[i]["ModuleCode"]); string moduleGroup1 = moduleCode1.Substring(0, 6); //string cadPathServer = string.Format("Thietke.Ck/{0}/{1}.Ck/CAD.{1}", moduleGroup, moduleCode); //string cadPathServerBC = string.Format("Thietke.Ck/{0}/{1}.Ck/BCCk.{1}/BC-CAD.{1}", moduleGroup, moduleCode); string stt = TextUtils.ToString(drsIn[i]["STT"]); string partsCode = TextUtils.ToString(drsIn[i]["PartsCode"]); string partsName = TextUtils.ToString(drsIn[i]["PartsName"]); decimal qty = TextUtils.ToDecimal(drsIn[i]["Qty"]); decimal makeTime = TextUtils.ToDecimal(drsIn[i]["MakeTime"]); string userName = TextUtils.ToString(drsIn[i]["UserName"]); DateTime?startDateDK = TextUtils.ToDate2(drsIn[i]["StartDateDK"]); DateTime?endDateDK = TextUtils.ToDate2(drsIn[i]["EndDateDK"]); string vatLieu = TextUtils.ToString(drsIn[i]["Material"]); string note = TextUtils.ToString(drsIn[i]["Note"]); workSheet.Cells[10, 1] = stt; workSheet.Cells[10, 2] = partsName; workSheet.Cells[10, 3] = partsCode; workSheet.Cells[10, 4] = moduleCode1; workSheet.Cells[10, 5] = qty; workSheet.Cells[10, 6] = makeTime; //workSheet.Cells[10, 6] = stt; workSheet.Cells[10, 8] = userName; workSheet.Cells[10, 9] = startDateDK != null?startDateDK.Value.ToString("dd/MM/yyyy") : ""; workSheet.Cells[10, 10] = endDateDK != null?endDateDK.Value.ToString("dd/MM/yyyy") : ""; workSheet.Cells[10, 18] = vatLieu; workSheet.Cells[10, 24] = note; //workSheet.Cells[10, 18] = moduleCode; if (note != "") { ((Excel.Range)workSheet.Rows[10]).Interior.Color = Excel.XlRgbColor.rgbLightBlue; } ((Excel.Range)workSheet.Rows[10]).Insert(); } ((Excel.Range)workSheet.Rows[9]).Delete(); ((Excel.Range)workSheet.Rows[9]).Delete(); #endregion } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { if (app != null) { app.ActiveWorkbook.Save(); app.Workbooks.Close(); app.Quit(); } } } }