queryleasehouseform.cs

C++编写的房产中介管理系统

//文件名：QueryLeaseHouseForm.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

using System.Data.SqlClient;
using Microsoft.Office.Interop.Excel;
using System.Reflection;
using Microsoft.Office.Core;

namespace MyHouse
{
    public partial class QueryLeaseHouseForm : Form
    {
        public QueryLeaseHouseForm()
        {
            InitializeComponent();
        }     
        public string MyCompany;
        private void QueryLeaseHouseForm_Load(object sender, EventArgs e)
        {
            String MySQLConnectionString = global::MyHouse.Properties.Settings.Default.MyHouseConnectionString;
            string MySQL = "Select * From 房源参数字典 Where 自编号='CQ001'";
            SqlConnection MyConnection = new SqlConnection(MySQLConnectionString);
            MyConnection.Open();
            System.Data.DataTable MyHouseTable = new System.Data.DataTable();
            SqlDataAdapter MyAdapter = new SqlDataAdapter(MySQL, MyConnection);
            MyAdapter.Fill(MyHouseTable);
            string MyName = "城区";
            string MyValue = MyHouseTable.Rows[0][MyName].ToString();
            String[] MyDistData = MyValue.Split(new char[1] { '、' });
            foreach (String MyString in MyDistData)
            {
                this.所在城区ComboBox.Items.Add(MyString);
            }
            MyName = "装修";
            MyValue = MyHouseTable.Rows[0][MyName].ToString();
            String[] MyDecoData = MyValue.Split(new char[1] { '、' });
            foreach (String MyString in MyDecoData)
            {
                this.装修级别ComboBox.Items.Add(MyString);
            }

            if (MyConnection.State == ConnectionState.Open)
            {
                MyConnection.Close();
            }
        }

        private System.Data.DataTable MyQueryTable;
        private void 查询Button_Click(object sender, EventArgs e)
        {
            MyQueryTable = new System.Data.DataTable();
            SqlConnection MyConnection = new SqlConnection();
            MyConnection.ConnectionString = global::MyHouse.Properties.Settings.Default.MyHouseConnectionString;
            MyConnection.Open();
            string MySQL = "SELECT * FROM 可租房源视图 WHERE ('" + this.当前日期DateTimePicker.Value.ToString() + "' BETWEEN 委托日期 AND 截止日期) AND (所在城区 LIKE '%" + this.所在城区ComboBox.Text + "%') AND (装修级别 LIKE '%" + this.装修级别ComboBox.Text + "%') AND (房=" + this.房TextBox.Text + ") AND (厅=" + this.厅TextBox.Text + ") AND (建筑面积 BETWEEN " + this.最小面积TextBox.Text + " AND " + this.最大面积TextBox.Text + ") AND (出租价格 BETWEEN " + this.最低价格TextBox.Text + " AND " + this.最高价格TextBox.Text + ")";
            SqlDataAdapter MyAdapter = new SqlDataAdapter(MySQL, MyConnection);
            MyAdapter.Fill(MyQueryTable);
            this.查询报表DataGridView.DataSource = MyQueryTable;
            if (MyConnection.State == ConnectionState.Open)
            {
                MyConnection.Close();
            }
        }

        private void 打印Button_Click(object sender, EventArgs e)
        {
            if (this.MyQueryTable.Rows.Count < 1)
            {
                return;
            }
            //导出Excel表格数据文件
            ApplicationClass MyExcel;
            Workbooks MyWorkBooks;
            Workbook MyWorkBook;
            Worksheet MyWorkSheet;
            char MyColumns;
            Range MyRange;
            Object[,] MyData = new Object[500, 35];
            int i, j;
            MyExcel = new ApplicationClass();
            MyExcel.Visible = true;
            if (MyExcel == null)
            {
                MessageBox.Show("Excel程序无法启动！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            MyWorkBooks = MyExcel.Workbooks;
            MyWorkBook = MyWorkBooks.Add(Missing.Value);
            MyWorkSheet = (Worksheet)MyWorkBook.Worksheets[1];
            MyColumns = (char)(this.MyQueryTable.Columns.Count + 64);
            MyRange = MyWorkSheet.get_Range("A5", MyColumns.ToString() + "5");
            int Count = 0;
            foreach (DataColumn MyNewColumn in this.MyQueryTable.Columns)
            {
                MyData[0, Count] = MyNewColumn.ColumnName;
                Count = Count + 1;
            }
            j = 1;
            //输出数据库记录
            foreach (DataRow MyRow in this.MyQueryTable.Rows)
            {
                for (i = 0; i < this.MyQueryTable.Columns.Count; i++)
                {
                    MyData[j, i] = MyRow[i].ToString();
                }
                j++;
            }
            MyRange = MyRange.get_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.所在城区ComboBox.Text + "]， 装修级别[ " + this.装修级别ComboBox.Text + "]， [" + this.房TextBox.Text + "]房[" + this.厅TextBox.Text + "]厅， 建筑面积 [" + this.最小面积TextBox.Text + "]至[" + this.最大面积TextBox.Text + "]平方米，价格[" + this.最低价格TextBox.Text + "]至[" + this.最高价格TextBox.Text + "]元"; ;
        }
    }
}