sourcegrid统计报表画法以及EXCEL导出内容代码完全版

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
//using SourceGrid;
using Aspose.Cells;
using System.Drawing.Drawing2D;

namespace WinTest
{
    public partial class sourceGridTest : Form
    {
        public sourceGridTest()
        {
            InitializeComponent();
            try
            {
               // CreateGrid();
            }
            catch (Exception)
            {

                return;
            }
        }
        private Dictionary<string, Dictionary<string, List<string>>> dicContents = new Dictionary<string, Dictionary<string, List<string>>>();
        private bool firstColumnLoad = true;//第一列初次加载
        private bool towColumnLoad = true;//第二列初次加载
        private bool threeColumnLoad = true;//第三列初次加载
        private bool isNull = false;//是否子类别为空

        /// <summary>
        /// 添加标题内容
        /// </summary>
        public void AddDictionary() 
        {
            string key = string.Empty;

            string strKey = string.Empty;
            key = "借阅_1";
            Dictionary<string, List<string>> dicBorrow = new Dictionary<string, List<string>>();

            strKey = "内阅_Y";
            dicBorrow.Add(strKey, new List<string>());
            dicBorrow[strKey].Add("借次_JYRC");
            dicBorrow[strKey].Add("纸阅（本）_ZZJY");
            strKey = "外阅_N";
            dicBorrow.Add(strKey, new List<string>());
            dicBorrow[strKey].Add("借次_JYRC");
            dicBorrow[strKey].Add("纸阅（本）_ZZJY");
            dicContents.Add(key, dicBorrow);

            key = "查印_3";
            Dictionary<string, List<string>> dicSearchAndPrint = new Dictionary<string, List<string>>();
            strKey = "对询_Y";
            dicSearchAndPrint.Add(strKey, new List<string>());
            dicSearchAndPrint[strKey].Add("查次_CDRC");
            dicSearchAndPrint[strKey].Add("查目_CKTM");
            strKey = "对1询_N";
            dicSearchAndPrint.Add(strKey, new List<string>());
            dicSearchAndPrint[strKey].Add("查次_CDRC");
            dicSearchAndPrint[strKey].Add("查目_CKTM");
            strKey = "电询_T";
            dicSearchAndPrint.Add(strKey, new List<string>());
            dicSearchAndPrint[strKey].Add("查次_CDRC");
            dicSearchAndPrint[strKey].Add("查目_CKTM");
            dicContents.Add(key, dicSearchAndPrint);

            key = "在阅_2";
            Dictionary<string, List<string>> dicSearchOnline = new Dictionary<string, List<string>>();
            strKey = "审数_1,4";
            dicSearchOnline.Add(strKey, new List<string>());
            dicContents.Add(key, dicSearchOnline);

            key = "土记";
            Dictionary<string, List<string>> dicLandUse = new Dictionary<string, List<string>>();
            strKey = "法控_7";
            dicLandUse.Add(strKey, new List<string>());
            dicLandUse[strKey].Add("查次_CDRC");
            dicLandUse[strKey].Add("查目_CKTM");
            strKey = "原询_4";
            dicLandUse.Add(strKey, new List<string>());
            dicLandUse[strKey].Add("查次_CDRC");
            dicLandUse[strKey].Add("查目_CKTM");
            strKey = "登询_5";
            dicLandUse.Add(strKey, new List<string>());
            dicLandUse[strKey].Add("查次_CDRC");
            dicLandUse[strKey].Add("查目_CKTM");
            strKey = "他询_6";
            dicLandUse.Add(strKey, new List<string>());
            dicLandUse[strKey].Add("次_CDRC");
            dicLandUse[strKey].Add("目_CKTM");
            dicContents.Add(key, dicLandUse);

            key = "打计_3";
            Dictionary<string, List<string>> dicPrint = new Dictionary<string, List<string>>();
            strKey = "对印_IP";
            dicPrint.Add(strKey, new List<string>());
            strKey = "对印_OP";
            dicPrint.Add(strKey, new List<string>());
            dicContents.Add(key, dicPrint);

            key = "统计_3";
            Dictionary<string, List<string>> dicImport = new Dictionary<string, List<string>>();
            strKey = "对出_IEP";
            dicImport.Add(strKey, new List<string>());
            strKey = "对出_OEP";
            dicImport.Add(strKey, new List<string>());
            dicContents.Add(key, dicImport);
        }

