Import data from an excel spreadsheet into the Epplus-database

Question

Gentlemen, I have the following problem: I created the code below, I made some changes, however I cannot import my file. My application is in .NET, and the database is the Oracle Sql, and adapted the library EPPLUS.

If anyone has a north of what I can do to improve my code, thank you, and if anyone has any suggestions of what I might be doing, thank you also.

I tried to use the Dapper, but I could not.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Text;
using System.IO;

namespace Teste1.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            ViewBag.Title = "Home Page";
            return View();
        }

        [HttpGet]
        [Route("Import")]
        public void ImportExcel()
        {
            //Esta dando erro na minha linha aqui tbm->  using (ExcelPackage xlPackage = new E(new FileInfo(@"C:\YourDirectory\sample.xlsx")))
            var myWorksheet = xlPackage.Workbook.Worksheets.First(); //selecionando o arquivo
            var totalRows = myWorksheet.Dimension.End.Row;
            var totalColumns = myWorksheet.Dimension.End.Column;

            var sb = new StringBuilder(); //Estes são seus dados
            for (int rowNum = 1; rowNum <= totalRows; rowNum++) //selecionando a linha
            {
                var row = myWorksheet.Cells[rowNum, 1, rowNum, totalColumns].Select(c => c.Value == null ? string.Empty : c.Value.ToString());
                sb.AppendLine(string.Join(",", row));
            }
        }
    }
}

Answer 1

Using the Exceldatareader library in version 2.1.2.3, you can transform the byte array of your file into Dataset and consequently make a foreach to insert your data. Controller:

 [HttpPost]
    public JsonResult Insert(FormCollection form)
    {
        try
        {
            BinaryReader reader = new BinaryReader(Request.Files["UploadFile"].InputStream);

            byte[] fileBytes = reader.ReadBytes(Request.Files["UploadFile"].ContentLength);

            bool idHvi = vContratoHviBusiness.Inserir(Convert.ToDateTime(form.GetValues("ContratoHviEntity.dtCriacao")[0]), Convert.ToInt32(form.GetValues("ContratoHviEntity.idContratoItem")[0]), form.GetValues("idContrato")[0], fileBytes, Path.GetFileName(Request.Files["UploadFile"].FileName));

            return Json(new { success = true, idHvi });
        }
        catch (Exception ex)
        {
            return Json(new { success = false, responseText = ex.Message });
        }
    }

Business:

  using (MemoryStream stream = new MemoryStream(fileBytes))
            {
                IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                ds = excelReader.AsDataSet();
                stream.Close();
            }

            if (ds.Tables[0].Rows[1][0].ToString() != idContrato)
            {
                throw new Exception("Este HVI não pertence a esse contrato!");
            }
            else
            {
                for (int i = 1; i < ds.Tables[0].Rows.Count; i++)
                {
                    entidadeHvi.dtCriacao = dtCriacao;
                    entidadeHvi.idContratoItem = itemContrato;
                    entidadeHvi.nmPlanilha = fileName;
                    entidadeHvi.nrFardo = ds.Tables[0].Rows[i][1].ToString();
                    entidadeHvi.vlMIC = Convert.ToDecimal(ds.Tables[0].Rows[i][2]);
                    entidadeHvi.vlUHML = Convert.ToDecimal(ds.Tables[0].Rows[i][3]);
                    entidadeHvi.vlSTR = Convert.ToDecimal(ds.Tables[0].Rows[i][4]);
                    entidadeHvi.vlUI = Convert.ToDecimal(ds.Tables[0].Rows[i][5]);
                    entidadeHvi.vlCodUniv = Convert.ToDecimal(ds.Tables[0].Rows[i][6].ToString() == "" ? null : ds.Tables[0].Rows[i][6]);
                    entidadeHvi.vlELG = Convert.ToDecimal(ds.Tables[0].Rows[i][7]);
                    entidadeHvi.vlRd = Convert.ToDecimal(ds.Tables[0].Rows[i][8]);
                    entidadeHvi.vl_b = Convert.ToDecimal(ds.Tables[0].Rows[i][9]);
                    entidadeHvi.vlCGrade = Convert.ToDecimal(ds.Tables[0].Rows[i][10]);
                    entidadeHvi.vlLeaf = Convert.ToDecimal(ds.Tables[0].Rows[i][11]);
                    entidadeHvi.vlTrArea = Convert.ToDecimal(ds.Tables[0].Rows[i][12]);
                    entidadeHvi.vlSFI = Convert.ToDecimal(ds.Tables[0].Rows[i][13]);
                    entidadeHvi.vlTrCnt = Convert.ToDecimal(ds.Tables[0].Rows[i][14]);
                    entidadeHvi.vlMR = Convert.ToDecimal(ds.Tables[0].Rows[i][15]);
                    entidadeHvi.vlSCI = Convert.ToDecimal(ds.Tables[0].Rows[i][16]);
                    entidadeHvi.vlCSP = Convert.ToDecimal(ds.Tables[0].Rows[i][17]);
                    entidadeHvi.nrLote = Convert.ToDecimal(ds.Tables[0].Rows[i][18]);
                    entidadeHvi.nmProdutor = ds.Tables[0].Rows[i][19].ToString();

                    vContratoHviRepository.Insert(entidadeHvi);
                    Save();
                }
            }

Remember that in this case I pass the data form by ajax and the file I by by Request.Files["filename"]. I hope I’ve helped ;-)