im looking for programm that reads Excel documents and can search for a word in that document thank you with this code am able to read all Cells but i want to add the function that i could search for a word that i input in(console.readLine)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
namespace ReadFromExcelComponente
{
class Program
{
static void Main(string[] args)
{
//lese den excel
Excel.Application ExcelApp;
Excel.Workbook workbook;
Excel.Worksheet worksheet;
Excel.Range range;
int row = 0;
int column = 0;
ExcelApp = new Excel.Application();
workbook = ExcelApp.Workbooks.Open(@"D:\Map", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
worksheet = (Excel.Worksheet)workbook.Worksheets.get_Item(1);
range = worksheet.UsedRange;
List<String> myList = new List<string>();
for (row = 1; row <= range.Rows.Count; row )
{
for (column = 1; column <= range.Columns.Count; column )
{
Console.WriteLine(" Coulmn Number: " column "--> " (range.Cells[row, column] as Excel.Range).Value2);
myList.Add(range.Cells[column].ToString());
}
}
Console.WriteLine(myList[2]);
Console.WriteLine(range.Cells[2, 2].Value);
workbook.Close(true, null, null);
ExcelApp.Quit();
Console.ReadKey();
}
}
}
CodePudding user response：
Try this:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
namespace ReadFromExcelComponente
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("Enter the word you want to search for:");
string SearchWord = Console.ReadLine();
//lese den excel
Excel.Application ExcelApp;
Excel.Workbook workbook;
Excel.Worksheet worksheet;
Excel.Range range;
int row = 0;
int column = 0;
ExcelApp = new Excel.Application();
workbook = ExcelApp.Workbooks.Open(@"D:\Map", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
worksheet = (Excel.Worksheet)workbook.Worksheets.get_Item(1);
range = worksheet.UsedRange;
for (row = 1; row <= range.Rows.Count; row )
{
for (column = 1; column <= range.Columns.Count; column )
{
if(range.Range(row, column).Value = SearchWord)
{
Console.WriteLine("Found word!");
break;
}
}
}
Console.WriteLine(myList[2]);
Console.WriteLine(range.Cells[2, 2].Value);
workbook.Close(true, null, null);
ExcelApp.Quit();
ExcelApp = null;
Console.ReadKey();
}
}
}
CodePudding user response：
When “your code” starts the Excel application and opens an Excel file… then YOUR CODE becomes responsible for “releasing” the COM objects it creates… namely… the workbook and the Excel application.
If you do not release those objects, as your current code doesn’t, then those objects will remain in memory as active objects and basically never get released. Even if your code closes the file and Excel app… YOUR CODE still has to explicitly “release” the COM object. Granted, if you reboot the machine or even quit the application… then those objects may/will get released.
Fortunately, it is not difficult to manage the Excel resources. One fairly straight forward approach is to use a try/catch/finally statement to manage the resources. The try portion is where we put ALL the code that deals with the Excel file(s). The catch portion is used to obviously catch the exceptions. And the finally portion is where we would put the code to “release” the Excel COM objects.
With this approach, we should be able to rest a little easier knowing that the COM objects will STILL be released… even if the code crashes while working with the Excel file.
Next, I am aware that sometimes you must use Interop to work with Excel files and I have used it often and know it can be awkward. Not to mention that Interop is notoriously SLOW especially when dealing with large Excel files. My point is that if you DO have a choice, I would highly recommend a third-party Excel library. There are many to chose from and some are free.
Getting down from my soap box…
Looking at your code, there is one thing I am not following when the code gets the UsedRange from the Excel worksheet…
range = worksheet.UsedRange;
This range is a basic Excel Range object containing all the “used” cells in the worksheet. So far so good…
But… later we see a double for loop through the range’s rows and columns. The code then puts the values of each cell into (List<string>) myList. This will work however it seems unnecessary. Even if you want to search for a word in all the cells, there is no need to put the cell values into a list… you ALREADY have the cells in the range variable! In other words, instead of adding the words to a list, just loop through range and search for it.
When I say you have the range to work with, this means the Excel.Range object. And you already know you can easily loop through that range object as your code demonstrates… HOWEVER… IF range is large, THEN, you will note a significant performance hit. Bottom line is that “looping” through Excel.Range’es is expensive and slow.
Basically we want to minimize any looping through the range. One possible solution is to cast the range into a two (2) dimensional Object array. THEN, do all the looping through the Object array, which should be significantly faster and it makes the code a little easier to read.
Therefore, the line of code…
range = worksheet.UsedRange;
may end up looking like…
range = worksheet.UsedRange;
object[,] worksheetValues = (object[,])range.Cells.Value;
Now, we can loop through the worksheetValues array instead of the range itself.
This “should” replace the mylist variable as it is not needed… all the values are in the worksheetValues array.
Now that we have a simple object array to “search” through… then all we need is a search term to look for. If we get the search term from the user, then we could create a method that takes that search term and the array to search through and return all the X and Y coordinates of the cells that the search term matched. This method may look something like…
private static List<Point> FindWordInArray(string target, object[,] data) {
List<Point> foundIndexes = new List<Point>();
for (int row = 1; row <= data.GetLength(0); row ) {
for (int col = 1; col <= data.GetLength(1); col ) {
if (data[row, col] != null) {
if (data[row, col].ToString().Equals(target)) {
foundIndexes.Add(new Point { X = col, Y = row });
}
}
}
}
return foundIndexes;
}
public class Point {
public int X { get; set; }
public int Y { get; set; }
}
I am aware the Point class may not be needed and is only there to simplify the example.
Below is a small yet complete example of what is described above. This includes setting up the try/catch/finally code along with releasing the COM objects.
NOTE: Excel starts array indexing at 1. Therefore the object array worksheetValues will start at index 1.
Excel.Application ExcelApp = null;
Excel.Workbook workbook = null;
Excel.Worksheet worksheet= null;
Excel.Range range = null;
string searchWord = "";
try {
ExcelApp = new Excel.Application();
workbook = ExcelApp.Workbooks.Open(@"D:\Map", 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
worksheet = (Excel.Worksheet)workbook.Worksheets.get_Item(1);
range = worksheet.UsedRange;
object[,] worksheetValues = (object[,])range.Cells.Value;
Console.WriteLine("There are " worksheetValues.GetLength(0) " Rows and " worksheetValues.GetLength(1) " Columns");
for (int row = 1; row <= worksheetValues.GetLength(0); row ) {
Console.Write("Row " row ": ");
for (int col = 1; col <= worksheetValues.GetLength(1); col ) {
if (worksheetValues[row, col] != null) {
Console.Write(worksheetValues[row, col]);
}
else {
Console.Write(" - ");
}
}
Console.WriteLine();
}
Console.WriteLine("Type the word you want to search for...");
searchWord = Console.ReadLine().Trim();
List<Point> foundItems = FindWordInArray(searchWord, worksheetValues);
foreach (Point point in foundItems) {
Console.WriteLine("Found at Row: " point.Y " - Col: " point.X);
}
}
catch (Exception ex) {
Console.WriteLine("Excel error: " ex.Message);
}
finally {
if (range != null) {
Marshal.ReleaseComObject(range);
}
if (worksheet != null) {
Marshal.ReleaseComObject(worksheet);
}
if (workbook != null) {
workbook.Close(true, null, null);
Marshal.ReleaseComObject(workbook);
}
if (ExcelApp != null) {
ExcelApp.Quit();
Marshal.ReleaseComObject(ExcelApp);
}
}
I hope this makes sense and helps.