queryconsumeform.h

宾馆酒店管理系统,建议在简体中文版Microsoft Windows Server 2003(SP1)/Microsoft Office 2003(SP2)/Microsoft SQL Server

			this->Controls->Add(this->消费品分类DataGridView);
			this->Controls->Add(商品名称Label);
			this->Controls->Add(this->商品名称ComboBox);
			this->Controls->Add(开始日期Label);
			this->Controls->Add(this->开始日期DateTimePicker);
			this->Controls->Add(结束日期Label);
			this->Controls->Add(this->结束日期DateTimePicker);
			this->FormBorderStyle = System::Windows::Forms::FormBorderStyle::Fixed3D;
			this->MaximizeBox = false;
			this->MinimizeBox = false;
			this->Name = L"QueryConsumeForm";
			this->StartPosition = System::Windows::Forms::FormStartPosition::CenterParent;
			this->Text = L"查询商品分类消耗信息";
			this->Load += gcnew System::EventHandler(this, &QueryConsumeForm::QueryConsumeForm_Load);
			(cli::safe_cast<System::ComponentModel::ISupportInitialize^  >(this->消费品分类DataGridView))->EndInit();
			this->ResumeLayout(false);
			this->PerformLayout();

		}
#pragma endregion
	public: String^ MyOperator;
	public: String^ MyCompany;
	public: System::Data::DataTable^ MyQueryTable;
	private: static String^ MyHotelConnectionString= L"Data Source=.;Initial Catalog=MyHotel;Integrated Security=True;Pooling=False";	
	private: System::Void QueryConsumeForm_Load(System::Object^  sender, System::EventArgs^  e) {
	   String^ MySQLConnectionString = MyHotelConnectionString;
       SqlConnection^ MyConnection = gcnew SqlConnection(MySQLConnectionString);
       MyConnection->Open();
	   System::Data::DataTable^ MyGoodsTable = gcnew System::Data::DataTable();
       String^ MySQL = "Select DISTINCT(名称) From 消费物品";
       SqlDataAdapter^ MyAdapter = gcnew SqlDataAdapter(MySQL, MyConnection);
       MyAdapter->Fill(MyGoodsTable);
       this->商品名称ComboBox->DataSource = MyGoodsTable;
       this->商品名称ComboBox->DisplayMember = "名称";
       this->商品名称ComboBox->ValueMember = "名称";
       if (MyConnection->State == ConnectionState::Open)
       {
          MyConnection->Close();
       }
	}
	private: System::Void 查询Button_Click(System::Object^  sender, System::EventArgs^  e) {
		String^ MySQLConnectionString = MyHotelConnectionString;
        SqlConnection^ MyConnection = gcnew SqlConnection(MySQLConnectionString);
        MyConnection->Open();
		MyQueryTable = gcnew System::Data::DataTable();
        String^ MySQL = "Select * From 消费品分类查询视图 Where 消费品名称='"+this->商品名称ComboBox->Text+"' AND (记帐时间 Between  '" + this->开始日期DateTimePicker->Value + "' AND '" + this->结束日期DateTimePicker->Value + "')";
        SqlCommand^ MyCommand = gcnew SqlCommand(MySQL, MyConnection);            
        SqlDataAdapter^ MyAdapter = gcnew SqlDataAdapter(MyCommand);
        MyAdapter->Fill(MyQueryTable);
        this->消费品分类DataGridView->DataSource = MyQueryTable;
        if (MyConnection->State == ConnectionState::Open)
        {
           MyConnection->Close();
        }
	}
	private: System::Void 打印Button_Click(System::Object^  sender, System::EventArgs^  e) {
	    if (MyQueryTable->Rows->Count < 1)
        {
           return;
        }
        //导出Excel表格数据文件
        ApplicationClass^ MyExcel;
        Workbooks^ MyWorkBooks;
        Workbook^ MyWorkBook;
        Worksheet^ MyWorkSheet;
        char MyColumns;
        Range^ MyRange;
		array<System::Object^,2>^ MyData=gcnew array<System::Object^,2>(5000,30);
        int Count, i, j;
        MyExcel = gcnew ApplicationClass();
        MyExcel->Visible = true;
        if (MyExcel == nullptr)
        {
			MessageBox::Show("Excel程序无法启动！", "信息提示", MessageBoxButtons::OK, MessageBoxIcon::Information);
            return;
        }
        MyWorkBooks = MyExcel->Workbooks;
		MyWorkBook = MyWorkBooks->Add(Missing::Value);
        MyWorkSheet = (Worksheet^)MyWorkBook->Worksheets[1];
		switch(MyQueryTable->Columns->Count + 64)
		{
			case 64:
				 MyRange=MyWorkSheet->Range["A5","A5"];
				break;
			case 65:
				MyRange=MyWorkSheet->Range["A5","B5"];
				break;
			case 66:
				MyRange=MyWorkSheet->Range["A5","C5"];
				break;
			case 67:
				MyRange=MyWorkSheet->Range["A5","D5"];
				break;
			case 68:
				MyRange=MyWorkSheet->Range["A5","E5"];
				break;
			case 69:
				MyRange=MyWorkSheet->Range["A5","F5"];
				break;
			case 70:
				MyRange=MyWorkSheet->Range["A5","G5"];
				break;
			case 71:
				MyRange=MyWorkSheet->Range["A5","H5"];
				break;
			case 72:
				MyRange=MyWorkSheet->Range["A5","I5"];
				break;
			case 73:
				MyRange=MyWorkSheet->Range["A5","J5"];
				break;
			case 74:
				MyRange=MyWorkSheet->Range["A5","K5"];
				break;
			case 75:
				MyRange=MyWorkSheet->Range["A5","L5"];
				break;
			case 76:
				MyRange=MyWorkSheet->Range["A5","M5"];
				break;
			case 77:
				MyRange=MyWorkSheet->Range["A5","N5"];
				break;
			case 78:
				MyRange=MyWorkSheet->Range["A5","O5"];
				break;
			case 79:
				MyRange=MyWorkSheet->Range["A5","P5"];
				break;
			case 80:
				MyRange=MyWorkSheet->Range["A5","Q5"];
				break;
			case 81:
				MyRange=MyWorkSheet->Range["A5","R5"];
				break;
			case 82:
				MyRange=MyWorkSheet->Range["A5","S5"];
				break;
			case 83:
				MyRange=MyWorkSheet->Range["A5","T5"];
				break;
			case 84:
				MyRange=MyWorkSheet->Range["A5","U5"];
				break;
			case 85:
				MyRange=MyWorkSheet->Range["A5","V5"];
				break;
			case 86:
				MyRange=MyWorkSheet->Range["A5","W5"];
				break;
			case 87:
				MyRange=MyWorkSheet->Range["A5","X5"];
				break;
			case 88:
				MyRange=MyWorkSheet->Range["A5","Y5"];
				break;
			case 89:
				MyRange=MyWorkSheet->Range["A5","Z5"];
				break;
		 }			        
        Count = 0;
        for each (DataColumn^ MyNewColumn in MyQueryTable->Columns)
        {
           MyData[0, Count] = MyNewColumn->ColumnName;
           Count = Count + 1;
        }
        j = 1;
        //输出数据库记录
        for each (DataRow^ MyRow in MyQueryTable->Rows)
        {
            for (i = 0; i < MyQueryTable->Columns->Count; i++)
            {
                MyData[j, i] = MyRow[i]->ToString();
            }
            j++;
        }
        MyRange = MyRange->Resize[MyQueryTable->Rows->Count + 1, MyQueryTable->Columns->Count];
        MyRange->Value2 = MyData;
        MyRange->EntireColumn->AutoFit();
        MyWorkSheet->Cells[1, 2] = this->MyCompany + this->商品名称ComboBox->Text + "消耗分类统计表";
        MyWorkSheet->Cells[2, 1] = "操作员：" + this->MyOperator;
        MyWorkSheet->Cells[3, 1] = "开始日期：" + this->开始日期DateTimePicker->Value.ToLongDateString();
        MyWorkSheet->Cells[4, 1] = "结束日期：" + this->结束日期DateTimePicker->Value.ToLongDateString();
	}
};
}