How to import excel file from angular to asp.core, than read data and push it in List

Question

I have found package like NPOI, but i dont know how to stream file, or how to convert it to bit before sending to asp.core. Curently i am using Angular 6

Answer 1

Here's some old code that might help. Note that the first method was added because I found it was quicker to send the data from Angular to the controller, process, and then send back as opposed to processing the data in JavaScript.

    /// <summary>
    /// Converts an uploaded Excel file (multipart) to a JSON object. Useful for converting large Excel files instead
    /// of doing so in the browser, due to performance issues with SheetJS JavaScript library.
    /// </summary>
    [HttpPost]
    [Route("ConvertedFile")]
    [SwaggerResponse(HttpStatusCode.OK, "Success", typeof(List<Dictionary<string, object>>))]
    public async Task<IHttpActionResult> GetConvertedFile()
    {            
        var fileData = await processUploadFile();

        fileData = removeEmptyRows(fileData);

        return Ok(fileData);
    }

    /// <summary>
    /// Converts an uploaded Excel file (multipart) to a JSON object. Sends the file to be processed.
    /// </summary>
    [HttpPost]
    [Route("")]
    [Authorize]
    [SwaggerResponse(HttpStatusCode.OK, "Success")]
    public Task<IHttpActionResult> UploadExcelFile()
    {            
        var uploadFileData = await processUploadFile();

        // Do something with uploadFileData...

        return Ok();
    }

    /// <summary>
    /// Processes the upload file by converting uploaded Excel file (XLSX) to "JSON" object.
    /// </summary>
    /// <returns></returns>
    /// <exception cref="HttpResponseException"></exception>
    private async Task<List<Dictionary<string, object>>> processUploadFile()
    {
        var documentData = new List<Dictionary<string, object>>();

        if (!Request.Content.IsMimeMultipartContent())
        {
            throw new HttpResponseException(Request.CreateResponse(HttpStatusCode.UnsupportedMediaType));
        }

        try
        {
            Stopwatch sw = new Stopwatch();
            sw.Start();

            // Manage the multipart file in memory, not on the file system
            MultipartMemoryStreamProvider streamProvider = new MultipartMemoryStreamProvider();

            await Request.Content.ReadAsMultipartAsync(streamProvider);

            // Should have only one "content" or file.
            HttpContent content = streamProvider.Contents.FirstOrDefault();

            // Convert the HttpContent to a Stream
            using (Stream fileUploadStream = await content.ReadAsStreamAsync())
            {
                // Convert the Excel file stream to a List of "rows"
                documentData = ExcelManager.ExcelDataReader(fileUploadStream);

                if (documentData != null)
                {
                    // header rows are not represented in the Values.
                    Log.Information("Order Data - Processed {0} lines of file data", documentData.Count);
                }
                else
                {
                    Log.Warning("File processing error. No data.");
                }
            }

            sw.Stop();

            Log.Debug("Excel File took {0} ms", sw.ElapsedMilliseconds);
        }
        catch (Exception ex)
        {
            Log.Error(ex, string.Format("Test File Processing error."));
            throw;
        }

        return documentData;
    }

    /// <summary>
    /// Removes the empty rows.
    /// </summary>
    /// <param name="documentData">
    /// Represent a list of excel rows. Each list item represent a row. Each Dictionary key represent the column
    /// name. Dictionary value represent cell value.
    /// </param>
    /// <returns></returns>
    private List<Dictionary<string, object>> removeEmptyRows(List<Dictionary<string, object>> documentData)
    {
        // build a new collection that only contains rows that have at least one column with a value
        List<Dictionary<string, object>> nonEmptyRows = new List<Dictionary<string, object>>();

        if (documentData != null && documentData.Count > 0)
        {
            // evaluate each item in the list
            foreach (var item in documentData)
            {
                bool isRowEmpty = false;

                // evaluate each Key/Value pair.
                // RowID, State, and Message are the fields added to the document for state tracking.
                // Do not consider those fields.
                foreach (var key in item.Keys)
                {
                    if (key != "RowID" && key != "State" && key != "Message")
                    {
                        var value = item[key];

                        Type type = value.GetType();

                        isRowEmpty = type.Name == "DBNull";
                    }

                    // if an non-null value is found within the current row then stop processing this item and
                    // continue on to the next item (excel row).
                    if (!isRowEmpty)
                    {
                        break;
                    }
                }

                // if the row has user supplied data then include it 
                if (!isRowEmpty)
                {
                    nonEmptyRows.Add(item);
                }
            }

            Log.Information("Removed {0} empty rows from excel File", documentData.Count - nonEmptyRows.Count);
        }

        return nonEmptyRows;
    }


