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