When the samples are downloaded from the internet and depending on the Excel setting under File > Options > Trust Center > Trust Center Settings..., Excel can give you a Security Warning - Enable Content. If that is the case, the excecution of the macro will end with hiding the Workbook completely. In that case, only a restart of Excel helps. A workaround is to close/reopen the Workbook just after enabling the Security Warning.

Add ability to change chart_type.

While a single date is always correctly formatted, on some installations, various dates in lists don't get transferred correctly.

Range('A2').value = [datetime(2012, 1, 1), datetime(2000, 1, 1)] results in 40909 36526 instead of 1/1/2012 1/1/2000.

See http://stackoverflow.com/questions/24121692/importing-xlwings-module-into-python-3-4

When installed with pip...

When reading in horizontal/vertical Excel Ranges, xlwings currently returns a nested ("2d") list:

[[1,2,3,4]]

They should be returned as flat list using something like:

[item for sublist in l for item in sublist]

in order to get:

[1,2,3,4]

Same actually for vertical lists [[1],[2]] and for numpy arrays.

Add argument atleast_2d to force 2d behaviour.

First of all, thanks for such amazing library. I am looking forward to having this useful module as comprehensive and soon as possible. I am kinda new to Python, mostly I am using Excel-Python Integration. I tried to put some functions I am using alot.
They worked nice for me so far. I hope I could help somehow.
Cheers

Offset * I have put this code under main.Range*

def offset(self,row,col):
    return Range(xlplatform.get_worksheet_name(self.xl_sheet),
                (self.row1+row, self.col1+col), **self.kwargs)

Color

**Codes in _xlwindows.py**
def set_color(xl_range,color_name_or_number):
    all_colors = {'black':1,
                  'white':2,
                  'red':3,
                  'green':4,
                  'blue':5,
                  'yellow':6,
                  'magenta':7,
                  'cyan':8,
                  'purple':13,
                  'orange':46,
                  'brown':53,
                  '':-4142}
    try:
        xl_range.Interior.ColorIndex = all_colors[color_name_or_number]
    except KeyError:
        xl_range.Interior.ColorIndex = color_name_or_number

def get_color(xl_range):
    color_codes = {1: 'black',
                   2: 'white',
                   3: 'red',
                   4: 'green',
                   5: 'blue',
                   6: 'yellow',
                   7: 'magenta',
                   8: 'cyan',
                   13: 'purple',
                   46: 'orange',
                   53: 'brown',
                   -4142:''}
    try:
        return color_codes[xl_range.Interior.ColorIndex]
    except KeyError:
        return xl_range.Interior.ColorIndex
------------------------------------------------------------------------------------------------------------
**Codes in main.Range**
    @property                 
    def color(self):
        """
        Sets and Gets color for given Range
        Available colors:   black, white, red, green, blue, yellow, 
                            magenta,cyan, purple, orange, brown     
        """
        return xlplatform.get_color(self.xl_range)

    @color.setter
    def color(self, color_name_or_number):
        xlplatform.set_color(self.xl_range, color_name_or_number) 

Worksheet Functions

**Codes in main.Workbook**
    def add_worksheet(self,worksheet_name = ''):
        if not worksheet_name.lower() in list(map(
        lambda x:x.lower(),xlplatform.sheet_list(self.xl_workbook))):
            xlplatform.new_worksheet(self.xl_workbook, worksheet_name)
        else:
            raise Exception("Sheetname '{}', with case ignored, is already in use."
            .format(worksheet_name))

    def remove_sheet(self, sheetname):
        xlplatform.delete_worksheet(self.xl_workbook,sheetname)


    def all_sheets(self):
        return xlplatform.sheet_list(self.xl_workbook)
---------------------------------------------------------------------------------------------------------
**Codes in _xlwindows.py
def new_worksheet(xl_workbook,worksheet_name):
    sheet_count = xl_workbook.Worksheets.Count    
    if worksheet_name == '':        
        xl_sheet = xl_workbook.Worksheets.Add (After=xl_workbook.Worksheets(sheet_count))
    else:
        xl_sheet = xl_workbook.Worksheets.Add (After=xl_workbook.Worksheets(sheet_count))
        xl_sheet.Name = worksheet_name
    return xl_sheet

