1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
|
from io import BytesIO
import numpy as np
from odf.opendocument import OpenDocumentSpreadsheet
from odf.table import (
Table,
TableCell,
TableRow,
)
from odf.text import P
from pandas import (
DataFrame,
ExcelWriter,
date_range,
read_excel,
)
from ..pandas_vb_common import tm
def _generate_dataframe():
N = 2000
C = 5
df = DataFrame(
np.random.randn(N, C),
columns=[f"float{i}" for i in range(C)],
index=date_range("20000101", periods=N, freq="H"),
)
df["object"] = tm.makeStringIndex(N)
return df
class WriteExcel:
params = ["openpyxl", "xlsxwriter", "xlwt"]
param_names = ["engine"]
def setup(self, engine):
self.df = _generate_dataframe()
def time_write_excel(self, engine):
bio = BytesIO()
bio.seek(0)
writer = ExcelWriter(bio, engine=engine)
self.df.to_excel(writer, sheet_name="Sheet1")
writer.save()
class WriteExcelStyled:
params = ["openpyxl", "xlsxwriter"]
param_names = ["engine"]
def setup(self, engine):
self.df = _generate_dataframe()
def time_write_excel_style(self, engine):
bio = BytesIO()
bio.seek(0)
writer = ExcelWriter(bio, engine=engine)
df_style = self.df.style
df_style.applymap(lambda x: "border: red 1px solid;")
df_style.applymap(lambda x: "color: blue")
df_style.applymap(lambda x: "border-color: green black", subset=["float1"])
df_style.to_excel(writer, sheet_name="Sheet1")
writer.save()
class ReadExcel:
params = ["xlrd", "openpyxl", "odf"]
param_names = ["engine"]
fname_excel = "spreadsheet.xlsx"
fname_excel_xls = "spreadsheet.xls"
fname_odf = "spreadsheet.ods"
def _create_odf(self):
doc = OpenDocumentSpreadsheet()
table = Table(name="Table1")
for row in self.df.values:
tr = TableRow()
for val in row:
tc = TableCell(valuetype="string")
tc.addElement(P(text=val))
tr.addElement(tc)
table.addElement(tr)
doc.spreadsheet.addElement(table)
doc.save(self.fname_odf)
def setup_cache(self):
self.df = _generate_dataframe()
self.df.to_excel(self.fname_excel, sheet_name="Sheet1")
self.df.to_excel(self.fname_excel_xls, sheet_name="Sheet1")
self._create_odf()
def time_read_excel(self, engine):
if engine == "xlrd":
fname = self.fname_excel_xls
elif engine == "odf":
fname = self.fname_odf
else:
fname = self.fname_excel
read_excel(fname, engine=engine)
class ReadExcelNRows(ReadExcel):
def time_read_excel(self, engine):
if engine == "xlrd":
fname = self.fname_excel_xls
elif engine == "odf":
fname = self.fname_odf
else:
fname = self.fname_excel
read_excel(fname, engine=engine, nrows=10)
from ..pandas_vb_common import setup # noqa: F401 isort:skip
|
