Thanks for the tutorial. but I got this error: System.Runtime.InteropServices.COMException: 'Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).' Why?
Hi, It doesn't work at the line 22. It say "Cannot implicity convert type 'object' to 'Microsoft.Office.Interlop.Excel.Worksheet'. An explicit conversion exists (are you missing a cast?) :( Do you have any idea ?
I wonder why it sees it as object, older library maybe. In any case just cast it: ws = (Worksheet)wb.Worksheets[Sheet]; or: ws = wb.Worksheets[Sheet] as Worksheet;
@@钱钧陶-l8z Add open file dialog then www.c-sharpcorner.com/UploadFile/mahesh/openfiledialog-in-C-Sharp/ and take it's path as the target file to avoid any confusion.
Thanks for the video!. I'm starting at c# programming and there is something that i don't understand. why "wb" and "ws" don't have a "new" instance? aren't they an object?
Objects don't have to be declared as new, they can be assigned straight from the initialization. Let's say you have a string that you want to have the value of "text". You can do String temp = new String(); temp = "text"; or you can do it straight away: string temp = "text"; You have created the object of type string temp with the "string temp" and now you only have to assign a value to it.
I follow your step but when i run it's says System.InvalidCastException: 'Unable to cast COM object of type 'Microsoft.Office.Interop.Excel.ApplicationClass' to interface type 'Microsoft.Office.Interop.Excel._Application'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{000208D5-0000-0000-C000-000000000046}' failed due to the following error: Error loading type library/DLL. (Exception from HRESULT: 0x80029C4A (TYPE_E_CANTLOADLIBRARY)).'
Remove using Microsoft.Office.Interop.Excel from the main class. Problem is you are calling custom class Excel and it's confusing it with dll Excel class.
Thanks for the video! Could anybody explain to me please how can I close process named "excel" correctly in my task manager? After closing of my application I've got process named "microsoft excel". If I run my app several times I get several unclosed processes "microsoft excel". Actually I use my excel table once to copy data to array into C# and I don't need use excel anymore up to next launch of my application.
This is one of the best tutorials I've found and has gotten me the closest to a working excel function! Thank you very much. I'm hoping someone can answer my question though because it doesn't make sense to me. If I name the class "ExcelClass" rather than just "Excel" like in the tutorial OR if I change the "public Excel(string path, int sheet)" method to "public ExcelOpen(string path, int sheet)" I get an error that says Method must have a return type. Why would changing the name of the class or method throw this error?
Because when you have a class named Excel and a method named Excel that method is a constructor of the class and doesn't require a return type. If you want to rename ether of those just add void before your method name.
Nice tutorial, But you didn't close the workbook, it can cause a problem even after closing the application. The solution is: make the wb variable public and use the wb.close() method after printing the message.
I have the same problem. After i close the app a window appears which opens the excel file. I tried the wb variable public and close like you said, but i got this error as exception : System.Runtime.InteroopServices.COMException: '0x800401A8'. I tried also other functions for release the object like System.Runtime.InteroopServices.Marshal.ReleaseObject(wb) but i got stuck. I found a function which closes all excel files ( //using System.Diagnostics; Process[ ] excelProcs = Process.GetProcessByName("EXCEL"); foreach(Process proc in excelProcs) { proc.Kill(); } ) but it is not recommended
Hi there! It's really interactive tutorial. I have a question, I have an error said that "Program does not contain a static 'Main' method suitable for an entry point". Also, I am a little bit confused where I need to put my excel document, maybe you could answer it?
Excel file goes in Documents folder if you're not changing the path. As for the error ether create a Forms project that auto generates main in Program.cs or put the code for Load method inside Console projects main method. Can't mix and match console and form projects.
Hi, experienced but rusty programmer here... The program runs but no message. When I debug/trace the steps in Form1.cs, it never gets past InitializeComponent(); it goes to program.cs (Main) and ends after Application.Run(new Form1())
Like I said, Rusty...I was missing code in the initializecomponent from the form1.designer.cs. I just used the events window to make sure it loaded (lazy lol).
@@pop2mcar Piece of advice when working with forms, don't stacktrace from Program.cs, it's there only to start the form class as a single thread apartment. Constructor is usually there only to set constants and initialize controls/events. In this case start from Form_Load event, it triggers when all controls in the form are loaded. Actually always start to trace from an event rather than a constructor when working with a form, be it Load, or Click etc.
Great tutorial.. But I am having a problem.. I can't get the MessageBox to show up.. I have gone over the code 1000 times now, it is exacly the same as shown in the video.. I get no errors when compiling and the form1 window pops up, but not the message box. I have tryd moving the TEST.xlsx to diffrent locations and add it to the path line.. Any clue? Would like to continue with the tutorials, but not until I know it works for me xD Ps. Also tryd running VS as admin.
What you can do is instead of MessageBox.Show(excel.readCell(0,0)) do string temp = excel.readCell(0,0); MessageBox.Show(temp); Breakpoint on messagebox line and see if temp has a value (hover over it with a mouse) of the excel cell. Depending on that we can see what's going on. (Just a heads up, path for file is C:\Users\"Current User"\Documents\TEST.xlsx")
Good question. You're not instantiating an interface, you're instantiating an abstract class inheriting it (or at least I think it's an abstract class, been a while since I looked it up) and VS is just reporting it as interface
files-cdn.cnblogs.com/files/fan0136/Microsoft.Office.Interop.Excel.rar you can use this link to download the .dll, add this to the solution as reference and the Interlop namespace will be available
Hey Bospear, I followed your every step it was osm code but i have issue in form1 backend code that is it is not accepting Excel as class for excel object error message "Excel is alias but it is used as type" plz guide me
Hi , thank you for sharin your knowledge. I've tried the above. Everything works but i cant receive a list of items. Do i need to parse the readcell to a list? I thought the int i++ and j++ would return each value when not null and show it in the messagebox. Hope you can help out.
value2 returs all values from a specific cell, regardless if it's null (returns as empty string), if you want to grab everything from a sheet one of the later tutorials shows how to do it, I think it's No5, you also have in the comment how to grab only used cells
I'm getting the error: System.InvalidCastException HResult=0x80004002 Message=Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Office.Interop.Excel.Workbook'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{000208DA-0000-0000-C000-000000000046}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)). Source= StackTrace:
Yea, my bad. In later videos I made the close statement but that one is bad as well. Just put a new method in the Excel class public void Close() { wb.Close(0); } and than call it after the read from the form. As for file you opened right now just kill excel.exe processes from task manager and that will clear it up.
@@TheBospear I placed above code in the Excel class without any errors. But I added below in the Form1.cs and "Close()" does not appear to recognized as the Excel class and Close method but instead just closes the form. private void button2_Click(object sender, EventArgs e) { Close(); }
@@slazper Object oriented programming, you need to call a method in the object (instance of class) you want. Close in Form is upon Form object, Excel excel = new Excel(...); excel.Close(); works upon Excel object. I suggest you look in to C# and Object oriented programming tutorials if you want to make a c# app with this first. It's a really big gap in knowledge you need to fill if comments here are to be effective.
Access to interop objects in WPF is the same as Forms, I guess that office version, and interop lib with it, is different. I suggest you read following two: learn.microsoft.com/en-us/dotnet/csharp/advanced-topics/interop/how-to-access-office-interop-objects stackoverflow.com/questions/68981380/load-the-excel-file-which-i-open-using-dialog-box-in-wpf-into-datagrid-when-i-cl
When I try to run Form1.cs, I receive the following popup window: "A project with an Output Type of Class Library cannot be started directly. In order to debug this project, add an executable project to this solution which references the library project. Set the executable project as the startup project." It seems like I created Form1 as the wrong type of file. I know this is too basic of a question to be asking here, but do you have any recommendations of how to create it properly? I created it through File -> New -> Visual C# class
File -> New -> Project -> Windows Forms Project (or Windows Forms App depending on the version). That also answers your question about lines 1-12, auto generated code.
Sir I do have a problem, I put the line of path as this Excel1 excel = new Excel1 (@"C:\Users\farshidmoosavi\Desktop\Revit API\first.xlsx", 1); and it says we cannot find the address, althogh the address is correct
You got to loop for it, row by row, cell by cell. Treat it like a matrix. Only problem you can have with it is date and time writing and reading. I'll do a video soon on it and post the whole class to github.
You might want to go with english since google translate isn't all that good. Default path is Documents or My Document on older windows versions. Otherwize you can make a custom path to any location.
@@zanekross9649 Sorry for the late response. This was a very old project I was in and I don't really remember. I hope you find @Adomas B 's respnse convenient
_Application is an object within Interop.Excel library, just make sure you have all using statements. If it still persists you can do _Excel.Application excel = new _Excel.Application();
@@TheBospear Interesting, I had my Excel class named something other than "Excel" so that's why it caused errors. I named it "Excel_Stuff.cs" because it was just a learning exercise. Is there a reason this class has to be named so definitively?
@@coyotemoon722 It shouldn't, I guess it came to some naming polymorphism between interop library and the class. I'll check it out one of these days to see what's up.
Hey Bospear, I just followed this tutorial, and everything runs great except that the MessageBox.WriteLine(excel.ReadCell(0,0)); line doesn't pull up any message. I have a Test.xlsx file with a string in the correct box and have tried to output this message to the console instead, but still haven't gotten anything to pop up. Any ideas on what might be going wrong here?
@@TheBospear I placed Text.xlsx in the documents folder and put a breakpoint inside the ReadCell method, but it gave me the same result as before. When I put a breakpoint in the Program.cs file, it goes into the Application.Run(new Form1()) line, then inside the Form1.cs file it runs through the Form1() function and ends. Should I be making a call to Form1_Load inside the public Form1() function? And if so, what would be the parameter for the EventArgs e? Thank you for your help!
@@TheBospear I tried double-clicking and unfortunately got no result. Here is the code I have Form1.cs: 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; namespace ImportExcel2 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) //File name, worksheet number { OpenFile(); } public void OpenFile() { Excel excel = new Excel(@"C:\Users\Conno\Documents\Test.xlsx", 1); //Console.WriteLine(excel.ReadCell(0, 0)); //MessageBox.Show(excel.ReadCell(0, 0).ToString()); MessageBox.Show(excel.ReadCell(0, 0)); } } } Excel.cs using System; using System.Collections.Generic; using System.Text; using Microsoft.Office.Interop.Excel; using _Excel = Microsoft.Office.Interop.Excel; namespace ImportExcel2 { public class Excel { string path = ""; _Application excel = new _Excel.Application(); Workbook wb; Worksheet ws; public Excel(string path, int Sheet) { this.path = path; wb = excel.Workbooks.Open(path); ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[Sheet]; } public string ReadCell(int i, int j) //Row, Column { i++; j++; if (ws.Cells[i, j] != null) return ws.Cells[i, j].ToString(); else return ""; } } } Program .cs using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; using System.Windows.Forms; using ImportExcel2; namespace ImportExcel2 { static class Program { /// /// The main entry point for the application. /// [STAThread] static void Main() { Application.SetHighDpiMode(HighDpiMode.SystemAware); Application.EnableVisualStyles(); Application.SetCompatibleTextRenderingDefault(false); Application.Run(new Form1()); } } } In the Excel.cs file, I do not make a call to the Value2 function since I don't have that or think that I need it.
Why in .NET 3.5 or less than my app don't read Excel file this's my mistake:" Cannot implicitly convert type 'object' to 'Microsoft.Office.Interop.Excel.Worksheet'. An explicit conversion exists (are you missing a cast?)". Thank you. Guy
I have a problem with the path. Either if I write 'string path = "";' or the same with the path of the TEST.xlsx file, it still doesnt compile, giving me the error: 'System.Runtime.InteropServices.COMException: ''TEST.xlsx' could not be found. Check the spelling of the file name, and verify that the file location is correct.' and marking ' wb = excel.Workbooks.Open(path); '. Can you/anyone help please ?
@"Test.xlsx" is in my documents for the excel interop, not in the root folder. I guess that's the problem. Try with a global path like @"C:\ExcelTestFolder\Test.xlsx"
Thanks for the reply. I now created a folder straight in C:\ called 'excel test folder' and put the 'TEST.xlsx' document in it. In the program i wrote this: 'string path = @"C:\excel test folder\TEST.xlsx";' and it doesnt work. I also tried typing two backslashes (\\) instead of one but I always end up with the same error... Im almost losing it... In any case, thanks for the video and the reply in the first place.
in the part where it is done the pucblic Excel etc etc i have a problem. Visual studio said that i need a return, but u dont have a return in this part. What i can do?
he has return type in the method that was created as string with two returns in the body probably doesn't need the else part but that a different story.... for the constructor which is the public Excel , it is called in the main method with an initialization using new So in the static void Main{string [ ] , args) { Excel excel = new Excel("pathOfFile", 1);} //that is in the program.cs file, I would first make sure you're not trying to call the Excel object like a method in the program file, then check that you wrote the constructor correctly.
These Tutorials are really helpful. Thank you. Does anyone have any idea how can i read the background color of a cell in Excel? I need this, so i can make the same color for the background of the textbox.
hey I did everything as you showed in the video but for some reason its not working. When I run the program an empty default Form Window is being displayed but nothing else. The initializeComponent() method is being executed which looks like this: private void InitializeComponent() { this.components = new System.ComponentModel.Container(); this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font; this.ClientSize = new System.Drawing.Size(800, 450); this.Text = "Form1"; } after that the program just finishes without executing the other methods like OenFile() etc. Can you please tell me what Im missing here?
my main method in prgoram.cs looks like this: static void Main() { Application.EnableVisualStyles(); Application.SetCompatibleTextRenderingDefault(false); Application.Run(new Form1()); } Form1 looks like this: public partial class Form1 : Form { public Form1() { InitializeComponent(); } public void Form1_Load(object sender, EventArgs e) { OpenFile(); } public void OpenFile() { Excel excel = new Excel(@"emails.xlsx", 1); MessageBox.Show(excel.ReadCell(0, 0)); } }
Hey Bospear, I haven't been able to produce the "TEST" line, I have an excel document saved to my desktop. I have no build errors or anything, but the fact that this was built in WinForms keeps producing a blank Form field. I have "MessageBox.Show(excel.ReadCell(0,0)); " and all the code line for line, but the Form1.designer page was never shown, so I have no reference here to see what you've done on the design. Please help. Thanks, Jamie
Done nothing to the design, you can run the same code on console or dll (which is kind of a plan at the end of the series), but the path shown in the video is targeting Documents, so check if you have a correct path for the file you have.
Hey man thanks for the maintenance you do on here. I realized a piece of code was misplaced, had to move it and it was good to go. Your explanation for document root path is great, thank you.
check this code here is the problem solved: ---- using System; using Microsoft.Office.Interop.Excel; using _Excel = Microsoft.Office.Interop.Excel; namespace mysheet { class Excel { string path = ""; _Application excel = new _Excel.Application(); Workbook wb; Worksheet ws;
Hi, i have two question 1) This function works only project on NET. Framework; is it correct? Because with a project in NET. Core right at the start i have an expection that say it can't open/recognize the file (other 'problem' is that the compiler want to force me the cast on worksheet) 2) Could i specify the worksheet that i want to read? I created more worksheet on excel file, but it opens only the first (obviously i try to change the value 'Sheet' when i create the object Excel)
@@TheBospear Yes, excatly, my mistake!. Because i am working with two excel files. The second worksheet was added in only one of them. In my program i was calling the other file. Anyway thank you again !
hello i have a question,i imported excel file to windows forms application,and i need to import graph from that same file,but i don't have an idea how to do it,if you could give me any suggestions i would be realy grateful.
Never tried, honestly I only work with data. I guess you imported data that make that chart, so you can use that data to make a chart in the form. msdn.microsoft.com/en-us/library/dd489237.aspx
hi thank you very much for the help the program is perfect but I have a problem if the cell contain a word for example "test" everything is perfect but if the cell contain number the I have this error : Microsoft.CSharp.RuntimeBinder.RuntimeBinderException: 'Cannot implicitly convert type 'double' to 'string'' can you help me;
Microsoft at one point decided that we have to be protected from our own conversion..... so just do return (String)ws.Cells[i,j].Value2 or return ws.Cells[i,j].Value2 as String.
No I am asking you I have written code like Application obj= new application(); And I have use namespace is Microsoft.Office.interop.excel. When my cursor coming to application () method it is throwing exception please reply
@@rajeshkale7849 Ok, first of, namespace should be name of the project, or name of a part of the project, you cannot use a library name as a namespace. Second, I can't know what problem you have in a code I haven't written or seen. Ether follow the tutorial or post your custom code.
Hi, im getting the following exception: 'Could not load file or assembly 'office, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'. The system cannot find the file specified.' I have excel version 2011 and it didn't work, installed 2013 afterwards and it still doesn't work, would appreciate any help, thanks!
Add the location of the network drive and path to the file in the path variable. So it's exactly the same way you would do on local. If your network drive requires admin rights access run the app or VS as admin.
thank you very much for this i have another question pls: i did create a simple form with a button, i need this button to open a folder and this folder exist in one of our servers do you have this code pls many thanks
@@microsecure2612 If you want to choose a file from that folder to load the path do the open file dialog with network location as the default (microsoft has example in documentation, can't write it out on a phone). If you just want to open in explorer I think System.Diagnostics.Process.Start targeting the folder would do the trick.
Separate classes based on their logical wholes. Program.cs is a default class that any c# project uses, Excel.cs is made to handle just Excel calls. But you shoot look in to tutorial about Object oriented programming a bit more in depth before continuing, it will do you more good.
Mhh, for me this didn't work with the "private void Form1_Load(object sender, EventArgs e) { OpenFile(); }" But when I called OpenFile() from "public Form1() { }" just above it, it was finally executed correctly. And I have no idea where you had to put the excel file to be able to call it by (@"TEXT.xlsx", 1), I had to put in the complete path...
Think you're missing initialize in the Form constructor, which is strange since it's a default call when you make a form. As for location it's in Documents.
Worked with 14 when I started making excel automaton. Don't know about older than 14 but newer will work. Also there should be NuGet packs for 14, 15 and 16 which should be a free download.
Thanks for the clear explanation SIr. I am new to C# programming and starting using visual studio, I have followed the procedure but I cannot get the expected result, do I have to type anything in the Main() of program.cs? Thanks!
No, when you use forms in C# you generally don't do anything with Main function, unless you need to pass outside arguments. What are you getting as a result?
I can get nothing from the program, I think it directly runs the empty Main(). Firstly I created Console application which is the program.cs, then I added a class(Excel.cs) and Form with the "Load" event handler, but I can get nothing. Thank you so much sir!
Yea, that will happen if you make a console application and add form later. So just add the main form at the start. Write in Main() this: Application.EnableVisualStyles(); Application.SetCompatibleTextRenderingDefault(false); Application.Run(new Form1()); new Form1 is the name of the start Form you made.
Sir, do you have any idea on this error happens in the code "wb = excel.Workbooks.Open(path);" : "Cannot convert COM object 'Microsoft.Office.Interop.Excel.ApplicationClass' to interface type 'Microsoft.Office.Interop.Excel._Application" (p.s. The message is not directly copied from visual studio as I am not using English language pack, I tried to translate the error message into English) Thank you so much!
First you need to create a Windows Forms Application, in which you can double-click on the main form in the form designer to have your Form1_Load() method to be automatically generated. And you need to store your Excel file into your Windows documents folder. You shall also implement a Close() method in your Excel class in order to avoid the presence of a residual Excel instance and "~$Test.xlsx" file like this : public void Close() { if (wb != null) { wb.Close(); } }
Hello Sir, I am getting an error: Missing partial modifier on declaration of type 'Trial.Excel'; another partial declaration of this type exists. "Please help me!"
Search through solution for that type name, you have declarations named the same thing in another class, or skip VS2013 and go to VS2017. Also check docs.microsoft.com/en-us/dotnet/csharp/programming-guide/classes-and-structs/partial-classes-and-methods just so that you have full documentation on it.
Thanks, in my case Value2 returns an object not an string, Object valor = ((_Range) ws.Cells[i, j]).Value2; --- using _Range = Microsoft.Office.Interop.Excel.Range; .... In the instance parte ToString() or whatever. thanks for the video.
System.IO.FileNotFoundException: 'Could not load file or assembly 'office, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'. The system cannot find the file specified.' I tried adding Microsoft.Office.Interop.Excel.dll manually from browsing in the reference manager and also via installing using Install-Package from nuget package manager. Still getting the same error. Confused!! Please HELP! Thanks.
@@TheBospear Thanks a lot for a quick reply. Didnt expect a reply from you. I figured out what the issue was. I was creating a new project with Console App(.NET CORE), I was supposed to do it with .NET Framework. I just finished with all your videos. It was short and really helpful. Thanks
Doubt it, more likely it's the library version, Microsoft really did a number on interop libraries (hence the need for these videos). What are the errors and what version interop dll are you using?
There could be a number of things, different library, com bug, wrong object lookup etc. post your code here and let me take a look to eliminate most of the possibilities
@@TheBospear I can see that you have first another window that is not "Form1" and after you close this, it shows you "Form1". I only had the "Form1" window that does not show us nothing, do you follow me?... I tried at visual studio 2016 is the same thing...
Main method is an entrance point to any application. In fact using any VS should have generated main method when creating a console application. What's featured in the tutorial however is a Windows Form Application, it too has a main method but it's auto generated in the Program.cs and isn't shown in the video.
@@TheBospear Thanks for the replay , i fixed it. However i changed the code a bit. I'm trying to compile the data from all workbooks in a directory to a single worksheet in a blank workbook. The data of each worksheet needs to be appended to the previous data that has been pasted. I looked up the internet and managed to count a worksheet's total rows. But that's not really what i need. Here is the code. static void Main(string[] args) { Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook sheet = excel.Workbooks.Open(@"C:___________________.xlsx"); Microsoft.Office.Interop.Excel.Worksheet x = excel.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet; Excel.Range userRange = x.UsedRange; int countRecords = userRange.Rows.Count; int add = countRecords + 1; x.Cells[add, 1] = "TotalRows" + countRecords; sheet.Close(true, Type.Missing, Type.Missing); excel.Quit(); }
I guess i'm asking for a c# answer to this popular vba question on stackoverflow. stackoverflow.com/questions/43112239/copy-and-paste-data-from-multiple-workbooks-to-a-worksheet-in-another-workbook?rq=1
@@saidalabed9417 For some reason I didn't get the comment notification. Since you have row numbers, all you need to do is loop through the file names in a folder, loop through the sheets until you find the name, read and write in another file. Listing files is done through Directory.GetFiles, while reading sheet names is done with worksheet.Name
for(int i = 0; i < 10; i++) { excel.ReadCell(i, 0); } You can also select a specific range and read all cells from it, but I will make tutorial on it later, sometime january if nothing else happens. In the meantime here's a stackoverflow link for it: stackoverflow.com/questions/2627343/read-all-the-cell-values-from-an-excel-range-in-c-sharp
sir is it possible to open microsoft publisher also using visual studio 2017? coz i need to print the data in visual studio 2017 using publisher. please help me TIA
yes i know but i dont know how to use it. is there any alternative way for me to print the data from database coz i need to print after searching it, TIA
Here's a couple of links that might help: Opening and exporting a file. csharp.hotexamples.com/examples/Microsoft.Office.Interop.Publisher/Application/Open/php-application-open-method-examples.html Reading and manipulating text. www.vbforums.com/showthread.php?554733-Find-and-replace-text-in-MS-Publisher-file Last one is in VB, but interop works the same so you can rewrite it in c# in few minutes.
This is kind of a specific tutorial, made to show people how to use interop at the time when microsoft didn't have proper documentation for it. If you want to count rows/columns as per new microsoft documentation TargetWorksheet1.UsedRange.Rows.Count - 1; same for columns. As for the database part that's a gigantic question, and I suggest you look up C# integration for the specific database you need.
I don't even know how to get to this point? what type of project should i create? is it c# console project ? or is it winforms project? or is it something else? how to get that excel.cs thing in solution explorer?
@@TheBospear thanks for your reply. I created a new project in VS. selected winforms project in c#. Right clicked the project name in solution explorer and added new class and renamed it excel. Had to google a bit. But i think i am in the right path because the files and code in my project look just like yours now. I created the excel file named Test in my VS project folder. Followed along all the coding. Ran the code. Shows tons of error. Trying to fix the code, uppercase, lowercase, space, underscore what not. Turns out its hard to even copy the code when i dont know what those syntax are doing.
Event handler for Form Load event. Do not attempt to just copy the code without basic C# knowledge, you will just give your self a headache. Do a basic C# tutorials first, it shouldn't take more than a week or two, then start making stuff.
Check References for Microsoft.CSharp reference, if it's there remove it then add it back if it's not just add it. That error is always tied to Microsoft.CSharp.dll as far as I've seen.
@@TheBospear Yes, I have the exactly labeled it 'Testfile' and when I go to properties > security, it says Object name: C:\Users\adoma\Documents\Testfile.xlsx, yet console still throws out an error : "System.IO.FileNotFoundException: 'Could not load file or assembly 'office, Version=15.0.0.0, Culture=neutral, PublicKeyToken = (My 64 bit hash) '. The system cannot find the file specified.'
@@Adomas_B You might have wanted to lead with the error, it's not excel file that it cannot find, it's your excel interop dll, or more likely, one of it's dependencies. Reinstall Excel and make sure versions match.
File -> new -> project -> windows form, and name the project, right click in solution explorer add->Class, and name it excel. I do suggest that you look in to some basic visual studio tutorials first, this series wasn't made to teach basics of programming but to help out junior developers to work with excel in c#.
Thank you! Another question: can I find out the count of non-empty cells in a column? I need to take a random cell in a column: int Rand = R.Next(1, ???);
Sadly it does. As far as I can see Excel Object Library is not supported on Mac and by Microsoft OSX is not suitable environment for interchanging COM objects. Sorry.
I'm getting the error: System.IO.FileNotFoundException HResult=0x80070002 Message=Could not load file or assembly 'office, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'. The system cannot find the file specified. Source= StackTrace:
@@TheBospear hiii bospear! can you give me your email? im working on a specific App that deals with excel values and i have no idea from where to start,,, i really apreciate your help thanks
Thank you for the video BosPear ! How do I read a int or double? When i put a number in an excel cell, i get an error "cannot convert double to string". I want to get values from excel and do calculation in my program with the values.
If you're calling it from Main (I'm guessing you did a console app) you need to ether make an object first or make the method you're calling a static method.
Hey, I am trying to make this "easy " project but I got stuck because after doing everything by you, it not working :/. Can you help me? I can´t find any mistake there. Thanks
@@TheBospear so Excel excel = new Excel(@"TEST.xlsx", 1); stop everything( I tried Messagebox.show("Test 1"); before and after (test 2) this command line and Box "Test 1" showed but "Test 2" was "dead" (didn´t show up). After commenting this part, Test 1 + Test2 showed up. :/
The easiest way availlable on the internet ! A big thank from a french student !
Hi! Thank you! How to close excel correctly? Even if I close VS after this code, my excel file doesn't open for editing.
Thanks for the tutorial. but I got this error:
System.Runtime.InteropServices.COMException: 'Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).'
Why?
I am also getting the same exception and i am also stuck on it.
Hi,
It doesn't work at the line 22. It say "Cannot implicity convert type 'object' to 'Microsoft.Office.Interlop.Excel.Worksheet'. An explicit conversion exists (are you missing a cast?) :(
Do you have any idea ?
I wonder why it sees it as object, older library maybe. In any case just cast it: ws = (Worksheet)wb.Worksheets[Sheet]; or: ws = wb.Worksheets[Sheet] as Worksheet;
@@TheBospear " ws = (Worksheet)wb.Worksheets[Sheet];" fixed the problem
@@luisurenaalex2183 thx dude it's worked
@@TheBospear Thanks!
We have all had problems with path=" ". But the tutorial served me. Thank you
Blank path leads to Documents, you can just enter @"C:\Your Path\Test.xlsx" and target where ever you want.
@@TheBospear I have tried @"C:\path\to\my\excel.xlsx" but it just doesn't work. VS says it can't find the specific file. .NET Core 3.1 WPF
@@TheBospear I'm using Office 16. It seems like the package only applies to Office 15.
@@钱钧陶-l8z Add open file dialog then
www.c-sharpcorner.com/UploadFile/mahesh/openfiledialog-in-C-Sharp/
and take it's path as the target file to avoid any confusion.
I need to create a small application for 2 different department. In order to no run the C#. is that possible?
Thanks for the video!. I'm starting at c# programming and there is something that i don't understand. why "wb" and "ws" don't have a "new" instance? aren't they an object?
Objects don't have to be declared as new, they can be assigned straight from the initialization.
Let's say you have a string that you want to have the value of "text". You can do String temp = new String(); temp = "text"; or you can do it straight away: string temp = "text"; You have created the object of type string temp with the "string temp" and now you only have to assign a value to it.
@@TheBospearI clearly understand now, thank you so much!
I follow your step but when i run it's says
System.InvalidCastException: 'Unable to cast COM object of type 'Microsoft.Office.Interop.Excel.ApplicationClass' to interface type 'Microsoft.Office.Interop.Excel._Application'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{000208D5-0000-0000-C000-000000000046}' failed due to the following error: Error loading type library/DLL. (Exception from HRESULT: 0x80029C4A (TYPE_E_CANTLOADLIBRARY)).'
Remove using Microsoft.Office.Interop.Excel from the main class. Problem is you are calling custom class Excel and it's confusing it with dll Excel class.
@@TheBospear I got this same problem but ı cant solved
Thanks for the video! Could anybody explain to me please how can I close process named "excel" correctly in my task manager? After closing of my application I've got process named "microsoft excel". If I run my app several times I get several unclosed processes "microsoft excel". Actually I use my excel table once to copy data to array into C# and I don't need use excel anymore up to next launch of my application.
What is on lines 1-12 of Form1.cs?
This is one of the best tutorials I've found and has gotten me the closest to a working excel function! Thank you very much. I'm hoping someone can answer my question though because it doesn't make sense to me. If I name the class "ExcelClass" rather than just "Excel" like in the tutorial OR if I change the "public Excel(string path, int sheet)" method to "public ExcelOpen(string path, int sheet)" I get an error that says Method must have a return type.
Why would changing the name of the class or method throw this error?
Because when you have a class named Excel and a method named Excel that method is a constructor of the class and doesn't require a return type. If you want to rename ether of those just add void before your method name.
honestly not even trying to sooth. but just to the point no jargon.
Nice tutorial, But you didn't close the workbook, it can cause a problem even after closing the application. The solution is: make the wb variable public and use the wb.close() method after printing the message.
I have the same problem. After i close the app a window appears which opens the excel file. I tried the wb variable public and close like you said, but i got this error as exception : System.Runtime.InteroopServices.COMException: '0x800401A8'.
I tried also other functions for release the object like System.Runtime.InteroopServices.Marshal.ReleaseObject(wb)
but i got stuck.
I found a function which closes all excel files
( //using System.Diagnostics;
Process[ ] excelProcs = Process.GetProcessByName("EXCEL");
foreach(Process proc in excelProcs) {
proc.Kill();
} )
but it is not recommended
@@andreeanita9135 Share your code i might be able to figure out the issue.
where is your excel file located in the project? I can't see it anywhere in the video.
Documents
Yes, I had the problem, I moved the file into the Documents folder and it worked.
Hi there! It's really interactive tutorial. I have a question, I have an error said that "Program does not contain a static 'Main' method suitable for an entry point". Also, I am a little bit confused where I need to put my excel document, maybe you could answer it?
Excel file goes in Documents folder if you're not changing the path. As for the error ether create a Forms project that auto generates main in Program.cs or put the code for Load method inside Console projects main method. Can't mix and match console and form projects.
এটি সর্বকালের সেরা ভিডিও
Hi, experienced but rusty programmer here... The program runs but no message. When I debug/trace the steps in Form1.cs, it never gets past InitializeComponent(); it goes to program.cs (Main) and ends after Application.Run(new Form1())
Like I said, Rusty...I was missing code in the initializecomponent from the form1.designer.cs. I just used the events window to make sure it loaded (lazy lol).
@@pop2mcar Piece of advice when working with forms, don't stacktrace from Program.cs, it's there only to start the form class as a single thread apartment. Constructor is usually there only to set constants and initialize controls/events. In this case start from Form_Load event, it triggers when all controls in the form are loaded.
Actually always start to trace from an event rather than a constructor when working with a form, be it Load, or Click etc.
It works except for one thing. I am not able to edit or delete the excel file now. How can this be fixed?
Great tutorial.. But I am having a problem.. I can't get the MessageBox to show up.. I have gone over the code 1000 times now, it is exacly the same as shown in the video.. I get no errors when compiling and the form1 window pops up, but not the message box. I have tryd moving the TEST.xlsx to diffrent locations and add it to the path line.. Any clue? Would like to continue with the tutorials, but not until I know it works for me xD
Ps. Also tryd running VS as admin.
What you can do is instead of MessageBox.Show(excel.readCell(0,0)) do string temp = excel.readCell(0,0); MessageBox.Show(temp); Breakpoint on messagebox line and see if temp has a value (hover over it with a mouse) of the excel cell. Depending on that we can see what's going on. (Just a heads up, path for file is C:\Users\"Current User"\Documents\TEST.xlsx")
How do you close the file? I had to save my spreadsheet as a different one because it got locked in read-only mode.
just Excel.Close();
Hi. I can´t undestand the line:
_Application excel = new _Excel.Application();
why I am able to instantiate the “Application” interface?
Good question. You're not instantiating an interface, you're instantiating an abstract class inheriting it (or at least I think it's an abstract class, been a while since I looked it up) and VS is just reporting it as interface
Sory, concrete class, not abstract class.
EPIC tutorial MAN!!
Do I need to have Excel installed on my system to perform this?
Yes.
:(
I think you can download Microsoft.Office.Interop.Excel NuGet package to get the reference you need.
files-cdn.cnblogs.com/files/fan0136/Microsoft.Office.Interop.Excel.rar
you can use this link to download the .dll, add this to the solution as reference and the Interlop namespace will be available
Hi i have one doubt how can we load same data to down list based on select query and condition
Hey Bospear, I followed your every step it was osm code but i have issue in form1 backend code
that is it is not accepting Excel as class for excel object error message "Excel is alias but it is used as type"
plz guide me
post the line of code that's giving you the error
Hi , thank you for sharin your knowledge. I've tried the above. Everything works but i cant receive a list of items. Do i need to parse the readcell to a list? I thought the int i++ and j++ would return each value when not null and show it in the messagebox. Hope you can help out.
value2 returs all values from a specific cell, regardless if it's null (returns as empty string), if you want to grab everything from a sheet one of the later tutorials shows how to do it, I think it's No5, you also have in the comment how to grab only used cells
I'm getting the error:
System.InvalidCastException
HResult=0x80004002
Message=Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Office.Interop.Excel.Workbook'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{000208DA-0000-0000-C000-000000000046}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).
Source=
StackTrace:
How do you close the excel file when you're done? My file is now locked and I can't edit it with excel anymore.
Yea, my bad. In later videos I made the close statement but that one is bad as well.
Just put a new method in the Excel class
public void Close()
{
wb.Close(0);
}
and than call it after the read from the form.
As for file you opened right now just kill excel.exe processes from task manager and that will clear it up.
Thank you so much! I just spent the last 2 hours trying to figure it out on my own.
2 hours is a lot :)
@@TheBospear I placed above code in the Excel class without any errors. But I added below in the Form1.cs and "Close()" does not appear to recognized as the Excel class and Close method but instead just closes the form.
private void button2_Click(object sender, EventArgs e)
{
Close();
}
@@slazper Object oriented programming, you need to call a method in the object (instance of class) you want. Close in Form is upon Form object, Excel excel = new Excel(...); excel.Close(); works upon Excel object.
I suggest you look in to C# and Object oriented programming tutorials if you want to make a c# app with this first. It's a really big gap in knowledge you need to fill if comments here are to be effective.
I do not have the microsoft.office library. How can I obtain this? I am using visual studios 2022 and can't find it online. Or at least in english.
Install excel, lib comes with it. Or you can get it from nuget www.nuget.org/packages/Microsoft.Office.Interop.Excel
thx. but it doesn't work for me. Is there a way how to open and read excel file in WPF?
Access to interop objects in WPF is the same as Forms, I guess that office version, and interop lib with it, is different. I suggest you read following two:
learn.microsoft.com/en-us/dotnet/csharp/advanced-topics/interop/how-to-access-office-interop-objects
stackoverflow.com/questions/68981380/load-the-excel-file-which-i-open-using-dialog-box-in-wpf-into-datagrid-when-i-cl
When I try to run Form1.cs, I receive the following popup window:
"A project with an Output Type of Class Library cannot be started directly. In order to debug this project, add an executable project to this solution which references the library project. Set the executable project as the startup project."
It seems like I created Form1 as the wrong type of file. I know this is too basic of a question to be asking here, but do you have any recommendations of how to create it properly? I created it through File -> New -> Visual C# class
File -> New -> Project -> Windows Forms Project (or Windows Forms App depending on the version). That also answers your question about lines 1-12, auto generated code.
Sir I do have a problem, I put the line of path as this
Excel1 excel = new Excel1 (@"C:\Users\farshidmoosavi\Desktop\Revit API\first.xlsx", 1);
and it says we cannot find the address, althogh the address is correct
the problem has been solved
Do post a solution please, just in case someone else manages to stumble at the same problem.
I was just entering a wrong Path, once I get it corrected, it's been solved.
If I had a dollar every time that happened to me... I wouldn't need a job any more :D
Thank you very much! You helped me a lot! Do you have ideas how to read all data from .xlsx file , not just one column and row?
try doing for loop?
Yep, already
You got to loop for it, row by row, cell by cell. Treat it like a matrix. Only problem you can have with it is date and time writing and reading. I'll do a video soon on it and post the whole class to github.
wo muss die Testdatei abgespeichert werden?
You might want to go with english since google translate isn't all that good. Default path is Documents or My Document on older windows versions. Otherwize you can make a custom path to any location.
I am having troubles with Value2 and the Excel type in the form1. Can anyone give me some sugestions please
Did you ever find a solution for this? I'm having the exact same problem
I just didn't type .Value2 and it worked for me
@@zanekross9649 Sorry for the late response. This was a very old project I was in and I don't really remember. I hope you find @Adomas B 's respnse convenient
Thanks a lot!! Very helpful!
nothing in my message box? just displays blank form1 ? i must of missed something.
@Bospear. How do you implement the Application Interface? I'm getting errors when using the _Application syntax.
_Application is an object within Interop.Excel library, just make sure you have all using statements. If it still persists you can do _Excel.Application excel = new _Excel.Application();
@@TheBospear Interesting, I had my Excel class named something other than "Excel" so that's why it caused errors. I named it "Excel_Stuff.cs" because it was just a learning exercise. Is there a reason this class has to be named so definitively?
@@coyotemoon722 It shouldn't, I guess it came to some naming polymorphism between interop library and the class. I'll check it out one of these days to see what's up.
I have the some error. It might have something to do with the VS version. Mine is VS2013
Hey Bospear, I just followed this tutorial, and everything runs great except that the MessageBox.WriteLine(excel.ReadCell(0,0)); line doesn't pull up any message. I have a Test.xlsx file with a string in the correct box and have tried to output this message to the console instead, but still haven't gotten anything to pop up. Any ideas on what might be going wrong here?
Put a break point inside ReadCell method, see if you get anything. Also you placed Test.xlsx in the Documents folder?
@@TheBospear I placed Text.xlsx in the documents folder and put a breakpoint inside the ReadCell method, but it gave me the same result as before. When I put a breakpoint in the Program.cs file, it goes into the Application.Run(new Form1()) line, then inside the Form1.cs file it runs through the Form1() function and ends. Should I be making a call to Form1_Load inside the public Form1() function? And if so, what would be the parameter for the EventArgs e? Thank you for your help!
@@connorkneeland3091 Just double click on the form and it will give your Form_Load event. Mind if you paste your code here so I can take a look?
@@TheBospear I tried double-clicking and unfortunately got no result. Here is the code I have
Form1.cs:
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;
namespace ImportExcel2
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e) //File name, worksheet number
{
OpenFile();
}
public void OpenFile()
{
Excel excel = new Excel(@"C:\Users\Conno\Documents\Test.xlsx", 1);
//Console.WriteLine(excel.ReadCell(0, 0));
//MessageBox.Show(excel.ReadCell(0, 0).ToString());
MessageBox.Show(excel.ReadCell(0, 0));
}
}
}
Excel.cs
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Office.Interop.Excel;
using _Excel = Microsoft.Office.Interop.Excel;
namespace ImportExcel2
{
public class Excel
{
string path = "";
_Application excel = new _Excel.Application();
Workbook wb;
Worksheet ws;
public Excel(string path, int Sheet)
{
this.path = path;
wb = excel.Workbooks.Open(path);
ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[Sheet];
}
public string ReadCell(int i, int j) //Row, Column
{
i++;
j++;
if (ws.Cells[i, j] != null)
return ws.Cells[i, j].ToString();
else
return "";
}
}
}
Program .cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;
using ImportExcel2;
namespace ImportExcel2
{
static class Program
{
///
/// The main entry point for the application.
///
[STAThread]
static void Main()
{
Application.SetHighDpiMode(HighDpiMode.SystemAware);
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new Form1());
}
}
}
In the Excel.cs file, I do not make a call to the Value2 function since I don't have that or think that I need it.
@@connorkneeland3091 Use .Text instead of .Value2
help me, plesase : An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in WpfApplication1.exe
What a Great Tutor
Why in .NET 3.5 or less than my app don't read Excel file
this's my mistake:" Cannot implicitly convert type 'object' to 'Microsoft.Office.Interop.Excel.Worksheet'. An explicit conversion exists (are you missing a cast?)".
Thank you. Guy
stackoverflow.com/questions/19888326/cannot-implicitly-convert-type-object-to-microsoft-office-interop-excel-works
type ws = (Worksheet)wb.Worksheets[sheet];
I have a problem with the path. Either if I write 'string path = "";' or the same with the path of the TEST.xlsx file, it still doesnt compile, giving me the error: 'System.Runtime.InteropServices.COMException: ''TEST.xlsx' could not be found. Check the spelling of the file name, and verify that the file location is correct.'
and marking ' wb = excel.Workbooks.Open(path); '.
Can you/anyone help please ?
@"Test.xlsx" is in my documents for the excel interop, not in the root folder. I guess that's the problem. Try with a global path like @"C:\ExcelTestFolder\Test.xlsx"
Thanks for the reply. I now created a folder straight in C:\ called 'excel test folder' and put the 'TEST.xlsx' document in it. In the program i wrote this: 'string path = @"C:\excel test folder\TEST.xlsx";' and it doesnt work. I also tried typing two backslashes (\\) instead of one but I always end up with the same error... Im almost losing it... In any case, thanks for the video and the reply in the first place.
Always. Are you sure it's xlsx and not xls or xlsm?
@@billmaragos2812 It worked for me by avoiding space in folder name. I hope, it would work for you too.
@@jorgateme thanks for the reply !
Perfect. Thank you, sir!
Thank you very much for this guide, it's very helpful!
Do you not have to preface wb, ws and excel with 'this' in C# within the Excel constructor?
not needed, there's nothing conflicted.
in the part where it is done the pucblic Excel etc etc i have a problem. Visual studio said that i need a return, but u dont have a return in this part. What i can do?
Nvm, i saw the problem xD
he has return type in the method that was created as string with two returns in the body probably doesn't need the else part but that a different story.... for the constructor which is the public Excel , it is called in the main method with an initialization using new So in the static void Main{string [ ] , args) { Excel excel = new Excel("pathOfFile", 1);} //that is in the program.cs file, I would first make sure you're not trying to call the Excel object like a method in the program file, then check that you wrote the constructor correctly.
These Tutorials are really helpful. Thank you. Does anyone have any idea how can i read the background color of a cell in Excel? I need this, so i can make the same color for the background of the textbox.
hey I did everything as you showed in the video but for some reason its not working. When I run the program an empty default Form Window is being displayed but nothing else. The initializeComponent() method is being executed which looks like this:
private void InitializeComponent()
{
this.components = new System.ComponentModel.Container();
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.ClientSize = new System.Drawing.Size(800, 450);
this.Text = "Form1";
}
after that the program just finishes without executing the other methods like OenFile() etc.
Can you please tell me what Im missing here?
my main method in prgoram.cs looks like this:
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new Form1());
}
Form1 looks like this:
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
public void Form1_Load(object sender, EventArgs e)
{
OpenFile();
}
public void OpenFile()
{
Excel excel = new Excel(@"emails.xlsx", 1);
MessageBox.Show(excel.ReadCell(0, 0));
}
}
@@frankringwald4315 Breakpoint on OpenFile method, start stepping through and see where it breaks.
Hey Bospear,
I haven't been able to produce the "TEST" line, I have an excel document saved to my desktop. I have no build errors or anything, but the fact that this was built in WinForms keeps producing a blank Form field. I have "MessageBox.Show(excel.ReadCell(0,0)); " and all the code line for line, but the Form1.designer page was never shown, so I have no reference here to see what you've done on the design. Please help.
Thanks,
Jamie
Done nothing to the design, you can run the same code on console or dll (which is kind of a plan at the end of the series), but the path shown in the video is targeting Documents, so check if you have a correct path for the file you have.
Hey man thanks for the maintenance you do on here. I realized a piece of code was misplaced, had to move it and it was good to go. Your explanation for document root path is great, thank you.
You're very welcome.
It shows an error "Program does not contain a static "Main" method suitable for an entry point"
Make a Forms project to follow this step by step, not Class Library or Console application. Alternatively I suggest looking in to basic C# tutorials
I am using all the same namespaces and added the references. However, value2 is not working in line 29 of your video.
15.0 library. try with ((Excel.Range)wb.cells[i,j]).Value2
check this code here is the problem solved:
----
using System;
using Microsoft.Office.Interop.Excel;
using _Excel = Microsoft.Office.Interop.Excel;
namespace mysheet
{
class Excel
{
string path = "";
_Application excel = new _Excel.Application();
Workbook wb;
Worksheet ws;
static void Main(string[] args)
{
Excel excel = new Excel("C:\\Temp\\baldo.xlsx",1);
Console.WriteLine(excel.readCell(0,0));
// Excel(@"C:\Temp\baldo.xls", 1);
//Excel("C:\\Temp\\baldo.xls", 1);
}
public Excel(String path, int Sheet)
{
this.path = path;
wb = excel.Workbooks.Open(path);
ws = wb.Worksheets[Sheet];
Console.WriteLine("Hello World!");
}
public string readCell(int i, int j)
{
i++;
j++;
if (((_Excel.Range)ws.Cells[i, j]).Value2 != null)
return ((_Excel.Range)ws.Cells[i, j]).Value2;
else
return "";
}
public void writeToCell(int i, int j, string s)
{
}
}
}
Excelent! how to create and e
run a macro vba excel from C# ?
Hi, i have two question
1) This function works only project on NET. Framework; is it correct? Because with a project in NET. Core right at the start i have an expection that say it can't open/recognize the file (other 'problem' is that the compiler want to force me the cast on worksheet)
2) Could i specify the worksheet that i want to read?
I created more worksheet on excel file, but it opens only the first (obviously i try to change the value 'Sheet' when i create the object Excel)
Framework only and yes you can open other sheets, both by their name and their number.
Thanks for the confirmation and video 😁💪💪
@@Cmerk10 Excel ex = new Excel("excel.xlsx", 2); number is the sheet number
@@TheBospear Yes, excatly, my mistake!. Because i am working with two excel files. The second worksheet was added in only one of them. In my program i was calling the other file. Anyway thank you again !
hello i have a question,i imported excel file to windows forms application,and i need to import graph from that same file,but i don't have an idea how to do it,if you could give me any suggestions i would be realy grateful.
Never tried, honestly I only work with data. I guess you imported data that make that chart, so you can use that data to make a chart in the form. msdn.microsoft.com/en-us/library/dd489237.aspx
hi thank you very much for the help the program is perfect but I have a problem
if the cell contain a word for example "test" everything is perfect but if the cell contain number the I have this error :
Microsoft.CSharp.RuntimeBinder.RuntimeBinderException: 'Cannot implicitly convert type 'double' to 'string''
can you help me;
Microsoft at one point decided that we have to be protected from our own conversion..... so just do return (String)ws.Cells[i,j].Value2 or return ws.Cells[i,j].Value2 as String.
can anyone help me out with the method to read excel file and view it into datagrid.
Hi i am getting error like class not registered when my cursor coming to the application() constructor it is throwing exception please reply
I don't remember making an application() method
No I am asking you I have written code like
Application obj= new application();
And I have use namespace is
Microsoft.Office.interop.excel.
When my cursor coming to application () method it is throwing exception please reply
@@rajeshkale7849 Ok, first of, namespace should be name of the project, or name of a part of the project, you cannot use a library name as a namespace. Second, I can't know what problem you have in a code I haven't written or seen. Ether follow the tutorial or post your custom code.
Hi, im getting the following exception:
'Could not load file or assembly 'office, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'. The system cannot find the file specified.'
I have excel version 2011 and it didn't work, installed 2013 afterwards and it still doesn't work, would appreciate any help, thanks!
Office 365. Or get from nuget. Theres multiple comments here describing that issue and the solution.
thanks for this tutorial
I have a question pls:
how to open file from network drive using c#?
thx
Add the location of the network drive and path to the file in the path variable. So it's exactly the same way you would do on local. If your network drive requires admin rights access run the app or VS as admin.
thank you very much for this
i have another question pls:
i did create a simple form with a button, i need this button to open a folder and this folder exist in one of our servers
do you have this code pls
many thanks
@@microsecure2612 If you want to choose a file from that folder to load the path do the open file dialog with network location as the default (microsoft has example in documentation, can't write it out on a phone). If you just want to open in explorer I think System.Diagnostics.Process.Start targeting the folder would do the trick.
@@TheBospear
Thank u very much for ur info:
Is it possible to have ur email address then i can email u back my simple code to check it pls ?
Thx
@@microsecure2612 pastebin it here, however I'm not sure when I'll have the time to review it. Could take few days
Hi Bospear
I am confusing that why you have both Excel.cs and Program.cs in the same time?
Separate classes based on their logical wholes. Program.cs is a default class that any c# project uses, Excel.cs is made to handle just Excel calls. But you shoot look in to tutorial about Object oriented programming a bit more in depth before continuing, it will do you more good.
@@TheBospear
Thank you .Then I will .
Mhh, for me this didn't work with the "private void Form1_Load(object sender, EventArgs e) { OpenFile(); }"
But when I called OpenFile() from "public Form1() { }" just above it, it was finally executed correctly.
And I have no idea where you had to put the excel file to be able to call it by (@"TEXT.xlsx", 1), I had to put in the complete path...
Think you're missing initialize in the Form constructor, which is strange since it's a default call when you make a form. As for location it's in Documents.
So did you find out what was missing? Have the same problem and your work-around just helped, but what is missing?
@@JasonACharles Form_Load method was missing, double click the form and it will be added automatically.
@@TheBospear thx, that did the trick
If i have a library no. 16 for Excel and some users would have older libraries on their computers(like 15), I guess this won't work?
Worked with 14 when I started making excel automaton. Don't know about older than 14 but newer will work. Also there should be NuGet packs for 14, 15 and 16 which should be a free download.
think you can download Microsoft.Office.Interop.Excel NuGet package to get the reference you need
Does it work on Excel in Microsoft 365?
Or do i need to download Excel 2013?
It does. In fact I think the videos were recorded on 365, and the OG project I did before deciding to make these videos was on 365
Thanks for the clear explanation SIr. I am new to C# programming and starting using visual studio, I have followed the procedure but I cannot get the expected result, do I have to type anything in the Main() of program.cs? Thanks!
No, when you use forms in C# you generally don't do anything with Main function, unless you need to pass outside arguments. What are you getting as a result?
I can get nothing from the program, I think it directly runs the empty Main().
Firstly I created Console application which is the program.cs, then I added a class(Excel.cs) and Form with the "Load" event handler, but I can get nothing. Thank you so much sir!
Yea, that will happen if you make a console application and add form later. So just add the main form at the start. Write in Main() this:
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new Form1());
new Form1 is the name of the start Form you made.
Thanks! Will try later!
Sir, do you have any idea on this error happens in the code "wb = excel.Workbooks.Open(path);" : "Cannot convert COM object 'Microsoft.Office.Interop.Excel.ApplicationClass' to
interface type 'Microsoft.Office.Interop.Excel._Application"
(p.s. The message is not directly copied from visual studio as I am not using English language pack, I tried to translate the error message into English)
Thank you so much!
Where do you put the excel file so that the program can find it?
Documents.
First you need to create a Windows Forms Application, in which you can double-click on the main form in the form designer to have your Form1_Load() method to be automatically generated.
And you need to store your Excel file into your Windows documents folder.
You shall also implement a Close() method in your Excel class in order to avoid the presence of a residual Excel instance and "~$Test.xlsx" file like this :
public void Close()
{
if (wb != null)
{
wb.Close();
}
}
Thank you so much ! I used this codes in my app. But I need to count my rows . How can I do that ?
Easiest way would be
int i = 0;
while(true)
{
if(string.IsNullOrEmpty(excel.readcell(i, 0))
break;
i++;
}
@@TheBospear Thank you so much ! :)
Can we able to read a Image ?
Hello Sir, I am getting an error: Missing partial modifier on declaration of type 'Trial.Excel'; another partial declaration of this type exists. "Please help me!"
Search through solution for that type name, you have declarations named the same thing in another class, or skip VS2013 and go to VS2017.
Also check docs.microsoft.com/en-us/dotnet/csharp/programming-guide/classes-and-structs/partial-classes-and-methods
just so that you have full documentation on it.
Thank you so much
Thanks, in my case Value2 returns an object not an string,
Object valor = ((_Range) ws.Cells[i, j]).Value2;
---
using _Range = Microsoft.Office.Interop.Excel.Range;
....
In the instance parte ToString() or whatever.
thanks for the video.
Dis u got a solution? I am also facing same issue at value2...
System.IO.FileNotFoundException: 'Could not load file or assembly 'office, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'. The system cannot find the file specified.'
I tried adding Microsoft.Office.Interop.Excel.dll manually from browsing in the reference manager and also via installing using Install-Package from nuget package manager. Still getting the same error. Confused!!
Please HELP! Thanks.
You're missing a dependency, install Excel it self.
@@TheBospear Thanks a lot for a quick reply. Didnt expect a reply from you. I figured out what the issue was. I was creating a new project with Console App(.NET CORE), I was supposed to do it with .NET Framework. I just finished with all your videos. It was short and really helpful. Thanks
I am not sure what is going on but lines 14 and 22 give errors. It may be because I'm using 2019 Visual Studio.
Doubt it, more likely it's the library version, Microsoft really did a number on interop libraries (hence the need for these videos). What are the errors and what version interop dll are you using?
i'm not able to access the Value2 property. why might that be? i've double and triple checked and i've done everything the way you showed
There could be a number of things, different library, com bug, wrong object lookup etc. post your code here and let me take a look to eliminate most of the possibilities
@@TheBospear I ended up being able to access it but I had to use the worksheet as a range
@@AlexPitsi Could be a newer or older library, or a com bug... it happens more often than it doesn't
@@TheBospear How do u use it as a range?
@@garrfildcsgomore4609 ((_Excel.Range)ws.Cells[i,j]).Value2
is this app compatible with Office Excel 2019? I did my Test.xlsx file, my program runs, but does not work, I didn't get the message on the window...
It's 365, however a lot of versions are on the same standard, so this gives you a place to start, now google excel 2019 interop and find the parallels
@@TheBospear I can see that you have first another window that is not "Form1" and after you close this, it shows you "Form1". I only had the "Form1" window that does not show us nothing, do you follow me?... I tried at visual studio 2016 is the same thing...
@@eladiogonzalezruiz2112 That's a message box window. It's the Messagebox.Show line of code that makes that appear.
@@TheBospear Yes I get it, I have this "Messagebox.Show" but still does not appear me when I run it, what problem I have if this happens?
@@eladiogonzalezruiz2112 One of about a million. Check if the window exists, if not post the code here so I can take a look.
Hi ,
Complete beginner here. I created a console app and I'm given an error saying "Program does not contain a static main method"
Main method is an entrance point to any application. In fact using any VS should have generated main method when creating a console application. What's featured in the tutorial however is a Windows Form Application, it too has a main method but it's auto generated in the Program.cs and isn't shown in the video.
@@TheBospear Thanks for the replay , i fixed it. However i changed the code a bit. I'm trying to compile the data from all workbooks in a directory to a single worksheet in a blank workbook. The data of each worksheet needs to be appended to the previous data that has been pasted. I looked up the internet and managed to count a worksheet's total rows. But that's not really what i need. Here is the code.
static void Main(string[] args)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook sheet = excel.Workbooks.Open(@"C:___________________.xlsx");
Microsoft.Office.Interop.Excel.Worksheet x = excel.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
Excel.Range userRange = x.UsedRange;
int countRecords = userRange.Rows.Count;
int add = countRecords + 1;
x.Cells[add, 1] = "TotalRows" + countRecords;
sheet.Close(true, Type.Missing, Type.Missing);
excel.Quit();
}
I guess i'm asking for a c# answer to this popular vba question on stackoverflow. stackoverflow.com/questions/43112239/copy-and-paste-data-from-multiple-workbooks-to-a-worksheet-in-another-workbook?rq=1
@@saidalabed9417 For some reason I didn't get the comment notification. Since you have row numbers, all you need to do is loop through the file names in a folder, loop through the sheets until you find the name, read and write in another file.
Listing files is done through Directory.GetFiles, while reading sheet names is done with worksheet.Name
System.Runtime.InteropServices.COMException: 'Excel cannot access 'Desktop'. The document may be read-only or encrypted.'
Didnt work for me :(
Bad path, target the file and make sure you have permissions for it.
How would you write the code to read through a column of 10 cells? I am not having success.
for(int i = 0; i < 10; i++)
{
excel.ReadCell(i, 0);
}
You can also select a specific range and read all cells from it, but I will make tutorial on it later, sometime january if nothing else happens. In the meantime here's a stackoverflow link for it:
stackoverflow.com/questions/2627343/read-all-the-cell-values-from-an-excel-range-in-c-sharp
sir is it possible to open microsoft publisher also using visual studio 2017? coz i need to print the data in visual studio 2017 using publisher. please help me TIA
There is a publisher interop library, but I haven't used publisher before so no idea how to use the library.
yes i know but i dont know how to use it. is there any alternative way for me to print the data from database coz i need to print after searching it, TIA
Here's a couple of links that might help:
Opening and exporting a file.
csharp.hotexamples.com/examples/Microsoft.Office.Interop.Publisher/Application/Open/php-application-open-method-examples.html
Reading and manipulating text.
www.vbforums.com/showthread.php?554733-Find-and-replace-text-in-MS-Publisher-file
Last one is in VB, but interop works the same so you can rewrite it in c# in few minutes.
What If I have Microsoft.Office.Interop.Excel -Version 14? There is no property Value2
Value, Text etc.
@@TheBospear Couldn't find any way. Finally NuGet LinqToExcel did the job
I'm getting an Error 1 No overload for method 'Open' takes '1' arguments
What version of interop are you using and also can you paste your excel class code here so I can take a look?
Where's the first part of this video showing the form and app.config?
When starting a project in visual studio, you choose a .Net Framework Form, and you get what's on the start of the video
Could you expand on this video? like how to count the rows or columns or how to input into database?
This is kind of a specific tutorial, made to show people how to use interop at the time when microsoft didn't have proper documentation for it. If you want to count rows/columns as per new microsoft documentation TargetWorksheet1.UsedRange.Rows.Count - 1; same for columns.
As for the database part that's a gigantic question, and I suggest you look up C# integration for the specific database you need.
I don't even know how to get to this point? what type of project should i create? is it c# console project ? or is it winforms project? or is it something else? how to get that excel.cs thing in solution explorer?
winforms .NetFramework and create new class, name it Excel.cs.
@@TheBospear thanks for your reply. I created a new project in VS. selected winforms project in c#. Right clicked the project name in solution explorer and added new class and renamed it excel. Had to google a bit. But i think i am in the right path because the files and code in my project look just like yours now. I created the excel file named Test in my VS project folder. Followed along all the coding. Ran the code. Shows tons of error. Trying to fix the code, uppercase, lowercase, space, underscore what not. Turns out its hard to even copy the code when i dont know what those syntax are doing.
@@beenay18 Yeah, these tutorials are made for C# devs since years ago Microsoft had little to none documentation on this library.
What Is The "Form1_Load" ?? Please
Event handler for Form Load event. Do not attempt to just copy the code without basic C# knowledge, you will just give your self a headache. Do a basic C# tutorials first, it shouldn't take more than a week or two, then start making stuff.
There is a error what says "Missing compiler required member 'Microsoft.CSharp.RuntimeBinder.Binder.Convert' " .what do to ?
Check References for Microsoft.CSharp reference, if it's there remove it then add it back if it's not just add it. That error is always tied to Microsoft.CSharp.dll as far as I've seen.
thank u but it's not working.when I added again ,errors were gone. after debugging again, Error appears :(
Strange. Are you using any dynamic calls?
I did everything as you said but it says that system doesn't find the file although I tied every way to describe a path
File needs to be in Document folder. Also post the path you're taking for the file so that I can check.
@@TheBospear My path is @"C:\Users\adoma\Documents\Testfile.xlsx";
@@Adomas_B and in documents folder you have a Testfile.xlsx, not Test.xlsx or Testfile.xls, or variant of that?
@@TheBospear Yes, I have the exactly labeled it 'Testfile' and when I go to properties > security, it says Object name: C:\Users\adoma\Documents\Testfile.xlsx, yet console still throws out an error :
"System.IO.FileNotFoundException: 'Could not load file or assembly 'office, Version=15.0.0.0, Culture=neutral, PublicKeyToken = (My 64 bit hash) '. The system cannot find the file specified.'
@@Adomas_B You might have wanted to lead with the error, it's not excel file that it cannot find, it's your excel interop dll, or more likely, one of it's dependencies. Reinstall Excel and make sure versions match.
Where are the first steps for creating this project ? unfortunately not helpful for those who want to create from scratch
File -> new -> project -> windows form, and name the project, right click in solution explorer add->Class, and name it excel.
I do suggest that you look in to some basic visual studio tutorials first, this series wasn't made to teach basics of programming but to help out junior developers to work with excel in c#.
Sir, i have a problem when i called int data from the excel, maybe you can help me
I'm going to need more info than that. Where does it break, what's the error message and your code snippet from the method where it breaks
How to specify the path to the file so that it is in the same folder as the exe file without entering the full path?
AppDomain.CurrentDomain.BaseDirectory
Thank you! Another question: can I find out the count of non-empty cells in a column? I need to take a random cell in a column: int Rand = R.Next(1, ???);
@@qlBlitzlp just go through the column and test for string.IsNullOrEmpty
Thanks! I thought there was a special function for this.
I can't find the right references to import. I am using visual studio on a mac, does that change things? Thanks
Sadly it does. As far as I can see Excel Object Library is not supported on Mac and by Microsoft OSX is not suitable environment for interchanging COM objects. Sorry.
Im getting an error saying Value2 is not defined. How do i resolve this?
Different library or .Text(). you got detail explanation in the comments
Check this code:
-----
using System;
using Microsoft.Office.Interop.Excel;
using _Excel = Microsoft.Office.Interop.Excel;
namespace mysheet
{
class Excel
{
string path = "";
_Application excel = new _Excel.Application();
Workbook wb;
Worksheet ws;
static void Main(string[] args)
{
Excel excel = new Excel("C:\\Temp\\baldo.xlsx",1);
Console.WriteLine(excel.readCell(0,0));
// Excel(@"C:\Temp\baldo.xls", 1);
//Excel("C:\\Temp\\baldo.xls", 1);
}
public Excel(String path, int Sheet)
{
this.path = path;
wb = excel.Workbooks.Open(path);
ws = wb.Worksheets[Sheet];
Console.WriteLine("Hello World!");
}
public string readCell(int i, int j)
{
i++;
j++;
if (((_Excel.Range)ws.Cells[i, j]).Value2 != null)
return ((_Excel.Range)ws.Cells[i, j]).Value2;
else
return "";
}
public void writeToCell(int i, int j, string s)
{
}
}
}
@@danielafrancos.9953 I'm very thankful! I love you dear!!!!!!! :* You deserve to be happy!!
@@beardlessspirit4946 your welcome : )
I'm getting the error:
System.IO.FileNotFoundException
HResult=0x80070002
Message=Could not load file or assembly 'office, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'. The system cannot find the file specified.
Source=
StackTrace:
Check the file location, with Excel library base location (@"*.xlsx") isn't applocation, it's in Documents folder
@@TheBospear I don't understand
@@RobertShane Check the location of the file you are trying to read. It's supposed to be in Documents folder.
@@TheBospear You mean the excel file I'm trying to read? It has to be in the documents folder?
@@RobertShane If you're using my code yes, if you want it somewhere else just enter the full path to it in the new Excel("HERE", 1); line
what Csharp version you're working with?
If I can remember correctly this was C# 7.0
@@TheBospear hiii bospear! can you give me your email? im working on a specific App that deals with excel values and i have no idea from where to start,,, i really apreciate your help
thanks
very easy and simple to use
Can any one tell me which project he has created
Thank you for the video BosPear !
How do I read a int or double? When i put a number in an excel cell, i get an error "cannot convert double to string". I want to get values from excel and do calculation in my program with the values.
same way you do a string, only replace it with int. So it's int value = (int)ws.ReadCell(0,0);
How could I find some text in the Excel file and copy the cell next to it into the text file?
By following this tutorial to get the text from excel and then using StreamWriter to write it
@@TheBospear thank you and a one problem, why I can't ws = wb.Worksheets[Sheet];
@@MegaDav11 Need more info than that, like the error you get
@@TheBospear Error CS0266 The object type cannot be converted to Microsoft.Office.Interop.Excel.Worksheet by default. There is an explicit transfer
@@MegaDav11 Different interop library, just do ws = (Worksheet)wb.Worksheets[Sheet];
or ws = wb.Worksheets[Sheet] as Worksheet;
"An object reference is required for the non-static field, method, or property" getting this problem need help
If you're calling it from Main (I'm guessing you did a console app) you need to ether make an object first or make the method you're calling a static method.
Hey, I am trying to make this "easy " project but I got stuck because after doing everything by you, it not working :/. Can you help me? I can´t find any mistake there.
Thanks
btw code is here.
pastebin.com/7HkYMLWu
Need more info, what isn't working for starters
Bospear when I run it, nothing happens
@@dadas12345678 Put a break point at the start and follow the steps, see where steps stop.
@@TheBospear so
Excel excel = new Excel(@"TEST.xlsx", 1);
stop everything( I tried Messagebox.show("Test 1"); before and after (test 2) this command line and Box "Test 1" showed but "Test 2" was "dead" (didn´t show up). After commenting this part, Test 1 + Test2 showed up. :/
Why is it exel.ReadCell(0,0));? I thought Excel starts at 1,1
because I added i++ and j++ in the ReadCell method to keep consistent with 2d arrays that start from 0,0