queryconsumeform.cs

一个很好的宾馆管理系统 VC++和SQL做的

//文件名：QueryConsumeForm.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 MyHotel
{
    public partial class QueryConsumeForm : Form
    {
        public QueryConsumeForm()
        {
            InitializeComponent();
        }
        public string MyOperator;
        public String MyCompany;
        public System.Data.DataTable MyQueryTable;
        private void QueryConsumeForm_Load(object sender, EventArgs e)
        {
            String MySQLConnectionString = global::MyHotel.Properties.Settings.Default.MyHotelConnectionString;
            SqlConnection MyConnection = new SqlConnection(MySQLConnectionString);
            MyConnection.Open();
            System.Data.DataTable MyGoodsTable = new System.Data.DataTable();
            string MySQL = "Select DISTINCT(名称) From 消费物品";
            SqlDataAdapter MyAdapter = new SqlDataAdapter(MySQL, MyConnection);
            MyAdapter.Fill(MyGoodsTable);
            this.商品名称ComboBox.DataSource = MyGoodsTable;
            this.商品名称ComboBox.DisplayMember = "名称";
            this.商品名称ComboBox.ValueMember = "名称";
            if (MyConnection.State == ConnectionState.Open)
            {
                MyConnection.Close();
            }
        }
        private void 查询Button_Click(object sender, EventArgs e)
        {
            String MySQLConnectionString = global::MyHotel.Properties.Settings.Default.MyHotelConnectionString;
            SqlConnection MyConnection = new SqlConnection(MySQLConnectionString);
            MyConnection.Open();
            MyQueryTable = new System.Data.DataTable();
            string MySQL = "Select * From 消费品分类查询视图 Where 消费品名称='"+this.商品名称ComboBox.Text+"' AND (记帐时间 Between  '" + this.开始日期DateTimePicker.Value + "' AND '" + this.结束日期DateTimePicker.Value + "')";
            SqlCommand MyCommand = new SqlCommand(MySQL, MyConnection);            
            SqlDataAdapter MyAdapter = new SqlDataAdapter(MyCommand);
            MyAdapter.Fill(MyQueryTable);
            this.消费品分类DataGridView.DataSource = MyQueryTable;
            if (MyConnection.State == ConnectionState.Open)
            {
                MyConnection.Close();
            }
        }

        private void 打印Button_Click(object sender, EventArgs e)
        {
            if (MyQueryTable.Rows.Count < 1)
            {
                return;
            }
            //导出Excel表格数据文件
            ApplicationClass MyExcel;
            Workbooks MyWorkBooks;
            Workbook MyWorkBook;
            Worksheet MyWorkSheet;
            char MyColumns;
            Range MyRange;
            Object[,] MyData = new Object[5000, 30];
            int Count, 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)(MyQueryTable.Columns.Count + 64);
            MyRange = MyWorkSheet.get_Range("A5", MyColumns.ToString() + "5");
            Count = 0;
            foreach (DataColumn MyNewColumn in MyQueryTable.Columns)
            {
                MyData[0, Count] = MyNewColumn.ColumnName;
                Count = Count + 1;
            }
            j = 1;
            //输出数据库记录
            foreach (DataRow MyRow in MyQueryTable.Rows)
            {
                for (i = 0; i < MyQueryTable.Columns.Count; i++)
                {
                    MyData[j, i] = MyRow[i].ToString();
                }
                j++;
            }
            MyRange = MyRange.get_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();
        }
    }
}