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 DIprivate AppDbContext _dbContext;                 // injected by DIpublic 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();
        returnthis.Content(sb==null?"":sb.ToString());
    }

    public IActionResult OnPostImport(){
        var sheet = this.ParseSheetFromRequest(true);
        var records = this.ParseDocsFromSheet(sheet);
        var sb = this.BuildTableHtml(records);
        returnthis.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;
        IFormFilefile= Request.Form.Files[0];
        if (file.Length ==0 ) {
            return sheet;
        }

        stringsFileExtension= Path.GetExtension(file.FileName).ToLower();
        varstream= file.OpenReadStream();
        if (sFileExtension == ".xls") {
            HSSFWorkbookhssfwb=newHSSFWorkbook(stream); //This will read the Excel 97-2000 formats  
            sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook  
        }
        else {
            XSSFWorkbookhssfwb=newXSSFWorkbook(stream); //This will read 2007 Excel format  
            sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook   
        }

        varrecords=this.ParseDocsFromSheet(sheet);

        // if need to save the fileif (saveFile) {
            stream = file.OpenReadStream();
            stringfolderName="Upload";
            stringwebRootPath= _hostingEnvironment.WebRootPath;
            stringnewPath= Path.Combine(webRootPath, folderName);
            if (!Directory.Exists(newPath)) {
                Directory.CreateDirectory(newPath);
            }
            stringfullPath= Path.Combine(newPath, file.FileName);
            using (var fileStream= newFileStream(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){

        IRowheaderRow= sheet.GetRow(0); //Get Header RowintcellCount= headerRow.LastCellNum;
        // ["Id","LastName","","UserName","","Name"]var headerNames= newList<string>();
        for (intj=0; j < cellCount; j++)
        {
            NPOI.SS.UserModel.ICellcell= 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= newList<ImportDocs>();

        for (inti= (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) //Read Excel File
        {
            IRowrow= sheet.GetRow(i);
            if (row == null) continue;
            if (row.Cells.All(d => d.CellType == CellType.Blank)) continue;
            varrecord=newImportDocs();
            vartype= typeof(ImportDocs);
            for (intj=0 ; j < cellCount; j++)
            {
                if (row.GetCell(j) != null){
                    varfield= row.GetCell(j).ToString();
                    varfieldName= headerNames[j];
                    if(String.IsNullOrWhiteSpace(fieldName)){
                        thrownewException($"There's a value in Cell({i},{j}) but has no header !");
                    }
                    varpi= type.GetProperty(fieldName);
                    // for Id column : a int typeif(pi.PropertyType.IsAssignableFrom(typeof(Int32))){
                        pi.SetValue(record,Convert.ToInt32(field));
                    }
                    // for other colun : stringelse{
                        pi.SetValue(record,field);
                    }
                }
            }
            records.Add(record);
        }
        return records;
    }

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

privateStringBuilderBuildTableHtml<T>(IList<T> records)
        where T: class
    {
        vartype = typeof(T);
        var pis = type.GetProperties();

        var sb = newStringBuilder();
        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: