NuGet :
EPPlus
Microsoft.EntityFrameworkCore
Microsoft.EntityFrameworkCore.Relational
Microsoft.EntityFrameworkCore.SqlServer
Microsoft.EntityFrameworkCore.Tools
Student :
public class Student
{
[Key]
public int StudentId { get; set; } = 0;
public string Name { get; set; } = "";
public string Roll { get; set; } = "";
public int Age { get; set; } = 0;
}
DatabaseContext :
public class DatabaseContext : DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder options)
{
var connectionString = string.Format(@"Data Source=RATIN-IT;Initial Catalog=SchoolDB;Integrated Security=True");
options.UseSqlServer(connectionString);
}
public DbSet<Student> Students { get; set; }
}
IStudentService :
public interface IStudentService
{
List<Student> GetStudents();
List<Student> SaveStudents(List<Student> students);
}
StudentService :
public class StudentService : IStudentService
{
DatabaseContext _dbContext = null;
public StudentService(DatabaseContext dbContext)
{
_dbContext = dbContext;
}
public List<Student> GetStudents()
{
return _dbContext.Students.ToList();
}
public List<Student> SaveStudents(List<Student> students)
{
_dbContext.BulkInsert(students);
return students;
}
}
StudentsController :
public class StudentsController : Controller
{
IStudentService _studentService = null;
List<Student> _students = new List<Student>();
public StudentsController(IStudentService studentService)
{
_studentService = studentService;
}
public IActionResult Index()
{
return View();
}
public JsonResult SaveStudents(List<Student> students)
{
_students = _studentService.SaveStudents(students);
return Json(_students);
}
public string GenerateAndDownloadExcel(int studentId, string name)
{
List<Student> students = _studentService.GetStudents();
var dataTable = CommonMethods.ConvertListToDataTable(students);
dataTable.Columns.Remove("StudentId");
byte[] fileContents = null;
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (ExcelPackage pck = new ExcelPackage())
{
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Students");
ws.Cells["A1"].Value = "School Name";
ws.Cells["A1"].Style.Font.Bold = true;
ws.Cells["A1"].Style.Font.Size = 16;
ws.Cells["A1"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["A1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells["A2"].Value = "Student List";
ws.Cells["A2"].Style.Font.Bold = true;
ws.Cells["A2"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["A2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells["A3"].LoadFromDataTable(dataTable, true);
ws.Cells["A3:C3"].Style.Font.Bold = true;
ws.Cells["A3:C3"].Style.Font.Size = 12;
ws.Cells["A3:C3"].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells["A3:C3"].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.SkyBlue);
ws.Cells["A3:C3"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["A3:C3"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
pck.Save();
fileContents = pck.GetAsByteArray();
}
return Convert.ToBase64String(fileContents);
}
}
Index.cshtml :
@{
ViewData["Title"] = "Student Page";
}
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.0/jszip.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.0/xlsx.js"></script>
<script src="~/jquery/jquery.min.js"></script>
<button id="btnDownloadExcel" class="btn btn-success" style="margin-right:5px;">Excel Download</button>
<br />
<div style="margin-bottom:20px;">
<input type="file" id="input" accept=".xls,.xlsx,.ods" style="float:left;" />
<button id="btnSave" class="btn btn-primary" style="float:right;margin-right:10px;">Save</button>
</div>
<table id="tblMain" class="table table-bordered table-striped">
<thead></thead>
<tbody></tbody>
</table>
<script type="text/javascript">
var _oStudents = [],
_oStudent = null,
_headers = [];
$(document).ready(function () {
Init();
});
function Init() {
_oStudent = NewStudentObj();
$("#btnDownloadExcel").click(function () {
GenerateAndDownloadExcel();
});
$("#btnSave").click(function () {
Save();
});
$("#input").on("change", function (e) {
var file = e.target.files[0];
if (!file) return;
var FR = new FileReader();
FR.onload = function (e) {
var data = new Uint8Array(e.target.result);
var workbook = XLSX.read(data, { type: 'array' });
var firstSheet = workbook.Sheets[workbook.SheetNames[0]];
var result = XLSX.utils.sheet_to_json(firstSheet, { header: 1 });
GenerateTable(result);
};
FR.readAsArrayBuffer(file);
$("#input").val("");
});
}
function GenerateAndDownloadExcel() {
var StudentId = 0,
name = "";
$.ajax({
url: "../Students/GenerateAndDownloadExcel?StudentId=" + StudentId + "&name=" + name,
//data: { 'Id': groupId },
type: 'GET',
success: function (result) {
ExcelFromBase64("Student List Excel.xlsx", result);
},
error: function (result) {
}
});
}
function Reset() {
_oStudents = [];
_oStudent = null;
_headers = [];
$("#tblMain thead tr,#tblMain tbody tr").remove();
}
function GenerateTable(exportStatus) {
Reset();
if (exportStatus.length > 0) {
var sTemp = "";
var headers = exportStatus[2];
sTemp = "<tr>";
sTemp += "<th style='text-align:center;vertical-align:middle;'>Serial</th>";
$.map(headers, function (header) {
_headers.push(header);
sTemp += "<th style='text-align:center;vertical-align:middle;min-width:100px;'>" + header + "</th>";
});
sTemp += "</tr>";
$("#tblMain thead").append(sTemp);
exportStatus = exportStatus.slice(3);
exportStatus = exportStatus != null ? exportStatus.filter(x => x.length > 0) : exportStatus;
var nSL = 0;
for (var i = 0; i < exportStatus.length; i++) {
nSL++;
_oStudent = NewStudentObj();
sTemp = "<tr>";
sTemp += "<td style='text-align:center;vertical-align:middle;'>" + nSL + "</td>";
var valueIndex = 0;
var es = exportStatus[i];
for (var j = 0; j < _headers.length; j++) {
propValue = es[j];
propValue = typeof (propValue) == "undefined" ? "" : propValue;
var propName = _headers[valueIndex];
_oStudent[propName] = propValue;
sTemp += "<td style='text-align:center;vertical-align:middle;' title='" + _headers[valueIndex] + "'>" + propValue + "</td>";
valueIndex++;
}
sTemp += "</tr>";
$("#tblMain tbody").append(sTemp);
_oStudents.push(_oStudent);
}
}
}
function ExcelFromBase64(fileName, bytesBase64) {
var link = document.createElement('a');
link.download = fileName;
link.href = 'data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,' + bytesBase64;
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
}
function Save() {
if (_oStudents.length > 0) {
var ajaxRequest = $.ajax({
url: "../Students/SaveStudents/",
type: "POST",
data: { students: _oStudents },
dataType: "json",
beforeSend: function () {
},
});
ajaxRequest.done(function (data) {
alert("Successfully saved.");
});
ajaxRequest.fail(function (jqXHR, textStatus) { alert("Error Found"); alerts('error title', 'error info', 'error'); });
}
else {
alert("No Data Found.");
}
}
function NewStudentObj() {
var oStudent = {
StudentId: 0,
Name: "",
Roll: "",
Age: 0
};
return oStudent;
}
</script>
0 Comments