using FarPoint.Win;
using FarPoint.Win.Spread;
using FarPoint.Win.Spread.CellType;
using GrapeCity.CalcEngine;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Text;
using System.Windows.Forms;

namespace spread_win_richtextbox
{
    public partial class Form3 : Form
    {
        public Form3()
        {
            InitializeComponent();
            InitTableField();
            formulaTextBox.Attach(fpSpread1);
            //fpSpread1.AddCustomFunction(new SUMM());
        }
        private void InitTableField()
        {
            DataColumn newCol = dt.Columns.Add("stdName", System.Type.GetType("System.String"));
            newCol = dt.Columns.Add("SN", System.Type.GetType("System.String"));
            newCol = dt.Columns.Add("BRtf", System.Type.GetType("System.Int32"));
            newCol = dt.Columns.Add("Bound", System.Type.GetType("System.String"));
            newCol = dt.Columns.Add("BoundRtf", System.Type.GetType("System.String"));
            newCol = dt.Columns.Add("GRtf", System.Type.GetType("System.Int32"));
            newCol = dt.Columns.Add("Grade", System.Type.GetType("System.String"));
            newCol = dt.Columns.Add("GradeRtf", System.Type.GetType("System.String"));
            newCol = dt.Columns.Add("CerNo", System.Type.GetType("System.String"));
            newCol = dt.Columns.Add("yxq", System.Type.GetType("System.String"));
        }
        //System.IO.MemoryStream stream;

