Creating Excel Files in C#
Vložit
- čas přidán 22. 05. 2024
- One of the most important tools in business is Excel. Spreadsheets are an easy way to visualize and manipulate data that almost any user can operate. So it makes sense that you would benefit from outputting your data to Excel. In fact, you can even use Excel as your default reporting tool. In this video, I am going to show you how to output data to Excel, format the document, and then also read data from Excel back into C#.
Source Code: leadmagnets.app/?Resource=Cre...
Mailing List: signup.iamtimcorey.com/
Purchase Courses: iamtimcorey.com
0:00 - Intro
0:58 - Creating Demo Console App
2:40 - EPPlus NuGet reference and terms of use
4:56 - Building demo app: C# code
7:00 - Saving to excel file: data model, async methods
15:41 - Saving to excel file: using statement
19:40 - Saving to excel file
26:56 - Styling the excel cells
35:55 - Note on created excel files: metadata
39:21 - Read from excel file
51:15 - Summary and concluding remarks
I wish I could like this video multiple times. There are so many tips and pro tricks, plus the well structured tutorial, making it an amazing source of information.
Great video, thank you very much!
You are most welcome. Thanks for watching.
Hey Tim.. I guess you've just answered one of my wish list.
In fact, I just mentioned this in your last video (TimCo Phase3 Planning) then this video arrived.
Appreciate it Tim!!! Wish you have a nice day.
You are most welcome. Thanks for watching.
My 11y/o granddaughter wants to try one of your courses, been working with her on her own personal phone book. I'm glad your still doing this, I just wouldn't have the time to put all this together for her. Keep up the good work! Worth every penny, and every minute...
Great!
Thanks a lot Tim, this video helped me to carryout a task for my company. Thanks
You are welcome.
Thank you Tim, that was a great tutorial. Answered most of the questions.
You are welcome.
@@IAmTimCorey Hi, I have been stack for a month now, how can I display a product card with image, name, and price in winforms app? Is it possible?
Thank you so much! well explained, high quality videos as always. you are the best :D
Glad you like them!
Tim! Thank you for useful video. It was really helpful for my styding.
You are welcome.
Many thanks for this tutorial; it's enabled me to create an easy report.
Excellent! That is Tim's goal!
Been using GemBox Spreadsheet for years for OpenXML (Excel) spreadsheet files. Works really well. Has a free and paid option depending on complexity and size of the file.
Interesting, thanks for sharing.
First once again, really great content in addition to the primary topic in your “using” commentary. Just terrific stuff there. I’ve done probably hundreds of operational reports accessed via web API endpoints that returned operational reports in excel. ClosedXML has worked so far but looking at this other package if it doesn’t handle what I need going forward. I have also always found recording macros in excel to do what I need a great way to find what excel wants to to what you want.
Much appreciated! Thanks for sharing your approaches.
Great video Tim, thanks for this!
My pleasure!
Thank you so much for all those tutorials.
You are welcome.
keep going, Man. Your way of explanation just as it should be.
Thanks!
Favorite quotes from this episode:
03:14
"If you're using it for commercial purposes you should be paying for it. That's what they have required, and I definitely support their desire to eat."
09:51
"First Name Equals...uh, not Japanese Calendar..."
Thanks for this video, Tim; you do a wonderful job keeping the content engaging.
Just keeping it real
Oh man, where were you with this video 3 months ago when this was my project at work!? joking aside, thank you! You implemented this in a way cleaner way than I was able to and I'm going to be stealing a fair number of your methods
Glad I could help!
What a great video, I might have some suggestions on reading from an Excel file but let try them first.
Several folks have shared tips and we welcome yours also.
Best c# instructor.
Thanks!
Just what I was currently working on.
Even me. I was just discussing about excel and its requirment in Bigdata companies
Great!
Same
One topic I would love to see covered and even have a full course would be source generators. There are lots of good things that can come from it. Would love to first see a most simple one, then move on to more advanced ones and even how to debug them. I read that using source generators can really help on performance since reflection is unfortunately too slow.
I will add it to the list. Thanks for the suggestion.
Very useful, thanks so much!!!
You are welcome.
very helpful sir, Thank you !!🙌
You are welcome.
Very useful topic. Thanks
You are welcome
Ah!...saved my soul.. thank you sir..thank you very much
Happy to help
Hi Tim, thanks for this video. Do you know how to output hierarchal data to excel? I tried to extend your example and add a List in the PersonModel so that each person has a collection of something. When I save it to excel it doesn't work. It would be ideal if excel can use collapsible rows to handle hierarchal data. I look on the internet and couldn't find any example to do that. Do you think it is possible to have regions of collapsible rows to handle parent child structure? Like a small square with a plus and minus sign. If it is a plus sign you can click on it and expand and show the children rows. If it is a minus sign you can click on it and collapse to hide the children rows. Thanks.
Thank you, Tim
You are welcome.
Hey Tim! Nice video, as always. Related to this, any thoughts on ClosedXML and why would I choose EPPlus over it?
I prefer the richness of the features of EPPlus but if ClosedXML works for your needs then go for it.
Hell yeah! Thanks Tim!
You are welcome.
Now watching and looking for ideas to utilize more features of Excel and same time, cleaner code pattern of it :D
Great!
This and DocX are my reporting bread and butter. Makes life so easy. (Edit: Also CsvHelper ^^ )
Great!
Thanks Tim, great video. I was just wondering how do you get those little tags inside functions that tells you what the argument is (like "printheaders")?
Thanks
Just found the answer in previous comement by Tim : " Here is the video (and timecode) where I explain how to use it: czcams.com/video/f0YeVirKPfw/video.html "
Hi.
Great as always. Thanks
You are welcome.
Hello Tim,
I am working as a Supply Chain Analyst. We have 3 different excel files which are based on SAP data.
I want to create a Windows Application which is going to consolidate all of excels on a page. After that, I need to add some formulas for each column to make a better analysis of the data.
Is this application can with multiple excel files?
Thanks for this video. I was not aware of the .xlsx format. The information you mentioned in 35:00 - 39:00 was new to me and got excited to learn that. I must say I not only watch you videos but also go through the comments without fail. I must say that your viewers are also genuine knowledge lovers and I always love to read the comments in your video plus your responses. Can you please make one video for handling AppSetting or Environment variables in the projects. With Transition to dotnet core I would like to know your take on that as well. Thanks once again and best wishes !!!
I think this is what you are looking for: czcams.com/video/_2_qksdQKCE/video.html
I'm glad you enjoy my content.
Thank you very much teacher
You are welcome.
Excellent !!!
Thanks!
Nice video, sir!
Thanks!
Cool. Thank you!
You are welcome.
Love your C#.
Thanks
0:00 - Intro
0:58 - Creating Demo Console App
2:40 - EPPlus NuGet reference and terms of use
4:56 - Building demo app: C# code
7:00 - Saving to excel file: data model, async methods
15:41 - Saving to excel file: using statement
19:40 - Saving to excel file
26:56 - Styling the excel cells
35:55 - Note on created excel files: metadata
39:21 - Read from excel file
51:15 - Summary and concluding remarks
I hope these time codes are helping to grow this channel!
Would be great if Tim did include these by default
@@noijze I've heard hat is impossible.
I partially agree though.
I might miss something to point out or may not have best description for the chapter title.
On the other hand, I like to share my notes with others :)
@IAmTimCorey
Hey, Tim. If you have prepared a list of topics you gonna talk about in video, you could just use some kind of stopwatch to keep track of times when you switch to the new topic...
I know it will add time to your video post editing, but I believe that you said it takes you only 3 minutes anyway...
Just think about it?
Thanks again Ralfs. The issue with adding them myself has to do with timing. It seems simple to keep track of my timecodes, but the problem is the editing process. When I create a lesson, I don't strictly follow an outline. So, I would need to record that outline on the fly (or at least the parts that deviated from my rough outline). Then I would need to record the timecode for when I start the topic. That would mean breaking my train of thought in order to record the timestamp and what I'm getting into. Then, I would need to adjust all of those timestamps. You see, when I record, I start with about 10 seconds of silence at the beginning. This allows me to later remove the background noise of the room by training the system to know what silence sounds like. This means that all of my timecodes would now be off by about ten seconds. All of that would mean producing videos more slowly. That, in turn, leads to less content.
For now, I've chosen to leave off the timecodes in order to speed up the process significantly. Eventually, I will have enough work to pay an editor to handle my videos full-time. In that case, adding timecodes will be part of their job. That way I can still produce videos at a reasonable speed while also providing more value to the viewer. That's part of what the paid content is helping to fund (or will help once we get to that level).
Thank you ❤️ how can i change the file to be saved in any device using my app ?
1. C# 7.1 for async Main
2. using directive vs. using statement.
Thanks for sharing.
I see the possibilités! Data entry and reporting as you point out, but also a data storage solution between databases and CSV/text files.
Yep.
Hi Tim, another great video as always. But I never thought I would see the day when you left a "bug" in your code.
Using FileInfo to load the Excel file keeps it in use until your application is finished. You can read from it and can write to it but you can't delete of move it.
Using FileStream ( even with OpenRead ) does free the file.
No big deal for this demo but in real live this may be a problem.
Only found myself when I tried to delete the file from the code ( and it took me some time to find the reason ).
Yep. The way I treated this application was as a whole. In a real application, I would take what I did here and put it all behind one method. Then the file would be released when the method is complete.
Hi Tim. Do you plan to make a video on outputting data to MS Word? Thanks for good example! EPPlus is my favorite Excel library.
Possibly. It is on the suggestion list.
Hey Tim, sort of unrelated question, do you plan to or have you already released any material/video/course on msbuild and csproj files? I would definitely go for it. Keep up the good work!
No, I don't currently
Tim what is the best way to do the following. I want open the file on button click and save it (which your video does show)
Hello Mr.Tim I was wondering exactly how you get those tags you mentioned at 23:09
Always so helpful!
Also quick question:
Are those little tags inbuilt Visual Studio function or i need some kind of extension to use it? Couldn't find it by myself and it seems very helpful.
Which tags are those? The ones over methods? Those are built-into Visual Studio (all editions).
@@IAmTimCorey The ones You were talking about in 23:08 ("PrintHeaders" etc.).
Thanks for response.
As always, great tutorial, many thanks for taking the time to teach us all that great stuff. Much appreciated!
I wonder if you could try to create a ’How To’ of how to stream a whole WPF Form to PDF Format,
like an invoice or receipt, without using the windows built in print to pdf function from the print preview, but rather with a normal SaveFileDialog.
It would be interesting to see a professional approach on that.
I know there is prebuilt stuff like SyncFusion, which is without a doubt a very good solution, but also an expensive one.
Thank you. I have added this to my list of possible future topics. This seems to get asked for a lot...
@@IAmTimCorey Yes indeed, i wonder why it is not asked for even more. But nice of you putting it on your list.
One question. Lets say I want to create a button just like the ones we create with the internal VBA that excels provide by linking an macro in there to a form we input in the excel file. How would we do that with the macros we created with c# with VS?
You can use the EPPlus version 4 and that version is free to use for commercial use and its still really good
Thanks for the tip.
In one of your videos a while back you talked about getting the community edition of Syncfusion (Blazor). I have used EPP in the past and found the Syncfusion XlsIO library much easier to use, richer and faster.
Thanks for sharing!
Tim you are the best pro teacher
Thank you!
@@IAmTimCorey Tim I have a doubt is wpf and webforms same?what makes them different?
No, they are entirely different. WinForms uses C# for UI placement, although you use drag-and-drop with a visual builder to actually place the items. WPF uses XAML for its layout and drag-and-drop is discouraged because it does not take advantage of the strengths of WPF (responsive layouts).
@@IAmTimCorey actually I am asking about webforms Tim not winforms
Webforms is a completely different thing from WPF. Webforms is a web application. WPF is a desktop application. Webforms is no longer getting updates and only works in the .NET Framework. WPF works in the latest version of .NET (and in every previous version).
Nice to meet you JapaneseCalendar Time Corey! btw recently i am exploring rest api (MSGraph) / CRUD approach -> excel stored on OneDrive, which i would be able then to display as a part of UI in webbrowser control. this way i can view this, process data either with UI or direct in Excel file and it will reflect on other layers. (without running Excel process and no Excel libraries). I dont know if that makes sense yet, however i got few legacy apps that would strongly benefit from this approach and would break from COM shackles.
Interesting. Let us know what you find
Best video thanksf or tutorial
Thank you!
I've seen my coworkers use interop to write to excel, well not good idea mind you. I replaced most of it with ClosedXML. But I think EPPlus is even better when you work on open source.
It is a great option.
Awesome video, if I use EPPlus at work in a class library and include it in my apps for internal line of business applications for internal use, not selling, would that be commercial use? I am using another library (OpenXML) now that is much more involved to write Excel files so I think I'd like to switch to EPPlus.
Yes it would.
You can use EPPlus versions prior to 5.0 for free since older versions are still under the old, more open license.
Great tip!
Like IText and WPFToolKit, but those were v4.0 they changed them.
Came here to write the same. Only version 5 and forward are under their new commercial license. If you use any version before v5, you are free to use it in commercial products.
The downside is, the SaveAsync ability is not available in pre-5.0 :(
Thank you Tim, that was really clearly presented. I thought reading from Excel will be easier. I have lot of whitespaces in my Excel so I use this method to count rows:
private int GetLastUsedRow(ExcelWorksheet sheet)
{
if (sheet.Dimension == null) { return 0; } // In case of a blank sheet
var row = sheet.Dimension.End.Row;
while (row >= 1)
{
var range = sheet.Cells[row, 1, row, sheet.Dimension.End.Column];
if (range.Any(c => !string.IsNullOrEmpty(c.Text)))
{
break;
}
row--;
}
return row;
}
I tried just:
int rowCnt = sheet.Dimension.End.Row;
But my loop ended faster than I want.
Thanks for sharing
Hi Tim, Thank you for your great effort. Is there a NuGet package you recommend us for generating a pdf file? For Report purpose.
Not a free one, no. There are paid products out there to do so, and a couple of free products that are tricky to use well but that's about it.
@@IAmTimCoreyThank you Tim for your reply. So is there free products you recommend to me other then RDLC
How do you get those tags? That would help so much with guidance.
Hey Tim, I wrote a similar thing few weeks ago. I was only reading data and importing them into SQL database. The biggest problem was the speed to save 9000 lines of data. Can you share your tips about this? How to import data from Excel to SQL server? I'm using Entity Framework Core 3.1.
Find out where your bottleneck is. Is it the loading or the saving? Once you figure that out, then figure out ways to optimize the process.
Pro tip from me when reading data from excel. The more universal way to read any value, doesn't matter the type is to use "ws.GetValue(row, col);"
Thanks for sharing your perspective.
Thanks bro for saving my job
You are welcome.
Hi Tim, should excel class be static? I have this doubt because I need to change every time file name because is decided at runtime, and I can't pass to constructor, so I'm thinking to pass file name to every method .
Thanks in advance.
Tim the BEST!!!
Hey Tim there is a way to show Microsoft Word App on a Wpf Grid or UserControl?
I need it please!!!!
That's trickier, because you typically need to use Word Interop, which requires Word to be installed. Some third-party libraries do something like it, though.
Hey Tim, what are a few places you would recommend for Asp.Net web hosting? Mostly for portfolio projects.
One that I found I really like is Interserver ( www.interserver.net/r/589892 - that's a referral link, by the way, but it doesn't affect my recommendation). It is $5/month for ASP.NET Core web hosting plus SQL databases. They have been reliable and cheap. I used them for my Getting Started with ASP.NET Core course. I showed off how to deploy each of the five ASP.NET Core types to the host, each with their own sub-domain.
@@IAmTimCorey Name Cheap + InterServer. Just watched your Web Hosting video. You did a great job on that. Thanks Tim!
I am having problem with other examples using Microsoft.Office...... and it not wanting to save the file when adding a Record to it. And i am wondering if this will save the file after i add a New record/data to an excel file.
How is it possible to report the function property name besides the value? Example. Name: "your string" ?
Thanks! i cant find reporting tool for .net core, but this can be a good one. but how we let the customers can put extra design on excel like there logo and other, so it can be a standard reporting? or do you have any other suggestion that can be a standard reporting for .net core. Thank you again
You could either put that into the code (you can add images and other designs using C#) or you could have the customer provide the formatted file and just put the content where they want it (a better solution).
@@IAmTimCorey Thank you! My brother, I'll try that
Any talk about an Excel 2022 that has VBA fully replaced by C#? Thanks
Hi Tim, Great tutorial, is it possible to convert complex PDF file to excel by using EPPlus. If yes, then how? I'll appreciate it.
No, it is not. A PDF isn't a good data source for anything. It takes a lot of work to extract data from it. Think of a PDF more as an end product.
One really good reporting solution actually that is completely free is blazor server side. I have found blazor server side the easiest and the most flexible for reporting. Its so easy to create sample components and pages that its so easy to create reporting solutions with blazor server side.
I agree, check out some of my videos on Blazor here - czcams.com/users/IAmTimCoreysearch?query=blazor
@@IAmTimCorey I actually watched all your blazor videos. I discovered there is much more you can do with blazor than even in your videos. I even created my own combo box in blazor and even have a custom mediaquerylist so for different screens, it can adjust whether its portrait or landscape or can change whether its running on different form factors like phones, tablets, etc.
This is pretty straightforward. Just last night, I tried a Nuget package called Open-XML-SDK. Is this to what you are referring when you say Open XML?
OpenXML is a standard that can be used to work with Excel files. That SDK is one option. Another option is ClosedXML.
In 31:41, ws.Row(1).Style.Font.Color.SetColor(Color.Blue);
it worked when I added using System.Drawing; on the top of the code :)
Thanks for sharing!
I've used Npoi and EPPlus and i would say that EPPlus is the better option. I dont know about ClosedXml since i personally have never used it but i've heard it has issues regarding file sizes just like Npoi does which is why i switched to EPPlus which is able to read excel files that are over 15 mb
Thanks for sharing.
Hey Tim! Any ideas on how would you go about opening an excel file based on the file extension instead of the entire file name?
Using the System.IO namespace, you can search for files in a directory and filter them by extension. Then, just use that list for opening up your Excel files.
@@IAmTimCorey Finally got it to work! Thank you for the advice for all the time you put in these videos to help us! Really appreciate it!
Thank you. But is there's a way to save the data from C# to the existing excel file? Any literature to recommend? Appreciate your help.
Check out the other libraries listed in the comments below while waiting for a reply. Maybe one of them can help you.
@@tomthelestaff-iamtimcorey7597 Thank you sir.
Hi I'm trying below in C# to format a range in excel, I don't know what's wrong with that.
Range.ReadingOrder = xlContext;
Can you help me please
Hello Tim. How can I manipulate excel objects but from Visual Studio Code? How is the processes to download the "Microsoft.Office.Interop.Excel" and use it in VS Code platform. Thanks.
The processes in this video will work in VSCode as well.
Mr Tim can you do a video about report generation with WPF? like RDLC report? or anything else similar?
I noted your recommendation and have added to my list of possible future topics, thanks.
@@IAmTimCorey thank you :)
I need two headers and some headers will contain the same column name .. How can i do that ?
Damn I was thinking on how to automize my excel for work. Can you save with this directly to an email or only locally? I suppose thats not a big deal tho just some manual copy pastes so that is really useful. Good job man keep going!
You need to save the file locally/on a network but you can then attach the file to an email. How to send email from C# is something that I will be covering soon.
@@IAmTimCorey Thanks man. Sending email throught C sharp is something I am really looking forward to(because you need to send an email every time someone registers). Again thanks for everything you do!
Any suggestion on free tools for generating spreadsheets?
Interop or epplus which should i go for updating to office 365 in by C# code in vs
I would lean away from Interop. That's a messy system to try to support, especially when there are better options out there like EPPlus and Syncfusion.
Great tutorial really very useful,
can you make tutorial for generating pdf files in C#
I will add it to the list. Thanks for the suggestion.
Tim, Question, How would you make the Main method Async Task, in Core 6, as the Main Method is not on show ?
It is async by default. You don’t need to do anything. It will just work.
@@IAmTimCorey Thanks Tim, just getting my head around the new template :)
I am working in Blazor, and was trying to implement this on the client side instead of the server. (result: no file was created) Thanks for pointing that out!
I’m glad it was helpful.
Is there something I might have missed in getting the address argument name to appear before you typed it in. I can do it with a ctrl ., but yours looked automated? Thanks
Sorry, I found the answer in 5 tip in core 5.0
The newest version of Visual Studio has the option of auto-adding using statements when it can determine they are needed.
may i ask, does this also apply on online excel? specifically the online version of excel in which collaborators may use/view.
This is about creating the actual Excel files. Online Excel still works on an actual Excel file. So, you can still open the files in online Excel.
EPPlus vs ClosedXML, which of these (or any other library) handles large datasets (~100 cols x ~100k rows) more efficiently.
Not sure. My guess is that it will be close to the same. Both are going to be slow because the XLSX standard isn't designed to be a database. Putting that many data points in the file will be tough.
Great work Tim, could you do a vlookup example with excel if possible?
Thanks for the suggestion. Please add it to the list on the suggestion site so others can vote on it as well: suggestions.iamtimcorey.com/
Curious, why do you need this when you can just connect to the excel object and do whatever you want? You can use Office.Interop or just the excel object without interop.
I use interop all the time! I prefer native myself but Tim’s videos often substitute packages.
You can't use interop without Excel being installed. That is a problem when you go to use it in production. You don't want a dependency on Excel, especially if your web server is the one generating the files.
I just swapped out Office.Interop.Excel with "Object" for production and it works great. Had to do this with a few applications for reports. Once you do that though, you lose intellisense. This is the free route, maybe not the best. :)
if your company has the money to have excel installed on a production server, they also have the money to purchase the commercial license of EPPlus. My company is so poor that they dropped office and went for libreoffice
That package is really nice, but is still doesn't understand the license option. can i use it to develop an app for my hospital?
I think commercial means you make money off of it. If you never make any money off it you should be good to go
Because you seem to fall under the Noncommercial license umbrella, you should not need to pay for it (consult your legal counsel, though, since I am not a lawyer). The relevant passage of the license says "Use by any charitable organization, educational institution, public research organization, public safety or health organization, environmental protection organization, or government institution is use for a permitted purpose regardless of the source of funding or obligations resulting from the funding."
Hello. I get data in real time mode (for instance every minute) and want to add them (create new row) to current exceel file. Can I do that using EPPlus library ?
Sure.
Hi Tim, I have a question, when I auto complete the statement there is no “address:” in grey color show up, how I can get this feature. Thanks you a lot
Here is a video on how to turn that on: czcams.com/video/morBKqtqmso/video.htmlsi=gnzzHX_qCr0eMq5u
@@IAmTimCorey You are the best, it helps me reading C# Robot Code easily
For simple objects Excel documents are ok. but for complex objects I would export and import with json though. But I understand your need of using Excel documents to import/export data. Thanks to small Companies Who still use Excel to store data! Its 2021 they still using it...
This isn’t the same as data export. This is to give to users to work with. Json does not solve that issue. Also, Excel is used in all company sizes.
@@IAmTimCorey Yes I get you. I know that Excel documents is for end-user... What I cant beleive is lots of companies still using those files for data stores. And we are in 2021... Its crazy
@@the-matrix-has-you But Excel is great for storing non-complex data. Also, filtering, sorting and calculating data is easier for users using Excel.
It's possible in windows application c#?
Why are we not working with Office interop objects ? That seems like the more native way, doesn't it ?
Couple of reasons - first, interop is messy. Second, in order to generate an Excel file using interop, you need to have Excel installed on the machine that is generating the file. In my example, it is clean, easy, and does not require Excel to be installed on the machine running the code.
How about creating .DB Files and fill them from sqlserver database ( please try to make video ) ... Thanks in advance
Topic suggestion noted and have added to my list, thanks.