Python xlwt 模块，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

def _excel_of_histories(self, histories, filename):
        headers = ('??', '???', '????', '??', '????', '????', '????', '????', '????', '????', '????',)
        columns = (
            lambda x: x.timeslot and x.timeslot.order.order_id or (x.comment or '?????'),
            'timeslot.order.grade',
            'timeslot.order.subject',
            'timeslot.order.level',
            lambda x: x.timeslot and ('%s-%s' % (x.timeslot.start.strftime('%H:%M'), x.timeslot.end.strftime('%H:%M'),)) or '',
            lambda x: x.timeslot and (x.timeslot.order.price / 100) or '',
            lambda x: x.timeslot and x.timeslot.duration_hours() or '',
            lambda x: x.timeslot and ('%s%%' % (x.timeslot.order.commission_percentage),) or '',
            lambda x: x.amount and (x.amount / 100) or '',
        )
        workbook = xlwt.Workbook()
        sheet_name = 'Export {0}'.format(datetime.date.today().strftime('%Y-%m-%d'))
        sheet = workbook.add_sheet(sheet_name)
        for y, th in enumerate(headers):
            sheet.write(0, y, th, excel.HEADER_STYLE)
        x = 1
        for history in histories:
            y = 0
            day_val = history['day'].date()
            sheet.write_merge(x, x + history['count'] - 1, y, y, day_val, excel.get_style_by_value(day_val))
            x_sub = x
            records = history['records']
            for record in records:
                y_sub = y+1
                for column in columns:
                    value = callable(column) and column(record) or excel.get_column_cell(record, column)
                    sheet.write(x_sub, y_sub, value, excel.get_style_by_value(value))
                    y_sub += 1
                x_sub += 1
            income = history['income'] / 100
            y += len(columns) + 1
            sheet.write_merge(x, x + history['count'] - 1, y, y, income, excel.get_style_by_value(income))
            x += len(records)
        return excel.wb_excel_response(workbook, filename)

def queryset_to_workbook(queryset, columns, headers=None, header_style=HEADER_STYLE,
                         default_style=DEFAULT_STYLE, cell_style_map=CELL_STYLE_MAP):
    '''
    ?django QuerySet??????excel?Workbook
    :param queryset: django QuerySet??
    :param columns: ? ('??', '???',        '????')
    :param headers: ? ('name', 'profile.phone', lambda x: (x.balance/100),)
    :param header_style: ?????
    :param default_style: ????
    :param cell_style_map: (????,??)???
    :return: xlwt.Workbook
    '''
    workbook = xlwt.Workbook()
    report_date = datetime.date.today()
    sheet_name = 'Export {0}'.format(report_date.strftime('%Y-%m-%d'))
    sheet = workbook.add_sheet(sheet_name)

    if headers:
        for y, th in enumerate(headers):
            sheet.write(0, y, th, header_style)
    else:
        for y, th in enumerate(columns):
            value = get_column_head(th)
            sheet.write(0, y, value, header_style)

    for x, obj in enumerate(queryset, start=1):
        for y, column in enumerate(columns):
            if callable(column):
                value = column(obj)
            else:
                value = get_column_cell(obj, column)
            style = get_style_by_value(value, cell_style_map, default_style)
            sheet.write(x, y, value, style)

    return workbook

def get_xlsx_export(self, context):
        datas = self._get_datas(context)
        output = io.StringIO()
        export_header = (
            self.request.GET.get('export_xlsx_header', 'off') == 'on')

        model_name = self.opts.verbose_name
        book = xlsxwriter.Workbook(output)
        sheet = book.add_worksheet(
            u"%s %s" % (_(u'Sheet'), force_text(model_name)))
        styles = {'datetime': book.add_format({'num_format': 'yyyy-mm-dd hh:mm:ss'}),
                  'date': book.add_format({'num_format': 'yyyy-mm-dd'}),
                  'time': book.add_format({'num_format': 'hh:mm:ss'}),
                  'header': book.add_format({'font': 'name Times New Roman', 'color': 'red', 'bold': 'on', 'num_format': '#,##0.00'}),
                  'default': book.add_format()}

        if not export_header:
            datas = datas[1:]
        for rowx, row in enumerate(datas):
            for colx, value in enumerate(row):
                if export_header and rowx == 0:
                    cell_style = styles['header']
                else:
                    if isinstance(value, datetime.datetime):
                        cell_style = styles['datetime']
                    elif isinstance(value, datetime.date):
                        cell_style = styles['date']
                    elif isinstance(value, datetime.time):
                        cell_style = styles['time']
                    else:
                        cell_style = styles['default']
                sheet.write(rowx, colx, value, cell_style)
        book.close()

        output.seek(0)
        return output.getvalue()

