Import and Export Excel file in ASP .NET Core 3.1 Razor age
Home .NET Core In this article we will discuss how to import and export excel file using ASP .NET Core 3.1 razor pages. We will discuss the below points >> H...
Join the DZone community and get the full member experience.
Join For FreeIn this article we will discuss how to import and export excel file using ASP .NET Core 3.1 razor pages. We will discuss the below points:
>> How to import Excel file in .NET Core and preview the uploaded excel file? >> How to export the excel file ?
NPOI Package
NPOI is a free tool, which supports xls, xlsx, and docx extensions. This project is the .NET version of POI Java project at http://poi.apache.org/. POI is an open source project, which can help you read/write XLS, DOC, PPT files. It covers most features of Excel, like styling, formatting, data formulas, extract images, etc. The good thing is, it doesn't require Microsoft Office to be installed on the server. NPOI is an open source component, and you can use it everywhere.
This post shows you the basic functionalities of NPOI, but you can do much more with NPOI like styling the individual cell or rows, creating excel formulas, and other stuff. The NPOI package supports both "xls" and "xlsx" extensions, using HSSFWorkbook and XSSFWorkbook classes. The HSSFWorkbook class is for "xls", where the other one is for "xlsx". To know more about NPOI, Refer the official documentation.
Import and Export Excel in ASP.NET Core 3.1 Razor Pages
Let's create a .NET Core web application with .NET Core 3.1 . Open VS 2019 ➠ Select ASP .NET Core web application
In this example, we create an input file for upload the Excel file, and after uploading it, append the Excel data into DIV. Then, we download that Excel data and also create an Excel file using dummy data. The design is shown below:
<form asp-controller="Home" asp-action="Export">
<div class="container">
<div class="row">
<div class="col-md-4">
<input type="file" id="fileupload" name="files" class="form-control" />
</div>
<div class="col-md-3">
<input type="button" name="Upload" value="Upload" id="btnupload" class="btn btn-primary" />
<a href="@Url.Action("Download", "Home")">Download</a>
</div>
<div class="col-md-5">
<input type="submit" name="Export" value="Create and Export" id="btnExport"
class="btn btn-primary" asp-action="Export" />
</div>
</div>
<div class="clearfix"> </div>
<div class="row">
<div id="divPrint"></div>
</div>
</div>
</form>
Code Snippet Explanation
The HTML code has the file upload control and a button to upload the file. The below JQuery code uploads the Excel file using Ajax. The code also performs client-side validation for file selection and extension checking.
Once the request is successful, it appends the server response to the HTML. Read this post for handling Ajax request with ASP.NET Core 3.1 razor pages and this post for uploading files in ASP.NET Core 3.1 Razor Pages.
xxxxxxxxxx
<script type="text/javascript" src="https://code.jquery.com/jquery-3.4.1.min.js"></script>
<script type="text/javascript">
$(function () {
$('#btnupload').on('click', function () {
var fileExtension = ['xls', 'xlsx'];
var filename = $('#fileupload').val();
if (filename.length == 0) {
alert("Please select a file.");
return false;
}
else {
var extension = filename.replace(/^.*\./, '');
if ($.inArray(extension, fileExtension) == -1) {
alert("Please select only excel files.");
return false;
}
}
var fdata = new FormData();
var fileUpload = $("#fileupload").get(0);
var files = fileUpload.files;
fdata.append(files[0].name, files[0]);
$.ajax({
type: "POST",
url: "/Home/Import",
beforeSend: function (xhr) {
xhr.setRequestHeader("XSRF-TOKEN",
$('input:hidden[name="__RequestVerificationToken"]').val());
},
data: fdata,
contentType: false,
processData: false,
success: function (response) {
if (response.length == 0)
alert('Some error occured while uploading');
else {
$('#divPrint').html(response);
}
},
error: function (e) {
$('#divPrint').html(e.responseText);
}
});
})
$('#btnExport').on('click', function () {
var fileExtension = ['xls', 'xlsx'];
var filename = $('#fileupload').val();
if (filename.length == 0) {
alert("Please select a file then Import");
return false;
}
});
});
</script>
To import the file, create a post method in the Homecontroller.cs file and save the uploaded file into wwwroot folder then append it into the div.
xxxxxxxxxx
public ActionResult Import()
{
IFormFile file = Request.Form.Files[0];
string folderName = "UploadExcel";
string webRootPath = _hostingEnvironment.WebRootPath;
string newPath = Path.Combine(webRootPath, folderName);
StringBuilder sb = new StringBuilder();
if (!Directory.Exists(newPath))
{
Directory.CreateDirectory(newPath);
}
if (file.Length > 0)
{
string sFileExtension = Path.GetExtension(file.FileName).ToLower();
ISheet sheet;
string fullPath = Path.Combine(newPath, file.FileName);
using (var stream = new FileStream(fullPath, FileMode.Create))
{
file.CopyTo(stream);
stream.Position = 0;
if (sFileExtension == ".xls")
{
HSSFWorkbook hssfwb = new HSSFWorkbook(stream); //This will read the Excel 97-2000 formats
sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook
}
else
{
XSSFWorkbook hssfwb = new XSSFWorkbook(stream); //This will read 2007 Excel format
sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook
}
IRow headerRow = sheet.GetRow(0); //Get Header Row
int cellCount = headerRow.LastCellNum;
sb.Append("<table class='table table-bordered'><tr>");
for (int j = 0; j < cellCount; j++)
{
NPOI.SS.UserModel.ICell cell = headerRow.GetCell(j);
if (cell == null || string.IsNullOrWhiteSpace(cell.ToString())) continue;
sb.Append("<th>" + cell.ToString() + "</th>");
}
sb.Append("</tr>");
sb.AppendLine("<tr>");
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) //Read Excel File
{
IRow row = sheet.GetRow(i);
if (row == null) continue;
if (row.Cells.All(d => d.CellType == CellType.Blank)) continue;
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
sb.Append("<td>" + row.GetCell(j).ToString() + "</td>");
}
sb.AppendLine("</tr>");
}
sb.Append("</table>");
}
}
return this.Content(sb.ToString());
}
To download the file, follow the code below:
xxxxxxxxxx
public ActionResult Download()
{
string Files = "wwwroot/UploadExcel/CoreProgramm_ExcelImport.xlsx";
byte[] fileBytes = System.IO.File.ReadAllBytes(Files);
System.IO.File.WriteAllBytes(Files, fileBytes);
MemoryStream ms = new MemoryStream(fileBytes);
return File(fileBytes, System.Net.Mime.MediaTypeNames.Application.Octet, "employee.xlsx");
}
To create and export the file, let's take dummy data for an employee and export it.
xxxxxxxxxx
public async Task<IActionResult> Export()
{
string sWebRootFolder = _hostingEnvironment.WebRootPath;
string sFileName = @"Employees.xlsx";
string URL = string.Format("{0}://{1}/{2}", Request.Scheme, Request.Host, sFileName);
FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
var memory = new MemoryStream();
using (var fs = new FileStream(Path.Combine(sWebRootFolder, sFileName), FileMode.Create, FileAccess.Write))
{
IWorkbook workbook;
workbook = new XSSFWorkbook();
ISheet excelSheet = workbook.CreateSheet("employee");
IRow row = excelSheet.CreateRow(0);
row.CreateCell(0).SetCellValue("EmployeeId");
row.CreateCell(1).SetCellValue("EmployeeName");
row.CreateCell(2).SetCellValue("Age");
row.CreateCell(3).SetCellValue("Sex");
row.CreateCell(4).SetCellValue("Designation");
row = excelSheet.CreateRow(1);
row.CreateCell(0).SetCellValue(1);
row.CreateCell(1).SetCellValue("Jack Supreu");
row.CreateCell(2).SetCellValue(45);
row.CreateCell(3).SetCellValue("Male");
row.CreateCell(4).SetCellValue("Solution Architect");
row = excelSheet.CreateRow(2);
row.CreateCell(0).SetCellValue(2);
row.CreateCell(1).SetCellValue("Steve khan");
row.CreateCell(2).SetCellValue(33);
row.CreateCell(3).SetCellValue("Male");
row.CreateCell(4).SetCellValue("Software Engineer");
row = excelSheet.CreateRow(3);
row.CreateCell(0).SetCellValue(3);
row.CreateCell(1).SetCellValue("Romi gill");
row.CreateCell(2).SetCellValue(25);
row.CreateCell(3).SetCellValue("FeMale");
row.CreateCell(4).SetCellValue("Junior Consultant");
row = excelSheet.CreateRow(4);
row.CreateCell(0).SetCellValue(4);
row.CreateCell(1).SetCellValue("Hider Ali");
row.CreateCell(2).SetCellValue(34);
row.CreateCell(3).SetCellValue("Male");
row.CreateCell(4).SetCellValue("Accountant");
row = excelSheet.CreateRow(5);
row.CreateCell(0).SetCellValue(5);
row.CreateCell(1).SetCellValue("Mathew");
row.CreateCell(2).SetCellValue(48);
row.CreateCell(3).SetCellValue("Male");
row.CreateCell(4).SetCellValue("Human Resource");
workbook.Write(fs);
}
using (var stream = new FileStream(Path.Combine(sWebRootFolder, sFileName), FileMode.Open))
{
await stream.CopyToAsync(memory);
}
memory.Position = 0;
return File(memory, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", sFileName);
}
Let's run the application and see the output.
Find the Source Code in Github.com/CoreProgramm/.
Published at DZone with permission of Jayant Tripathy. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments