Hướng dẫn how do i write pandas dataframe in excel using xlsxwriter? - làm cách nào để viết khung dữ liệu gấu trúc trong excel bằng xlsxwriter?

Python Pandas là một thư viện phân tích dữ liệu Python. Nó có thể đọc, lọc và sắp xếp lại các bộ dữ liệu nhỏ và lớn và xuất chúng theo một loạt các định dạng bao gồm Excel.

Pandas ghi các tệp Excel bằng mô -đun XLWT cho các tệp XLS và các mô -đun OpenPyXL hoặc XLSXWriter cho các tệp XLSX.

Sử dụng xlsxwriter với gấu trúc

Để sử dụng xlsxwriter với gấu trúc, bạn chỉ định nó là công cụ viết Excel:

import pandas as pd

# Create a Pandas dataframe from the data.
df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')

# Close the Pandas Excel writer and output the Excel file.
writer.save()

Đầu ra từ điều này sẽ giống như sau:

Xem ví dụ đầy đủ tại ví dụ: Ví dụ về Pandas Excel.Example: Pandas Excel example.

Truy cập xlsxwriter từ gấu trúc

Để áp dụng các tính năng xlsxwriter như biểu đồ, định dạng có điều kiện và định dạng cột cho đầu ra gấu trúc, chúng ta cần truy cập các đối tượng sổ làm việc và bảng tính cơ bản. Sau đó, chúng ta có thể coi chúng như các đối tượng XLSXWriter bình thường.workbook and worksheet objects. After that we can treat them as normal XlsxWriter objects.

Tiếp tục từ ví dụ trên, chúng tôi làm điều đó như sau:

import pandas as pd

# Create a Pandas dataframe from the data.
df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')

# Get the xlsxwriter objects from the dataframe writer object.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

Điều này tương đương với mã sau khi tự sử dụng XLSXWRITER:

workbook  = xlsxwriter.Workbook('filename.xlsx')
worksheet = workbook.add_worksheet()

Các đối tượng sổ làm việc và bảng tính sau đó có thể được sử dụng để truy cập các tính năng xlsxwriter khác, xem bên dưới.

Thêm biểu đồ vào đầu ra DataFrame

Khi chúng tôi có các đối tượng sổ làm việc và bảng tính, như được hiển thị trong phần trước, chúng tôi có thể sử dụng chúng để áp dụng các tính năng khác như thêm biểu đồ:

# Get the xlsxwriter objects from the dataframe writer object.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Create a chart object.
chart = workbook.add_chart({'type': 'column'})

# Get the dimensions of the dataframe.
(max_row, max_col) = df.shape

# Configure the series of the chart from the dataframe data.
chart.add_series({'values': ['Sheet1', 1, 1, max_row, 1]})

# Insert the chart into the worksheet.
worksheet.insert_chart(1, 3, chart)

Đầu ra sẽ trông như thế này:

Xem ví dụ đầy đủ tại ví dụ: Pandas Excel đầu ra với biểu đồ.Example: Pandas Excel output with a chart.

Định dạng đầu ra DataFrame

XLSXWRITER và PANDA cung cấp rất ít hỗ trợ để định dạng dữ liệu đầu ra từ DataFrame ngoài định dạng mặc định như tiêu đề và ô chỉ mục và bất kỳ ô nào có chứa ngày hoặc dữ liệu. Ngoài ra, không thể định dạng bất kỳ ô nào đã có định dạng mặc định được áp dụng.

Nếu bạn yêu cầu định dạng rất được kiểm soát của đầu ra DataFrame thì có lẽ bạn sẽ tốt hơn khi sử dụng XLSXWriter trực tiếp với dữ liệu thô được lấy từ Pandas. Tuy nhiên, một số tùy chọn định dạng có sẵn.

Ví dụ: có thể đặt các định dạng ngày và DateTime mặc định thông qua giao diện Pandas:

writer = pd.ExcelWriter("pandas_datetime.xlsx",
                        engine='xlsxwriter',
                        datetime_format='mmm d yyyy hh:mm:ss',
                        date_format='mmmm dd yyyy')

Mà sẽ cho:

Xem ví dụ đầy đủ tại ví dụ: Pandas Excel đầu ra với Datetimes.Example: Pandas Excel output with datetimes.

Có thể định dạng bất kỳ dữ liệu cột/ngày DateTime nào khác bằng cách sử dụng

import pandas as pd

# Create a Pandas dataframe from the data.
df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')

# Get the xlsxwriter objects from the dataframe writer object.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Add some cell formats.
format1 = workbook.add_format({'num_format': '#,##0.00'})
format2 = workbook.add_format({'num_format': '0%'})

# Set the column width and format.
worksheet.set_column(1, 1, 18, format1)

# Set the format but not the column width.
worksheet.set_column(2, 2, None, format2)

Xem ví dụ đầy đủ tại ví dụ: Pandas Excel đầu ra với định dạng cột.Example: Pandas Excel output with column formatting.

Thêm một khung dữ liệu vào bảng bảng tính

Như đã giải thích khi làm việc với các bảng bảng tính, các bảng trong Excel là một cách để nhóm một loạt các ô thành một thực thể duy nhất, như thế này:Working with Worksheet Tables, tables in Excel are a way of grouping a range of cells into a single entity, like this:

Cách để thực hiện điều này với một gấu trúc DataFrame là viết dữ liệu mà không cần chỉ mục hoặc tiêu đề và bằng cách bắt đầu 1 hàng về phía trước để cho phép không gian cho tiêu đề bảng:

