C# :: Aufgabe #263 :: Lösung #2

4 Lösungen Lösungen öffentlich
#263

Zelle aus Exceldatei auslesen

Anfänger - C# von Gustl - 12.06.2019 um 10:29 Uhr
Es solle eine Excel Datei eingelesen (XLSX) und dann ein bestimmter Inhalt einer Zelle wieder ausgegeben werden.

#2
vote_ok
von daniel59 (4260 Punkte) - 23.07.2019 um 10:47 Uhr
Quellcode ausblenden C#-Code
using System;
using System.Collections.Generic;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;

namespace ConsoleReadExcel
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("----- Zelle aus Exceldatei auslesen ----");

            ReadExcel("tba");

            Console.WriteLine("Programm beendet");
            Console.ReadLine();
        }

        static void ReadExcel(string filename)
        {
            Excel.Application app = null;
            Excel.Workbooks workbooks = null;
            Excel.Workbook workbook = null;
            Excel.Sheets sheets = null;
            Excel.Worksheet worksheet = null;
            Excel.Range cells = null;

            List<object> comObjects = new List<object>();

            try
            {
                app = new Excel.Application();
                app.Visible = false;
                workbooks = app.Workbooks;
                workbook = workbooks.Open(filename);
                sheets = workbook.Worksheets;
                worksheet = sheets.Item[1];
                cells = worksheet.Cells;

                comObjects.Add(app);
                comObjects.Add(workbooks);
                comObjects.Add(workbook);
                comObjects.Add(sheets);
                comObjects.Add(worksheet);
                comObjects.Add(cells);

                int row, column;
                do
                {
                    Console.Write("Zelle: ");
                    string cell = Console.ReadLine();
                    GetRowColumn(cell, out row, out column);

                    dynamic dyn = cells[row, column];
                    comObjects.Add(dyn);
                    object value = dyn.Value;
                    Console.WriteLine($"Wert: {value}");

                } while (row != 0 && column != 0);

                workbook?.Close(false);
                app?.Application?.Quit();
                app?.Quit();
                ReleaseComObjects(ref comObjects);
            }
            catch (Exception)
            {
                workbook?.Close(false);
                app?.Application?.Quit();
                app?.Quit();
                ReleaseComObjects(ref comObjects);
            }
        }

        static void ReleaseComObjects(ref List<object> comObjects)
        {
            for (int i = 0; i < comObjects.Count; i++)
            {
                object obj = comObjects[i];
                if (Marshal.IsComObject(obj))
                {
                    while (obj != null && Marshal.ReleaseComObject(obj) != 0) ;
                    while (obj != null && Marshal.FinalReleaseComObject(obj) != 0) ;
                }
                obj = null;
            }

            GC.Collect();
            GC.WaitForPendingFinalizers();
        }

        static void GetRowColumn(string cell, out int row, out int column)
        {
            row = 0;
            column = 0;

            string col = "";
            string r = "";
            bool columnRead = false;
            foreach (char c in cell)
            {
                if (char.IsLetter(c) && !columnRead)
                {
                    col += c;
                }
                else
                {
                    columnRead = true;
                    r += c;
                }
            }

            if (!int.TryParse(r, out row))
            {
                return;
            }
            column = GetColumnNumber(col);
        }

        static int GetColumnNumber(string column)//A --> 1 B --> 2 CI --> 87
        {
            int col = 0;
            char[] temp = column.ToUpper().ToCharArray();
            for (int i = temp.Length - 1; i >= 0; i--)
            {
                int num = Convert.ToInt32(temp[i]) - 64;
                col += num * Convert.ToInt32(Math.Pow(26, temp.Length - 1 - i));
            }
            return col;
        }
    }
}

Kommentare:

Für diese Lösung gibt es noch keinen Kommentar

Bitte melden Sie sich an um eine Kommentar zu schreiben.
Kommentar schreiben