Make an Excel Editor with C#! - Create, Write, and Read Excel files with C# and .NET

this video is sponsored by lib Exel but more about them later reading and writing Excel files sounds like fun to me at least that is if my application can do it for me but how would we do that I mean writing a text file might be straightforward but respecting the Excel formatting sounds a bit more complex well it doesn't need to be let's put together an application in this video capable of doing just that with the help of C andn Net let's get right into it before we get started let's make sure we have all all the necessary software installed you'll need visual studio for this tutorial if you haven't installed it yet head over to the visual Studio website and download the latest version make sure to include the net desktop development workload during the installation process luckily however you don't necessarily need Microsoft Office for that so you can skip that one all right once you've got that sorted out let us create a new C project for this simple example we will build a console application as this app focuses on Excel file handling so search and select the console app from the list then give your project a name choose where to save it on your drive and where to save the solution we will save the solution in the same directory choose. Net 7 and select do not use top level statement click on Create and now let's set up our requirements for the application to work so the open XML package is what we need so this package essentially will allow us to interact with the Excel files without any need for specialized software so go to the project tab manage new get packages and search for open XML you should get document format. openxml as your first result install that and you should be good to go now let's start coding at the top of our program CS file you'll see some using statements this would be to include all the necessary libraries from our open XML package so it's using document format doop XML then the same with packaging and with do spreadsheet document format open XML here is the main name space for the open XML SDK the packaging one is the name space that contains classes that help us package and manage the Excel files and the spreadsheet statement is the namespace that contains classes specific to excel operations next we have our program class and the main method if you selected do not use top level statements before it should just be here if not you can just add them now now we can begin with the main part first we essentially want to start the interaction with the user so we'll print some messages and ask for the file name of the Excel file they want to work work with next we check if the user has provided a file name if they haven't we'll use a default file name we also check if the file exists on the disk so here we are using an if statement to check if file path is empty if it is we set it to a default value of your file. xlsx so in this if statement we're using file. exist to check if the file actually exist on the disk if the file doesn't exist we'll create a new one using open XML SDK here we're using a using statement to manage the resource spreadsheet document. create is used to create a new Excel file the spreadsheet document type. workbook specifies that we're creating a workbook next we're creating a new workbook part which is essentially the container for the Excel workbook we then initialize it with a new workbook object here we are creating a new worksheet part and initializing it with a new worksheet object we also pass in a new sheet data object which will hold the data for this worksheet this line creates a sheet collection inside the workbook this collection will hold all the individual sheets in the Excel file here we're creating a new sheet object and setting its properties we then add the sheet to the sheets collection and finally we save the workbook and this writes all our changes to the dis now since we already have something going on let's see if our logic works out press on run wait for it to launch perfect it's asking for a file name write one down then something like this should be fine and enter all right this should have created our new file because on obviously this one wasn't created yet let's check for that click on program.cs open container folder bin debug Net 7 and there we have it great that should give us now an Excel file to work with no matter if it exists or not so let's start with the file reading next before we continue though I want to take a quick minute to talk about an amazing tool that can make your life a whole lot easier when it's it comes to handling Excel files I'm talking about today's sponsor libxl libxl is a powerful library that allows you to read and write Excel files without needing Microsoft Excel imagine the convenience of exporting and extracting data to and from Excel files with minimal effort and guess what it's not just for C it supports multiple languages such as C C++ dely PHP Python and even fortron li XL offers a plethora of features that go beyond standard options it supports both old and new Excel formats from Excel 1997 all the way to 2021 plus it has separate editions for Linux Mac and iOS and when it comes to Performance we're talking about writing speeds of up to 2.1 million cells per second for numbers that's blazingly fast and the best part it comes with a royalty-free distribution meaning you can use the library in your commercial applications without any additional fees so if you're serious about taking your Excel operations to the next level you've got to check out lib Exel visit their website which we've Linked In the description below to learn more and get started now let's read the data from the Excel file so we must open the file so that we can then extract the data from it for that again we use a using statement to manage the resource spreadsheet document. openen is used to open an existing Excel file the false parameter specifies that we're opening the file in readon mode great we've opened the Excel file and now we'll read its content here we are getting the workbook part from the opened document this part contains all the data and definitions for the workbook in this line we're using Link's first method to get the first sheet object from the workbook this is where our data is stored if you want to know more about link we're going to upload a tutorial on link in depth very soon and then here we're getting the worksheet part associated with the sheet we just found this part contains the actual data in the sheet next we get the sheet data object from the worksheet this object contains all the rows and cells in the sheet here we are checking if the sheet is empty if it is we print a message to the console that will be the case for example for when we create a new file with the application as it's obviously going to be empty if the sheet is not empty though we Loop through each row and cell to print the content to the console there we go that would be our second milestone reached let's check out how it looks in our app press run and there we go it asks as for our file again let's use the same one again as before and it opened it read it and told us that it's empty which makes sense we just created it in the previous step now comes the moment where we actually write to it all right for that we will follow a similar pattern as with reading just with a few key differences let's get to it first naturally we ask the user if they want to add a new row to the Excel file we then read their response if the user's response is why which stands for yes we proceed to add a new row this is just a simple addition to maybe handle other kind of responses depending on the response although ready for any kind of continuation you may think of like giving them a list of options or asking what they want to do next we will simply use it as if you say no the application will just end enough for this video scope I'd say if the user enters yes though we then ask the user to enter the data for the new row separated by commas we then split this string into an array and this is how this application is going to do it but you can imagine that there are many other options as well if you wanted to use them so we then open the Excel file again but this time with right access by setting the second parameter to True here we create a new row object and loop through the user data to create a new cell object we then append these cells to the new row and the new row to the sheet and well that essentially does it this will take our output and write it inside of our Excel files separated by cells finally we print a thank you message to the console great let's test it now and see how it runs as before run it give it your file name file is empty correct yes and now let's just add John comma do comma and let's say his age which is for example 30 now it will finish and close with a nice closing message and now we can rerun to see how the application will read our newly added data and if we do that add our file name there it has read and printed out the data we just added if we go ahead and open the file itself as well we should be able to see it written in there just open containing folder again bin debug net and there it is double click and here is our newly added entry perfect so there we have it our application works correctly and yes in its current form you would need to rerun the program to read the new data and rerun for any additional line you want to write but you do have the functionality working this means that now it is time for you to improve the usability and accessibility as well as the general looks of the application this app can easily be converted to use some visual framework like WPF for example so yes now it's your turn get this app and make it competitive do you want to learn how well for that we got plenty of tutorials as well for Maui check out our latest Maui video where we build a crossplatform app in mere minutes and for WPF well what about our in-depth WPF video where we build a full journaling application that even uses a database the possibilities are endless and with that if you found this video helpful please hit the like button and if you're new here don't forget to subscribe for more content like this thanks for watching and as always happy coding

Channel: tutorialsEU

Views: 21,501

Keywords: Tutorials, Tutorial, Programming, Course, Learn, guide, development, programmer, video course, video tutorial, learn how to, how to, learn c#, c# course, c# tutorial, dotnet framework, .net core, .net, what is cloud computing, cloud computing explained, cloud computing tutorial, microsoft azure, azure tutorial for beginners, azure cloud, what is serverless, serverless architecture, serverless computing, cyber security course, csharp tutorial for beginners, csharp tutorial, c#

Id: 6gMDGbf9XZ4

Length: 12min 17sec (737 seconds)

Published: Mon Sep 18 2023