C# Excel Tutorial - #1 - Open and Read Excel Files

Quick guide how to open and read excel file through C# application you are making.

[@schwarzyzz7914]

The easiest way availlable on the internet ! A big thank from a french student !

[@СергейКостычев-х1о]

Hi! Thank you! How to close excel correctly? Even if I close VS after this code, my excel file doesn't open for editing.

[@armindashti8014]

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?

[@kaifikhan]

I am also getting the same exception and i am also stuck on it.

[@charavayfelix4475]

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 ?

[@TheBospear]

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;

[@luisurenaalex2183]

@@TheBospear " ws = (Worksheet)wb.Worksheets[Sheet];" fixed the problem

[@keyounky]

@@luisurenaalex2183 thx dude it's worked

[@Adomas_B]

@@TheBospear Thanks!

[@paputec]

We have all had problems with path=" ". But the tutorial served me. Thank you

[@TheBospear]

Blank path leads to Documents, you can just enter @"C:\Your Path\Test.xlsx" and target where ever you want.

[@钱钧陶-l8z]

​@@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

[@钱钧陶-l8z]

@@TheBospear I'm using Office 16. It seems like the package only applies to Office 15.

[@TheBospear]

@@钱钧陶-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.

[@dhannant]

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?

[@TheBospear]

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.

[@bhootpurvmanusya]

honestly not even trying to sooth. but just to the point no jargon.

[@Grizzly12th]

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)).'

[@TheBospear]

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.

[@ertugrulozdemir2329]

@@TheBospear I got this same problem but ı cant solved

[@uniquedesign5680]

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.

[@andreeanita9135]

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

[@uniquedesign5680]

@@andreeanita9135 Share your code i might be able to figure out the issue.

[@yerkoantonioalvarez]

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?

[@TheBospear]

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.

[@yerkoantonioalvarez]

@@TheBospearI clearly understand now, thank you so much!

[@natedurand]

What is on lines 1-12 of Form1.cs?

[@WanSyazlina]

where is your excel file located in the project? I can't see it anywhere in the video.

[@TheBospear]

Documents

[@messager3000]

Yes, I had the problem, I moved the file into the Documents folder and it worked.

[@anthonykurniajaya4938]

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?

[@TheBospear]

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.

[@jackwinter2669]

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.

[@TheBospear]

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.

[@jackwinter2669]

Thank you so much! I just spent the last 2 hours trying to figure it out on my own.

[@Matrix21rms]

2 hours is a lot :)

[@slazper]

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

[@TheBospear]

@@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.

[@LE0p01d]

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.

[@darius8171]

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?

[@dennisling5692]

try doing for loop?

[@darius8171]

Yep, already

[@TheBospear]

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.

[@thomaskloiber7452]

wo muss die Testdatei abgespeichert werden?

[@TheBospear]

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.

[@BCTAHbKA]

এটি সর্বকালের সেরা ভিডিও

[@pop2mcar]

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())

[@pop2mcar]

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).

[@TheBospear]

@@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.

[@bountypw7242]

EPIC tutorial MAN!!

[@10tronic]

Hi. I can´t undestand the line: _Application excel = new _Excel.Application(); why I am able to instantiate the “Application” interface?

[@TheBospear]

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

[@TheBospear]

Sory, concrete class, not abstract class.

[@WingBeatZ]

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.

[@TheBospear]

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")

[@farshidmousavi109]

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

[@farshidmousavi109]

the problem has been solved

[@TheBospear]

Do post a solution please, just in case someone else manages to stumble at the same problem.

[@farshidmousavi109]

I was just entering a wrong Path, once I get it corrected, it's been solved.

[@TheBospear]

If I had a dollar every time that happened to me... I wouldn't need a job any more :D

[@robvanbreemen6660]

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.

[@TheBospear]

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

[@karishmakate91]

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?

[@canvasbykaya]

i'm having the same issue, did you end up figuring it out?

[@natedurand]

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

[@TheBospear]

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.

[@radhikakurakula1425]

Hi i have one doubt how can we load same data to down list based on select query and condition

[@pebe26]

Do I need to have Excel installed on my system to perform this?

[@TheBospear]

Yes.

[@Igor767]

:(

[@edgardopichardo]

I think you can download Microsoft.Office.Interop.Excel NuGet package to get the reference you need.

[@UpamanyuYavalkar]

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

[@Felifoxy]

It works except for one thing. I am not able to edit or delete the excel file now. How can this be fixed?

[@coyotemoon722]

@Bospear. How do you implement the Application Interface? I'm getting errors when using the _Application syntax.

[@TheBospear]

_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();

[@coyotemoon722]

@@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?

[@TheBospear]

@@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.

[@jbond5834]

I have the some error. It might have something to do with the VS version. Mine is VS2013

[@maunguyenvan9296]

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

[@RodrigoSouza-dc4xd]

stackoverflow.com/questions/19888326/cannot-implicitly-convert-type-object-to-microsoft-office-interop-excel-works

[@Adomas_B]

type ws = (Worksheet)wb.Worksheets[sheet];

[@jeremyflowers8908]

Do you not have to preface wb, ws and excel with 'this' in C# within the Excel constructor?

[@TheBospear]

not needed, there's nothing conflicted.

[@Cmerk10]

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]

