Using Visual Studio and Excel is actually quite fun – the Visual Studio advanced environment provides a bit more possibilities than the Visual Basic Editor, built-in Excel. In this article, about a year ago I have shown how to loop through Excel files with a C# program. The task today is to build a small program, which opens an Excel file, searches within for a given word and returns information about the column and the row on which the word was found.
Thus, as far as the console applications become rather boring and I cannot make fancy screenshots with them, I have decided to build a small project with Windows Form. This is how the interface looks like:
No fancy UI/UX design, simply a browse button to select the path of the file, which is displayed through a label. Then the word, which should be looked for is entered in the textBox and once the “GO” button is clicked, the excel file is opened and the results are displayed:
Of course, it is possible not to find the word we are searching for. Thus we get this:
So far so good. The whole project is accessible in GitHub here, but still some of the code follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 |
using System; using System.Windows.Forms; using Excel = Microsoft.Office.Interop.Excel; namespace NewWindowForm { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) { lblResult.Text = "Please, select a file."; lblPath.Text = ""; } private void button1_Click(object sender, EventArgs e) { Excel.Application excel = null; excel = new Excel.Application(); excel.Visible = true; Excel.Workbook wkb = null; wkb = Open(excel, lblPath.Text); Excel.Range searchedRange = excel.get_Range("A1", "XFD1048576"); Excel.Range currentFind = searchedRange.Find(tbInput.Text); string displayResult = ""; if (currentFind != null) { displayResult = "Found at \ncolumn - " + currentFind.Column + "\nrow - " + currentFind.Row; } else { displayResult = "The searched string \"" + tbInput.Text + "\" is not found."; } lblResult.Text = displayResult; wkb.Close(true); excel.Quit(); } public static Excel.Workbook Open(Excel.Application excelInstance, string fileName, bool readOnly = false, bool editable = true, bool updateLinks = true) { Excel.Workbook book = excelInstance.Workbooks.Open( fileName, updateLinks, readOnly, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, editable, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); return book; } private void button2_Click(object sender, EventArgs e) { OpenFileDialog choofdlog = new OpenFileDialog(); choofdlog.Filter = "All Files (*.*)|*.*"; choofdlog.FilterIndex = 1; choofdlog.Multiselect = true; if (choofdlog.ShowDialog() == DialogResult.OK) { lblPath.Text = choofdlog.FileName; lblResult.Text = "File is selected. Now enter a word and press the \"GO\" button."; } } } } |
Enjoy the code!