public DataTable getDataFromXLS2007(string strFilePath)
{
DataTable table2;
try
{
OleDbConnection connection = new OleDbConnection(this.connectionString.Replace("{sourcefile}", strFilePath));
connection.Open();
OleDbCommand command = new OleDbCommand("SELECT * FROM [Sheet1$]", connection);
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = command;
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
connection.Close();
adapter = null;
table2 = dataTable;
}
catch (Exception exception)
{
CTLError.WriteError("CTLImportExcel GetDataFromXLS2007", exception.Message);
return(null);
throw new Exception(exception.Message);
}
return(table2);
}
public static void LoadDanhMucSKU()
{
try
{
if (CheckFileINVMST())
{
try
{
SplashScreen.SetCommentaryString("Load Danh mục .......");
TPSDataAccess access = new TPSDataAccess(@"C:\WINDOWS\Temp\INVMST.tps");
_tableDMSKU = access.GetDataTable("Select SKU,UPC,DESCRIPTION from INVMST where CONF_PRICE='N' and UPC<>''");
//_tableDMSKU.Columns.Add("GhiChu");
}
catch (Exception e)
{
CTLError.WriteError("GetDMSKU config GetConfiguration", e.Message);
}
}
}
catch (Exception e)
{
CTLError.WriteError("LoadDanhMucSKU config GetConfiguration", e.Message);
return;
throw;
}
}
public void WireInfomationCIM(string path, int index, string name, string sdt, string Address)
{
try
{
Microsoft.Office.Interop.Excel.Application xlApp;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Range chartRange;
xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Open(path, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//xlWorkSheet.Cells[1, 1] = "http://csharp.net-informations.com";
//xlWorkSheet.Rows.Insert(0, 0);
//xlWorkSheet.Rows.Insert(0, 0);
//xlWorkSheet.Rows.Insert(0, 0);
//thong tin KH
//Ten KH
xlWorkSheet.get_Range("A" + index + 1, "H" + index + 1).Merge(false);
chartRange = xlWorkSheet.get_Range("A" + index + 1, "H" + index + 1);
chartRange.FormulaR1C1 = name;
//So DT
xlWorkSheet.get_Range("A" + index + 2, "H" + index + 2).Merge(false);
chartRange = xlWorkSheet.get_Range("A" + index + 2, "H" + index + 2);
chartRange.FormulaR1C1 = sdt;
//Dia Chi
xlWorkSheet.get_Range("A" + index + 3, "H" + index + 3).Merge(false);
chartRange = xlWorkSheet.get_Range("A" + index + 3, "H" + index + 3);
chartRange.FormulaR1C1 = Address;
chartRange.Font.Bold = true;
chartRange.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
chartRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
chartRange.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
chartRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
//xlWorkSheet.get_Range("A" + index, "H" + index+2).Formula = value;
xlWorkBook.Save();
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
catch (Exception exception)
{
CTLError.WriteError("CTLImportExcel WireInfomationCIM", exception.Message);
return;
}
}
public static void GetConfiguration()
{
XmlDocument document = new XmlDocument();
try
{
document.Load(Application.StartupPath + @"\Config.xml");
try
{
//_pathfile = document.SelectSingleNode("//PathFile").Attributes["Value"].Value;
_loaican = document.SelectSingleNode("//LoaiCan").Attributes["Value"].Value;
_pathfileWinDSS = document.SelectSingleNode("//PathFileWindss").Attributes["Value"].Value;
//_timemer = document.SelectSingleNode("//Timemer").Attributes["Value"].Value;
#region autoupdate
try
{
LayCauHinh();
_Apptype = document.SelectSingleNode("//AppType").Attributes["Value"].Value;
_chucNang = document.SelectSingleNode("//ChucNang").Attributes["Value"].Value;
_DBNameFrontEnd = document.SelectSingleNode("//DBNameFrontEnd").Attributes["Value"].Value != string.Empty ? document.SelectSingleNode("//DBNameFrontEnd").Attributes["Value"].Value : "SGCReport";
_ismanager = document.SelectSingleNode("//IsManager").Attributes["Value"].Value;
_strKillapp = document.SelectSingleNode("//StrKillApp").Attributes["Value"].Value;
_startapp = document.SelectSingleNode("//StrStartApp").Attributes["Value"].Value;
_deletefile = document.SelectSingleNode("//DeleteFile").Attributes["Value"].Value;
}
catch (Exception)
{
return;
}
#endregion
}
catch (Exception exception)
{
CTLError.WriteError("Config getCF", exception.Message);
}
}
catch (Exception exception)
{
CTLError.WriteError("Config getCF", exception.Message);
throw new Exception(exception.Message);
}
}
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
CTLError.WriteError("CTLImportExcel releaseObject", ex.Message);
//MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
}
finally
{
GC.Collect();
}
}
public static bool CheckFileINVMST()
{
try
{
FileInfo fileInfo = new FileInfo(@"C:\WINDOWS\Temp\INVMST.tps");
SplashScreen.SetCommentaryString("Kiểm tra Danh mục .......");
if (!fileInfo.Exists)
{
File.Copy(Path.Combine(Config._pathfileWinDSS, "INVMST.TPS"), fileInfo.FullName, true);
}
else if (fileInfo.LastWriteTime.ToString("ddMMyyyy") != DateTime.Now.ToString("ddMMyyyy"))
{
File.Copy(Path.Combine(Config._pathfileWinDSS, "INVMST.TPS"), fileInfo.FullName, true);
}
return(true);
}
catch (Exception e)
{
CTLError.WriteError("CheckFile FormTimKiem Bang bao gia", e.Message);
return(false);
throw;
}
}
