EPPLUS Library - Beginners Guide Part-1
Hindi Video : Click here
Source Code : Click to download [672 KB]
What is EPPlus?
- A library to manage excel spreadsheets. EPPlus is a .NET library that reads and writes Excel 2007/2010 or higher files using the Open Office Xml format. It is support .xlsx, .xlsm excel file format.
Why we are using EPPlus?
- First of all EPPlus is a free tool.
- We can do same thing by using Microsoft interop excel. When you are like to doing server side office automation. That is not a supported scenario by Microsoft. Follow this link : http://support.microsoft.com/kb/257757
How to attach EPPlus Library :
- Option 1 : Download it from : http://epplus.codeplex.com/
- Option 2 : To install EPPlus, run the following command in the Package Manager Console
PM> : Install-Package EPPlus
EPPlus Supports :
- Cell Ranges
- Cell styling (Border, Color, Fill, Font, Number, Alignments)
- Charts
- Pictures
- Shapes
- Comments
- Tables
- Protection
- Encryption
- Pivot tables
- Data validation
- Conditional formatting
- VBA
- Formula calculation..etc
Today we are learning :
- Create a demo an epplus project using .NET technologies (Language C#)
Open Visual Studio (Create a console application) :
using OfficeOpenXml;
using System.IO;
using System;
//add these namespace
class Program
{
static void Main(string[] args)
{
ExcelPackage ExcelPkg = new ExcelPackage();
ExcelWorksheet wsSheet1 = ExcelPkg.Workbook.Worksheets.Add("Sheet1");
using (ExcelRange Rng = wsSheet1.Cells[2, 2, 2, 2])
{
Rng.Value = "Welcome to Everyday be coding - tutorials for beginners";
Rng.Style.Font.Size = 16;
Rng.Style.Font.Bold = true;
Rng.Style.Font.Italic = true;
}
wsSheet1.Protection.IsProtected = false;
wsSheet1.Protection.AllowSelectLockedCells = false;
ExcelPkg.SaveAs(new FileInfo(@"D:\New.xlsx"));
}
}
using OfficeOpenXml;
using System.IO;
using System;
//add these namespace
class Program
{
static void Main(string[] args)
{
ExcelPackage ExcelPkg = new ExcelPackage();
ExcelWorksheet wsSheet1 = ExcelPkg.Workbook.Worksheets.Add("Sheet1");
using (ExcelRange Rng = wsSheet1.Cells[2, 2, 2, 2])
{
Rng.Value = "Welcome to Everyday be coding - tutorials for beginners";
Rng.Style.Font.Size = 16;
Rng.Style.Font.Bold = true;
Rng.Style.Font.Italic = true;
}
wsSheet1.Protection.IsProtected = false;
wsSheet1.Protection.AllowSelectLockedCells = false;
ExcelPkg.SaveAs(new FileInfo(@"D:\New.xlsx"));
}
}
- Now build & execute this code. File is (New.xlsx) store on D: drive of computer.
Please subscribe my YouTube Channel & don't forget to like and share.
| YouTube : | https://goo.gl/rt4tHH |
| Facebook : | https://goo.gl/m2skDb |
| Twitter : | https://goo.gl/nUwGnf |
thanks so much, i am a new user and i have few questions, i already have an excel file(will all formatted) and i just want to insert values into the sheet. i have to call the sheet from embedded resource.i am making xll plugins using excel-dna and now i want export the result into the sheet and save in local drive.Thank you
ReplyDeleteThanks for your tutorial. It help me to begin with EPPlus
ReplyDeleteNice but what is the purpose of wsSheet1.Cells[2, 2, 2, 2] ?
ReplyDeleteI mean [2,2,2,2] and can I create dynamic columns?
start from B2 end on B2 b is 2
Deletestart and end in B2 cell
DeleteNice snippet to start with .. but what is the purpose of doing wsSheet1.Cells[2, 2, 2, 2] ? I mean 2,2,2,2 ? and can I create dynamic columns using EPPlus? Probably yes! then how?
ReplyDeleteYou will select the cell with them
Deletefor example,
excelWorksheet.Cells[FromRow: 5, FromCol: 1, ToRow: 5, ToCol: 3]
OR
excelWorksheet.Cells[5, 1,5, 3]
OR
excelWorksheet.Cells[Address: "A1"]
OR
excelWorksheet.Cells["A1"]
Get the count value if you want post data Row by row,
DeleteexcelWorksheet.Cells["A" + count].Value = "something";
excelWorksheet.Cells["B" + count].Value = "something";
excelWorksheet.Cells["C" + count].Value = "something";
Provide with the Link to get EPPLUS dll. the current one is not working
ReplyDeletecan you show how to read from an existing Excel Spredsheet please?
ReplyDeleteThanks, this is the best resource I've found in the web about EPPlus to get started.
ReplyDeleteThanks Rui
Deletecould you show how do we read and edit spreadsheets?
ReplyDeleteSir, I have seen ur video on upload to Google Drive using vb.Net I want same in vba I want upload my excel file converted to pdf.
ReplyDeleteCan I save the Excel file in C > Desktop? Because it doesn't work
ReplyDelete