def get_xls_export(self, context):
        datas = self._get_datas(context)
        output = io.StringIO()
        export_header = (
            self.request.GET.get('export_xls_header', 'off') == 'on')

        model_name = self.opts.verbose_name
        book = xlwt.Workbook(encoding='utf8')
        sheet = book.add_sheet(
            u"%s %s" % (_(u'Sheet'), force_text(model_name)))
        styles = {'datetime': xlwt.easyxf(num_format_str='yyyy-mm-dd hh:mm:ss'),
                  'date': xlwt.easyxf(num_format_str='yyyy-mm-dd'),
                  'time': xlwt.easyxf(num_format_str='hh:mm:ss'),
                  'header': xlwt.easyxf('font: name Times New Roman, color-index red, bold on', num_format_str='#,##0.00'),
                  'default': xlwt.Style.default_style}

        if not export_header:
            datas = datas[1:]
        for rowx, row in enumerate(datas):
            for colx, value in enumerate(row):
                if export_header and rowx == 0:
                    cell_style = styles['header']
                else:
                    if isinstance(value, datetime.datetime):
                        cell_style = styles['datetime']
                    elif isinstance(value, datetime.date):
                        cell_style = styles['date']
                    elif isinstance(value, datetime.time):
                        cell_style = styles['time']
                    else:
                        cell_style = styles['default']
                sheet.write(rowx, colx, value, style=cell_style)
        book.save(output)

        output.seek(0)
        return output.getvalue()

def get_xlsx_export(self, context):
        datas = self._get_datas(context)
        output = io.BytesIO()
        export_header = (
            self.request.GET.get('export_xlsx_header', 'off') == 'on')

        model_name = self.opts.verbose_name
        book = xlsxwriter.Workbook(output)
        sheet = book.add_worksheet(
            u"%s %s" % (_(u'Sheet'), force_text(model_name)))
        styles = {'datetime': book.add_format({'num_format': 'yyyy-mm-dd hh:mm:ss'}),
                  'date': book.add_format({'num_format': 'yyyy-mm-dd'}),
                  'time': book.add_format({'num_format': 'hh:mm:ss'}),
                  'header': book.add_format({'font': 'name Times New Roman', 'color': 'red', 'bold': 'on', 'num_format': '#,##0.00'}),
                  'default': book.add_format()}

        if not export_header:
            datas = datas[1:]
        for rowx, row in enumerate(datas):
            for colx, value in enumerate(row):
                if export_header and rowx == 0:
                    cell_style = styles['header']
                else:
                    if isinstance(value, datetime.datetime):
                        cell_style = styles['datetime']
                    elif isinstance(value, datetime.date):
                        cell_style = styles['date']
                    elif isinstance(value, datetime.time):
                        cell_style = styles['time']
                    else:
                        cell_style = styles['default']
                sheet.write(rowx, colx, value, cell_style)
        book.close()

        output.seek(0)
        return output.getvalue()

def get_xls_export(self, context):
        datas = self._get_datas(context)
        output = io.BytesIO()
        export_header = (
            self.request.GET.get('export_xls_header', 'off') == 'on')

        model_name = self.opts.verbose_name
        book = xlwt.Workbook(encoding='utf8')
        sheet = book.add_sheet(
            u"%s %s" % (_(u'Sheet'), force_text(model_name)))
        styles = {'datetime': xlwt.easyxf(num_format_str='yyyy-mm-dd hh:mm:ss'),
                  'date': xlwt.easyxf(num_format_str='yyyy-mm-dd'),
                  'time': xlwt.easyxf(num_format_str='hh:mm:ss'),
                  'header': xlwt.easyxf('font: name Times New Roman, color-index red, bold on', num_format_str='#,##0.00'),
                  'default': xlwt.Style.default_style}

        if not export_header:
            datas = datas[1:]
        for rowx, row in enumerate(datas):
            for colx, value in enumerate(row):
                if export_header and rowx == 0:
                    cell_style = styles['header']
                else:
                    if isinstance(value, datetime.datetime):
                        cell_style = styles['datetime']
                    elif isinstance(value, datetime.date):
                        cell_style = styles['date']
                    elif isinstance(value, datetime.time):
                        cell_style = styles['time']
                    else:
                        cell_style = styles['default']
                sheet.write(rowx, colx, value, style=cell_style)
        book.save(output)

        output.seek(0)
        return output.getvalue()

