FormExcel.cs

using System;
using System.Data;
using System.IO;
using System.Text;
using System.Windows.Forms;

namespace WinFormExcel
{
    public partial class FormExcel : Form
    {
        private DataTable table;
        private DataRow dataRow;

        public FormExcel()
        {
            #region
            InitializeComponent();
            table = new DataTable("Sell");
            table.Locale = System.Globalization.CultureInfo.InvariantCulture;
            DataColumn column = table.Columns.Add("ID", typeof(String));
            table.Columns.Add("ProductName", typeof(String));
            table.Columns.Add("ClientName", typeof(String));
            table.Columns.Add("Price", typeof(Decimal)).DefaultValue = 0.00M;
            table.Columns.Add("Number", typeof(Int32)).DefaultValue = 0;
            table.Columns.Add("Total", typeof(Decimal)).Expression = "Price * Number";
            table.Columns.Add("Date", typeof(DateTime)).DefaultValue = DateTime.Today;
            table.Constraints.Add("PK", column, true); // 创建主键。
            FileInfo info = new FileInfo("Data.xml");
            if (info.Exists)
                using (StreamReader reader = info.OpenText())
                {
                    table.Clear();
                    table.BeginLoadData();
                    table.ReadXml(reader);
                    table.EndLoadData();
                    table.AcceptChanges();
                }
            DataGridViewStyle();
            cmbSpan.DropDownStyle = ComboBoxStyle.DropDownList;
            cmbSpan.DataSource = new string[] { "今日销售", "本月销售", "本季度销售", "本年度销售" };
            cmbClientName.DropDownStyle = ComboBoxStyle.DropDownList;
            cmbClientName.DataSource = new string[] { "", "联想", "华硕", "惠普", "明基", "宏基" };
            cmbProductName.DropDownStyle = ComboBoxStyle.DropDownList;
            cmbProductName.DataSource = new string[] { "", "SAMSUNG", "LG", "SONY", "Apple", "IBM" };
            btnCancel_Click(null, EventArgs.Empty);
            #endregion
        }