using Excel;
using Serilog;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
public static class ExcelManager
{
    /// <summary>
    /// Read the steam file to convert it into a list of dictionaries
    /// </summary>
    /// <param name="fileUploadStream">The file stream</param>
    /// <param name="columnNames">The column names to be read in the excel file</param>
    /// <returns></returns>
    public static List<Dictionary<string, object>> ExcelDataReader(Stream fileUploadStream)
    {
        List<Dictionary<string, object>> docData = new List<Dictionary<string, object>>();

        IExcelDataReader excelReader = null;

        try
        {
            excelReader = ExcelReaderFactory.CreateOpenXmlReader(fileUploadStream);

            // Columns names are not handled
            excelReader.IsFirstRowAsColumnNames = true;

            bool isEmpty = isFileEmpty(excelReader);

            if (!isEmpty)
            {
                // note: files that have only the header row will throw an exception when AsDataSet is called.
                DataSet ds = excelReader.AsDataSet();

                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    Dictionary<string, object> data = new Dictionary<string, object>();

                    foreach (DataColumn col in ds.Tables[0].Columns)
                    {
                        data[col.ColumnName] = row[col];
                    }
                    //This filter duplicates inserts with the same OrderLinkId
                    if ((docData.Exists(e => e.Values.ElementAt(0).Equals(data.Values.ElementAt(0)) && e.Values.ElementAt(1).Equals(data.Values.ElementAt(1))) == false))
                    {
                        docData.Add(data);
                    }
                }
            }
        }
        catch (Exception ex)
        {
            Log.Error(ex, "");
            throw;
        }
        finally
        {
            excelReader.Close();
        }

        return docData;
    }

    /// <summary>
    /// Verifies the file has at least one row of data. No header fields are used in the file.
    /// </summary>
    /// <param name="excelReader">The excel reader.</param>
    /// <returns></returns>
    private static bool isFileEmpty(IExcelDataReader excelReader)
    {
        try
        {
            while (excelReader.Read())
            {
                return false;
            }
        }
        catch (Exception)
        {
            // swallow this exception.
        }

        return true;
    }
}

Answer 2

You need to use form data

HTML

<input  accept=".xlsx,.csv" (change)="uploadFile($event)" type="file" #file />

TS

uploadFile(event) {
    const file = event.target.files[0];
    if (this.validateFile(file)) {
      this.isUploadInprogress = true;
      const formData: FormData = new FormData();
      formData.append('files', file, file.name);

      this.uploadService
        .uploadFile(formData)
        .subscribe(
          uploadResponse => this.uploadSuccess(uploadResponse, file),
          error => (this.isUploadInprogress = false)
        );
    } else {
      this.invitationsService.showError('Please upload valid file');
    }
  }

Answer 3

html: (Angular ->primeNg)

Ts:

onFileUpload(event) {
const file = event.files[0];
this.progressBar = true;
const formData: FormData = new FormData();
formData.append('file', file, file.name);
this.Service.importexcel(formData).subscribe(x => console.log(x));

}

asp api core controler: You can separete it in arhitecture after with await... And than send it to SQL from ienumerable after checking regex.

    [HttpPost("import", Name = "ImportExcel")]
    public async Task<IActionResult> ImportExcel([FromForm]IFormFile file)
    {
        string folderName = "Upload";
        string newPath = Path.Combine(Guid.NewGuid().ToString() +'_'+ folderName);

        if (!Directory.Exists(newPath))
        {
            Directory.CreateDirectory(newPath);
        }            
            string sFileExtension = Path.GetExtension(file.FileName).ToLower();
            ISheet sheet;
            string fullPath = Path.Combine(newPath, file.FileName);
            var list = new List<ShipmentImportExcel>();

        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;


            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) //Read Excel File
            {
                var obj = new ExcelObject..();
                IRow row = sheet.GetRow(i);

                obj.property1 = row.GetCell(0).ToString();
                obj.property2 = row.GetCell(1).ToString();                   

                list.Add(obj);
            }
        }
        return Ok(list);

    }