def get_xlsx_export(self, context):
        datas = self._get_datas(context)
        output = StringIO.StringIO()
        export_header = (
            self.request.GET.get('export_xlsx_header', 'off') == 'on')

        model_name = self.opts.verbose_name
        book = xlsxwriter.Workbook(output)
        sheet = book.add_worksheet(
            u"%s %s" % (_(u'Sheet'), force_unicode(model_name)))
        styles = {'datetime': book.add_format({'num_format': 'yyyy-mm-dd hh:mm:ss'}),
                  'date': book.add_format({'num_format': 'yyyy-mm-dd'}),
                  'time': book.add_format({'num_format': 'hh:mm:ss'}),
                  'header': book.add_format({'font': 'name Times New Roman', 'color': 'red', 'bold': 'on', 'num_format': '#,##0.00'}),
                  'default': book.add_format()}

        if not export_header:
            datas = datas[1:]
        for rowx, row in enumerate(datas):
            for colx, value in enumerate(row):
                if export_header and rowx == 0:
                    cell_style = styles['header']
                else:
                    if isinstance(value, datetime.datetime):
                        cell_style = styles['datetime']
                    elif isinstance(value, datetime.date):
                        cell_style = styles['date']
                    elif isinstance(value, datetime.time):
                        cell_style = styles['time']
                    else:
                        cell_style = styles['default']
                sheet.write(rowx, colx, value, cell_style)
        book.close()

        output.seek(0)
        return output.getvalue()

def get_xls_export(self, context):
        datas = self._get_datas(context)
        output = StringIO.StringIO()
        export_header = (
            self.request.GET.get('export_xls_header', 'off') == 'on')

        model_name = self.opts.verbose_name
        book = xlwt.Workbook(encoding='utf8')
        sheet = book.add_sheet(
            u"%s %s" % (_(u'Sheet'), force_unicode(model_name)))
        styles = {'datetime': xlwt.easyxf(num_format_str='yyyy-mm-dd hh:mm:ss'),
                  'date': xlwt.easyxf(num_format_str='yyyy-mm-dd'),
                  'time': xlwt.easyxf(num_format_str='hh:mm:ss'),
                  'header': xlwt.easyxf('font: name Times New Roman, color-index red, bold on', num_format_str='#,##0.00'),
                  'default': xlwt.Style.default_style}

        if not export_header:
            datas = datas[1:]
        for rowx, row in enumerate(datas):
            for colx, value in enumerate(row):
                if export_header and rowx == 0:
                    cell_style = styles['header']
                else:
                    if isinstance(value, datetime.datetime):
                        cell_style = styles['datetime']
                    elif isinstance(value, datetime.date):
                        cell_style = styles['date']
                    elif isinstance(value, datetime.time):
                        cell_style = styles['time']
                    else:
                        cell_style = styles['default']
                sheet.write(rowx, colx, value, style=cell_style)
        book.save(output)

        output.seek(0)
        return output.getvalue()

def __init__(self,key,city):
        self.key = key
        self.city = city
        # ?????????????????????
        self.T = datetime.datetime.strftime(datetime.datetime.now(),"%Y%m%d%H%M")
        # ??????????sheet??
        self.work = xlwt.Workbook(encoding="utf-8")
        self.sheet = self.work.add_sheet("{}_{}".format(self.key,self.city))
        # ??????
        self.get_title()

    # ?????????

