10 examples of 'pandas write to existing excel file' in Python

49
def write(self, row_n, col_n, value):
50
    '''写入数据，如(2,3，"hello"),第二行第三列写入数据"hello"'''
51
    self.ws.cell(row_n, col_n).value = value
52
    self.wb.save(self.filename)

84
def WriteToExcel():
85
    f = open('wordbank.csv','w')
86
    list = CountInstancesGlobal()
87
    for twoplet in list:
88
        f.write(str(twoplet[0]))
89
        f.write(',')
90
        f.write(str(twoplet[1]))
91
        f.write('\n')
92
    f.close()

68
def writeExcel(write_infos_mul, excel_path, sheetNames=None, set_color=False):
69
    workbook = xlwt.Workbook()
70
    # style = xlwt.easyxf('align: wrap on')
71
    style1 = xlwt.easyxf('pattern: pattern solid, fore_colour red;')
72
    for ind, write_infos in enumerate(write_infos_mul):
73
        if sheetNames is None:
74
            sheetname = str(ind)
75
        else:
76
            sheetname = sheetNames[ind]
77
        sheetname = decode_strs(sheetname)
78
        writeSheet = workbook.add_sheet(sheetname)
79
        for row, item_infos in enumerate(write_infos):
80
            unnormal_shop = item_infos[-2]
81
            for idx, info in enumerate(item_infos):
82
                if type(info) == type('sss'):
83
                    info = decode_strs(info)
84
                writeSheet.write(row, idx, info)
85
    workbook.save(excel_path)

634
def export_matrix_to_excel(row_names, col_names, matrix, filepath='export.xlsx', sheetname='Export'):
635
    workbook = xlsxwriter.Workbook(filepath)
636
    ws = workbook.add_worksheet(sheetname)
637
    # formatting
638
    # border
639
    format_border = workbook.add_format()
640
    format_border.set_border(1)
641
    format_border.set_font_size(9)
642
    # border + text wrap
643
    format_border_text_wrap = workbook.add_format()
644
    format_border_text_wrap.set_text_wrap()
645
    format_border_text_wrap.set_border(1)
646
    format_border_text_wrap.set_font_size(9)
647
    # set column width
648
    ws.set_column(0, 1, width=15, cell_format=None)
649
    ws.set_column(1, 50, width=9, cell_format=None)
650
    # write data
651
    for i, p in enumerate(col_names):  # process names
652
        ws.write(0, i+1, p, format_border_text_wrap)
653
    for i, p in enumerate(row_names):  # product names
654
        ws.write(i+1, 0, p, format_border)
655
    for i, row in enumerate(range(matrix.shape[0])):  # matrix
656
        ws.write_row(i+1, 1, matrix[i, :], format_border)
657
    workbook.close()

133
def save_workbook(workbook, filename):
134
    """Save the given workbook on the filesystem under the name filename.
135
136
    :param workbook: the workbook to save
137
    :type workbook: :class:`openpyxl.workbook.Workbook`
138
139
    :param filename: the path to which save the workbook
140
    :type filename: string
141
142
    :rtype: bool
143
144
    """
145
    writer = ExcelWriter(workbook)
146
    writer.save(filename)
147
    return True

16
def copy_excel(excelpath1, excelpath2):
17
    """复制excel，把 excelpath1 数据复制到 excelpath2"""
18
    wb2 = openpyxl.Workbook()
19
    wb2.save(excelpath2)
20
    # 读取数据
21
    wb1 = openpyxl.load_workbook(excelpath1)
22
    wb2 = openpyxl.load_workbook(excelpath2)
23
    sheets1 = wb1.sheetnames
24
    sheets2 = wb2.sheetnames
25
    sheet1 = wb1[sheets1[0]]
26
    sheet2 = wb2[sheets2[0]]
27
    max_row = sheet1.max_row         # 最大行数
28
    max_column = sheet1.max_column   # 最大列数
29
    for m in list(range(1, max_row + 1)):
30
        for n in list(range(97, 97 + max_column)):   # chr(97)='a'
31
            n = chr(n)                            # ASCII字符
32
            i = '%s%d' % (n, m)                     # 单元格编号
33
            cell1 = sheet1[i].value               # 获取data单元格数据
34
            sheet2[i].value = cell1               # 赋值到test单元格
35
    wb2.save(excelpath2)                 # 保存数据
36
    wb1.close()                          # 关闭excel
37
    wb2.close()

187
@staticmethod
188
def _save_json(out_file, out_data: OrderedDict):
189
    """
190
191
    :param str|Path out_file:
192
    :param out_data:
193
    :return:
194
    """
195
    if not isinstance(out_file, Path):
196
        out_file = Path(out_file)
197
198
    with out_file.open('w', encoding='utf8') as f:
199
        export_string = json.dumps(out_data, cls=MyEncoder,
200
                                   indent=2, ensure_ascii=False)
201
        f.write(export_string)

39
def _save_output_workbook(self):
40
    """ Saves output file.
41
    """
42
    self._output_wb.save(self._output_file)

9
def test_write_texttable():
10
    content = [[1, 2]]
11
    test_file = "test.texttable"
12
    expected = dedent(
13
        """
14
    pyexcel_sheet1:
15
    +---+---+
16
    | 1 | 2 |
17
    +---+---+"""
18
    ).strip("\n")
19
    pe.save_as(array=content, dest_file_name=test_file)
20
    with open(test_file, "r") as f:
21
        written = f.read()
22
        eq_(written, expected)
23
    os.unlink(test_file)

32
def write_sheet(self, name, objects):
33
    excel_sheet = self._sheet_map[name]
34
    worksheet = self.get_or_create_sheet(excel_sheet)
35
    excel_sheet.write(worksheet, self.styles, objects)
36
37
    self.update_active_sheet()