reportbooksform.h

vc++开发环境SQL数据库图书租借管理系统

			this->打印Button->Size = System::Drawing::Size(70, 23);
			this->打印Button->TabIndex = 53;
			this->打印Button->Text = L"打印";
			this->打印Button->TextAlign = System::Drawing::ContentAlignment::MiddleRight;
			this->打印Button->TextImageRelation = System::Windows::Forms::TextImageRelation::ImageBeforeText;
			this->打印Button->UseVisualStyleBackColor = true;
			this->打印Button->Click += gcnew System::EventHandler(this, &ReportBooksForm::打印Button_Click);
			// 
			// 查询Button
			// 
			this->查询Button->Image = (cli::safe_cast<System::Drawing::Image^  >(resources->GetObject(L"查询Button.Image")));
			this->查询Button->Location = System::Drawing::Point(390, 58);
			this->查询Button->Name = L"查询Button";
			this->查询Button->Size = System::Drawing::Size(70, 23);
			this->查询Button->TabIndex = 52;
			this->查询Button->Text = L"查询";
			this->查询Button->TextAlign = System::Drawing::ContentAlignment::MiddleRight;
			this->查询Button->TextImageRelation = System::Windows::Forms::TextImageRelation::ImageBeforeText;
			this->查询Button->UseVisualStyleBackColor = true;
			this->查询Button->Click += gcnew System::EventHandler(this, &ReportBooksForm::查询Button_Click);
			// 
			// 出版社TextBox
			// 
			this->出版社TextBox->Location = System::Drawing::Point(58, 31);
			this->出版社TextBox->Name = L"出版社TextBox";
			this->出版社TextBox->Size = System::Drawing::Size(157, 21);
			this->出版社TextBox->TabIndex = 51;
			// 
			// label2
			// 
			this->label2->AutoSize = true;
			this->label2->Location = System::Drawing::Point(7, 38);
			this->label2->Name = L"label2";
			this->label2->Size = System::Drawing::Size(53, 12);
			this->label2->TabIndex = 50;
			this->label2->Text = L"出版社：";
			// 
			// ReportBooksForm
			// 
			this->AutoScaleDimensions = System::Drawing::SizeF(6, 12);
			this->AutoScaleMode = System::Windows::Forms::AutoScaleMode::Font;
			this->ClientSize = System::Drawing::Size(542, 416);
			this->Controls->Add(this->类别TextBox);
			this->Controls->Add(this->label5);
			this->Controls->Add(this->作者TextBox);
			this->Controls->Add(this->label1);
			this->Controls->Add(this->书号TextBox);
			this->Controls->Add(this->label7);
			this->Controls->Add(this->书名TextBox);
			this->Controls->Add(this->label6);
			this->Controls->Add(this->开始日期DateTimePicker);
			this->Controls->Add(this->label4);
			this->Controls->Add(this->结束日期DateTimePicker);
			this->Controls->Add(this->label3);
			this->Controls->Add(this->查询报表DataGridView);
			this->Controls->Add(this->打印Button);
			this->Controls->Add(this->查询Button);
			this->Controls->Add(this->出版社TextBox);
			this->Controls->Add(this->label2);
			this->Name = L"ReportBooksForm";
			this->StartPosition = System::Windows::Forms::FormStartPosition::CenterParent;
			this->Text = L"馆藏图书报表";
			(cli::safe_cast<System::ComponentModel::ISupportInitialize^  >(this->查询报表DataGridView))->EndInit();
			this->ResumeLayout(false);
			this->PerformLayout();

		}
#pragma endregion
		public: String^ MyCompany;
		private: System::Data::DataTable^ MyQueryTable;
		private: static String^ MyBooksConnectionString= L"Data Source=.;Initial Catalog=MyBooks;Integrated Security=True;Pooling=False";
		private: System::Void 查询Button_Click(System::Object^  sender, System::EventArgs^  e) {
			MyQueryTable = gcnew System::Data::DataTable();
            SqlConnection^ MyConnection = gcnew SqlConnection();
            MyConnection->ConnectionString = MyBooksConnectionString;
            MyConnection->Open();
            String^ MySQL = "SELECT * FROM 馆藏图书 WHERE (入库数量>遗失数量) AND (入库日期 BETWEEN '" + this->开始日期DateTimePicker->Value.ToString() + "' AND '" + this->结束日期DateTimePicker->Value.ToString() +
                "') AND (类别 LIKE '%" + this->类别TextBox->Text +
                "%') AND (书号 LIKE '%" + this->书号TextBox->Text +
                "%')  AND (书名 LIKE '%" + this->书名TextBox->Text + 
                "%')  AND (作者 LIKE '%" + this->作者TextBox->Text + 
                "%') AND (出版社 LIKE '%" + this->出版社TextBox->Text + "%')";
            SqlDataAdapter^ MyAdapter = gcnew SqlDataAdapter(MySQL, MyConnection);
            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 (this->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(this->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 this->MyQueryTable->Columns)
            {
                MyData[0, Count] = MyNewColumn->ColumnName;
                Count = Count + 1;
            }
            j = 1;
            //输出数据库记录
            for each (DataRow^ MyRow in this->MyQueryTable->Rows)
            {
                for (i = 0; i < this->MyQueryTable->Columns->Count; i++)
                {
                    MyData[j, i] = MyRow[i]->ToString();
                }
                j++;
            }
            MyRange = MyRange->Resize[this->MyQueryTable->Rows->Count + 1, this->MyQueryTable->Columns->Count];
            MyRange->Value2 = MyData;
            MyRange->EntireColumn->AutoFit();
            MyWorkSheet->Cells[2, 2] = this->MyCompany + "图书馆馆藏图书报表";
            MyWorkSheet->Cells[4, 1] = "开始日期：" + this->开始日期DateTimePicker->Value.ToShortDateString() + "　　　　　　　　结束日期：" + this->结束日期DateTimePicker->Value.ToShortDateString();           
 		 }
	};
}