        DataTable dt = new DataTable();
        private void Form1_Load(object sender, EventArgs e)
        {
          
            //for (int i = 0; i < fpSpread1.Sheets.Count; i++)
            //{
            //    fpSpread1.Sheets[i].Models.Data.Changed += Data_Changed;               
            //}
        }
        public void test(int i)
        {
            try
            {
                fpSpread1.Open(AppDomain.CurrentDomain.BaseDirectory + "1.xml");
                AddPage(fpSpread1.Sheets[0], 0, 2, false);
                SetStdInfo(fpSpread1.Sheets[0], false);
                AddPage(fpSpread1.Sheets[1], 0, 3, true);
                SetStdInfo(fpSpread1.Sheets[1], true);
                fpSpread1.Save(AppDomain.CurrentDomain.BaseDirectory + "\\TEMP\\S" + i.ToString() + ".xml", false);
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message);
            }
        }
        private void SetStdInfo(FarPoint.Win.Spread.SheetView Spread_Excel, bool CopyRow)
        {
            Spread_Excel.Protect = false;
            //dt.Rows.Add(new object[] { "空气中甲烷气体标准物质的成分表示不同的类型和状态", "GBW(E)060755", "1", "123457", @"{\rtf1\ansi\ansicpg936\deff0{\fonttbl{\f0\fnil\fcharset134 \'cb\'ce\'cc\'e5;}}  \viewkind4\uc1\pard\qc\lang2052\f0\fs23 12\up7 3\up0 45\dn7 7\up0\par  }  ", "1" , "U=1% K=A32", @"{\rtf1\ansi\ansicpg936\deff0{\fonttbl{\f0\fnil\fcharset134 \'cb\'ce\'cc\'e5;}{\f1\fswiss\fcharset0 Arial;}}  \viewkind4\uc1\pard\qc\lang2052\f0\fs23 U=\up7 1\up0 % K=\f1 A\up7\f0 3\dn7 2\up0\par  }  ", "20170755", "2020-12-30" });
            //dt.Rows.Add(new object[] { "空气中甲烷气体标准物质", "GBW(E)060756", "0", "0.181*10-2 mol/molfda", @"", "0", "U=1% K=3a", @"", "20170756", "2020-12-30" });
            //dt.Rows.Add(new object[] { "空气中甲烷气体标准物质", "GBW(E)060757", "0", "1.095*10-2 mol/mol", @"", "0", "U=1% K=5", @"", "20170757", "2020-12-30" });
            string Sql = "select * from p_RecdStd where DetailId=201900001641 order by stdIdx";


            DataTable dt = new DataTable();
            if (sqlConnection.State == ConnectionState.Closed)
                sqlConnection.Open();
            SqlDataAdapter command = new SqlDataAdapter(Sql, sqlConnection);
            command.Fill(dt);
           
            int Row = Spread_Excel.Cells["标准器"].Row.Index;
            int Col = Spread_Excel.Cells["标准器"].Column.Index;
            if (CopyRow == true)
            {
                int SpanRowCount = 1;
                if (dt.Rows.Count < 5)
                    SpanRowCount = 2;
                for (int i = 2; i < 8; i++)
                {
                    int ColumnSpan = Spread_Excel.Cells[Row - 2, Col].ColumnSpan;
                    Spread_Excel.AddSpanCell(Row, Col, SpanRowCount, ColumnSpan);

                    var style2 = Spread_Excel.GetStyleInfo(Row - 2, Col);
                    if (style2 != null)
                    {
                        Spread_Excel.SetStyleInfo(Row, Col, style2);
                    }
                    Col = Col + ColumnSpan;
                }

                Row = Spread_Excel.Cells["标准器"].Row.Index;
                Col = Spread_Excel.Cells["标准器"].Column.Index;
            }
            int StrCol = Spread_Excel.Cells["标准器"].Column.Index;
            int RowSpan = Spread_Excel.Cells["标准器"].RowSpan;

            string fieldname = "stdName";
            int ColSpan = Spread_Excel.Cells[Row, Col].ColumnSpan;
            for (int r = 0; r < dt.Rows.Count; r++)
            {
                DataRow dr = dt.Rows[r];
                for (int c = 1; c <= 6; c++)
                {
                    if (c == 1)
                        fieldname = "stdName";
                    else if (c == 2)
                        fieldname = "SN";
                    else if (c == 3)
                    {
                        fieldname = "Bound";
                        if (Convert.ToBoolean(dr["BRtf"]) == true)
                            fieldname = "BoundRtf";
                    }
                    else if (c == 4)
                    {
                        fieldname = "Grade";
                        if (Convert.ToBoolean(dr["GRtf"]) == true)
                            fieldname = "GradeRtf";
                    }
                    else if (c == 5)
                        fieldname = "CerNo";
                    else if (c == 6)
                        fieldname = "yxq";
                    if (fieldname.Contains("Rtf"))
                    {
                        FarPoint.Win.Spread.CellType.RichTextCellType rtb = new FarPoint.Win.Spread.CellType.RichTextCellType();
                        rtb.WordWrap = true;
                        rtb.Multiline = true;
                        string text = dr[fieldname].ToString();
                        Spread_Excel.Cells[Row, Col].CellType = rtb;
                        Spread_Excel.Cells[Row, Col].Value = text;
                    }
                    else
                    {
                        string text = dr[fieldname].ToString();
                        Spread_Excel.Cells[Row, Col].Value = text;
                        //Spread_Excel.ActiveSheet.Cells[Row, Col].ResetCellType();
                        //FarPoint.Win.Spread.SheetView sv = Spread_Excel;
                        //FarPoint.Win.Spread.CellType.RichTextCellType rtb = new FarPoint.Win.Spread.CellType.RichTextCellType();
                        //rtb.Multiline = true;
                        //rtb.WordWrap = true;
                        //Spread_Excel.Cells[Row, Col].CellType = rtb;
                        //Spread_Excel.Cells[Row, Col].Value = text;

                    }
                    if (c == 6)
                        Col = 0;
                    else
                        Col = Col + Spread_Excel.Cells[Row, Col].ColumnSpan;
                    //CTextCellType ctc = new CTextCellType();
                    //Spread_Excel.Cells[Row, Col].CellType = ctc;
                }
                if (r + 1 < dt.Rows.Count)
                {
                    Spread_Excel.CopyRange(Row, 0, Row + RowSpan, 0, RowSpan, Spread_Excel.ColumnCount, false);
                    Row = Row + RowSpan;
                    Col = StrCol;

                }                
            }
            dt.Clear();
            command.Dispose();
            sqlConnection.Close();
        }
        private void Data_Changed(object sender, FarPoint.Win.Spread.Model.SheetDataModelEventArgs e)
        {
            MessageBox.Show(e.Row.ToString()+","+e.Column.ToString());
        }

