Deep dive into OpenXML Part 2

So In the Deep dive in to OpenXML part 1 I showed you the common methods which we can use to generate an excel file. but most of you are not aware to use those methods so today i'm going to show you the way to use those method and generate an excel file.  for this exercise use a Console application project.


Excel File Generator Class

   /// <summary>  
   /// File Generator Class  
   /// </summary>  
   class FileGenerator  
   {  
   
     #region Variable Declaration  
   
     /// <summary>  
     /// Column Span for Grouping   
     /// </summary>  
     private const string ColSpanLine = "1:4";  
   
     private const string SheetName = "Sample";  
     #endregion  
   
     #region Public Methods  
   
     /// <summary>  
     /// Generates the report.  
     /// </summary>  
     /// <param name="reportList">The report list.</param>  
     /// <param name="fileLocation">The file location.</param>  
     public static void GenerateReport(List<LevelOne> reportList, string fileLocation)  
     {  
       //To create package  
       using (SpreadsheetDocument spreadsheetDocument = ExcelUtilities.CreateWorkbook(fileLocation))  
       {  
         // Add a worksheet  
         Worksheet worksheet = ExcelUtilities.AddWorksheet(spreadsheetDocument, SheetName);  
   
   
         // set column and row values to add the data  
         uint column = 1;  
         uint row = 1;    
   
         //check the current level of the report  
         uint currentLevel = 0;  
   
         foreach (LevelOne levelOne in reportList)  
         {  
           ExcelUtilities.SetCellValue(spreadsheetDocument, worksheet, column++, row, levelOne.LevelOneName, false, false);  
           ExcelUtilities.SetCellValue(spreadsheetDocument, worksheet, column++, row, levelOne.LevelOneScore, false, false);  
             
           ExcelUtilities.MakeOutlinedRow(worksheet, row, ColSpanLine, false, currentLevel, true);  
   
           var levelTwostartColumn = column;  
           row++;  
   
           foreach (var levelTwo in levelOne.LevelOneList )  
           {  
             ExcelUtilities.SetCellValue(spreadsheetDocument, worksheet, levelTwostartColumn, row, levelTwo.LevelTwoName, false, false);  
             ExcelUtilities.SetCellValue(spreadsheetDocument, worksheet, levelTwostartColumn+1, row, levelTwo.LevelTwoScore, false, false);  
   
             ExcelUtilities.MakeOutlinedRow(worksheet, row, ColSpanLine, false, currentLevel + 1, false);  
   
             uint leveThreeStartColoumn = levelTwostartColumn+2;  
             column++;  
             row++;  
   
             foreach (var levelThree in levelTwo.LevelTwoList)  
             {  
               ExcelUtilities.SetCellValue(spreadsheetDocument, worksheet, leveThreeStartColoumn, row, levelThree.LevelThreeName, false, false);  
               ExcelUtilities.SetCellValue(spreadsheetDocument, worksheet, leveThreeStartColoumn+1, row, levelThree.LevelThreeScore, false, false);  
   
               ExcelUtilities.MakeOutlinedRow(worksheet, row, ColSpanLine, false, currentLevel + 2, false);  
               column++;  
               row++;  
             }  
           }  
   
           column = 1;  
           currentLevel = 0;  
         }  
   
         // save the worksheet  
         worksheet.Save();  
   
         // close the SpreadSheet Document  
         spreadsheetDocument.Close();  
   
       }  
     }  
     #endregion  
   
       
   }  

Data holding Classes

  public class Report  
   {  
     /// <summary>  
     /// Gets or sets the   
     /// </summary>  
     public List<LevelOne> ReportList { get; set; }  
   }  
   
   public class LevelOne  
   {  
     public string LevelOneName { get; set; }  
     public string LevelOneScore { get; set; }  
     public List<LevelTwo> LevelOneList { get; set; }  
   }  
   
   public class LevelTwo  
   {  
     public string LevelTwoName { get; set; }  
     public string LevelTwoScore { get; set; }  
     public List<LevelThree> LevelTwoList{ get; set; }  
   }  
   
   public class LevelThree  
   {  
     public string LevelThreeName { get; set; }  
     public string LevelThreeScore { get; set; }  
   }  

Program.cs Classes

here before give the file location you have to create a Temp folder and a excel file called temp in that folder. otherwise this will not work and also this program will not work when you try to run the project while you are open the temp.xlsx so be sure that file is closed when you run the project.  I used this method to get sample data so in your case you can pass dataset or a data from Database of whatever data source you like.  

  class Program  
   {  
     private const string FilePath = @"C:\Temp\Temp.xlsx";  
   
     /// <summary>  
     /// Main Method  
     /// </summary>  
     /// <param name="args">The arguments.</param>  
     static void Main(string[] args)  
     {  
       var reportList = GetData();  
       FileGenerator.GenerateReport(reportList, FilePath);  
     }  
   
     /// <summary>  
     /// Gets the data.  
     /// </summary>  
     /// <returns></returns>  
     private static List<LevelOne> GetData()  
     {  
       var level31 = new LevelThree { LevelThreeName = "Level3Name1",LevelThreeScore = "1"};  
       var level32 = new LevelThree { LevelThreeName = "Level3Name2", LevelThreeScore = "5" };  
       var level33 = new LevelThree { LevelThreeName = "Level3Name3", LevelThreeScore = "56" };  
       var level34 = new LevelThree { LevelThreeName = "Level3Name4", LevelThreeScore = "44" };  
   
       var levelThreelist = new List<LevelThree> { level31,level32 };  
       var levelThreelist2 = new List<LevelThree> { level33, level34 };  
   
       var levelTwo1 = new LevelTwo { LevelTwoList = levelThreelist,LevelTwoName = "Level2Name1",LevelTwoScore = "65"};  
       var levelTwo2 = new LevelTwo { LevelTwoList = levelThreelist2, LevelTwoName = "Level2Name2", LevelTwoScore = "85" };  
   
       var levelTwoList = new List<LevelTwo>{levelTwo1,levelTwo2};  
   
       var levelOne = new LevelOne { LevelOneList = levelTwoList,LevelOneName = "levelOneName1",LevelOneScore = "100"};  
   
       var levelOneList = new List<LevelOne>{levelOne};  
   
       return levelOneList;  
     }  

Hmm that's all.  Run the project and go and check the file the you'll get output like this. comment below.

 if you have any problem and please read the part 1 of this article before you try this. okay then have a nice day