#region Copyright Syncfusion Inc. 2001 - 2007
//
//  Copyright Syncfusion Inc. 2001 - 2007. All rights reserved.
//
//  Use of this code is subject to the terms of our license.
//  A copy of the current license can be obtained at any time by e-mailing
//  licensing@syncfusion.com. Any infringement will be prosecuted under
//  applicable laws. 
//
#endregion

using System;
using System.ComponentModel;
using System.Drawing;
using System.Windows.Forms;
using Syncfusion.XlsIO;
using System.Globalization;
using System.IO;
using Confluence.WorkbooksGateway.Gateway.XlsIO;
using System.Collections.Generic;
using System.Text;
using Syncfusion.Calculate;

namespace EssentialXlsIOSamples
{
    /// <summary>
    /// Summary description for Form1.
    /// </summary>
    public class CreateSpreadsheet : Form
    {
        #region Constants
        //private const string DEFAULTPATH = @"..\..\..\..\..\..\..\..\Common\Data\{0}";

        private const string DEFAULTPATH = @"..\..\Data\{0}";
        private const string OUTPUTPATH = @"..\..\Output\{0}";
        #endregion

        #region Fields
        private System.Windows.Forms.Button btnCreate;
        private Label label2;
        private GroupBox groupBox1;
        private RadioButton rdbExcel2007;
        private RadioButton rdbExcel97to2003;
        private RadioButton rdbExcel2010;
        private PictureBox pictureBox3;
        private RadioButton rdbExcel2013;

        /// <summary>
        /// Required designer variable.
        /// </summary>
        private System.ComponentModel.Container components = null;
        #endregion

        #region Initialize
        public CreateSpreadsheet()
        {
            //
            // Required for Windows Form Designer support
            //
            InitializeComponent();
            this.FormBorderStyle = FormBorderStyle.FixedSingle;
            this.StartPosition = FormStartPosition.CenterScreen;
            this.rdbExcel2013.Checked = true;
            //
            // TODO: Add any constructor code after InitializeComponent call
            //
        }
        #endregion

        #region CreateSpreadsheet

        private static void CopyCrossChecksToSheet(Syncfusion.XlsIO.IWorksheet destinationSheet, Syncfusion.XlsIO.IWorksheet sourceSheet)
        {
            int destinationRowIndex = 1;

            int endRow = sourceSheet.Rows[sourceSheet.Rows.Length - 1].Row;
            int endColumn = sourceSheet.Columns[sourceSheet.Columns.Length - 1].Column;

            Syncfusion.XlsIO.IRange sourceRange = sourceSheet[1, 1, endRow, endColumn];
            Syncfusion.XlsIO.IRange destinationRange = destinationSheet[destinationRowIndex, 1,
                destinationRowIndex + endRow, endColumn];
            sourceRange.CopyTo(destinationRange, ExcelCopyRangeOptions.All);
            destinationRowIndex += sourceRange.Rows.Length + 1;
        }