def get_xlsx_export(self, context):
        datas = self._get_datas(context)
        output = io.BytesIO()
        export_header = (
            self.request.GET.get('export_xlsx_header', 'off') == 'on')

        model_name = self.opts.verbose_name
        book = xlsxwriter.Workbook(output)
        sheet = book.add_worksheet(
            u"%s %s" % (_(u'Sheet'), force_text(model_name)))
        styles = {'datetime': book.add_format({'num_format': 'yyyy-mm-dd hh:mm:ss'}),
                  'date': book.add_format({'num_format': 'yyyy-mm-dd'}),
                  'time': book.add_format({'num_format': 'hh:mm:ss'}),
                  'header': book.add_format({'font': 'name Times New Roman', 'color': 'red', 'bold': 'on', 'num_format': '#,##0.00'}),
                  'default': book.add_format()}

        if not export_header:
            datas = datas[1:]
        for rowx, row in enumerate(datas):
            for colx, value in enumerate(row):
                if export_header and rowx == 0:
                    cell_style = styles['header']
                else:
                    if isinstance(value, datetime.datetime):
                        cell_style = styles['datetime']
                    elif isinstance(value, datetime.date):
                        cell_style = styles['date']
                    elif isinstance(value, datetime.time):
                        cell_style = styles['time']
                    else:
                        cell_style = styles['default']
                sheet.write(rowx, colx, value, cell_style)
        book.close()

        output.seek(0)
        return output.getvalue()

def get_xls_export(self, context):
        datas = self._get_datas(context)
        output = io.BytesIO()
        export_header = (
            self.request.GET.get('export_xls_header', 'off') == 'on')

        model_name = self.opts.verbose_name
        book = xlwt.Workbook(encoding='utf8')
        sheet = book.add_sheet(
            u"%s %s" % (_(u'Sheet'), force_text(model_name)))
        styles = {'datetime': xlwt.easyxf(num_format_str='yyyy-mm-dd hh:mm:ss'),
                  'date': xlwt.easyxf(num_format_str='yyyy-mm-dd'),
                  'time': xlwt.easyxf(num_format_str='hh:mm:ss'),
                  'header': xlwt.easyxf('font: name Times New Roman, color-index red, bold on', num_format_str='#,##0.00'),
                  'default': xlwt.Style.default_style}

        if not export_header:
            datas = datas[1:]
        for rowx, row in enumerate(datas):
            for colx, value in enumerate(row):
                if export_header and rowx == 0:
                    cell_style = styles['header']
                else:
                    if isinstance(value, datetime.datetime):
                        cell_style = styles['datetime']
                    elif isinstance(value, datetime.date):
                        cell_style = styles['date']
                    elif isinstance(value, datetime.time):
                        cell_style = styles['time']
                    else:
                        cell_style = styles['default']
                sheet.write(rowx, colx, value, style=cell_style)
        book.save(output)

        output.seek(0)
        return output.getvalue()

def __init__(self):
        self.headers={'Host': 'api.k.sohu.com','User-Agent': 'SohuNews/5.9.3 BuildCode/126',
                      'Authorization': '990006203070023','Content-Encoding': 'UTF-8'}
        self.new_url='http://api.k.sohu.com/api/channel/v6/news.go?p1=NjMwMjg4NTczMDc1OTEyNzA2OA%3D%3D&pid=-1&channelId=1&num=20&imgTag=1&showPic=1&picScale=11&rt=json&net=wifi&cdma_lat=22.553053&cdma_lng=113.902393&from=channel&mac=b4%3A0b%3A44%3A83%3A93%3A16&AndroidID=4dd00e258bbe295f&carrier=CMCC&imei=990006203070023&imsi=460020242631842&density=3.0&apiVersion=37&skd=9bf84c6c9d24711f43f7058db2d1ed5ba7c6a2fecca504d3f44839a8bf22b4521ff192a4ac2d77946d871706ceb89baa269d145d2f5a07fddb656d6417029bb04459d2a5aa0ca50764b2de62da32f9e5e6055efa78b93cafbd89ef0971a836d3542ce2065edff7017a28b164e4210fec&v=1502985600&t=1503044087&page=1&action=0&mode=0&cursor=0&mainFocalId=0&focusPosition=1&viceFocalId=0&lastUpdateTime=0&gbcode=440300&forceRefresh=0&apiVersion=37&u=1&source=0&isSupportRedPacket=0&t=1503044087'


        self.old_url='http://api.k.sohu.com/api/channel/v6/news.go?p1=NjMwMjg4NTczMDc1OTEyNzA2OA%3D%3D&pid=-1&channelId=1&num=20&imgTag=1&showPic=1&picScale=11&rt=json&net=wifi&cdma_lat=22.568970&cdma_lng=113.948697&from=channel&mac=b4%3A0b%3A44%3A83%3A93%3A16&AndroidID=4dd00e258bbe295f&carrier=CMCC&imei=990006203070023&imsi=460020242631842&density=3.0&apiVersion=37&skd=9bf84c6c9d24711f43f7058db2d1ed5ba7c6a2fecca504d3f44839a8bf22b4521ff192a4ac2d77946d871706ceb89baa26d2a04c74e7f9a802366235a8d013a24459d2a5aa0ca50764b2de62da32f9e5e6055efa78b93cafbd89ef0971a836d3542ce2065edff7017a28b164e4210fec&v=1502985600&t=1502992147&page=2&action=2&mode=1&cursor=6817205&mainFocalId=6816008&focusPosition=2&viceFocalId=0&lastUpdateTime=0&gbcode=440300&forceRefresh=0&apiVersion=37&u=1&source=0&isSupportRedPacket=0&t=1502992147'

        self.current=datetime.datetime.now().strftime('%Y-%m-%d_%H-%M-%M')+'.xls'
        self.excelFile=xlwt.Workbook()

        self.sheet=self.excelFile.add_sheet('sohu')
        self.row = 0

