Python xlwt.Workbook() Examples

def __init__(self, path, engine=None, encoding=None, mode='w',
                 **engine_kwargs):
        # Use the xlwt module as the Excel writer.
        import xlwt
        engine_kwargs['engine'] = engine

        if mode == 'a':
            raise ValueError('Append mode is not supported with xlwt!')

        super(_XlwtWriter, self).__init__(path, mode=mode, **engine_kwargs)

        if encoding is None:
            encoding = 'ascii'
        self.book = xlwt.Workbook(encoding=encoding)
        self.fm_datetime = xlwt.easyxf(num_format_str=self.datetime_format)
        self.fm_date = xlwt.easyxf(num_format_str=self.date_format) 

def generate_excel_file():
    """Builds excel file in memory, later bind this data to BINARY file.
    """
    import io
    from xlwt import Workbook

    file_excel = io.BytesIO()  # create a file-like object
    # Create the Excel file
    workbook = Workbook(encoding='utf-8')
    sheet = workbook.add_sheet('sheet1')

    sheet.write(0, 0, 'column1')
    sheet.write(0, 1, 'column2')
    sheet.write(0, 2, 'column3')

    sheet.write(1, 0, 'Field11')
    sheet.write(1, 1, 'ఫీల్డ్12')
    sheet.write(1, 2, 'fält13')

    sheet.write(2, 0, 'поле21')
    sheet.write(2, 1, 'फील्ड22')
    sheet.write(2, 2, 'สนาม23')
    workbook.save(file_excel)
    return file_excel 

def test_abs_restrictions_env_model(data):
    model = EnvelopmentModelBase(data,
                                 EnvelopmentModelInputOriented(
                                     generate_upper_bound_for_efficiency_score),
                                 DefaultConstraintCreator())

    bounds = {'I2': (0.01, 0.5)}
    model = EnvelopmentModelWithAbsoluteWeightRestrictions(model, bounds)
    start_time = datetime.datetime.now()
    model_solution = model.run()
    end_time = datetime.datetime.now()
    utils_for_tests.check_if_category_is_within_abs_limits(
        model_solution, bounds)

    work_book = xlwt.Workbook()
    writer = XLSWriter(Parameters(), work_book, datetime.datetime.today(),
                       (end_time - start_time).total_seconds())
    writer.write_data(model_solution)
    work_book.save('tests/test_abs_constraints_env_output.xls') 

def export2xls(self):
        import xlwt
        font0 = xlwt.Font()
        font0.bold = True
        font0.height = 300
        print((font0.height))

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

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

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

        ws.write(0, 0, 'Test', style0)
        ws.write(2, 0, 1)
        ws.write(2, 1, 1)
        ws.write(2, 2, xlwt.Formula("A3+B3"))

        wb.save('datasheet.xls')
        os.system("gnumeric datasheet.xls") 

def from_data(self, fields, rows):
        workbook = xlwt.Workbook()
        worksheet = workbook.add_sheet('Sheet 1')

        for i, fieldname in enumerate(fields):
            worksheet.write(0, i, fieldname)
            worksheet.col(i).width = 8000 # around 220 pixels

        style = xlwt.easyxf('align: wrap yes')

        for row_index, row in enumerate(rows):
            for cell_index, cell_value in enumerate(row):
                if isinstance(cell_value, basestring):
                    cell_value = re.sub("\r", " ", cell_value)
                if cell_value is False: cell_value = None
                worksheet.write(row_index + 1, cell_index, cell_value, style)

        fp = StringIO()
        workbook.save(fp)
        fp.seek(0)
        data = fp.read()
        fp.close()
        return data 

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 create_workbook(output_file):
    ''' Creates a proper instance of data file writer depending on file extension.
        Supported formats are: xls, xslx and csv.

        Args:
            output_file (str): file name.

        Returns:
            (xlwt.Workbook, XlsxWorkbook or OneCsvWriter): created data file
                writer instance.

        Raises:
            ValueError: if a file with not supported extension was provided.
    '''
    if output_file.endswith('.xls'):
        work_book = xlwt.Workbook()
    elif output_file.endswith('.xlsx'):
        work_book = XlsxWorkbook()
    elif output_file.endswith('.csv'):
        work_book = OneCsvWriter(output_file)
    else:
        raise ValueError('File {0} has unsupported output format'.format
                         (output_file))
    return work_book 

