Featured
EPPlus - Create advanced Excel spreadsheets using .NET 6
Last modified: March 07, 2022Create Project
Create an ASP.NET Razor Page application.
Install EPPlus Excel
.
For more detail on EPPlus Excel package, click Here
Create an Excel File
using (var package = new ExcelPackage())
{
var worksheet = package.Workbook.Worksheets.Add("ExchangeRate");
//add headers
worksheet.Cells[1, 1].Value = "Amount";
worksheet.Cells[1, 2].Value = "Currency";
worksheet.Cells[1, 3].Value = "Amount";
worksheet.Cells[1, 4].Value = "Currency";
//Add some items...
worksheet.Cells["A2"].Value = 1;
worksheet.Cells["B2"].Value = "USD";
worksheet.Cells["C2"].Value = 7.8;
worksheet.Cells["D2"].Value = "HKD";
//Add some items...
worksheet.Cells["A3"].Value = 1;
worksheet.Cells["B3"].Value = "GDB";
worksheet.Cells["C3"].Value = 10.3;
worksheet.Cells["D3"].Value = "HKD";
worksheet.Cells["C2:C3"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
worksheet.Cells["C2:C3"].Style.Font.Bold = true;
var xlFile = new FileInfo(@"C:\Temp\createExcel.xlsx");
package.SaveAs(xlFile);//save the Excel file
}
Read an Excel File
var filePath = new FileInfo(@"C:\Temp\createExcel.xlsx");
if (filePath != null)
{
using (var package = new ExcelPackage(filePath))
{
var firstSheet = package.Workbook.Worksheets.First(); //first worksheet
var endRow = firstSheet.Dimension.End.Row; //total no of Rows
var endCol = firstSheet.Dimension.End.Column; //total no of Column
//first row is the header so we skip that and start from 2
for(int i = 2; i <= endRow; i++)
{
for(int j=1; j <= endCol; j ++)
{
var value = firstSheet.Cells[i, j].Value.ToString();
}
}
}
}
Export to CSV
var filePath = new FileInfo(@"C:\Temp\createExcel.xlsx");
if (filePath != null)
{
using (var package = new ExcelPackage(filePath))
{
var firstSheet = package.Workbook.Worksheets.First(); //first worksheet
var endRow = firstSheet.Dimension.End.Row; //total no of Rows
var endCol = firstSheet.Dimension.End.Column; //total no of Column
var endColLetter = ((char)(endCol + 'A' - 1)).ToString() + endRow.ToString();
var format = new ExcelOutputTextFormat
{
Delimiter = ';',
Culture = new CultureInfo("en-GB"),
Encoding = new UTF8Encoding(),
};
var csvFile = new FileInfo(@"C:\Temp\exportToCSV.csv");
await firstSheet.Cells["A1:"+ endColLetter].SaveToTextAsync(csvFile, format);
}
}
Input Excel Formula
using (var package = new ExcelPackage())
{
var worksheet = package.Workbook.Worksheets.Add("MarkSheet");
//add headers
worksheet.Cells[1, 1].Value = "Subject";
worksheet.Cells[1, 2].Value = "Passing Mark";
worksheet.Cells[1, 3].Value = "Mark";
worksheet.Cells[1, 4].Value = "Pass?";
//Add some items...
worksheet.Cells["A2"].Value = "English";
worksheet.Cells["B2"].Value = 40;
worksheet.Cells["C2"].Value = 50;
worksheet.Cells["D2"].Formula = "=IF(C2>=B2,\"Pass\",\"Fail\")"; //enter excel forumla
worksheet.Cells["A3"].Value = "Math";
worksheet.Cells["B3"].Value = 40;
worksheet.Cells["C3"].Value = 30;
worksheet.Cells["D3"].Formula = "=IF(C2>=B2,\"Pass\",\"Fail\")"; //enter excel forumla
worksheet.Calculate(); //perform the calculation
var xlFile = new FileInfo(@"C:\Temp\formulaExcel.xlsx");
package.SaveAs(xlFile);//save the Excel file
}
Add Chart
using (var package = new ExcelPackage())
{
var worksheet = package.Workbook.Worksheets.Add("MarkSheet");
worksheet.Cells[1, 1].Value = "Subject";
worksheet.Cells[1, 2].Value = "Mark";
//Add some items...
worksheet.Cells["A2"].Value = "English";
worksheet.Cells["B2"].Value = 40;
worksheet.Cells["A3"].Value = "Math";
worksheet.Cells["B3"].Value = 70;
var chart = worksheet.Drawings.AddChart("barchart", eChartType.ColumnClustered);
chart.SetSize(300, 300);
chart.SetPosition(100, 100);
var series = chart.Series.Add(worksheet.Cells["B2:B3"], worksheet.Cells["A2:A3"]);
series.Fill.Color = Color.Blue;
var xlFile = new FileInfo(@"C:\Temp\chart.xlsx");
package.SaveAs(xlFile);//save the Excel file
}
Protect Excel/Worksheet
using (var package = new ExcelPackage())
{
var worksheet = package.Workbook.Worksheets.Add("ExchangeRate");
//add headers
worksheet.Cells[1, 1].Value = "Amount";
worksheet.Cells[1, 2].Value = "Currency";
worksheet.Cells[1, 3].Value = "Amount";
worksheet.Cells[1, 4].Value = "Currency";
//Add some items...
worksheet.Cells["A2"].Value = 1;
worksheet.Cells["B2"].Value = "USD";
worksheet.Cells["C2"].Value = 7.8;
worksheet.Cells["D2"].Value = "HKD";
worksheet.Protection.SetPassword("test123"); //protect worksheet
var xlFile = new FileInfo(@"C:\Temp\password.xlsx");
package.SaveAs(xlFile,"abc123");//save the Excel file. Protect the whole excel file
}
