How To Save Records After Importing Excel Sheet Into Asp.net Core Razor Pages

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

Solution 1:

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: