frmCircularSankey.aspx.cs

using System;
using System.Data;
using System.IO;
using System.Text.RegularExpressions;
using System.Web.UI;
using IEF_Visualization.cls;
using Microsoft.Office.Interop.Excel;
using ClosedXML.Excel;
using System.Collections;

namespace IEF_Visualization
{
    public partial class frmCircularSankey : System.Web.UI.Page
    {
        clsDataHandling objDataHandling = new clsDataHandling();
        protected void Page_Load(object sender, EventArgs e)
        {

            string user_name = Session["USER_NAME"] as string;
            //string user_name = "stefan21";
            objDataHandling.user_name = user_name;

            string D = Request.QueryString["D"];
            string d_year = Request.QueryString["year"];
            string d_country = Request.QueryString["country"];

            if (D != null)
            {
                objDataHandling.setNodeAndFlowValues(input_node_data, input_flow_data, d_year, d_country);
            }

            if ((user_name == null || user_name == "") && D == null)
            {
                Response.Redirect("frmHome.aspx", false);
            }
            else
            {
                lblUser.Text = Session["USER_FULL_NAME"] as string;

                //lbl_hi_user.Text = user_name;
            }

            if (!Page.IsPostBack)
            {
                string job_id = Request.QueryString["job_id"];

                if (job_id != null)
                {
                    //ArrayList controllersID = new ArrayList();
                    //getControllersID(controllersID);
                    ArrayList controllers_data = objDataHandling.getData(job_id);
                    setControllersData(controllers_data);
                    btnSave.Text = "Update Data";
                    btnSaveAs.Visible = true;
                }
                

            }
        }

        protected void btnSave_Click(object sender, EventArgs e)
        {
            string user_name = Session["USER_NAME"] as string;
            objDataHandling.user_name = Session["USER_NAME"] as string;
            objDataHandling.flow_data = Convert.ToString(input_flow_data.InnerText);
            objDataHandling.remarks = Convert.ToString(txtRemarks.Text);
            objDataHandling.project_name = Convert.ToString(txtproject_name.Text);
            objDataHandling.type = "Sankey";

            objDataHandling.node_data = Convert.ToString(input_node_data.InnerText);

            objDataHandling.default_node_color = "";
            objDataHandling.default_flow_color = "";
            objDataHandling.background_color = Convert.ToString(background_color.Text); // "#ffffff";
            objDataHandling.drag_option = show_labels.Checked;
            objDataHandling.curviness = Convert.ToDouble(curvature.Value); // 0.5;
            objDataHandling.diagram_width = Convert.ToInt32(canvas_width.Text); //  900;
            objDataHandling.diagram_height = Convert.ToInt32(canvas_height.Text); //  900;
            objDataHandling.flow_opacity = Convert.ToDouble(default_flow_opacity.Value); //  0.9;
            objDataHandling.node_opacity = Convert.ToDouble(default_node_opacity.Value); //  0.9;
            objDataHandling.border = Convert.ToInt32(node_border.SelectedIndex); //  1;
            objDataHandling.level_visibility = show_labels.Checked;
            objDataHandling.font_face = Convert.ToString(font_face.SelectedIndex);
            objDataHandling.font_size = Convert.ToInt32(font_size.Text); //  12;
            objDataHandling.font_color = Convert.ToString(font_color.Text); //  "#add8e6";
            objDataHandling.unit_name = Convert.ToString(unit_name.Text);
            objDataHandling.flow_width= Convert.ToDouble(flow_width.Value);
            objDataHandling.node_height= Convert.ToDouble(node_height.Value);

            string job_id = Request.QueryString["job_id"];
            if (job_id != null)
            {
                objDataHandling.UpdateData(job_id);

                string script = "alert('" + "Data has been updated successfully" + "');";
                ScriptManager.RegisterStartupScript(this, typeof(System.Web.UI.Page), "UserSecurity", script, true);
            }
            else
            {
                job_id = objDataHandling.createJobID(user_name);
                objDataHandling.SaveData(job_id);
                string script = "alert('" + "Data has been saved successfully" + "');";
                ScriptManager.RegisterStartupScript(this, typeof(System.Web.UI.Page), "UserSecurity", script, true);

            }
        }


        protected void btnDownloadData_Click(object sender, EventArgs e)
        {
            DataSet ds = getDataSetExportToExcel();

            //exportToExcel(ds);

            using (XLWorkbook wb = new XLWorkbook())
            {
                foreach (System.Data.DataTable dt in ds.Tables)
                {
                    wb.Worksheets.Add(dt);
                }

                //Export the Excel file.
                Response.Clear();
                Response.Buffer = true;
                Response.Charset = "";
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AddHeader("content-disposition", "attachment;filename=DataSet.xlsx");
                using (MemoryStream MyMemoryStream = new MemoryStream())
                {
                    wb.SaveAs(MyMemoryStream);
                    MyMemoryStream.WriteTo(Response.OutputStream);
                    Response.Flush();
                    Response.End();
                }
            }
        }

