using System;
using System.Data;
using System.IO;
using System.Text;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Xml;
using GemBox.Spreadsheet;

namespace WebDemo
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                DataTable people = new DataTable();

                people.Columns.Add("ID", Type.GetType("System.Int32"));
                people.Columns.Add("FirstName", Type.GetType("System.String"));
                people.Columns.Add("LastName", Type.GetType("System.String"));

                Session["people"] = people;

                this.LoadDataFromFile(Request.PhysicalApplicationPath + "InData.csv");
                                
                this.SetDataBinding();
            }
        }

        protected void Export_Click(object sender, EventArgs e)
        {
            DataTable people = (DataTable)Session["people"];
            // Create excel file.
            ExcelFile ef = new ExcelFile();
            ExcelWorksheet ws = ef.Worksheets.Add("DataSheet");
            ws.InsertDataTable(people, "A1", true);

            Response.Clear();

            // Stream file to browser, in required type.
            switch (this.RadioButtonList1.SelectedValue)
            {
                case "XLS":
                    Response.ContentType = "application/vnd.ms-excel";
                    Response.AddHeader("Content-Disposition", "attachment; filename=" + "Report14.xls");
                    ef.SaveXls(Response.OutputStream);
                    break;

                case "CSV":
                    Response.ContentType = "text/csv";
                    Response.AddHeader("Content-Disposition", "attachment; filename=" + "Report15.csv");
                    ef.SaveCsv(Response.OutputStream, CsvType.CommaDelimited);
                    break;

                case "XLSX":
                    Response.ContentType = "application/vnd.openxmlformats";
                    Response.AddHeader("Content-Disposition", "attachment; filename=" + "Report16.xlsx");
                    // With XLSX it is a bit more complicated as MS Packaging API can't write
                    // directly to Response.OutputStream. Therefore we use temporary MemoryStream.
                    MemoryStream ms = new MemoryStream();
                    ef.SaveXlsx(ms);
                    ms.WriteTo(Response.OutputStream);
                    break;

                case "ODS":
                    Response.ContentType = "application/vnd.oasis.opendocument.spreadsheet";
                    Response.AddHeader("Content-Disposition", "attachment; filename=" + "Report17.ods");
                    ef.SaveOds(Response.OutputStream);
                    break;

                case "HTML":
                    Response.ContentType = "text/html";
                    Response.AddHeader("Content-Disposition", "attachment; filename=" + "Report18.html");
                    XmlTextWriter writer = new XmlTextWriter(Response.OutputStream, new UTF8Encoding(false));
                    ef.SaveHtml(writer, null, true);
                    writer.Close();
                    break;
            }

            Response.End();
        }

        private void LoadDataFromFile(string fileName)
        {
            ExcelFile ef = new ExcelFile();

            DataTable people = (DataTable)Session["people"];

            ef.LoadCsv(fileName, CsvType.CommaDelimited);

            ExcelWorksheet ws = ef.Worksheets[0];

            ws.ExtractToDataTable(people, ws.Rows.Count,
                ExtractDataOptions.StopAtFirstEmptyRow, ws.Rows[0], ws.Columns[0]);
        }

        private void SetDataBinding()
        {
            DataTable people = (DataTable)Session["people"];
            DataView peopleDataView = people.DefaultView;

            this.GridView1.DataSource = peopleDataView;
            peopleDataView.AllowDelete = true;
            this.GridView1.DataBind();
        }

        protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            DataTable people = (DataTable)Session["people"];

            people.Rows[e.RowIndex].Delete();
            this.SetDataBinding();
        }

        protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
        {
            this.GridView1.EditIndex = e.NewEditIndex;
            this.SetDataBinding();
        }

        protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {
            int i;
            int rowIndex = e.RowIndex;
            DataTable people = (DataTable)Session["people"];

            for (i = 1; i <= people.Columns.Count; i++)
            {
                TextBox editTextBox = this.GridView1.Rows[rowIndex].Cells[i].Controls[0] as TextBox;

                if (editTextBox != null)
                {
                    people.Rows[rowIndex][i - 1] = editTextBox.Text;
                }
            }

            this.GridView1.EditIndex = -1;
            this.SetDataBinding();
        }

        protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
        {
            this.GridView1.EditIndex = -1;
            this.SetDataBinding();
        }
    }
}