XinYu_LG2MES/ClientSolution/Core.LgMes.Client.Report/reportCommon.cs

using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Diagnostics;
using System.Data;
using Infragistics.Win.UltraWinGrid;
using Infragistics.Win.UltraWinGrid.ExcelExport;
using System.Runtime.InteropServices;
using CoreFS.CA06;
using Infragistics.Win;
using Core.LgMes.Client.Comm;
using System.Collections;
using System.Drawing;
using Infragistics.Win.UltraWinEditors;
using System.Xml;
using System.Data.OleDb;
using Core.Mes.Client.Common;

namespace Core.LgMes.Client.Report
{
    class reportCommon
    {
        private static UltraGridExcelExporter ultraGridExcelExporter1 = new UltraGridExcelExporter();

        public CoreFS.CA06.OpeBase ob = null;
        
        public static void SetUltraGridColumnsReadOnly(ref UltraGrid ultGrid)
        {
            try
            {
                foreach (UltraGridBand band in ultGrid.DisplayLayout.Bands)
                {
                    foreach (UltraGridColumn column in band.Columns)
                    {
                        column.CellActivation = Activation.ActivateOnly;
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public static void ExportData(ref UltraGrid ultGrid, string strFileName)
        {
            try
            {
                if (ultGrid.Rows.Count == 0) return;

                if (strFileName.Length == 0)
                    strFileName = "未命名";

                strFileName = string.Format(Application.StartupPath + "\\Report\\{0}.xls", strFileName);
                ultraGridExcelExporter1.Export(ultGrid, strFileName);

                ProcessStartInfo p = new ProcessStartInfo(strFileName);
                p.WorkingDirectory = Path.GetDirectoryName(strFileName);
                Process.Start(p);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public static bool ExcelInstalled()
        {
            bool bInstalled = (Type.GetTypeFromProgID("Excel.Application") != null);
            if (!bInstalled)
            {
                MessageBox.Show("系统没有安装EXCEL组件，无法导出数据！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
            }
            return bInstalled;
        }

        public static void Export2Excel(ref UltraGrid ultGrid, string strTemplateName, string strFileName,
            ref int iStartRowNum, ref int[] iCaptionX, ref int[] iCaptionY, ref string[] strCaptionFromColumns,
            ref string[] strColumns, ref int[] iStartX, ref int[] iWidth, ref int[] iOffsetY, ref int[] iHeight, ref int[] iPlainText)
        {
            try
            {
                if (ultGrid.Rows.Count == 0) return;
                if (strTemplateName.Trim().Length == 0) return;

                strTemplateName = Application.StartupPath + "\\Report\\" + strTemplateName + ".xls";
                if (!File.Exists(strTemplateName))
                {
                    MessageBox.Show("找不到指定的模版文件，数据导出失败！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                    return;
                }

                if (strFileName.Length == 0) strFileName = "未命名";
                strFileName = Application.StartupPath + "\\Report\\" + strFileName + ".xls";

                if (File.Exists(strFileName)) File.Delete(strFileName);

                File.Copy(strTemplateName, strFileName);
                File.SetAttributes(strFileName, FileAttributes.Normal);

                Microsoft.Office.Interop.Excel.ApplicationClass excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();

                if (excelApp == null)
                {
                    MessageBox.Show("无法启动EXCEL，数据导出失败！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                    return;
                }

                excelApp.Visible = false;

                Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(strFileName,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                for (int i = 0; i < strCaptionFromColumns.Length; i++)
                {
                    excelApp.Cells[iCaptionY[i], iCaptionX[i]] = Convert.ToString(ultGrid.DisplayLayout.Bands[0].Columns[strCaptionFromColumns[i]].Header.Caption);
                }

                int iStart = iStartRowNum;
                int iMaxHeight = 1;
                string strValue = "";
                Microsoft.Office.Interop.Excel.Range cellRange;
                for (int i = 0; i < ultGrid.Rows.Count; i++)
                {
                    for (int j = 0; j < strColumns.Length; j++)
                    {
                        cellRange = excelApp.get_Range(excelApp.Cells[iStart + iOffsetY[j], iStartX[j]], excelApp.Cells[iStart + iOffsetY[j] + iHeight[j] - 1, iStartX[j] + iWidth[j] - 1]);
                        cellRange.Merge(null);
                        cellRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
                        cellRange.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
                        cellRange.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                        strValue = Convert.ToString(ultGrid.Rows[i].Cells[strColumns[j]].Value);
                        if (iPlainText[j] == 1 && strValue.Length > 0)
                            strValue = "'" + strValue;          //以纯文本格式显示
                        cellRange.Value2 = strValue;
                        if (iHeight[j] > iMaxHeight)
                            iMaxHeight = iHeight[j];
                    }

                    iStart += iMaxHeight;
                }

                excelWorkbook.Save();
                excelWorkbook.Close(true, Type.Missing, Type.Missing);
                excelApp.Workbooks.Close();
                excelApp.Quit();
                Marshal.ReleaseComObject(excelWorkbook);
                Marshal.ReleaseComObject(excelApp.Workbooks);
                Marshal.ReleaseComObject(excelApp);
                GC.Collect(GC.GetGeneration(excelApp));
                excelApp = null;

                ProcessStartInfo p = new ProcessStartInfo(strFileName);
                p.WorkingDirectory = Path.GetDirectoryName(strFileName);
                Process.Start(p);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public static string ConstructSteelNames(string strSteelNamesIn)
        {
            if (strSteelNamesIn.Trim().Length == 0)
                return string.Empty;

            if (!strSteelNamesIn.Trim().Contains(",") && !strSteelNamesIn.Trim().Contains("，"))
                return "'" + strSteelNamesIn.Trim().ToUpper() + "'";

            string[] strSteel = strSteelNamesIn.Trim().Split(new string[] { ",", "，" }, StringSplitOptions.None);

            string strRet = "";
            for (int i = 0; i < strSteel.Length; i++)
            {
                if (i == 0)
                    strRet = "'" + (strSteel[i].Trim().Length == 0 ? "null" : strSteel[i].Trim()) + "'";
                else
                    strRet += ",'" + (strSteel[i].Trim().Length == 0 ? "null" : strSteel[i].Trim()) + "'";
            }

            return strRet.ToUpper();
        }

        /// <summary>
        /// 将长整型转换浮点型
        /// </summary>
        /// <param name="lgValue"></param>
        /// <returns></returns>
        public static float LongToFloat(long lgValue)
        {
            float flValue = 0;
            try
            {
                if (lgValue >= 0)
                {
                    byte[] btValue = LongToHex(lgValue);
                    flValue = HexToFloat(btValue);
                }
            }
            catch { }
            return flValue;
        }

        private static unsafe byte[] LongToHex(long lValue)
        {
            long[] lValuePtr = new long[1];
            int sizeOfValue = Marshal.SizeOf(typeof(long));
            byte[] byteValue = new byte[sizeOfValue];

            lValuePtr[0] = lValue;
            fixed (long* pTempValue = lValuePtr)
            {
                Marshal.Copy(new IntPtr(pTempValue), byteValue, 0, sizeOfValue);
            }

            return byteValue;
        }

        private static unsafe float HexToFloat(byte[] byteValue)
        {
            float[] fltValuePtr = new float[1];
            int sizeOfValue = Marshal.SizeOf(typeof(float));

            fixed (float* pTempValue = fltValuePtr)
            {
                Marshal.Copy(byteValue, 0, new IntPtr(pTempValue), sizeOfValue);
            }

            return fltValuePtr[0];
        }

        /// <summary>
        /// 获取牌号
        /// </summary>
        /// <returns></returns>
        public static DataSet GetSteel()
        {
            DataSet dsSteel = new DataSet();
            try
            {
                string strErr = "";
                string strSql = "select steelcode CODE, Steel CODE_DESC from scm_steel t where t.largeflag='1'  order by steelcode";

                //hengxing ClientCommon._RemotingHelp.ExecuteMethod(par, out strErr) as DataSet;
 
                ArrayList arrySteel = new ArrayList();
                arrySteel.Add("GetSteelInfo.select");

                CommonClientToServer cctos = new CommonClientToServer();
               //cctos.ob = ob;
                DataSet ds = cctos.ExecuteQueryFunctions("Core.LgMes.Server.Common.ComDBQuery",
                                            "doSimpleQuery", arrySteel, out strErr);

                if (strErr == "" && ds != null)
                {
                    dsSteel = ds;
                }
                else
                {
                    MessageBox.Show("钢种加载失败！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    if (MessageBox.Show("是否重新加载钢种？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                        GetSteel();
                }
                    
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return dsSteel;
        }

        /// <summary>
        /// 设置UltraGrid列样式为DownList
        /// </summary>
        /// <param name="ultragridValue">要设置的UltraGrid</param>
        /// <param name="dsValue">DataSet数据</param>
        /// <param name="strID">DownList的Value值</param>
        /// <param name="strName">DownList的Text值</param>
        /// <param name="strColumnsName">设置的列名</param>
        public static void SetUltraGridDownList(Infragistics.Win.UltraWinGrid.UltraGrid ultragridValue,
                                                DataSet dsValue, string strID, string strName, string strColumnsName)
        {
            //获取单位
            DataSet ds = dsValue;

            //增加Grid单位字段数据集
            ultragridValue.DisplayLayout.ValueLists.Add(strColumnsName);

            //初始经Grid单位字段数据集
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                ultragridValue.DisplayLayout.ValueLists[strColumnsName].ValueListItems.Add(ds.Tables[0].Rows[i][strID].ToString(), ds.Tables[0].Rows[i][strName].ToString());
            }
            //绑定单位
            ultragridValue.DisplayLayout.Bands[0].Columns[strColumnsName].ValueList = ultragridValue.DisplayLayout.ValueLists[strColumnsName];
        }

        public static void SetGridFontData(ref UltraGrid grid, DefaultableBoolean CaptionVisible)
        {
            try
            {
                grid.DisplayLayout.CaptionVisible = CaptionVisible;

                grid.Font = new Font("宋体", 9F);

                grid.DisplayLayout.CaptionAppearance.TextHAlign = HAlign.Center;
                grid.DisplayLayout.CaptionAppearance.TextVAlign = VAlign.Middle;
                grid.DisplayLayout.CaptionAppearance.FontData.Name = "宋体";
                grid.DisplayLayout.CaptionAppearance.FontData.SizeInPoints = 10.5F;
                grid.DisplayLayout.CaptionAppearance.FontData.Bold = Infragistics.Win.DefaultableBoolean.True;

                grid.DisplayLayout.Override.CellAppearance.FontData.Name = "宋体";
                grid.DisplayLayout.Override.CellAppearance.FontData.SizeInPoints = 9F;

                grid.DisplayLayout.Override.HeaderAppearance.FontData.Name = "宋体";
                grid.DisplayLayout.Override.HeaderAppearance.FontData.SizeInPoints = 9F;
                grid.DisplayLayout.Override.HeaderAppearance.FontData.Bold = Infragistics.Win.DefaultableBoolean.True;

                grid.DisplayLayout.Override.SummaryFooterAppearance.FontData.Name = "宋体";
                grid.DisplayLayout.Override.SummaryFooterAppearance.FontData.SizeInPoints = 9F;
                grid.DisplayLayout.Override.SummaryFooterAppearance.FontData.Bold = Infragistics.Win.DefaultableBoolean.True;

                grid.DisplayLayout.Override.SummaryValueAppearance.BackColor = Color.LightGreen;
                grid.DisplayLayout.Override.SummaryValueAppearance.FontData.Name = "宋体";
                grid.DisplayLayout.Override.SummaryValueAppearance.FontData.SizeInPoints = 9F;
                grid.DisplayLayout.Override.SummaryValueAppearance.FontData.Bold = Infragistics.Win.DefaultableBoolean.True;

                grid.DisplayLayout.Override.RowSelectorAppearance.FontData.Name = "宋体";
                grid.DisplayLayout.Override.RowSelectorAppearance.FontData.SizeInPoints = 9F;
                grid.DisplayLayout.Override.RowSelectorAppearance.TextVAlign = Infragistics.Win.VAlign.Middle;
            }
            catch { }
        }

        public static void proc_DecodePath(ref DataTable table)
        {
            if (table == null || !table.Columns.Contains("作业路线"))
            {
                return;
            }

            string strSrcPath = "", strDesPath = "";
            for (int i = 0; i < table.Rows.Count; i++)
            {
                try
                {
                    strSrcPath = Convert.ToString(table.Rows[i]["作业路线"]);
                    strDesPath = CStaticMethod.analysPath(strSrcPath);
                    table.Rows[i]["作业路线"] = strDesPath;
                }
                catch { }
            }
        }

        //Add on 2009-10-25 by XHJ
        public static void ExportDataWithSaveDialog(ref UltraGrid ultGrid, string strFileName)
        {
            try
            {
                if (ultGrid.Rows.Count == 0) return;

                if (strFileName.Length == 0)
                    strFileName = "未命名";

                SaveFileDialog dlg = new SaveFileDialog();
                dlg.Title = "保存";
                dlg.OverwritePrompt = true;
                dlg.Filter = "Excel文件(*.xls)|*.xls";
                dlg.AddExtension = true;
                dlg.FileName = strFileName;
                if (dlg.ShowDialog() == DialogResult.OK)
                {
                    strFileName = dlg.FileName;
                    ultraGridExcelExporter1.Export(ultGrid, strFileName);

                    if (MessageBox.Show("数据导出成功！\r\n需要打开所导出文件吗？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                    {
                        ultraGridExcelExporter1.Export(ultGrid, strFileName);

                        ProcessStartInfo p = new ProcessStartInfo(strFileName);
                        p.WorkingDirectory = Path.GetDirectoryName(strFileName);
                        Process.Start(p);
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        public static void SetGridRowFilter(ref UltraGrid ultGrid, bool bAllowFilter)
        {
            try
            {
                if (bAllowFilter)
                {
                    ultGrid.DisplayLayout.Override.AllowRowFiltering = Infragistics.Win.DefaultableBoolean.True;
                }
                else
                {
                    ultGrid.DisplayLayout.Bands[0].ColumnFilters.ClearAllFilters();
                    ultGrid.DisplayLayout.Override.AllowRowFiltering = Infragistics.Win.DefaultableBoolean.False;
                }
            }
            catch { }
        }

        public static void proc_Statics_Avg2(ref UltraGrid ultraGrid, ArrayList alistColumns, bool clearExists, ArrayList alistFormat)
        {
            try
            {
                if (alistColumns == null)
                {
                    return;
                }

                UltraGridBand band = ultraGrid.DisplayLayout.Bands[0];
                if (clearExists)
                    band.Summaries.Clear();

                SummarySettings summary = null;

                UltraGridColumn cl = null;
                foreach (UltraGridColumn column in ultraGrid.DisplayLayout.Bands[0].Columns)
                {
                    if (column.ToString() == "第二炼钢厂转炉生产调度表")
                    {
                        if (column.RowLayoutColumnInfo.OriginX == 1)
                        {
                            cl = column;
                            break;
                        }
                    }
                    else
                    {
                        if (column.RowLayoutColumnInfo.OriginX == 0)
                        {
                            cl = column;
                            break;
                        }
                    }
                }

                summary = band.Summaries.Add(SummaryType.Count, cl);
                summary.SummaryDisplayArea = SummaryDisplayAreas.BottomFixed;
                summary.SummaryPosition = SummaryPosition.UseSummaryPositionColumn;
                summary.DisplayFormat = "平均：";

                summary.Appearance.TextHAlign = Infragistics.Win.HAlign.Center;
                summary.Appearance.TextVAlign = Infragistics.Win.VAlign.Middle;
                summary.Appearance.FontData.Bold = DefaultableBoolean.True;

                string strFormat = "";

                for (int i = 0; i < alistColumns.Count; i++)
                {
                    try
                    {
                        try
                        {
                            strFormat = alistFormat[i].ToString();
                        }
                        catch
                        {
                            strFormat = " {0:############0.00}";
                        }

                        summary = band.Summaries.Add(SummaryType.Average, band.Columns[alistColumns[i].ToString()]);
                        summary.SummaryDisplayArea = SummaryDisplayAreas.BottomFixed;
                        summary.SummaryPosition = SummaryPosition.UseSummaryPositionColumn;
                        summary.DisplayFormat = strFormat;

                        summary.Appearance.TextHAlign = Infragistics.Win.HAlign.Right;
                        summary.Appearance.TextVAlign = Infragistics.Win.VAlign.Middle;
                        summary.Appearance.FontData.Bold = DefaultableBoolean.True;
                        summary.Appearance.ForeColor = Color.DarkBlue;
                        summary.Appearance.BackColor = Color.White;
                    }
                    catch { }
                }

                band.Override.SummaryFooterCaptionVisible = Infragistics.Win.DefaultableBoolean.False;
            }
            catch { }
        }

        public static void proc_Statics(ref UltraGrid ultraGrid, ArrayList alistColumns, bool clearExists, string strFormat)
        {
            try
            {
                if (alistColumns == null)
                {
                    return;
                }

                UltraGridBand band = ultraGrid.DisplayLayout.Bands[0];
                if (clearExists)
                    band.Summaries.Clear();

                SummarySettings summary = null;

                UltraGridColumn cl = null;
                foreach (UltraGridColumn column in ultraGrid.DisplayLayout.Bands[0].Columns)
                {
                    if (column.ToString() == "第二炼钢厂转炉生产调度表")
                    {
                        if (column.RowLayoutColumnInfo.OriginX == 1)
                        {
                            cl = column;
                            break;
                        }
                    }
                    else
                    {
                        if (column.RowLayoutColumnInfo.OriginX == 0)
                        {
                            cl = column;
                            break;
                        }
                    }
                }

                summary = band.Summaries.Add(SummaryType.Count, cl);
                summary.SummaryDisplayArea = SummaryDisplayAreas.BottomFixed;
                summary.SummaryPosition = SummaryPosition.UseSummaryPositionColumn;
                summary.DisplayFormat = "合计：";

                summary.Appearance.TextHAlign = Infragistics.Win.HAlign.Center;
                summary.Appearance.TextVAlign = Infragistics.Win.VAlign.Middle;
                summary.Appearance.FontData.Bold = DefaultableBoolean.True;

                if (string.IsNullOrEmpty(strFormat.Trim()))
                {
                    strFormat = " {0:############0.00}";
                }
                for (int i = 0; i < alistColumns.Count; i++)
                {
                    try
                    {
                        summary = band.Summaries.Add(SummaryType.Sum, band.Columns[alistColumns[i].ToString()]);
                        summary.SummaryDisplayArea = SummaryDisplayAreas.BottomFixed;
                        summary.SummaryPosition = SummaryPosition.UseSummaryPositionColumn;
                        summary.DisplayFormat = strFormat;

                        summary.Appearance.TextHAlign = Infragistics.Win.HAlign.Right;
                        summary.Appearance.TextVAlign = Infragistics.Win.VAlign.Middle;
                        summary.Appearance.FontData.Bold = DefaultableBoolean.True;
                        summary.Appearance.ForeColor = Color.DarkBlue;
                        summary.Appearance.BackColor = Color.White;
                    }
                    catch { }
                }

                band.Override.SummaryFooterCaptionVisible = Infragistics.Win.DefaultableBoolean.False;
            }
            catch { }
        }

        public static void proc_Statics_Avg(ref UltraGrid ultraGrid, ArrayList alistColumns, bool clearExists, string strFormat)
        {
            try
            {
                if (alistColumns == null)
                {
                    return;
                }

                UltraGridBand band = ultraGrid.DisplayLayout.Bands[0];
                if (clearExists)
                    band.Summaries.Clear();

                SummarySettings summary = band.Summaries.Add(SummaryType.Count, band.Columns[0]);
                summary.SummaryDisplayArea = SummaryDisplayAreas.BottomFixed;
                summary.SummaryPosition = SummaryPosition.UseSummaryPositionColumn;
                summary.DisplayFormat = "平均：";

                summary.Appearance.TextHAlign = Infragistics.Win.HAlign.Center;
                summary.Appearance.TextVAlign = Infragistics.Win.VAlign.Middle;
                summary.Appearance.FontData.Bold = DefaultableBoolean.True;

                if (string.IsNullOrEmpty(strFormat.Trim()))
                {
                    strFormat = " {0:############0.00}";
                }
                for (int i = 0; i < alistColumns.Count; i++)
                {
                    try
                    {
                        summary = band.Summaries.Add(SummaryType.Average, band.Columns[alistColumns[i].ToString()]);
                        summary.SummaryDisplayArea = SummaryDisplayAreas.BottomFixed;
                        summary.SummaryPosition = SummaryPosition.UseSummaryPositionColumn;
                        summary.DisplayFormat = strFormat;

                        summary.Appearance.TextHAlign = Infragistics.Win.HAlign.Right;
                        summary.Appearance.TextVAlign = Infragistics.Win.VAlign.Middle;
                        summary.Appearance.FontData.Bold = DefaultableBoolean.True;
                        summary.Appearance.ForeColor = Color.DarkBlue;
                        summary.Appearance.BackColor = Color.White;
                    }
                    catch { }
                }

                band.Override.SummaryFooterCaptionVisible = Infragistics.Win.DefaultableBoolean.False;
            }
            catch { }
        }

        /// <summary>
        /// 汇总函数
        /// </summary>
        /// <param name="ultraMain"></param>
        public static void SetSum(UltraGrid ultraMain)
        {
            //定义存放汇总方式(0:标题,1:计数,2:合计,3:平均,4:最大,5:最小)
            string[] strOpt = new string[6];
            UltraGridBand band = ultraMain.DisplayLayout.Bands[0];

            try
            {
                band.Summaries.Clear();
                SummarySettings summaryCount = new SummarySettings(); //计数
                SummarySettings summarySum = new SummarySettings();   //求和
                SummarySettings summaryAvg = new SummarySettings();   //平均
                SummarySettings summaryMax = new SummarySettings();   //最大值
                SummarySettings summaryMin = new SummarySettings();   //最小值

                for (int i = 0; i < ultraMain.DisplayLayout.Bands[0].Columns.Count; i++)
                {
                    if (ultraMain.DisplayLayout.Bands[0].Columns[i].Tag != null)
                    {
                        strOpt[0] = ultraMain.DisplayLayout.Bands[0].Columns[i].Tag.ToString().Substring(0, 1);

                        if (strOpt[0] == "1")  //标题
                        {
                            summaryCount = band.Summaries.Add(SummaryType.Count, band.Columns[i]);
                            summaryCount.SummaryPosition = SummaryPosition.UseSummaryPositionColumn;
                            summaryCount.DisplayFormat = "计数：";

                            summarySum = band.Summaries.Add(SummaryType.Sum, band.Columns[i]);
                            summarySum.SummaryPosition = SummaryPosition.UseSummaryPositionColumn;
                            summarySum.DisplayFormat = "求和：";

                            summaryAvg = band.Summaries.Add(SummaryType.Average, band.Columns[i]);
                            summaryAvg.SummaryPosition = SummaryPosition.UseSummaryPositionColumn;
                            summaryAvg.DisplayFormat = "平均：";

                            summaryMax = band.Summaries.Add(SummaryType.Maximum, band.Columns[i]);
                            summaryMax.SummaryPosition = SummaryPosition.UseSummaryPositionColumn;
                            summaryMax.DisplayFormat = "最大值：";

                            summaryMin = band.Summaries.Add(SummaryType.Minimum, band.Columns[i]);
                            summaryMin.SummaryPosition = SummaryPosition.UseSummaryPositionColumn;
                            summaryMin.DisplayFormat = "最小值：";
                            break;
                        }
                    }
                }


                for (int i = 0; i < ultraMain.DisplayLayout.Bands[0].Columns.Count; i++)
                {
                    if (ultraMain.DisplayLayout.Bands[0].Columns[i].Tag != null)
                    {
                        strOpt[0] = ultraMain.DisplayLayout.Bands[0].Columns[i].Tag.ToString().Substring(0, 1);
                        strOpt[1] = ultraMain.DisplayLayout.Bands[0].Columns[i].Tag.ToString().Substring(1, 1);
                        strOpt[2] = ultraMain.DisplayLayout.Bands[0].Columns[i].Tag.ToString().Substring(2, 1);
                        strOpt[3] = ultraMain.DisplayLayout.Bands[0].Columns[i].Tag.ToString().Substring(3, 1);
                        strOpt[4] = ultraMain.DisplayLayout.Bands[0].Columns[i].Tag.ToString().Substring(4, 1);
                        strOpt[5] = ultraMain.DisplayLayout.Bands[0].Columns[i].Tag.ToString().Substring(5, 1);
                        if (strOpt[0] == "0")
                        {
                            if (strOpt[1] == "1")  //计数
                            {
                                SetSummaryMod(summaryCount, SummaryType.Count, band, i);
                            }
                            if (strOpt[2] == "1")  //求和
                            {
                                SetSummaryMod(summarySum, SummaryType.Sum, band, i);
                            }
                            if (strOpt[3] == "1")  //平均
                            {
                                SetSummaryMod(summaryAvg, SummaryType.Average, band, i);
                            }
                            if (strOpt[4] == "1")  //最大
                            {
                                SetSummaryMod(summaryMax, SummaryType.Maximum, band, i);
                            }
                            if (strOpt[5] == "1")  //最小
                            {
                                SetSummaryMod(summaryMin, SummaryType.Minimum, band, i);
                            }
                        }
                    }
                }

                band.Override.SummaryFooterCaptionVisible = Infragistics.Win.DefaultableBoolean.False;
            }
            catch
            {
            }
        }

        /// <summary>
        /// 添加汇总
        /// </summary>
        /// <param name="summaryMain">汇总块</param>
        /// <param name="sumtype">汇总类型</param>
        /// <param name="ultraBand">UltraGrid.Band</param>
        private static void SetSummaryMod(SummarySettings summaryMain, SummaryType sumtype, UltraGridBand ultraBand, int intindex)
        {
            try
            {
                summaryMain = ultraBand.Summaries.Add(sumtype, ultraBand.Columns[intindex]);

                summaryMain.SummaryPosition = SummaryPosition.UseSummaryPositionColumn;
                if (sumtype == SummaryType.Count)
                    summaryMain.DisplayFormat = " {0:#####}";
                else
                    summaryMain.DisplayFormat = " {0:#####.00}";
                summaryMain.Appearance.TextHAlign = Infragistics.Win.HAlign.Center;
                summaryMain.Appearance.ForeColor = Color.DarkBlue;
                summaryMain.Appearance.BackColor = Color.White;
            }
            catch
            {
            }
        }

        /// <summary>
        /// 设置列名汇总
        /// </summary>
        /// <param name="ultGrid"></param>
        /// <param name="bAllowFilter"></param>
        /// <param name="ultrachk"></param>
        public static void SetStatisticsMod(ref UltraGrid ultGrid, bool bAllowFilter, ref UltraCheckEditor ultrachk)
        {
            try
            {
                if (bAllowFilter)
                {
                    frmSetStatisticsMode frmSetMode = new frmSetStatisticsMode(ref ultGrid);
                    frmSetMode.WindowState = FormWindowState.Normal;
                    frmSetMode.StartPosition = FormStartPosition.CenterParent;
                    frmSetMode.ShowDialog();
                    if (frmSetMode.htMode.Count == 0)
                    {
                        ultrachk.Checked = false;
                        return;
                    }
                    else
                    {
                        foreach (DictionaryEntry de in frmSetMode.htMode)
                        {
                            if (ultGrid.DisplayLayout.Bands[0].Columns[de.Key.ToString()].RowLayoutColumnInfo.LabelPosition != LabelPosition.LabelOnly
                               || ultGrid.DisplayLayout.Bands[0].Columns[de.Key.ToString()].Hidden == false)
                            {
                                ultGrid.DisplayLayout.Bands[0].Columns[de.Key.ToString()].Tag = de.Value.ToString();
                            }
                        }
                        SetSum(ultGrid);
                    }
                }
                else
                {
                    ultGrid.DisplayLayout.Bands[0].Summaries.Clear();
                }
            }
            catch { }
        }


        /// <summary>
        /// 设置列名汇总
        /// </summary>
        /// <param name="ultGrid"></param>
        /// <param name="bAllowFilter"></param>
        /// <param name="ultrachk"></param>
        public static void SetColHide(ref UltraGrid ultGrid)
        {
            try
            {            
                frmSetColHide frmSetMode = new frmSetColHide(ref ultGrid);
                frmSetMode.WindowState = FormWindowState.Normal;
                frmSetMode.StartPosition = FormStartPosition.CenterParent;
                frmSetMode.ShowDialog();
                if (frmSetMode.htMode.Count == 0)
                {                       
                    return;
                }
                else
                {
                    foreach (DictionaryEntry de in frmSetMode.htMode)
                    {
                        ultGrid.DisplayLayout.Bands[0].Columns[de.Key.ToString()].Hidden = Convert.ToBoolean(de.Value);
                    }                      
                }
                             
            }
            catch { }
        }

        #region "导出到EXCEL测试程序"
        public static void exportToExcelByDataset(string filePath, DataSet ds, UltraGrid ultGrid)
        {
            string sqlstr;
            FileInfo fi = new FileInfo(filePath);

            if (fi.Exists)
            {
                fi.Delete();
                //     throw new Exception("文件删除失败");   
            }
            else
            {
                fi.Create();
            }

            string sqlcon = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended ProPerties=Excel 8.0;";
            OleDbConnection olecon = new OleDbConnection(sqlcon);
            OleDbCommand olecmd = new OleDbCommand();
            olecmd.Connection = olecon;
            olecmd.CommandType = CommandType.Text;
            try
            {
                olecon.Open();
                
                int ii = 0;
                sqlstr = "CREATE TABLE BOFTABLE(";            
                foreach (UltraGridColumn ultCol in ultGrid.DisplayLayout.Bands[0].Columns)
                {
                    if (!String.IsNullOrEmpty(ultCol.Header.Caption)
                        && ultCol.Hidden == false)
                    {
                        if (ii == ultGrid.DisplayLayout.Bands[0].Columns.Count - 1)
                        {
                            if (ultCol.DataType.ToString() == "System.Double")
                                sqlstr = sqlstr + ultCol.Key.ToString() + " number)";
                            else
                                sqlstr = sqlstr + ultCol.Key.ToString() + " varchar(40))";
                        }
                        else
                        {
                            if (ultCol.DataType.ToString() == "System.Dublue")

                                sqlstr = sqlstr + ultCol.Key.ToString() + " number,";
                            else
                                sqlstr = sqlstr + ultCol.Key.ToString() + " varchar(20),";
                        }
                        
                    }
                    ii++;
                }
                
                olecmd.CommandText = sqlstr;
                olecmd.ExecuteNonQuery();
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    sqlstr = "INSERT INTO sheet1 VALUES(";
                    int jj = 0;
                    foreach (UltraGridColumn ultCol in ultGrid.DisplayLayout.Bands[0].Columns)
                    {
                        if (!String.IsNullOrEmpty(ultCol.Header.Caption)
                       && ultCol.Hidden == false)
                        {
                        if (jj == ultGrid.DisplayLayout.Bands[0].Columns.Count-1)
                        {
                            if (ultCol.DataType.ToString() == "System.Dublue")
                            {
                                sqlstr = sqlstr + isnull(ds.Tables[0].Rows[i].ItemArray[jj].ToString()) + ")";
                            }
                            else
                            {
                                sqlstr = sqlstr + "'" + isnull(ds.Tables[0].Rows[i].ItemArray[jj].ToString().Replace("'", "''")) + "')";
                            }
                        }
                        else
                        {
                            if (ultCol.DataType.ToString() == "System.Dublue")
                            {
                                sqlstr = sqlstr + isnull(ds.Tables[0].Rows[i].ItemArray[jj].ToString()) + ",";
                            }
                            else
                            {
                                sqlstr = sqlstr + "'" + isnull(ds.Tables[0].Rows[i].ItemArray[jj].ToString().Replace("'", "''")) + "',";
                            }
                        }
                            }
                        jj++;
                    }
                    olecmd.CommandText = sqlstr;
                    olecmd.ExecuteNonQuery();
                }
                MessageBox.Show(@"Excel文件：" + filePath + " 导出成功！");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                olecmd.Dispose();
                olecon.Close();
                olecon.Dispose();
            }
        }

        /// <summary>    
        /// change to string "null" if input is null    
        /// </summary>    
        /// <param name="obj"></param>    
        /// <returns></returns> 
        private static string isnull(string obj)
        {
            if (obj.Length > 0)
            {
                return obj;
            }
            else
            {
                return "null";
            }
        }
        #endregion

        /// <summary>
        /// 自动调整列宽
        /// </summary>
        /// <param name="grid"></param>
        public static void AutoSizeColumn(Infragistics.Win.UltraWinGrid.UltraGrid grid)
        {
            if (grid.DisplayLayout.Bands.Count >= 1)
            {
                foreach (Infragistics.Win.UltraWinGrid.UltraGridColumn c in grid.DisplayLayout.Bands[0].Columns)
                {
                    c.PerformAutoResize(10);
                }
            }
            if (grid.DisplayLayout.Bands.Count >= 2)
            {
                foreach (Infragistics.Win.UltraWinGrid.UltraGridColumn c in grid.DisplayLayout.Bands[1].Columns)
                {
                    c.PerformAutoResize(10);
                }
            }
            if (grid.DisplayLayout.Bands.Count >= 3)
            {
                foreach (Infragistics.Win.UltraWinGrid.UltraGridColumn c in grid.DisplayLayout.Bands[2].Columns)
                {
                    c.PerformAutoResize(PerformAutoSizeType.AllRowsInBand);
                }
            }

        }


        public static DataSet returnDataSet(string sqlStr)
        {
           

            DataSet ds = new DataSet();
           
            return ds;           
        }
    }
}