        private void btnCreate_Click(object sender, System.EventArgs e)
        {
            #region Workbook Intialization

            //New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
            //The instantiation process consists of two steps.

            ExcelEngine excelEngine = new ExcelEngine();
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;

            IWorkbook workbook = application.Workbooks.Open("../../Data/ExcelData.xlsx", ExcelParseOptions.ParseWorksheetsOnDemand);
            workbook.Version = ExcelVersion.Excel2013;
            workbook.ThrowOnUnknownNames = false;

            //copy the 'Cross Checks' template into the workbook
            IWorkbook newWorkbook = excelEngine.Excel.Workbooks.Create(1);
            IWorksheet destinationSheet = newWorkbook.ActiveSheet;
            destinationSheet.Name = "Cross Checks";
            newWorkbook.ThrowOnUnknownNames = false;
            IWorkbook template = excelEngine.Excel.Workbooks.OpenReadOnly("../../Data/CrossCheckTemplateBad.xlsx");
            CopyCrossChecksToSheet(destinationSheet, template.ActiveSheet);
            template.Close();
            workbook.Worksheets.AddCopy(destinationSheet);

            destinationSheet.InsertRow(1, 1); //this line will throw an Index out-of-range exception

            #endregion

            #region Workbook Save
            string fileName = "";
            string outputPath = "";
            if (rdbExcel97to2003.Checked)
            {
                fileName = "CreateSpreadsheet.xls";
                workbook.Version = ExcelVersion.Excel97to2003;
                outputPath = GetFullOutputPath(fileName);
                workbook.SaveAs(outputPath);
            }
            else if (rdbExcel2007.Checked)
            {
                fileName = "CreateSpreadsheet.xlsx";
                workbook.Version = ExcelVersion.Excel2007;
                outputPath = GetFullOutputPath(fileName);
                workbook.SaveAs(outputPath);
            }
            else if (rdbExcel2010.Checked)
            {
                fileName = "CreateSpreadsheet.xlsx";
                workbook.Version = ExcelVersion.Excel2010;
                outputPath = GetFullOutputPath(fileName);
                workbook.SaveAs(outputPath);
            }
            else if (rdbExcel2013.Checked)
            {
                fileName = "CreateSpreadsheet.xlsx";
                workbook.Version = ExcelVersion.Excel2013;
                outputPath = GetFullOutputPath(fileName);
                workbook.SaveAs(outputPath);
            }


            #endregion

            #region Workbook Close and Dispose

            //Close the workbook.
            workbook.Close();
            excelEngine.Dispose();
            #endregion

            #region View the Workbook
            //Message box confirmation to view the created document.
            if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",
            MessageBoxButtons.YesNo, MessageBoxIcon.Information)
            == DialogResult.Yes)
            {
                try
                {
                    //Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
                    System.Diagnostics.Process.Start(outputPath);

                    //Exit
                    this.Close();
                }
                catch (Win32Exception ex)
                {
                    MessageBox.Show("Ms Excel is not installed in this system");
                    Console.WriteLine(ex.ToString());
                }
            }
            else
                this.Close();
            #endregion
        }
        #endregion

        #region HelperMethods
        private string GetFullTemplatePath(string inputFile)
        {
            return string.Format(DEFAULTPATH, inputFile);
        }

        private string GetFullOutputPath(string outputFile)
        {
            return string.Format(OUTPUTPATH, outputFile);
        }

        #endregion

        #region Windows Form Designer generated code
        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
            System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(CreateSpreadsheet));
            this.btnCreate = new System.Windows.Forms.Button();
            this.label2 = new System.Windows.Forms.Label();
            this.groupBox1 = new System.Windows.Forms.GroupBox();
            this.rdbExcel2013 = new System.Windows.Forms.RadioButton();
            this.rdbExcel2010 = new System.Windows.Forms.RadioButton();
            this.rdbExcel2007 = new System.Windows.Forms.RadioButton();
            this.rdbExcel97to2003 = new System.Windows.Forms.RadioButton();
            this.pictureBox3 = new System.Windows.Forms.PictureBox();
            this.groupBox1.SuspendLayout();
            ((System.ComponentModel.ISupportInitialize)(this.pictureBox3)).BeginInit();
            this.SuspendLayout();
            // 
            // btnCreate
            // 
            this.btnCreate.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Right)));
            this.btnCreate.BackColor = System.Drawing.Color.Transparent;
            this.btnCreate.Image = ((System.Drawing.Image)(resources.GetObject("btnCreate.Image")));
            this.btnCreate.ImageAlign = System.Drawing.ContentAlignment.MiddleRight;
            this.btnCreate.Location = new System.Drawing.Point(375, 153);
            this.btnCreate.Name = "btnCreate";
            this.btnCreate.Size = new System.Drawing.Size(85, 26);
            this.btnCreate.TabIndex = 0;
            this.btnCreate.Text = "Create";
            this.btnCreate.UseVisualStyleBackColor = false;
            this.btnCreate.Click += new System.EventHandler(this.btnCreate_Click);
            // 
            // label2
            // 
            this.label2.Font = new System.Drawing.Font("Verdana", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(161)));
            this.label2.ForeColor = System.Drawing.SystemColors.ControlText;
            this.label2.Location = new System.Drawing.Point(0, 87);
            this.label2.Name = "label2";
            this.label2.Size = new System.Drawing.Size(426, 48);
            this.label2.TabIndex = 64;
            this.label2.Text = "Click the button to view an Excel spreadsheet generated by Essential XlsIO. Pleas" +
        "e note that MS Excel Viewer or MS Excel is required to view the resultant docume" +
        "nt.";
            this.label2.TextAlign = System.Drawing.ContentAlignment.MiddleLeft;
            // 
            // groupBox1
            // 
            this.groupBox1.Controls.Add(this.rdbExcel2013);
            this.groupBox1.Controls.Add(this.rdbExcel2010);
            this.groupBox1.Controls.Add(this.rdbExcel2007);
            this.groupBox1.Controls.Add(this.rdbExcel97to2003);
            this.groupBox1.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.groupBox1.Location = new System.Drawing.Point(3, 138);
            this.groupBox1.Name = "groupBox1";
            this.groupBox1.Size = new System.Drawing.Size(349, 48);
            this.groupBox1.TabIndex = 73;
            this.groupBox1.TabStop = false;
            this.groupBox1.Text = "Save As";
            // 
            // rdbExcel2013
            // 
            this.rdbExcel2013.AutoSize = true;
            this.rdbExcel2013.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.rdbExcel2013.Location = new System.Drawing.Point(268, 19);
            this.rdbExcel2013.Name = "rdbExcel2013";
            this.rdbExcel2013.Size = new System.Drawing.Size(75, 17);
            this.rdbExcel2013.TabIndex = 7;
            this.rdbExcel2013.Text = "Excel2013";
            this.rdbExcel2013.UseVisualStyleBackColor = true;
            // 
            // rdbExcel2010
            // 
            this.rdbExcel2010.AutoSize = true;
            this.rdbExcel2010.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.rdbExcel2010.Location = new System.Drawing.Point(187, 20);
            this.rdbExcel2010.Name = "rdbExcel2010";
            this.rdbExcel2010.Size = new System.Drawing.Size(75, 17);
            this.rdbExcel2010.TabIndex = 6;
            this.rdbExcel2010.Text = "Excel2010";
            this.rdbExcel2010.UseVisualStyleBackColor = true;
            // 
            // rdbExcel2007
            // 
            this.rdbExcel2007.AutoSize = true;
            this.rdbExcel2007.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.rdbExcel2007.Location = new System.Drawing.Point(106, 20);
            this.rdbExcel2007.Name = "rdbExcel2007";
            this.rdbExcel2007.Size = new System.Drawing.Size(75, 17);
            this.rdbExcel2007.TabIndex = 1;
            this.rdbExcel2007.Text = "Excel2007";
            this.rdbExcel2007.UseVisualStyleBackColor = true;
            // 
            // rdbExcel97to2003
            // 
            this.rdbExcel97to2003.AutoSize = true;
            this.rdbExcel97to2003.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.rdbExcel97to2003.Location = new System.Drawing.Point(9, 19);
            this.rdbExcel97to2003.Name = "rdbExcel97to2003";
            this.rdbExcel97to2003.Size = new System.Drawing.Size(96, 17);
            this.rdbExcel97to2003.TabIndex = 0;
            this.rdbExcel97to2003.Text = "Excel97to2003";
            this.rdbExcel97to2003.UseVisualStyleBackColor = true;
            // 
            // pictureBox3
            // 
            this.pictureBox3.Image = ((System.Drawing.Image)(resources.GetObject("pictureBox3.Image")));
            this.pictureBox3.Location = new System.Drawing.Point(0, 0);
            this.pictureBox3.Name = "pictureBox3";
            this.pictureBox3.Size = new System.Drawing.Size(639, 71);
            this.pictureBox3.SizeMode = System.Windows.Forms.PictureBoxSizeMode.AutoSize;
            this.pictureBox3.TabIndex = 75;
            this.pictureBox3.TabStop = false;
            // 
            // CreateSpreadsheet
            // 
            this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
            this.BackColor = System.Drawing.Color.White;
            this.ClientSize = new System.Drawing.Size(466, 195);
            this.Controls.Add(this.groupBox1);
            this.Controls.Add(this.label2);
            this.Controls.Add(this.btnCreate);
            this.Controls.Add(this.pictureBox3);
            this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedSingle;
            this.Icon = ((System.Drawing.Icon)(resources.GetObject("$this.Icon")));
            this.MaximizeBox = false;
            this.Name = "CreateSpreadsheet";
            this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
            this.Text = "Create Spreadsheet";
            this.groupBox1.ResumeLayout(false);
            this.groupBox1.PerformLayout();
            ((System.ComponentModel.ISupportInitialize)(this.pictureBox3)).EndInit();
            this.ResumeLayout(false);
            this.PerformLayout();

        }
        #endregion

        #region Dispose methods
        /// <summary>
        /// Clean up any resources being used.
        /// </summary>
        protected override void Dispose(bool disposing)
        {
            if (disposing)
            {
                if (components != null)
                {
                    components.Dispose();
                }
            }
            base.Dispose(disposing);
        }

        internal static void CopyData(IWorksheet sourceSheet, IWorksheet destinationSheet)
        {
            if (sourceSheet.MergedCells != null)
            {
                foreach (var cell in sourceSheet.MergedCells)
                    cell.UnMerge();
            }

            for (int i = 0; i < sourceSheet.Columns.Length; i++)
            {
                sourceSheet.Range[3, i + 1, sourceSheet.Rows.Length, i + 1]
                    .CopyTo(destinationSheet.Range[1, i + 1, sourceSheet.Rows.Length, i + 1]);
            }
        }

        internal static void BuildErrorBanner(Syncfusion.XlsIO.IWorksheet checkSummary)
        {
            Syncfusion.XlsIO.IRange errorTextRange = checkSummary[7, 2, 7, 6];
            errorTextRange.Merge();
            var red = Color.FromArgb(255, 0, 0);
            errorTextRange.CellStyle.Borders[ExcelBordersIndex.EdgeTop].ColorRGB = red;
            errorTextRange.CellStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
            errorTextRange.CellStyle.Borders[ExcelBordersIndex.EdgeLeft].ColorRGB = red;
            errorTextRange.CellStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
            errorTextRange.CellStyle.Borders[ExcelBordersIndex.EdgeRight].ColorRGB = red;
            errorTextRange.CellStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
            errorTextRange.HorizontalAlignment = ExcelHAlign.HAlignCenter;
            Syncfusion.XlsIO.IRange hyperLinkRange = checkSummary[8, 2, 8, 6];
            hyperLinkRange.Merge();
            hyperLinkRange.CellStyle.Borders[ExcelBordersIndex.EdgeBottom].ColorRGB = red;
            hyperLinkRange.CellStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
            hyperLinkRange.CellStyle.Borders[ExcelBordersIndex.EdgeLeft].ColorRGB = red;
            hyperLinkRange.CellStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
            hyperLinkRange.CellStyle.Borders[ExcelBordersIndex.EdgeRight].ColorRGB = red;
            hyperLinkRange.CellStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
            hyperLinkRange.HorizontalAlignment = ExcelHAlign.HAlignCenter;

            Syncfusion.XlsIO.IRange errorTextCell = checkSummary[7, 2];
            errorTextCell.CellStyle.Font.FontName = "Calibri";
            errorTextCell.CellStyle.Font.Size = 12.0;
            errorTextCell.CellStyle.Font.Bold = true;
            errorTextCell.Text = "! Errors found in this Workbook !";
            errorTextCell.CellStyle.Font.RGBColor = Color.FromArgb(192, 0, 0);

            Syncfusion.XlsIO.IRange hyperLinkCell = checkSummary[8, 2];
            var hyperLink = checkSummary.HyperLinks.Add(hyperLinkCell);
            hyperLink.Type = ExcelHyperLinkType.Workbook;
            hyperLink.Address = "'Sheet3'!A1";
            hyperLink.TextToDisplay = "For more information go to the Error Log";
            hyperLinkCell.CellStyle.Font.FontName = "Calibri";
            hyperLinkCell.CellStyle.Font.Size = 11.0;
        }

        /// <summary>
        /// The main entry point for the application.
        /// </summary>
        [STAThread]
        static void Main()
        {
            Application.EnableVisualStyles();
            Application.Run(new CreateSpreadsheet());
        }
        #endregion
    }
}