C# :: Aufgabe #263

3 Lösungen Lösungen öffentlich

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.

Lösungen:

vote_ok
von crazyfrien (60 Punkte) - 19.06.2019 um 16:00 Uhr
Quellcode ausblenden C#-Code
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;
using Excel = Microsoft.Office.Interop.Excel;

//Need to add the Microsoft Office XX.X Object Library at COM

namespace ReadExcel
{
    public partial class Form1 : Form
    {
        string filePath ="";

        Excel.Application app;
        Excel.Workbook workBook;
        Excel.Worksheet workSheet;
        string[,] tableContents;

        public Form1()
        {
            InitializeComponent();

            app = new Excel.Application();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //Choose File via Dialog
            openFileDialog1.ShowDialog();
            filePath = openFileDialog1.FileName;
            textBox1.Text = filePath;

            //Read file
            workBook = app.Workbooks.Open(filePath);
            workSheet = workBook.Worksheets[1];

            //Show how many items are available
            int s=1, z = 1;
            while (workSheet.Cells[z,s].Value != null)
            {
                while (workSheet.Cells[z,s].Value != null)
                {
                    s++;

                }
                    z++;
            }
            s--;

            //Create array and write contents inside array
            tableContents = new string[s,z];
            for(int i=0;i<tableContents.GetLength(0);i++)
            {
                for(int j=0;j<tableContents.GetLength(1);j++)
                {
                    tableContents[i, j] = workSheet.Cells[j+1, i+1].Value;
                }
            }

            //Write to richtTextBox1
            richTextBox1.Clear();
            if (textBox2.Text != "" || textBox3.Text != "")
            {
                try
                {
                    richTextBox1.Text = tableContents[Convert.ToInt32(textBox2.Text)-1, Convert.ToInt32(textBox3.Text)-1];
                }
                catch(IndexOutOfRangeException ex)
                {
                    richTextBox1.Text = "Empty";
                }
                catch(Exception ex)
                {
                    richTextBox1.Text = ex.Message;
                }
            }
            else
            {
                for (int i = 0; i < tableContents.GetLength(1); i++)
                {
                    for (int j = 0; j < tableContents.GetLength(0); j++)
                    {
                        richTextBox1.Text += tableContents[j, i] + "\t";
                    }
                    richTextBox1.Text += "\n";
                }
            }
        }
    }
}
vote_ok
von daniel59 (4100 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;
        }
    }
}
vote_ok
von hollst (11280 Punkte) - 28.08.2019 um 14:04 Uhr
Quellcode ausblenden C#-Code
using System;
using System.Data;
using System.Windows.Forms;
using System.IO;

using ExcelDataReader;

namespace read_an_excel_file__xls_or_xlsx
{
    public partial class Form1 : Form
    {
        public Form1()        {
            InitializeComponent();
        }

        DataSet result;
        private void btOpen_Click(object sender, EventArgs e)
        {
            using (OpenFileDialog ofd = new OpenFileDialog()    {Filter = "Excel1|*.xls|Exel2|*.xlsx", ValidateNames = true })
            {
                if (ofd.ShowDialog() == DialogResult.OK)
                {
                    FileStream fs = File.Open(ofd.FileName, FileMode.Open, FileAccess.Read);
                    FileInfo fi = new FileInfo(ofd.FileName);
                    this.tb_filename.Text = fi.Name;
                    using (var reader = ExcelReaderFactory.CreateReader(fs))
                    {
                        result = reader.AsDataSet();
                        comboBox1.Items.Clear();
                        foreach (DataTable dt in result.Tables)
                            comboBox1.Items.Add(dt.TableName);
                        reader.Close();
                    }                
                }
            }
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)        {
            dataGridView1.DataSource = result.Tables[comboBox1.SelectedIndex];
        }

        private void dataGridView1_MouseClick(object sender, MouseEventArgs e)
        {
            this.tb_selected_cell.Text = 
            dataGridView1.SelectedCells[0].RowIndex.ToString()
+ "  " +
            dataGridView1.SelectedCells[0].ColumnIndex.ToString();

            string s = 
                dataGridView1[dataGridView1.SelectedCells[0].ColumnIndex, dataGridView1.SelectedCells[0].RowIndex].Value.ToString();
            this.tb_content.Text = s;
        }
    }
}

/*
Lösung basiert im Wesentlichen auf 

https://www.youtube.com/watch?v=JlzzdB3K-1M

https://stackoverflow.com/questions/27634477/using-exceldatareader-to-read-excel-data-starting-from-a-particular-cell
(If you are using ExcelDataReader 3+ you will find that there isn't any method for AsDataSet() for your reader object, 
You need to also install another package for ExcelDataReader.DataSet, then you can use the AsDataSet() method.)

und

https://www.youtube.com/watch?v=_h_4-HxrMMc

wichtig ist, dass ExcelDataReader und ExcelDataReader.DataSet über NuGet eingebunden werden.
*/