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
