//read Excel and save in database public static int fReadExcelFilePO(string fname, string idPO, ref List <ListExpPO> listExpPOs) { ExcelOffice.Range xlRange = null; ExcelOffice.Workbook xlWorkbook = null; ExcelOffice.Application xlApp = null; ExcelOffice._Worksheet xlWorksheet = null; try { xlApp = new ExcelOffice.Application(); xlWorkbook = xlApp.Workbooks.Open(fname); xlWorksheet = (ExcelOffice._Worksheet)xlWorkbook.Sheets[1]; xlRange = xlWorksheet.UsedRange; string xName = xlWorksheet.Name.ToString(); int rowCount = xlRange.Rows.Count; int colCount = xlRange.Columns.Count; string nameOfD = Convert.ToString((xlRange.Cells[6, 1] as ExcelOffice.Range).Text); for (int i = 11; i <= rowCount; i++) { string index = Convert.ToString((xlRange.Cells[i, 3] as ExcelOffice.Range).Text); if (string.Empty != index) { ListExpPO listExpPO = new ListExpPO(); string strName = Convert.ToString((xlRange.Cells[i, 4] as ExcelOffice.Range).Text); string idProvine = convertToUnSign3(strName.Trim()); idProvine = idProvine.Replace(" ", ""); string strNofD = Convert.ToString((xlRange.Cells[i, 4] as ExcelOffice.Range).Text); if (strNofD.Trim() == "-") { continue; } strNofD = strNofD.Replace(",", "").Trim(); int NofD = Convert.ToInt32(strNofD); listExpPO.IdPO = idPO; listExpPO.IdProvince = idProvine; listExpPO.NameOfDevice = nameOfD; listExpPO.NumberOfDevice = NofD; listExpPOs.Add(listExpPO); } else { i = rowCount + 1; } } //cleanup GC.Collect(); GC.WaitForPendingFinalizers(); //rule of thumb for releasing com objects: // never use two dots, all COM objects must be referenced and released individually // ex: [somthing].[something].[something] is bad //release com objects to fully kill excel process from running in the background Marshal.ReleaseComObject(xlRange); Marshal.ReleaseComObject(xlWorksheet); //close and release xlWorkbook.Close(); Marshal.ReleaseComObject(xlWorkbook); //quit and release xlApp.Quit(); Marshal.ReleaseComObject(xlApp); return(1); } catch (Exception) { GC.Collect(); GC.WaitForPendingFinalizers(); //rule of thumb for releasing com objects: // never use two dots, all COM objects must be referenced and released individually // ex: [somthing].[something].[something] is bad //release com objects to fully kill excel process from running in the background Marshal.ReleaseComObject(xlRange); Marshal.ReleaseComObject(xlWorksheet); //close and release xlWorkbook.Close(); Marshal.ReleaseComObject(xlWorkbook); //quit and release xlApp.Quit(); Marshal.ReleaseComObject(xlApp); return(0); } }
private void btnSave_Click(object sender, EventArgs e) { /*Check PO in DB*/ int ret = 0; /*Save The Edited Contract Info */ PO newPO = new PO(); newPO.IDContract = txbIDContract.Text; newPO.IDPO = txbPOCode.Text; newPO.PONumber = txbPOName.Text; newPO.NumberOfDevice = float.Parse(txbNumberDevice.Text); newPO.DateCreatedPO = TimePickerDateCreatedPO.Value.ToString("yyyy-MM-dd"); newPO.DurationConfirmPO = TimePickerDateConfirmPO.Value.ToString("yyyy-MM-dd"); newPO.DefaultActiveDatePO = TimepickerDefaultActive.Value.ToString("yyyy-MM-dd"); newPO.DeadLinePO = TimePickerDeadLinePO.Value.ToString("yyyy-MM-dd"); newPO.TotalValuePO = float.Parse(txbValuePO.Text); newPO.Tupo = float.Parse(txbTUPO.Text); //Check và tạo forder theo mẫu string DriveName = ""; DriveInfo[] driveInfos = DriveInfo.GetDrives(); foreach (DriveInfo driveInfo in driveInfos) { //MessageBox.Show(driveInfo.Name.ToString()); if (String.Compare(driveInfo.Name.ToString().Substring(0, 3), @"D:\") == 0 || String.Compare(driveInfo.Name.ToString().Substring(0, 3), @"E:\") == 0) { //MessageBox.Show(driveInfo.Name.ToString().Substring(0, 1)); DriveName = driveInfo.Name.ToString().Substring(0, 3); break; } } Directory.CreateDirectory(DriveName + "OPM"); Directory.CreateDirectory(DriveName + "OPM" + txbPOName.Text); string strPODirectory = DriveName + "OPM\\" + txbPOName.Text; /*Create Folder Contract on F Disk*/ //string strContractDirectory = txbIDContract.Text.Replace('/', '_'); //strContractDirectory = strContractDirectory.Replace('-', '_'); //string strPODirectory = "F:\\OPM\\" + strContractDirectory + "\\" + txbPOName.Text; ret = newPO.GetDetailPO(txbPOCode.Text); if (0 == ret) { if (!Directory.Exists(strPODirectory)) { Directory.CreateDirectory(strPODirectory); MessageBox.Show("Folder have been created!!!"); } else { MessageBox.Show("Folder already exist!!!"); } ret = newPO.InsertNewPO(newPO); if (2 == ret) { MessageBox.Show("Cập nhật thành công"); } else { MessageBox.Show(ConstantVar.CreateNewPOSuccess); UpdateCatalogPanel(txbPOName.Text); /*Create Bao Lanh Thuc Hien Hop Dong*/ Directory.CreateDirectory(DriveName + "LP"); string fileBLTUPO_temp = DriveName + @"LP\BLPO_Template.docx"; //string fileBLTUPO_temp = @"F:\LP\BLPO_Template.docx"; string strBLTUPOName = strPODirectory + "\\De nghi Bao lanh thuc hien & tam ung PO MSTT.docx"; /*truy Suất thông tin của Contract*/ ContractObj contractObj = new ContractObj(); ContractObj.GetObjectContract(txbIDContract.Text, ref contractObj); this.Cursor = Cursors.WaitCursor; OpmWordHandler.Create_BLTU_PO(fileBLTUPO_temp, strBLTUPOName, txbPOName.Text, txbIDContract.Text, contractObj.NameContract, contractObj.DateSigned, TimePickerDateCreatedPO.Value.ToString("yyyy-MM-dd"), txbValuePO.Text, txbTUPO.Text, contractObj.SiteB, TimepickerDefaultActive.Value.ToString("yyyy-MM-dd")); /*Send Email To DF*/ string fileBLTUPO_temp1 = DriveName + @"LP\VBDNTU.docx"; //string fileBLTUPO_temp = @"F:\LP\BLPO_Template.docx"; string strBLTUPOName1 = strPODirectory + "\\Van ban de nghi tam ung.docx"; /*truy Suất thông tin của Contract*/ ContractObj contractObj1 = new ContractObj(); ContractObj.GetObjectContract(txbIDContract.Text, ref contractObj1); this.Cursor = Cursors.WaitCursor; OpmWordHandler.Create_VBTUHD(fileBLTUPO_temp1, strBLTUPOName1, txbPOName.Text, txbIDContract.Text, contractObj1.NameContract, contractObj1.DateSigned, TimePickerDateCreatedPO.Value.ToString("yyyy-MM-dd"), txbValuePO.Text, txbTUPO.Text, contractObj1.SiteB, TimepickerDefaultActive.Value.ToString("yyyy-MM-dd")); /*Send Email To DF*/ } /*Create Bao Lanh Thuc Hien Hop Dong*/ //string filename = @"F:\LP\MSTT_Template.docx"; //string strBLHPName = strContractDirectory + "\\Bao_Lanh_Hop_Dong.docx"; //OpmWordHandler.CreateBLTH_Contract(filename, strBLHPName, tbContract.Text, tbBidName.Text, tbxDateSigned.Text, tbxSiteB.Text, txbGaranteeValue.Text, txbGaranteeActiveDate.Text); /*Send Email To DF*/ //OPMEmailHandler.fSendEmail("Mail From DoanTD Gmail", strBLHPName); //UpdateCatalogPanel(txbPOCode.Text); } //đọc file excel--Dưỡng List <ListExpPO> listExpPOs = new List <ListExpPO>(); if (txbnamefilePO.Text != null) { int retEx = OpmExcelHandler.fReadExcelFilePO(txbnamefilePO.Text, txbPOCode.Text, ref listExpPOs); if (retEx == 1) { ListExpPO listExpPO = new ListExpPO(); int retInsert = listExpPO.InsertMultiListPO(listExpPOs); if (retInsert == 1) { MessageBox.Show("thông tin trong File PO đã lưu thành công"); } else { MessageBox.Show("thông tin trong File PO không lưu được"); } } else { MessageBox.Show("Đọc file không thành công"); } } else { MessageBox.Show("Chưa import file Phan bo hop dong"); } }