Python и Excel

Введение

Примеры

  • Добавление данных в файл Excel

    import os, sys
    from openpyxl import Workbook
    from datetime import datetime
    
    dt = datetime.now()
    list_values = [["01/01/2016", "05:00:00", 3], \
    ["01/02/2016", "06:00:00", 4], \
    ["01/03/2016", "07:00:00", 5], \
    ["01/04/2016", "08:00:00", 6], \
    ["01/05/2016", "09:00:00", 7]]
    
    # Создать рабочую книгу в Excel:
    wb = Workbook()
    sheet = wb.active
    sheet.title = 'data'
    
    # Добавить заголовки в рабочую книгу Excel:
    row = 1
    sheet['A'+str(row)] = 'Date'
    sheet['B'+str(row)] = 'Hour'
    sheet['C'+str(row)] = 'Value'
    
    # Заполнить данными
    for item in list_values:
    row += 1
    sheet['A'+str(row)] = item[0]
    sheet['B'+str(row)] = item[1]
    sheet['C'+str(row)] = item[2]
    
    # Сохранить файл:
    filename = 'data_' + dt.strftime("%Y%m%d_%I%M%S") + '.xlsx'
    wb.save(filename)
    
    # Открыть файл для пользователя:
    os.chdir(sys.path[0])
    os.system('start excel.exe "%s\\%s"' % (sys.path[0], filename, ))
  • OpenPyXL

    OpenPyXL — это модуль для создания и работы с рабочими книгами xlsx/xlsm/xltx/xltm в памяти.

    import openpyxl as opx
    #Чтобы изменить существующую книгу, мы указали к ней путь
    workbook = opx.load_workbook(workbook_path)

    

    load_workbook() содержит параметр read_only. Установив значение True, он загрузит книгу в режиме read_only. Загружать книгу с этим параметром удобно при чтении больших файлов xlsx:

    workbook = opx.load_workbook(workbook_path, read_only=True)

    

    После того, как вы загрузили книгу в память, вы можете получить доступ к отдельным листам с помощью workbook.sheets

    first_sheet = workbook.worksheets[0]

    

    Если вы хотите указать имя доступных листов, вы можете использовать workbook.get_sheet_names().

    sheet = workbook.get_sheet_by_name('Sheet Name')

    

    Строки листа могут быть доступны с помощью команды sheet.rows. Чтобы перебрать строки на листе, используйте:

    for row in sheet.rows:
    print row[0].value

    

    Поскольку каждая row в строках является списком cell (ячеек), нужно использовать команду Cell.value для получения содержимого ячейки.

    

    Создание новой рабочей книги

    # Вызов функции Workbook() создаёт новую книгу
    wb = opx.Workbook()
    
    # Затем мы можем создать новый лист в рабочей книге
    ws = wb.create_sheet('Sheet Name', 0) #0 refers to the index of the sheet order in the wb

    

    В openpyxl можно менять свойства вкладки, например tabColor:

    ws.sheet_properties.tabColor = 'FFC0CB'

    

    Чтобы сохранить созданную книгу, пишем:

    wb.save('filename.xlsx')
  • Создание графика в Excel с помощью xlsxwriter

    import xlsxwriter
    
    # пример данных
    chart_data = [
    {'name': 'Lorem', 'value': 23},
    {'name': 'Ipsum', 'value': 48},
    {'name': 'Dolor', 'value': 15},
    {'name': 'Sit', 'value': 8},
    {'name': 'Amet', 'value': 32}
    ]
    
    # путь к XLS-файлу
    xls_file = 'chart.xlsx'
    
    # рабочая книга
    workbook = xlsxwriter.Workbook(xls_file)
    
    # добавить новый лист в книгу
    worksheet = workbook.add_worksheet()
    
    row_ = 0
    col_ = 0
    
    # написать заголовки
    worksheet.write(row_, col_, 'NAME')
    col_ += 1
    worksheet.write(row_, col_, 'VALUE')
    row_ += 1
    
    # вписать пример данных
    for item in chart_data:
    col_ = 0
    worksheet.write(row_, col_, item['name'])
    col_ += 1
    worksheet.write(row_, col_, item['value'])
    row_ += 1
    
    # создать круговую диаграмму
    pie_chart = workbook.add_chart({'type': 'pie'})
    
    # добавить ряды
    pie_chart.add_series({
    'name': 'Series Name',
    'categories': '=Sheet1!$A$3:$A$%s' % row_,
    'values': '=Sheet1!$B$3:$B$%s' % row_,
    'marker': {'type': 'circle'}
    })
    # вставить круговую диаграмму
    worksheet.insert_chart('D2', pie_chart)
    
    # добавить столбцовую диаграмму
    column_chart = workbook.add_chart({'type': 'column'})
    
    # добавить ряд к диаграмме
    column_chart.add_series({
    'name': 'Series Name',
    'categories': '=Sheet1!$A$3:$A$%s' % row_,
    'values': '=Sheet1!$B$3:$B$%s' % row_,
    'marker': {'type': 'circle'}
    })
    # вставить столбцовую диаграмму
    worksheet.insert_chart('D20', column_chart)
    
    workbook.close()

    

    Результат:

    

  • Как читать данные в Excel, с помощью модуля xlrd

    Библиотека Python xlrd предназначена для извлечения данных из файлов Excel-таблиц.

    

    Установка:

    pip install xlrd

    

    Или можно исопльзовать файл setup.py из pypi

    https://pypi.python.org/pypi/xlrd

    

    Чтение листа Excel: Импортируйте модуль xlrd и откройте Excel-файл, используя метод open_workbook().

    import xlrd
    book=xlrd.open_workbook('sample.xlsx')

    Проверка количества листов в Excel

    print book.nsheets

    

    Вывод названий листов

    print book.sheet_names()

    

    Получить лист из значения индекса

    sheet=book.sheet_by_index(1)

    

    Прочитать содержимое ячейки

    cell = sheet.cell(row,col) #where row=row number and col=column number
    print cell.value #to print the cell contents

    

    Получить количество строк и количество столбцов в листе Excel

    num_rows=sheet.nrows
    num_col=sheet.ncols

    

    Получить лист Excel с именем

    sheets = book.sheet_names()
    cur_sheet = book.sheet_by_name(sheets[0])

    

  • Форматирование Excel-файлов с помощью xlsxwriter

    import xlsxwriter
    
    # создать новый файл
    workbook = xlsxwriter.Workbook('your_file.xlsx')
    
    # add some new formats to be used by the workbook
    percent_format = workbook.add_format({'num_format': '0%'})
    percent_with_decimal = workbook.add_format({'num_format': '0.0%'})
    bold = workbook.add_format({'bold': True})
    red_font = workbook.add_format({'font_color': 'red'})
    remove_format = workbook.add_format()
    
    # добавить новый лист
    worksheet = workbook.add_worksheet()
    
    # установить ширину столбца A
    worksheet.set_column('A:A', 30, )
    
    # установить столбец B на 20 и установить формат процентов для ячеек этого столбца
    worksheet.set_column('B:B', 20, percent_format)
    
    # убрать форрматирование у первого ряда (изменения высоты=None)
    worksheet.set_row('0:0', None, remove_format)
    
    workbook.close()

Синтаксис

Параметры

Примечания