python类Workbook()的实例源码

def write_excel():
    filename = "python_excel_test.xls"
    excel_file = xlwt.Workbook()
    sheet = excel_file.add_sheet('2016')
    row = 0
    col = 0
    ctype = 'string'
    value = 'Rocky1'
    xf = 0
    sheet.write(row, col, value)

    sheet2 = excel_file.add_sheet('2017')
    row = 0
    col = 0
    ctype = 'string'
    value = 'Rocky122'
    xf = 0
    sheet2.write(row, col, value)
    excel_file.save(filename)

def saveToExcel(content):
    """
    save the content from createBsObj(content) into excel.
    :param content:
    :return:
    """
    wbk = xlwt.Workbook(encoding='utf-8', style_compression=0)
    sheet = wbk.add_sheet("sheet1",cell_overwrite_ok=True)
    wbk.save('data.xls')

    xlsfile=r'./data.xls'
    book = xlrd.open_workbook(xlsfile)
    sheet_name = book.sheet_names()[0]
    sheet1 = book.sheet_by_name(sheet_name)

    nrows = sheet1.nrows
    ncols = sheet1.ncols
    # sheet.write(nrows+i,ncols,bookTitle)
    wbk.save('data.xls')


# main call function

def __init__(self, dim=5, func=None, domain=None):
    self.data = xlwt.Workbook() 
#    self.data = xlrd.open_workbook('C:\Users\ZDZ\Documents\programs\Result\PCA//data.xlsx')
#    self.table = self.data.sheets()[0] 
    self.table = self.data.add_sheet('pca',
                                     cell_overwrite_ok=True)


    self.x = None
    self.n = dim
    self.func = func
    self.dim = dim
    self.domain = domain
    self.orthmat = data_orth.OrthA(self.n)
    self.GroupNum = 5
    for i in xrange(10000):
        self.optimizer = CC_DEaxisPCA.differential_evolution_optimizer(
                                self, population_size=min(self.n*10,100), Round = i%5,
                                n_cross=0,cr=0.5, eps=1e-8, monitor_cycle=50000,
                                show_progress=True)
#    print list(self.x)
#    for x in self.x:
#      assert abs(x-1.0)<1e-2

def create_simple_xls(self, **kw):
        font0 = xlwt.Font()
        font0.name = 'Times New Roman'
        font0.colour_index = 2
        font0.bold = True

        style0 = xlwt.XFStyle()
        style0.font = font0

        style1 = xlwt.XFStyle()
        style1.num_format_str = 'D-MMM-YY'

        wb = xlwt.Workbook(**kw)
        ws = wb.add_sheet('A Test Sheet')

        ws.write(0, 0, 'Test', style0)
        ws.write(1, 0, datetime(2010, 12, 5), style1)
        ws.write(2, 0, 1)
        ws.write(2, 1, 1)
        ws.write(2, 2, xlwt.Formula("A3+B3"))
        return wb, ws

def setup(self):
        if self.report.ftype == REPORT.XLS:
            font_h = xlwt.Font()
            font_h.bold = True
            style_h = xlwt.XFStyle()
            style_h.font = font_h

            self.xls_styles = {
                'datetime': xlwt.easyxf(num_format_str='D/M/YY h:mm'),
                'date': xlwt.easyxf(num_format_str='D/M/YY'),
                'time': xlwt.easyxf(num_format_str='h:mm'),
                'default': xlwt.Style.default_style,
                'bold': style_h
            }

            self.wb = xlwt.Workbook()
            self.ws = self.wb.add_sheet('Data')
            if self.make_sub_reports:
                self.section_ws = self.wb.add_sheet('Section')

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 store_table(tabdata, filename, format='csv'):
    """Saves table data as csv file"""
    if format == 'csv':
        f = file(filename, 'w')
        w = csv.writer(f, delimiter=',')
        for row in tabdata:
            w.writerow(row)
    elif format == 'xls':
        workbook = xlwt.Workbook()
        sheet = workbook.add_sheet('0')
        rn = 0
        for row in tabdata:
            cn = 0
            for c in row:
                sheet.write(rn, cn, c.decode('utf8'))
                cn += 1
            rn += 1
        workbook.save(filename)

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 write_data(data, tname):
    file = xlwt.Workbook(encoding='utf-8')
    table = file.add_sheet(tname, cell_overwrite_ok=True)
    l = 0
    for line in data:
        c = 0
        for _ in line:
            table.write(l, c, line[c])
            c += 1
        l += 1
    sio = StringIO.StringIO()
    file.save(sio)
    return sio


