dataprocess
/
DynamicRows.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using FarPoint.Web.Spread;
using FarPoint.Web.Spread.Model;
using FarPoint.CalcEngine;
using System.Data.OleDb;

namespace ControlExplorer
{
    public partial class DynamicRows : System.Web.UI.Page
    {
        #region 页面事件

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                // 模板编号
                string templateid = null;
                templateid = Request.QueryString["tid"];
                hfTemplateID.Value = templateid;

                // 历史编号
                string historyid = null;
                historyid = Request.QueryString["hid"];
                hfHistoryID.Value = historyid;

                // 填报时间
                txtDate.Text = DateTime.Today.ToShortDateString();

                InitializeSheetView();

                // 填充历史数据
                if (!string.IsNullOrWhiteSpace(historyid))
                {
                    InitializeHistoryDetail(historyid);
                }
            }
        }

        #endregion

        #region 菜单操作

        // 新建数据
        protected void btnNew_Click(object sender, EventArgs e)
        {
            hfHistoryID.Value = null;

            UpdateMenuState("New");

            InitializeSheetView();
        }

        // 修改数据
        protected void btnEdit_Click(object sender, EventArgs e)
        {
            UpdateMenuState("Edit");

            fpTemplate.Sheets[0].OperationMode = OperationMode.Normal;
        }

        // 添加新行
        protected void btnAddRow_Click(object sender, EventArgs e)
        {
            InserNewRow();
        }

        // 保存报表
        protected void btnSave_Click(object sender, EventArgs e)
        {

            #region 将用户录入的数据保存到数据库中

            string oldhistoryid = null;
            string historyid = "";

            try
            {
                fpTemplate.SaveChanges();


                oldhistoryid = hfHistoryID.Value;
                historyid = SaveHistory(hfTemplateID.Value);

                SaveData(historyid, hfTemplateID.Value);

                UpdateMenuState("View");

                hfHistoryID.Value = historyid;

                fpTemplate.Sheets[0].OperationMode = OperationMode.SingleSelect;

            }
            catch (ArgumentException ex)
            {
                // 回滚填报记录
                DeleteHistory(hfTemplateID.Value, historyid);

                hfHistoryID.Value = oldhistoryid;

                ScriptManager.RegisterClientScriptBlock(this.UpdatePanel1, this.GetType(), "DataError", "ShowMessage('" + ex.Message + "');", true);

                return;
            }

            ScriptManager.RegisterClientScriptBlock(this.UpdatePanel1, this.GetType(), "Success", "ShowMessage('保存成功！');", true);

            #endregion


        }

        // 获取历史填报数据
        protected void btnHistory_Click(object sender, EventArgs e)
        {
            UpdateMenuState("History");

            InitializeHistoryList();

            #region 获取数据
            string commandstring = string.Format("SELECT TOP 30 * FROM 填报记录 WHERE 模板编号 = '{0}' ORDER BY 填报时间 DESC ", hfTemplateID.Value);
            string connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString;

            using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connectionstring))
            {
                System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(commandstring, connection);
                System.Data.OleDb.OleDbCommandBuilder builder = new System.Data.OleDb.OleDbCommandBuilder(adapter);

                DataTable dt = new DataTable();
                adapter.Fill(dt);

                fpTemplate.Sheets[0].DataSource = dt;

                int columncount = fpTemplate.Sheets[0].ColumnCount - 1;

                for (int i = 0; i < fpTemplate.Sheets[0].RowCount; i++)
                {
                    FarPoint.Web.Spread.ButtonCellType btn = new FarPoint.Web.Spread.ButtonCellType("HistoryDetail");
                    FarPoint.Web.Spread.StyleInfo style = new FarPoint.Web.Spread.StyleInfo();
                    btn.ButtonType = FarPoint.Web.Spread.ButtonType.LinkButton;
                    btn.Text = "查看";
                    btn.CommandName = "HistoryDetail";
                    style.CellType = btn;
                    fpTemplate.Sheets[0].Cells[i, columncount].CellType = btn;
                }
            }

            #endregion
        }

        #endregion

        #region 私有方法

        // 插入新行
        private void InserNewRow()
        {
            int rowcount = 1;
            int toprow = -1;

            DefaultSheetDataModel dtModel = (fpTemplate.Sheets[0].DataModel as DefaultSheetDataModel);

            ExternalRangeExpression expr = dtModel.GetCustomName("DataArea") as ExternalRangeExpression;
            if (expr != null)
            {
                toprow = expr.Row;
            }

            // 计算新行的插入位置
            int tmp = int.Parse(fpTemplate.Sheets[0].Cells[toprow, 0].Text);
            int row = tmp;
            while (int.TryParse(fpTemplate.Sheets[0].Cells[row + toprow - 1, 0].Text, out tmp))
            {
                row += 1;
            }

            row = row + toprow - 1;

            // 添加新行
            fpTemplate.Sheets[0].Rows.Add(row, rowcount);

            // 新行行号
            fpTemplate.Sheets[0].Cells[row, 0].Value = row - toprow + 1;

            for (int col = 0; col < fpTemplate.Sheets[0].ColumnCount; col++)
            {
                // 复制单元格样式，比如：边线、数据格式、演示等
                StyleInfo si = new StyleInfo();
                fpTemplate.Sheets[0].StyleModel.GetCompositeInfo(toprow, col, -1, si);
                fpTemplate.Sheets[0].StyleModel.SetDirectInfo(row, col, si);
            }
        }

        // 初始化模板
        private void InitializeSheetView()
        {
            SheetSkin skin = fpTemplate.Sheets[0].ActiveSkin;
            fpTemplate.Reset();
            fpTemplate.EditModePermanent = true;
            fpTemplate.Sheets.Clear();
            fpTemplate.Sheets.Add(new SheetView());
            fpTemplate.UseClipboard = false;
            fpTemplate.ClientAutoCalculation = true;
            fpTemplate.Open(DataTools.GetTemplateStream(hfTemplateID.Value));
            fpTemplate.Sheets[0].AllowPage = false;
            fpTemplate.Sheets[0].AutoCalculation = true;
            fpTemplate.Sheets[0].Protect = true;

            //// 初始化组合框单元格类型
            //InitializeComboBoxCellType();
            fpTemplate.Sheets[0].ActiveSkin = skin;
            fpTemplate.Sheets[0].LockBackColor = System.Drawing.Color.LightGray;
            lTitle.Text = DataTools.GetTemplate(hfTemplateID.Value)["模板名称"].ToString();
        }

        // 初始化填报历史
        private void InitializeHistoryList()
        {
            Panel1.Visible = false;
            btnSave.Visible = false;
            SheetSkin skin = fpTemplate.Sheets[0].ActiveSkin;

            fpTemplate.Open(Server.MapPath("~\\Templates\\填报记录.xml"));
            fpTemplate.UseClipboard = false;
            fpTemplate.ClientAutoCalculation = true;
            fpTemplate.Sheets[0].AllowPage = true;
            fpTemplate.Sheets[0].PageSize = 20;
            fpTemplate.Sheets[0].AutoCalculation = true;
            fpTemplate.Sheets[0].Protect = true;

            fpTemplate.Sheets[0].ActiveSkin = skin;

            fpTemplate.Sheets[0].AutoGenerateColumns = false;
            fpTemplate.Sheets[0].DataAutoCellTypes = false;

            fpTemplate.Sheets[0].Columns[0].DataField = "记录编号";
            fpTemplate.Sheets[0].Columns[1].DataField = "模板编号";
            fpTemplate.Sheets[0].Columns[2].DataField = "模板名称";
            fpTemplate.Sheets[0].Columns[3].DataField = "部门";
            fpTemplate.Sheets[0].Columns[4].DataField = "填报人";
            fpTemplate.Sheets[0].Columns[5].DataField = "填报时间";
            fpTemplate.Sheets[0].Columns[6].DataField = "修改时间";

            // 添加过滤功能
            fpTemplate.Sheets[0].AutoFilterMode = FarPoint.Web.Spread.AutoFilterMode.Enhanced;
            FarPoint.Web.Spread.IRowFilter rowFilter = new FarPoint.Web.Spread.HideRowFilter(fpTemplate.Sheets[0]);
            FarPoint.Web.Spread.FilterColumnDefinition fd3 = new FarPoint.Web.Spread.FilterColumnDefinition(3, FarPoint.Web.Spread.FilterListBehavior.Default);
            FarPoint.Web.Spread.FilterColumnDefinition fd4 = new FarPoint.Web.Spread.FilterColumnDefinition(4, FarPoint.Web.Spread.FilterListBehavior.Default);
            FarPoint.Web.Spread.FilterColumnDefinition fd5 = new FarPoint.Web.Spread.FilterColumnDefinition(5, FarPoint.Web.Spread.FilterListBehavior.Default);
            FarPoint.Web.Spread.FilterColumnDefinition fd6 = new FarPoint.Web.Spread.FilterColumnDefinition(6, FarPoint.Web.Spread.FilterListBehavior.Default);

            rowFilter.ColumnDefinitions.Add(fd3);
            rowFilter.ColumnDefinitions.Add(fd4);
            rowFilter.ColumnDefinitions.Add(fd5);
            rowFilter.ColumnDefinitions.Add(fd6);

            fpTemplate.Sheets[0].RowFilter = rowFilter;
            fpTemplate.ButtonCommand += new SpreadCommandEventHandler(fpTemplate_ButtonCommand);
        }

        // 加载填报历史数据
        private void InitializeHistoryDetail(string historyid)
        {
            UpdateMenuState("View");
            fpTemplate.Sheets[0].AllowPage = false;
            fpTemplate.Sheets[0].OperationMode = OperationMode.SingleSelect;

            string tablename = DataTools.GetTemplateDataSource(hfTemplateID.Value);

            int toprow = -1;

            DefaultSheetDataModel dtModel = (fpTemplate.Sheets[0].DataModel as DefaultSheetDataModel);
            ExternalRangeExpression expr = dtModel.GetCustomName("DataArea") as ExternalRangeExpression;
            if (expr != null)
            {
                toprow = expr.Row;
            }

            List<string> columns = DataTools.GetTemplateDataFields(hfTemplateID.Value);
            Dictionary<string, int> datafields = new Dictionary<string, int>();

            // 找到全部数据字段对应单元格的所在列索引
            for (int c = 0; c < columns.Count; c++)
            {
                ExternalCellExpression df = fpTemplate.Sheets[0].GetCustomName(columns[c]) as ExternalCellExpression;
                if (df != null)
                {
                    datafields.Add(columns[c], df.Column);
                }
            }

            #region 数据操作

            string commandstring = string.Format("SELECT * FROM [{0}] WHERE 记录编号 = '{1}'", tablename, historyid);
            string connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString;

            using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connectionstring))
            {
                System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(commandstring, connection);
                System.Data.OleDb.OleDbCommandBuilder builder = new System.Data.OleDb.OleDbCommandBuilder(adapter);

                DataTable dt = new DataTable();
                adapter.Fill(dt);

                object value = null;
                int endrow = dt.Rows.Count + toprow;
                // 插入新数据
                for (int r = toprow; r < endrow; r++)
                {
                    DataRow dr = dt.Rows[r - toprow];
                    fpTemplate.Sheets[0].Rows[r].Tag = dr["ID"];
                    foreach (string col in datafields.Keys.ToList<string>())
                    {
                        fpTemplate.Sheets[0].SetValue(r, datafields[col], dr[col]);
                    }

                    if (toprow < endrow - 1)
                    {
                        InserNewRow();
                    }
                }
            }

            #endregion
        }

        // 删除模板填报历史记录
        private void DeleteHistory(string templateid, string historyid)
        {
            #region 数据操作

            string tablename = DataTools.GetTemplateDataSource(templateid);
            string commandstring = "DELETE FROM 填报记录 WHERE 记录编号 = '" + historyid + "'";
            string connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString;

            using (OleDbConnection connection = new OleDbConnection(connectionstring))
            {
                OleDbCommand cmd = new OleDbCommand(commandstring, connection);
                connection.Open();
                cmd.ExecuteNonQuery();

                commandstring = string.Format("DELETE FROM {0} WHERE 记录编号 = '{1}' ", tablename, historyid);
                cmd = new OleDbCommand(commandstring, connection);
                cmd.ExecuteNonQuery();

                connection.Close();
            }

            #endregion

        }

        // 保存模板填报历史记录
        private string SaveHistory(string templateid)
        {
            string hisoryid = (string.IsNullOrWhiteSpace(hfHistoryID.Value) ? Guid.NewGuid().ToString() : hfHistoryID.Value);

            #region 数据操作

            string commandstring = "SELECT TOP 50 * FROM 填报记录 WHERE 记录编号 = '" + hisoryid + "'";
            string connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString;

            using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connectionstring))
            {
                System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(commandstring, connection);
                System.Data.OleDb.OleDbCommandBuilder builder = new System.Data.OleDb.OleDbCommandBuilder(adapter);

                DataTable dt = new DataTable();
                adapter.Fill(dt);

                DataRow dr = (string.IsNullOrWhiteSpace(hfHistoryID.Value) ? dt.NewRow() : dt.Rows[0]);

                if (string.IsNullOrWhiteSpace(hfHistoryID.Value))
                {
                    // 新建数据
                    dr["记录编号"] = hisoryid;
                    dr["模板编号"] = templateid;
                    dr["部门"] = lstCategoryID.SelectedItem.Text;
                    dr["填报人"] = txtName.Text;
                    dr["填报时间"] = DateTime.Now;
                    dr["修改时间"] = DateTime.Now;

                    dt.Rows.Add(dr);
                }
                else
                {
                    // 历史数据
                    dr["修改时间"] = DateTime.Now;
                }

                adapter.Update(dt);
            }

            #endregion

            return hisoryid;
        }

        // 保存数据
        private void SaveData(string historyid, string templateid)
        {
            // 计算新行的插入位置
            int toprow = -1;
            int tmp = 1;
            int row = tmp;

            DefaultSheetDataModel dtModel = (fpTemplate.Sheets[0].DataModel as DefaultSheetDataModel);
            ExternalRangeExpression expr = dtModel.GetCustomName("DataArea") as ExternalRangeExpression;
            if (expr != null)
            {
                toprow = expr.Row;
            }

            while (int.TryParse(fpTemplate.Sheets[0].Cells[row + toprow - 1, 0].Text, out tmp))
            {
                row += 1;
            }

            row = row + toprow - 1;

            #region 将用户录入的数据保存到数据库中

            List<string> columns = DataTools.GetTemplateDataFields(hfTemplateID.Value);
            Dictionary<string, int> datafields = new Dictionary<string, int>();

            // 找到全部数据字段对应单元格的所在列索引
            for (int c = 0; c < columns.Count; c++)
            {
                ExternalCellExpression df = fpTemplate.Sheets[0].GetCustomName(columns[c]) as ExternalCellExpression;
                if (df != null)
                {
                    datafields.Add(columns[c], df.Column);
                }
            }

            string tablename = DataTools.GetTemplateDataSource(templateid);
            string commandstring = "SELECT * FROM " + tablename;
            string connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString;

            using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connectionstring))
            {
                System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(commandstring, connection);
                System.Data.OleDb.OleDbCommandBuilder builder = new System.Data.OleDb.OleDbCommandBuilder(adapter);

                DataTable dt = new DataTable();
                adapter.Fill(dt);

                object value = null;

                // 插入新数据
                for (int r = toprow; r < row; r++)
                {
                    if (fpTemplate.Sheets[0].Cells[r, 1].Value == null)
                        break;

                    DataRow dr = (string.IsNullOrWhiteSpace(hfHistoryID.Value) ? dt.NewRow() : dt.Rows[0]);

                    foreach (string col in datafields.Keys.ToList<string>())
                    {
                        value = fpTemplate.Sheets[0].GetValue(r, datafields[col]);
                        if (value != null)
                        {
                            dr[col] = value;
                        }
                    }

                    // 新建数据
                    if (string.IsNullOrWhiteSpace(hfHistoryID.Value))
                    {
                        dr["记录编号"] = historyid;
                        dt.Rows.Add(dr);
                    }
                }

                adapter.Update(dt);
            }

            #endregion
        }

        // 更新菜单状态
        private void UpdateMenuState(string action)
        {
            switch (action)
            {
                case "New":
                    Panel1.Visible = true;
                    btnNew.Visible = true;
                    btnEdit.Visible = false;
                    btnSave.Visible = true;
                    btnHistory.Visible = true;
                    break;
                case "Edit":
                    Panel1.Visible = false;
                    btnNew.Visible = true;
                    btnEdit.Visible = false;
                    btnSave.Visible = true;
                    btnHistory.Visible = true;
                    break;
                case "View":
                    Panel1.Visible = false;
                    btnNew.Visible = true;
                    btnEdit.Visible = false;
                    btnSave.Visible = false;
                    btnHistory.Visible = true;
                    break;
                case "History":
                    Panel1.Visible = false;
                    btnNew.Visible = true;
                    btnEdit.Visible = false;
                    btnSave.Visible = false;
                    btnHistory.Visible = false;
                    break;
                default:
                    break;
            }
        }

        #endregion

        #region Spread 操作

        protected void fpTemplate_ButtonCommand(object sender, SpreadCommandEventArgs e)
        {
            fpTemplate.SaveChanges();
            if (e.CommandName == "HistoryDetail")
            {
                string historyid = fpTemplate.Sheets[0].Cells[fpTemplate.Sheets[0].ActiveRow, 0].Text;

                hfHistoryID.Value = historyid;

                InitializeSheetView();

                if (!string.IsNullOrWhiteSpace(historyid))
                {
                    InitializeHistoryDetail(historyid);
                }
            }
        }

        #endregion
    }
}