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);
}
}
}