Framework only and yes you can open other sheets, both by their name and their number.

[@Cmerk10]

Thanks for the confirmation and video 😁💪💪

[@TheBospear]

@@Cmerk10 Excel ex = new Excel("excel.xlsx", 2); number is the sheet number

[@Cmerk10]

@@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 !

[@billmaragos2812]

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 ?

[@TheBospear]

@"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"

[@billmaragos2812]

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.

[@TheBospear]

Always. Are you sure it's xlsx and not xls or xlsm?

[@jorgateme]

@@billmaragos2812 It worked for me by avoiding space in folder name. I hope, it would work for you too.

[@billmaragos2812]

@@jorgateme thanks for the reply !

[@kenma6432]

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!

[@TheBospear]

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?

[@kenma6432]

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!

[@TheBospear]

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.

[@kenma6432]

Thanks! Will try later!

[@kenma6432]

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!

[@rronfrangu1103]

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.

[@SamRechym1]

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

[@TheBospear]

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.

[@SamRechym1]

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.

[@TheBospear]

You're very welcome.

[@נתנאלאדר-מ5ל]

Thanks a lot!! Very helpful!

[@invazorzimutube]

I am having troubles with Value2 and the Excel type in the form1. Can anyone give me some sugestions please

[@zanekross9649]

Did you ever find a solution for this? I'm having the exact same problem

[@Adomas_B]

I just didn't type .Value2 and it worked for me

[@invazorzimutube]

​@@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

[@anon0912]

How do you close the file? I had to save my spreadsheet as a different one because it got locked in read-only mode.

[@TheBospear]

just Excel.Close();

[@faab007nl]

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:

[@muntaqimtechnicalbaloch5107]

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

[@TheBospear]

post the line of code that's giving you the error

[@sarahabraham7248]

It shows an error "Program does not contain a static "Main" method suitable for an entry point"

[@TheBospear]

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

[@have-z5h]

Hi Bospear I am confusing that why you have both Excel.cs and Program.cs in the same time?

[@TheBospear]

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.

[@have-z5h]

@@TheBospear Thank you .Then I will .

[@TerraCalc123]

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.

[@abhijeetjoshi6103]

Dis u got a solution? I am also facing same issue at value2...

[@FAAS12023000]

Excelent! how to create and e run a macro vba excel from C# ?

[@tuphanhoang9074]

help me, plesase : An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in WpfApplication1.exe

[@danielvargas1537]

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?

[@danielvargas1537]

Nvm, i saw the problem xD

[@kylekastilahn]

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.

[@messager3000]

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

[@StefanSnyman1996]

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.

[@TheBospear]

same way you do a string, only replace it with int. So it's int value = (int)ws.ReadCell(0,0);

[@tatolelashvili3185]

What a Great Tutor

[@sagebrushkeeper7]

I can't find the right references to import. I am using visual studio on a mac, does that change things? Thanks

[@TheBospear]

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.

[@leemyers9332]

nothing in my message box? just displays blank form1 ? i must of missed something.

[@meliseyka]

thx. but it doesn't work for me. Is there a way how to open and read excel file in WPF?

[@TheBospear]

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

[@frankringwald4315]

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?

[@frankringwald4315]

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

[@TheBospear]

@@frankringwald4315 Breakpoint on OpenFile method, start stepping through and see where it breaks.

[@simxschl]

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

[@TheBospear]

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.

[@JasonACharles]

So did you find out what was missing? Have the same problem and your work-around just helped, but what is missing?

[@TheBospear]

@@JasonACharles Form_Load method was missing, double click the form and it will be added automatically.

[@JasonACharles]

@@TheBospear thx, that did the trick

[@sageoffaith]

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.

[@TheBospear]

Install excel, lib comes with it. Or you can get it from nuget www.nuget.org/packages/Microsoft.Office.Interop.Excel

[@XxxDjNikxxX]

Thank you very much for this guide, it's very helpful!

[@shhhomi]

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.

[@TheBospear]

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

[@karimalramlawi7228]

Does it work on Excel in Microsoft 365? Or do i need to download Excel 2013?

[@TheBospear]

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

[@Adhodge1992]

I am using all the same namespaces and added the references. However, value2 is not working in line 29 of your video.

[@TheBospear]

15.0 library. try with ((Excel.Range)wb.cells[i,j]).Value2