        #region AddRow
        private void btnAdd_Click(object sender, EventArgs e)
        {
            if (this.ValidateItem())
                return;
            try
            {
                dataRow = table.NewRow();
                dataRow.SetField<string>("ID", maskedTextID.Text);
                this.SetField();
                table.Rows.Add(dataRow);
                btnCancel.PerformClick();
                btnSave.Enabled = true;
            }
            catch
            {
                dataRow.ClearErrors();
                MessageBox.Show(string.Format("订单编号“{0}”添加失败！", maskedTextID.Text), "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }
        #endregion

        #region ValidateItem()
        private bool ValidateItem()
        {
            decimal price;
            if (cmbProductName.SelectedIndex < 1)
            {
                MessageBox.Show("请选择商品名称！", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                cmbProductName.DroppedDown = true;
                cmbProductName.Focus();
            }
            else if (cmbClientName.SelectedIndex < 1)
            {
                MessageBox.Show("请选择客户名称！", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                cmbClientName.DroppedDown = true;
                cmbClientName.Focus();
            }
            else if (!decimal.TryParse(txtPrice.Text, out price))
            {
                MessageBox.Show("订单单价格式不正确，请输入订单单价！", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                txtPrice.Focus();
                txtPrice.SelectAll();
            }
            else if (txtNumber.TextLength < 1)
            {
                MessageBox.Show("订单数量不能为空，请输入订单数量！", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                txtNumber.Focus();
            }
            else if (dtpDate.Value.Date > DateTime.Today)
            {
                MessageBox.Show("订单日期不能超过当前日期！", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                dtpDate.ResetText();
            }
            else
                return false;
            return true;
        }
        #endregion

        #region SetField
        private void SetField()
        {
            dataRow.SetField<string>("ProductName", cmbProductName.SelectedValue as string);
            dataRow.SetField<string>("ClientName", cmbClientName.SelectedValue as string);
            dataRow.SetField<decimal>("Price", decimal.Parse(txtPrice.Text));
            dataRow.SetField<int>("Number", int.Parse(txtNumber.Text));
            dataRow.SetField<DateTime>("Date", dtpDate.Value.Date);
        }
        #endregion

        #region UpdateRow
        private void btnModify_Click(object sender, EventArgs e)
        {
            if (this.ValidateItem())
                return;
            try
            {
                dataRow = (gridView.CurrentRow.DataBoundItem as DataRowView).Row;
                dataRow.BeginEdit();
                this.SetField();
                dataRow.EndEdit();
                btnCancel.PerformClick();
                btnSave.Enabled = true;
            }
            catch
            {
                dataRow.ClearErrors();
                dataRow.CancelEdit();
                MessageBox.Show(string.Format("订单编号“{0}”修改失败！", maskedTextID.Text), "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }
        #endregion

        #region DeleteRow
        private void btnDelete_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("真的要删除此记录吗？", "确定删除", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.OK)
            {
                try
                {
                    dataRow = (gridView.CurrentRow.DataBoundItem as DataRowView).Row;
                    dataRow.Delete();
                    btnCancel.PerformClick();
                    btnSave.Enabled = true;
                }
                catch
                {
                    dataRow.ClearErrors();
                    dataRow.RejectChanges();
                    MessageBox.Show(string.Format("订单编号“{0}”删除失败！", maskedTextID.Text), "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
            }
        }
        #endregion

        #region RowFilter
        private void btnQuery_Click(object sender, EventArgs e)
        {
            StringBuilder sb = new StringBuilder();
            if (cmbProductName.SelectedIndex > 0)
                sb.AppendFormat("ProductName='{0}'", cmbProductName.SelectedValue);
            if (cmbClientName.SelectedIndex > 0)
            {
                if (sb.Length > 0)
                    sb.Append(" and ");
                sb.AppendFormat("ClientName='{0}'", cmbClientName.SelectedValue);
            }
            if (dtpDate.Checked)
            {
                if (sb.Length > 0)
                    sb.Append(" and ");
                sb.AppendFormat("Date='#{0}#'", dtpDate.Value.Date);
            }
            table.DefaultView.RowFilter = sb.ToString();
            sb.Capacity = sb.Remove(0, sb.Length).Length;
            sb = null;
            if (table.DefaultView.Count < 1)
                MessageBox.Show("没有您要的信息。", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            this.SetEnabled(true);
        }
        #endregion

        #region SaveData
        private void btnSave_Click(object sender, EventArgs e)
        {
            FileInfo info = new FileInfo("Data.xml");
            if (info.Exists)
                info.Attributes = FileAttributes.Normal;
            using (StreamWriter writer = info.CreateText())
            {
                table.AcceptChanges();
                table.WriteXml(writer, XmlWriteMode.IgnoreSchema);
            }
            btnSave.Enabled = false;
            btnCancel.PerformClick();
        }
        #endregion

        #region CancelButton
        private void btnCancel_Click(object sender, EventArgs e)
        {
            gbText.ClearText();
            this.SetEnabled(true);
            maskedTextID.Text = table.DefaultView.GetNewID("PS");
        }
        #endregion

        #region SetEnabled
        private void SetEnabled(bool flag)
        {
            btnAdd.Enabled = flag;
            btnModify.Enabled = !flag;
            btnDelete.Enabled = !flag;
            dtpDate.Checked = !flag;
        }
        #endregion

        #region OnFormClosing
        protected override void OnFormClosing(FormClosingEventArgs e)
        {
            base.OnFormClosing(e);
            if (btnSave.Enabled)
            {
                switch (MessageBox.Show("是否保存对“订单信息”的更改？", "系统询问", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question))
                {
                    case DialogResult.Yes:
                        btnSave.PerformClick();
                        break;
                    case DialogResult.No:
                        break;
                    case DialogResult.Cancel:
                        e.Cancel = true;
                        break;
                }
            }
        }
        #endregion

        #region KeyPressEventArgs
        private void txtPrice_KeyPress(object sender, KeyPressEventArgs e)
        {
            e.Handled = !(char.IsDigit(e.KeyChar) || e.KeyChar == '.' || (Keys)e.KeyChar == Keys.Back || (Keys)e.KeyChar == Keys.Delete);
        }

        private void txtNumber_KeyPress(object sender, KeyPressEventArgs e)
        {
            e.Handled = !(char.IsDigit(e.KeyChar) || (Keys)e.KeyChar == Keys.Back || (Keys)e.KeyChar == Keys.Delete);
        }
        #endregion

        #region CurrentRow.DataBoundItem
        private void gridView_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex < 0)
                return;
            dataRow = (gridView.CurrentRow.DataBoundItem as DataRowView).Row;
            maskedTextID.Text = dataRow.Field<string>("ID");
            cmbProductName.Text = dataRow.Field<string>("ProductName");
            cmbClientName.Text = dataRow.Field<string>("ClientName");
            txtPrice.Text = dataRow.Field<decimal>("Price") + "";
            txtNumber.Text = dataRow.Field<int>("Number") + "";
            dtpDate.Value = dataRow.Field<DateTime>("Date");
            this.SetEnabled(false);
        }
        #endregion

        private void btnExcel_Click(object sender, EventArgs e)
        {
            gridView.PrintToExcel("订单统计表");
        }

        #region DataGridViewStyle
        private void DataGridViewStyle()
        {
            DataGridViewTextBoxColumn columnID = new DataGridViewTextBoxColumn();
            columnID.DataPropertyName = "ID";
            columnID.HeaderText = "编号";
            gridView.Columns.Add(columnID);
            DataGridViewTextBoxColumn columnProductName = new DataGridViewTextBoxColumn();
            columnProductName.DataPropertyName = "ProductName";
            columnProductName.HeaderText = "商品名称";
            gridView.Columns.Add(columnProductName);
            DataGridViewTextBoxColumn columnClientName = new DataGridViewTextBoxColumn();
            columnClientName.DataPropertyName = "ClientName";
            columnClientName.HeaderText = "客户名称";
            gridView.Columns.Add(columnClientName);
            DataGridViewTextBoxColumn columnPrice = new DataGridViewTextBoxColumn();
            columnPrice.DataPropertyName = "Price";
            columnPrice.DefaultCellStyle.Alignment = System.Windows.Forms.DataGridViewContentAlignment.MiddleRight;
            columnPrice.DefaultCellStyle.Format = "￥#,##0.00;￥-#,##0.00;￥0.00";
            columnPrice.HeaderText = "单价";
            gridView.Columns.Add(columnPrice);
            DataGridViewTextBoxColumn columnNumber = new DataGridViewTextBoxColumn();
            columnNumber.DataPropertyName = "Number";
            columnNumber.DefaultCellStyle.Alignment = System.Windows.Forms.DataGridViewContentAlignment.MiddleRight;
            columnNumber.DefaultCellStyle.Format = "#,##0;-#,##0;0";
            columnNumber.HeaderText = "数量";
            gridView.Columns.Add(columnNumber);
            DataGridViewTextBoxColumn columnTotal = new DataGridViewTextBoxColumn();
            columnTotal.DataPropertyName = "Total";
            columnTotal.DefaultCellStyle.Alignment = System.Windows.Forms.DataGridViewContentAlignment.MiddleRight;
            columnTotal.DefaultCellStyle.Format = "￥#,##0.00;￥-#,##0.00;￥0.00";
            columnTotal.HeaderText = "总金额";
            gridView.Columns.Add(columnTotal);
            DataGridViewTextBoxColumn columnDate = new DataGridViewTextBoxColumn();
            columnDate.DataPropertyName = "Date";
            columnDate.DefaultCellStyle.Format = "yyyy-MM-dd";
            columnDate.HeaderText = "日期";
            gridView.Columns.Add(columnDate);
            gridView.BorderStyle = BorderStyle.Fixed3D;
            gridView.AutoGenerateColumns = false; // 禁用自动创建列。
            gridView.AllowUserToAddRows = false; // 隐藏添加行。
            gridView.AllowUserToDeleteRows = false; // 禁用删除行。
            gridView.AllowUserToResizeRows = false; // 禁用调整行的大小。
            gridView.AllowUserToResizeColumns = false; // 禁用调整列的大小。
            gridView.AllowUserToOrderColumns = false; // 禁用列重新定位。
            gridView.ReadOnly = true; // 禁用编辑单元格。
            gridView.MultiSelect = false; // 用户仅能选择一个单元格、行或列。
            gridView.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.BottomCenter;
            gridView.SelectionMode = DataGridViewSelectionMode.FullRowSelect; // 选定整个行。
            gridView.DataSource = table.DefaultView;
        }
        #endregion

        #region TimeSpanRowFilter
        private void buttonFind_Click(object sender, EventArgs e)
        {
            DateTime beginDate = DateTime.Today, endDate = DateTime.Today;
            int year = DateTime.Today.Year;
            int month = DateTime.Today.Month;
            switch (cmbSpan.SelectedIndex)
            {
                case 0:
                    break;
                case 1:
                    beginDate = beginDate.AddDays(1 - beginDate.Day);
                    endDate = beginDate.AddMonths(1).AddDays(-1);
                    break;
                case 2:
                    switch ((month + 2) / 3)
                    {
                        case 1:
                            beginDate = new DateTime(year, 1, 1);
                            endDate = new DateTime(year, 3, 31);
                            break;
                        case 2:
                            beginDate = new DateTime(year, 4, 1);
                            endDate = new DateTime(year, 6, 30);
                            break;
                        case 3:
                            beginDate = new DateTime(year, 7, 1);
                            endDate = new DateTime(year, 9, 30);
                            break;
                        case 4:
                            beginDate = new DateTime(year, 10, 1);
                            endDate = new DateTime(year, 12, 31);
                            break;
                    }
                    break;
                case 3:
                    beginDate = new DateTime(year, 1, 1);
                    endDate = new DateTime(year, 12, 31);
                    break;
            }
            table.DefaultView.RowFilter = string.Format("'#{0}#'<=Date and Date<='#{1}#'", beginDate, endDate);
        }
        #endregion
    }
}