MES_Client/TGMES/Core.Mes.Client.Comm/Control/GridHelper.cs

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Core.Mes.Client.Comm.Tool;
using Infragistics.Win.UltraWinGrid;
using Infragistics.Win.UltraWinDataSource;
using Infragistics.Win;

namespace Core.Mes.Client.Comm.Control
{
    /// <summary>
    /// Grid数据处理类
    /// </summary>
    public class GridHelper
    {
        /// <summary>
        /// 删除记录激活指定行
        /// </summary>
        /// <param name="grid">ultraGrid</param>
        /// <param name="delRowIndex">删除行的索引</param>
        public static void GridAfterDelRow_ReSelectRow(ref Infragistics.Win.UltraWinGrid.UltraGrid grid, int delRowIndex)
        {
            if (grid.Rows.Count == 0) return;

            if ((delRowIndex + 1) > grid.Rows.Count)
            {
                grid.Rows[delRowIndex - 1].Activate();
                grid.Rows[delRowIndex - 1].Selected = true;
            }
            else
            {
                grid.Rows[delRowIndex].Activate();
                grid.Rows[delRowIndex].Selected = true;
            }
        }

        /// <summary>
        /// 清除UltraGrid的DataSource的数据
        /// </summary>
        /// <param name="ulGrid"></param>
        public static void ClearGridDataSourceData(Infragistics.Win.UltraWinGrid.UltraGrid ulGrid)
        {
            if (ulGrid.DataSource is DataSet)
            {
                for (int i = ((DataSet)ulGrid.DataSource).Tables.Count - 1; i >= 0; i--)
                {
                    ((DataSet)ulGrid.DataSource).Tables[i].Rows.Clear();
                }
            }
        }

