public FileContentResult Export()
{
//bikin file baru
ExcelPackage pck = new ExcelPackage();
//sumber data
List <Context.Multidrop> dbMultidrop = RepoMultidrop.FindAll();
IList <String> listTujuan = new List <string>();
String dataTujuan = "";
//bikin worksheet worksheet
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Sheet 1");
//bikin header cell[baris,kolom] , nama kolom sesuaikan dengan template
ws.Cells[1, 1].Value = "Jumlah Kota";
ws.Cells[1, 2].Value = "Tambahan Waktu Tempuh";
ws.Cells[1, 3].Value = "Tambahan Waktu Kerja";
ws.Cells[1, 4].Value = "Tujuan";
ws.Cells[1, 5].Value = "Id Database";
// Inserts Data
int idx = 0;
int nextRow = 0;
for (int i = 0; i < dbMultidrop.Count(); i++)
{
ws.Cells[i + 2 + nextRow, 1].Value = dbMultidrop[i].JumlahKota;
ws.Cells[i + 2 + nextRow, 2].Value = dbMultidrop[i].WaktuTempuh;
ws.Cells[i + 2 + nextRow, 3].Value = dbMultidrop[i].WaktuKerja;
ws.Cells[i + 2 + nextRow, 5].Value = dbMultidrop[i].Id;
dataTujuan = dbMultidrop[i].tujuan;
listTujuan = dataTujuan.Split(new String[] { " - " }, StringSplitOptions.None);
for (idx = 0; idx <= listTujuan.Count - 1; idx++)
{
Context.Location loc = RepoLocation.FindByNama(listTujuan[idx]);
ws.Cells[i + 2 + nextRow, 4].Value = loc.Code;
nextRow++;
}
nextRow--;
}
var fsr = new FileContentResult(pck.GetAsByteArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
fsr.FileDownloadName = "Export Data Multidrop.xls";
return(fsr);
}
public string GetLocationByName(string name)
{
Context.Location dbitems = RepoLocation.FindByNama(name);
return(new JavaScriptSerializer().Serialize(dbitems));
}
public string Upload(IEnumerable <HttpPostedFileBase> files)
{
ResponeModel response = new ResponeModel();
//algoritma
if (files != null)
{
foreach (var file in files)
{
try
{
using (var package = new ExcelPackage(file.InputStream))
{
var currentSheet = package.Workbook.Worksheets;
var workSheet = currentSheet.First();
var noOfRow = workSheet.Dimension.End.Row;
//sheet 1
for (int rowIterator = 2; rowIterator <= noOfRow; rowIterator++)
{
if (workSheet.Cells[rowIterator, 1].Value != null && workSheet.Cells[rowIterator, 2].Value != null && workSheet.Cells[rowIterator, 3].Value != null &&
workSheet.Cells[rowIterator, 4].Value != null && workSheet.Cells[rowIterator, 5].Value != null && workSheet.Cells[rowIterator, 6].Value != null &&
workSheet.Cells[rowIterator, 7].Value != null && workSheet.Cells[rowIterator, 8].Value != null && workSheet.Cells[rowIterator, 9].Value != null &&
workSheet.Cells[rowIterator, 10].Value != null && workSheet.Cells[rowIterator, 11].Value != null && workSheet.Cells[rowIterator, 12].Value != null &&
workSheet.Cells[rowIterator, 13].Value != null && workSheet.Cells[rowIterator, 14].Value != null && workSheet.Cells[rowIterator, 15].Value != null)
{
int id = 0;
int resId;
if (workSheet.Cells[rowIterator, 17].Value != null)
{
if (int.TryParse(workSheet.Cells[rowIterator, 17].Value.ToString(), out resId))
{
id = resId;
}
}
//cara gancang ngarah teu kudu aya pengecekan tiap field
Context.MasterPool dbitem = new Context.MasterPool();
try
{
if (id != 0)
{
dbitem = RepoPool.FindByPK(id);
if (RepoPool.IsExist(workSheet.Cells[rowIterator, 2].Value.ToString(), id))
{
continue;
}
}
if (RepoPool.IsExist(workSheet.Cells[rowIterator, 2].Value.ToString(), 2))
{
continue;
}
dbitem.IsActive = bool.Parse(workSheet.Cells[rowIterator, 1].Value.ToString());
dbitem.NamePool = workSheet.Cells[rowIterator, 2].Value.ToString();
dbitem.Capacity = int.Parse(workSheet.Cells[rowIterator, 3].Value.ToString());
dbitem.Address = workSheet.Cells[rowIterator, 4].Value.ToString();
dbitem.IdProvinsi = RepoLoc.FindByNama(workSheet.Cells[rowIterator, 5].Value.ToString()).Id;
dbitem.IdKabKota = RepoLoc.FindByNama(workSheet.Cells[rowIterator, 6].Value.ToString()).Id;
dbitem.IdKec = RepoLoc.FindByNama(workSheet.Cells[rowIterator, 7].Value.ToString()).Id;
dbitem.IdKel = RepoLoc.FindByNama(workSheet.Cells[rowIterator, 8].Value.ToString()).Id;
dbitem.Longitude = workSheet.Cells[rowIterator, 9].Value.ToString();
dbitem.Latitude = workSheet.Cells[rowIterator, 10].Value.ToString();
dbitem.Radius = int.Parse(workSheet.Cells[rowIterator, 11].Value.ToString());
dbitem.KodeTelp = workSheet.Cells[rowIterator, 12].Value.ToString();
dbitem.Telp = workSheet.Cells[rowIterator, 13].Value.ToString();
dbitem.Pic = workSheet.Cells[rowIterator, 14].Value.ToString();
dbitem.Handphone = workSheet.Cells[rowIterator, 15].Value.ToString();
dbitem.ListZoneParkir.Clear();
int idx = 0;
for (idx = rowIterator; idx <= noOfRow; idx++)
{
if (workSheet.Cells[idx, 1].Value == null || idx == rowIterator)
{
if (workSheet.Cells[idx, 16].Value != null && workSheet.Cells[idx, 17].Value != null)
{
try
{
Context.ZoneParkir dbparkir = new Context.ZoneParkir();
dbparkir.IdZone = RepoLookup.FindByName(workSheet.Cells[idx, 16].Value.ToString()).Id;
dbparkir.Pit = int.Parse(workSheet.Cells[idx, 17].Value.ToString());
dbitem.ListZoneParkir.Add(dbparkir);
}
catch (Exception)
{
}
}
}
else
{
break;
}
}
rowIterator = idx - 1;
RepoPool.save(dbitem, UserPrincipal.id);
}
catch (Exception)
{
}
}
}
}
response.Success = true;
}
catch (Exception e)
{
response.Success = false;
response.Message = e.Message.ToString();
}
}
}
return(new JavaScriptSerializer().Serialize(new { Response = response }));
}