        private void button1_Click(object sender, EventArgs e)
        {


            fpSpread1.Sheets[1].Protect = false;

            string Sql = "select * from p_RecdStd where DetailId=201900001641 order by stdIdx";
            DataTable dt = new DataTable();
            if (sqlConnection.State == ConnectionState.Closed)
                sqlConnection.Open();
            SqlDataAdapter command = new SqlDataAdapter(Sql, sqlConnection);
            command.Fill(dt);

            int Row = fpSpread1.Sheets[1].Cells["标准器"].Row.Index;
            int Col = fpSpread1.Sheets[1].Cells["标准器"].Column.Index;
            //int ColumnSpan = fpSpread1.Sheets[1].Cells["标准器"].ColumnSpan;

            for (int i = 2; i < 8; i++)
            {
                int ColumnSpan = fpSpread1.Sheets[1].Cells[Row - 2, Col].ColumnSpan;
                fpSpread1.Sheets[1].AddSpanCell(Row, Col, 1, ColumnSpan);
                Col = Col + ColumnSpan;
            }

            //this.fpSpread1.Sheets[0].Cells[1, 1].Border.BorderStyle = BorderStyle.None;



            //this.fpSpread1.ActiveSheet.Cells[1, 1]HorizontalGridLine = new GridLine(GridLineType.None);
            //this.fpSpread1.ActiveSheet.VerticalGridLine = new GridLine(GridLineType.None);



            // AddPage(fpSpread1.Sheets[0], 0, 0, false);


            //fpSpread1.Sheets[0].PrintInfo.ShowBorder = false;//是否要打印在整个控件周围的外框边界。
            //fpSpread1.Sheets[0].PrintInfo.ShowGrid = false;//该值指示是否打印网格线
            //fpSpread1.Sheets[0].PrintInfo.ShowRowHeaders = false;//获取或设置是否打印行头
            //fpSpread1.Sheets[0].PrintInfo.ShowColumnHeaders = false;//获取或设置是否打印列头
            //fpSpread1.Sheets[0].PrintInfo.Centering = FarPoint.Win.Spread.Centering.Horizontal;//该值指示打印输出时的居中显示方式
            ////fpSpread1.Sheets[0].PrintInfo.JobName = FileName;
            //fpSpread1.Sheets[0].PrintInfo.ShowPrintDialog = false;//是否显示打印机选择窗口
            //fpSpread1.Sheets[0].PrintInfo.ShowColor = false;
            //fpSpread1.Sheets[0].PrintInfo.Preview = true;//是否预览
            ////if (PrintName != "")
            ////    Spread_Excel.PrintInfo.Printer = PrintName;//打印机名称
            ////Spread_Excel.PrintInfo = printset;
            //fpSpread1.PrintSheet(fpSpread1.Sheets[0]);




            //stream.Seek(0, System.IO.SeekOrigin.Begin);
            //fpSpread1.Open(stream);
            //fpSpread1.Invalidate();

            //fpSpreadDesigner.ShowDialog(fpSpread1);





            //System.IO.MemoryStream stream = new System.IO.MemoryStream();
            //fpSpread2.Save(AppDomain.CurrentDomain.BaseDirectory + "111.xml", FarPoint.Win.Spread.SaveXMLOptions.AllAndStyleMerging);
            //fpSpread2.Save(stream, false);
            //byte[] fbytes = stream.GetBuffer();





            //FarPoint.Win.Spread.PrintInfo printset = new FarPoint.Win.Spread.PrintInfo();
            //printset.ShowBorder = false;//是否要打印在整个控件周围的外框边界。
            //printset.ShowGrid = false;//该值指示是否打印网格线
            //printset.ShowRowHeaders = false;//获取或设置是否打印行头
            //printset.ShowColumnHeaders = false;//获取或设置是否打印列头
            //printset.Centering = FarPoint.Win.Spread.Centering.Horizontal;//该值指示打印输出时的居中显示方式
            ////printset.JobName = FileName;
            //printset.ShowPrintDialog = false;//是否显示打印机选择窗口
            //printset.ShowColor = false;
            //printset.Preview = true;//是否预览

            ////if (PrintName != "")
            ////    printset.Printer = PrintName;//打印机名称
            //fpSpread2.ActiveSheet.PrintInfo = printset;
            //fpSpread2.PrintSheet(fpSpread2.ActiveSheet);

            ////System.IO.MemoryStream stream = new System.IO.MemoryStream();
            ////fpSpread2.Save(AppDomain.CurrentDomain.BaseDirectory + "1.xml", false);
        }

