Monday, July 6, 2020

How to create an Excel WorkBook, Sheet in C#.NET

Recently, I had a requirement to export List to an Excel document separated by multiple sheets. I tried creating a .xlsx file using File.Create(filepath); approach which caused issues in writing into different sheets and workbook. So, I had to use interop library to get it done as follows. step 1: install interop nugget package to your solution
Microsoft.Office.Interop.Excel
step 2: Create a workbook and sheets
  
   var excel = new Microsoft.Office.Interop.Excel.Application(); 
   excel.Visible = false; 
   excel.DisplayAlerts = false; 
   var xlWorkBook = excel.Workbooks.Add(Type.Missing); 
   
   var xlSheets = xlWorkBook.Sheets as Microsoft.Office.Interop.Excel.Sheets;

   var worKsheeT =(Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.ActiveSheet; 
   worKsheeT.Name = "FirstSheet";
   
   var secondSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(
    Type.Missing,Type.Missing, Type.Missing, Type.Missing); 
   secondSheet.Name = "SecondSheet";
              
    xlWorkBook.SaveAs(filePath);
    xlWorkBook.Close(); 
    excel.Quit();

  

No comments:

Post a Comment