Steps To Generate Excel Using EPPlus

My work is to create an Excel file, which will contain three fields: Address, Latitude and Longitude. The user will upload an Address in an Excel file and after reading the addresses from an Excel file, I have to retrieve Latitude and Longitude, using Bing MAP API and create another Excel file, which will contain Addresses along with the Latitude and Longitude of that address and download the new Excel file to the user's end.

Step 1

Create a MVC Application and add EPPLUS from NuGet package manager to your solution.

Step 2

Add the code to your .cshtml page.

<h2>Upload File</h2>

sing (Html.BeginForm("Upload", "Home", null, FormMethod.Post, new { enctype = "multipart/form-data" }))
  {
      @Html.AntiForgeryToken()
      @Html.ValidationSummary()

      <div class="form-group">
          <input type="file" id="dataFile" name="upload" />
      </div>

      <div class="form-group">
          <input type="submit" value="Upload" class="btn btn-default" />
      </div>


  }

Step 3

Now, add the code, mentioned below to your controller.

[HttpPost]
        public ActionResult Upload(HttpPostedFileBase upload)
        {
            if (ModelState.IsValid)
            {
                if (Path.GetExtension(upload.FileName) == ".xlsx")
                {
                    ExcelPackage package = new ExcelPackage(upload.InputStream);
            //From This part we will read excel file
                    DataTable dt = ExcelPackageExtensions.ToDataTable(package);


                    DataTable dtExcel = new DataTable();
                    dtExcel.Columns.Add("Address", typeof(String));
                    dtExcel.Columns.Add("LAT", typeof(Double));
                    dtExcel.Columns.Add("LONG", typeof(Double));

                    List<Coordinates> lstCor = new List<Coordinates>();
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        //Fill the new data Table to generate new excel file
                    }
            //This method will generate new excel and download the same
                    generateExcel(dtExcel);
                }
            }
            return View();
        }

Step 4

“ExcelPackageExtensions.ToDataTable(package)” for this create a new class with the name ExcelPackageExtensions and create a static method with the name “ToDataTable()”. The code is mentioned below.

public static class ExcelPackageExtensions
    {
        public static DataTable ToDataTable(this ExcelPackage package)
        {
            ExcelWorksheet workSheet = package.Workbook.Worksheets.First();
            DataTable table = new DataTable();
            foreach (var firstRowCell in workSheet.Cells[1, 1, 1, workSheet.Dimension.End.Column])
            {
                table.Columns.Add(firstRowCell.Text);
            }

            for (var rowNumber = 2; rowNumber <= workSheet.Dimension.End.Row; rowNumber++)
            {
                var row = workSheet.Cells[rowNumber, 1, rowNumber, workSheet.Dimension.End.Column];
                var newRow = table.NewRow();
                foreach (var cell in row)
                {
                    newRow[cell.Start.Column - 1] = cell.Text;
                }
                table.Rows.Add(newRow);
            }
            return table;
        }
    }

Step 5

Now, add the code part to generate and download an Excel file.

[NonAction]
        public void generateExcel(DataTable Dtvalue)
        {
            string excelpath = "";
            excelpath = @"C:\Excels\abc.xlsx";//Server.MapPath("~/UploadExcel/" + DateTime.UtcNow.Date.ToString() + ".xlsx");
            FileInfo finame = new FileInfo(excelpath);
            if (System.IO.File.Exists(excelpath))
            {
                System.IO.File.Delete(excelpath);
            }
            if (!System.IO.File.Exists(excelpath))
            {
                ExcelPackage excel = new ExcelPackage(finame);
                var sheetcreate = excel.Workbook.Worksheets.Add(DateTime.UtcNow.Date.ToString());
                if (Dtvalue.Rows.Count > 0)
                {
                    for (int i = 0; i < Dtvalue.Rows.Count; )
                    {
                        sheetcreate.Cells[i + 1, 1].Value = Dtvalue.Rows[i][0].ToString();
                        sheetcreate.Cells[i + 1, 2].Value = Dtvalue.Rows[i][1].ToString();
                        sheetcreate.Cells[i + 1, 3].Value = Dtvalue.Rows[i][2].ToString();
                        i++;
                    }
                }
                //sheetcreate.Cells[1, 1, 1, 25].Style.Font.Bold = true;
                //excel.Save();

                var workSheet = excel.Workbook.Worksheets.Add("Sheet1");
                //workSheet.Cells[1, 1].LoadFromCollection(data, true);
                using (var memoryStream = new MemoryStream())
                {
                    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    Response.AddHeader("content-disposition", "attachment;  filename=Contact.xlsx");
                    excel.SaveAs(memoryStream);
                    memoryStream.WriteTo(Response.OutputStream);
                    Response.Flush();
                    Response.End();
                }
            }

        }

**Upload format of Excel is mentioned below.



**Download format of Excel is given below.