# excel??????

def write_sheet(data_mat, filename, sheetname='Sheet1', header=np.empty(0, dtype=object),
                header_format=None, data_format=np.empty(0, dtype=object)):
    wbk = xlwt.Workbook()
    sheet = wbk.add_sheet(sheetname)
    start_line = 0
    if len(header) == data_mat.shape[1]:
        for j in xrange(data_mat.shape[1]):
            if header_format is None:
                sheet.write(0, j, header[j])
            else:
                sheet.write(0, j, header[j], header_format)
        start_line = 1
    if data_format.size != 0 and len(data_format) != data_mat.shape[0]:
        raise Exception(
            'data_format should be the same length as rows of data_mat')
    for i in xrange(data_mat.shape[0]):
        for j in xrange(data_mat.shape[1]):
            if data_format.size == 0:
                sheet.write(i + start_line, j, data_mat[i][j])
            else:
                sheet.write(i + start_line, j, data_mat[i][j], data_format[i])
    wbk.save(filename)

def write_data(data, tname):
    file = xlwt.Workbook(encoding='utf-8')
    table = file.add_sheet(tname, cell_overwrite_ok=True)
    l = 0
    for line in data:
        c = 0
        for _ in line:
            table.write(l, c, line[c])
            c += 1
        l += 1
    sio = StringIO.StringIO()
    file.save(sio)
    return sio


# excel??????

def __init__(self, dim=5, func=None, domain=None, index = 0):
#    self.data = xlwt.Workbook() 
##    self.data = xlrd.open_workbook('C:\Users\ZDZ\Documents\programs\Result\PCA//data.xlsx')
##    self.table = self.data.sheets()[0] 
#    self.table = self.data.add_sheet('pca', cell_overwrite_ok=True)

    self.fileHandle = open('D:/result/dataLowPCA%d.txt'%i, 'w')

    self.x = None
    self.n = dim
    self.func = func
    self.dim = dim
    self.domain = domain
    self.orthmat = data_orth.OrthA(self.n)
    self.optimizer = data_DEaxisPCA_2.differential_evolution_optimizer(
                                self,population_size=self.n*10,
                                n_cross=0,cr=0.5, eps=1e-8, monitor_cycle=300000,
                                show_progress=True)
#    print list(self.x)
#    for x in self.x:
#      assert abs(x-1.0)<1e-2

def xls(self):
        from xlwt import Workbook

        filename = self.filename_front + '.xls'
        wb = Workbook(encoding='utf-8')  
        wb_sheet = wb.add_sheet("sheet1")
        for title in self.title_list:
            wb_sheet.write(0, self.title_list.index(title), title)

        for line in self.query_set :
            row_no = (self.query_set.index(line) + 1)
            print(line)
            col_no = 0
            for column in line[1:] :
                if col_no == len(line) - 2:
                    # 2016-10-17 15:21:33.348313+00:00
                    re.split('\.' , str(column))[0]
                wb_sheet.write(row_no, col_no, column)
                col_no += 1
                # wb.write(?, ?, ??)
        return filename