[@danielafrancos.9953]

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

[@pcrishinandanyoga9675]

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!"

[@TheBospear]

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.

[@pcrishinandanyoga9675]

Thank you so much

[@MichaKurzewski]

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?

[@TheBospear]

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.

[@edgardopichardo]

think you can download Microsoft.Office.Interop.Excel NuGet package to get the reference you need

[@blazept567]

Perfect. Thank you, sir!

[@microsecure2612]

thanks for this tutorial I have a question pls: how to open file from network drive using c#? thx

[@TheBospear]

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.

[@microsecure2612]

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

[@TheBospear]

@@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.

[@microsecure2612]

@@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

[@TheBospear]

@@microsecure2612 pastebin it here, however I'm not sure when I'll have the time to review it. Could take few days

[@kumarayush3556]

can anyone help me out with the method to read excel file and view it into datagrid.

[@Spirittism]

Could you expand on this video? like how to count the rows or columns or how to input into database?

[@TheBospear]

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.

[@bupolsdupro124]

Where do you put the excel file so that the program can find it?

[@TheBospear]

Documents.

[@christiangalvan7715]

How would you write the code to read through a column of 10 cells? I am not having success.

[@TheBospear]

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

[@diegocarreras9313]

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!

[@TheBospear]

Office 365. Or get from nuget. Theres multiple comments here describing that issue and the solution.

[@zadrondraney3905]

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.

[@TheBospear]

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?

[@nikosnikisianlis7573]

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;

[@TheBospear]

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.

[@tarktastan7100]

Thank you so much ! I used this codes in my app. But I need to count my rows . How can I do that ?

[@TheBospear]

Easiest way would be int i = 0; while(true) { if(string.IsNullOrEmpty(excel.readcell(i, 0)) break; i++; }

[@tarktastan7100]

@@TheBospear Thank you so much ! :)

[@roccoguarino9136]

Hi for me it works only if I put the OpenFile() inside the method Form1. Could you explain me why? What is the function of the method Form1_Load? Thk

[@TheBospear]

Form1_Load gets called when Form UI is generated. So if you started a console rather than a Form app Form1_Load will not be called. Also, Form1_Load is an event listener, you make the even by double clicking Form UI in the UI tab or by adding the event in form properties (or you can make event by your self but why would you do that manually)

[@roccoguarino9136]

@@TheBospear thk for the help. When I create the project I used the Form app. I am new to Visual Studio so I did not catch your suggestion. Could you be more specific? Thk

[@TheBospear]

@@roccoguarino9136 It doesn't get any more specific than that unless I picture guide you. So this might be better kzbin.info/www/bejne/jnyTqYuHqd6nlbc

[@roccoguarino9136]

@@TheBospear thk you so much

[@FrazerHatyai]

