#pragma once 
namespace project 
{  
using namespace System;  
using namespace System::ComponentModel;  
using namespace System::Collections;
  using namespace System::Windows::Forms;
  using namespace System::Data;
  using namespace System::Drawing;
  using namespace System::Text;
  using namespace System::Data::OleDb;
  using namespace System::IO;
  ///  /// Summary for Form1  ///  
/// WARNING: If you change the name of this class, you will need to change the  
///          'Resource File Name' property for the managed resource compiler tool  
///          associated with all .resx files this class depends on.  Otherwise,  
///          the designers will not be able to interact properly with localized  
///          resources associated with this form.  ///  
public ref class Form1 : public System::Windows::Forms::Form  
{  public:   
Form1(void)   {    
InitializeComponent();
    //    //TODO: Add the constructor code here    //   
}  
protected:   ///   /// Clean up any resources being used.   ///   
~Form1()   
{    
if (components)    
{     delete components;
    }
  private: System::Windows::Forms::Label^  label1;
  private: System::Windows::Forms::Button^  button2;
  private: System::Windows::Forms::Button^  button3;
 protected:  private:   ///   /// Required designer variable.   ///
   System::ComponentModel::Container ^components;
 #pragma region Windows Form Designer generated code   ///   /// Required method for Designer support - do not modify   /// the contents of this method with the code editor.   ///   
void InitializeComponent(void)   
{    this->label1 = (gcnew System::Windows::Forms::Label());
    this->button2 = (gcnew System::Windows::Forms::Button());
    this->button3 = (gcnew System::Windows::Forms::Button());
    this->openFileDialog1 = (gcnew System::Windows::Forms::OpenFileDialog());
    this->SuspendLayout();
    //    // label1    //    this->label1->AutoSize = true;
    this->label1->Font = (gcnew System::Drawing::Font(L"Microsoft Sans Serif", 14.25F, System::Drawing::FontStyle::Regular, System::Drawing::GraphicsUnit::Point,     static_cast(0)));
    this->label1->Location = System::Drawing::Point(86, 9);
    this->label1->Name = L"label1";
    this->label1->Size = System::Drawing::Size(264, 24);
    this->label1->TabIndex = 0;
    this->label1->Text = L"Welcome to CASRN Database";
    //    // button2    //    this->button2->Location = System::Drawing::Point(29, 58);
    this->button2->Name = L"button2";
    this->button2->Size = System::Drawing::Size(167, 39);
    this->button2->TabIndex = 2;
    this->button2->Text = L"Input CASRN Number File";
    this->button2->UseVisualStyleBackColor = true;
    this->button2->Click += gcnew System::EventHandler(this, &Form1::button2_Click);
    //    // button3    //    this->button3->Location = System::Drawing::Point(237, 58);
    this->button3->Name = L"button3";
    this->button3->Size = System::Drawing::Size(179, 39);
    this->button3->TabIndex = 3;
    this->button3->Text = L"The Result";
    this->button3->UseVisualStyleBackColor = true;
    this->button3->Click += gcnew System::EventHandler(this, &Form1::button3_Click);
    //    // openFileDialog1    //    this->openFileDialog1->FileName = L"openFileDialog1";
    //    // Form1    //    this->AutoScaleDimensions = System::Drawing::SizeF(6, 13);
    this->AutoScaleMode = System::Windows::Forms::AutoScaleMode::Font;
    this->ClientSize = System::Drawing::Size(451, 146);
    this->Controls->Add(this->button3);
    this->Controls->Add(this->button2);
    this->Controls->Add(this->label1);
    this->Name = L"Form1";
    this->Text = L"chem";
    this->ResumeLayout(false);
    this->PerformLayout();
   } #pragma endregion   
// get data table from Excel file
   // strFilePath is the path of the Excel file
   // sheet is the sheet name in the Excel file
   private: DataTable^ getDataFromXLS(String^ strFilePath, String^ sheet)
     {
      try
      {
       // connect string for OLEDB to read Excel file
       String^ strConnectionString = "";
       strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +            "Data Source=" + strFilePath + "; Jet OLEDB:Engine Type=5;" +            "Extended Properties=Excel 8.0;";
       // connect to Excel file
       OleDbConnection^ cnCSV = gcnew OleDbConnection(strConnectionString);
       // open Excel file
       cnCSV->Open();
       // write query command for all data from the sheet in Excel file
       OleDbCommand^ cmdSelect = gcnew OleDbCommand("SELECT * FROM [" + sheet + "$]", cnCSV);
       // create new OleDbDataAdapter instance to run query string
       OleDbDataAdapter^ daCSV = gcnew OleDbDataAdapter();
       // assign sellect command to query string
       daCSV->SelectCommand = cmdSelect;
       // create data table to hold data
       DataTable^ dtCSV = gcnew DataTable();
       // fill data to data table
       daCSV->Fill(dtCSV);
       // close Excel file
       cnCSV->Close();
       daCSV = nullptr;
       return dtCSV;
      }      catch (Exception^ ex)      {
       // write error message
       Console::WriteLine(ex->Message);
       return nullptr;
      }      finally { }     }  
// implement click on event for button2
  private: System::Void button2_Click(System::Object^  sender, System::EventArgs^  e)
 {      // choose type file will be visible on open file dialog
      openFileDialog1->Filter = "Text files (*.txt)|*.txt|All files (*.*)|*.*";
      // open file dialog
      this->openFileDialog1->ShowDialog();
      // assign file name to member inputFile
      this->inputFile = this->openFileDialog1->FileName;
      // message to user
      System::Windows::Forms::MessageBox::Show("Completed choosing the file " + this->inputFile);
      // enable button3
      this->button3->Enabled = true;
     }  // implement click on event for button1
  private: System::Void button1_Click(System::Object^  sender, System::EventArgs^  e)
 {          }
  // implement click on event for button3
  private: System::Void button3_Click(System::Object^  sender, System::EventArgs^  e) {
      // disable button2, button1, button3
           this->button2->Enabled = false;
      this->button3->Enabled = false;
      try {
       // open input file
       StreamReader^ infile = gcnew StreamReader(this->inputFile);
       // open output file
       StreamWriter^ outfile = gcnew StreamWriter("output.txt");
       String^ line;
       // get number of romw in data grid view
       DataTable^ test = getDataFromXLS(System::IO::Directory::GetCurrentDirectory()->ToString()+"\ChemList.xlt", "Actor_data");
       int number_line = test->Rows->Count;
       // read line by line in input file
       int flag=0;
 //inital 0:false, 1 true
       int successCount=0;
       int faileCount=0;
       while ((line = infile->ReadLine()) != nullptr) {
        // look for data
        if (line=="") continue;
        flag=0;
        for(int i=0; iRows[0]->ToString()->Trim();
         // compare id in file and in input file
         if( cmp->CompareTo(line->Trim()) == 0 ){
          successCount++;
          // out id CASRN data
          String^ outID = "CASRN:   " + test->Rows[0]->ToString()->Trim();
          // out name data
          String^ outname = "PREFERRED_NAME:   " + test->Rows[1]->ToString()->Trim();
          // out name struct
          String^ outstruct = "Structure: " + test->Rows[2]->ToString()->Trim();
          // write id
          outfile->WriteLine(outID);
          // write name
          outfile->WriteLine(outname);
          // write struct
          outfile->WriteLine(outstruct);
          // write new line
          outfile->WriteLine("");
          outfile->WriteLine("--------------------------");
          outfile->WriteLine("");
          flag=1;
          break;
         }                }        if (flag==0){          faileCount++;
          outfile->WriteLine("ID: " + line + " doesn't exist in the data");
          outfile->WriteLine("");
          outfile->WriteLine("--------------------------");
          outfile->WriteLine("");
         }
       }
       // close output
       outfile->Close();
       // close input
       infile->Close();
       // message to user
       System::Windows::Forms::MessageBox::Show("Completed! "+ successCount.ToString()+" results successed, "+faileCount.ToString() +" unable to find. "+"See the detail results in output.txt.");
     }     catch (Exception^ e) {
      // write error to screen
      Console::WriteLine(e->Message);
     }
     // enable button1 and button2
          this->button2->Enabled = true;
    } };
 }