def write_excel(path, CaseName, OrderName, OrderId, Subscriber, status):
# Workbook is created
wb = Workbook()
#open excel
book = xlrd.open_workbook(path)
#make writable copy
wb = copy(book)
#Read first sheet
sheet1 = wb.get_sheet(0)
# add_sheet is used to create sheet.
#sheet1 = wb.add_sheet('Sheet 2')
row = Return_Row_Count(path)
print(row)
sheet1.write(row, 0, CaseName)
sheet1.write(row, 1, OrderName)
sheet1.write(row, 2, OrderId)
sheet1.write(row, 3, Subscriber)
sheet1.write(row, 4, status)
#wb.save('xlwt example.xlsx')
wb.save(path)
def save_to_excel(name_file, data):
try:
r = 0
try:
rb = xlrd.open_workbook(name_file, formatting_info=True)
r_sheet = rb.sheet_by_index(0)
r = r_sheet.nrows
except:
wb = Workbook()
sheet1 = wb.add_sheet('Sheet1')
wb.save(name_file)
print "create file"
save_to_excel(name_file, data)
return
wb = copy(rb)
sheet = wb.get_sheet(0)
# ------------------------
data = data.split('\r\n')
for i in data:
sheet.write(r, 0, i) # gi vao row, col
r = r + 1
#---------------------------
wb.save(name_file)
except Exception as e:
print e
def write_sheet(filename, j, box_list_left_load, extent_left,
box_list_right_load, extent_right, block, extent_block, b1,
b1_extent):
#print(box_list_left_load,box_list_right_load)
level_extent = [
box_list_left_load, extent_left, box_list_right_load, extent_right,
block, extent_block, b1, b1_extent
]
k = 0
if j == 0:
wb = Workbook()
sheet1 = wb.add_sheet('Sheet 1')
sheet1.write(j + 1, 0, filename)
sheet1.write(j, 0, 'Image_id')
sheet1.write(j, 1, 'level_left_wheel_path')
sheet1.write(j, 2, 'extent_left_wheel_path')
sheet1.write(j, 3, 'level_rigght_wheel_path')
sheet1.write(j, 4, 'extent_right_wheel_path')
sheet1.write(j, 5, 'level_block')
sheet1.write(j, 6, 'extent_block')
for i in range(len(level_extent)):
if (i <= 3 and k != 9):
sheet1.write(j + 1, i + 1, level_extent[i])
else:
if k != 9:
if extent_block == 0:
sheet1.write(j + 1, 5, level_extent[6])
sheet1.write(j + 1, 6, level_extent[7])
else:
sheet1.write(j + 1, 5, level_extent[4])
sheet1.write(j + 1, 6, level_extent[5])
k = 9
else:
book = xlrd.open_workbook('/content/markings2.xls')
wb = copy(book)
sheet1 = wb.get_sheet('Sheet 1')
sheet1.write(j + 1, 0, filename)
for i in range(len(level_extent)):
if (i <= 3 and k != 9):
sheet1.write(j + 1, i + 1, level_extent[i])
else:
if k != 9:
if extent_block == 0:
sheet1.write(j + 1, 5, level_extent[6])
sheet1.write(j + 1, 6, level_extent[7])
else:
sheet1.write(j + 1, 5, level_extent[4])
sheet1.write(j + 1, 6, level_extent[5])
k = 9
wb.save('/content/markings2.xls')
def write_to_excel(result_list, row):
if excel_file_exist == 0:
file = Workbook(encoding='utf-8')
table = file.add_sheet('汇总')
else:
workbook = xlrd.open_workbook(target_path)
file = copy(workbook)
table = file.get_sheet(0)
for j in range(result_list.__len__()):
table.write(row, j, result_list[j])
file.save(target_path)
def WriteIntoExcel(path, sheet_name, *args):
wb = Workbook()
book = xlrd.open_workbook(path)
wb = copy(book)
sheet1 = wb.get_sheet(0)
row = Return_Row_Count(path, sheet_name)
col = Return_Column_Count(path, sheet_name)
print(row)
for colc in range(col):
sheet1.write(row, colc, args[colc])
wb.save(path)
def excel_write():
book = Workbook()
sheet1 = book.add_sheet('Sheet1')
book.add_sheet('Sheet2')
sheet1.write(0, 0, 'A1')
sheet1.write(0, 1, 'B1')
row1 = sheet1.row(1)
row1.write(0, 'A2')
row1.write(1, 'B2')
sheet1.col(0).width = 10000
sheet2 = book.get_sheet(1)
sheet2.row(0).write(0, 'Sheet 2 A1')
sheet2.row(0).write(1, 'Sheet 2 B1')
sheet2.flush_row_data()
sheet2.write(1, 0, 'Sheet 2 A3')
sheet2.col(0).width = 5000
sheet2.col(0).hidden = True
book.save('sample.xls')
book.save(TemporaryFile())
def write_output_xls(path, filename):
book = Workbook()
sheet1 = book.add_sheet('Sheet 1')
book.add_sheet('Sheet 2')
sheet1.write(0, 0, 'A1')
sheet1.write(0, 1, 'B1')
row1 = sheet1.row(1)
row1.write(0, 'A2')
row1.write(1, 'B2')
sheet1.col(0).width = 10000
sheet2 = book.get_sheet(1)
sheet2.row(0).write(0, 'Sheet 2 A1')
sheet2.row(0).write(1, 'Sheet 2 B1')
sheet2.flush_row_data()
sheet2.write(1, 0, 'Sheet 2 A3')
sheet2.col(0).width = 5000
sheet2.col(0).hidden = True
book.save('simple.xls')
book.save(TemporaryFile())
def createFile(fileCreated,usrInfoFile,numRows):
"""Create an .xls file formated to take data from anod tests.
fileCreated -- The name of file to be created
userInfoFile -- .xls File that has user and test information
-- Create with usrData.py->createtemplate(file) and then edit
numRows -- Number of rows of data in usrInfoFile
The first sheet of the fileCreated will be identical to the first sheet of usrInfoFile.
"""
# Initialize file
wbook = Workbook() # wbook - write book
wbook.add_sheet('test_info')
wbook.add_sheet('Ch1_data')
wbook.add_sheet('Ch2_data')
# copy 1st sheet of usrInfoFile to 1st sheet of fileCreated
rbook = open_workbook(usrInfoFile) # rbook - read book
rsheet = rbook.sheet_by_index(0)
for col in range(2):
for row in range(numRows):
num = rsheet.cell(row,col).value
wbook.get_sheet(0).write(row,col,num)
# Pre-format data-sheets
col = 0
row = 0
wbook.get_sheet(1).write(row,col,data[0])
wbook.get_sheet(2).write(row,col,data[0])
num = (len(data)+1)/2
for col in range(1,num):
wbook.get_sheet(1).write(row,col,data[col])
wbook.get_sheet(2).write(row,col,data[col+4])
# save file
wbook.save(fileCreated)
wbook.save(TemporaryFile())
def write_total_case_result(task, result):
if not path.exists(RESULT_DIR):
print '===create result dir==='
os.mkdir(RESULT_DIR)
from_row = 0
book = None
if path.exists(TOTAL_RESULT_FILE):
book = open_workbook(TOTAL_RESULT_FILE)
sheet = book.sheet_by_index(0)
from_row = sheet.nrows
wb = None
if book:
wb = copy(book)
else:
wb = Workbook()
ws = None
try:
ws = wb.get_sheet(0)
except Exception:
pass
if not ws:
ws = wb.add_sheet('total_result')
if from_row == 0:
ws.row(0).write(0, 'from apk')
ws.row(0).write(1, 'from jetpack')
ws.row(0).write(2, 'to apk')
ws.row(0).write(3, 'type')
ws.row(0).write(4, 'result')
from_row += 1
ws.row(from_row).write(0, task.from_file_name)
ws.row(from_row).write(1, task.from_jetpack_string)
ws.row(from_row).write(2, task.to_file_name)
ws.row(from_row).write(3, task.task_type)
ws.row(from_row).write(4, result)
wb.save(TOTAL_RESULT_FILE)
class XLSWriter:
DEFAULT_SHEET_NAME = "Hoja 1"
def __init__(self, input_filename=None):
self.__input_filename = input_filename
self.__rd_book = None
if not input_filename:
self.__wt_book = Workbook()
self.__wt_book.add_sheet(XLSWriter.DEFAULT_SHEET_NAME)
else:
self.__wt_book = copy(open_workbook(input_filename, formatting_info=True))
self.active_sheet = self.__wt_book.get_active_sheet()
def __get_row_index(self, col_index, col_value):
"""
Get the row index where col_value is found at the given column index
"""
if not self.__input_filename:
raise Exception("No filename given, i.e. no data to search for")
if not self.__rd_book:
self.__rd_book = open_workbook(self.__input_filename, on_demand=True)
row_index = None
sheet = self.__rd_book.get_sheet(self.active_sheet)
for i in range(sheet.nrows):
if sheet.cell_value(i, col_index) == col_value:
row = i
break
if not row_index:
raise Exception("Row to replace not found!")
return row_index
def append(self, cols):
"""
Append a new row containing cols data at the end of the document.
"""
row_count = len(self.__wt_book.get_sheet(self.active_sheet).get_rows())
self.write(row_count, cols)
def replace(self, col_index, col_value, cols):
"""
Replace the contents of the row where col_value is found at the given
column index.
"""
self.write(self.__get_row_index(col_index, col_value), cols)
def write(self, row_index, cols):
"""
Write the data contained in cols in the row with index row_index
and replace the data found there.
"""
sheet = self.__wt_book.get_sheet(self.active_sheet)
for i in range(len(cols)):
sheet.write(row_index, i, cols[i])
def save(self, output_filename=None):
"""
Serialize the workbook to the given output_filename or
to input_filename if not given
"""
if not output_filename:
self.__wt_book.save(self.__input_filename)
else:
self.__wt_book.save(output_filename)
from tempfile import TemporaryFile
from xlwt import Workbook
book = Workbook()
sheet1 = book.add_sheet('Sheet 1')
book.add_sheet('Sheet 2')
sheet1.write(0, 0, 'A1')
sheet1.write(0, 1, 'B1')
row1 = sheet1.row(1)
row1.write(0, 'A2')
row1.write(1, 'B2')
sheet1.col(0).width = 1000
sheet2 = book.get_sheet(1)
sheet2.row(0).write(0, 'Sheet 2 A1')
sheet2.row(0).write(1, 'Sheet 2 B1')
sheet2.flush_row_data()
sheet2.write(1, 0, 'Sheet 2 A3')
sheet2.col(0).width = 5000
sheet2.col(0).hidden = True
book.save('simple.xls')
book.save(TemporaryFile())
cpy_sheet.write(count, 1, item_name.text)
#tt defines the number of times crawler updates
tt = 0
while True:
url = 'https://www.prothomalo.com/'
source_code = requests.get(url)
plain_text = source_code.text
soup2 = bs4.BeautifulSoup(plain_text)
for link in soup2.findAll('a', {'class': 'link_overlay'}):
rb = xlrd.open_workbook('Prothom_Alo.xls')
#Copy file for writing operation
wb = copy(rb)
#sheet for writing operation
cpy_sheet = wb.get_sheet(0)
#sheet for reading operation
w_sheet = rb.sheet_by_index(0)
href = "https://www.prothomalo.com" + link.get('href')
#function to get to the page containing headline along with the news
get_single_item_data(href)
#save as the same file after each news's extraction
wb.save('Prothom_Alo.xls')
tt = tt + 1
print(tt)
clk = 0
#clock to update the system after each 120 seconds or 2 minutes
while clk < 120:
time.sleep(1)
clk = clk + 1
class ExcelFile:
def __init__(self, file_path, header_format, data_format):
self._file_path = file_path
self._header_style0 = easyxf('borders: left thick;' + header_format)
self._header_style1 = easyxf(header_format)
self._data_style0 = easyxf('borders: left thick;' + data_format)
self._data_style1 = easyxf(data_format)
self._date_style0 = easyxf('borders: right thin, left thick; alignment: horizontal left, wrap true;', num_format_str='DD-MM-YYYY')
self._date_style1 = easyxf('borders: right thin; alignment: horizontal left, wrap true;', num_format_str='DD-MM-YYYY')
self._empty_cell_style0 = easyxf('borders: right thin, left thick;')
self._empty_cell_style1 = easyxf('borders: right thin;')
self._last_row_style0 = easyxf('borders: right thin, bottom thin, left thick')
self._last_row_style1 = easyxf('borders: right thin, bottom thin')
self._workbook = Workbook()
self._sheets = [] # contains the sheet objects
self._current_sheet = None # current sheet object of class 'Workbook'
self._my_sheet = None # current sheet object of class 'MySheet'
# add a new sheet in the workbook
def add_sheet(self, sheet_name):
self._current_sheet = self._workbook.add_sheet(sheet_name, cell_overwrite_ok = True)
self._current_sheet.show_grid = 0 # removes all the grid lines in excel
self._my_sheet = MySheet(sheet_name)
self._sheets.append(self._my_sheet)
# set a sheet as current sheet
def set_sheet(self, sheet_name):
index = 0 # integer value representing each sheet
flag = False
for sheet in self._sheets:
if sheet._name == sheet_name:
flag = True
self._my_sheet = sheet
break
index += 1
if flag == True:
self._current_sheet = self._workbook.get_sheet(index)
elif flag == False:
raise ValueError('Invalid sheet name.')
# set a new header format into all sheets
def set_header_format(self, header_format):
self._header_style0 = easyxf('borders: left thick;' + header_format)
self._header_style1 = easyxf(header_format)
# add a new format into a column of current sheet
def set_data_format(self, header_name, column_format):
column_no = self._check_column_is_name(header_name)
if column_no != None:
if column_format == 'date':
self._my_sheet._data_format[column_no] = column_format
elif column_no == 0:
self._my_sheet._data_format[column_no] = easyxf('borders: right thin, left thick; alignment: horizontal left, wrap true;' + column_format)
else:
self._my_sheet._data_format[column_no] = easyxf('borders: right thin; alignment: horizontal left, wrap true;' + column_format)
else:
raise ValueError('Invalid header name')
# write a single header into current sheet
def write_header(self, column_no, header_name):
if column_no == 0:
style = self._header_style0
else:
style = self._header_style1
self._current_sheet.write(0, column_no, header_name, style)
self._my_sheet._headers[column_no] = header_name
# write a list of headers into current sheet
def write_headers(self, headers):
for header in headers:
self.write_header(headers.index(header), header)
# write data into a single cell of current sheet
def write_data(self, row_no, column, data, column_is_name = None):
# If 'column_is_name' argument is True then 'column' argument contains header-name
# If 'column_is_name' argument is False then 'column' argument contains column-number
column_no = None
if column_is_name == True:
column_no = self._check_column_is_name(column)
elif column_is_name == False:
column_no = self._check_column_is_number(column)
elif isinstance(column, int):
column_no = self._check_column_is_name(column)
if column_no != None:
raise ConflictError(
"""The 'column' argument in function write_data() creates a conflict.
Set 'column_is_name' argument as 'True' if 'column' argument is the header-name or
set 'column_is_name' argument as 'False' if 'column' argument is the column-number"""
)
else:
column_no = self._check_column_is_number(column)
else:
column_no = self._check_column_is_name(column)
if column_no == None:
raise ValueError('Invalid header name or column number.')
# set style in empty cells
if row_no not in self._my_sheet._is_row_empty:
self._set_empty_cell_style(row_no)
self._my_sheet._is_row_empty.append(row_no)
# select the appropriate style and write data
column_format = self._my_sheet._data_format.get(column_no)
style = self._set_data_style(column_no, column_format)
self._current_sheet.write(row_no, column_no, data, style)
# set style for data cells
def _set_data_style(self, column_no, column_format):
if column_format == None:
if column_no == 0:
style = self._data_style0
else:
style = self._data_style1
elif column_format == 'date':
self._current_sheet.col(column_no).width = 256 * 12 # 12 spaces for date cell
if column_no == 0:
style = self._date_style0
else:
style = self._date_style1
else:
style = column_format
return style
# set style into empty cells of a row
def _set_empty_cell_style(self, row_no):
for column_no in range(len(self._my_sheet._headers)):
if column_no == 0:
self._current_sheet.write(row_no, column_no, None, self._empty_cell_style0)
else:
self._current_sheet.write(row_no, column_no, None, self._empty_cell_style1)
# check if the column given is header name
def _check_column_is_name(self, column):
column_no = None
if column in self._my_sheet._headers.values():
column_no = [key for key, value in self._my_sheet._headers.iteritems() if value == column][0]
return column_no
# check if the column given is column number
def _check_column_is_number(self, column):
column_no = None
if column in self._my_sheet._headers:
column_no = column
return column_no
# write data into next available row of current sheet
def write_row(self, row_data):
if len(self._my_sheet._headers) < len(row_data):
raise ExcessDataError("The argument 'row_data' in function write_row() contains more data than the number of headers")
# set style in empty cells
row_no = self._current_sheet.last_used_row + 1
if row_no not in self._my_sheet._is_row_empty:
self._set_empty_cell_style(row_no)
self._my_sheet._is_row_empty.append(row_no)
row = self._current_sheet.row(row_no)
for value in row_data:
column_no = row_data.index(value)
column_format = self._my_sheet._data_format.get(column_no)
style = self._set_data_style(column_no, column_format)
row.write(column_no, value, style)
# call this function at the end to save the excel file
def save(self):
self._set_last_row_style()
self._workbook.save(self._file_path)
# write last row into all sheets to complete the styling
def _set_last_row_style(self):
index = 0
for sheet in self._sheets:
current_sheet = self._workbook.get_sheet(index)
index += 1
row = current_sheet.row(current_sheet.last_used_row + 1)
for i in range(len(sheet._headers)):
if i == 0:
row.write(i, None, self._last_row_style0)
else:
row.write(i, None, self._last_row_style1)
ws.col(2).level = 1
ws.col(3).level = 2
ws.col(4).level = 1
ws.col(5).level = 2
w.save('outlines.xls')
########NEW FILE########
__FILENAME__ = overwriting
from xlwt import Workbook
book = Workbook()
sheet1 = book.add_sheet('Sheet 1', cell_overwrite_ok=True)
sheet1.write(0, 0, 'original')
sheet = book.get_sheet(0)
sheet.write(0, 0, 'new')
sheet2 = book.add_sheet('Sheet 2')
sheet2.write(0, 0, 'original')
sheet2.write(0, 0, 'new')
########NEW FILE########
__FILENAME__ = panes
from xlwt import Workbook
from xlwt.Utils import rowcol_to_cell
w = Workbook()
sheet = w.add_sheet('Freeze')
sheet.panes_frozen = True
sheet.remove_splits = True
from tempfile import TemporaryFile
from xlwt import Workbook
book = Workbook()
sheet1 = book.add_sheet('Sheet 1')
book.add_sheet('Sheet 2')
sheet1.write(0, 0, 'A1')
sheet1.write(0, 1, 'B1')
row1 = sheet1.row(1)
row1.write(0, 'A2')
row1.write(1, 'B2')
sheet1.col(0).width = 10000
sheet2 = book.get_sheet(1)
sheet2.row(0).write(0, 'Sheet 2 A1')
sheet2.row(0).write(1, 'Sheet 2 B1')
sheet2.flush_row_data()
sheet2.write(1, 0, 'Sheet 2 A3')
sheet2.col(0).width = 5000
sheet2.col(0).hidden = True
book.save('simple.xls')
book.save(TemporaryFile())
def _data_to_xls(statDict: dict, resultDict: dict, filePath: str,
totalRTime: str, logger):
"""
Function to prepare a spreadsheet using the data for
results.
Args:
statDict (dict): The dict containing the test run numerical stats.
resultDict (dict): The dict containing the test result metadata.
filePath (str): File path of spreadsheet.
totalRTime (str): Total runtime of the framework.
"""
# Create mapping for the keys to description to be put in the sheet.
rMap = [{
'dCount': 'Disruptive Tests'
}, {
'ndCount': 'Non Disruptive Tests'
}, {
'dSkipCount': 'Disruptive Tests Skipped'
}, {
'ndSkipCount': 'Non Disruptive Tests Skipped'
}, {
'runCount': 'Tests Ran'
}, {
'skipCount': 'Tests Skipped'
}, {
'totalCount': 'Total Tests'
}, {
'dPass': '******'
}, {
'ndPass': '******'
}, {
'Pass': '******'
}]
topicList = [
'Test Name', 'Nature', 'Volume Type', 'Result', 'Time (hh:mm:ss)',
'Skip Reason'
]
# Create a workbook.
wb = Workbook()
# Populate the stats
style = xlwt.easyxf('font: bold 1')
for component in statDict:
row = 0
tR = wb.add_sheet(component)
tDict = statDict[component]
for subDict in rMap:
for key, val in subDict.items():
tR.write(row, 0, val, style)
tR.write(row, 1, tDict[key])
row += 1
# Writing total runtime in the Total sheet.
tR = wb.get_sheet('Total')
timeVal = _time_rollover_conversion(totalRTime, True)
tR.write(row, 0, "Total Time", style)
tR.write(row, 1, timeVal)
rowDiff = row + 2
# Populating the test data to the sheets.
for component in resultDict:
tR = wb.get_sheet(component)
row = rowDiff
# Add the topics.
col = 0
for topic in topicList:
tR.write(row, col, topic, style)
col += 1
row += 1
for nature in resultDict[component]:
testsDict = resultDict[component][nature]
for test in testsDict:
for volType in testsDict[test]:
volData = testsDict[test][volType]
tR.write(row, 0, test)
tR.write(row, 1, nature)
tR.write(row, 2, volType)
tR.write(row, 3, volData['testResult'])
timeVal = _time_rollover_conversion(volData['timeTaken'])
tR.write(row, 4, timeVal)
tR.write(row, 5, volData['skipReason'])
row += 1
# Push the changes to the file.
try:
wb.save(filePath)
except Exception as error:
tb = traceback.format_exc()
logger.error(f"XLS Save error : {error}")
logger.error(f"XLS Save traceback : {tb}")
from xlutils.copy import copy
from docx import Document
import re
import requests
import os.path
#找到資料夾中所有的.docx檔
file = glob.glob("*.docx")
for d in range(0,len(file)):
if os.path.exists("record.xls")!= True:
wb = Workbook()
sheet1 = wb.add_sheet('Sheet1')
else:
rb = open_workbook('record.xls')
wb = copy(rb)
sheet1 = wb.get_sheet(0)
i = row+d-1
document = Document(file[d])
docText = '\n'.join([paragraph.text.encode('utf-8') for paragraph in document.paragraphs])
n = docText.count('@')+1
sp = re.split(r'@*',docText)
#item1
for j in range(0,n):
if sp[j] == "item1":
item1 = sp[j+1]
if "item1" not in sp:
item1 = ""
if len(item1.split()) == 0:
item1 = ""
class XLSWriter:
DEFAULT_SHEET_NAME = "movimientos"
def __init__(self, input_filename=None):
self.__input_filename = input_filename
self.__rd_book = None
if not input_filename:
self.__wt_book = Workbook()
self.__wt_book.add_sheet(XLSWriter.DEFAULT_SHEET_NAME)
else:
self.__wt_book = copy(open_workbook(input_filename))
self.active_sheet = self.__wt_book.get_active_sheet()
def __get_row_index(self, col_index, col_value):
"""
Get the row index where col_value is found at the given column index
"""
if not self.__input_filename:
raise Exception("No filename given, i.e. no data to search for")
if not self.__rd_book:
self.__rd_book = open_workbook(self.__input_filename, on_demand=True)
row_index = None
sheet = self.__rd_book.get_sheet(self.active_sheet)
for i in range(sheet.nrows):
if sheet.cell_value(i, col_index) == col_value:
row = i
break
if not row_index:
raise Exception("Row to replace not found!")
return row_index
def append(self, cols):
"""
Append a new row containing cols data at the end of the document.
"""
row_count = len(self.__wt_book.get_sheet(self.active_sheet).get_rows())
self.write(row_count, cols)
def replace(self, col_index, col_value, cols):
"""
Replace the contents of the row where col_value is found at the given
column index.
"""
self.write(self.__get_row_index(col_index, col_value), cols)
def write(self, row_index, cols):
"""
Write the data contained in cols in the row with index row_index
and replace the data found there.
"""
sheet = self.__wt_book.get_sheet(self.active_sheet)
for i in range(len(cols)):
sheet.write(row_index, i, cols[i])
def save(self, output_filename=None):
"""
Serialize the workbook to the given output_filename or
to input_filename if not given
"""
if not output_filename:
self.__wt_book.save(self.__input_filename)
else:
self.__wt_book.save(output_filename)
def collectsf():
#setting excel paramaters
clearing()
print("cleared and moving on")
wb = Workbook()
sheet = wb.add_sheet("sheet 1")
rb = xlrd.open_workbook("tester.xls", formatting_info=True)
r_sheet = rb.sheet_by_index(0)
r = r_sheet.nrows
wb = copy(rb)
i = 0
# setting path of recorded data folder
print("setting path")
oldpath = os.getcwd()
try:
os.chdir("wav")
print("Directory Changed")
except:
print("could not change directory")
path = os.getcwd()
#iterating through files in wav
for filename in glob.glob(os.path.join(path, '*.wav')):
# reading the wav files stored at the database of sounds
# reading the wav files stored at the database of sounds
try:
i += 1
frate, data = wavfile.read(filename)
#fname = filename.replace(path , "")
#looping through the single wav file
#for i in range(len(data)):
#storing the data in a numpy array as a float datatype to extract sound features
#data = np.array(data[i],dtype=float)
data = np.array(data)
freq = frequency(frate, data)
#extracting sound features
#editing kurtosis output
kurt = ''
kurt = str(kurtosis(data))
kurt = kurt.replace('[', "")
kurt = kurt[0:9]
#editing skewness output
skk = ''
skk = str(skew(data))
skk = skk.replace('[', "")
skk = skk[0:9]
#array of data = [ mean , variance , minimum , maximum , median , kurtosis , Skewness , Frequency ]
arrofdata = 0
arrofdata = [
np.mean(data),
np.var(data),
str(np.min(data)),
str(np.max(data)),
np.median(data), kurt, skk, freq
]
print("data stored in array")
#importing data into excel sheet
sheet = wb.get_sheet(0)
sheet.write(r, 0, "NA")
sheet.write(r, 1, arrofdata[0])
sheet.write(r, 2, arrofdata[1])
sheet.write(r, 3, arrofdata[2])
sheet.write(r, 4, arrofdata[3])
sheet.write(r, 5, arrofdata[4])
sheet.write(r, 6, arrofdata[5])
sheet.write(r, 7, arrofdata[6])
sheet.write(r, 8, arrofdata[7])
r = r + 1
except:
print("could not read so i passed it")
pass
try:
os.chdir(oldpath)
print("dir is here: " + os.getcwd())
print("Directory Changed")
except:
print("coudl not change directory")
print("saved")
wb.save("tester.xls")
return i
def create_excel():
from tempfile import TemporaryFile
from xlwt import Workbook
from xlwt import *
book = Workbook()
sheet1 = book.add_sheet("Sheet 1")
book.add_sheet("Sheet 2")
sheet1.write(0, 0, "نام شرکت")
sheet1.write(0, 1, "تاریخ خرید")
sheet1.write(0, 2, "ضخامت")
sheet1.write(0, 3, "عرض")
sheet1.write(0, 4, "طول")
sheet1.write(0, 5, "میزان کیلوگرم")
sheet1.write(0, 6, "ضخامت قلع")
sheet1.write(0, 7, "شکل ورق")
rows = py_db.executeAndReturnRows(
"""
SELECT
(SELECT `sk_link_to_company` FROM `tbl_sale_kharidha` WHERE `sk_id`=`skd_link_to_kharid_ha`) AS kha,
`skd_date_kharid`,
`skd_zekhamate_varagh`,
`skd_arz`,
`skd_tool`,
`skd_mizan`,
`skd_zekhamte_ghal`,
`skd_shekle_varagh`
FROM `tbl_sale_kharidha_details`
ORDER BY kha, skd_date_kharid
"""
)
i = 1
for row in rows:
row1 = sheet1.row(i)
row1.write(0, row[0].__str__())
row1.write(1, row[1].__str__())
row1.write(2, Formula("VALUE(" + row[2].__str__() + ")"))
row1.write(3, Formula("VALUE(" + row[3].__str__() + ")"))
row1.write(4, Formula("VALUE(" + row[4].__str__() + ")"))
row1.write(5, Formula("VALUE(" + row[5].__str__() + ")"))
row1.write(6, Formula("VALUE(" + row[6].__str__() + ")"))
row1.write(7, Formula("VALUE(" + row[7].__str__() + ")"))
i += 1
sheet1.col(0).width = 10000
sheet2 = book.get_sheet(1)
sheet2.row(0).write(0, "Sheet 2 A1")
sheet2.row(0).write(1, "Sheet 2 B1")
sheet2.flush_row_data()
sheet2.write(1, 0, "Sheet 2 A3")
sheet2.col(0).width = 5000
sheet2.col(0).hidden = True
book.save("simple.xls")
book.save(TemporaryFile())
print "Content-Type: text/html"
print
print "ok"
def create_template(self):
try:
open_workbook(self.template_path)
except FileNotFoundError:
# 文件夹下无模板文件，直接创建然后导入数据
wb = Workbook(encoding='ascii')
ws = wb.add_sheet("1")
style1 = XFStyle()
font1 = Font()
font1.bold = True
style1.font = font1
ws.write(0, 0, OLD_FILE_NAME, style1)
ws.write(0, 1, NEW_FILE_NAME, style1)
style2 = XFStyle()
font2 = Font()
font2.bold = True
font2.colour_index = 2
style2.font = font2
ws.write(1, 3, TEMPLATE_HINT, style2)
new_row_id = 1
for i in range(len(self.table_name_list0)):
if i not in self.disable_pos_list:
ws.write(new_row_id, 0, self.table_name_list0[i])
new_row_id += 1
wb.save(self.template_path)
return
# 文件夹下已经存在模板文件
# 暂时不检查完整性
rb = open_workbook(self.template_path, formatting_info=True)
r_sheet = rb.sheet_by_index(0)
wb = copy(rb)
sheet = wb.get_sheet(0)
def update_old_name(old_name_list, disable_pos_list):
needed_add_name_list = []
old_name_index = 0 # 旧文件名列序号
name0_list = r_sheet.col_values(old_name_index,
start_rowx=1,
end_rowx=None)
for i in range(len(old_name_list)):
if i not in disable_pos_list:
if old_name_list[i] not in name0_list:
needed_add_name_list.append(old_name_list[i])
if len(needed_add_name_list) == 0:
print("没有文件名需要添加到模板文件中，或者所有文件名已经添加")
return
n_row = r_sheet.nrows
print("原模板文件中已经有" + str(n_row) + "行")
for name in needed_add_name_list:
print("将文件名" + name + "添加到模板文件中")
sheet.write(n_row, old_name_index, name)
n_row += 1
update_old_name(self.table_name_list0, self.disable_pos_list)
try:
wb.save(self.template_path)
except PermissionError:
messagebox.showwarning("模板文件异常", "请关闭文件夹下的模板文件后再重新导入")
return
messagebox.showinfo("一切正常", "文件导入成功，模板文件已生成。\n请打开对应文件夹的模板文件编辑新文件名")
ws.col(2).level = 1
ws.col(3).level = 2
ws.col(4).level = 1
ws.col(5).level = 2
w.save('outlines.xls')
########NEW FILE########
__FILENAME__ = overwriting
from xlwt import Workbook
book = Workbook()
sheet1 = book.add_sheet('Sheet 1',cell_overwrite_ok=True)
sheet1.write(0,0,'original')
sheet = book.get_sheet(0)
sheet.write(0,0,'new')
sheet2 = book.add_sheet('Sheet 2')
sheet2.write(0,0,'original')
sheet2.write(0,0,'new')
########NEW FILE########
__FILENAME__ = panes
from xlwt import Workbook
from xlwt.Utils import rowcol_to_cell
w = Workbook()
sheet = w.add_sheet('Freeze')
sheet.panes_frozen = True
sheet.remove_splits = True
#!/usr/bin/env python
from xlwt import Workbook
book = Workbook()
sheet1 = book.add_sheet("Sheet 1", cell_overwrite_ok=True)
sheet1.write(0, 0, "original")
sheet = book.get_sheet(0)
sheet.write(0, 0, "new")
sheet2 = book.add_sheet("Sheet 2")
sheet2.write(0, 0, "original")
#sheet2.write(0, 0, "new")
book.save("my.xls")
class XlsWriter:
link_style = easyxf("""
font:
height 160,
colour_index blue;
""")
normal_style = easyxf("""
alignment:
wrap yes;
""")
header_style = easyxf("""
font:
colour_index white;
pattern:
back_colour gray50,
pattern thick_forward_diag;
alignment:
wrap yes;
""")
title_style = easyxf("""
font:
height 250,
bold yes;
""")
subtitle_style = easyxf("""
font:
height 200,
bold yes;
""")
header_roate_style = easyxf("""
font:
colour_index white;
pattern:
back_colour gray50,
pattern thick_forward_diag;
alignment:
rota 90,
wrap yes;
""")
def __init__(self):
self.book = Workbook(encoding='utf-8')
self.sheet_inf = []
def get_response(self, filename):
self.response = HttpResponse(mimetype='application/excel')
self.response['Content-Disposition'] = 'attachment; filename=%s' % filename
self.book.save(self.response)
return self.response
def save(self, path):
self.book.save(path)
def add_sheet(self, name, add_title=True):
def _short_str(name):
l = len(name)
if l > 31:
name = name[:13]+' ... '+name[-13:]
#replace invalid chars
for i in INVALID_SHEET_CHAR:
name = name.replace(i[0],i[1])
return name
try:
self.book.add_sheet(_short_str(name))
except:
#fallback one
try:
self.book.add_sheet(_short_str(slugify(name)))
except:
#fallback two
self.book.add_sheet('Sheet %d ' % (len(self.sheet_inf)+1))
self.sheet_inf.append(0)
sheet = len(self.sheet_inf)-1
if add_title:
self.write_row(sheet, [name], self.title_style)
return sheet
def write_vheader_row(self, ws, rowlist, style=None):
self.write_row(ws, rowlist, self.header_roate_style)
def write_header_row(self, ws, rowlist, style=None):
self.write_row(ws, rowlist, self.header_style)
def write_row(self, ws, rowlist, style=None):
sh = self.book.get_sheet(ws)
r = self.sheet_inf[ws]
row = sh.row(r)
c = 0
for v in rowlist:
if v == SEPARATOR:
row.write(c, '', self.normal_style)
sh.col(c).width = LINK_WIDTH
elif isinstance(v, Formula):
row.write(c,v, self.link_style)
sh.col(c).width = LINK_WIDTH
elif style is not None and (style != self.header_roate_style or len(v) > 0):
try:
row.write(c,v, style)
except:
logger.exception('Could not write cell')
else:
try:
row.write(c,v, self.normal_style)
except:
logger.exception('Could not write cell')
if (isinstance(v, str) or isinstance(v, unicode)) and style != self.header_roate_style and v != SEPARATOR:
l = min(0x0d00 + len(v)*200, MAX_WIDTH)
if l > sh.col(c).width:
sh.col(c).width = l
c += 1
self.sheet_inf[ws] = r+1
# Initialize an empty workbook
wbook = Workbook(encoding='utf-8')
# Add two sheets to the workbook
sheet1 = wbook.add_sheet('Sheet 1')
wbook.add_sheet('Sheet 2')
# Writing to sheet 1
# We can write by specifying row and column
sheet1.write(0, 0, 'A1')
sheet1.write(0, 1, 'B1')
# Or by creating a new row and specifying only column
row1 = sheet1.row(1)
row1.write(0, 'A2')
row1.write(1, 'B2')
# We can also adjust the column width
sheet1.col(0).width = 10000
# Writing to sheet 2
# We can access the sheets objects as follows
sheet2 = wbook.get_sheet(1)
sheet2.row(0).write(0, 'A1 S2')
sheet2.row(0).write(1, 'B1 S2')
# When adding a large number of rows, we can
# use flush_row_data() to clear memory
sheet2.flush_row_data()
sheet2.row(1).write(0, 'A2 S2')
# We can also hide columns
sheet2.col(0).hidden = True
# xlwt only support .xls writing
wbook.save('write_xlsx.xls')
from xlwt import Workbook
book = Workbook()
sheet1 = book.add_sheet('Sheet 1', cell_overwrite_ok=True)
sheet1.write(0, 0, 'original')
sheet = book.get_sheet(0)
sheet.write(0, 0, 'new')
sheet2 = book.add_sheet('Sheet 2')
sheet2.write(0, 0, 'original')
sheet2.write(0, 0, 'new')
