In this blog, two recently added features are being discussed :

1. Subtotals
2. Loading particular sheet in Excel

SubTotals

In C1Excel, we can create subtotals for rows and column and also set the outline level of row/column containing the Total. This can be achieved by using Formula, OutlineLevel property. Image below shows the Excel file that has Formula, OutlineLevel applied : For example : The below line will set the outline level of the row so that a Expand ( ) or Collapse (-) sign is displayed before the row

 sheet.Rows[4].OutlineLevel = 2;

Also, code line below can be used to set the formula for calculating subtotal :

sheet[5, 2].Formula = “SUBTOTAL(9,C4:C5)”;

Following code can be used for creation of excel file with subtotals :

    XLStyle totalStyle = new XLStyle(book);  
 totalStyle.Font = new Font("Arial", 12, FontStyle.Bold) ;  

 XLSheet sheet = book.Sheets[0];  
 sheet[2, 1].Value = "Subject";  
 sheet[2, 2].Value = "Marks";  
 sheet[3, 1].Value = "Eng A";  
 sheet[3, 2].Value = 17;  

 //Set the Outline Level of the row  
 sheet.Rows[3].OutlineLevel = 2;  
 sheet.Rows[3].Visible = true;  

 sheet[4, 1].Value = "Eng B";  
 sheet[4, 2].Value = 14;  

 sheet.Rows[4].OutlineLevel = 2;  
 sheet.Rows[4].Visible = true;  

 sheet[5, 1].Value = "Total";  
 sheet[5, 1].Style = totalStyle;  
 sheet[5, 2].Value = 31;  

 //Used to calculate subtotal  
 sheet[5, 2].Formula = "SUBTOTAL(9,C4:C5)";  
 sheet.Rows[5].OutlineLevel = 1;  

 sheet[7, 1].Value = "IT";  
 sheet[7, 2].Value = 25;  
 sheet.Rows[7].OutlineLevel = 2;  

 sheet[8, 1].Value = "Total";  
 sheet[8, 1].Style = totalStyle;  
 sheet[8, 2].Value = 25;  

 sheet[8, 2].Formula = "SUBTOTAL(9,C8:C8)";  
 sheet.Rows[8].OutlineLevel = 1;  

 sheet[10, 1].Value = "Grand Total";  
 sheet[10, 1].Style = totalStyle;  

 sheet[10, 2].Value = 140;  
 sheet[10, 2].Formula = "SUBTOTAL(9,C4:C8)";  

 sheet.Rows[10].OutlineLevel = 0;  

 book.Save("SaveSubTotal.xlsx");   // or “Save.xls”

Loading Particular Sheet in C1Excel

Many times we have an excel file having large number of sheets, but we need to access/work on particular sheet only. Earlier, it was required to load the entire xls/xls files containing all the sheets that caused a lot of memory and time consumption by the Load event, as it loaded all the sheets in Excel file. Now, we have an overload for Load event that loads data only from the particular sheet as a result time and memory consumption is improved since data only from a particular sheet is loaded. For instance, suppose there is an Excel file containing 100 Sheets and user wants only second sheet. We will call Load method that loads only sheet 2, thus saving the time and memory consumption that would have been consumed if we had to load all the sheets.

c1XLBook1.Load(path, false);

This event is used to load the sheet names only(not the data in sheets). Second parameter (false),means that we need to just load the sheet names and not the data in sheets. Next we will load the specific sheet using C1XLBook.Sheets[index].Load(path, bool). Following code specifies the sheet that needs to be loaded, In the above code we are loading only second sheet:

c1XLBook1.Sheets[1].Load(path, true);

Thus, following code will load only first sheet of the excel file :

// path of file for loading (xls or xlsx)  
 string path = "..//..//MultipleSheets.xlsx";  

 // load workbook without worksheet data  
 book.Load(path, false);  
 //load data on sheet 0 only  
 book.Sheets[0].Load(path, true);

Download Sample