SharePoint Basics - Find out Site Template used to create a SharePoint Site

Sometimes, we need to find out Site template used to create SharePoint site. A very easy way to find out the template without writing any code is as below.

Step 1: Open your SharePoint Site/Sub site in browser.
Step 2: Right click in the browser and select "View page source".
Step 3: Find Keyword "g_wsaSiteTemplateId". It will show a code for Site Template.
Step 4: Here, "STS#0" is used which is the template code for "Team Site" template. You can refer this TechNet Article for Template Code Reference.

 

If you have any questions you can reach out our SharePoint Consulting team here.

Export large number of records from Database to Excel using C#

Objective

In this blog, I've explained how we can export large number of records in Excel file within very few seconds. I have implemented this solution in one of the project, where I need to export around 100,000 records in less than a minute.

Approach

• First step is to download the using NuGet Package with following command:

      

 

• It will add DocumentFormat.OpenXml.dll into your project:

    

 

• I had a situation where I was getting data in Generic List, So I converted Generic List to DataTable first. (Note: If you have all the records in DataTable itself, you don't need to execute below function!)

 public static DataSet ToDataSet<T>(this IList<T> list)  
 {  
       Type elementType = typeof(T);  
       DataSet ds = new DataSet();  
       DataTable t = new DataTable();  
       ds.Tables.Add(t);  
       foreach(var propInfo in elementType.GetProperties())  
       {  
            Type ColType = Nullable.GetUnderlyingType(propInfo.PropertyType) ?? propInfo.PropertyType;  
            t.Columns.Add(propInfo.Name, ColType);  
       }  
       foreach (T item in list)  
       {  
            DataRow row = t.NewRow();  
            foreach (var propInfo in elementType.GetProperties())  
            {  
                     row[propInfo.Name] = propInfo.GetValue(item, null) ?? DBNull.Value;  
            }  
            t.Rows.Add(row);  
       }  
       return ds;  
 }  

• Now use below code to export the records in Excel file.

 //Convert Generic List to DataSet  
 DataSet ds = ToDataSet(objList);   
 //Specify the column names which would be the Headers in Excel File when exported.  
 ds.Tables[0].Columns[0].ColumnName = "Account Name";  
 ds.Tables[0].Columns[1].ColumnName = "NAV Date";  
 ds.Tables[0].Columns[2].ColumnName = "NAV ₹";  
 var filename = ExportDataSet(ds);  
 return filename;  
 private static string ExportDataSet(DataSet ds)  
 {  
      string destination = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString() + ".xlsx");  
      using (var workbook = SpreadsheetDocument.Create(destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))  
      {  
           var workbookPart = workbook.AddWorkbookPart();  
           workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();  
           workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();  
           foreach (System.Data.DataTable table in ds.Tables)  
           {  
             var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();  
             var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();  
             sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);  
             DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();  
             string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);  
             uint sheetId = 1;  
             if (sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)  
             {  
                       sheetId = sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s =>s.SheetId.Value).Max() + 1;  
             }  
             DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName };  
             sheets.Append(sheet);  
             DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();  
             List<String> columns = new List<string>();  
             foreach (System.Data.DataColumn column in table.Columns)  
             {  
                  columns.Add(column.ColumnName);  
                  DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();  
                  cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;  
                  cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);  
                  headerRow.AppendChild(cell);  
             }  
             sheetData.AppendChild(headerRow);  
             foreach (System.Data.DataRow dsrow in table.Rows)  
             {  
                  DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();  
                  foreach (String col in columns)  
                  {  
                      DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();  
                      cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;  
                      cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString());  
                      newRow.AppendChild(cell);  
                  }  
                  sheetData.AppendChild(newRow);  
             }  
           }  
      }  
      return destination;  
 }  

If you have any questions you can reach out our SharePoint Consulting team here.

SharePoint List View Web Part - Open documents in new browser tab

Scenario:

I wanted to show documents stored in a Document library, on a page with the help of  List View Web Part. I added List View web part and configured the same to display documents from a document library. Everything looked good apart from one thing. I wanted documents to open up in a new tab. But, instead, documents were opening in same tab.  

If there had been navigation links just thrown up on the page without any web part, I would have just added target="_blank" attribute. But, here, I had used Listview web part. So, I was compelled to come up with a different solution. A solution that should be working without jeopardizing the page view and other functionalities. I kept on turning over different solutions until I ran across a solution that looked easy and could be carried out with minimum efforts. I added a content editor web part and punched in my code into it. Saved the page and clicked on the document. And as expected, It didn't let me down.

Solution: Here, I am sharing the steps that I've performed to rid myself of that issue:

Step 1:

Click on Edit page from Site Settings Gear icon.

 

Step 2:

Now, Click on Add a web part.

Step 3:

Seek "Media and Content" and add "Content Editor" web part.  It should be on the top in the all web parts listed in Media and Content gallery.

Step 4:

Once web part has been added, click on "Click here to add content".

Step 5:

You should see "Format Text" ribbon and in that ribbon, You should see "Edit Source".

Step 6:

Click  on Edit Source and tap this code into that window. Click on OK.

 <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.0/jquery.min.js" type="text/javascript" />  
 <script type="text/javascript">  
 $(document).ready(function(){  
   $('#onetidDoclibViewTbl0 a').removeAttr('onclick').attr('target', '_blank');}  
 );  
 </script>  

 

Step 7:

 Now, go to Edit Web Part menu and change appearance settings to hide title and chrome for this web part.

Step 8:

Click on Stop Editing to save the page.

Step 9:

Check it in and publish it.

Step 10:

Now, Click on a document name and it should be opening in a new browser tab.

If you have any questions you can reach out our SharePoint Consulting team here.

SharePoint - Calendar Web Part with Color Code and Overlays

In this blog, I'll walkthrough in detail how we can configure SharePoint Calendar Web Part (in OOTB manners) that displays different color code for different category of events. In this example, "Category" is a choice column (having three choices - "Tasks", "Training" and "Current Events") defined in the Calendar List.

Once configured, calendar web part will look as shown in below screenshot. This will show different color for different category of events. For example, we took three different categories that are "Tasks", "Training" and "Current Events".

Now Let's check how we can achieve this.

Step 1: Create three different views for all categories.

1. Create a calendar view that will show events filtered by Category = "Current Events".

• Navigate to Calendar List -> Select Create View (from Ribbon).

• Select Calendar View.

• Fill Information and Apply Filter. This view will hold all events filtered by Category = "Current Event".

2. Repeat above steps to create two more calendar views that will show events filtered by Category = "Tasks" and "Training".

3. Create one common calendar view "ALL". Don't Apply filter in this view.

Step 2. Now we will add Calendar Overlays in newly created "ALL" calendar view.

1. Define Calendar Overlay for different category specific views in "ALL" Calendar View we have created.

• To add Calendar Overlay, Select Calendar > Open "ALL" Calendar View > Calendar Overlays (from Ribbon).

• Click New Calendar.

• Fill information and apply color code for your view > Press OK.

2. Repeat these steps for "Training" and "Tasks" to define Calendar Overlay for these categories too.

3. We have "ALL" Calendar View defined with overlay. Now, we can use this view on Calendar View Web Part.

4. To observe the results, let's add Calendar web part on SharePoint Page. And select "ALL" Calendar View from the web part properties. We will see the result as expected with different color codes on Calendar for different category of events.

So, this way we can configure Calendar web part to display different color code for different category of events with Calendar Overlay.

 

If you have any questions you can reach out our SharePoint Consulting team here.