9 examples of 'openpyxl write to cell' in Python

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

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)

190
def write_worksheet_cols(doc, worksheet, style_table):
191
    """Write worksheet columns to xml."""
192
    if worksheet.column_dimensions:
193
        start_tag(doc, 'cols')
194
        for column_string, columndimension in \
195
            sorted(iteritems(worksheet.column_dimensions)):
196
            col_index = column_index_from_string(column_string)
197
            col_def = {'min': str(col_index), 'max': str(col_index)}
198
            if columndimension.width != -1:
199
                col_def['customWidth'] = '1'
200
            if not columndimension.visible:
201
                col_def['hidden'] = 'true'
202
            if columndimension.outline_level > 0:
203
                col_def['outlineLevel'] = str(columndimension.outline_level)
204
            if columndimension.collapsed:
205
                col_def['collapsed'] = 'true'
206
            if columndimension.auto_size:
207
                col_def['bestFit'] = 'true'
208
            if column_string in worksheet._styles:
209
                col_def['style'] = str(style_table[hash(worksheet.get_style(column_string))])
210
            if columndimension.width > 0:
211
                col_def['width'] = str(columndimension.width)
212
            else:
213
                col_def['width'] = '9.10'
214
            tag(doc, 'col', col_def)
215
        end_tag(doc, 'cols')

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

228
def write(r, c, val):
229
    widths.setdefault(c, 0)
230
    widths[c] = max(widths[c], self.calc_width(val))
231
232
    fmt = formats.get(type(val))
233
    if fmt:
234
        worksheet.write_datetime(r, c, val, fmt)
235
    elif r == 0:
236
        worksheet.write(r, c, val, formats['header'])
237
    else:
238
        worksheet.write(r, c, val)

59
def from_excel(self, cell):
60
    raise NotImplementedError()

285
def get_range_of_cells(row_index=None, columns=None):
286
    if columns is None:
287
        start_letter = self._get_column_as_letter(sheet, self.data_df.columns[0], startcol)
288
        end_letter = self._get_column_as_letter(sheet, self.data_df.columns[-1], startcol)
289
    else:
290
        start_letter = self._get_column_as_letter(sheet, columns[0], startcol)
291
        end_letter = self._get_column_as_letter(sheet, columns[-1], startcol)
292
    if row_index is None:  # returns cells range for the entire dataframe
293
        start_index = startrow + 1
294
        end_index = start_index + len(self)
295
    else:
296
        start_index = startrow + row_index + 1
297
        end_index = start_index
298
    return '{start_letter}{start_index}:{end_letter}{end_index}'.format(start_letter=start_letter,
299
                                                                        start_index=start_index,
300
                                                                        end_letter=end_letter,
301
                                                                        end_index=end_index)

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

68
def worksheet_from_excel(excel_sheet):
69
    worksheet = Worksheet()
70
    for col in range(excel_sheet.ncols):
71
        for row in range(excel_sheet.nrows):
72
            cell = excel_sheet.cell(row, col)
73
            if cell.ctype == XL_CELL_ERROR:
74
                formula = '=%s' % (error_text_from_code[cell.value], )
75
            elif cell.ctype == XL_CELL_DATE:
76
                formula = '=DateTime(%s, %s, %s, %s, %s, %s)' % xldate_as_tuple(
77
                    cell.value, excel_sheet.book.datemode)
78
            else:
79
                formula = unicode(excel_sheet.cell(row, col).value)
80
            worksheet[col + 1, row + 1].formula = formula
81
    return worksheet