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 FarPoint.Win.Spread;
using FarPoint.Win.Spread.Data;

namespace WindowsFormsApp1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            SetDataTable();
            fpSpread1.ActiveSheet.ColumnCount = 100;
            fpSpread1.ActiveSheet.Rows[2, fpSpread1.ActiveSheet.Rows.Count - 1].Visible = false;
            fpSpread1.ActiveSheet.Columns[10, fpSpread1.ActiveSheet.Columns.Count - 1].Visible = false;
            //fpSpread1.ActiveSheet.RowCount = 5;
        }
        DataTable dt = new DataTable();

        DataTable dt2 = new DataTable();
        DataTable dt3 = new DataTable();

        private void SetDataTable()
        {
            dt = new DataTable();
            dt.Columns.Add("true");
            dt.Columns.Add("记录模板编号");
            dt.Columns.Add("证书模板编号");
            dt.Columns.Add("记录编号");
            dt.Columns.Add("证书编号");
            dt.Columns.Add("委托单位");
            dt.Columns.Add("证书类型");
            dt.Columns.Add("证书单位");
            dt.Columns.Add("样品名称");
            dt.Columns.Add("规格型号");
            dt.Columns.Add("出厂编号");
            dt.Columns.Add("制造厂家");

            dt2 = dt.Clone();
            dt3 = dt.Clone();

            dt2.Columns.Add("结论");
            dt2.Columns.Add("温度");
            dt2.Columns.Add("湿度");
            dt2.Columns.Add("其它");

            for(int i=1;i<=10;i++)
            {
                dt.Rows.Add(true, 1, 1, "202340000"+i.ToString(), "", "测试单位" + i.ToString(), "检定证书", "测试单位" + i.ToString(), "压力表", "A-0" + i.ToString());
                dt2.Rows.Add(true, 2, 1, "202350000" + i.ToString(), "", "测试单位" + i.ToString(), "测试证书", "测试单位" + i.ToString(), "电子台秤" + i.ToString(), "C-00" + i.ToString(), "0982", "中国", "合格", "23", "88", "样品完好");
            }
            ////dt.Rows.Add("记录模板", "证书模板", "选择", "序号", "记录编号", "证书编号", "委托单位", "证书类型", "证书单位", "样品名称", "规格型号", "出厂编号", "制造厂家");
            //dt.Rows.Add(true, 1, 1, "2023400001", "", "测试单位", "检定证书", "测试单位", "压力表", "A-01");
            //dt.Rows.Add(true, 1, 1, "2023400002", "", "测试单位", "校准证书", "测试单位", "电子台秤", "B-002", "006");
            //dt.Rows.Add(true, 1, 1, "2023400003", "", "测试单位", "测试证书", "测试单位", "真空压力表", "C-003", "0982", "中泵量仪厂");
            //dt.Rows.Add(false, 1, 1, "2023400004", "", "测试单位", "测试证书", "测试单位", "真空压力表", "C-003", "0982", "中泵量仪厂");
            //dt.Rows.Add(false, 1, 1, "2023400005", "", "测试单位", "测试证书", "测试单位", "真空压力表", "C-003", "0982", "中泵量仪厂");
            //dt.Rows.Add(false, 1, 1, "2023400006", "", "测试单位", "测试证书", "测试单位", "真空压力表", "C-003", "0982", "中泵量仪厂");
            ////dt.Rows.Add(7, 6, false, "3", "2023400003", "", "测试单位", "测试证书", "测试单位", "真空压力表", "C-003", "0982", "中泵量仪厂");

            ////dt2.Rows.Add("记录模板", "证书模板", "选择", "序号", "记录编号", "证书编号", "委托单位", "证书类型", "证书单位", "样品名称", "规格型号", "出厂编号", "制造厂家");
            //dt2.Rows.Add(true, 2, 1, "2023400001", "", "测试单位", "检定证书", "测试单位", "压力表", "A-01");
            //dt2.Rows.Add(false, 2, 1, "2023400002", "", "测试单位", "校准证书", "测试单位", "电子台秤", "B-002", "006");
            //dt2.Rows.Add(false, 2, 1, "2023400003", "", "测试单位", "测试证书", "测试单位2", "真空压力表1", "C-003", "0982", "1", "合格");
            //dt2.Rows.Add(false, 2, 1, "2023400004", "", "测试单位", "测试证书", "测试单位2", "真空压力表2", "C-003", "0982", "1", "合格", "23");
            //dt2.Rows.Add(true, 2, 1, "2023400005", "", "测试单位", "测试证书", "测试单位2", "真空压力表3", "C-003", "0982", "1", "合格", "23", "88");
            //dt2.Rows.Add(true, 2, 1, "2023400006", "", "测试单位", "测试证书", "测试单位2", "真空压力表5", "C-003", "0982", "1", "合格", "23", "88", "样品完好");
            ////dt2.Rows.Add(3, 6, false, "3", "2023400003", "", "测试单位", "测试证书", "测试单位2", "真空压力表", "C-003", "0982", "2");
            ////dt2.Rows.Add(3, 6, false, "3", "2023400003", "", "测试单位", "测试证书", "测试单位2", "真空压力表", "C-003", "0982", "3");
            ////dt2.Rows.Add(3, 6, false, "3", "2023400003", "", "测试单位", "测试证书", "测试单位2", "真空压力表", "C-003", "0982", "4");
            ////dt2.Rows.Add(3, 6, false, "3", "2023400003", "", "测试单位", "测试证书", "测试单位2", "真空压力表", "C-003", "0982", "5");


        }

        private void btnSetDataTable_Click(object sender, EventArgs e)
        {
            //fpSpread1.Sheets[0] = new SheetView();
            GrapeCity.Spreadsheet.IWorksheet TestActiveSheet = fpSpread1.AsWorkbook().ActiveSheet;
            TestActiveSheet.AutoFilterMode = false;

            GrapeCity.Spreadsheet.ITable table = TestActiveSheet.Range("A1:G2").CreateTable(true);
            table.ShowAutoFilter = false;
            table.AutoGenerateColumns = true;
            table.DataSource = dt;
            fpSpread1.ActiveSheet.Columns[0, dt.Columns.Count - 1].Visible = true;
            //GrapeCity.Spreadsheet.IRange range = TestActiveSheet.UsedRange;
            //string Range = range.CurrentRegion.ToString();
            //TestActiveSheet.UsedRange.CurrentRegion.ToString();
            string nRange = "A" + (dt.Rows.Count + 2) + ":G" + (dt2.Columns.Count + 1);

            //TestActiveSheet.Range(nRange)中nRange的获取需要更便捷、准确的方式。
            //GrapeCity.Spreadsheet.ITable table2 = TestActiveSheet.Range(TestActiveSheet.UsedRange.CurrentRegion.ToString()).CreateTable(true);
            GrapeCity.Spreadsheet.ITable table2 = TestActiveSheet.Range(nRange).CreateTable(true);
            table2.AutoGenerateColumns = true;
            table2.ShowAutoFilter = false;
            table2.DataSource = dt2;
            fpSpread1.ActiveSheet.Rows[0, dt.Rows.Count + dt2.Rows.Count + 1].Visible = true;
            if (dt2.Columns.Count > dt.Columns.Count)
                fpSpread1.ActiveSheet.Columns[0, dt2.Columns.Count - 1].Visible = true;

            //这三种方式设置无效果，请问应该如何设置可以让行和列不可编辑，以及设置列为冻结列，移动横向滚动条时，冻结列不可移动
            fpSpread1.ActiveSheet.Rows[2].Locked = true;
            fpSpread1.ActiveSheet.Columns[2].Locked = true;
            TestActiveSheet.Range(nRange).Locked = true;

            fpSpread1.ActiveSheet.FrozenColumnCount = 3;
            //fpSpread1.ActiveSheet.Protect = true;

        }

        private void btnSetDataTable2_Click(object sender, EventArgs e)
        {
            fpSpread1.Sheets[0] = new SheetView();
            //dt.Columns.Add("true");
            //dt.Columns.Add("记录模板编号");
            //dt.Columns.Add("证书模板编号");
            //dt.Columns.Add("记录编号");
            //dt.Columns.Add("证书编号");
            //dt.Columns.Add("委托单位");
            //dt.Columns.Add("证书类型");
            //dt.Columns.Add("证书单位");
            //dt.Columns.Add("样品名称");
            //dt.Columns.Add("规格型号");
            //dt.Columns.Add("出厂编号");
            //dt.Columns.Add("制造厂家");

            //将 Spread 绑定到外部数据集：Binding Spread to an External Data Set
            fpSpread1.Sheets[0].AutoGenerateColumns = false;
            fpSpread1.Sheets[0].DataSource = dt;
            fpSpread1.Sheets[0].ColumnCount = dt.Columns.Count;
            for (int i = 0; i < dt.Columns.Count; i++)
                fpSpread1.Sheets[0].BindDataColumn(i, dt.Columns[i].ColumnName);
            fpSpread1.Sheets[0].Rows[0, dt.Rows.Count - 1].Visible = true;
            fpSpread1.Sheets[0].Columns[0, dt.Columns.Count - 1].Visible = true;
            fpSpread1.ActiveSheet.Protect = false;

            fpSpread1.Sheets[0].ColumnHeader.Cells[0, 0].Text = "学号";//设置自定义列名

            //fpSpread1.Sheets[0].BindDataColumn(0, "true");
            //fpSpread1.Sheets[0].BindDataColumn(1, "记录模板编号");
            //fpSpread1.Sheets[0].BindDataColumn(2, "证书模板编号");
            //fpSpread1.Sheets[0].BindDataColumn(3, "记录编号");
            //fpSpread1.Sheets[0].BindDataColumn(4, "证书编号");
            //fpSpread1.Sheets[0].BindDataColumn(5, "委托单位");
            //fpSpread1.Sheets[0].BindDataColumn(5, "证书类型");
            //fpSpread1.Sheets[0].BindDataColumn(5, "证书单位");
            //fpSpread1.Sheets[0].BindDataColumn(5, "样品名称");
            //fpSpread1.Sheets[0].BindDataColumn(5, "规格型号");
            //fpSpread1.Sheets[0].BindDataColumn(5, "出厂编号");
            //fpSpread1.Sheets[0].BindDataColumn(5, "制造厂家");


            //FarPoint.Win.Spread.Data.SpreadDataBindingAdapter data = new FarPoint.Win.Spread.Data.SpreadDataBindingAdapter();
            //// Assign the datasource to a data table
            //data.DataSource = dt;
            //data.Spread = fpSpread1;
            //data.SheetName = fpSpread1.Sheets[0].SheetName;
            //data.MapperInfo = new FarPoint.Win.Spread.Data.MapperInfo(3, 2, 1, 1);
            //data.FillSpreadDataByDataSource();
            //fpSpread1.Sheets[0].Rows[0, 100].Visible = true;
            //fpSpread1.Sheets[0].Columns[0, dt.Columns.Count-1].Visible = true;
        }


        string CertifPath = AppDomain.CurrentDomain.BaseDirectory + "12.xml";
        private void btnSave_Click(object sender, EventArgs e)
        {
            fpSpread1.Save(CertifPath.ToString(), false);
        }

        private void btnOpen_Click(object sender, EventArgs e)
        {
            fpSpread1.Open(CertifPath);

        }

        private void btnGetDataTable_Click(object sender, EventArgs e)
        {
            //打开本地的xml文件如何获取fpSpread1中的多表数据（需要包含列名称）,转为DataTable
            for (int i = 0; i < fpSpread1.AsWorkbook().ActiveSheet.Tables.Count; i++)
            {
                var tb = fpSpread1.AsWorkbook().ActiveSheet.Tables[i];
                //tb.DataBodyRange;
                //tb.Range
            }

            //GrapeCity.Spreadsheet.IWorksheet TestActiveSheet = fpSpread1.AsWorkbook().ActiveSheet;

            //DataView dv = fpSpread1.Sheets[0].GetDataView(true);
        }

        private void btnSort_Click(object sender, EventArgs e)
        {
            //如何对打开xml文件后，进行排序

            for (int i = 0; i < fpSpread1.AsWorkbook().ActiveSheet.Tables.Count; i++)
            {
                var tb = fpSpread1.AsWorkbook().ActiveSheet.Tables[i];
                //tb.Sort = 1;
                //tb.Sort("A");               
            }




            //FarPoint.Win.Spread.TableStyle tstyle = fpSpread1.CreateTableStyle("Style1", FarPoint.Win.Spread.TableStyle.TableStyleLight2);
            //fpSpread1.Sheets[0].Cells[1, 1].Text = "Last Name";
            //fpSpread1.Sheets[0].Cells[1, 2].Text = "Value";
            //fpSpread1.Sheets[0].Cells[2, 1].Text = "Smith";
            //fpSpread1.Sheets[0].Cells[2, 2].Value = 50;
            //fpSpread1.Sheets[0].Cells[3, 1].Text = "Vil";
            //fpSpread1.Sheets[0].Cells[3, 2].Value = 10;
            //fpSpread1.Sheets[0].Cells[4, 1].Text = "Press";
            //fpSpread1.Sheets[0].Cells[4, 2].Value = 78;
            //fpSpread1.TableStyleCollection.Add(tstyle);
            //FarPoint.Win.Spread.TableView table = fpSpread1.Sheets[0].AddTable("table", 1, 1, 5, 2, "Style1");
            //FarPoint.Win.Spread.ComplexSortInfo[] sort = new FarPoint.Win.Spread.ComplexSortInfo[1];
            //sort[0] = new FarPoint.Win.Spread.ComplexSortInfo(1, true);
            //table.Sort(sort);
        }

        private void btnAddDataRow_Click(object sender, EventArgs e)
        {
            DataRow NewRow = dt.NewRow();
            NewRow["true"] = true;
            NewRow["记录模板编号"] = "1";
            NewRow["证书模板编号"] = "1";
            NewRow["记录编号"] = "2023400007";
            NewRow["证书编号"] = "";
            NewRow["委托单位"] = "测试7";
            NewRow["证书类型"] = "校准证书";
            NewRow["证书单位"] = "测试7";
            NewRow["样品名称"] = "电子台秤";
            NewRow["规格型号"] = "001";
            NewRow["出厂编号"] = "002";
            NewRow["制造厂家"] = "中星";
            dt.Rows.Add(NewRow);
            fpSpread1.Sheets[0].AddUnboundRows(dt.Rows.Count, 1);

            NewRow = dt2.NewRow();
            NewRow["true"] = true;
            NewRow["记录模板编号"] = "1";
            NewRow["证书模板编号"] = "1";
            NewRow["记录编号"] = "2023400008";
            NewRow["证书编号"] = "";
            NewRow["委托单位"] = "测试8";
            NewRow["证书类型"] = "校准证书";
            NewRow["证书单位"] = "测试8";
            NewRow["样品名称"] = "电子台秤8";
            NewRow["规格型号"] = "008";
            NewRow["出厂编号"] = "008";
            NewRow["制造厂家"] = "中星8";
            dt2.Rows.Add(NewRow);
            //fpSpread1.Sheets[0].AddUnboundRows(dt2.Rows.Count, 1);
            fpSpread1.ActiveSheet.Rows[0, dt.Rows.Count + dt2.Rows.Count].Visible = true;
            //btnSetDataTable_Click(null,null);
        }

        private void btnCopySpread_Click(object sender, EventArgs e)
        {
            FarPoint.Win.Spread.FpSpread fpSpread2 = new FarPoint.Win.Spread.FpSpread();
            fpSpread2.AddNewSheetView(0);
            this.Controls.Add(fpSpread2);
            fpSpread2.Dock = System.Windows.Forms.DockStyle.Top;
            fpSpread1.BringToFront();
        }

        private void Get()
        {
            //// Assign new formatter
            //DataSet dataSet = BuildDataSet(5, 5);
            //this.spreadDataBindingAdapter1.Spread = this.fpSpread1;
            //this.spreadDataBindingAdapter1.SheetName = this.fpSpread1.ActiveSheet.SheetName;
            //this.spreadDataBindingAdapter1.DataSource = dataSet.Tables[0];
            //spreadDataBindingAdapter1.MapperInfo = new MapperInfo(1, 2, 3, 4);
            //MySpreadDataViewDataFormatter testFormatter = new MySpreadDataViewDataFormatter
            //(this.spreadDataBindingAdapter1.SpreadDataView.Columns[2], fpSpread1.ActiveSheet);
            //this.spreadDataBindingAdapter1.SpreadDataView.Columns[2].Formatter = testFormatter;
            //this.spreadDataBindingAdapter1.FillSpreadDataByDataSource();
        }
    }









    public class MySpreadDataViewDataFormatter : ISpreadDataViewDataFormatter
    {

        private SpreadDataColumn column;
        private SheetView sheetView;
        public SheetView SheetView
        {
            get { return sheetView; }
            set { sheetView = value; }
        }

        public MySpreadDataViewDataFormatter(SpreadDataColumn ownerColumn, SheetView sheetView)
        {
            if (ownerColumn == null)
            {
                throw new ArgumentNullException("ownerColumn");
            }
            column = ownerColumn;
            this.SheetView = sheetView;
        }
        public object GetCellValue(Cell cell)
        {
            object ret = null;
            try
            {
                ret = this.SheetView.GetValue(cell.Row.Index, cell.Column.Index);
                ret += ": Customized format";
            }
            catch
            {
                ret = " No value";
            }
            return ret;
        }
        public void SetCellValue(Cell cell, object value)
        {
            this.SheetView.SetValue(cell.Row.Index, cell.Column.Index, value + ": Customized format");
        }



    }
}