"""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. Code should be compatible with Office 2003 or Office 2007
If you get an error message about "IOError: Could not add reference to assembly Microsoft.Office.Interop.Excel" then 
you need to install the Primary Interop Assemblies for Microsoft Office, which can be downloaded from:
Office 2003 PIA: http://www.microsoft.com/downloads/details.aspx?familyid=3c9a983a-ac14-4125-8ba0-d36d67e0f4ad&displaylang=en
Office 2007 PIA: http://www.microsoft.com/downloads/details.aspx?familyid=59DAEBAA-BED4-4282-A28C-B864D8BFA513&displaylang=en
Install from Office Setup CD: http://msdn.microsoft.com/en-us/library/aa159923(office.11).aspx#officeprimaryinteropassembliesfaq_after

The original code gave an Error: 0x80028018 "Old Format or Invalid Type Library" when trying to set worksheet values (Name, Cell.Value2, etc) 
This is a known issue when the computer's region is set to something other than en-us: http://support.microsoft.com/kb/320369
The ExcelManager.SetProperty method was added as a workaround to this issues. It uses the Reflection and the InvokeMember method
to ensure that the CurrentCulture is set to en-US. This method should be used whenever you set a property value to ensure
that the code will work in any culture. 

More info at http://www.ironpython.info/index.php/Interacting_with_Excel

"""
import sys
import clr
clr.AddReference("Office")
clr.AddReference("Microsoft.Office.Interop.Excel")
from Microsoft.Office.Interop import Excel
from System.Reflection import Missing, BindingFlags
from System.Threading import Thread
from System.Globalization import CultureInfo
from System import Array, 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\\RslPivot V1.4\\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(True) #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 SetProperty(target, name, value):
        """Used to set properties using the en-US culture, which is the safest way to do Excel COM integration.
        See: http://support.microsoft.com/kb/320369"""
        try:
            args = Array[object]([value])
            return target.GetType().InvokeMember(name, BindingFlags.Instance | BindingFlags.SetProperty | BindingFlags.Public, None, target, args, CultureInfo(1033))
        except:
            print "Error setting Property: " + name
            print sys.exc_info()
            raise

    @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.SetProperty(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  #Threw error whenever the computer's region was set to something other than en-us. Use SetProperty method instead.
        ExcelManager.SetProperty(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)
        aCell = ExcelManager.ExcelWorksheet.Cells.Item.__getitem__(ColIndex).Item.__getitem__(RowIndex)
        if type(Value) == DateTime:
            #aCell.Value2 = Value.ToOADate() #Threw exception when culture was not en-us. Use SetProperty instead
            ExcelManager.SetProperty(aCell, "Value2", Value.ToOADate())
            ExcelManager.SetProperty(aCell, "NumberFormat", ExcelManager.LocalShortDateFormat)
        else:
            ExcelManager.SetProperty(aCell, "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()
        ExcelManager.SetProperty(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.SetProperty(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.SetProperty(ExcelManager.PivotChart, "HasLegend" , True)
        ExcelManager.SetProperty(ExcelManager.PivotChart, "HasTitle", True) #Show chart title and set it's text
        ExcelManager.SetProperty(ExcelManager.PivotChart.ChartTitle, "Text", strName)
        ExcelManager.SetProperty(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.
           NOTE: May cause tool bars to be perminantly disabled if the worksheet is saved! It is advised not to use this method, needs to be reworked."""
        #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")
        pass