void CreateExcel() { string filePath = "D:\\tet.xlsx"; 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]; //Excel.Range oRng = workSheet.Range["M1"]; //oRng.EntireColumn.Insert(Excel.XlInsertShiftDirection.xlShiftToRight,Excel.XlInsertFormatOrigin.xlFormatFromRightOrBelow); for (int i = 0; i < 3; i++) { ((Excel.Range)workSheet.Rows[13]).Insert(); } Excel.Range rCopy = workSheet.Range["A10", "Z12"]; rCopy.Copy(); Excel.Range rPaste = workSheet.Range["A14", "Z16"]; rPaste.PasteSpecial(Excel.XlPasteType.xlPasteAll); DataTable dt = TextUtils.Select("SELECT top 1 (select COUNT(*) from [dbo].[fnStringToTable]([NameTS],',')) as countTS " + " FROM [QLKHCV].[dbo].[MaterialHistory] where ModuleID = " + " order by (select COUNT(*) from [dbo].[fnStringToTable]([NameTS],',')) desc)"); app.ActiveWorkbook.Save(); app.Workbooks.Close(); app.Quit(); Process.Start(filePath); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { TextUtils.ReleaseComObject(app); TextUtils.ReleaseComObject(workBoook); TextUtils.ReleaseComObject(workSheet); } }
private void btnHuongDanSuDung_Click(object sender, EventArgs e) { if (cboModule.EditValue == null) { return; } if (cboVersion.SelectedIndex < 0) { return; } string localPath = ""; FolderBrowserDialog fbd = new FolderBrowserDialog(); if (fbd.ShowDialog() == DialogResult.OK) { localPath = fbd.SelectedPath; } else { return; } string code = TextUtils.ToString(cboModule.EditValue).Substring(5, 5); string name = TextUtils.ToString(grvCboModule.GetFocusedRowCellValue(colModuleName)).ToUpper(); string version = string.Format("{0:00}", (int)cboVersion.SelectedValue); string _pPathDT = Application.StartupPath + "\\Templates\\PhongKyThuat\\"; using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang tạo biểu mẫu")) { Word.Application word = new Word.Application(); Word.Document doc = new Word.Document(); try { string fileName = "D0." + version + "." + code + ".docx"; try { File.Copy(_pPathDT + "HDSD.docx", (localPath + "\\" + fileName), true); } catch (Exception ex) { MessageBox.Show(ex.Message); doc.Close(); word.Quit(); return; } doc = word.Documents.Open(localPath + "\\" + fileName); doc.Activate(); TextUtils.FindReplaceAnywhere(word, "<code>", Path.GetFileNameWithoutExtension(fileName)); TextUtils.FindReplaceAnywhere(word, "<name>", name); //TextUtils.FindReplaceAnywhere(word, "<thongso>", TextUtils.ToString(grvCboModule.GetFocusedRowCellValue(colSpecifications))); //TextUtils.RepalaceText(doc, "<thongso>", TextUtils.ToString(grvCboModule.GetFocusedRowCellValue(colSpecifications))); doc.Save(); Process.Start(localPath + "\\" + fileName); } catch (Exception ex) { MessageBox.Show(ex.Message, "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { doc.Close(); word.Quit(); TextUtils.ReleaseComObject(doc); TextUtils.ReleaseComObject(word); } } }
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 CreateExcel() { using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang tạo biểu mẫu danh mục vật tư chính...")) { ModulesModel thisModule = (ModulesModel)(ModulesBO.Instance.FindByAttribute("Code", cboModule.EditValue.ToString()))[0]; int moduleID = thisModule.ID; if (moduleID == 0) { return; } ArrayList listModuleHistory = vDMVTCBO.Instance.FindByAttribute("ModuleID", moduleID); if (listModuleHistory.Count <= 0) { return; } DataTable dt = TextUtils.Select("SELECT top 1 (select COUNT(*) from [dbo].[fnStringToTable]([NameTS],'@')) as countTS " + " FROM [QLKHCV].[dbo].[MaterialHistory] with(nolock) where ModuleID = " + moduleID + " order by (select COUNT(*) from [dbo].[fnStringToTable]([NameTS],'@')) desc"); int numberTT = TextUtils.ToInt(dt.Rows[0][0]); if (numberTT == 0) { return; } vDMVTCModel mModel = (vDMVTCModel)listModuleHistory[0]; string productName = mModel.ProductName; string productCode = mModel.ProductCode; string projectName = ""; string projectCode = ""; string projectDateEnd = ""; string projectID = cboProject.Properties.View.GetFocusedRowCellValue(colProjectID) != null ? cboProject.Properties.View.GetFocusedRowCellValue(colProjectID).ToString() : ""; if (projectID != "") { projectName = cboProject.Properties.View.GetFocusedRowCellValue(colProjectName).ToString(); projectCode = cboProject.Properties.View.GetFocusedRowCellValue(colProjectCode).ToString(); projectDateEnd = cboProject.Properties.View.GetFocusedRowCellDisplayText(colDateEnd).ToString(); } string localPath = @"D:/Thietke.Ck/" + productCode.Substring(0, 6) + "/" + productCode + ".Ck/DOC." + productCode + "/"; string filePath = Application.StartupPath + "/Templates/DMVTC.xlsm"; if (!Directory.Exists(localPath)) { Directory.CreateDirectory(localPath); } File.Copy(filePath, localPath + "DMVTC." + productCode + ".xlsm", true); 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 + "DMVTC." + productCode + ".xlsm"); workBoook = app.Workbooks[1]; workSheet = (Excel.Worksheet)workBoook.Worksheets[1]; //chi tiết dự án workSheet.Cells[4, 4] = productCode; workSheet.Cells[5, 4] = productName; workSheet.Cells[6, 4] = projectCode; workSheet.Cells[7, 4] = projectName.ToUpper(); DataTable dtHistory = TextUtils.Select("vDMVTC", new Expression("ModuleID", moduleID)); workSheet.Cells[4, 19] = dtHistory.Compute("Sum(Total)", ""); workSheet.Cells[5, 19] = dtHistory.Compute("Max(Delivery)", ""); workSheet.Cells[6, 19] = projectDateEnd; workSheet.Cells[15, 20] = "Tân Phát, ngày " + DateTime.Now.Day + " tháng " + DateTime.Now.Month + " năm " + DateTime.Now.Year; workSheet.PageSetup.RightHeader = "Người sửa đổi: " + Global.AppFullName; //nếu như vượt quá 14 thông số thì thêm cột if (numberTT > 14) { for (int i = 0; i < numberTT - 14; i++) { Excel.Range oRng = workSheet.Range["Y1"]; oRng.EntireColumn.Insert(Excel.XlInsertShiftDirection.xlShiftToRight, Excel.XlInsertFormatOrigin.xlFormatFromRightOrBelow); } } for (int i = 0; i < listModuleHistory.Count; i++) { vDMVTCModel hModel = (vDMVTCModel)listModuleHistory[i]; for (int j = 0; j <= 3; j++) { ((Excel.Range)workSheet.Rows[13]).Insert(); } //Copy and Past file Excel.Range rCopy = workSheet.Range["A10", "AZ12"]; rCopy.Copy(); Excel.Range rPaste = workSheet.Range["A14", "AZ16"]; rPaste.PasteSpecial(Excel.XlPasteType.xlPasteAll); workSheet.Cells[14, 1] = listModuleHistory.Count - i; //stt workSheet.Cells[14, 2] = hModel.Code; //Mã vật tư workSheet.Cells[14, 3] = hModel.Name; //Tên vật tư workSheet.Cells[14, 4] = hModel.Hang; //Hãng workSheet.Cells[14, 5] = hModel.ThoiGianGHCuoi.ToString("dd/MM/yyyy"); //Thời gian GH cuối workSheet.Cells[14, 6] = hModel.Delivery; //Thời gian giao hàng(ngày) workSheet.Cells[14, 7] = hModel.Unit; //Đơn vị tính workSheet.Cells[14, 8] = hModel.Qty; //Số lượng workSheet.Cells[14, 9] = hModel.Price; //Đơn giá workSheet.Cells[14, 10] = hModel.Qty * hModel.Price; //Thành tiền string[] arrTT = hModel.NameTS.Split('@'); string[] arrValueTT = hModel.ValueTT.Split('@'); string[] arrValueYC = hModel.ValueYC.Split('@'); for (int t = 0; t < arrTT.Count(); t++) { workSheet.Cells[14, 12 + t] = arrTT[t]; workSheet.Cells[15, 12 + t] = arrValueTT[t]; workSheet.Cells[16, 12 + t] = arrValueYC[t]; } } for (int i = 0; i < 4; i++) { ((Excel.Range)workSheet.Rows[10]).Delete(); } app.ActiveWorkbook.Save(); app.Workbooks.Close(); app.Quit(); Process.Start(localPath + "DMVTC." + productCode + ".xlsm"); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { TextUtils.ReleaseComObject(app); TextUtils.ReleaseComObject(workBoook); TextUtils.ReleaseComObject(workSheet); } } }