CDataGrid

 enum DataMask { Num, Float, Date, NvChar, Bit };
    [ToolboxBitmap(typeof(System.Windows.Forms.DataGridView))]
    class CDataGrid : DataGridView
    {
        public DataMask val_type;
        public object val_default;
        public bool val_null;
        public CDataGrid()
        {
            val_type = new DataMask();
            val_default = null;
            val_null = true;
            this.CellValidated += new DataGridViewCellEventHandler(CDataGrid_CellValidated);
            this.RowsAdded += new DataGridViewRowsAddedEventHandler(CDataGrid_RowsAdded);
            this.RowsRemoved += new DataGridViewRowsRemovedEventHandler(CDataGrid_RowsRemoved);
            this.CellEndEdit += new DataGridViewCellEventHandler(CDataGrid_CellEndEdit);
        }//构造函数
        public string cValue
        {
            get
            {
                return "'" + this.CurrentCell.Value.ToString() + "'";
            }
        }
        public void PaintEachOtherGridItem(Color cor)
        {
            if (this.Rows.Count != 0)
            {
                for (int i = 0; i < this.Rows.Count; )
                {
                    this.Rows[i].DefaultCellStyle.BackColor = cor;
                    i += 2;
                }
            }
        }//隔行着色
        public void FillTableRow(System.Data.DataTable table)
        {
            this.Visible = false;
            this.Rows.Clear();
            for (int i = 0; i < table.Rows.Count; i++)      //显示到表格
            {
                this.Rows.Add();
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    if (table.Rows[i][j].ToString().Length>0)
                        this.Rows[i].Cells[j + 1].Value = table.Rows[i][j].ToString();
                }
            }
            this.Visible = true;
        }//填充数据，行
        public void Fill(System.Data.DataTable table)
        {
            this.Visible = false;
            this.Rows.Clear();
            this.Columns.Clear();
                       
            //this.ColumnCount = table.Columns.Count + 1;
            this.Columns.Add("sn", "sn");
            this.Columns[0].Visible = false;
            
            foreach (DataColumn col in table.Columns)
                this.Columns.Add(col.ColumnName, col.ColumnName);
            
            FillTableRow(table);
            
            this.Visible = true;
        }
        public void AutosizeColumnsWidth()
        {
            this.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None;
            foreach (DataGridViewColumn col in this.Columns)
                col.Width = 12 * col.Name.Length + 32;
        }//自动调整列宽
        public void Export()
        {
            
            #region range
            int count = this.Columns.Count;
            if (count > 256||count==0) return;//超出Excel的最大行数，或者为零
            string cell = string.Empty;
            int first = count / 26;
            int second = count - (first * 26);
            if (first > 1)
            {
                if (second > 0)
                    cell = ((char)(64 + first)).ToString() + ((char)(64 + second)).ToString();
                else if (second == 0)
                    cell = ((char)(64 + first - 1)).ToString() + ((char)(90)).ToString();
                else
                {
                    MessageBox.Show("Interial Error", "Error Information", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
            }
            else if (first == 1)
            {
                if (second > 0)
                    cell = ((char)(64 + first)).ToString() + ((char)(64 + second)).ToString();
                else if (second == 0)
                    cell = ((char)(90)).ToString();
                else
                {
                    MessageBox.Show("Interial Error", "Error Information", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
            }
            else if (first == 0)
            {
                cell = ((char)(64 + second)).ToString();
            }
            else
            {
                MessageBox.Show("Interial Error", "Error Information", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            cell += "1";
            #endregion
            #region Start Excel
            Excel.Application app = new Excel.ApplicationClass();
            if (app == null)
            {
                MessageBox.Show("Excel无法启动，请确认本机已经正确安装Microsoft Excel。","启动错误",MessageBoxButtons.OK,MessageBoxIcon.Error);
                return;
            }
            app.Visible = false;
            Excel.Workbooks wbs = app.Workbooks;
            Excel.Workbook wb = wbs.Add(Missing.Value);
            Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1];
            #endregion
            Excel.Range r = ws.get_Range("A1", cell); //MessageBox.Show(cell);
            //r.Select();
            r.NumberFormatLocal = "@";                    
            #region header
            object[] objHeader = new object[this.Columns.Count];
            foreach (DataGridViewColumn c in this.Columns)
                objHeader[c.Index] = c.Name;
            #endregion
            #region write data
            r.Value2 = objHeader;
            if (this.Rows.Count > 1)
            {
                r = ws.get_Range("A2", Missing.Value);
                object[,] objData = new Object[this.Rows.Count, this.Columns.Count];
                for (int i = 0; i < this.Rows.Count - 1; i++)
                {
                    foreach (DataGridViewColumn col in this.Columns)
                    {
                        if (this.Rows[i].Cells[col.Index].Value != null)
                            objData[i, col.Index] = this.Rows[i].Cells[col.Index].Value;
                    }
                }
                
                r = r.get_Resize(this.Rows.Count, this.Columns.Count);
                r.Select(); //选中
                r.NumberFormatLocal = "@";  //文本格式
                r.Value2 = objData;
                r.EntireColumn.AutoFit();   //自动调整列宽
                r.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;    //居中
            }
            #endregion
            app.Visible = true;
             
        }//导出到Microsoft Excel
        #region ///验证
        private void CDataGrid_CellValidated(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex == this.Rows.Count - 1) return;
            if (CurrentCell.ReadOnly) return;//单元格只读，则返回
            #region default value
            if (CurrentCell.Value == null)//单元格为null
            {
                if (val_null) //允许为空
                {                    
                    CurrentCell.ErrorText =string.Empty;
                }
                else//不允许为空
                {
                    if (val_default != null)//默认值存在
                    {
                        CurrentCell.Value = val_default;
                        CurrentCell.ErrorText = string.Empty;
                    }//恢复为默认值
                    else    //默认值不存在
                        CurrentCell.ErrorText = "内容尚未填充！";
                }
                return;//返回
            }
            #endregion
            #region 验证
            switch (val_type)
            {
                case DataMask.Num:
                    {
                        if (!Regex.IsMatch(CurrentCell.Value.ToString(), @"^([0-9]*)$"))
                            CurrentCell.ErrorText = "错误的数字格式";
                        else CurrentCell.ErrorText = String.Empty;
                        break;
                    }
                case DataMask.Float:
                    {
                        if (!Regex.IsMatch(CurrentCell.Value.ToString().ToString(), @"^(-?"d+)("."d+)?$"))
                            CurrentCell.ErrorText = "错误的浮点数据格式";
                        else CurrentCell.ErrorText = String.Empty;
                        break;
                    }
                case DataMask.Date:
                    {
                        if (!Regex.IsMatch(CurrentCell.Value.ToString().ToString(), @"^(?<month>(0?[1-9])|1[0-2])(-|/)(?<day>(0?[1-9])|((1|2)[0-9])|30|31)(-|/)(?<year>"d{4})"))
                            CurrentCell.ErrorText = "数据格式: mm-dd-yyyy";
                        else CurrentCell.ErrorText = String.Empty;
                        break;
                    }
                case DataMask.Bit:
                    {
                        if (CurrentCell.Value.ToString() != "1" && CurrentCell.Value.ToString() != "0")
                            this.Rows[e.RowIndex].Cells[e.ColumnIndex].ErrorText = "错误的Bool格式";
                        else this.Rows[e.RowIndex].Cells[e.ColumnIndex].ErrorText = String.Empty;
                        break;
                    }
                case DataMask.NvChar:
                    {
                        CurrentCell.ErrorText = String.Empty;
                        break;
                    }
                default:
                    {
                        break;
                    }
            }
            #endregion

        }
        private void CDataGrid_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {
            this.Rows[e.RowIndex].ErrorText = String.Empty;
        } // Clear the row error in case the user presses ESC.
        #endregion
        #region ///添加, 删除行
        private void CDataGrid_RowsAdded(object sender, DataGridViewRowsAddedEventArgs e)
        {
            foreach (DataGridViewRow row in this.Rows)
            {
                row.Cells[0].Value = row.Index + 1;
            }
        }
        private void CDataGrid_RowsRemoved(object sender, DataGridViewRowsRemovedEventArgs e)
        {
            foreach (DataGridViewRow row in this.Rows)
            {
                row.Cells[0].Value = row.Index + 1;
            }
        }
        #endregion
    }