private Boolean init()
{
FileNameSearch = "";
SheetNameSearch = "";
ResultPosX = 0;
TesterPosX = 0;
VerPosX = 0;
DatePosX = 0;
RemarkPosX = 0;
fs = EN_FILE_STATE.FILE_STATE_EMPTY;
return(true);
}
private DataTable get_data()
{
Console.WriteLine("ExcelBinding:get_data >> File Name : " + FileNameSearch);
//String strExcelConn = "provider=Microsoft.Jet.OLEDB.4.0;Data Source='"+FileNameSearch+"';Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\"";
String strExcelConn = "provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileNameSearch + ";Mode=ReadWrite;Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1\"";
OleDbConnection connExcel = new OleDbConnection(strExcelConn);
OleDbCommand cmdExcel = new OleDbCommand();
DataTable dtContent = new DataTable();
OleDbDataAdapter da = new OleDbDataAdapter();
cmdExcel.Connection = connExcel;
try
{
Console.WriteLine("get_data >> Sheet Name : " + SheetNameSearch);
cmdExcel.CommandText = "select * from [" + SheetNameSearch + "]";
connExcel.Open();
da.SelectCommand = cmdExcel;
da.Fill(dtContent);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
connExcel.Close();
connExcel.Dispose();
da.Dispose();
}
dtContent = modify_data(dtContent);
fs = EN_FILE_STATE.FILE_STATE_LOAD;
return(dtContent);
}
private Boolean saveAs_data(DataTable dtContentToSave)
{
Console.WriteLine("ExcelBinding:saveAs_data : " + FileNameSearch);
String strExcelConn = "provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileNameSearch + ";Mode=ReadWrite;Extended Properties=\"Excel 12.0 XML;HDR=No\"";
OleDbConnection connExcel = new OleDbConnection(strExcelConn);
OleDbCommand cmdExcel1 = new OleDbCommand();
OleDbCommand cmdExcel2 = new OleDbCommand();
OleDbCommand cmdExcel3 = new OleDbCommand();
cmdExcel1.Connection = connExcel;
cmdExcel2.Connection = connExcel;
cmdExcel3.Connection = connExcel;
// Create table (cmdExcel1)
// Remove header (cmdExcel2)
// Insert data (cmdExcel3
char[] MyChar = { '$', '\'' };
string SheetNameTrim = SheetNameSearch.Trim(MyChar);
cmdExcel1.CommandText = "CREATE TABLE [" + SheetNameTrim + "] (";
cmdExcel2.CommandText = "UPDATE [" + SheetNameTrim + "] SET ";
cmdExcel3.CommandText = "INSERT INTO [" + SheetNameTrim + "] VALUES (";
for (int i = 0; i < dtContentToSave.Columns.Count - 1; i++)
{
cmdExcel1.CommandText += "F" + i + " varchar(30)";
cmdExcel2.CommandText += "F" + (i + 1) + " = \"\"";
cmdExcel3.CommandText += "@F" + i;
cmdExcel3.Parameters.Add(new OleDbParameter("@F" + i, OleDbType.VarChar));
if (i != dtContentToSave.Columns.Count - 2)
{
cmdExcel1.CommandText += ", ";
cmdExcel2.CommandText += ", ";
cmdExcel3.CommandText += ", ";
}
}
cmdExcel1.CommandText += " );";
cmdExcel2.CommandText += ";";
cmdExcel3.CommandText += ")";
try
{
connExcel.Open();
cmdExcel1.ExecuteNonQuery();
cmdExcel2.ExecuteNonQuery();
for (int i = 0; i < dtContentToSave.Rows.Count - 1; i++)
{
for (int j = 0; j < dtContentToSave.Columns.Count - 1; j++)
{
cmdExcel3.Parameters["@F" + j].Value = dtContentToSave.Rows[i][j].ToString();
}
cmdExcel3.ExecuteNonQuery();
}
connExcel.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmdExcel1.Dispose();
cmdExcel2.Dispose();
connExcel.Dispose();
}
fs = EN_FILE_STATE.FILE_STATE_SAVEAS;
return(true);
}
