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

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..
My Blogs - https://code-sample.com and https://code-sample.xyz
My Book1 - BEST SELLING ANGULAR BOOK (INCLUDING ALL VERSIONS 2, 4, 5, 6, 7)
My Book2 - ANGULAR 2 INTERVIEW QUESTIONS BOOK - Both Books are Available on WorldWide.

www.code-sample.com/. Powered by Blogger.