        /// <summary>
        /// 创建表格
        /// 如需扩展只需加入新的集合，增加行数/列数即可
        /// </summary>
        public void CreateGrid()
        {
            threeColumnLoad = true;//第三列初次加载
            towColumnLoad = true;//第二列初次加载
            firstColumnLoad = true;//第一列初次加载
            dicContents.Clear();
            //设置grid
            SourceGrid.Grid grid = grid1;
            grid.Rows.Clear();
            grid.Columns.Clear();
            grid.BorderStyle = BorderStyle.FixedSingle;
            grid.FixedRows = 1;
            grid.FixedColumns = 1;

            grid.Height = 1000;
            grid.Width = 1000;

            grid.ColumnsCount = 19;
            grid.Rows.Insert(0);
            //行数
            int rowsCount = 25;
            //列数
            int columnsCount = 18;
            #region 添加表格标题
            AddDictionary();

            #endregion

            //内容列
            SourceGrid.Cells.ColumnHeader head = null;
            head = new SourceGrid.Cells.ColumnHeader("月份");
            grid[0, 0] = head;
            grid[0, 0].ColumnSpan = 6;
            grid[0, 0].Editor = null;
            head.AutomaticSortEnabled = false;//禁止排序

            //计算中间每列宽度
            int ColumnsWidthCount = 0;
            for (int i = 6; i <= columnsCount; i++)
            {
                head = new SourceGrid.Cells.ColumnHeader(i - 5 + "月");
                grid[0, i] = head;
                grid[0, i].View.TextAlignment = DevAge.Drawing.ContentAlignment.MiddleCenter;
                grid.Columns[i].Width = 50;
                ColumnsWidthCount += 50;
                head.AutomaticSortEnabled = false;//禁止排序
            }

            head = new SourceGrid.Cells.ColumnHeader("横向合计");
            head.AutomaticSortEnabled = false;//禁止排序
            grid[0,columnsCount] = head;
            //最后一列的宽度填充
            grid.Columns[columnsCount].Width = grid1.Width - ColumnsWidthCount - 2 * 12;

            //内容行，以后扩展可以增加行数
            for (int i = 1; i <= rowsCount; i++)
            {
                grid.Rows.Insert(i);
            }

            //第一列计数器
            int rowIndex = 0;
            int num = 0;
            //第二列计数器
            int rowIndex_Tow=0;
            int num_Tow = 0;
            //第三列计数器
            int rowIndex_Three = 0;

            #region 绘制横向标题栏
            foreach (string iKey in dicContents.Keys)
            {
                rowIndex = 0;
                //计算每个大类要占用多少行
                foreach (string jKey in dicContents[iKey].Keys)
                {
                    rowIndex_Tow = 0;
                    //如果数据标题只有两列
                    if (dicContents[iKey][jKey].Count == 0)
                    {
                        rowIndex = dicContents[iKey].Count;
                        rowIndex_Tow = 1;
                        rowIndex_Three++;
                        isNull = true;
                    }
                    else
                    {
                        //数据标题有三列
                        foreach (string kKey in dicContents[iKey][jKey])
                        {
                            rowIndex++;
                            rowIndex_Tow++;
                            rowIndex_Three++;

                            if (threeColumnLoad == true)
                            {
                                //第二列第一次加载的列计算方式
                                grid[1, 4] = new SourceGrid.Cells.Cell(kKey, typeof(string));
                                grid[1, 4].ColumnSpan = 2;
                                grid[1, 4].Editor = null;//禁止修改
                                threeColumnLoad = false;
                            }
                            else
                            {
                                //第二列非第一次加载以后的列计算方式
                                grid[rowIndex_Three, 4] = new SourceGrid.Cells.Cell(kKey, typeof(string));
                                grid[rowIndex_Three, 4].ColumnSpan = 2;
                                grid[rowIndex_Three, 4].Editor = null;//禁止修改
                            }
                           
                        }
                        isNull = false;
                    }
                    LoadGridRowsTitle(grid, rowIndex_Tow, ref num_Tow, ref towColumnLoad, isNull, jKey, true, 1, 2);
                }
                LoadGridRowsTitle(grid, rowIndex, ref num, ref firstColumnLoad, isNull, iKey, false, 1, 0);
            } 
            #endregion

            #region 填充数据

            //for (int i = 1; i < num; i++)
            //{
            //    for (int j = 6; j <= columnsCount; j++)
            //    {
            //        grid[i, j] = new SourceGrid.Cells.Cell(grid[i, 0].Value.ToString() + "、" + grid[i, 3].Value.ToString() + "、" + grid[i, 5].Value.ToString(), typeof(string));
            //        grid[i, j].Editor = null;
            //    }
            //}

            #endregion

            #region 页脚
            //利用档案总计
            grid[num, 0] = new SourceGrid.Cells.Cell("总计", typeof(string)); ;
            grid[num, 0].ColumnSpan = 6;
            grid[num, 0].Editor = null;
            head.AutomaticSortEnabled = false;//禁止排序
            //利用档案总计
            grid[num, 6] = new SourceGrid.Cells.Cell("KKCOUNT", typeof(string)); ;
            grid[num, 6].ColumnSpan = 13;
            grid[num, 6].Editor = null;
            head.AutomaticSortEnabled = false;//禁止排序

            //利用档案人次总计
            grid[num + 1, 0] = new SourceGrid.Cells.Cell("利用总计", typeof(string)); ;
            grid[num + 1, 0].ColumnSpan = 6;
            grid[num + 1, 0].Editor = null;
            head.AutomaticSortEnabled = false;//禁止排序

            //利用档案总计
            grid[num + 1, 6] = new SourceGrid.Cells.Cell("KKCOUNT1", typeof(string)); ;
            grid[num + 1, 6].ColumnSpan = 13;
            grid[num + 1, 6].Editor = null;
            head.AutomaticSortEnabled = false;//禁止排序 
            #endregion
        }