def __init__(self, path, engine=None,
                 date_format=None, datetime_format=None, mode='w',
                 **engine_kwargs):
        # Use the xlsxwriter module as the Excel writer.
        import xlsxwriter

        if mode == 'a':
            raise ValueError('Append mode is not supported with xlsxwriter!')

        super(_XlsxWriter, self).__init__(path, engine=engine,
                                          date_format=date_format,
                                          datetime_format=datetime_format,
                                          mode=mode,
                                          **engine_kwargs)

        self.book = xlsxwriter.Workbook(path, **engine_kwargs) 

def test_super_efficiency_with_VRS(DEA_example2_data):
    data = DEA_example2_data
    data.add_input_category('I1')
    data.add_input_category('I2')
    data.add_input_category('I3')
    data.add_output_category('O1')
    data.add_output_category('O2')
    model = EnvelopmentModelBase(
        data,
        EnvelopmentModelInputOriented(generate_supper_efficiency_upper_bound),
        DefaultConstraintCreator())
    model = EnvelopmentModelVRSDecorator(model)
    super_efficiency_model = SupperEfficiencyModel(model)
    start_time = datetime.datetime.now()
    solution = super_efficiency_model.run()
    end_time = datetime.datetime.now()
    work_book = xlwt.Workbook()
    writer = XLSWriter(Parameters(), work_book, datetime.datetime.today(),
                       (end_time - start_time).total_seconds())
    writer.write_data(solution)
    work_book.save('tests/test_super_efficiency_with_VRS.xls') 

def __init__(self, path, engine=None, **engine_kwargs):
        # Use the openpyxl module as the Excel writer.
        from openpyxl.workbook import Workbook

        super(_OpenpyxlWriter, self).__init__(path, **engine_kwargs)

        # Create workbook object with default optimized_write=True.
        self.book = Workbook()

        # Openpyxl 1.6.1 adds a dummy sheet. We remove it.
        if self.book.worksheets:
            try:
                self.book.remove(self.book.worksheets[0])
            except AttributeError:

                # compat
                self.book.remove_sheet(self.book.worksheets[0]) 

def __init__(self, path, engine=None, mode='w', **engine_kwargs):
        # Use the openpyxl module as the Excel writer.
        from openpyxl.workbook import Workbook

        super(_OpenpyxlWriter, self).__init__(path, mode=mode, **engine_kwargs)

        if self.mode == 'a':  # Load from existing workbook
            from openpyxl import load_workbook
            book = load_workbook(self.path)
            self.book = book
        else:
            # Create workbook object with default optimized_write=True.
            self.book = Workbook()

            if self.book.worksheets:
                try:
                    self.book.remove(self.book.worksheets[0])
                except AttributeError:

                    # compat - for openpyxl <= 2.4
                    self.book.remove_sheet(self.book.worksheets[0]) 

def test_peel_the_onion_CRS_multi_output_oriented(DEA_example2_data):
    model = _create_large_model_CRS_multi_output_oriented_with_non_discretionary(
        DEA_example2_data)
    start_time = datetime.datetime.now()
    solution, ranks, state = peel_the_onion_method(model)
    end_time = datetime.datetime.now()
    _check_large_model_CRS_multi_output_oriented_with_non_discretionary(
        solution, model.input_data)
    dmus = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K']
    expected_ranks = [1, 3, 2, 1, 3, 1, 1, 2, 1, 2, 1]
    utils_for_tests.check_onion_ranks(
        model.input_data, dmus, expected_ranks, ranks)

    work_book = xlwt.Workbook()
    ranks_as_list = []
    ranks_as_list.append(ranks)
    writer = XLSWriter(Parameters(), work_book, datetime.datetime.today(),
                       (end_time - start_time).total_seconds(),
                       ranks=ranks_as_list)
    writer.write_data(solution)
    work_book.save('tests/test_peel_the_onion.xls') 