def __init__(self, main_test, output_file_path=None, *args, **kwargs):
        """Initialize Excel workbook and Sheet.

        Args:
            main_test (object): the main test instance (e.g. TestSuite instance
                or TestFlow instance).
            output_file_path (str): path to create the excel file in. Leave
                None to create at the test's working dir with the default name.
        """
        super(ExcelHandler, self).__init__(main_test)

        self.row_number = 0
        self.test_to_row = {}
        self.output_file_path = output_file_path
        if self.output_file_path is None:
            self.output_file_path = os.path.join(self.main_test.work_dir,
                                                 self.EXCEL_WORKBOOK_NAME)

        self.workbook = xlwt.Workbook(encoding=self.EXCEL_FILE_ENCODING)
        self.sheet = self.workbook.add_sheet(self.EXCEL_SHEET_NAME,
                                             cell_overwrite_ok=True)

def __init__(self, main_test=None, *args, **kwargs):
        """Initialize the result handler.

        Note:
            Loads the signatures from the DB.

        Args:
            main_test (object): the main test instance.
        """
        super(SignatureHandler, self).__init__(main_test, *args, **kwargs)

        self.row_number = 0
        self.signatures = SignatureData.objects.all()
        self.output_file_path = os.path.join(self.main_test.work_dir,
                                             self.EXCEL_WORKBOOK_NAME)

        self.workbook = xlwt.Workbook(encoding=self.EXCEL_FILE_ENCODING)
        self.sheet = self.workbook.add_sheet(self.EXCEL_SHEET_NAME,
                                             cell_overwrite_ok=True)
        self._prepare_excel_file()

def create_example_xls(filename):
    w = xlwt.Workbook()
    ws1 = w.add_sheet(u'\N{GREEK SMALL LETTER ALPHA}\N{GREEK SMALL LETTER BETA}\N{GREEK SMALL LETTER GAMMA}')

    ws1.write(0, 0, u'\N{GREEK SMALL LETTER ALPHA}\N{GREEK SMALL LETTER BETA}\N{GREEK SMALL LETTER GAMMA}')
    ws1.write(1, 1, u'\N{GREEK SMALL LETTER DELTA}x = 1 + \N{GREEK SMALL LETTER DELTA}')

    ws1.write(2,0, u'A\u2262\u0391.')     # RFC2152 example
    ws1.write(3,0, u'Hi Mom -\u263a-!')   # RFC2152 example
    ws1.write(4,0, u'\u65E5\u672C\u8A9E') # RFC2152 example
    ws1.write(5,0, u'Item 3 is \u00a31.') # RFC2152 example
    ws1.write(8,0, u'\N{INTEGRAL}')       # RFC2152 example

    w.add_sheet(u'A\u2262\u0391.')     # RFC2152 example
    w.add_sheet(u'Hi Mom -\u263a-!')   # RFC2152 example
    one_more_ws = w.add_sheet(u'\u65E5\u672C\u8A9E') # RFC2152 example
    w.add_sheet(u'Item 3 is \u00a31.') # RFC2152 example

    one_more_ws.write(0, 0, u'\u2665\u2665')

    w.add_sheet(u'\N{GREEK SMALL LETTER ETA WITH TONOS}')
    w.save(filename)

def create_simple_xls(self, **kw):
        font0 = xlwt.Font()
        font0.name = 'Times New Roman'
        font0.colour_index = 2
        font0.bold = True

        style0 = xlwt.XFStyle()
        style0.font = font0

        style1 = xlwt.XFStyle()
        style1.num_format_str = 'D-MMM-YY'

        wb = xlwt.Workbook(**kw)
        ws = wb.add_sheet('A Test Sheet')

        ws.write(0, 0, 'Test', style0)
        ws.write(1, 0, datetime(2010, 12, 5), style1)
        ws.write(2, 0, 1)
        ws.write(2, 1, 1)
        ws.write(2, 2, xlwt.Formula("A3+B3"))
        return wb, ws

def test_intersheets_ref(self):
        book = xlwt.Workbook()
        sheet_a = book.add_sheet('A')
        sheet_a.write(0, 0, 'A1')
        sheet_a.write(0, 1, 'A2')
        sheet_b = book.add_sheet('B')
        sheet_b.write(0, 0, xlwt.Formula("'A'!$A$1&'A'!$A$2"))
        out = BytesIO()
        book.save(out)

