My work is to create an excel file, which will contain 3 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 build another excel file, which can contain Addresses beside the Latitude and longitude of that address and download the new excel file to the user's end.
Create a MVC Application and add EPPLUS from NuGet package manager to your solution.
Now, 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>
}
And then, 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();
}
“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;
}
}
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();
}
}
}
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.