System.Runtime.InteropServices.COMException: 'Excel cannot access 'Desktop'. The document may be read-only or encrypted.' Didnt work for me :(

[@TheBospear]

Bad path, target the file and make sure you have permissions for it.

[@srinivasu2953]

Can we able to read a Image ?

[@venkataramananori5579]

I'm getting an Error 1 No overload for method 'Open' takes '1' arguments

[@TheBospear]

What version of interop are you using and also can you paste your excel class code here so I can take a look?

[@قراءات-غ4ظ]

what Csharp version you're working with?

[@TheBospear]

If I can remember correctly this was C# 7.0

[@قراءات-غ4ظ]

@@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

[@afsanarabeya4417]

There is a error what says "Missing compiler required member 'Microsoft.CSharp.RuntimeBinder.Binder.Convert' " .what do to ?

[@TheBospear]

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.

[@afsanarabeya4417]

thank u but it's not working.when I added again ,errors were gone. after debugging again, Error appears :(

[@TheBospear]

Strange. Are you using any dynamic calls?

[@shirishcin]

Where are the first steps for creating this project ? unfortunately not helpful for those who want to create from scratch

[@TheBospear]

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#.

[@edwinpeterdionisio5284]

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

[@TheBospear]

There is a publisher interop library, but I haven't used publisher before so no idea how to use the library.

[@edwinpeterdionisio5284]

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

[@TheBospear]

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.

[@AlexPitsi]

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

[@TheBospear]

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

[@AlexPitsi]

@@TheBospear I ended up being able to access it but I had to use the worksheet as a range

[@TheBospear]

@@AlexPitsi Could be a newer or older library, or a com bug... it happens more often than it doesn't

[@garrfildcsgomore4609]

@@TheBospear How do u use it as a range?

[@TheBospear]

@@garrfildcsgomore4609 ((_Excel.Range)ws.Cells[i,j]).Value2

[@Trinable555]

Great Video

[@kamanyuhuhu9985]

Sir, i have a problem when i called int data from the excel, maybe you can help me

[@TheBospear]

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

[@rajeshkale7849]

Hi i am getting error like class not registered when my cursor coming to the application() constructor it is throwing exception please reply

[@TheBospear]

I don't remember making an application() method

[@rajeshkale7849]

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

[@TheBospear]

@@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.

[@katkovkirill10]

i did everything like you`ve shown but i am getting only this window i.imgur.com/hhordz7.png

[@JasonACharles]

Was there a solution to this problem, getting the same thing, not even error code... just Form 1 pops up.

[@TheBospear]

Put a break point at Messagebox.Show, see if you get the value you expected inside it or if it hits it at all. Form will display the moment everything from the constructor is executed after which Form_Load() is called

[@Swakke31]

Maybe next time show us which template you used and where you need to put the excel file.

[@TheBospear]

This tutorial was made because Microsoft had no documentation on the library back then. Template as any intern can see plainly is windows forms (not that it matters, you can use the same class in any .net framework project). As for location of the excel file I think that it's in the second tutorial, if not it's the default working directory, which is Documents.

[@Swakke31]

@@TheBospear I have tried a windowsformsapp template, but can not run it because of the static main thats missing in your program.

[@TheBospear]

@@Swakke31 static void main is the entry method in all C# applications, it is not missing. Make a new windows forms application .Net Framework and do exactly what I did in the tutorial.

[@eladiogonzalezruiz2112]

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

[@TheBospear]

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

[@eladiogonzalezruiz2112]

@@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...

[@TheBospear]

@@eladiogonzalezruiz2112 That's a message box window. It's the Messagebox.Show line of code that makes that appear.

[@eladiogonzalezruiz2112]

@@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?

[@TheBospear]

@@eladiogonzalezruiz2112 One of about a million. Check if the window exists, if not post the code here so I can take a look.

[@robertsmith4019]

Where's the first part of this video showing the form and app.config?

[@TheBospear]

When starting a project in visual studio, you choose a .Net Framework Form, and you get what's on the start of the video

[@saidalabed9417]

Hi , Complete beginner here. I created a console app and I'm given an error saying "Program does not contain a static main method"

[@TheBospear]

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.

[@saidalabed9417]

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

[@saidalabed9417]

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

[@TheBospear]

@@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

[@khalidsaifullah6467]

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? plz Answer Me....

[@TheBospear]

That means that Excel is not installed. You can grab the excel interop nuget to solve this.

[@khalidsaifullah6467]

Thnks too many for your replay !!

[@khalidsaifullah6467]

@@TheBospear ​ Bospear I've already done that... i've installed Microsoft.Office.Interop.Excel -Version 15.0.4795.1000 from Nuget Manager. but still getting the same error. what to do ?? plz help

[@TheBospear]

@@khalidsaifullah6467 Could you paste your excel class here?

[@qlBlitzlp]

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?

[@TheBospear]

AppDomain.CurrentDomain.BaseDirectory

[@qlBlitzlp]

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, ???);

[@TheBospear]

@@qlBlitzlp just go through the column and test for string.IsNullOrEmpty

[@qlBlitzlp]

Thanks! I thought there was a special function for this.

[@saikirangattu2924]

very easy and simple to use

[@beenay18]

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]

winforms .NetFramework and create new class, name it Excel.cs.

[@beenay18]

@@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.

[@TheBospear]

@@beenay18 Yeah, these tutorials are made for C# devs since years ago Microsoft had little to none documentation on this library.

[@hiteshbhargava7042]

"An object reference is required for the non-static field, method, or property" getting this problem need help

[@TheBospear]

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.

[@RobertShane]

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]

Check the file location, with Excel library base location (@"*.xlsx") isn't applocation, it's in Documents folder

[@RobertShane]

@@TheBospear I don't understand

[@TheBospear]

@@RobertShane Check the location of the file you are trying to read. It's supposed to be in Documents folder.

[@RobertShane]

@@TheBospear You mean the excel file I'm trying to read? It has to be in the documents folder?

[@TheBospear]

@@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

[@AdithyakumarCR]

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]

You're missing a dependency, install Excel it self.

[@AdithyakumarCR]

@@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

[@mhdfirassbarakat6587]

What Is The "Form1_Load" ?? Please

[@TheBospear]

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.

[@tsuki-no-akuma]

Gods, as someone with misophonia I gave this vid an honest try, but had to duck out at like 40 seconds.

[@donstz]

What If I have Microsoft.Office.Interop.Excel -Version 14? There is no property Value2

[@TheBospear]

Value, Text etc.

[@donstz]

@@TheBospear Couldn't find any way. Finally NuGet LinqToExcel did the job

[@yanik4962]

Why is it exel.ReadCell(0,0));? I thought Excel starts at 1,1

[@TheBospear]

because I added i++ and j++ in the ReadCell method to keep consistent with 2d arrays that start from 0,0