"""ExcelManager contains code for interacting with an Excel spreadsheet.
This code should work with both IronPython 1.1 and IronPython 2.0
NOTE: Microsoft Excel must be installed on the machine for this to work."""
import sys
import clr
clr.AddReference("Office")
clr.AddReference("Microsoft.Office.Interop.Excel")
from Microsoft.Office.Interop import Excel
from System.Reflection import Missing
from System.Threading import Thread
from System import DateTime, DayOfWeek

def reload():
    """Helper method to reload this script when testing or debuging"""
    print "Reloading..."
    global ExcelManager
    ExcelManager.CloseExcel(True) #Force Excel to close
    execfile( "D:\\My Documents\\Resolver\\RslExtentions V1.1\\ExcelManager.py")

def run():
    """Sample method for how to use the Excel Manager"""
    global SheetData
    SheetData = [['a','b','c'],[1,2,3],[4,5,6],[7,8,9]] #Data should be packed row by row in a multi-dimentional array. First Row is Header Row.
    ExcelManager.OpenExcel(False) #Open Excel but do not show the window yet
    ExcelManager.NewBlankWorkbook("My Autofilter")
    ExcelManager.LoadSheetData(SheetData)
    ExcelManager.CreatePivotTable("My Pivot Table")
    ExcelManager.CreatePivotChart("My Pivot Chart")
    ExcelManager.HideToolbars(lstExcept=["PivotTable Field List", "Standard", "FormulaBar"]) #Disable all the toolbars except the Pivottable, Standard toolbar and FormulaBar
    ExcelManager.ShowWindow()

