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')