def delete_worksheet(xl_workbook, sheetname):
    xl_workbook.Application.DisplayAlerts = False    
    xl_workbook.Sheets(sheetname).Delete()

def sheet_list(xl_workbook):
    z = []
    sheet_count = xl_workbook.Worksheets.Count
    for i in range(1,sheet_count+1):
        z.append(xl_workbook.Worksheets(i).Name)
    return z

Excel prints a 65535 instead. Currently this is only handled for np.nan within numpy arrays. Note that np.nan is of type numbers.Number

• on PyPi
• on docs

in Python 2.5, things like Range(*args, workbook=self.com_workbook, **kwargs)are not legal.

when called from VBA

stderr includes warnings. Windows checks for the ErrorCode - if it is 0, then no log are shown even though it may contain warnings. Mac checks for the content of the file, that's why for Mac, they are muted on using python -W ignore foo.py.

Just a problem for Python 2.7? ADODB seems to rounds to the next second, for example...

This causes the same problem as np.nan, see: http://visualstudiomagazine.com/articles/2008/07/01/return-double-values-in-excel.aspx

replace with #NUM on the side of Excel.

If python has just been added to the path, Windows needs to be restarted, even if the command prompt does already work with python.

Probably best to make wb = ActiveWorkbook() or wb = Workbook.active() as otherwise `wb = Workbook(r'path\to\file.xls') would have to have an additional Parameter which makes it clunky.

For now add a comment to the docs that path should always be fully qualified. When called from VBA, the current working directory is changed into the path of where the Python interpreter lives except the default installation is used (as otherwise, depending on the installation, a certain pywin dll isn't found) while the source file path is being appended. This means, that things like f = open('workfile', 'w') don't work anymore without specifying the fully qualified path for workfile.

Remains to be seen if we can make pywin32 reliably work for all installations without being in the Python root directory.

This is the same issue as #31 for MultiIndex Headers.

see: http://www.jkp-ads.com/articles/Excel2007TablesVBA.asp
and: http://www.techrepublic.com/blog/10-things/10-reasons-to-use-excels-table-object/

Some notes:

import xlwings as xw
wb = xw.books.active
r = wb.sheets[0].range('A1').api.list_object.name.get()
wb.sheets[0].range('A1').api.list_object.list_rows[2].range_object.delete_range()
wb.sheets[0].range('A1').api.list_object.list_rows[1].range_object.insert_into_range(shift=kw.shift_down)

I tried to send a pandas dataframe to excel using xlwings and the elements of the dataframe are getting truncated. Here is a toy example:

import pandas as pd
from xlwings import Workbook, Range

wb = Workbook()

mydata = {'00': [0.000000e+00, 1.133106e+08, 1.112194e+08], '01': [0.000000e+00, 1.125851e+08, 1.104678e+08], '02': [0.000000e+00, 1.118832e+08, 1.113757e+08]}
mymonth = [0, 1, 2]
mydf = pd.DataFrame(data=mydata, index=mymonth)

Range("A1").value = mydf

I'm using:

• python 2.7.6 32bits
• xlwings 0.1.0
• pandas 0.13.1
• numpy 1.8.0
• MS Excel 2007

TypeError: Objects of type 'numpy.int32' can not be converted to a COM VARIANT (but obtaining the buffer() of this object could).

Seems to be OK on >=1.8.0, not sure what the real problem is, but converting it using np.asscalar() works.

Otherwise, something like [None, None] causes an Error when written to Excel.

Correct Line 472 and possibly other ocurrencies (TODO: factor out the check for single cells).

# Simple Lists: Turn into list of lists
if isinstance(data, list) and (isinstance(data[0], (numbers.Number, string_types, time_types) or data[0] is None):

E.g. when the python string "01" gets written over to Excel, it's being automatically turned into a the number 1. The python string format on Excel should probably be (optionally) enforced.

Mimimize number of I/O operations:
E.g. don't do Range('A1').value = 'Header 1' and Range('A2').value = 'Header 2' but Range('A1').value = ['Header 1', 'Header 2']

Otherwise, things like len(my_array) don't work for a single element.

Currently, nan's are only handled (i.e. converted to a blank cell) if NumPy is installed. Since you can do:

float('nan')

It should also be handled using the builtin

math.isnan()

Using Enthought Canopy (which may be to blame, not sure though):

pywin32 installed properly, excel 2013 on windows 7

ImportError Traceback (most recent call last)
in ()
----> 1 from xlwings import Worbook, Range, Chart

C:\Users\porkypine\AppData\Local\Enthought\Canopy\User\lib\site-packages\xlwings__init__.py in ()
9 # Platform specific imports
10 if sys.platform.startswith('win'):
---> 11 import xlwings._xlwindows as xlplatform
12 else:
13 import xlwings._xlmac as xlplatform

C:\Users\porkypine\AppData\Local\Enthought\Canopy\User\lib\site-packages\xlwings_xlwindows.py in ()
3
4 import datetime as dt
----> 5 import win32api # needed as first import to find all dlls
6 import pywintypes
7 import pythoncom

ImportError: DLL load failed: The specified module could not be found.

because of assert microsecond == 0.

E.g. if the sheetname is misspelled, the current error is:

pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2147352565), None)

this can be decoded like this:

import win32api
win32api.FormatMessage(-2147352565)

Just following the example in the README. Changing proc.name() to proc.name fixes the problem. Python 2.7 (Anaconda) on OS X.

 In [1]: from xlwings import Workbook, Range, Chart
 In [2]: wb=Workbook()
 ---------------------------------------------------------------------------
 TypeError                                 Traceback (most recent call last)
 <ipython-input-2-fa7edd33f875> in <module>()
 ----> 1 wb=Workbook()

 /Users/pwang/anaconda/lib/python2.7/site-packages/xlwings/main.pyc in __init__(self, fullname)
      68         else:
      69             # Open Excel if necessary and create a new workbook
 ---> 70             self.xl_app, self.xl_workbook = xlplatform.new_workbook()
      71
      72         self.name = xlplatform.get_workbook_name(self.xl_workbook)

 /Users/pwang/anaconda/lib/python2.7/site-packages/xlwings/_xlmac.pyc in new_workbook()
      73
      74 def new_workbook():
 ---> 75     is_running = is_excel_running()
      76
      77     xl_app = app('Microsoft Excel')

 /Users/pwang/anaconda/lib/python2.7/site-packages/xlwings/_xlmac.pyc in is_excel_running()
      30 def is_excel_running():
      31     for proc in psutil.process_iter():
 ---> 32         if proc.name() == 'Microsoft Excel':
      33             return True
      34     return False

 TypeError: 'str' object is not callable

Currently, nan are returned as empty cells. It should probably be #N/A as default with optionally being turned into empty cells.

No argument should mean ActiveSheet

e.g.:

df_2 = pd.DataFrame([1, 3, 5, np.nan, 6, 8], columns=['col1'])

Should it really be wb.range('Sheet1', 'A1').value or would something like Range('Sheet1', 'A1', workbook=wb) be more Pythonic? The advantage would be that we could use classmethods for Chart.add() instead of Chart().add(), for example. Also, classes would always be classes (e.g. Range) and not turn into methods (wb.range).

this is due to the atexit call to reset the StatusBar - check if Excel is running first before calling it.

The standalone version (frozen executable) can be blocked to execute on very restrictive (corporate) environments.
Currently it just hangs doing nothing - it should fail with an error message.

This will be very handy for 1d arrays/lists. If you want to write out a 1d NumPy array in column orientation you currently have to do something like this for 1d arrays:

Range('A1').value = 1d_array[:,np.newaxis]

It should be easier like this:

Range('A1').vertical.value = 1d_array

A few samples:

• Form Controls (Cross platform):

sht.api.Shapes("Drop Down 1").ControlFormat.Value = 1

• ActiveX Controls (Windows only):

sht.api.OLEObjects("ComboBox1").Object.Value = 1

with win32timezone.TimeZoneInfo.utc()

The current output for this DataFrame:

   Foo     Bar     Baz
     A  B    C  D    E
0    0  1    2  3    4
1    0  1    2  3    4
2    0  1    2  3    4
3    0  1    2  3    4
4    0  1    2  3    4
5    0  1    2  3    4

is:

Optionally/default it should be like this: