test_excelreadonecolumn_testvisualstudio/Test_ExcelReadOneColumn/Program.cs

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using Microsoft.Office.Interop.Excel;   // zusätzlich ist der Projektverweis auf COM -> Microsoft Excel xx-Objectlibrary
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace Test_ExcelReadOneColumn
{
    internal class Program
    {
        #region Version und Copyright
        // Version und Copyright
        static string programName = Path.GetFileNameWithoutExtension(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase); //Den Programmnamen auslesen
        static string programVersion = System.Reflection.Assembly.GetExecutingAssembly().GetName().Version.ToString();
        static object[] attributes = Assembly.GetExecutingAssembly().GetCustomAttributes(typeof(AssemblyCopyrightAttribute), false);
        static string copyright = GenerateCopyright();

        //Assembly Datum und Zeit
        static DateTime value = AssemblyDateTime();
        string date = value.ToShortDateString();
        string time = value.ToLongTimeString();

        private static DateTime AssemblyDateTime()
        {
            var version = System.Reflection.Assembly.GetExecutingAssembly().GetName().Version;
            var buildDateTime = new DateTime(2000, 1, 1).Add(new TimeSpan(TimeSpan.TicksPerDay * version.Build + TimeSpan.TicksPerSecond * 2 * version.Revision));
            //Tage seit dem 1. Januar 2000 und Sekunden seit Mitternacht, (multiplizier mit 2 ergibt das Original)
            return buildDateTime;
        }

        private static string CurrentYear()
        {
            DateTime dtn = DateTime.Now;
            return dtn.Year.ToString();
        }

        private static string StartYear()
        {
            string startYear = "";
            if (((AssemblyCopyrightAttribute)attributes[0]).Copyright.Contains("Copyright © "))
            {
                startYear = ((AssemblyCopyrightAttribute)attributes[0]).Copyright.Replace("Copyright © ", "");
                while (startYear.StartsWith(" "))
                {
                    startYear = startYear.Remove(0, 1);
                }

                if (startYear.Contains("-"))
                {
                    startYear = startYear.Remove(startYear.IndexOf("-"));
                }
                else
                {
                    startYear = startYear.Remove(startYear.IndexOf(" "));
                }
                return startYear;
            }
            else
            {
                DateTime dtn = DateTime.Now;
                return dtn.Year.ToString();
            }
        }

        private static string GenerateCopyright()
        {
            string startYear = StartYear();
            string currentYear = CurrentYear();

            if (startYear == currentYear)
            {
                return ((AssemblyCopyrightAttribute)attributes[0]).Copyright;
            }
            else
            {
                string temp = ((AssemblyCopyrightAttribute)attributes[0]).Copyright;
                string start = temp.Remove(temp.IndexOf(startYear) - 1);
                string end = (temp.Remove(0, temp.IndexOf(" by")));

                return String.Concat(start, " ", startYear, "-", currentYear, end);
            }
        }
        #endregion

        #region Programm-Info
        /// <summary>
        /// Contains the name of the program file
        /// </summary>
        public static string ProgramName { set; get; }

        /// <summary>
        /// Contains the version of the program file
        /// </summary>
        public static string ProgramVersion { set; get; }

        /// <summary>
        /// Contains the copyright notice
        /// </summary>
        public static string Copyright { set; get; }

        private static void SetProgramInfo()
        {
            //Name, Version und Copyright setzen
            ProgramName = programName;
            ProgramVersion = programVersion;
            Copyright = copyright;
        }
        #endregion

        static void Main(string[] args)
        {
            // Programminformationen setzen
            SetProgramInfo();

            ////////////////////////////////////////////////////////////////
            //
            // TODO: Hier Programmcode einfügen
            string fileName = @"C:\CAD\Git-WorkRepository\VisualStudio\_TEST\Test_ExcelReadOneColumn.git\Daten\Test.xlsx";
            List<string> machines = new List<string>();

            var ld = new Program();
            machines = ld.LoadData(fileName); //Alle Maschinentypen suchen
            machines = ld.EliminateDuplicates(machines);
            machines = ld.SortData(machines);

            string output = "";
            foreach (var item in machines)
            {
                output += item + "\r\n";
            }
            Console.WriteLine(output);
            Console.ReadLine();
            //TODO: machines filtern

            //
            ////////////////////////////////////////////////////////////////
        }

        private List<string> LoadData(string fileName)
        {
            List<string> data = new List<string>();

            Microsoft.Office.Interop.Excel.Application ExcelApplication;
            Microsoft.Office.Interop.Excel.Workbook ExcelWorkbook;
            Microsoft.Office.Interop.Excel.Worksheet ExcelWorkSheet;
            ExcelApplication = null;

            ExcelApplication = new Microsoft.Office.Interop.Excel.Application();
            ExcelWorkbook = ExcelApplication.Workbooks.Open(fileName);
            ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkbook.ActiveSheet;

            int numberOfUsedRows = ExcelWorkSheet.UsedRange.Rows.Count;

            for (int i = 5; i < numberOfUsedRows + 1; i++)
            {
                if (ExcelWorkSheet.Cells[i, "A"].Value2 != null)
                {
                    data.Add(ExcelWorkSheet.Cells[i, "A"].Value2.ToString());
                }
            }

            //Excel beenden
            if (ExcelApplication != null)
            {
                ExcelApplication.Quit();
            }

            return data;
        }

        private List<string> EliminateDuplicates(List<string> machines)
        {
            List<string> data = new List<string>();

            for (int i = 0; i < machines.Count; i++)
            {
                if (data != null)
                {
                    //Wenn es schon Daten gibt, dann vergleichen
                    bool exist = false;

                    for (int j = 0; j < data.Count; j++)
                    {
                        if (machines[i] == data[j])
                        {
                            exist = true;
                        }
                    }

                    if (!exist)
                    {
                        data.Add(machines[i]);
                    }
                    else
                    {
                        exist = false;
                    }
                }
                else
                {
                    data.Add(machines[i]); //Wenn es noch keine Daten gibt, dann aufnehmen
                }
            }

            return data;
        }

        private List<string> SortData(List<string> machines)
        {
            List<string> data = new List<string>();

            if (machines != null)
            {
                data = machines;
                data.Sort();
            }
            
            return data;
        }
    }
}