import export excel file in mvc 5

import export excel file in mvc 5

This is basically used for export and Import excel file using MVC 5 ASP.net.

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

Hey! I'm Anil Singh. I author this blog. I'm Active Blogger, Programmer. I love learning new technologies, programming, blogging and participating the forum discussions more...
http://www.code-sample.com
http://www.code-sample.xyz

You Might Also Like
Post a Comment
www.code-sample.com/. Powered by Blogger.