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

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.

#4
vote_ok
von MarkusH. (440 Punkte) - 29.09.2019 um 16:35 Uhr
Quellcode ausblenden C#-Code
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.IO;
using System.Linq;
using System.Text.RegularExpressions;

namespace ConsoleDemo
{
    /// <summary>
    /// Read Excel cells using OpenXML
    /// </summary>
    class Program
    {
        #region Fields
        static readonly Regex CELLPATTERN = new Regex(@"^([A-Z]+)(\d+)$");
        static WorkbookPart workbookPart;
        static WorksheetPart worksheetPart;
        static SheetData sheetData;
        static Sheet sheet;
        #endregion

        /// <summary>
        /// Main program
        /// </summary>
        /// <param name="args">No arguments used</param>
        static void Main(string[] args)
        {
            string excelFile = @"E:\Test.xlsx";
            if(!File.Exists(excelFile)) PrintMessageAndExit(-1, "Exel file does not exist!");

            Console.Write("Please select the worksheet index (zero based): ");
            if (!int.TryParse(Console.ReadLine(), out int sheetIndex)) PrintMessageAndExit(-1, "The sheet index is invalid!");

            int exitCode = ReadExcelCells(sheetIndex, excelFile);
            PrintMessageAndExit(exitCode, "Exiting program!");
        }

        /// <summary>
        /// Read Excel cells within a loop
        /// </summary>
        /// <param name="sheetIndex">The zero based worksheet</param>
        /// <param name="fileName">The full path to the Excel file</param>
        /// <returns>The exit code for the program</returns>
        static int ReadExcelCells(int sheetIndex, string fileName)
        {
            SpreadsheetDocument spreadsheetDocument;
            int exitCode = 0;
            using (spreadsheetDocument = SpreadsheetDocument.Open(fileName, false))
            {
                try
                {
                    workbookPart = spreadsheetDocument.WorkbookPart;
                    worksheetPart = workbookPart.WorksheetParts.First();
                    sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();
                    sheet = workbookPart.Workbook.Descendants<Sheet>().ElementAt(sheetIndex);

                    string userInput = string.Empty;
                    do
                    {
                        Console.Write("Please enter a cell id or q to quit:");
                        userInput = Console.ReadLine().Trim().ToUpper();
                        if (!userInput.Equals("Q"))
                        {
                            Tuple<int, int> indices = ValidateUserInput(userInput);
                            Cell selectedCell = GetCellAtIndices(indices);
                            string cellContent = ReadExcelCell(selectedCell);
                            Console.WriteLine($"Cell ({ indices.Item1 } / { indices.Item2 }): { cellContent }");
                            Console.WriteLine();
                        }
                    }
                    while (userInput != "Q");
                }
                catch (ArgumentException exception)
                {
                    PrintMessageAndExit(1, $"Argument exception: { exception.Message }");
                    exitCode = -1;
                }
                catch(Exception exception)
                {
                    PrintMessageAndExit(1, $"General exception: { exception.Message }");
                    exitCode = -1;
                }
                finally
                {
                    spreadsheetDocument.Close();
                }
            }
            return exitCode;
        }

        /// <summary>
        /// Read the content of the selected Excel cell
        /// IMPORTANT: Works with text only!
        /// </summary>
        /// <param name="cell">The selected cell</param>
        /// <returns>The content of the selected Excel cell</returns>
        static string ReadExcelCell(Cell cell)
        {
            CellValue cellValue = cell.CellValue;
            string text = (cellValue == null) ? cell.InnerText : cellValue.Text;
            if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
            {
                text = workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(Convert.ToInt32(cell.CellValue.Text)).InnerText;
            }
            return (text ?? string.Empty).Trim();
        }

        /// <summary>
        /// Get the Excel cell at the selected row and column indices
        /// </summary>
        /// <param name="indices"></param>
        /// <returns></returns>
        static Cell GetCellAtIndices(Tuple<int, int> indices)
        {
            if (indices.Item1 < 0 || indices.Item2 < 0) throw new ArgumentException("Indices out of bounds!");
            if (sheetData == null) throw new ArgumentException("SheetData must not be null");
            Row row = sheetData.Elements<Row>().ElementAt(indices.Item1);
            Cell cell = row.Elements<Cell>().ElementAt(indices.Item2);
            return cell;
        }

        /// <summary>
        /// Convert the column letter to a non zero based number
        /// Examples:
        ///   A  -->  1
        ///   Z  --> 26
        ///   AA --> 27
        /// </summary>
        /// <param name="columnAddress">The selected column name</param>
        /// <returns>The column as a non zero based number</returns>
        static int GetColumnNumber(string columnAddress)
        {
            int[] digits = new int[columnAddress.Length];
            for (int i = 0; i < columnAddress.Length; ++i)
            {
                digits[i] = Convert.ToInt32(columnAddress[i]) - 64;
            }
            int multiplier = 1;
            int result = 0;
            for (int position = digits.Length - 1; position >= 0; --position)
            {
                result += digits[position] * multiplier;
                multiplier *= 26;
            }
            return result;
        }

        /// <summary>
        /// Validate the user input for an Excel cell
        /// </summary>
        /// <param name="userinput">The user input</param>
        /// <returns>The zero based indices for row and column if user input is valid, null otherwise</returns>
        static Tuple<int, int> ValidateUserInput(string userinput)
        {
            Tuple<int, int> indices = null;
            Match match = CELLPATTERN.Match(userinput);
            if (match != null && match.Success)
            {                
                int columnNumber = GetColumnNumber(match.Groups[1].ToString());
                int rowNumber = Convert.ToInt32(match.Groups[2].ToString());
                indices = new Tuple<int, int>(rowNumber - 1, columnNumber - 1);
            }
            return indices;
        }

        /// <summary>
        /// Print messages and exit program if errorcode is zero or negative
        /// </summary>
        /// <param name="errorcode">The errorcode</param>
        /// <param name="message">The message to print</param>
        static void PrintMessageAndExit(int errorcode, string message)
        {
            Console.WriteLine(message);
            Console.WriteLine("Please press any key!");
            Console.ReadKey();
            if (errorcode > 0) return;
            Exit(errorcode);
        }

        /// <summary>
        /// Exit the program
        /// </summary>
        /// <param name="errorcode">The errorcode</param>
        static void Exit(int errorcode)
        {
            Environment.Exit(errorcode);
        }
    }
}

Kommentare:

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

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