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 ReadExcelData
{
    public partial class frm17ReadExcelData : Form
    {
        public frm17ReadExcelData()
        {
            InitializeComponent();
        }

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

        // Read the data in the workbook's first two columns.
        private void readButton_Click(object sender, EventArgs e)
        {
            Cursor = Cursors.WaitCursor;

            // Get the Excel application object.
            Excel.Application excel = new Excel.Application();

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

            // Open the workbook read-only.
            Excel.Workbook workbook = excel.Workbooks.Open(
                Filename: fileTextBox.Text,
                ReadOnly: true);

            // Get the first worksheet.
            Excel.Worksheet sheet = (Excel.Worksheet)workbook.Sheets[1];

            // Get the titles and values.
            SetTitleAndListValues(sheet, 1, 1, title1Label, items1ListBox);
            SetTitleAndListValues(sheet, 1, 2, title2Label, items2ListBox);

            // Close the workbook without saving changes.
            workbook.Close(SaveChanges: false);

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


            Cursor = Cursors.Default;
        }

        // Set a title Label and the values in a ListBox.
        // Get the title from cell (row, col). Get the values from
        // cell (row + 1, col) to the end of the column.
        private void SetTitleAndListValues(Excel.Worksheet sheet,
            int row, int col, Label lbl, ListBox lst)
        {
            Excel.Range range;

            // Get the title.
            range = sheet.Cells[row, col];
            lbl.Text = (string)range.Value;
            lbl.ForeColor = System.Drawing.ColorTranslator.FromOle(
                (int)range.Font.Color);
            lbl.BackColor = System.Drawing.ColorTranslator.FromOle(
                (int)range.Interior.Color);

            // Get the values.
            // Find the last cell in the column.
            range = (Excel.Range)sheet.Columns[col];
            Excel.Range lastCell = range.get_End(Excel.XlDirection.xlDown);

            // Get a Range holding the values.
            Excel.Range firstCell = sheet.Cells[row + 1, col];
            Excel.Range valueRange = sheet.get_Range(firstCell, lastCell);

            // Copy the values into a list.
            List<string> values = new List<string>();
            foreach (object value in valueRange.Value)
            {
                values.Add((string)value);
            }

            // Display the values in the ListBox.
            lst.DataSource = values;
        }


        /// <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.items2ListBox = new System.Windows.Forms.ListBox();
            this.title2Label = new System.Windows.Forms.Label();
            this.items1ListBox = new System.Windows.Forms.ListBox();
            this.title1Label = new System.Windows.Forms.Label();
            this.fileTextBox = new System.Windows.Forms.TextBox();
            this.label1 = new System.Windows.Forms.Label();
            this.readButton = new System.Windows.Forms.Button();
            this.SuspendLayout();
            // 
            // items2ListBox
            // 
            this.items2ListBox.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom)));
            this.items2ListBox.FormattingEnabled = true;
            this.items2ListBox.Location = new System.Drawing.Point(138, 100);
            this.items2ListBox.Name = "items2ListBox";
            this.items2ListBox.Size = new System.Drawing.Size(120, 147);
            this.items2ListBox.TabIndex = 15;
            // 
            // title2Label
            // 
            this.title2Label.Anchor = System.Windows.Forms.AnchorStyles.Top;
            this.title2Label.Location = new System.Drawing.Point(138, 79);
            this.title2Label.Name = "title2Label";
            this.title2Label.Size = new System.Drawing.Size(120, 18);
            this.title2Label.TabIndex = 14;
            this.title2Label.Text = "Column B";
            this.title2Label.TextAlign = System.Drawing.ContentAlignment.MiddleCenter;
            // 
            // items1ListBox
            // 
            this.items1ListBox.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom)));
            this.items1ListBox.FormattingEnabled = true;
            this.items1ListBox.Location = new System.Drawing.Point(12, 100);
            this.items1ListBox.Name = "items1ListBox";
            this.items1ListBox.Size = new System.Drawing.Size(120, 147);
            this.items1ListBox.TabIndex = 13;
            // 
            // title1Label
            // 
            this.title1Label.Anchor = System.Windows.Forms.AnchorStyles.Top;
            this.title1Label.Location = new System.Drawing.Point(12, 79);
            this.title1Label.Name = "title1Label";
            this.title1Label.Size = new System.Drawing.Size(120, 18);
            this.title1Label.TabIndex = 12;
            this.title1Label.Text = "Column A";
            this.title1Label.TextAlign = System.Drawing.ContentAlignment.MiddleCenter;
            // 
            // 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, 10);
            this.fileTextBox.Name = "fileTextBox";
            this.fileTextBox.Size = new System.Drawing.Size(213, 20);
            this.fileTextBox.TabIndex = 9;
            this.fileTextBox.Text = "Items.xlsx";
            // 
            // label1
            // 
            this.label1.AutoSize = true;
            this.label1.Location = new System.Drawing.Point(12, 13);
            this.label1.Name = "label1";
            this.label1.Size = new System.Drawing.Size(26, 13);
            this.label1.TabIndex = 11;
            this.label1.Text = "File:";
            // 
            // readButton
            // 
            this.readButton.Anchor = System.Windows.Forms.AnchorStyles.Top;
            this.readButton.Location = new System.Drawing.Point(97, 36);
            this.readButton.Name = "readButton";
            this.readButton.Size = new System.Drawing.Size(75, 23);
            this.readButton.TabIndex = 10;
            this.readButton.Text = "Read";
            this.readButton.UseVisualStyleBackColor = true;
            this.readButton.Click += new System.EventHandler(this.readButton_Click);
            // 
            // frm17ReadExcelData
            // 
            this.AcceptButton = this.readButton;
            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.ClientSize = new System.Drawing.Size(269, 259);
            this.Controls.Add(this.items2ListBox);
            this.Controls.Add(this.title2Label);
            this.Controls.Add(this.items1ListBox);
            this.Controls.Add(this.title1Label);
            this.Controls.Add(this.fileTextBox);
            this.Controls.Add(this.label1);
            this.Controls.Add(this.readButton);
            this.Name = "frm17ReadExcelData";
            this.Text = "ReadExcelData";
            this.Load += new System.EventHandler(this.frm17ReadExcelData_Load);
            this.ResumeLayout(false);
            this.PerformLayout();

        }

        #endregion

        private System.Windows.Forms.ListBox items2ListBox;
        private System.Windows.Forms.Label title2Label;
        private System.Windows.Forms.ListBox items1ListBox;
        private System.Windows.Forms.Label title1Label;
        private System.Windows.Forms.TextBox fileTextBox;
        private System.Windows.Forms.Label label1;
        private System.Windows.Forms.Button readButton;
    }
}