        /// <summary>
        /// sourceGrid处理合并列的类
        /// </summary>
        /// <param name="grid">控件</param>
        /// <param name="rowIndex">合并行数</param>
        /// <param name="num">下一个标题的位置</param>
        /// <param name="firstLoad">是否第一次加载</param>
        /// <param name="isNull">是否有第三级别</param>
        /// <param name="Key">标题名</param>
        /// <param name="isSecondColum">是否为无第三级别的类</param>
        /// <param name="x">初始横坐标</param>
        /// <param name="y">单元格纵坐标</param>
        private static void LoadGridRowsTitle(SourceGrid.Grid grid, int rowIndex, ref int num, ref bool firstLoad, bool isNull, string Key, bool isSecondColum, int x, int y)
        {
            if (firstLoad == true)
            {
                //第二列第一次加载的列计算方式
                grid[x, y] = new SourceGrid.Cells.Cell(Key, typeof(string));
                grid[x, y].ColumnSpan = 2;
                grid[x, y].RowSpan = rowIndex;
                grid[x, y].Editor = null;//禁止修改
                grid[x, y].View.Border = new DevAge.Drawing.RectangleBorder(new DevAge.Drawing.BorderLine(Color.Black, 1, DashStyle.Solid, 0), new DevAge.Drawing.BorderLine(Color.Black, 1, DashStyle.Solid, 0));//边框
                //居中
                grid[x, y].View.TextAlignment = DevAge.Drawing.ContentAlignment.MiddleCenter;
                firstLoad = false;
                num = 1 + rowIndex;
            }
            else
            {
                //try
                //{

                    //第二列非第一次加载以后的列计算方式
                    grid[num, y] = new SourceGrid.Cells.Cell(Key, typeof(string));
                    grid[num, y].ColumnSpan = 2;
                    grid[num, y].RowSpan = rowIndex;
                    grid[num, y].Editor = null;//禁止修改
                    if (isSecondColum)
                    {
                        if (isNull)
                        {
                            grid[num, y].ColumnSpan = 4;
                        }
                    }
                //}
                //catch (Exception)
                //{

                //    return;
                //}

                num = num + rowIndex;
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //第一列计数器
            int rowIndex = 0;
            int num = 0;
            //int index = 0;
            //第二列计数器
            int rowIndex_Tow = 0;
            int num_Tow = 0;
            //第三列计数器
            int rowIndex_Three = 1;

            firstColumnLoad = true;//第一列初次加载
            towColumnLoad = true;//第二列初次加载
            threeColumnLoad = true;//第三列初次加载
            isNull = false;//是否子类别为空
            int columnsCount = 18;//列数

            using (SaveFileDialog dlg = new SaveFileDialog())
            {
                dlg.Filter = "Xls文件(*.xls)|*.xls";
                dlg.Title = "保存导出结果";
                if (dlg.ShowDialog(this) == DialogResult.OK)
                {
                    Workbook workbook = new Workbook(); //工作簿 
                    Worksheet sheet = workbook.Worksheets[0]; //工作表 
                    Cells cells = sheet.Cells;//单元格 
                    #region Excel样式设置
                    //大标题样式
                    Style styleTitle = workbook.Styles[workbook.Styles.Add()];//新增样式 
                    styleTitle.HorizontalAlignment = TextAlignmentType.Center;//文字居中 
                    styleTitle.Font.Name = "宋体";//文字字体 
                    styleTitle.Font.Size = 15;//文字大小 
                    styleTitle.Font.IsBold = true;//粗体 
                    styleTitle.IsTextWrapped = true;//单元格内容自动换行 

                    //子标题样式    
                    Style styleSubtitle = workbook.Styles[workbook.Styles.Add()];//新增样式 
                    styleSubtitle.HorizontalAlignment = TextAlignmentType.Center;//文字居中 
                    styleSubtitle.Font.Name = "宋体";//文字字体 
                    styleSubtitle.Font.Size = 11;//文字大小 
                    styleSubtitle.Font.IsBold = true;//粗体 
                    styleSubtitle.IsTextWrapped = true;//单元格内容自动换行 
                    //styleTitle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
                    //styleTitle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
                    //styleTitle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
                    //styleTitle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
                    cells.Merge(0, 0, 1, grid1.Columns.Count);//合并单元格 
                    cells[0, 0].PutValue("利用统计（2016年度）");//填写内容 
                    cells[0, 0].SetStyle(styleTitle);
                    cells.Rows[0].Height = 50;

                    #endregion

                    //内容列
                    cells.Merge(1, 0, 1, 6);
                    cells[1, 0].PutValue("月份");
                    cells[1, 0].SetStyle(styleSubtitle);
                    for (int i = 6; i <= columnsCount; i++)
                    {
                        cells[1, i].PutValue(i - 5 + "月");
                        cells[1, i].SetStyle(styleSubtitle);
                    }
                    cells[1, columnsCount].PutValue("横向合计");
                    cells[1, columnsCount].SetStyle(styleSubtitle);

                    #region 绘制横向标题栏
                    foreach (string iKey in dicContents.Keys)
                    {
                        rowIndex = 0;
                        //计算每个大类要占用多少行
                        foreach (string jKey in dicContents[iKey].Keys)
                        {
                            rowIndex_Tow = 0;
                            //如果数据标题只有两列
                            if (dicContents[iKey][jKey].Count == 0)
                            {
                                rowIndex = dicContents[iKey].Count;
                                rowIndex_Tow = 1;
                                rowIndex_Three++;
                                isNull = true;
                            }
                            else
                            {
                                //数据标题有三列
                                foreach (string kKey in dicContents[iKey][jKey])
                                {
                                    rowIndex++;
                                    rowIndex_Tow++;
                                    rowIndex_Three++;
                                    if (threeColumnLoad == true)
                                    {
                                        //第二列第一次加载的列计算方式
                                        cells.Merge(2, 4, 1, 2);
                                        cells[2, 4].PutValue(kKey);//填写内容 
                                        cells[2, 4].SetStyle(styleSubtitle);
                                        threeColumnLoad = false;
                                    }
                                    else
                                    {
                                        try
                                        {
                                            //第二列非第一次加载以后的列计算方式
                                            cells.Merge(rowIndex_Three, 4, 1, 2);
                                            cells[rowIndex_Three, 4].PutValue(kKey);//填写内容 
                                            cells[rowIndex_Three, 4].SetStyle(styleSubtitle);
                                        }
                                        catch (Exception)
                                        {
                                            break;
                                        }
                                    }
                                }
                                isNull = false;
                            }
                            LoadExcelRowsTitle(rowIndex_Tow, ref num_Tow, ref towColumnLoad, cells, styleSubtitle, isNull, jKey, true, 2, 2);
                        }
                        LoadExcelRowsTitle(rowIndex, ref num, ref firstColumnLoad, cells, styleSubtitle,isNull,iKey,false,2,0);
                    }
                    #endregion

                    #region 填充主要数据

                    for (int i = 1; i < grid1.Rows.Count - 2; i++)
                    {
                        for (int j = 6; j <= grid1.Columns.Count - 1; j++)
                        {
                            cells[i + 1, j].PutValue("kkk");
                            var contentStyle = cells[i + 1, j].GetStyle();
                            contentStyle.HorizontalAlignment = TextAlignmentType.Center;
                            cells[i + 1, j].SetStyle(contentStyle);  
                        }
                    } 
                    #endregion

                    #region 页脚
                    //利用档案总计
                    cells[num, 0].PutValue("总计");
                    cells.Merge(num, 0, 1, 6);
                    cells[num, 0].SetStyle(styleSubtitle);
                    cells[num, 6].PutValue(grid1[num-1 , grid1.Columns.Count - 1].Value);

                    var footerStyle = cells[num, 6].GetStyle();
                    footerStyle.HorizontalAlignment = TextAlignmentType.Center;
                    cells[num, 6].SetStyle(footerStyle);  
                    cells.Merge(num, 6, 1, 13);

                    //利用档案人次总计
                    cells[num+1, 0].PutValue("利用总计");
                    cells.Merge(num + 1, 0, 1, 6);
                    cells[num+1, 0].SetStyle(styleSubtitle);
                    cells[num+1, 6].PutValue(grid1[num, grid1.Columns.Count - 1].Value);
                    footerStyle.HorizontalAlignment = TextAlignmentType.Center;
                    cells[num+1, 6].SetStyle(footerStyle);  
                    cells.Merge(num + 1, 6, 1, 13);
                    #endregion

                    workbook.Save(dlg.FileName);

                }
            }
        }

        /// <summary>
        /// Excel处理合并列的类
        /// </summary>
        /// <param name="grid">控件</param>
        /// <param name="rowIndex">合并行数</param>
        /// <param name="num">下一个标题的位置</param>
        /// <param name="firstLoad">是否第一次加载</param>
        /// <param name="isNull">是否有第三级别</param>
        /// <param name="Key">标题名</param>
        /// <param name="isSecondColum">是否为无第三级别的类</param>
        /// <param name="x">初始横坐标</param>
        /// <param name="y">单元格纵坐标</param>
        private static void LoadExcelRowsTitle(int rowIndex, ref int num, ref bool firstLoad, Cells cells, Style styleTitle, bool isNull, string Key, bool isSecondColum, int x, int y)
        {
            if (firstLoad == true)
            {
                //第一列第一次加载的列计算方式
                cells.Merge(x, y, rowIndex, 2);//合并列
                cells[x, y].PutValue(Key);//填写内容 
                cells[x, y].SetStyle(styleTitle);//填写内容 
                firstLoad = false;
                num = 2 + rowIndex;
            }
            else
            {
                //第一列第一次加载的列计算方式
                cells.Merge(num, y, rowIndex, 2);//合并列
                cells[num, y].PutValue(Key);//填写内容 
                cells[num, y].SetStyle(styleTitle);//填写内容 
                if (isSecondColum)
                {
                    if (isNull)
                    {
                        cells.Merge(num, y, 1, 4);
                    }
                }
                num = num + rowIndex;
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            CreateGrid();
        }

    }
}

 grid样式设置：

例如文字居左：

 grid[i, j].View.TextAlignment = DevAge.Drawing.ContentAlignment.TopLeft;

此版本适用于C/S端