python ruby-programming-language Smalltalk Vue.js Knockoutjs TypeScript JavaScript, OOPs JavaScript Angular 8,7,6,5,4 and 2 Interview Questions and Answers Angular 8,7,6,5,4 and 2 Interview Questions and Answers Angular 4 Angular 2 AngularJs 1.x NodeJs Perl Programming R Programming RequireJs Rust Programming Backbonejs closure programming language go-programming-language kotlin-programming-language
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

Hi there! Welcome to my blog. My name is Anil Singh. I'm a author of this blog. For more detail about me, kindly refer to this link..
My Blogs - http://code-sample.com and http://code-sample.xyz
My Books - Interview Questions and Answers Books- Get Your Book in 15+ Digital Stores Worldwide..

You Might Also Like
www.code-sample.com/. Powered by Blogger.
ASK Questions