def test_virual_restrictions_env_model(data):
    model = EnvelopmentModelBase(data,
                                 EnvelopmentModelInputOriented(
                                     generate_upper_bound_for_efficiency_score),
                                 DefaultConstraintCreator())

    bounds = {'I2': (0.01, 0.5)}
    model = EnvelopmentModelWithVirtualWeightRestrictions(model, bounds)
    start_time = datetime.datetime.now()
    model_solution = model.run()
    end_time = datetime.datetime.now()
    utils_for_tests.check_if_category_is_within_virtual_limits(
        model_solution, bounds)

    work_book = xlwt.Workbook()
    writer = XLSWriter(Parameters(), work_book, datetime.datetime.today(),
                       (end_time - start_time).total_seconds())
    writer.write_data(model_solution)
    work_book.save('tests/test_virtual_constraints_env_output.xls') 

def write_excel_xls(path, sheet_name_list, value):
    # 新建一个工作簿
    workbook = xlwt.Workbook()

    # 获取需要写入数据的行数
    index = len(value)

    for sheet_name in sheet_name_list:

        # 在工作簿中新建一个表格
        sheet = workbook.add_sheet(sheet_name)

        # 往这个工作簿的表格中写入数据
        for i in range(0, index):
            for j in range(0, len(value[i])):
                sheet.write(i, j, value[i][j])

    # 保存工作簿
    workbook.save(path) 

def export_topology(self, **kwargs):
        workbook = Workbook()
        filename = kwargs["export_filename"]
        if "." not in filename:
            filename += ".xls"
        for obj_type in ("device", "link"):
            sheet = workbook.add_sheet(obj_type)
            for index, property in enumerate(model_properties[obj_type]):
                if property in db.dont_migrate[obj_type]:
                    continue
                sheet.write(0, index, property)
                for obj_index, obj in enumerate(db.fetch_all(obj_type), 1):
                    value = getattr(obj, property)
                    if type(value) == bytes:
                        value = str(self.decrypt(value), "utf-8")
                    sheet.write(obj_index, index, str(value))
        workbook.save(self.path / "files" / "spreadsheets" / filename) 

def test_virtual_weight_restrictions_multiplier_model(data):

    model = MultiplierModelBase(data, 0,
                                MultiplierInputOrientedModel())

    bounds = {'I1': (None, 0.5)}
    model = MultiplierModelWithVirtualWeightRestrictions(model, bounds)
    start_time = datetime.datetime.now()
    model_solution = model.run()
    end_time = datetime.datetime.now()
    utils_for_tests.check_if_category_is_within_virtual_limits(
        model_solution, bounds)

    work_book = xlwt.Workbook()
    writer = XLSWriter(Parameters(), work_book, datetime.datetime.today(),
                       (end_time - start_time).total_seconds())
    writer.write_data(model_solution)
    work_book.save('tests/test_virtual_weights_multi_output.xls') 

def __init__(self, path, engine=None,
                 date_format=None, datetime_format=None, mode='w',
                 **engine_kwargs):
        # Use the xlsxwriter module as the Excel writer.
        import xlsxwriter

        if mode == 'a':
            raise ValueError('Append mode is not supported with xlsxwriter!')

        super(_XlsxWriter, self).__init__(path, engine=engine,
                                          date_format=date_format,
                                          datetime_format=datetime_format,
                                          mode=mode,
                                          **engine_kwargs)

        self.book = xlsxwriter.Workbook(path, **engine_kwargs) 

def __init__(self, path, engine=None, mode='w', **engine_kwargs):
        # Use the openpyxl module as the Excel writer.
        from openpyxl.workbook import Workbook

        super(_OpenpyxlWriter, self).__init__(path, mode=mode, **engine_kwargs)

        if self.mode == 'a':  # Load from existing workbook
            from openpyxl import load_workbook
            book = load_workbook(self.path)
            self.book = book
        else:
            # Create workbook object with default optimized_write=True.
            self.book = Workbook()

            if self.book.worksheets:
                try:
                    self.book.remove(self.book.worksheets[0])
                except AttributeError:

                    # compat - for openpyxl <= 2.4
                    self.book.remove_sheet(self.book.worksheets[0]) 

def test_price_ratio_multiplier_model(data):

    model = MultiplierModelBase(data, 0,
                                MultiplierInputOrientedModel())

    bounds = {('I1', 'I2'): (None, 0.4), ('O1', 'O2'): (0.01, None)}
    model = MultiplierModelWithPriceRatioConstraints(model, bounds)
    start_time = datetime.datetime.now()
    model_solution = model.run()
    end_time = datetime.datetime.now()
    utils_for_tests.check_if_category_is_within_price_ratio_constraints(
        model_solution, bounds)

    work_book = xlwt.Workbook()
    writer = XLSWriter(Parameters(), work_book, datetime.datetime.today(),
                       (end_time - start_time).total_seconds())
    writer.write_data(model_solution)
    work_book.save('tests/test_price_ratio_multi_output.xls') 