        /// <summary>
        /// 将ultragrid的数据导出到Excel中
        /// </summary>
        /// <param name="ulGrid">要导出数据的ulGrid名称</param>
        /// <param name="sFileName">Excel文件名</param>
        public static void ulGridToExcel(Infragistics.Win.UltraWinGrid.UltraGrid ulGrid, string sFileName, bool showMergedCell)
        {
            try
            {
                Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter ulGridExt = new Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter();
                //ulGridExt.CellExporting += new Infragistics.Win.UltraWinGrid.ExcelExport.CellExportingEventHandler(ultraGridExcelExporter1_CellExporting);
                System.Windows.Forms.SaveFileDialog saveFileDialog1 = new SaveFileDialog();
                if (ulGrid.Rows.Count == 0)
                {
                    MessageBox.Show("没有数据，无法导出！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    return;
                }

                if (showMergedCell)
                    ulGridExt.CellExported += (sender, e) =>
                    {
                        // Get the cell being exporter
                        var cell = e.GridRow.Cells[e.GridColumn];

                        // Get the Merged cells.
                        var mergedCells = cell.GetMergedCells();

                        // Check to see if there are any merged cells. If not, we don't need to do anything.
                        if (mergedCells != null)
                        {
                            // There are merged cells. Check to see if the cell being exported is the first one.
                            var isFirstCell = true;
                            foreach (var mergedCell in mergedCells)
                                if (cell.Row.Index > mergedCell.Row.Index)
                                {
                                    // If the cell being exported has a higher index that any of the cells
                                    // it is merged with, them it's not the first cell and we should already
                                    // have handled the merge.
                                    isFirstCell = false;
                                    break;
                                }

                            // If it is the first cell, merge this cell with the cells below it based on the
                            // count of the merged cells.
                            if (isFirstCell)
                            {
                                var rowIndex = e.CurrentRowIndex;
                                var colIndex = e.CurrentColumnIndex;
                                e.CurrentWorksheet.MergedCellsRegions.Add(
                                    rowIndex,
                                    colIndex,
                                    rowIndex + (mergedCells.Length - 1),
                                    colIndex);
                            }
                        }
                    };


                saveFileDialog1.FileName = sFileName + DateTime.Now.ToString("yyyyMMdd");
                saveFileDialog1.Filter = "Excel文件(*.xls)|*.xls";
                if (saveFileDialog1.ShowDialog() == DialogResult.OK)
                {
                    string sFullName = saveFileDialog1.FileName;
                    ulGridExt.Export(ulGrid, sFullName);
                    ProcessStartInfo p = new ProcessStartInfo(sFullName);
                    p.WorkingDirectory = Path.GetDirectoryName(sFullName);
                    Process.Start(p);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("导出失败,原因:" + ex.Message, "错误信息", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }


        /// <summary>
        /// 将ultragrid的数据导出到Excel中
        /// </summary>
        /// <param name="ulGrid">要导出数据的ulGrid名称</param>
        /// <param name="sFileName">Excel文件名</param>
        public static void ulGridToExcel(Infragistics.Win.UltraWinGrid.UltraGrid ulGrid, string sFileName)
        {
            ulGridToExcel(ulGrid, sFileName, false);
            
        }

        /// <summary>
        /// 将ultragrid的数据导出到Excel中
        /// </summary>
        /// <param name="ulGridList">ulGrid组</param>
        /// <param name="sheetNameList">顺序对应的Sheet名称</param>
        /// <param name="sFileName">Excel文件名</param>
        public static void ulGridToExcel(ArrayList ulGridList, ArrayList sheetNameList, string sFileName)
        {
            try
            {
                Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter ulGridExt = new Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter();
                //ulGridExt.CellExporting += new Infragistics.Win.UltraWinGrid.ExcelExport.CellExportingEventHandler(ultraGridExcelExporter1_CellExporting);
                System.Windows.Forms.SaveFileDialog saveFileDialog1 = new SaveFileDialog();

                saveFileDialog1.FileName = sFileName + DateTime.Now.ToString("yyMMdd") + ".xls";
                if (saveFileDialog1.ShowDialog() == DialogResult.OK)
                {
                    string sFullName = saveFileDialog1.FileName;
                    Infragistics.Excel.Workbook workbook1 = new Infragistics.Excel.Workbook();
                    for (int i = 0; i < ulGridList.Count; i++)
                    {
                        Infragistics.Win.UltraWinGrid.UltraGrid itemGrid = ulGridList[i] as Infragistics.Win.UltraWinGrid.UltraGrid;
                        string sheetname = (sheetNameList.Count > i && sheetNameList[i].ToString() != "")
                            ? sheetNameList[i].ToString() : ("sheet" + i.ToString());
                        workbook1.Worksheets.Add(sheetname);
                        //没有数据时会出错,所以没有数据的面不不调用
                        if (itemGrid.Rows.Count > 0)
                            ulGridExt.Export(itemGrid, workbook1.Worksheets[i]);
                    }
                    workbook1.Save(sFullName);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("导出失败,原因:" + ex.Message);
            }
        }

        /// <summary>
        /// 清除Grid的列过滤
        /// </summary>
        /// <param name="ulGrid">Grid名称</param>
        public static void ClearUlGridFilter(Infragistics.Win.UltraWinGrid.UltraGrid ulGrid)
        {
            ulGrid.DisplayLayout.Bands[0].ColumnFilters.ClearAllFilters();
        }

        /// <summary>
        /// 增加Grid的列过滤
        /// </summary>
        /// <param name="ulGrid"></param>
        public static void AddUlGridFilter(Infragistics.Win.UltraWinGrid.UltraGrid ulGrid)
        {
            ulGrid.DisplayLayout.Override.AllowRowFiltering = Infragistics.Win.DefaultableBoolean.True;
        }

        /// <summary>
        /// 刷新Grid数据并根据数据调整Grid列宽
        /// </summary>
        /// <param name="ultraGrid">需要处理的Grid</param>
        public static void RefreshAndAutoSize(Infragistics.Win.UltraWinGrid.UltraGrid ultraGrid)
        {
            try
            {
                ultraGrid.DataBind();

                foreach (Infragistics.Win.UltraWinGrid.UltraGridBand band in ultraGrid.DisplayLayout.Bands)
                {
                    foreach (Infragistics.Win.UltraWinGrid.UltraGridColumn column in band.Columns)
                    {
                        column.PerformAutoResize(Infragistics.Win.UltraWinGrid.PerformAutoSizeType.AllRowsInBand);
                    }
                }

                ultraGrid.Refresh();
            }
            catch { }
        }

        /// <summary>
        /// 刷新Grid数据并根据数据调整Grid列宽 方法名错，弃用
        /// </summary>
        /// <param name="ultraGrid">需要处理的Grid</param>
        /// <param name="cols">不需要调整列宽的列</param>
        public static void RefreshAndAutoSizeExceptRows(Infragistics.Win.UltraWinGrid.UltraGrid ultraGrid, Infragistics.Win.UltraWinGrid.UltraGridColumn[] cols)
        {
            RefreshAndAutoSizeExceptColumns(ultraGrid, cols);
        }

        /// <summary>
        /// 刷新Grid数据并根据数据调整Grid列宽
        /// </summary>
        /// <param name="ultraGrid">需要处理的Grid</param>
        /// <param name="cols">不需要调整列宽的列</param>
        public static void RefreshAndAutoSizeExceptColumns(Infragistics.Win.UltraWinGrid.UltraGrid ultraGrid, Infragistics.Win.UltraWinGrid.UltraGridColumn[] cols)
        {
            try
            {
                ultraGrid.DataBind();

                foreach (Infragistics.Win.UltraWinGrid.UltraGridBand band in ultraGrid.DisplayLayout.Bands)
                {
                    foreach (Infragistics.Win.UltraWinGrid.UltraGridColumn column in band.Columns)
                    {
                        if (cols != null && cols.Contains(column))
                        {
                            continue;
                        }
                        column.PerformAutoResize(Infragistics.Win.UltraWinGrid.PerformAutoSizeType.AllRowsInBand);
                    }
                }

                ultraGrid.Refresh();
            }
            catch { }
        }

        /// <summary>
        /// 刷新Grid数据并根据数据调整Grid列宽
        /// </summary>
        /// <param name="ultraGrid">需要处理的Grid</param>
        /// <param name="cols">不需要调整列宽的列</param>
        public static void RefreshAndAutoSizeExceptColumns(Infragistics.Win.UltraWinGrid.UltraGrid ultraGrid, params string[] columns)
        {
            try
            {
                ultraGrid.DataBind();
                foreach (Infragistics.Win.UltraWinGrid.UltraGridBand band in ultraGrid.DisplayLayout.Bands)
                {
                    foreach (Infragistics.Win.UltraWinGrid.UltraGridColumn column in band.Columns)
                    {
                        if (columns != null && columns.Contains(column.Key))
                        {
                            continue;
                        }
                        column.PerformAutoResize(Infragistics.Win.UltraWinGrid.PerformAutoSizeType.AllRowsInBand);
                    }
                }
                ultraGrid.Refresh();
            }
            catch { }
        }

        /// <summary>
        /// 复制DataTable
        /// </summary>
        /// <param name="src">源DataTable</param>
        /// <param name="dest">目标DataTable</param>
        /// <param name="ClearExists">是否清除目标DataTable现有数据</param>
        public static void CopyDataToDatatable(ref DataTable src, ref DataTable dest, bool ClearExists)
        {
            if (src == null || dest == null)
            {
                return;
            }

            if (ClearExists)
            {
                dest.Rows.Clear();
            }

            DataRow CurRow, NewRow;

            for (int i = 0; i < src.Rows.Count; i++)
            {
                CurRow = src.Rows[i];
                NewRow = dest.NewRow();

                for (int j = 0; j < src.Columns.Count; j++)
                {
                    try
                    {
                        if (dest.Columns.Contains(src.Columns[j].ColumnName))
                        {
                            NewRow[src.Columns[j].ColumnName] = CurRow[j];
                        }
                    }
                    catch { }
                }

                dest.Rows.Add(NewRow);
            }
        }

		 /// <summary>
        /// 复制DataTable
        /// </summary>
        /// <param name="src">源DataTable</param>
        /// <param name="dest">目标DataTable</param>
        /// <param name="ClearExists">是否清除目标DataTable现有数据</param>
        public static void CopyDataToDatatable(DataTable src, DataTable dest, bool ClearExists)
        {
            if (src == null || dest == null)
            {
                return;
            }

            if (ClearExists)
            {
                dest.Rows.Clear();
            }

            DataRow CurRow, NewRow;

            for (int i = 0; i < src.Rows.Count; i++)
            {
                CurRow = src.Rows[i];
                NewRow = dest.NewRow();

                for (int j = 0; j < src.Columns.Count; j++)
                {
                    try
                    {
                        if (dest.Columns.Contains(src.Columns[j].ColumnName))
                        {
                            NewRow[src.Columns[j].ColumnName] = CurRow[j];
                        }
                    }
                    catch { }
                }

                dest.Rows.Add(NewRow);
            }
        }

        /// <summary>
        /// 复制DataTable并添加ArrayList数据列
        /// </summary>
        /// <param name="src">源DataTable</param>
        /// <param name="dest">目标DataTable</param>
        /// <param name="ClearExists">是否清除目标DataTable现有数据</param>
        /// <param name="alistColumns">列名集合</param>
        /// <param name="alistValue">数据集合</param>
        public static void CopyDataToDatatable(ref DataTable src, ref DataTable dest, bool ClearExists, ArrayList alistColumns, ArrayList alistValue)
        {
            if (src == null || dest == null)
            {
                return;
            }

            if (ClearExists)
            {
                dest.Rows.Clear();
            }

            DataRow CurRow, NewRow;

            for (int i = 0; i < src.Rows.Count; i++)
            {
                CurRow = src.Rows[i];
                NewRow = dest.NewRow();

                for (int j = 0; j < src.Columns.Count; j++)
                {
                    try
                    {
                        if (dest.Columns.Contains(src.Columns[j].ColumnName))
                        {
                            NewRow[src.Columns[j].ColumnName] = CurRow[j];
                        }
                    }
                    catch { }
                }

                if (alistColumns != null && alistValue != null)
                {
                    for (int idx = 0; idx < alistColumns.Count; idx++)
                    {
                        try
                        {
                            if (dest.Columns.Contains(alistColumns[idx].ToString()))
                            {
                                NewRow[alistColumns[idx].ToString()] = alistValue[idx];
                            }
                        }
                        catch { }
                    }
                }

                dest.Rows.Add(NewRow);
            }
        }

        /// <summary>
        /// Grid是否包含列
        /// </summary>
        /// <param name="grid">UltraGrid</param>
        /// <param name="strColumn">列名</param>
        /// <returns>有返回true 无返回false</returns>
        public static bool GridContainsColumn(ref Infragistics.Win.UltraWinGrid.UltraGrid grid, string strColumn)
        {
            for (int i = 0; i < grid.DisplayLayout.Bands[0].Columns.Count; i++)
            {
                if (grid.DisplayLayout.Bands[0].Columns[i].Key.Equals(strColumn))
                    return true;
            }

            return false;
        }

        /// <summary>
        /// Grid是否包含列
        /// </summary>
        /// <param name="grid">UltraGrid</param>
        /// <param name="BandIndex">UltraGrid绑定数据集数组下标</param>
        /// <param name="strColumn">列名</param>
        /// <returns>有返回true 无返回false</returns>
        public static bool GridContainsColumn(ref Infragistics.Win.UltraWinGrid.UltraGrid grid, int BandIndex, string strColumn)
        {
            if (BandIndex <= grid.DisplayLayout.Bands.Count - 1)
            {
                for (int i = 0; i < grid.DisplayLayout.Bands[BandIndex].Columns.Count; i++)
                {
                    if (grid.DisplayLayout.Bands[BandIndex].Columns[i].Key.Equals(strColumn))
                        return true;
                }
            }

            return false;
        }

        /// <summary>
        /// 获取Table中最大编号，自增1返回。
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static string AutoCode(DataTable dt)
        {
            int maxCode = 0;

            //substring去除S001中的S，得到001。
            string maxCodeString = dt.Rows[0][0].ToString().Substring(1);

            //是否为数字
            if (StringUtil.IsNumber(maxCodeString))
            {
                maxCode = Convert.ToInt32(maxCodeString);
            }
            else
            {
                MessageBox.Show("传入编号格式有误，请查看代码！", "提示");
            }

            //比较得出最大id。
            for (int i = 1; i < dt.Rows.Count; i++)
            {
                string maxCodeString_Two = dt.Rows[i][0].ToString().Substring(1);

                int maxCode_Two = 0;

                if (StringUtil.IsNumber(maxCodeString_Two))
                {
                    maxCode_Two = Convert.ToInt32(maxCodeString_Two);
                    if (maxCode_Two > maxCode)
                    {
                        maxCode = maxCode_Two;
                    }
                }
            }

            //新增行
            maxCode += 1;

            string codeTop = "";

            if (maxCode < 10)
            {
                codeTop = "00";
            }
            else if (maxCode < 100)
            {
                codeTop = "0";
            }

            //获取头文字D。
            string d = dt.Rows[0][0].ToString().Substring(0, 1);

            //头文字+0+最大数字
            string newCode = d + codeTop + maxCode;

            return newCode;
        }

		/// <summary>
		/// 根据关键字快速查找父表的UltaGridRow数组。
		/// </summary>
		/// <param name="grid">UltraGrid</param>
		/// <param name="columnNames">列名</param>
		/// <param name="keys">key值</param>
		/// <returns>UltraGridRow数组</returns>
		///  2014年11月29日 15:25:26 cx
        public static UltraGridRow[] GetRowsWithKey(UltraGrid grid, string[] columnNames, string[] keys)
		{
			UltraGridRow[] rows = null;
			IQueryable<UltraGridRow> queryableRow = grid.Rows.AsQueryable();
			if (columnNames.Length != keys.Length)
			{
				throw new Exception("列名数组与key值数组长度不一致！");
			}

			for (int i = 0; i < columnNames.Length; i++)
			{
				queryableRow = GetQueryResult(queryableRow, columnNames[i], keys[i]);
			}
			rows = queryableRow.ToArray();
			return rows;
		}

		private static IQueryable<UltraGridRow> GetQueryResult(IQueryable<UltraGridRow> queryableRow,
				string column, string key)
		{
			queryableRow = from q in queryableRow
						   where q.Cells[column].Value.ToString().ToUpper() == key.ToUpper()
						   select q;
			return queryableRow;
		}


		/// <summary>
		/// 根据主键快速查找子表的UltaGridRow数组(结果为父行下的所有子行)。
		/// </summary>
		/// <param name="band">子表band</param>
		/// <param name="columnNames">列名</param>
		/// <param name="keys">key值</param>
		/// <returns>UltraGridRow数组</returns>
		/// 2014年11月29日 15:25:36 cx
        public static UltraGridRow[] GetRowsWithKey(UltraGridChildBand band , string[] columnNames, string[] keys)
		{
			UltraGridRow[] rows = null;
			IQueryable<UltraGridRow> queryableRow = band.Rows.AsQueryable();
			if (columnNames.Length != keys.Length)
			{
				throw new Exception("列名数组与key值数组长度不一致！");
			}

			for (int i = 0; i < columnNames.Length; i++)
			{
				queryableRow = GetQueryResult(queryableRow, columnNames[i], keys[i]);
			}
			rows = queryableRow.ToArray();
			return rows;
		}

        /// <summary>
        /// 设置指定的列只读
        /// </summary>
        /// <param name="band"></param>
        /// <param name="columnKeys"></param>
        public static void SetColumnsActive(UltraGridBand band, params string[] columnKeys)
		{
            foreach(string columnKey in columnKeys)
            {
                if(band.Columns.Exists(columnKey))
                {
                    band.Columns[columnKey].CellActivation = Activation.ActivateOnly;
                }
            }
		}

        /// <summary>
        /// 设置不包含在指定列中的列只读。
        /// </summary>
        /// <param name="band"></param>
        /// <param name="columnKeys"></param>
        public static void SetExcludeColumnsActive(UltraGridBand band, params string[] columnKeys)
        {
            foreach (UltraGridColumn gridColumn in band.Columns)
            {
                if (columnKeys.Contains(gridColumn.Key) == false)
                {
                    gridColumn.CellActivation = Activation.ActivateOnly;
                }
            }
        }

        /// <summary>
        /// 设置不包含在指定列中的列只读，并且设置可编辑列颜色
        /// </summary>
        /// <param name="band"></param>
        /// <param name="columnKeys"></param>
        public static void SetColumnsActivateAndColor(UltraGridBand band, params string[] columnKeys)
        {
            foreach (UltraGridColumn gridColumn in band.Columns)
            {
                if (columnKeys.Contains(gridColumn.Key) == false)
                    gridColumn.CellActivation = Activation.ActivateOnly;
                else if (gridColumn.Key != "Choose")
                    gridColumn.CellAppearance.BackColor = Color.FromArgb(((int)(((byte)(255)))), ((int)(((byte)(255)))), ((int)(((byte)(128)))));
                else
                    gridColumn.CellActivation = Activation.AllowEdit;
            }
        }

        /// <summary>
        /// 初始化UltraGrid Card模式数据
        /// </summary>
        /// <param name="ultDataSource"></param>
        /// <param name="ultraGrid"></param>
        public static void InitCardGrid(UltraDataSource ultDataSource, UltraGrid ultraGrid)
        {
            try
            {
                int iColumnsCount = ultDataSource.Band.Columns.Count;
                object[] obj = new object[iColumnsCount];

                ultDataSource.Rows.Clear();

                for (int i = 0; i < ultDataSource.Band.Columns.Count; i++)
                {
                    try
                    {
                        if (ultDataSource.Band.Columns[i].DataType == typeof(Bitmap) ||
                            ultDataSource.Band.Columns[i].DataType == typeof(Image))
                            obj[i] = null;
                        else if (ultDataSource.Band.Columns[i].DataType == typeof(DateTime))
                            obj[i] = DBNull.Value;
                        else if (ultDataSource.Band.Columns[i].DataType == typeof(decimal) ||
                                ultDataSource.Band.Columns[i].DataType == typeof(double) ||
                                ultDataSource.Band.Columns[i].DataType == typeof(Single))
                            obj[i] = 0;
                        else
                            obj[i] = "";
                    }
                    catch { }

                    try
                    {
                        ultraGrid.DisplayLayout.Bands[0].Columns[i].Header.Appearance.FontData.Bold = DefaultableBoolean.False;
                    }
                    catch { }
                }

                ultDataSource.Rows.Add(obj);

                ultraGrid.UpdateData();
            }
            catch { }
        }

        /// <summary>
        /// 隐藏指定的列
        /// </summary>
        /// <param name="gridBand">UltraGridBand</param>
        /// <param name="columnKeys">需要隐藏的列</param>
        public static void HidenColumns(UltraGridBand gridBand, params string[] columnKeys)
        {
            foreach (string columnKey in columnKeys)
            {
                if (gridBand.Columns.Exists(columnKey))
                {
                    gridBand.Columns[columnKey].Hidden = true;
                }
            }
        }

        /// <summary>
        /// 隐藏不包含在指定列中的列。
        /// </summary>
        /// <param name="gridBand">UltraGridBand</param>
        /// <param name="columnKeys">不需要隐藏的列</param>
        public static void HidenExcludeColumns(UltraGridBand gridBand, params string[] columnKeys)
        {
            foreach(UltraGridColumn gridColumn in gridBand.Columns)
            {
                if (columnKeys.Contains(gridColumn.Key) == false)
                {
                    gridColumn.Hidden = true;
                }
            }
        }

        /// <summary>
        /// UltraGridBand列设置全部只读。
        /// </summary>
        /// <param name="grid">UltraGridBand</param>
        public static void SetAllColumnsActive(UltraGrid grid)
        {
            foreach (UltraGridColumn column in grid.DisplayLayout.Bands[0].Columns)
            {
                column.CellActivation = Activation.ActivateOnly;
            }
        }


    }
}