class ExcelManager(object):
    """Static class for managing the Excel application. 
    See http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel(office.11).aspx for more details."""
    
    ExcelApp = None #Excel Application Interop Object
    ExcelWorkbook = None #Excel Workbook Interop Object
    ExcelWorksheet = None #Excel Worksheet Interop Object
    PivotTable = None 
    PivotChart = None 
    LocalShortDateFormat = Thread.CurrentThread.CurrentCulture.DateTimeFormat.ShortDatePattern #used to format from OADate to correct format
    
    @staticmethod
    def OpenExcel(ShowWindow=False):
        """Opens a new Excel Application.
        ShowWindow: True means the window will be visible, otherwise the window will be hidden.
        """
        #print "ExcelManager: Opening Excel"
        ExcelManager.ExcelApp = Excel.ApplicationClass()
        if ShowWindow:
            ExcelManager.ShowWindow()
        else:
            ExcelManager.HideWindow()

    @staticmethod
    def ShowWindow():
        ExcelManager.ExcelApp.Visible = True
        
    @staticmethod
    def HideWindow():
        ExcelManager.ExcelApp.Visible = False
    
    @staticmethod
    def CloseExcel(ForceClose=False):
        """Closes the Excel application. If ForceClose=True then the user will not be prompted to save any changes."""
        if ExcelManager.ExcelApp <> None and ExcelManager.ExcelWorkbook <> None:
            print "Quitting Excel..."
            if ForceClose and ExcelManager.ExcelApp.Workbooks.Count > 0:
                ExcelManager.ExcelWorkbook.Saved = True                
            ExcelManager.ExcelApp.Quit()
            
    @staticmethod
    def NewBlankWorkbook(SheetName="Worksheet"):
        """Opens a new blank workbook in Excel with one worksheet with the given Name."""
        #print "ExcelManager: NewBlankWorkbook"
        ExcelManager.ExcelWorkbook = ExcelManager.ExcelApp.Workbooks.Add()
        #NOTE: use Item.__getitem__(index) to be compatable with both IronPython 1.1 and IronPython 2.0
        ExcelManager.ExcelWorkbook.Worksheets.Item.__getitem__(3).Delete() #workbook starts with 3 blank worksheets. Delete the second and 3rd.
        ExcelManager.ExcelWorkbook.Worksheets.Item.__getitem__(2).Delete()   
        ExcelManager.ExcelWorkbook.Worksheets.Item.__getitem__(1).Name = SheetName
        ExcelManager.ExcelWorksheet = ExcelManager.ExcelWorkbook.Worksheets.Item.__getitem__(1)
        
    @staticmethod    
    def SetCell(RowIndex, ColIndex, Value):
        #print "Loading Cell(" + str(RowIndex) + str(ColIndex) +  ") with " + str(Value)
        if type(Value) == DateTime:
            aCell = ExcelManager.ExcelWorksheet.Cells.Item.__getitem__(ColIndex).Item.__getitem__(RowIndex)
            aCell.Value2 = Value.ToOADate()
            aCell.NumberFormat = ExcelManager.LocalShortDateFormat
        else:
            ExcelManager.ExcelWorksheet.Cells.Item.__getitem__(ColIndex).Item.__getitem__(RowIndex).Value2 = Value
    
    @staticmethod
    def SetRow(RowIndex, ArrayValues):
        #print "Loading Row " + str(RowIndex) + " with " + str(ArrayValues)
        intColIndex = 0
        for aValue in ArrayValues:
            intColIndex += 1
            ExcelManager.SetCell(RowIndex,intColIndex, aValue)

    @staticmethod
    def GetCommandBar(strName):
        """Helper method used to get a Commandbar. This way the code works with both IronPython 1.1 and IronPython 2.0"""
        if hasattr(ExcelManager.ExcelApp.CommandBars, "Item"):
            return ExcelManager.ExcelApp.CommandBars.Item(strName) #IronPython 2.0
        else:
            return ExcelManager.ExcelApp.CommandBars[strName] #IronPython 1.1

    @staticmethod            
    def LoadSheetData(SheetData):
        """Loads data into the Excel WorkSheet.
           SheetData should contain an object for each row, with each object containing data for that row."""
        intRow = 0
        for aRow in SheetData:
            intRow +=1
            ExcelManager.SetRow(intRow,aRow)
        ExcelManager.ExcelWorksheet.UsedRange.AutoFilter(1, Missing.Value, Excel.XlAutoFilterOperator.xlAnd, Missing.Value, True) #Enable Autofilter 
     
    @staticmethod
    def CreatePivotTable(strName="Pivot Table"):
        """Creates a Pivot Table.
        See http://www.eggheadcafe.com/community/aspnet/2/10050513/solved--export-data-from.aspx for more info."""
        NewSheet = ExcelManager.ExcelWorkbook.Worksheets.Add()
        NewSheet.Name = "Pivot Table"
        ExcelManager.ExcelWorkbook.PivotTableWizard(Excel.XlPivotTableSourceType.xlDatabase, ExcelManager.ExcelWorksheet.UsedRange, NewSheet.UsedRange, strName, True, True, True, False, Missing.Value, Missing.Value, False, False, Excel.XlOrder.xlDownThenOver, 5, Missing.Value, Missing.Value)
        ExcelManager.ExcelWorkbook.ShowPivotTableFieldList = True
        ExcelManager.GetCommandBar("PivotTable").Visible = False
        ExcelManager.PivotTable = NewSheet
    
    @staticmethod    
    def CreatePivotChart(strName="Pivot Chart"):
        """Creates a Pivot Chart.
        See http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.chart_properties(VS.80).aspx for more info.
        Example code: http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/c7b0f6dd-8890-41c7-a5e0-e18f70686f8c"""
        ExcelManager.PivotChart = ExcelManager.ExcelWorkbook.Charts.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value)
        #ExcelManager.PivotChart.ChartWizard(ExcelManager.ExcelWorksheet.UsedRange, Excel.XlChartType.xlColumnClustered, Missing.Value, Excel.XlRowCol.xlColumns, Missing.Value, Missing.Value, True, "Pivot Chart Name", Missing.Value, Missing.Value, Missing.Value) #This method threw an 0x800A03EC error message, so set the values seperately
        ExcelManager.PivotChart.ChartType = Excel.XlChartType.xlColumnClustered
        #ExcelManager.PivotChart.SetSourceData(ExcelManager.ExcelWorksheet.UsedRange, Excel.XlRowCol.xlColumns) #Threw error... but seems to work fine without it. Must assume that it is a pivot chart
        ExcelManager.PivotChart.HasLegend = True
        ExcelManager.PivotChart.HasTitle = True #Show chart title and set it's text
        ExcelManager.PivotChart.ChartTitle.Text = strName
        ExcelManager.PivotChart.Name = "Pivot Chart" #Set Tab name
        
    @staticmethod
    def HideToolbars(lstExcept=["PivotTable Field List", "Standard", "FormulaBar"]):
        """Hides all the toolbars and only shows the worksheet and charts. If the toolbar's name is in lstExcept then it will remain visible."""
        for aBar in ExcelManager.ExcelApp.CommandBars:
            if lstExcept.Contains(aBar.Name):
                aBar.Enabled = True
            else:
                aBar.Enabled = False #disable
        ExcelManager.ExcelApp.DisplayFormulaBar = lstExcept.Contains("FormulaBar")