def __init__(self):
        # count用于计数excel行
        self.excel = xlwt.Workbook(encoding='utf8')
        self.sheet = self.excel.add_sheet('文献列表', True)
        self.set_style()
        self.sheet.write(0, 0, '序号', self.basic_style)
        self.sheet.write(0, 1, '题名', self.basic_style)
        self.sheet.write(0, 2, '作者', self.basic_style)
        self.sheet.write(0, 3, '单位', self.basic_style)
        self.sheet.write(0, 4, '关键字', self.basic_style)
        self.sheet.write(0, 5, '摘要', self.basic_style)
        self.sheet.write(0, 6, '来源', self.basic_style)
        self.sheet.write(0, 7, '发表时间', self.basic_style)
        self.sheet.write(0, 8, '数据库', self.basic_style)
        if config.crawl_isDownLoadLink == '1':
            self.sheet.write(0, 9, '下载地址', self.basic_style)

        # 生成userKey,服务器不做验证
        self.cnkiUserKey = self.set_new_guid() 

def test_abs_restrictions_env_model_output(data):
    filename = 'tests/DEA_Harish_parameters.txt'
    params = parse_parameters_from_file(filename)
    categories, data, dmu_name, sheet_name = read_data(
        params.get_parameter_value('DATA_FILE'))
    coefficients, has_same_dmus = convert_to_dictionary(data)
    model_input = construct_input_data_instance(categories, coefficients)
    models, all_params = build_models(params, model_input)
    assert len(models) == 1 and len(all_params) == 1
    model = models[0]

    start_time = datetime.datetime.now()
    model_solution = model.run()
    end_time = datetime.datetime.now()
    bounds = {'Urban Roads (%)': (None, 0.003)}
    utils_for_tests.check_if_category_is_within_abs_limits(
        model_solution, bounds)

    work_book = xlwt.Workbook()
    writer = XLSWriter(Parameters(), work_book, datetime.datetime.today(),
                       (end_time - start_time).total_seconds())
    writer.write_data(model_solution)
    work_book.save('tests/test_abs_constraints_env_outoriented_output.xls') 

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_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 db_doc():
    wb = xlwt.Workbook(encoding='utf-8', style_compression=2)
    for db in cfg_db_list:
        print(db, "start generating......")
        write_excel(wb, db)
        print(db, "finished",
              "\n=========================================================")

    if os.path.exists(fname):
        os.remove(fname)
    wb.save(fname)

    print("Document has been generated") 

def gen_xls(l,filename):
    fp = xlwt.Workbook()
    table = fp.add_sheet('numbers',cell_overwrite_ok=True)
    #列表的遍历，用index真的好方便，内存循环踩了个坑，要改用row而不是l作为索引对象
    for row in l:
        for col in row:
            table.write(l.index(row),row.index(col),col)  #row表示行，col表示列，后者的英文不一定匹配，我还是再查找下，list.index()可以得到列表中对于元素的索引值
    fp.save('numbers.xls')
    print '写入完毕'

#主函数，我猜这次返回的应该是列表吧 

def gen_xls(d,filename):
    fp = xlwt.Workbook()
    table = fp.add_sheet('student',cell_overwrite_ok=True)
    #试了下，与很多要转utf-8（ASCII码）存文件的情况不同，xls不接受ASCII码形式的存储，直接用字典里面的Unicode就行了，简直好评，不用在特意decode或者encode了
    #想写得更加自动化一些，好复用.本身不太想用两层循环的，不过也不知道有没有更便捷的存储方式（比如整行自动匹配导入，算法是背后优化封装好的，就用了万能的这种方法）
    for n in range(len(d)):
        table.write(n,0,n+1)
        m = 0
        for record in d[str(n+1)]:
            table.write(n,m+1,record)
            m += 1
    fp.save('student.xls')
    print '写入完毕'