def create_example_xls(filename):
    w = xlwt.Workbook()
    ws1 = w.add_sheet(u'\N{GREEK SMALL LETTER ALPHA}\N{GREEK SMALL LETTER BETA}\N{GREEK SMALL LETTER GAMMA}')

    ws1.write(0, 0, u'\N{GREEK SMALL LETTER ALPHA}\N{GREEK SMALL LETTER BETA}\N{GREEK SMALL LETTER GAMMA}')
    ws1.write(1, 1, u'\N{GREEK SMALL LETTER DELTA}x = 1 + \N{GREEK SMALL LETTER DELTA}')

    ws1.write(2,0, u'A\u2262\u0391.')     # RFC2152 example
    ws1.write(3,0, u'Hi Mom -\u263a-!')   # RFC2152 example
    ws1.write(4,0, u'\u65E5\u672C\u8A9E') # RFC2152 example
    ws1.write(5,0, u'Item 3 is \u00a31.') # RFC2152 example
    ws1.write(8,0, u'\N{INTEGRAL}')       # RFC2152 example

    w.add_sheet(u'A\u2262\u0391.')     # RFC2152 example
    w.add_sheet(u'Hi Mom -\u263a-!')   # RFC2152 example
    one_more_ws = w.add_sheet(u'\u65E5\u672C\u8A9E') # RFC2152 example
    w.add_sheet(u'Item 3 is \u00a31.') # RFC2152 example

    one_more_ws.write(0, 0, u'\u2665\u2665')

    w.add_sheet(u'\N{GREEK SMALL LETTER ETA WITH TONOS}')
    w.save(filename)

def on_proceed(self,event):
        self.Selected_Index=self.checkedItems
        all_divs=[self.DIV]
        if self.DIV=='All Divisions':
            all_divs=self.DB.Get_Div(self.YEAR,self.CLASS)

        self.book = Workbook()

        for each_div in all_divs:

            sheet = self.book.add_sheet(self.CLASS+each_div)
            self.write_headings_to_excel(sheet)
            self.get_each_sub(sheet,each_div)

        self.save()
        self.Close()

def get_excel():
    book = xlwt.Workbook(encoding = 'utf-8',style_compression=0)
    sheet = book.add_sheet('data',cell_overwrite_ok = True)

    global dt
    j = 0
    for each in datelist((2014, 1, 1), (2014, 1, 31)):
        dt = {'cdateEnd':each,'pageNo1':'1','pageNo2':''}
        get_html()
        soup = BeautifulSoup(h)
        #j = 0
        for tabb in soup.find_all('tr'):
            i=0;
            for tdd in tabb.find_all('td'):
                #print (tdd.get_text()+",",)
                sheet.write(j,i,tdd.get_text())
                i = i+1
            j=j+1
    book.save(r'.\\re'+each+'.xls')

def __call__(self):
        book = xlwt.Workbook()
        sheet = book.add_sheet(self.sheet_name)
        rowx = 0
        for colx, value in enumerate(self.headings):
            sheet.write(rowx, colx, value)
        sheet.set_panes_frozen(True)  # frozen headings instead of split panes
        sheet.set_horz_split_pos(rowx + 1)  # in general, freeze after last heading row
        sheet.set_remove_splits(True)  # if user does unfreeze, don't leave a split there
        for row in self.data:
            rowx += 1
            for colx, value in enumerate(row):
                sheet.write(rowx, colx, value.encode('utf-8').decode('utf-8'))
        buf = cStringIO.StringIO()
        if self.file_name:
            book.save(self.file_name)
        book.save(buf)
        out = base64.encodestring(buf.getvalue())
        buf.close()
        return out


# -*- coding: utf-8 -*-