df.to_excel(writer, sheet_name='Sheet1',
            startrow=1, header=False, index=False)

Sau đó, chúng tôi tạo một danh sách các tiêu đề để sử dụng trong

import pandas as pd

# Create a Pandas dataframe from the data.
df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')

# Get the xlsxwriter objects from the dataframe writer object.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

column_settings = [{'header': column} for column in df.columns]

Cuối cùng, chúng tôi thêm cấu trúc bảng Excel, dựa trên hình dạng DataFrame và với các tiêu đề cột chúng tôi đã tạo từ các cột DataFrame:

(max_row, max_col) = df.shape

worksheet.add_table(0, 0, max_row, max_col - 1, {'columns': column_settings})

Xem ví dụ đầy đủ tại ví dụ: Pandas Excel đầu ra với bảng bảng tính.Example: Pandas Excel output with a worksheet table.

Thêm một bộ lọc tự động vào đầu ra DataFrame

Như được giải thích khi làm việc với các bộ lọc tự động, các bộ lọc tự động trong Excel là một cách để lọc phạm vi dữ liệu 2D để chỉ hiển thị các hàng phù hợp với các tiêu chí do người dùng xác định.Working with Autofilters, autofilters in Excel are a way of filtering a 2d range of data to only display rows that match a user defined criteria.

Cách để thực hiện điều này với một gấu trúc DataFrame là trước tiên viết dữ liệu mà không cần chỉ mục (trừ khi bạn muốn đưa nó vào dữ liệu được lọc):

df.to_excel(writer, sheet_name='Sheet1', index=False)

Sau đó chúng tôi có được hình dạng DataFrame và thêm bộ lọc tự động:

import pandas as pd

# Create a Pandas dataframe from the data.
df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')

# Get the xlsxwriter objects from the dataframe writer object.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

Chúng tôi cũng có thể thêm một tiêu chí lọc tùy chọn. Vùng đặt chỗ ở khu vực trong bộ lọc bị bỏ qua và có thể là bất kỳ chuỗi nào thêm sự rõ ràng cho biểu thức:

import pandas as pd

# Create a Pandas dataframe from the data.
df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')

# Get the xlsxwriter objects from the dataframe writer object.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

Tuy nhiên, nó không đủ để áp dụng các tiêu chí. Các hàng mà trận đấu don don cũng phải được ẩn. Chúng tôi sử dụng gấu trúc để tìm ra các hàng nào để ẩn:

import pandas as pd

# Create a Pandas dataframe from the data.
df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')

# Get the xlsxwriter objects from the dataframe writer object.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

Điều này cung cấp cho chúng tôi một bảng tính được lọc như thế này:

Xem ví dụ đầy đủ tại ví dụ: Pandas Excel đầu ra với một bộ lọc tự động.Example: Pandas Excel output with an autofilter.

Xử lý nhiều khung dữ liệu gấu trúc

Có thể viết nhiều hơn một DataFrame vào một bảng tính hoặc vào một số bảng tính. Ví dụ: để ghi nhiều khung dữ liệu vào nhiều bảng tính:

import pandas as pd

# Create a Pandas dataframe from the data.
df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')

# Get the xlsxwriter objects from the dataframe writer object.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

Xem ví dụ đầy đủ tại ví dụ: Pandas Excel với nhiều khung dữ liệu.Example: Pandas Excel with multiple dataframes.

Cũng có thể định vị nhiều khung dữ liệu trong cùng một bảng tính:

import pandas as pd

# Create a Pandas dataframe from the data.
df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')

# Get the xlsxwriter objects from the dataframe writer object.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

Xem ví dụ đầy đủ tại ví dụ: Pandas Excel DataFrame Định vị.Example: Pandas Excel dataframe positioning.

Chuyển các tùy chọn cấu trúc XLSXWRITER cho gấu trúc cho gấu trúc

XLSXWriter hỗ trợ một số tùy chọn xây dựng

workbook  = xlsxwriter.Workbook('filename.xlsx')
worksheet = workbook.add_worksheet()

workbook  = xlsxwriter.Workbook('filename.xlsx')
worksheet = workbook.add_worksheet()

workbook  = xlsxwriter.Workbook('filename.xlsx')
worksheet = workbook.add_worksheet()

workbook  = xlsxwriter.Workbook('filename.xlsx')
worksheet = workbook.add_worksheet()

import pandas as pd

# Create a Pandas dataframe from the data.
df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')

# Get the xlsxwriter objects from the dataframe writer object.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

Lưu ý, các phiên bản của gấu trúc trước 1.3.0 đã sử dụng cú pháp này:

import pandas as pd

# Create a Pandas dataframe from the data.
df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')

# Get the xlsxwriter objects from the dataframe writer object.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

Lưu đầu ra DataFrame vào một chuỗi

Cũng có thể viết Pandas XLSXWriter DataFrame đầu ra vào mảng byte:

import pandas as pd

# Create a Pandas dataframe from the data.
df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')

# Get the xlsxwriter objects from the dataframe writer object.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

Lưu ý: Tính năng này yêu cầu gấu trúc> = 0,17.

Làm cách nào để viết gấu trúc DataFrame trong Excel?

Làm cách nào để viết dữ liệu trong Excel bằng XLSXWRITER?

Pandas có sử dụng xlsxwriter không?

Làm cách nào để ghi dữ liệu vào bảng tính Excel bằng Python?