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

#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

von MarkusH. (440 Punkte)
- 29.09.2019 um 16:35 Uhr

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
Seite 1 von 0
1