        private void AddPage(SheetView sv, int RowIndex, int tpId, bool AddPic)
        {
            int rr = 0, cc = 0;
            FpSpread Spread2 = new FpSpread();
            try
            {
                Spread2.Open(AppDomain.CurrentDomain.BaseDirectory + tpId.ToString() + ".xml");
                sv.AddRows(RowIndex, Spread2.Sheets[0].RowCount);
                for (int r = 0; r < Spread2.Sheets[0].RowCount; r++)
                {
                    for (int c = 0; c < Spread2.Sheets[0].ColumnCount; c++)// copy span;  
                    {
                        rr = r; cc = c;
                        var spans = Spread2.Sheets[0].GetSpanCell(r, c);
                        if (spans != null && spans.Row == r && spans.Column == c)
                        {
                            sv.AddSpanCell(RowIndex + r, c, spans.RowCount, spans.ColumnCount);
                        }
                        var cell = Spread2.Sheets[0].Cells[r, c];
                        if (!String.IsNullOrEmpty(cell.Formula))//copy formula and value
                        {
                            sv.SetFormula(RowIndex + r, c, cell.Formula);
                        }
                        else
                        {
                            sv.SetValue(RowIndex + r, c, cell.Value);
                            if (cell.Value != null && cell.Value.ToString().Contains("rtf"))
                            {
                                cell.CellType = new RichTextCellType() { Multiline = true, WordWrap = true };
                                //cell.CellType = new TextCellType() { Multiline = true, WordWrap = true };
                                //RichTextCellType rtb = new RichTextCellType();
                                //rtb.WordWrap = true;
                                //rtb.Multiline = true;
                                //cell.CellType = rtb;
                            }
                        }
                        sv.SetTag(RowIndex + r, c, cell.Tag);

                        var style2 = Spread2.Sheets[0].GetStyleInfo(r, c);
                        if (style2 != null)
                        {
                            //style2 = Spread2.Sheets[0].GetStyleInfo(r, c, new StyleInfo());
                            sv.SetStyleInfo(RowIndex + r, c, style2);
                        }
                    }
                }
                if (AddPic == true)
                {
                    foreach (FarPoint.Win.Spread.DrawingSpace.PSObject shape in Spread2.Sheets[0].DrawingContainer.ContainedObjects)
                    {
                        if (shape != null && shape.BackgroundImage != null)
                        {
                            var newshape = shape.Clone() as FarPoint.Win.Spread.DrawingSpace.PSShape;
                            sv.AddShape(newshape);
                        }
                    }
                }               
                Spread2.Dispose();
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message);
                Spread2.Dispose();
            }
            #region 老的方式，直接插入55行空白行
            //Spread_RecdCertif.ActiveSheet.AddRows(Spread_RecdCertif.ActiveSheet.Rows.Count, 55);
            ////Spread_RecdCertif.ActiveSheet.Rows[Spread_RecdCertif.ActiveSheet.Rows.Count - 55].PageBreak = true;//插入分页符           
            //Spread_RecdCertif.ActiveSheet.Rows[Spread_RecdCertif.ActiveSheet.Rows.Count - 55].BackColor = Color.LightGray; //添加分页线
            //for (int i = Spread_RecdCertif.ActiveSheet.Rows.Count - 55; i < Spread_RecdCertif.ActiveSheet.Rows.Count; i++)
            //{
            //    Spread_RecdCertif.ActiveSheet.Rows[i].HorizontalAlignment = CellHorizontalAlignment.Center;
            //    Spread_RecdCertif.ActiveSheet.Rows[i].VerticalAlignment = CellVerticalAlignment.Center;
            //}
            //Spread_RecdCertif.ActiveSheet.Rows[Spread_RecdCertif.ActiveSheet.Rows.Count - 55].Border = new FarPoint.Win.BevelBorder(FarPoint.Win.BevelBorderType.Raised, Color.Blue, Color.Blue, 2, false, true, false, true);//设置边框
            #endregion
        }


        class SUMM : Function
        {
            public SUMM() : base("SUMM", 3, 3, GrapeCity.CalcEngine.FunctionAttributes.Number) { }
            protected override void Evaluate(IArguments arguments, IValue result)
            {
                IValue range = arguments[0];
                //确认第一个参数是否是引用类型,不是的话返回错误值类型

                if (range.ValueType != GrapeCity.CalcEngine.ValueType.AdjustableReference && range.ValueType != GrapeCity.CalcEngine.ValueType.Reference)
                {
                    arguments.EvaluationContext.Error = GrapeCity.CalcEngine.CalcError.Value;
                    result.SetValue(GrapeCity.CalcEngine.CalcError.Value);
                }
                //if (range.ValueType != GrapeCity.CalcEngine.ValueType.AdjustableReference)
                //{
                //    arguments.EvaluationContext.Error = GrapeCity.CalcEngine.CalcError.Value;
                //    result.SetValue(GrapeCity.CalcEngine.CalcError.Value);
                //}
                else
                {
                    IEvaluationContext evaluationContext = arguments.EvaluationContext;
                    IReferenceSource referenceSource = range.GetReferenceSource(evaluationContext);
                    //获取第一个参数，这个即是你目前需要的一个范围，调用的时候需要写"$C$4:$C$9"这种
                    RangeReference rangeRef = range.GetReference(evaluationContext, 0);

                    //获取第二个参数
                    double criteria = arguments[1].GetNumber(evaluationContext);
                    string range3 = arguments[2].GetText(evaluationContext);


                    double Amt = 0;
                    for (int r = rangeRef.Row; r <= rangeRef.Row2; r++)
                    {
                        for (int c = rangeRef.Column; c <= rangeRef.Column2; c++)
                        {
                            referenceSource.GetValue(evaluationContext, r, c, result);
                            Amt += result.GetNumber(evaluationContext);
                        }
                    }
                    Amt = Amt / criteria;
                    string Len = "";
                    if (range3.ToString().Contains("."))
                        Len = range3.Substring(range3.IndexOf(".") + 1, range3.Length - range3.IndexOf(".") - 1);
                    string Value = Amt.ToString("F" + Len.Length.ToString());
                    result.SetValue(evaluationContext, Value);


                    //获取范围，循环可以获取单元格的值，进行逻辑运算
                    //for (int c = rangeRef.Column; c <= rangeRef.Column2; c++)
                    //{
                    //    for (int r = rangeRef.Row; r <= rangeRef.Row2; r++)
                    //    {
                    //        referenceSource.GetValue(evaluationContext, r, c, result);
                    //        double cellValue = result.GetNumber(evaluationContext);
                    //        if (cellValue < criteria)
                    //        {
                    //            sum += cellValue;
                    //        }
                    //    }
                    //}
                    //result.SetValue(evaluationContext, sum);
                }
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            fpSpread1.Open(AppDomain.CurrentDomain.BaseDirectory + "1.xml");
        }

        private void fpSpread1_Change(object sender, ChangeEventArgs e)
        {

        }

        private void fpSpread1_EditChange(object sender, EditorNotifyEventArgs e)
        {

        }

        private void button3_Click(object sender, EventArgs e)
        {
            fpSpread1.AllowDragFill = true;
            fpSpreadDesigner.ShowDialog(fpSpread1);
        }
    }
}
