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 "";
}
}
|
Anil Singh is an author, tech blogger, and software programmer. Book writing, tech blogging is something do extra and Anil love doing it. For more detail, kindly refer to this link..
|