C# :: Aufgabe #263
4 Lösungen

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:

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"; } } } } }

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; } } }

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. */

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); } } }