C# Excel Tutorial: How to Read and Write Data from Cells

hello everyone in this video we will go over how to write data to individual cells in excel using the excel interop library we will also go over how to read data into variables we will then go over how to save workbooks programmatically in front of us we have a basic visual studio project that has the interop library installed and we'll create an empty workbook if you have any questions on how to set this up feel free to refer to my other video that i will link down in the description let's go ahead and run the project as is as expected it created an empty workbook the first step to reading and writing data is to create a reference to the worksheet to do this we will type worksheet worksheet equals sample workbook dot worksheets sheet1 now when it comes to referencing worksheets there are a few ways to do it the first is what i've just done which is pass the actual name in a worksheet which in this case is sheet1 alternatively you could pass the position of the worksheet like this it's important to note that the starting index for worksheets is one this may feel a little odd since it's programmers we've gotten used to using a zero based index system for a a common theme throughout the excel interop library is that it is usually not zero based referencing the position of certain elements like worksheets in this case now that we have a reference to the worksheet we can start writing to individual cells we will start by changing the value of just one cell to do this we will type worksheet.range a1.value equals sales amount i view sales amount as an example header this header text will be written to cell a1 which is what i have passed into the range object the range object is what is used to represent one of multiple cells let's go ahead and run our code as you can see the text was written into cell a1 so that's how you write static text into one cell but what if you have a bunch of data that you want to write into cells dynamically for example let's say we have this array of sales data where each value represents the price of a sold item and we want to write this data into one cell after another to do this we will need to use a for loop so let's type four and x equal to zero x less than sales data.length x plus plus then we will type worksheet.range a plus 2 plus x that value equals sales theta x let's break down this line a little bit for each iteration we are changing the cell we are working with the first iteration we have a2 since we have 2 plus x and x is zero and for each iteration after we're incrementing by one so we'll have a two a three a four and all the way until we finish the loop let's go ahead and run our code the program has now written each element of that array into column a starting from a2 and has worked its way down now instead of writing data into just one cell what if we wanted to write the same data into a range of cells we can do this by changing the syntax of the parameter we passed to the range object we can do this by typing worksheet.range b2 colon b6 so instead of specifying just one cell we can specify the first cell colon then the last cell and then we can assign whatever value we'd like in this case i will type dot value equal sale let's also assign a header to keep things consistent let's go ahead and run our code so now we've taken the text sale and wrote it into every cell within the range we specified when working with ranges you may end up working at the same range over and over again it might be easier to declare a range object in that case to do this we will type range range equals worksheet.range a2 colon a6 this makes things a little bit easier if you know you will use this range often if we test this out we'll see that we'll still get the same result this covers different ways to programmatically write data to excel files but now let's talk about reading data reading data is pretty straightforward we will type string cell data equals double quotes plus worksheet dot range a2.value we add the double quotes here as an easy way to cast the cell contents as type string we do this because when we read data it will be read as whatever data type it is in excel so if we have a cell that contains numbers it will be run as an enter or double which will lead to a tight mismatch error so to avoid this i typically always prepend a double quotes in the beginning of the statement i'll go ahead and print out the result as well as add every line statement so that we can stop our program from ending let's go ahead and run our code as we can see in the console we have printed the contents of cell a2 which happen to be test reading data like this can be very useful depending on what you're doing you may read data and then apply formulas to it apply conditional logic based off of it etc lastly after working with our workbook we will want to save it saving is very straightforward to do this we will type sampleworkbook.save as and we will pass in the save path for the workbook we've created let's go ahead and run our code that's all for this video if you found this video helpful and would like to see more like these please like and subscribe to the channel also if there are any topics i haven't covered feel free to suggest them in the comments i may make a video about them in the future thanks for watching

Channel: Software Engineering Skills

Views: 11,745

Keywords: excel automation, microsoft interop, interop, interop excel, c#, coding, how to read data programmatically, excel code, coding excel, automation, automating excel, microsoft office interop excel, excel interop

Id: 2FeS-Raf0lA

Length: 6min 11sec (371 seconds)

Published: Thu Jan 27 2022