#主函数，嘛，最后还是用“丑陋的二重循环”实现了，但是其实也没什么，还是要看场景和优化，毕竟这也不是做查找或者排序，在日常使用中也不用太担心性能问题 

def gen_xls(d,filename):
    fp = xlwt.Workbook()
    table = fp.add_sheet('city',cell_overwrite_ok=True)
    #这次一次循环就可以了
    for n in range(len(d)):
        table.write(n,0,n+1)
        table.write(n,1,d[str(n+1)])
    fp.save('city.xls')
    print '写入完毕'

#主函数 

def export(request):
    if request.method == "GET":
        asset_list = models.Assets.objects.all()
        bt = ['ID','主机名','外网地址','内网地址','系统类型','机房','ServerID','GameID','角色','创建时间','更新时间','是否启用','备注']
        wb = xlwt.Workbook(encoding='utf-8')
        sh = wb.add_sheet("主机详情",cell_overwrite_ok=True)
        dateFormat = xlwt.XFStyle()
        dateFormat.num_format_str = 'yyyy/mm/dd'
        for i in range(len(bt)):
            sh.write(0,i,bt[i])
        for i in range(len(asset_list)):
            sh.write(i + 1, 0, asset_list[i].id)
            sh.write(i + 1, 1, asset_list[i].hostname)
            sh.write(i + 1, 2, asset_list[i].wip)
            sh.write(i + 1, 3, asset_list[i].lip)
            sh.write(i + 1, 4, asset_list[i].system_type)
            for idc in asset_list[i].idc_set.all():
                sh.write(i + 1, 5, idc.name)
            sh.write(i + 1, 6, asset_list[i].serverid)
            sh.write(i + 1, 7, asset_list[i].gameid)
            for g in asset_list[i].hostgroup_set.all():
                sh.write(i + 1, 8, g.name)
            sh.write(i + 1, 9, asset_list[i].ctime,dateFormat)
            sh.write(i + 1, 10, asset_list[i].utime,dateFormat)
            sh.write(i + 1, 11, asset_list[i].get_online_status_display())
            sh.write(i + 1, 12, asset_list[i].memo)
        response = HttpResponse(content_type='application/vnd.ms-excel')
        response['Content-Disposition'] = 'attachment; filename=asset' + time.strftime('%Y%m%d', time.localtime(
                time.time())) + '.xls'
        wb.save(response)

        return response 

def export_excel():
    username = window.username.text()
    now = datetime.now()
    nowStr = now.strftime("%Y-%m-%d--%H-%M-%S")
    imgfileName = '%s %s.bmp' % (username, nowStr)
    xlsfilename = '%s %s.xls' % (username, nowStr)
    window.plot.getImage(imgfileName)
    fieldnames = ["سطر","نام", "تخفیف", " قیمت کل", "تعداد", "تاریخ"]
        
    book = xlwt.Workbook(encoding="utf-8")
    sheet = book.add_sheet(username)
    n = 0
    for field in fieldnames:
        sheet.write(0, n, field)
        n = n + 1
    n = 1
    for date, name, num, price, discount in window.all_orders:
        this_product_total_price = (price * num) - discount
        sheet.write(n,0,"%s" % n)
        sheet.write(n,1,"%s" % name)
        sheet.write(n,2,"%s" % discount)
        sheet.write(n,3,"%s" % this_product_total_price)
        sheet.write(n,4,"%s" % num)
        sheet.write(n,5,"%s" % date)
        n = n + 1
    sheet.write(n+2, 1, "نمودار هزینه های انجام شده")
    sheet.insert_bitmap(imgfileName, n+2, 3)
    book.save(xlsfilename)
    os.remove(imgfileName) 

def __init__(self, path, engine=None, encoding=None, mode='w',
                 **engine_kwargs):
        # Use the xlwt module as the Excel writer.
        import xlwt
        engine_kwargs['engine'] = engine

        if mode == 'a':
            raise ValueError('Append mode is not supported with xlwt!')

        super(_XlwtWriter, self).__init__(path, mode=mode, **engine_kwargs)

        if encoding is None:
            encoding = 'ascii'
        self.book = xlwt.Workbook(encoding=encoding)
        self.fm_datetime = xlwt.easyxf(num_format_str=self.datetime_format)
        self.fm_date = xlwt.easyxf(num_format_str=self.date_format)