def test_merged_cells_xlsx(self):
book = xlrd.open_workbook(from_this_dir('merged_cells.xlsx'))
sheet1 = book.sheet_by_name('Sheet1')
expected = []
got = sheet1.merged_cells
self.assertEqual(expected, got)
sheet2 = book.sheet_by_name('Sheet2')
expected = [(0, 1, 0, 2)]
got = sheet2.merged_cells
self.assertEqual(expected, got)
sheet3 = book.sheet_by_name('Sheet3')
expected = [(0, 1, 0, 2), (0, 1, 2, 4), (1, 4, 0, 2), (1, 9, 2, 4)]
got = sheet3.merged_cells
self.assertEqual(expected, got)
sheet4 = book.sheet_by_name('Sheet4')
expected = [(0, 1, 0, 2), (2, 20, 0, 1), (1, 6, 2, 5)]
got = sheet4.merged_cells
self.assertEqual(expected, got)
python类open_workbook()的实例源码
def parse_dof_file(file_path):
""" Parse dof rolling sales xls file"""
book = xlrd.open_workbook(file_path)
sheet = book.sheet_by_index(0)
rows = sheet.get_rows()
# remove first 4 row
[next(rows) for x in range(4)]
# 5th row is the headers
headers = to_headers(next(rows))
for row in rows:
_row = list(map(cell_converter, row))
if len(list(filter(item_exists, _row))):
yield dict(zip(headers, _row))
def setup(startDatabase, endDatabase, maxElement):
global generation
global genSize
global data
global database
database = []
generation = []
Individual.bestChromosome = None
Individual.bestFitness = 0
for i in range (0, maxElement * maxElement):
database.append([])
#open the database file
data = xlrd.open_workbook("Complex Final database.xlsx").sheet_by_index(0)
createDatabase (startDatabase, endDatabase, maxElement)
#DEBUG ONLY
temp(maxElement)
#print(database)
#generation 0: all random lists of chromosomes
for i in range (0, genSize):
generation.append(Individual(randomChrom(maxElement), maxElement))
#print(database)
def extract(self, filename, **kwargs):
workbook = xlrd.open_workbook(filename)
sheets_name = workbook.sheet_names()
output = "\n"
for names in sheets_name:
worksheet = workbook.sheet_by_name(names)
num_rows = worksheet.nrows
num_cells = worksheet.ncols
for curr_row in range(num_rows):
row = worksheet.row(curr_row)
new_output = []
for index_col in xrange(num_cells):
value = worksheet.cell_value(curr_row, index_col)
if value:
if isinstance(value, (int, float)):
value = unicode(value)
new_output.append(value)
if new_output:
output += u' '.join(new_output) + u'\n'
return output
def convert(self, xls_filename):
xls_filename = _unicode_anyway(xls_filename);
try:
self._workbook = xlrd.open_workbook(xls_filename);
self._xls_filetime = os.path.getmtime(xls_filename);
self._xls_filename = xls_filename;
except:
raise Exception("Failed to load workbook: %s" % xls_filename);
self._sheet_names = self._workbook.sheet_names();
self._meta_tables = list();
if self._meta in self._sheet_names:
self._load_meta_sheet();
else:
self._load_meta_header();
for sheet_desc in self._meta_tables:
self._convert_sheet(sheet_desc);
self._tables.append(sheet_desc.table_name);
def get_workbooks(self):
"""
If the data to be processed is not stored in files or if
special parameters need to be passed to xlrd.open_workbook
then this method must be overriden.
Any implementation must return an iterable sequence of tuples.
The first element of which must be an xlrd.Book object and the
second must be the filename of the file from which the book
object came.
"""
for path in self.get_filepaths():
yield (
xlrd.open_workbook(
path,
pickleable=0,
formatting_info=1,
on_demand=True),
os.path.split(path)[1]
)
def excel():
fname = "..\\Case\\test.xlsx"
bk = xlrd.open_workbook(fname)
try:
sh = bk.sheet_by_name('Sheet1')
except:
print "%?????Sheet1?" % fname
# ????
nrows = sh.nrows
# ????
ncols = sh.ncols
print '?????%s ?????%s' % (nrows, ncols)
a = []
for i in range(0, nrows):
c = sh.row_values(i)
# if i == 0:
# continue
# else:
# c[0] = int(c[0])
# c[6] = int(c[6])
a.append(c)
return a
# print a
def readexcel(path):
workbook = xlrd.open_workbook(path)
sheets = workbook.sheet_names()
# ??????
for sname in sheets:
print(sname)
#?????sheet
worksheet = workbook.sheet_by_name(sheets[0])
#??sheet???????
print(worksheet.nrows)
print(worksheet.ncols)
#?????cell?
print(worksheet.cell(0,1).value)
#??????
print(worksheet.row(0)[0].value)
#??cell???
print(worksheet.row(0)[0].ctype)
#???????
for cell in worksheet.row(0):
print(cell.value)
#???????
for cell in worksheet.col(0):
if cell.value is not None and cell.value !='':
print(cell.value)
def read_sheets_from_xls(file_path):
workbook = xlrd.open_workbook(file_path)
sheets = []
for sheet in workbook.sheets():
if sheet.ncols <= 0:
continue
cells = []
for y in range(0, sheet.nrows):
# ??????
all_empty = True
for v in sheet.row_values(y):
if v != '':
all_empty = False
break
if all_empty:
continue
text = sheet.cell_value(y, 0)
# ?????
if isinstance(text, unicode) and text.startswith('//'):
continue
cells.append(sheet.row(y))
if len(cells) > 0:
sheets.append((sheet.name, cells))
return sheets
def readExcel(self, filename):
self.positions = []
wb = open_workbook(filename)
for sheet in wb.sheets():
number_of_rows = sheet.nrows
number_of_columns = sheet.ncols
rows = []
for row in range(0, number_of_rows):
values = []
for col in range(number_of_columns):
value = sheet.cell(row, col).value
try:
value.replace(' ', '')
except ValueError:
pass
finally:
values.append(value)
position = Position(*values)
self.positions.append(position)
#-----------------------------------------------------------------
# Read US stocks from text file (as saved in Outlook) -- OBSOLETE
#-----------------------------------------------------------------
def readExcel(self, filename):
self.positions = []
wb = open_workbook(filename)
for sheet in wb.sheets():
number_of_rows = sheet.nrows
number_of_columns = sheet.ncols
rows = []
for row in range(0, number_of_rows):
values = []
for col in range(number_of_columns):
value = sheet.cell(row, col).value
try:
value.replace(' ', '')
except ValueError:
pass
finally:
values.append(value)
position = Position(*values)
self.positions.append(position)
#-----------------------------------------------------------------
# Read US stocks from text file (as saved in Outlook) -- OBSOLETE
#-----------------------------------------------------------------
def importData(fileName):
"""
Main entry point - opens import data Excel sheet (assumed to be first
sheet) and processes each record, one at a time.
"""
global importSheet, importRow, headers
book = xlrd.open_workbook(fileName)
importSheet = book.sheet_by_index(0)
print "importing from %s" % importSheet.name
# get cell value types from first-non header data row (2nd), since headers are all of type text
for colnum in range(0, importSheet.ncols):
headers.append((importSheet.cell(0, colnum).value, importSheet.cell(1, colnum).ctype))
configErrorReporting(headers)
for importRow in range(1, importSheet.nrows):
record = {}
for colnum in range(0, importSheet.ncols):
if headers[colnum][1] == xlrd.XL_CELL_DATE:
dateTuple = xlrd.xldate_as_tuple(importSheet.cell(rowx=importRow, colx=colnum).value, book.datemode)
date = datetime.date(dateTuple[0], dateTuple[1], dateTuple[2])
# required format for xsd:Date type, for web service call
record[headers[colnum][0]] = date.isoformat()
else:
value = importSheet.cell_value(rowx=importRow, colx=colnum)
if isinstance(value, basestring):
record[headers[colnum][0]] = value.strip()
else:
record[headers[colnum][0]] = value
#print "%s: %s" % (type(record[headers[colnum]]), record[headers[colnum]])
record = handleAccountContact(record)
book.unload_sheet(importSheet.name)
def excel_to_db(excel_file):
"""
Asset add batch function
"""
try:
data = xlrd.open_workbook(filename=None, file_contents=excel_file.read())
except Exception, e:
return False
else:
table = data.sheets()[0]
rows = table.nrows
for row_num in range(1, rows):
row = table.row_values(row_num)
if row:
group_instance = []
ip, port, hostname, use_default_auth, username, password, group = row
if get_object(Asset, hostname=hostname):
continue
if isinstance(password, int) or isinstance(password, float):
password = unicode(int(password))
use_default_auth = 1 if use_default_auth == u'??' else 0
password_encode = CRYPTOR.encrypt(password) if password else ''
if hostname:
asset = Asset(ip=ip,
port=port,
hostname=hostname,
use_default_auth=use_default_auth,
username=username,
password=password_encode
)
asset.save()
group_list = group.split('/')
for group_name in group_list:
group = get_object(AssetGroup, name=group_name)
if group:
group_instance.append(group)
if group_instance:
asset.group = group_instance
asset.save()
return True
def execute(self, context, filepath, *sheet_readers):
workbook = xlrd.open_workbook(filepath)
return [reader(context, workbook) for reader in sheet_readers]
def load_from_excel(self, file, click_type='callback_data', default_click='default_blank_callback'):
buttons = []
wb = open_workbook(file, formatting_info=True)
sheet = wb.sheet_by_name("Sheet1")
print('Reading keyboard from:', file)
for col in range(sheet.ncols):
text = data = ''
buttons.append([])
for row in range(sheet.nrows):
cell = sheet.cell(row, col)
fmt = wb.xf_list[cell.xf_index]
border = fmt.border
has_bottom = bool(border.bottom_line_style)
if not has_bottom:
text = str(cell.value)
else:
data = str(cell.value)
if data and text:
buttons[col].append({'text': text, click_type: data})
else:
buttons[col].append({'text': data, click_type: default_click})
text = ''
data = ''
if not has_bottom and text:
raise ExcelNoBottomException('Cell({0},{1}) has no bottom border.'.format(row, col))
# Flip columns and rows
buttons = list(map(list, itertools.zip_longest(*buttons)))
buttons = [[button for button in row if button is not None] for row in buttons]
self.keyboard['inline_keyboard'] = buttons
def setWb(self):
sheet_names = list()
if os.path.isfile(self.reportName):
wb = copy(xlrd.open_workbook(self.reportName, formatting_info=1))
sheets = wb._Workbook__worksheets
for s in sheets:
sheet_names.append(s.get_name())
return wb, sheet_names
else:
return xlwt.Workbook(encoding='utf-8'), sheet_names
def read_excel_sheet(file=None, file_content=None, sheet_num=0, sheet_name=None, title_row=0, titles_list=None):
'''
??Excel?????Sheet???
:param file: Excel????
:param file_content: Excel????, ????????file or file_content???
:param sheet_num: ?????
:param sheet_name: ?????(???)
:param title_row: ??????????(?0??), ??????????
:param titles_list: ??????, ????????title_row??, ?????????
:return: list(dict) ??????????dict, ?title_row?titles_list???key
'''
if titles_list is None:
titles_list = []
try:
wb = xlrd.open_workbook(filename=file, file_contents=file_content)
except Exception as e:
raise e
sheet = wb.sheets()[sheet_num]
nrows = sheet.nrows # ??
ncols = sheet.ncols # ??
_console.debug('rows: %s, cols: %s' %(nrows, ncols))
if titles_list and len(titles_list) > 1:
titles = titles_list
else:
titles = sheet.row_values(title_row) # ?????
_console.debug(titles)
# ?????????
list = []
for row_num in range(title_row + 1, nrows):
_console.debug('row: %s' %(row_num,))
row = sheet.row_values(row_num)
if row:
obj = {}
for i, title in enumerate(titles):
obj[title] = row[i]
list.append(obj)
return list
def main():
weekday_sheets_file = xlrd.open_workbook(WEEKDAY_FILE_NAME).sheets()
weekend_sheets_file = xlrd.open_workbook(WEEKEND_FILE_NAME).sheets()
raw_weekday_sheets = extract_valid_sheets(weekday_sheets_file)
raw_weekend_sheets = extract_valid_sheets(weekend_sheets_file)
new_weekday_sheets = xlwt.Workbook()
new_weekend_sheets = xlwt.Workbook()
pole_file = open(POLE_COMPLETION)
poles = [p[:-1].split(',') for p in pole_file.readlines()]
create_sheet(raw_weekday_sheets, new_weekday_sheets, 'weekday', poles)
create_sheet(raw_weekend_sheets, new_weekend_sheets, 'weekend', poles)
new_weekday_sheets.save(NORM_WEEKDAY_FILE)
new_weekend_sheets.save(NORM_WEEKEND_FILE)
def do(self):
#app = wx.PySimpleApp(0)
wcd="Excel Files(*.xls)|*.xls|"
dir = "/home"
save_dlg = wx.FileDialog(self.parent, message='Choose File to be Imported', defaultDir=dir, defaultFile= '', wildcard=wcd, style=wx.OPEN)
if save_dlg.ShowModal() == wx.ID_OK:
path = save_dlg.GetPath()
self.book = open_workbook(path)
self.current_sheet=self.book.sheet_by_index(0)
self.rown=self.current_sheet.nrows
self.DB=db_operations()
self.write_to_db()
save_dlg.Destroy()
print "extd"
def __init__(self, filepath):
# ????excel???
if filepath.endswith('.xlsx') or filepath.endswith('.xls'):
if os.path.exists(filepath):
try:
# ??xlrd book??
self.fileHandle = xlrd.open_workbook(filepath)
except Exception as err:
logging.error("{} error: ????excel????! ????: {} ????: {}".format(
Operator.get_current_func(), filepath, str(err)))
if not self.fileHandle:
raise Exception('????excel????! ??????')