        public System.Data.DataTable sampleData(System.Data.DataTable dt)
        {
            DataColumn workCol = dt.Columns.Add("Source", typeof(String));

            dt.Columns.Add("value", typeof(String));
            dt.Columns.Add("target", typeof(String));

            //Add in the datarow
            DataRow newRow = dt.NewRow();

            newRow["Source"] = "A";
            newRow["value"] = "2";
            newRow["target"] = "B";

            dt.Rows.Add(newRow);

            return dt;
        }

        public System.Data.DataTable parsedData(System.Data.DataTable dt, string node_data, string[] colNames)
        {
            //System.Data.DataTable dt = new System.Data.DataTable(tableName);
            string[] lineData = Regex.Split(node_data, "[\r\n]+");
            string pattern = @"\[([^\]]*)\]";

            for (int i = 0; i < colNames.Length; i++)
            {
                dt.Columns.Add(new DataColumn(colNames[i]));
            }

            for (var f = 0; f < lineData.Length; f++)
            {
                MatchCollection matches = Regex.Matches(lineData[f], pattern);
                var row = dt.Rows.Add();

                for (var c = 0; c < matches.Count; c++)
                {
                    row[colNames[c]] = matches[c].Groups[0].Value.Substring(1, matches[c].Groups[0].Value.Length - 2);

                }
            }

            return dt;
        }


        public void CreateCSVFile(System.Data.DataTable dt)
        {
            string attachment = "attachment; filename=city.xls";
            Response.ClearContent();
            Response.AddHeader("content-disposition", attachment);
            Response.ContentType = "application/vnd.ms-excel";
            string tab = "";
            foreach (DataColumn dc in dt.Columns)
            {
                Response.Write(tab + dc.ColumnName);
                tab = "\t";
            }
            Response.Write("\n");
            int i;
            foreach (DataRow dr in dt.Rows)
            {
                tab = "";
                for (i = 0; i < dt.Columns.Count; i++)
                {
                    Response.Write(tab + dr[i].ToString());
                    tab = "\t";
                }
                Response.Write("\n");
            }
            Response.End();
        }

        public DataSet getDataSetExportToExcel()
        {
            DataSet ds = new DataSet();

            string[] colNamesFlow = { "Source", "Value", "Color", "Flow Style", "Target" };
            string[] colNamesNodes = { "Name", "Color", "Orientation", "Width", "Height", "X_position", "Y_position" };

            string flow_data = Convert.ToString(input_flow_data.InnerText);
            string node_data = Convert.ToString(input_node_data.InnerText);

            System.Data.DataTable dt_flow = new System.Data.DataTable("flow_data");
            System.Data.DataTable dt_node = new System.Data.DataTable("node_data");

            //dt_s = sampleData(dt_s);

            dt_flow = parsedData(dt_flow, flow_data, colNamesFlow);
            dt_node = parsedData(dt_node, node_data, colNamesNodes);

            ds.Tables.Add(dt_flow);
            ds.Tables.Add(dt_node);

            return ds;
        }

        protected void btnSaveAs_Click(object sender, EventArgs e)
        {
            string user_name = Session["USER_NAME"] as string;
            //string user_name = "stefan01";
            objDataHandling.user_name = user_name;
            objDataHandling.flow_data = Convert.ToString(input_flow_data.InnerText);
            objDataHandling.remarks = Convert.ToString(txtRemarks.Text);
            objDataHandling.project_name = Convert.ToString(txtproject_name.Text);
            objDataHandling.type = "Sankey";
            objDataHandling.node_data = Convert.ToString(input_node_data.InnerText);

            objDataHandling.default_node_color = "";
            objDataHandling.default_flow_color = "";
            objDataHandling.background_color = Convert.ToString(background_color.Text); // "#ffffff";
            //objDataHandling.drag_option = enable_drag.Checked;
            objDataHandling.curviness = Convert.ToDouble(curvature.Value); // 0.5;
            objDataHandling.diagram_width = Convert.ToInt32(canvas_width.Text); //  900;
            objDataHandling.diagram_height = Convert.ToInt32(canvas_height.Text); //  900;
            objDataHandling.flow_opacity = Convert.ToDouble(default_flow_opacity.Value); //  0.9;
            objDataHandling.node_opacity = Convert.ToDouble(default_node_opacity.Value); //  0.9;
            objDataHandling.border = Convert.ToInt32(node_border.SelectedIndex); //  1;
            objDataHandling.level_visibility = show_labels.Checked;
            objDataHandling.font_face = Convert.ToString(font_face.SelectedIndex);
            objDataHandling.font_size = Convert.ToInt32(font_size.Text); //  12;
            objDataHandling.font_color = Convert.ToString(font_color.Text); //  "#add8e6";
            objDataHandling.unit_name = Convert.ToString(unit_name.Text);
            objDataHandling.flow_width = Convert.ToDouble(flow_width.Value);
            objDataHandling.node_height = Convert.ToDouble(node_height.Value);

            string job_id = objDataHandling.createJobID(user_name);
            objDataHandling.SaveData(job_id);
            string script = "alert('" + "Data has been saved successfully" + "');";
            ScriptManager.RegisterStartupScript(this, typeof(System.Web.UI.Page), "UserSecurity", script, true);

        }