def saveToExcel(appData,dateTime):
    # print "tmp hold"
    # return 0
    table_name,app_array= (appData["item"],appData["app_array"])

    import xlwt

    efile = xlwt.Workbook()
    table = efile.add_sheet('Sheet1')

    table.write(0,0,u'??')
    table.write(0,1,table_name)
    table.write(0,2,u'??')
    table.write(0,3,dateTime)

    for num,each in enumerate(app_array):
        index = num +1
        table.write(index,0,index)
        table.write(index,1,app_array[num])

    efile.save('App Annie'+ '_' + contry +'_' +table_name + '_' +dateTime+'.xls')

def saveAll(self):
        book=xlwt.Workbook()
        sheet =book.add_sheet(str(self.keyword), cell_overwrite_ok=True)
        container=self.saveDetail()
        print u'\n????'
        print u'\n?????????...'
        heads=[u'????', u'????', u'????', u'??', u'??', u'??', u'????',u'????',u'????',u'??????']
        ii=0
        for head in heads:
            sheet.write(0,ii,head)
            ii+=1
        i=1
        for list in container:
            j=0
            for one in list:
                sheet.write(i, j, one)
                j+=1
            i+=1
        book.save(str(self.keyword)+'.xls')
        print u'\n?????'

def save_detail(self):
        book=xlwt.Workbook()
        sheet=book.add_sheet('sheet1',cell_overwrite_ok=True)
        heads=[u'??',u'??',u'??',u'????',u'??']
        ii=0
        for head in heads:
            sheet.write(0,ii,head)
            ii+=1

        container=self.get_detail()
        f=open(r'F:\Desktop\DouBan2.txt','w')
        i=1
        for list in container:
            f.writelines(list[5].encode('utf-8'))
            list.remove(list[5])
            j=0
            for data in list:
                sheet.write(i,j,data)
                j+=1
            i+=1
        f.close()
        print u'\n\n',u'??txt???'
        book.save('DouBan2.xls')
        print u'\n\n',u'??Excel??!'

def exportAsExcel(self, path, studentList=None):
        studentList = studentList or self.studentList
        import xlwt
        xls = xlwt.Workbook(encoding='utf-8')
        xlss = xls.add_sheet("??????")
        attrs = []
        width = [100, 80, 50, 100, 200, 80, 80]
        for header in range(0, len(attributeList)):
            xlss.write(0, header, attributeList[header][1])
            xlss.col(header).width = width[header] * 256 // 9
            attrs.append(attributeList[header][0])
        for row in range(0, len(studentList)):
            student = studentList[row]
            for column in range(0, len(attrs)):
                if column == 2:
                    value = student.getSex()
                else:
                    value = getattr(student, attrs[column])
                xlss.write(row + 1, column, value)
        xls.save(path)

def write_data_into_excel(x, total_number_dalvik_size, dalvik_size, dalvik_alloc, app_map_name,
                          total_dalvik_size1,
                          dalvik_size1, dalvik_alloc1, first_time, end_time, levels, capacity,
                          computed, uid_item_info):
    book = Workbook(encoding='utf-8')
    table = book.add_sheet(Res.sheet_name)
    for r in range(6):
        table.write(0, r + 1, Res.sheet_row_arr[r])

    column_content_arr = [total_number_dalvik_size, dalvik_size, dalvik_alloc, total_dalvik_size1, dalvik_size1,
                          dalvik_alloc1, first_time, end_time, levels, capacity, computed,
                          uid_item_info]
    for c in range(len(column_content_arr)):
        arr_into_table(table, c, column_content_arr[c])
    book.save(str(app_map_name) + str(x) + Res.output_file_name)

