Cfrgtkky

Im trying tofind a way on how I can save the uploaded excell sheet to my database Ms SQL server.

I have the following:

Model

public class ImportDocs

{

    public int Id { get; set; }

    public string Name { get; set; }

    public string LastName { get; set; }

    public string UserName { get; set; }

}

HTML and JavaScript for viewing the records

<form method="post" enctype="multipart/form-data">

<div class="row">

    <div class="col-md-4">

        <input type="file" id="fUpload" name="files" class="form-control" />

    </div>

    <div class="col-md-8">

        <input type="button" id="btnUpload" value="Upload" />

    </div>

</div>

<br />

<div id="dvData"></div>

<br />

<div class="col-md-8">

    <input type="button" id="btnSave" value="Save To Database" />

</div>

JavaScript

@Html.AntiForgeryToken()

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>

<script src="https://ajax.aspnetcdn.com/ajax/jQuery/jquery-3.3.1.min.js"> 

</script>

<script type="text/javascript">



$(document).ready(function () {

    $('#btnUpload').on('click', function () {

        var fileExtension = ['xls', 'xlsx'];

        var filename = $('#fUpload').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 = $("#fUpload").get(0);

        var files = fileUpload.files;

        fdata.append(files[0].name, files[0]);

        $.ajax({

            type: "POST",

            url: "/ImportExcelFiles/Index?handler=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 {

                    $('#dvData').html(response);

                }

            },

            error: function (e) {

                $('#dvData').html(e.responseText);

            }

        });

    })

});

C# Code

  private IHostingEnvironment _hostingEnvironment;

    public IndexModel(IHostingEnvironment hostingEnvironment)

    {

        _hostingEnvironment = hostingEnvironment;

    }

    public ActionResult OnPostImport()

    {

        IFormFile file = Request.Form.Files[0];

        string folderName = "Upload";

        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'><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());

    }

}

How can I archieve this

You're almost there. What you need is to inspect the excel with Reflection and construct a list of ImportDocs, so that we can save the IList<ImportDocs> into database.

The process on server side can be divided into 4 parts :

1. create an instance of ISheet using current request


2. create a list of ImportDocs (we'll name it as 'records') instance using the sheet


3. save the list of records to database


4. build a html <table>...</table> for client displaying

Here's the structure of page model :

    private IHostingEnvironment _hostingEnvironment; // injected by DI

    private AppDbContext _dbContext;                 // injected by DI





    public IActionResult OnPostSave(){

        var sheet = this.ParseSheetFromRequest(false);

        var records = this.ParseDocsFromSheet(sheet);

        var sb = this.BuildTableHtml(records);

        // typically, we'll use Database to generate the Id

        //     as we cannot trust user 

        foreach (var record in records) {

            record.Id = default(int);  

        }

        this._dbContext.ImportDocs.AddRange(records);

        this._dbContext.SaveChanges();

        return this.Content(sb==null?"":sb.ToString());

    }



    public IActionResult OnPostImport(){

        var sheet = this.ParseSheetFromRequest(true);

        var records = this.ParseDocsFromSheet(sheet);

        var sb = this.BuildTableHtml(records);

        return this.Content(sb==null?"":sb.ToString());

    }



    private ISheet ParseSheetFromRequest(bool saveFile) { 

        // ...

    }



    private List<ImportDocs> ParseDocsFromSheet(ISheet sheet){

        // ...

    }

    private StringBuilder BuildTableHtml<T>(IList<T> records){   

        // ...

    }

Here the ParseSheetFromRequest() is a helper method used to create a new ISheet from current request, I simply copy your code:

    private ISheet ParseSheetFromRequest(bool saveFile) {

        ISheet sheet= null;

        IFormFile file = Request.Form.Files[0];

        if (file.Length ==0 ) {

            return sheet;

        }



        string sFileExtension = Path.GetExtension(file.FileName).ToLower();

        var stream = file.OpenReadStream();

        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   

        }



        var records = this.ParseDocsFromSheet(sheet);



        // if need to save the file

        if (saveFile) {

            stream = file.OpenReadStream();

            string folderName = "Upload";

            string webRootPath = _hostingEnvironment.WebRootPath;

            string newPath = Path.Combine(webRootPath, folderName);

            if (!Directory.Exists(newPath)) {

                Directory.CreateDirectory(newPath);

            }

            string fullPath = Path.Combine(newPath, file.FileName);

            using (var fileStream= new FileStream(fullPath, FileMode.Create)) {

                file.CopyTo(fileStream);

            }

        }



        return sheet;

    }

And the ParseDocsFromSheet() is another helper method used to parse ImportDocs from sheet. It uses Reflection to inspect the field name and then construct a new strongly-typed instance at RunTime :

    private List<ImportDocs> ParseDocsFromSheet(ISheet sheet){



        IRow headerRow = sheet.GetRow(0); //Get Header Row

        int cellCount = headerRow.LastCellNum;

        // ["Id","LastName","","UserName","","Name"]

        var headerNames= new List<string>();

        for (int j = 0; j < cellCount; j++)

        {

            NPOI.SS.UserModel.ICell cell = headerRow.GetCell(j);

            if (cell == null || string.IsNullOrWhiteSpace(cell.ToString())) {

                headerNames.Add("");  // add empty string if cell is empty

            }else{

                headerNames.Add( cell.ToString());

            }

        }



        var records= new List<ImportDocs>();



        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;

            var record = new ImportDocs();

            var type = typeof(ImportDocs);

            for (int j = 0 ; j < cellCount; j++)

            {

                if (row.GetCell(j) != null){

                    var field = row.GetCell(j).ToString();

                    var fieldName = headerNames[j];

                    if(String.IsNullOrWhiteSpace(fieldName)){

                        throw new Exception($"There's a value in Cell({i},{j}) but has no header !");

                    }

                    var pi = type.GetProperty(fieldName);

                    // for Id column : a int type

                    if(pi.PropertyType.IsAssignableFrom(typeof(Int32))){

                        pi.SetValue(record,Convert.ToInt32(field));

                    }

                    // for other colun : string

                    else{

                        pi.SetValue(record,field);

                    }

                }

            }

            records.Add(record);

        }

        return records;

    }

Finally, to build the <table>, we can create a reusable method :

    private StringBuilder BuildTableHtml<T>(IList<T> records)

        where T: class

    {

        var type = typeof(T);

        var pis = type.GetProperties();



        var sb = new StringBuilder();

        sb.Append("<table class='table'><tr>");

        foreach(var pi in pis){

            sb.Append("<th>" + pi.Name + "</th>");

        }

        sb.Append("</tr>");

        foreach (var record in records) //Read Excel File

        {

            sb.AppendLine("<tr>");

            foreach(var pi in pis){

                sb.Append("<td>" + pi.GetValue(record) + "</td>");

            }

            sb.AppendLine("<tr>");

        }

        sb.Append("</table>");

        return sb;

    }

Test case: