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;

// Open the Add References dialog, open the Assemblies branch,
// select the the Extensions tab, and check:
//      Microsoft.Office.Interop.Excel

using Excel = Microsoft.Office.Interop.Excel;

namespace WriteExcelData
{
    public partial class frm18WriteExcelData : Form
    {
        public frm18WriteExcelData()
        {
            InitializeComponent();
        }

        // Initialize the file name with a full path.
        private void frm18WriteExcelData_Load(object sender, EventArgs e)
        {
            fileTextBox.Text = System.IO.Path.GetFullPath("Items.xlsx");
        }

        // Write into the Excel workbook.
        private void writeButton_Click(object sender, EventArgs e)
        {
            // Get the Excel application object.
            Excel.Application excel = new Excel.Application();

            // Uncomment to make Excel visible.
            //excel.Visible = true;

            // Open the workbook.
            Excel.Workbook workbook = excel.Workbooks.Open(Filename: fileTextBox.Text);

            // See if a worksheet with today's date already exists.
            string sheetName = DateTime.Now.ToString("MM-dd-yy");
            Excel.Worksheet sheet = FindSheet(workbook, sheetName);
            if (sheet == null)
            {
                // Add the worksheet at the end.
                sheet = (Excel.Worksheet)workbook.Sheets.Add(
                    After: workbook.Sheets[workbook.Sheets.Count]);
                sheet.Name = DateTime.Now.ToString("MM-dd-yy");
            }

            // Add some data to individual cells.
            sheet.Cells[1, 1] = "A";
            sheet.Cells[1, 2] = "B";
            sheet.Cells[1, 3] = "C";

            // Make that range of cells bold and red.
            Excel.Range headerRange = sheet.get_Range("A1", "C1");
            headerRange.Font.Bold = true;
            headerRange.Font.Color =
                System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
            headerRange.Interior.Color =
                System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Pink);

            // Add some data to a range of cells.
            int[,] values =
            {
                { 2,  4,  6},
                { 3,  6,  9},
                { 4,  8, 12},
                { 5, 10, 15},
            };
            Excel.Range valueRange = sheet.get_Range("A2", "C5");
            valueRange.Value = values;

            // Save the changes and close the workbook.
            workbook.Close(SaveChanges: true);

            // Close the Excel server.
            excel.Quit();

            MessageBox.Show("Done");
        }

        // Return the worksheet with the given name if it exists.
        private Excel.Worksheet FindSheet(Excel.Workbook workbook, string sheetName)
        {
            foreach (Excel.Worksheet sheet in workbook.Sheets)
                if (sheet.Name == sheetName) return sheet;
            return null;
        }


        /// <summary>
        /// Required designer variable.
        /// </summary>
        private System.ComponentModel.IContainer components = null;

        /// <summary>
        /// Clean up any resources being used.
        /// </summary>
        /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
        protected override void Dispose(bool disposing)
        {
            if (disposing && (components != null))
            {
                components.Dispose();
            }
            base.Dispose(disposing);
        }

        #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()
        {
            this.fileTextBox = new System.Windows.Forms.TextBox();
            this.label1 = new System.Windows.Forms.Label();
            this.writeButton = new System.Windows.Forms.Button();
            this.SuspendLayout();
            // 
            // fileTextBox
            // 
            this.fileTextBox.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Left) 
            | System.Windows.Forms.AnchorStyles.Right)));
            this.fileTextBox.Location = new System.Drawing.Point(44, 12);
            this.fileTextBox.Name = "fileTextBox";
            this.fileTextBox.Size = new System.Drawing.Size(328, 20);
            this.fileTextBox.TabIndex = 2;
            // 
            // label1
            // 
            this.label1.AutoSize = true;
            this.label1.Location = new System.Drawing.Point(12, 15);
            this.label1.Name = "label1";
            this.label1.Size = new System.Drawing.Size(26, 13);
            this.label1.TabIndex = 3;
            this.label1.Text = "File:";
            // 
            // writeButton
            // 
            this.writeButton.Anchor = System.Windows.Forms.AnchorStyles.Top;
            this.writeButton.Location = new System.Drawing.Point(155, 47);
            this.writeButton.Name = "writeButton";
            this.writeButton.Size = new System.Drawing.Size(75, 23);
            this.writeButton.TabIndex = 4;
            this.writeButton.Text = "Write";
            this.writeButton.UseVisualStyleBackColor = true;
            this.writeButton.Click += new System.EventHandler(this.writeButton_Click);
            // 
            // frm18WriteExcelData
            // 
            this.AcceptButton = this.writeButton;
            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.ClientSize = new System.Drawing.Size(384, 83);
            this.Controls.Add(this.fileTextBox);
            this.Controls.Add(this.label1);
            this.Controls.Add(this.writeButton);
            this.Name = "frm18WriteExcelData";
            this.Text = "WriteExcelData";
            this.Load += new System.EventHandler(this.frm18WriteExcelData_Load);
            this.ResumeLayout(false);
            this.PerformLayout();

        }

        #endregion

        private System.Windows.Forms.TextBox fileTextBox;
        private System.Windows.Forms.Label label1;
        private System.Windows.Forms.Button writeButton;
    }
}