xlsx

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Excel/XLSX Manipulation

Excel/XLSX 文件编程处理

Working with Excel files programmatically.

通过编程方式处理Excel文件。

Python (openpyxl)

Python（openpyxl）

Reading Excel

读取Excel文件

python

from openpyxl import load_workbook

wb = load_workbook('data.xlsx')
ws = wb.active  # Get active sheet

python

from openpyxl import load_workbook

wb = load_workbook('data.xlsx')
ws = wb.active  # Get active sheet

Read cell

value = ws['A1'].value

Iterate rows

for row in ws.iter_rows(min_row=2, values_only=True): print(row)

undefined

for row in ws.iter_rows(min_row=2, values_only=True): print(row)

undefined

Writing Excel

写入Excel文件

python

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "Data"

python

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "Data"

Write data

ws['A1'] = 'Name' ws['B1'] = 'Age' ws.append(['John', 30]) ws.append(['Jane', 25])

wb.save('output.xlsx')

undefined

ws['A1'] = 'Name' ws['B1'] = 'Age' ws.append(['John', 30]) ws.append(['Jane', 25])

wb.save('output.xlsx')

undefined

Formatting

格式设置

python

from openpyxl.styles import Font, PatternFill

python

from openpyxl.styles import Font, PatternFill

Bold header

ws['A1'].font = Font(bold=True)

Background color

ws['A1'].fill = PatternFill(start_color="FFFF00", fill_type="solid")

Number format

ws['B2'].number_format = '0.00' # Two decimals

undefined

ws['B2'].number_format = '0.00' # Two decimals

undefined

Formulas

公式使用

python

undefined

python

undefined

Add formula

ws['C2'] = '=A2+B2'

Sum column

ws['D10'] = '=SUM(D2:D9)'

undefined

ws['D10'] = '=SUM(D2:D9)'

undefined

Python (pandas)

Python（pandas）

Reading Excel

读取Excel文件

python

import pandas as pd

python

import pandas as pd

Read sheet

df = pd.read_excel('data.xlsx', sheet_name='Sheet1')

Read multiple sheets

dfs = pd.read_excel('data.xlsx', sheet_name=None)

undefined

dfs = pd.read_excel('data.xlsx', sheet_name=None)

undefined

Writing Excel

写入Excel文件

python

undefined

python

undefined

Write DataFrame

df.to_excel('output.xlsx', index=False)

Multiple sheets

with pd.ExcelWriter('output.xlsx') as writer: df1.to_excel(writer, sheet_name='Sheet1') df2.to_excel(writer, sheet_name='Sheet2')

undefined

with pd.ExcelWriter('output.xlsx') as writer: df1.to_excel(writer, sheet_name='Sheet1') df2.to_excel(writer, sheet_name='Sheet2')

undefined

Data Transformation

数据转换

python

undefined

python

undefined

Filter

filtered = df[df['Age'] > 25]

Group by

grouped = df.groupby('Department')['Salary'].mean()

Pivot

pivot = df.pivot_table(values='Sales', index='Region', columns='Product')

undefined

pivot = df.pivot_table(values='Sales', index='Region', columns='Product')

undefined

JavaScript (xlsx)

JavaScript（xlsx）

javascript

import XLSX from 'xlsx';

// Read file
const workbook = XLSX.readFile('data.xlsx');
const sheetName = workbook.SheetNames[0];
const worksheet = workbook.Sheets[sheetName];

// Convert to JSON
const data = XLSX.utils.sheet_to_json(worksheet);

// Write file
const newWorksheet = XLSX.utils.json_to_sheet(data);
const newWorkbook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(newWorkbook, newWorksheet, 'Data');
XLSX.writeFile(newWorkbook, 'output.xlsx');

javascript

import XLSX from 'xlsx';

// Read file
const workbook = XLSX.readFile('data.xlsx');
const sheetName = workbook.SheetNames[0];
const worksheet = workbook.Sheets[sheetName];

// Convert to JSON
const data = XLSX.utils.sheet_to_json(worksheet);

// Write file
const newWorksheet = XLSX.utils.json_to_sheet(data);
const newWorkbook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(newWorkbook, newWorksheet, 'Data');
XLSX.writeFile(newWorkbook, 'output.xlsx');

Common Operations

常见操作

CSV to Excel

CSV转Excel

python

import pandas as pd

df = pd.read_csv('data.csv')
df.to_excel('data.xlsx', index=False)

python

import pandas as pd

df = pd.read_csv('data.csv')
df.to_excel('data.xlsx', index=False)

Excel to CSV

Excel转CSV

python

df = pd.read_excel('data.xlsx')
df.to_csv('data.csv', index=False)

python

df = pd.read_excel('data.xlsx')
df.to_csv('data.csv', index=False)

Merging Excel Files

合并Excel文件

python

dfs = []
for file in ['file1.xlsx', 'file2.xlsx', 'file3.xlsx']:
    df = pd.read_excel(file)
    dfs.append(df)

combined = pd.concat(dfs, ignore_index=True)
combined.to_excel('merged.xlsx', index=False)

python

dfs = []
for file in ['file1.xlsx', 'file2.xlsx', 'file3.xlsx']:
    df = pd.read_excel(file)
    dfs.append(df)

combined = pd.concat(dfs, ignore_index=True)
combined.to_excel('merged.xlsx', index=False)

Remember

注意事项

Close workbooks after use
Handle large files in chunks
Validate data before writing
Use pandas for data analysis, openpyxl for formatting

使用后关闭工作簿
分块处理大文件
写入前验证数据
数据分析用pandas，格式设置用openpyxl