数据结构教程#

本教程会简单过一遍xlwings读写数据的最常用的场景和默认的方式。如果需要深入了解如何通过 options 方法控制更多的细节，请查阅转换器及选项。

后面所有的示例代码都依赖下面的模块导入语句：

>>> import xlwings as xw

单个单元格#

根据单元格里面存储的是数字、字符串、空白还是日期，返回的python对象类型分别是 float, unicode, None 或 datetime :

>>> import datetime as dt
>>> sheet = xw.Book().sheets[0]
>>> sheet['A1'].value = 1
>>> sheet['A1'].value
1.0
>>> sheet['A2'].value = 'Hello'
>>> sheet['A2'].value
'Hello'
>>> sheet['A3'].value is None
True
>>> sheet['A4'].value = dt.datetime(2000, 1, 1)
>>> sheet['A4'].value
datetime.datetime(2000, 1, 1, 0, 0)

列表#

一维列表：在Excel中代表行或者列的区域，在Python中返回的都是一个列表。所以一旦把他们读入Python中，就是丢失行、列的方向信息。如果这个的确是个问题的话，下面一个知识点会说明如何保留这些信息：
```
>>> sheet = xw.Book().sheets[0]
>>> sheet['A1'].value = [[1],[2],[3],[4],[5]]  # Column orientation (nested list)
>>> sheet['A1:A5'].value
[1.0, 2.0, 3.0, 4.0, 5.0]
>>> sheet['A1'].value = [1, 2, 3, 4, 5]
>>> sheet['A1:E1'].value
[1.0, 2.0, 3.0, 4.0, 5.0]
```
要把单个的单元格强制转换为列表，可以这样:
```
>>> sheet['A1'].options(ndim=1).value
[1.0]
```
备注

To write a list in column orientation to Excel, use transpose: sheet.range('A1').options(transpose=True).value = [1,2,3,4]
二维列表：如果必须保留行列的方向信息，可以在区域操作中设置ndim 。这样会把区域信息返回为一个嵌套的列表（二维列表）：
```
>>> sheet['A1:A5'].options(ndim=2).value
[[1.0], [2.0], [3.0], [4.0], [5.0]]
>>> sheet['A1:E1'].options(ndim=2).value
[[1.0, 2.0, 3.0, 4.0, 5.0]]
```
二维区域会自动返回为嵌套列表。当把一个嵌套列表赋值给Excel区域的时候，只要明确目标区域的左上角单元格地址就行了。下面的例子也使用了索引方式把区域的值读会Python：
```
>>> sheet['A10'].value = [['Foo 1', 'Foo 2', 'Foo 3'], [10, 20, 30]]
>>> sheet.range((10,1),(11,3)).value
[['Foo 1', 'Foo 2', 'Foo 3'], [10.0, 20.0, 30.0]]
```

备注

Try to minimize the number of interactions with Excel. It is always more efficient to do sheet.range('A1').value = [[1,2],[3,4]] than sheet.range('A1').value = [1, 2] and sheet.range('A2').value = [3, 4].

区域扩展#

通过 expand 或者 options 方法中的 expand 关键词可以动态地获得区域范围。 expand 会直接给出扩展后的区域对象,而 options 方法在调用区域的是才计算区域扩展。下面例子说明了两种方法的不同之处：

>>> sheet = xw.Book().sheets[0]
>>> sheet['A1'].value = [[1,2], [3,4]]
>>> range1 = sheet['A1'].expand('table')  # or just .expand()
>>> range2 = sheet['A1'].options(expand='table')
>>> range1.value
[[1.0, 2.0], [3.0, 4.0]]
>>> range2.value
[[1.0, 2.0], [3.0, 4.0]]
>>> sheet['A3'].value = [5, 6]
>>> range1.value
[[1.0, 2.0], [3.0, 4.0]]
>>> range2.value
[[1.0, 2.0], [3.0, 4.0], [5.0, 6.0]]

上面例子中的 'table' 是向 'down' (下方)和 'right' (右边)扩展，其他可用选项可以分别只在行或者列方向上扩展。

备注

Using expand() together with a named Range as top left cell gives you a flexible setup in Excel: You can move around the table and change its size without having to adjust your code, e.g. by using something like sheet.range('NamedRange').expand().value.

NumPy数组#

NumPy数组和嵌套列表类似，只是表示空元素时用 nan 而不是 None 。如果要把一个区域读到数组中，需要在 options 中设置 convert=np.array ：

>>> import numpy as np
>>> sheet = xw.Book().sheets[0]
>>> sheet['A1'].value = np.eye(3)
>>> sheet['A1'].options(np.array, expand='table').value
array([[ 1.,  0.,  0.],
       [ 0.,  1.,  0.],
       [ 0.,  0.,  1.]])

Pandas数据表(DataFrame)#

>>> sheet = xw.Book().sheets[0]
>>> df = pd.DataFrame([[1.1, 2.2], [3.3, None]], columns=['one', 'two'])
>>> df
   one  two
0  1.1  2.2
1  3.3  NaN
>>> sheet['A1'].value = df
>>> sheet['A1:C3'].options(pd.DataFrame).value
   one  two
0  1.1  2.2
1  3.3  NaN
# options: work for reading and writing
>>> sheet['A5'].options(index=False).value = df
>>> sheet['A9'].options(index=False, header=False).value = df

Pandas的序列(Serie)#

>>> import pandas as pd
>>> import numpy as np
>>> sheet = xw.Book().sheets[0]
>>> s = pd.Series([1.1, 3.3, 5., np.nan, 6., 8.], name='myseries')
>>> s
0    1.1
1    3.3
2    5.0
3    NaN
4    6.0
5    8.0
Name: myseries, dtype: float64
>>> sheet['A1'].value = s
>>> sheet['A1:B7'].options(pd.Series).value
0    1.1
1    3.3
2    5.0
3    NaN
4    6.0
5    8.0
Name: myseries, dtype: float64

备注

You only need to specify the top left cell when writing a list, a NumPy array or a Pandas DataFrame to Excel, e.g.: sheet['A1'].value = np.eye(10)

Chunking: Read/Write big DataFrames etc.#

When you read and write from or to big ranges, you may have to chunk them or you will hit a timeout or a memory error. The ideal chunksize will depend on your system and size of the array, so you will have to try out a few different chunksizes to find one that works well:

import pandas as pd
import numpy as np
sheet = xw.Book().sheets[0]
data = np.arange(75_000 * 20).reshape(75_000, 20)
df = pd.DataFrame(data=data)
sheet['A1'].options(chunksize=10_000).value = df

And the same for reading:

# As DataFrame
df = sheet['A1'].expand().options(pd.DataFrame, chunksize=10_000).value
# As list of list
df = sheet['A1'].expand().options(chunksize=10_000).value