This is basically used for export and Import excel file using MVC 5 ASP.net.
public class XLImportExportManager
public class XLImportExportManager
{
public XLImportExportManager() { }
public string BulkUploadSample(int tenentId,int companyId)
{
Excel._Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = Missing.Value;
xlApp = new Excel.Application();
xlWorkBook =
xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
string filename =( (new Random()).Next(1,9) ).ToString() +"_DownloadedFile.xls";
string fileNm = HttpContext.Current.Server.MapPath("/Data/DownloadedFile.xls");
xlWorkSheet.Cells[1, 1] = "ID";
xlWorkSheet.Cells[1, 2] = "CostCenter";
xlWorkSheet.Cells[1, 3] = "MobileNo";
xlWorkSheet.Cells[1, 4] = "EmailID";
xlWorkSheet.Cells[1, 5] = "FirstName";
xlWorkSheet.Cells[1, 6] = "LastName";
xlWorkSheet.Cells[1, 7] = "Services";
int i = 2;
foreach (var s in (new SubscriberRepository()).GetSubscriber(tenentId, companyId))
{
xlWorkSheet.Cells[i, 1] = i -
1;
xlWorkSheet.Cells[i, 2] = (new BusinessEntityRepository()).CocenterNmById(Convert.ToInt32(s.CostCenterID))[0].ToString();
xlWorkSheet.Cells[i, 3] =
s.MobileNo;
xlWorkSheet.Cells[i, 4] =
s.EmailID;
xlWorkSheet.Cells[i, 5] =
s.FirstName;
xlWorkSheet.Cells[i, 6] =
s.LastName;
xlWorkSheet.Cells[i, 7] = "HandSet Data-Stand Alone-4G-500 MB";
string rangeValue = "B" + i.ToString();
xlWorkSheet.Range[rangeValue].Validation.Add(Excel.XlDVType.xlValidateList, Type.Missing, Excel.XlFormatConditionOperator.xlBetween,
GetNameOfCostCenter(tenentId, companyId));
rangeValue = "G"+i.ToString();
xlWorkSheet.Range[rangeValue].Validation.Add(Excel.XlDVType.xlValidateList, Type.Missing, Excel.XlFormatConditionOperator.xlBetween,
GetServices(tenentId, companyId));
i = i + 1;
}
if (File.Exists(fileNm))
{
File.Delete(fileNm);
}
xlWorkBook.SaveAs(fileNm, Excel.XlFileFormat.xlWorkbookNormal,
misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue,
misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
return fileNm;
}
private void releaseObject(object obj)
{
try
{
Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
}
finally
{
GC.Collect();
}
}
public string GetNameOfCostCenter(int tenantId, int companyId)
{
StringBuilder costCenter =new StringBuilder();
foreach (var s in (new BusinessEntityRepository()).GetCostName(tenantId, companyId))
{
costCenter.Append(s + ",");
}
if (costCenter.ToString().Length > 0)
return costCenter.ToString().Substring(0,
costCenter.ToString().Length - 1);
else
return "";
}
public string GetServices(int tenantId, int companyId)
{
StringBuilder costCenter = new StringBuilder();
foreach (var s in (new BusinessEntityRepository()).GetServices(tenantId, companyId))
{
costCenter.Append(s.ProductType
+ " " + s.UsageType + "
" + s.NetValue + " " +
s.UsageIncluded + " " + s.Unit + ",");
}
if (costCenter.ToString().Length > 0)
return costCenter.ToString().Substring(0,
costCenter.ToString().Length - 1);
else
return "";
}
}