JG Vimalan's Blog

It is all about coding!

Excel report using C# in SharePoint Custom Webpart

The following code snippet is focussing on creating an Excel at runtime to display the employees attendance report for the selected month using C# via SharePoint WebPart,

 

So, you will get an idea on creating excel at runtime using C#,

 

#region Required Namespaces

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.IO;
using System.Data;
using Microsoft.SharePoint;
using System.Web;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Drawing;
using System.Reflection;

#endregion  
    

. . .

. . .

. . .
        /// <summary>
        /// Create the excel workbook with monthly report.
        /// </summary>
        public void CreateExcelWorkbook()
        {
            _Message.Text = “Generating report…”;
           
            Excel.Application oXL;
            Excel._Workbook oWB;
            Excel._Worksheet oSheet;
            Excel.Range oRng;
            SPSite mySite= null;
            SPWeb myWeb = null;
            string strCurrentDir = @”C:\Inetpub\wwwroot\wss\VirtualDirectories\80 \MonthlyReportsHolder\”;

            try
            {
                SPSecurity.RunWithElevatedPrivileges(delegate()
                {            
                    GC.Collect();

                    mySite= new SPSite(“http://MySite“);
                    myWeb = mySite.OpenWeb(“/MyWeb”);
                    myWeb.AllowUnsafeUpdates = true;
                    myWeb.ParentWeb.AllowUnsafeUpdates = true;

                    SPList members = myWeb.ParentWeb.Lists[“Company Members”];
                    SPList leaveRecords = myWeb.Lists[“Leave Details”];                  

                    oXL = new Excel.Application();
        oXL.Visible = false;
        //Get a new workbook.
        oWB = (Excel._Workbook)(oXL.Workbooks.Add( Missing.Value ));
        oSheet = (Excel._Worksheet)oWB.ActiveSheet;   
               
                    //Legend
                    oSheet.Cells[1, 1] = “LEGEND”;
                    oSheet.Cells[1, 2] = “FORENOON”;
                    Excel.Range fn = (Excel.Range)oSheet.Cells[1, 2];
                    fn.Font.Color = ColorTranslator.ToWin32(System.Drawing.Color.Green);
                    fn.Font.Bold = true;
                    fn.EntireColumn.AutoFit();
                    oSheet.Cells[1, 3] = “AFTERNOON”;
                    Excel.Range an = (Excel.Range)oSheet.Cells[1, 3];
                    an.Font.Color = ColorTranslator.ToWin32(System.Drawing.Color.Blue);
                    an.Font.Bold = true;
                    an.EntireColumn.AutoFit();
                    oSheet.Cells[1, 4] = “FULLDAY”;
                    Excel.Range fu = (Excel.Range)oSheet.Cells[1, 4];
                    fu.Font.Color = ColorTranslator.ToWin32(System.Drawing.Color.Red);
                    fu.Font.Bold = true;
                    fu.EntireColumn.AutoFit();
                    oSheet.Cells[1, 5] = “PRESENT”;
                    Excel.Range pr = (Excel.Range)oSheet.Cells[1, 5];
                    pr.Font.Color = ColorTranslator.ToWin32(System.Drawing.Color.Gray);
                    pr.Font.Bold = true;
                    pr.EntireColumn.AutoFit();

                    _Message.Text = “Generating report…”;

                    // Build the sheet contents
                    for (int count = 1; count <= members.ItemCount; count++)
                    {
                        _Message.Text = “Generating report…”;

                        string memberName = members.Items[count – 1][“Team_x0020_Member”].ToString();

                        int index = memberName.IndexOf(“#”);
                        if (index > 0)
                            memberName = memberName.Substring(index + 1);

                        oSheet.Cells[count + 3, 1] = memberName;

                        string memberID = members.Items[count – 1][“Title”].ToString();

                        SPQuery filterQuery = new SPQuery();
                        filterQuery.Query = “<Where><And>” +
                        “<Eq><FieldRef Name=’EmployeeID’></FieldRef> <Value Type=’Text’>” + memberID + “</Value></Eq>” +
                        “<Eq><FieldRef Name=’Status’></FieldRef> <Value Type=’Text’>” + “HR Approved” + “</Value></Eq>” +
                        “</And>” +
                        “</Where>”;

                        SPListItemCollection thisMemberLeaveRecords = leaveRecords.GetItems(filterQuery);                       

                        if (thisMemberLeaveRecords.Count > 0)
                        {
                            foreach (SPListItem leaveRecord in thisMemberLeaveRecords)
                            {
                                DateTime From = Convert.ToDateTime(leaveRecord[“EventDate”]);
                                DateTime To = Convert.ToDateTime(leaveRecord[“EndDate”]);

                                TimeSpan duration = To – From;

                                if (From.Month == Month || To.Month == Month)
                                {
                                    if (From.ToShortTimeString() == “2:00 PM”)
                                    {
                                        if (From.Month == Month)
                                        {
                                            oSheet.Cells[count + 3, From.Day + 1] = leaveRecord[“LeaveType”].ToString();

                                            Excel.Range colorSetterA = (Excel.Range)oSheet.Cells[count + 3, From.Day + 1];
                                            colorSetterA.Font.Color = ColorTranslator.ToWin32(System.Drawing.Color.Blue);
                                            colorSetterA.Font.Bold = true;
                                        }
                                    }
                                    else if (To.ToShortTimeString() == “1:00 PM”)
                                    {
                                        if (To.Month == Month)
                                        {
                                            oSheet.Cells[count + 3, From.Day + 1] = leaveRecord[“LeaveType”].ToString();

                                            Excel.Range colorSetterB = (Excel.Range)oSheet.Cells[count + 3, From.Day + 1];
                                            colorSetterB.Font.Color = ColorTranslator.ToWin32(System.Drawing.Color.Green);
                                            colorSetterB.Font.Bold = true;
                                        }
                                    }
                                    else
                                    {
                                        if (From.Month == Month)
                                        {
                                            oSheet.Cells[count + 3, From.Day + 1] = leaveRecord[“LeaveType”].ToString();

                                            Excel.Range colorSetterC = (Excel.Range)oSheet.Cells[count + 3, From.Day + 1];
                                            colorSetterC.Font.Color = ColorTranslator.ToWin32(System.Drawing.Color.Red);
                                            colorSetterC.Font.Bold = true;
                                        }
                                    }
                                }

                                if (duration.Days >= 1)
                                {
                                    int totalDays = duration.Days;

                                    for (int day = 1; day <= totalDays; day++)
                                    {
                                        if (From.AddDays(day).Month == Month)
                                        {                                          
                                            oSheet.Cells[count + 3, From.AddDays(day).Day + 1] = leaveRecord[“LeaveType”].ToString();

                                            Excel.Range colorSetterD = (Excel.Range)oSheet.Cells[count + 3, From.AddDays(day).Day + 1];
                                            colorSetterD.Font.Color = ColorTranslator.ToWin32(System.Drawing.Color.Red);
                                            colorSetterD.Font.Bold = true;                                          
                                        }
                                    }
                                }
                            }
                        }
                    }

                    DateTime dt = Convert.ToDateTime(Month + “/” + “1” + “/” + Year);

                    List<string> companyHolidays = new List<string>();
                    SPList companyHolidayList = myWeb.Lists[“companyHolidays”];

                    if (companyHolidayList.ItemCount > 0)
                    {
                        foreach (SPListItem item in companyHolidayList.Items)
                            companyHolidays.Add(Convert.ToDateTime(item[“Date”]).ToShortDateString());
                    }

                    int holidaysCount = 0;

                    for (int day = 1; day <= DateTime.DaysInMonth(Year, Month); day++)
                    {
                        _Message.Text = “Generating report…”;

                        oSheet.Cells[3, day + 1] = day;
                        bool dayCounted = false;

                        for (int count = 1; count <= members.ItemCount; count++)
                        {
                            Excel.Range prColor = (Excel.Range)oSheet.Cells[count + 3, day + 1];
                            prColor.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

                            if (dt.AddDays(day-1).DayOfWeek == DayOfWeek.Saturday ||
                                dt.AddDays(day-1).DayOfWeek == DayOfWeek.Sunday ||
                                companyHolidays.Contains(dt.AddDays(day-1).ToShortDateString()))                               
                            {
                                oSheet.Cells[count + 3, day + 1] = string.Empty;
                                if (!dayCounted)
                                {
                                    dayCounted = true;
                                    holidaysCount++;
                                }
                                continue;
                            }
                           
                            if (prColor.Text == null || prColor.Text.ToString() == “”)
                            {
                                oSheet.Cells[count + 3, day + 1] = “P”;
                                prColor.Font.Color = ColorTranslator.ToWin32(System.Drawing.Color.Gray);
                            } 
                        }
                    }

                    oSheet.Cells[3, DateTime.DaysInMonth(Year, Month) + 2] = “No of Working Days”;
                    Excel.Range noOfWrkDys = (Excel.Range)oSheet.Cells[3, DateTime.DaysInMonth(Year, Month) + 2];
                    noOfWrkDys.EntireColumn.AutoFit();
                    noOfWrkDys.Font.Bold = true;
                    oSheet.Cells[3, DateTime.DaysInMonth(Year, Month) + 3] = “No of Days Present”;
                    Excel.Range noOfDysPrsnt = (Excel.Range)oSheet.Cells[3, DateTime.DaysInMonth(Year, Month) + 3];
                    noOfDysPrsnt.EntireColumn.AutoFit();
                    noOfDysPrsnt.Font.Bold = true;
                    oSheet.Cells[3, DateTime.DaysInMonth(Year, Month) + 4] = “Days on PL”;
                    Excel.Range plDays = (Excel.Range)oSheet.Cells[3, DateTime.DaysInMonth(Year, Month) + 4];
                    plDays.EntireColumn.AutoFit();
                    plDays.Font.Bold = true;
                    oSheet.Cells[3, DateTime.DaysInMonth(Year, Month) + 5] = “Days on CL”;
                    Excel.Range clDays = (Excel.Range)oSheet.Cells[3, DateTime.DaysInMonth(Year, Month) + 5];
                    clDays.EntireColumn.AutoFit();
                    clDays.Font.Bold = true;

                    for (int count = 1; count <= members.ItemCount; count++)
                    {
                        oSheet.Cells[count + 3, DateTime.DaysInMonth(Year, Month) + 2] = DateTime.DaysInMonth(Year, Month) – holidaysCount;
                    }                   

                    for (int count = 1; count <= members.ItemCount; count++)
                    {
                        double presentDays = 0;
                        double plTaken = 0;
                        double clTaken = 0;

                        for (int day = 1; day <= DateTime.DaysInMonth(Year, Month); day++)
                        {
                            Excel.Range prColor = (Excel.Range)oSheet.Cells[count + 3, day + 1];
                            if (prColor.Text.ToString() != “”)
                            {
                                if (prColor.Text.ToString() == “P”)
                                    presentDays += 1;

                                if (prColor.Text.ToString() == “PL” && (Convert.ToInt32(prColor.Font.Color) == ColorTranslator.ToWin32(System.Drawing.Color.Blue) ||
                                    Convert.ToInt32(prColor.Font.Color) == ColorTranslator.ToWin32(System.Drawing.Color.Green)))
                                    plTaken += 0.5;
                                else if (prColor.Text.ToString() == “PL” && Convert.ToInt32(prColor.Font.Color) == ColorTranslator.ToWin32(System.Drawing.Color.Red))
                                    plTaken += 1;

                                if (prColor.Text.ToString() == “CL” && (Convert.ToInt32(prColor.Font.Color) == ColorTranslator.ToWin32(System.Drawing.Color.Blue) ||
                                    Convert.ToInt32(prColor.Font.Color) == ColorTranslator.ToWin32(System.Drawing.Color.Green)))
                                    clTaken += 0.5;
                                else if (prColor.Text.ToString() == “CL” && Convert.ToInt32(prColor.Font.Color) == ColorTranslator.ToWin32(System.Drawing.Color.Red))
                                    clTaken += 1;

                                if (Convert.ToInt32(prColor.Font.Color) == ColorTranslator.ToWin32(System.Drawing.Color.Blue) ||
                                    Convert.ToInt32(prColor.Font.Color) == ColorTranslator.ToWin32(System.Drawing.Color.Green))
                                    presentDays += 0.5;
                            }
                        }

                        oSheet.Cells[count + 3, DateTime.DaysInMonth(Year, Month) + 3] = presentDays;
                        oSheet.Cells[count + 3, DateTime.DaysInMonth(Year, Month) + 4] = plTaken;
                        oSheet.Cells[count + 3, DateTime.DaysInMonth(Year, Month) + 5] = clTaken;
                    }

                    //Format A1:AF1 as bold, vertical alignment = center.
        oSheet.get_Range(“A3”, “AF1”).Font.Bold = true;                  
                 oSheet.get_Range(“A3”, “AF1”).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; 
                    oSheet.get_Range(“A3”, “AF1”).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                   
        //AutoFit columns
        oRng = oSheet.get_Range(“A3”, “A3”);
                    oSheet.get_Range(“A3”, “A” + members.ItemCount + 3).Font.Bold = true;                  

        oRng.EntireColumn.AutoFit();
        oXL.Visible = false;
        oXL.UserControl = false;

        string strFile =”Report” + System.DateTime.Now.Ticks.ToString() + “.xls”;
                    oWB.SaveAs( strCurrentDir + strFile,Excel.XlFileFormat.xlWorkbookDefault,null,null,false,false,Excel.XlSaveAsAccessMode.xlShared,false,false,null,null,null);

        // Need all following code to clean up and extingush all references!!!
        oWB.Close(null,null,null);
        oXL.Workbooks.Close();
        oXL.Quit();
        System.Runtime.InteropServices.Marshal.ReleaseComObject (oRng);
        System.Runtime.InteropServices.Marshal.ReleaseComObject (oXL);
        System.Runtime.InteropServices.Marshal.ReleaseComObject (oSheet);
        System.Runtime.InteropServices.Marshal.ReleaseComObject (oWB);
        oSheet=null;
        oWB=null;
        oXL = null;
        GC.Collect();  // force final cleanup! 

                    _Message.ForeColor = Color.Red;
                    _Message.Text = “<A href=” + mySite.Url + @”\MonthlyReportsHolder\”+ strFile + “>Download Report</a>”;
                });
            }
            catch(System.Exception error)
            {
                _Message.Text = error.Message;
            }
            finally
            {
                //dispose sharepoint objects!
                myWeb.Dispose();
                mySite.Dispose();
            }
        }

Advertisements

September 27, 2009 - Posted by | SharePoint

2 Comments »

  1. thanks for sharing the info.that is interesting.

    Comment by bike sales | October 30, 2010 | Reply

  2. sharepoint developer? Sell your webparts @ http://www.thesharepointmarket.com/submit-product/

    Comment by thesharepointmarket | November 6, 2010 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: