WPF 学生成绩管理工具开发笔记(10) ——ReoGrid应用

Nuget安装   Install-Package unvell.ReoGridWPF.dll -Version 2.1.0

在WPF中应用，有些限制 

 

     

 

        主要涉及ReoGrid写数据方法和边框设置，写博客以留存。

using ScoreTools.Models;
using System;
using System.Collections.ObjectModel;
using System.Data;
using System.Reflection;
using System.Windows.Input;
using unvell.ReoGrid;
using unvell.ReoGrid.DataFormat;

namespace ScoreTools.ViewModels
{
    public class UcBaiMingAvgViewModel : NotifycationBase
    {

        #region 绑定属性
        private ObservableCollection<StudentSCore> _ScoreList;
        public ObservableCollection<StudentSCore> ScoreList
        {
            get { return this._ScoreList; }
            set
            {
                this._ScoreList = value;
                RaisePropertyChanged(() => ScoreList);
            }
        }
        #endregion

        private ICommand _SaveToExcelCommand;
        public ICommand SaveToExcelCommand
        {
            get
            {
                if (this._SaveToExcelCommand == null)
                    this._SaveToExcelCommand = new RelayCommandBase(SaveToExcelCommandAction);
                return this._SaveToExcelCommand;
            }
        }

        private void SaveToExcelCommandAction(object obj)
        {
            if (grdsheet.MaxContentRow >= 3)
            {
                var filepath = Comm.GeneralUnits.GetSaveFileNameDialog("百名平均表");
                this.grd.Save(filepath);
                System.Windows.MessageBox.Show("保存成功！");
            }
        }

        private ReoGridControl grd;
        private Worksheet grdsheet;

        public UcBaiMingAvgViewModel(ReoGridControl reogrid)
        {
            this.grd = reogrid;
            this.grdsheet = grd.CurrentWorksheet;
            this.grdsheet.Name = "百名平均统计表";
            LoadStudentScoreList();
        }

        private void LoadStudentScoreList()
        {
            int colIndex = -1, rowIndex = 1;
            string subJectFieldsStr1 = string.Empty, subJectFieldsStr2 = string.Empty;

            Type stuType = typeof(StudentSCore);
            PropertyInfo[] propertyInfos = stuType.GetProperties();

            //写入标题
            grdsheet[0, 0] = "百名平均统计表";
            var rng = grdsheet.Ranges[0, 0, 1, propertyInfos.Length - 1];
            rng.Merge();
            rng.Style.HorizontalAlign = ReoGridHorAlign.Center;
            rng.Style.VerticalAlign = ReoGridVerAlign.Middle;
            rng.Style.FontSize = 20;
            rng.Style.Bold = true;


            rowIndex = 1;
            //利用类反射，写入表头
            foreach (var pi in propertyInfos)
            {
                //排除属性
                if (pi.Name.Contains("班名次")) continue;

                colIndex++;
                SetRangeValue(colIndex, rowIndex, pi.Name, true);

                //学科字段拼接
                var v = (StudentAttribute)pi.GetCustomAttribute(typeof(StudentAttribute));
                if (v.IsSubJect && !pi.Name.Contains("排名"))
                    subJectFieldsStr2 += $",ROUND(AVG({pi.Name}),2),null";

                if (v.IsSubJect)
                    subJectFieldsStr1 += $", {pi.Name}";
            }


            int[] arInt = { 10, 20, 30, 40, 50, 100, 200, 300 };//统计范围使用固定的数组
            DataTable dt;

            int positionStart = 1;  //起始行
            int prevN = 0;          //前一次统计
            foreach (int n in arInt)
            {
                string sql = $"select 编号,班级,校名次,姓名{subJectFieldsStr1},总分 from ScoresInfo where cardid={App.CurrentCard.CardID} and 校名次<={n} and 校名次>{prevN}" +
                    $"   union all  " +
                    $" select '前{n}名平均',null,null,null{subJectFieldsStr2},null from ScoresInfo where cardid ={App.CurrentCard.CardID}  and 校名次<={n}";

                prevN = n;

                dt = App.Db.GetDataTable(sql);//获得本次查询结果

                //写入sheet 表格
                foreach (DataRow row in dt.Rows)
                {
                    rowIndex++;
                    //每次行+1，保证行数够用
                    if (rowIndex >= grdsheet.RowCount) grdsheet.Rows++;

                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        SetRangeValue(i, rowIndex, row[i]);
                    }

                    //平均行合并
                    if (row[0].ToString().Contains("平均"))
                    {
                        grdsheet.Ranges[$"A{rowIndex + 1}:C{rowIndex + 1}"].Merge();
                    }

                }
                //外边框
                int lines = positionStart == 1 ? dt.Rows.Count + 1 : dt.Rows.Count;
                grdsheet.SetRangeBorders(new RangePosition(positionStart, 0, lines, propertyInfos.Length - 1),
                                              BorderPositions.Outside, new RangeBorderStyle
                                              {
                                                  Color = unvell.ReoGrid.Graphics.SolidColor.Black,
                                                  Style = BorderLineStyle.BoldSolid
                                              });
                //内边框
                grdsheet.SetRangeBorders(new RangePosition(positionStart, 0, lines, propertyInfos.Length - 1),
                                              BorderPositions.InsideAll, new RangeBorderStyle
                                              {
                                                  Color = unvell.ReoGrid.Graphics.SolidColor.Black,
                                                  Style = BorderLineStyle.Solid
                                              });


                //增加一空行
                rowIndex++;
                if (rowIndex >= grdsheet.RowCount) grdsheet.Rows++;

                positionStart = rowIndex + 1;
            }

            grdsheet.AutoFitColumnWidth(0, true);//首列自动列宽

        }

        private void SetRangeValue(int colIndex, int rowIndex, object value, bool IsBold = false)
        {
            CellPosition pos = new CellPosition(rowIndex, colIndex);
            //先设置格式，后写内容
            grdsheet.SetRangeDataFormat(pos.ToAddress(), CellDataFormatFlag.Text);
            grdsheet.SetCellData(pos.ToAddress(), value);
            grdsheet.Ranges[pos.ToAddress()].Style.FontName = "宋体";//,Arial
            Cell cell = grdsheet.GetCell(pos);
            cell.Style.HAlign = ReoGridHorAlign.Center;
            cell.Style.Bold = IsBold;
        }
    }
}