My Sharepoint Life

1. Create Excel Template Library

             2. Create Excel Template Library

b.    Named Library as Excel Report Template Library

c. Click More Options, Set Document Template for Microsoft Excel Spreadsheet. Then Click Create.

             3.   Upload Excel Template to Library    

      For each excel report has a template for display. After made it from Ms Office Excel, template must be in Excel Report Sharepoint Library.

 

b.    Choose Excel Template of Report

                   Click Browse, Then Choose Report Template File, Click Open, Click OK 

             4.    Publish Excel Template as Excel Service

             5. Create Webpart project in Ms Visual Studio 2010

b.   Insert sharepoint site URL and deploy as a farm solution

c. Add all reference in external library folder

 d. Add new class and fill it with code below

    using System;  
 using Microsoft.Office.Excel.Server.WebServices;  
 using Microsoft.SharePoint;  
 using System.Configuration;  
 namespace EquipmentBooking.Report  
 {  
   public class ExcelServiceReport : IDisposable  
   {  
     #region Variables  
     ExcelService _xlTemplateService;  
     Status[] _outTemplateStatus;  
     ExcelService _xlTemporaryService;  
     Status[] _outTemporaryStatus;  
     string _sessionTemporaryId;  
     #endregion  
     #region Field  
     //field for sheet name that going to filled with set cell  
     private string _sheetName = Constanta.REPORT_SHEET_1;  
     /// <summary>  
     /// Gets or sets the name of the sheet.  
     /// </summary>  
     /// <value>  
     /// The name of the sheet.  
     /// </value>  
     public string SheetName  
     {  
       get { return _sheetName; }  
       set { _sheetName = value; }  
     }  
     private string _targetWorkbookPath;  
     /// <summary>  
     /// Gets or sets the target wookbook path.  
     /// </summary>  
     /// <value>  
     /// The target wookbook path.  
     /// </value>  
     public string TargetWookbookPath  
     {  
       get { return _targetWorkbookPath; }  
       set { _targetWorkbookPath = value; }  
     }  
     private string _temporaryWorkbookPath;  
     /// <summary>  
     /// Gets or sets the temporary workbook path.  
     /// </summary>  
     /// <value>  
     /// The temporary workbook path.  
     /// </value>  
     public string TemporaryWorkbookPath  
     {  
       get { return _temporaryWorkbookPath; }  
       set { _temporaryWorkbookPath = value; }  
     }  
     #endregion  
     #region Constructor  
     /// <summary>  
     /// Default Constructor  
     /// </summary>  
     public ExcelServiceReport(string reportName)  
     {  
       //excel service variable  
       _xlTemplateService = new ExcelService();  
       _xlTemporaryService = new ExcelService();  
       _sessionTemporaryId = String.Empty;  
       //excel web access get the excel template  
       this.TargetWookbookPath = GenerateTemplatePath(reportName);  
       //set temporary file path  
       this.TemporaryWorkbookPath = GenerateTemporaryPath(reportName);  
       //call the open workbook, and point to the trusted  
       //location of the workbook to open  
       string sessionTemplateId = _xlTemplateService.OpenWorkbookForEditing(TargetWookbookPath,   
         "en-US", "en-US", out _outTemplateStatus);  
       //open template workbook & copy to temporary file  
       _xlTemplateService.SaveWorkbookCopy(sessionTemplateId, TemporaryWorkbookPath,   
         WorkbookType.FullWorkbook, SaveOptions.AllowOverwrite, out _outTemplateStatus);  
       //close template service  
       _xlTemplateService.CloseWorkbook(sessionTemplateId, out _outTemplateStatus);  
       //open for editing temporary file  
       _sessionTemporaryId = _xlTemporaryService.OpenWorkbookForEditing(TemporaryWorkbookPath,   
         "en-US", "en-US", out _outTemporaryStatus);  
     }  
     #endregion  
     #region Private Member  
     /// <summary>  
     /// Generates the template path.  
     /// </summary>  
     /// <param name="reportName">Name of the report.</param>  
     /// <returns></returns>  
     private string GenerateTemplatePath(string reportName)  
     {  
       string path = string.Concat(ConfigurationManager.AppSettings[Constanta.APP_SETTING_EXCEL_REPORT_TEMPLATE_PATH].ToString()  
         ,reportName, Constanta.REPORT_FILE_TYPE_XLSX);  
       return path;   
     }  
     /// <summary>  
     /// Generates the temporary path.  
     /// </summary>  
     /// <param name="reportName">Name of the report.</param>  
     /// <returns></returns>  
     private string GenerateTemporaryPath(string reportName)  
     {  
       string path = string.Concat(ConfigurationManager.AppSettings[Constanta.APP_SETTING_EXCEL_REPORT_TEMPORARY_PATH].ToString(),  
         reportName, '_',  
         DateTime.Now.ToString(Constanta.FORMAT_DATE_TIME), Constanta.REPORT_FILE_TYPE_XLSX  
         );  
       return path;  
     }  
     /// <summary>  
     /// Converts the empty string.  
     /// </summary>  
     /// <param name="stringItem">The string item.</param>  
     /// <returns></returns>  
     private string ConvertEmptyString(string stringItem)  
     {  
       if (string.IsNullOrEmpty(stringItem))  
       {  
         return string.Empty;  
       }  
       else  
       {  
         return stringItem;  
       }  
     }  
     #endregion  
     #region Public Member  
     /// <summary>  
     /// Sets data to selected cell.  
     /// </summary>  
     /// <param name="row">The row.</param>  
     /// <param name="col">The col.</param>  
     /// <param name="data">The data.</param>  
     public void SetCell(int row, int col, string data)  
     {  
       _xlTemporaryService.SetCell(_sessionTemporaryId, SheetName, row, col, ConvertEmptyString(data), out _outTemporaryStatus);  
     }  
     /// <summary>  
     /// Saves data to work book.  
     /// </summary>  
     public void SaveWorkBook()  
     {  
       _xlTemporaryService.SaveWorkbook(_sessionTemporaryId, out _outTemporaryStatus);  
       _xlTemporaryService.CloseWorkbook(_sessionTemporaryId, out _outTemporaryStatus);  
     }  
     /// <summary>  
     /// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.  
     /// </summary>  
     public void Dispose()  
     {  
       _xlTemplateService.Dispose();  
       _xlTemporaryService.Dispose();  
       _sessionTemporaryId = String.Empty;  
     }  
     #endregion  
   }  
 }  

 using System;  
 using System.Collections.Generic;  
 using System.Linq;  
 using System.Text;  
 namespace PTNNT.EquipmentBooking.Report  
 {  
   public class Constanta  
   {  
     #region Excel report  
     public const string SCHEDULE_REPORT_NAME = "ScheduleReport";  
     public const string EQUIPMENT_NO_REPORT_NAME = "EquipmentNumberReport";  
     public const string REPORT_SHEET_1 = "Sheet1";  
     public const string REPORT_DEFAULT_USER = "Unknown";  
     public const string REPORT_FILE_TYPE_XLSX = ".xlsx";  
     public const string REPORT_TEMPLATE_FILENAME_PREFIX = "TemplateRpt";  
     public const string REPORT_TEMPORARY_FILENAME_PREFIX = "TempRpt";  
     public const string APP_SETTING_EXCEL_REPORT_TEMPLATE_PATH = "ExcelReportTemplatePath";  
     public const string APP_SETTING_EXCEL_REPORT_TEMPORARY_PATH = "ExcelReportTemporaryPath";  
     public const string FORMAT_DATE_TIME = "MM-dd-yy_HHmmss";  
     #region Cell Position  
     public const int COLUMN_HEADER_POSITION = 5;  
     public const int ROW_HEADER_POSITION = 6;  
     public const int ROW_CONTENT_POSITION = 6;  
     public const int ROW_EXCEL_CATEGORY_POSITION = 1;  
     public const int ROW_EXCEL_REPORT_TYPE_POSITION = 2;  
     public const int ROW_EXCEL_DATE_POSITION = 3;  
     public const int COL_EXCEL_FILTER_POSITION = 2;  
     #endregion  
     #endregion  
     #region column name  
     public const string EQUIPMENT_BOOKING_LIST = "EquipmentBooking";  
     public const string EQUIPMENT_CATEGORY_LIST = "EquipmentCategory";  
     public const string EQUIPMENT_NUMBER_LIST = "EquipmentNumber";  
     public const string EQUIPMENT_TYPE_LIST = "EquipmentType";  
     public const string EQUIPMENT_MODEL_LIST = "EquipmentModel";  
     public const string EQUIPMENT_CATEGORY = "Equipment Category";  
     public const string EQUIPMENT_MODEL = "Equipment Model";  
     public const string EQUIPMENT_TYPE = "Equipment Type";  
     public const string REQUESTED_FOR = "Requested For";  
     public const string DESCRIPTION_OF_WORK = "Description Of Work";  
     public const string PROPOSED_START_DATE = "Proposed Start Date";  
     public const string PROPOSED_FINISHED_DATE = "Proposed Finished Date";  
     public const string SCHEDULED_START_DATE = "Scheduled Start Date";  
     public const string SCHEDULED_FINISH_DATE = "Scheduled Finish Date";  
     public const string PURPOSE = "Purpose";  
     public const string REQUESTED_FOR_DEPARTMENT = "Requested For Department";  
     public const string EQUIPMENT_NO = "Equipment No";  
     public const string EQUIPMENT_NUMBER = "Equipment Number";  
     #endregion  
     #region list field  
     public const string NAME = "Name";  
     public const string ID = "ID";  
     #endregion  
   }  
 }  

 using System;  
 using System.Web.UI;  
 using System.Web.UI.WebControls;  
 using System.Web.UI.WebControls.WebParts;  
 using Microsoft.Office.Excel.Server.WebServices;  
 using Microsoft.Office.Excel.WebUI;  
 using Microsoft.SharePoint;  
 using System.Collections.Generic;  
 using System.Linq;  
 using System.Data;  

    private ExcelWebRenderer _reportWebpart = new ExcelWebRenderer();  
 private ExcelServiceReport excelReport;  
 private SPList equipmentBookingList;  

 //generate report based on report type  
     public void GenerateReport()  
     {  
       int column = 0;  
       int row = 0;  
       using (excelReport = new ExcelServiceReport(Constanta.SCHEDULE_REPORT_NAME))  
       {  
         //get EquipmentBooking list  
         equipmentBookingList = null;  
         equipmentBookingList = SPContext.Current.Web.Lists.TryGetList(Constanta.EQUIPMENT_BOOKING_LIST);  
         //get list dates  
         var dates = GetListDates();  
         //write filter to excel  
         excelReport.SetCell(Constanta.ROW_EXCEL_CATEGORY_POSITION,  
           Constanta.COL_EXCEL_FILTER_POSITION, string.Concat(": ", DDEquipmentCategory.SelectedItem.Text));  
         excelReport.SetCell(Constanta.ROW_EXCEL_REPORT_TYPE_POSITION,  
           Constanta.COL_EXCEL_FILTER_POSITION, RBMonthly.Checked ? string.Concat(": ", RBMonthly.Text) : string.Concat(": ", RBSpesific.Text));  
         excelReport.SetCell(Constanta.ROW_EXCEL_DATE_POSITION,  
           Constanta.COL_EXCEL_FILTER_POSITION, RBMonthly.Checked ? string.Concat(": ", DDMonth.SelectedItem.Text, " ", DDYear.SelectedItem.Text) : string.Concat(": ", string.Format("{0:dd MMMM yyyy}", DateFrom.SelectedDate), " - ", string.Format("{0:dd MMMM yyyy}", DateTo.SelectedDate)));  
         foreach (var item in dates)  
         {  
           row = 0;  
           //write date header to excel  
           excelReport.SetCell(Constanta.COLUMN_HEADER_POSITION,  
             column, string.Format("{0:dd MMMM yyyy}", item.Date));  
           var items = (from li in equipmentBookingList.Items.OfType<SPListItem>()  
                  where (li[Constanta.SCHEDULED_START_DATE] is DateTime) && ((DateTime)li[Constanta.SCHEDULED_START_DATE]).Date <= item.Date &&  
                  (li[Constanta.SCHEDULED_FINISH_DATE] is DateTime) && ((DateTime)li[Constanta.SCHEDULED_FINISH_DATE]).Date >= item.Date  
                  select li).ToList();  
           //if not all category report  
           if (DDEquipmentCategory.SelectedIndex != 0)  
           {  
             items = items.Where(x => new SPFieldLookupValue(x[Constanta.EQUIPMENT_CATEGORY] as String).LookupValue.ToString() == DDEquipmentCategory.SelectedItem.Text).ToList();  
           }  
           //write content to excel  
           foreach (var data in items)  
           {  
             string temp = string.Concat(new SPFieldLookupValue(data[Constanta.EQUIPMENT_CATEGORY] as String).LookupValue, " ", Environment.NewLine,  
               new SPFieldLookupValue(data[Constanta.EQUIPMENT_TYPE] as String).LookupValue, " ",  
               new SPFieldLookupValue(data[Constanta.EQUIPMENT_MODEL] as String).LookupValue, " ", Environment.NewLine,  
               new SPFieldLookupValue(data[Constanta.REQUESTED_FOR] as String).LookupValue, " ", Environment.NewLine,  
               data[Constanta.DESCRIPTION_OF_WORK] as String);  
             excelReport.SetCell(Constanta.ROW_CONTENT_POSITION + row,  
             column, temp);  
             row++;  
           }  
           column++;  
         }  
         //save the edited temporary workbook to designated temporary work book  
         excelReport.SaveWorkBook();  
         string path = excelReport.TemporaryWorkbookPath;  
         //show temporary workbook path  
         _reportWebpart.WorkbookUri = path;  
         _reportWebpart.AllowRecalculation = true;  
         this.ReportWebpart.Controls.Add(_reportWebpart);  
         this.ReportWebpart.Visible = true;  
       }  
     }