def write_the_data(data_time_map, data_time_map_begin, battery_percentage, capacity_map, computed_drain_map,
                   battery_actual_drain_map, carried_out_what, uid_detailed_info_map, uid_detailed_num_map, x,
                   app_map_name):
    times_num = 0
    f = Workbook(encoding='utf-8')
    table = f.add_sheet('Sheet')
    table.write(0, 0, u'time')
    table.write(0, 1, u'end_time')
    table.write(0, 2, u'levels')
    table.write(0, 3, u'Capacity')
    table.write(0, 4, u'Computed drain')
    # table.write(0, 5, u'actual drain')
    table.write(0, 5, u'steps')
    table.write(0, 6, u'UID')

    for times_num in range(len(data_time_map)):
        table.write(times_num + 1, 0, data_time_map[times_num])
    for times_nums in range(len(data_time_map_begin)):
        table.write(times_nums + 1, 1, data_time_map_begin[times_nums])
    for per_num in range(len(battery_percentage)):
        table.write(per_num + 1, 2, battery_percentage[per_num])
    for cap_num in range(len(capacity_map)):
        table.write(cap_num + 1, 3, capacity_map[cap_num])
    for cop_num in range(len(computed_drain_map)):
        table.write(cop_num + 1, 4, computed_drain_map[cop_num])
    # for bad_num in range(len(battery_actual_drain_map)):
    #     table.write(bad_num+1,5,battery_actual_drain_map[bad_num])
    for cow_num in range(len(carried_out_what)):
        table.write(cow_num + 1, 5, carried_out_what[cow_num])
    for udi_num in range(len(uid_detailed_info_map)):
        table.write(udi_num + 1, 6, uid_detailed_info_map[udi_num])

    f.save(str(app_map_name) + str(x) + 'dage.xlsx')

def configErrorReporting(headers):
  """
  Configure import exception log, which is an Excel spreadsheet in the same 
  format as the input format, but with an extra column added - "Error",
  which contains the error message.

  Can only be called after first row of input Excel spreadsheet is read
  to initialize the global, "headers"
  """
  dateFmt = easyxf(
    'font: name Arial, bold True, height 200;',
    #'borders: left thick, right thick, top thick, bottom thick;',
     num_format_str='MM-DD-YYYY'
  )

  headerFmt = easyxf(
    'font: name Arial, bold True, height 200;',
  )
  global errorsWorkbook, erroutSheet, erroutRow
  errorsWorkbook = Workbook()
  erroutSheet = errorsWorkbook.add_sheet('Import Errors')

  for colnum in range(0, len(headers)):
    erroutSheet.write(0, colnum, headers[colnum][0], 
    tern(headers[colnum][0]==xlrd.XL_CELL_DATE, dateFmt, headerFmt))

  # Add extra column for error message
  erroutSheet.write(0, len(headers), "Error", headerFmt)
  erroutSheet.flush_row_data()
  erroutRow = 1
  errorsWorkbook.save('errors.xls')

def init_new_excel(self, filename):
        xls = xlwt.Workbook()
        sheet = xls.add_sheet('sheet1', cell_overwrite_ok=True)
        sheet.write(0, 0, u'???')
        sheet.write(0, 1, u'??????')
        sheet.write(0, 2, u'?????')
        sheet.write(0, 3, u'?????')
        sheet.write(0, 4, u'???')
        sheet.write(0, 5, u'????')
        sheet.write(0, 6, u'????')
        sheet.write(0, 7, u'??(??)')
        sheet.write(0, 8, u'????')
        sheet.write(0, 9, u'????')
        sheet.write(0, 10, u'????')
        sheet.write(0, 11, u'??????')
        sheet.write(0, 12, u'????')
        sheet.write(0, 13, u'????')
        sheet.write(0, 14, u'????(??)')
        sheet.write(0, 15, u'??????')
        sheet.write(0, 16, u'??')
        sheet.write(0, 17, u'??')
        sheet.write(0, 18, u'??????')
        sheet.write(0, 19, u'??????')
        sheet.write(0, 20, u'??????')
        sheet.write(0, 21, u'??????')
        xls.save(os.path.join(XLS_FILE_DIR, filename + '.xls'))
        self.handlers.append(xls)
        self.file_mapper[filename] = len(self.handlers) - 1