def extract(filename, format='csv', sizefilter=0, singlefile=False):
    tables = extract_docx_table(filename)
    name = filename.rsplit('.', 1)[0]
    format = format.lower()
    n = 0
    lfilter = int(sizefilter)
    if singlefile:
        workbook = xlwt.Workbook()
        for t in tables:
            if lfilter >= len(t):
                print 'Table length %d instead of %d. Skipped' % (len(t), lfilter)
                continue
            n += 1
            sheet = workbook.add_sheet(str(n))
            rn = 0
            for row in t:
                cn = 0
                for c in row:
                    sheet.write(rn, cn, c.decode('utf8'))
                    cn += 1
                rn += 1
        destname = name + '.%s' % (format)
        workbook.save(destname)
        print destname, 'saved'
    else:
        for t in tables:
            if lfilter >= len(t):
                print 'Table length %d instead of %d. Skipped' % (len(t), lfilter)
                continue
            n += 1
            destname = name + '_%d.%s' % (n, format)
            store_table(t, destname, format)
            print destname, 'saved'

def __init__(self, filename):
        self.workbook = xlsxwriter.Workbook(filename)

def __init__(self, filename):
        self.__fileName = filename
        try:
            xlrd.open_workbook(filename)
        except:
            work_book = xlwt.Workbook()
            work_sheet = work_book.add_sheet("Sheet1")
            work_book.save(filename)

def get_xlsx_export(self, context):
        datas = self._get_datas(context)
        output = io.BytesIO()
        export_header = (
            self.request.GET.get('export_xlsx_header', 'off') == 'on')

        model_name = self.opts.verbose_name
        book = xlsxwriter.Workbook(output)
        sheet = book.add_worksheet(
            u"%s %s" % (_(u'Sheet'), force_text(model_name)))
        styles = {'datetime': book.add_format({'num_format': 'yyyy-mm-dd hh:mm:ss'}),
                  'date': book.add_format({'num_format': 'yyyy-mm-dd'}),
                  'time': book.add_format({'num_format': 'hh:mm:ss'}),
                  'header': book.add_format({'font': 'name Times New Roman', 'color': 'red', 'bold': 'on', 'num_format': '#,##0.00'}),
                  'default': book.add_format()}

        if not export_header:
            datas = datas[1:]
        for rowx, row in enumerate(datas):
            for colx, value in enumerate(row):
                if export_header and rowx == 0:
                    cell_style = styles['header']
                else:
                    if isinstance(value, datetime.datetime):
                        cell_style = styles['datetime']
                    elif isinstance(value, datetime.date):
                        cell_style = styles['date']
                    elif isinstance(value, datetime.time):
                        cell_style = styles['time']
                    else:
                        cell_style = styles['default']
                sheet.write(rowx, colx, value, cell_style)
        book.close()

        output.seek(0)
        return output.getvalue()