        private ArrayList getControllersID(ArrayList controllersID)
        {
            controllersID.Add("input_flow_data");
            controllersID.Add("txtRemarks");
            controllersID.Add("txtproject_name");
            controllersID.Add("input_node_data");
            controllersID.Add("background_color");
            controllersID.Add("enable_drag");
            controllersID.Add("curvature");
            controllersID.Add("canvas_width");
            controllersID.Add("canvas_height");
            controllersID.Add("default_flow_opacity");
            controllersID.Add("default_node_opacity");
            controllersID.Add("node_border");
            controllersID.Add("show_labels");
            controllersID.Add("font_face");
            controllersID.Add("font_size");
            controllersID.Add("font_color");
            controllersID.Add("flow_width");
            controllersID.Add("node_height");

            return controllersID;
        }

        private void setControllersData(ArrayList controllers_data)
        {
            string query = "select distinct dm.job_id, dm.project_name, dm.remarks, rd.node_data, rd.default_node_color, rd.flow_data, rd.default_flow_color, ";
            query += "md.background_color, md.border, md.curviness, md.diagram_height, md.diagram_width, md.drag_option, md.flow_opacity, md.node_opacity, ";
            query += "md.font_color, md.font_face, md.font_size, md.level_visibility from raw_data rd, data_master dm, meta_data md ";

            input_flow_data.InnerText = Convert.ToString(controllers_data[5]);
            txtRemarks.Text = Convert.ToString(controllers_data[2]);
            txtproject_name.Text = Convert.ToString(controllers_data[1]);
            input_node_data.InnerText = Convert.ToString(controllers_data[3]);
            background_color.Text = Convert.ToString(controllers_data[7]);
            //enable_drag.Checked = Convert.ToBoolean(controllers_data[12]);
            curvature.Attributes["value"] = Convert.ToString(controllers_data[9]);
            canvas_width.Text = Convert.ToString(controllers_data[11]);
            canvas_height.Text = Convert.ToString(controllers_data[10]);
            default_flow_opacity.Attributes["value"] = Convert.ToString(controllers_data[13]);
            default_node_opacity.Attributes["value"] = Convert.ToString(controllers_data[14]);
            node_border.SelectedIndex = Convert.ToInt16(controllers_data[8]);
            //show_labels.Checked = Convert.ToBoolean(controllers_data[18]);
            //font_face.SelectedIndex = Convert.ToIndex(controllers_data[16])
            font_size.Text = Convert.ToString(controllers_data[17]);
            font_color.Text = Convert.ToString(controllers_data[15]);
            unit_name.Text = Convert.ToString(controllers_data[19]);
            flow_width.Attributes["value"] = Convert.ToString(controllers_data[20]);
            node_height.Attributes["value"] = Convert.ToString(controllers_data[21]);
        }

        protected void btnLoggOut_Click(object sender, EventArgs e)
        {
            Session.RemoveAll();
            Response.Redirect("frmHome.aspx", false);
        }

        protected void btnNodeFile_Click(object sender, EventArgs e)
        {
            //if File is not selected then return  
            if (Request.Files["fileNode"].ContentLength <= 0)
            {
                return;
            }

            //Get the file extension  
            string fileExtension = Path.GetExtension(Request.Files["fileNode"].FileName);

            //If file is not in excel format then return  
            if (fileExtension != ".xls" && fileExtension != ".xlsx")
            {
                return;
            }

            //Get the File name and create new path to save it on server  
            string fileLocation = Server.MapPath("\\") + Request.Files["fileNode"].FileName;

            //if the File is exist on serevr then delete it  
            if (File.Exists(fileLocation))
            {
                File.Delete(fileLocation);
            }
            //save the file lon the server before loading  
            Request.Files["fileNode"].SaveAs(fileLocation);

            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(fileLocation);
            string data = "";
            int sheetcount = 0;
            
            foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in xlWorkBook.Worksheets)
            {
                data = "";
                int rowcount = 0;
                //foreach (Excel.Range row in sheet.UsedRange.Rows)
                //{
                //    data += row.Range["C"].Value.ToString();
                //}

                foreach (Microsoft.Office.Interop.Excel.Range row in sheet.UsedRange.Rows)
                {
                    foreach (Microsoft.Office.Interop.Excel.Range cell in row.Columns)
                    {
                        if (rowcount != 0)
                            data += " [" + cell.Value + "] ";
                            
                    }
                    data += "\n";
                    rowcount++;
                }
                if (sheetcount==0)
                    input_flow_data.InnerText = data;
                else if (sheetcount==1)
                    input_node_data.InnerText = data;
                sheetcount++;
               
            }
            

            xlApp.Quit();
            releaseObject(xlWorkBook);
            releaseObject(xlApp);         

            File.Delete(fileLocation);
        }
        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(obj);
            }
            catch { }

            finally
            {
                obj = null;
            }
        }

    }
}