sourcegrid统计报表画法以及EXCEL导出内容代码完全版
阅读原文时间:2023年07月13日阅读:2

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 = ;  
        grid.FixedColumns = ;

        grid.Height = ;  
        grid.Width = ;

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

        #endregion

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

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

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

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

        //第一列计数器  
        int rowIndex = ;  
        int num = ;  
        //第二列计数器  
        int rowIndex\_Tow=;  
        int num\_Tow = ;  
        //第三列计数器  
        int rowIndex\_Three = ;

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

                        if (threeColumnLoad == true)  
                        {  
                            //第二列第一次加载的列计算方式  
                            grid\[, \] = new SourceGrid.Cells.Cell(kKey, typeof(string));  
                            grid\[, \].ColumnSpan = ;  
                            grid\[, \].Editor = null;//禁止修改  
                            threeColumnLoad = false;  
                        }  
                        else  
                        {  
                            //第二列非第一次加载以后的列计算方式  
                            grid\[rowIndex\_Three, \] = new SourceGrid.Cells.Cell(kKey, typeof(string));  
                            grid\[rowIndex\_Three, \].ColumnSpan = ;  
                            grid\[rowIndex\_Three, \].Editor = null;//禁止修改  
                        }

                    }  
                    isNull = false;  
                }  
                LoadGridRowsTitle(grid, rowIndex\_Tow, ref num\_Tow, ref towColumnLoad, isNull, jKey, true, , );  
            }  
            LoadGridRowsTitle(grid, rowIndex, ref num, ref firstColumnLoad, isNull, iKey, false, , );  
        }  
        #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, \] = new SourceGrid.Cells.Cell("总计", typeof(string)); ;  
        grid\[num, \].ColumnSpan = ;  
        grid\[num, \].Editor = null;  
        head.AutomaticSortEnabled = false;//禁止排序  
        //利用档案总计  
        grid\[num, \] = new SourceGrid.Cells.Cell("KKCOUNT", typeof(string)); ;  
        grid\[num, \].ColumnSpan = ;  
        grid\[num, \].Editor = null;  
        head.AutomaticSortEnabled = false;//禁止排序

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

        //利用档案总计  
        grid\[num + , \] = new SourceGrid.Cells.Cell("KKCOUNT1", typeof(string)); ;  
        grid\[num + , \].ColumnSpan = ;  
        grid\[num + , \].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 = ;  
            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, , DashStyle.Solid, ), new DevAge.Drawing.BorderLine(Color.Black, , DashStyle.Solid, ));//边框  
            //居中  
            grid\[x, y\].View.TextAlignment = DevAge.Drawing.ContentAlignment.MiddleCenter;  
            firstLoad = false;  
            num =  + rowIndex;  
        }  
        else  
        {  
            //try  
            //{

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

            //    return;  
            //}

            num = num + rowIndex;  
        }  
    }

    private void button1\_Click(object sender, EventArgs e)  
    {  
        //第一列计数器  
        int rowIndex = ;  
        int num = ;  
        //int index = 0;  
        //第二列计数器  
        int rowIndex\_Tow = ;  
        int num\_Tow = ;  
        //第三列计数器  
        int rowIndex\_Three = ;

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

        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\[\]; //工作表  
                Cells cells = sheet.Cells;//单元格  
                #region Excel样式设置  
                //大标题样式  
                Style styleTitle = workbook.Styles\[workbook.Styles.Add()\];//新增样式  
                styleTitle.HorizontalAlignment = TextAlignmentType.Center;//文字居中  
                styleTitle.Font.Name = "宋体";//文字字体  
                styleTitle.Font.Size = ;//文字大小  
                styleTitle.Font.IsBold = true;//粗体  
                styleTitle.IsTextWrapped = true;//单元格内容自动换行 

                //子标题样式  
                Style styleSubtitle = workbook.Styles\[workbook.Styles.Add()\];//新增样式  
                styleSubtitle.HorizontalAlignment = TextAlignmentType.Center;//文字居中  
                styleSubtitle.Font.Name = "宋体";//文字字体  
                styleSubtitle.Font.Size = ;//文字大小  
                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(, , , grid1.Columns.Count);//合并单元格  
                cells\[, \].PutValue("利用统计(2016年度)");//填写内容  
                cells\[, \].SetStyle(styleTitle);  
                cells.Rows\[\].Height = ;

                #endregion

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

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

                #region 填充主要数据

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

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

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

                //利用档案人次总计  
                cells\[num+, \].PutValue("利用总计");  
                cells.Merge(num + , , , );  
                cells\[num+, \].SetStyle(styleSubtitle);  
                cells\[num+, \].PutValue(grid1\[num, grid1.Columns.Count - \].Value);  
                footerStyle.HorizontalAlignment = TextAlignmentType.Center;  
                cells\[num+, \].SetStyle(footerStyle);  
                cells.Merge(num + , , , );  
                #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, );//合并列  
            cells\[x, y\].PutValue(Key);//填写内容  
            cells\[x, y\].SetStyle(styleTitle);//填写内容  
            firstLoad = false;  
            num =  + rowIndex;  
        }  
        else  
        {  
            //第一列第一次加载的列计算方式  
            cells.Merge(num, y, rowIndex, );//合并列  
            cells\[num, y\].PutValue(Key);//填写内容  
            cells\[num, y\].SetStyle(styleTitle);//填写内容  
            if (isSecondColum)  
            {  
                if (isNull)  
                {  
                    cells.Merge(num, y, , );  
                }  
            }  
            num = num + rowIndex;  
        }  
    }

    private void button2\_Click(object sender, EventArgs e)  
    {  
        CreateGrid();  
    }

}  

}

grid样式设置:

例如文字居左:

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

此版本适用于C/S端

手机扫一扫

移动阅读更方便

阿里云服务器
腾讯云服务器
七牛云服务器

你可能感兴趣的文章