如何根据XLSX文件中多个单元格的内容来命名文件

一、如何根据XLSX文件中多个单元格的内容来命名文件

使用 Python

python

import openpyxl

import os

打开 XLSX 文件

workbook = openpyxl.load_workbook('data.xlsx')

获取活动工作表

sheet = workbook.active

循环遍历要用于命名的单元格

for row in range(2, sheet.max_row + 1):

获取单元格值

cell_values = [sheet.cell(row, col).value for col in range(1, sheet.max_column + 1)]

将单元格值连接成文件名

filename = '_'.join(str(value) for value in cell_values)

保存文件

sheet.cell(row, sheet.max_column + 1).value = filename

workbook.save('data.xlsx')

将文件移动到新目录

os.rename('data.xlsx', os.path.join('new_directory', filename + '.xlsx'))

使用 VBA

vba

Sub RenameFilesBasedOnCells()

Dim wb As Workbook

Dim ws As Worksheet

Dim i As Long, j As Long

Dim filename As String

Set wb = ActiveWorkbook

Set ws = wb.ActiveSheet

For i = 2 To ws.Rows.Count

filename = ""

For j = 1 To ws.Columns.Count

filename = filename & ws.Cells(i, j).Value & "_"

Next j

filename = Left(filename, Len(filename) 1)

ws.Cells(i, ws.Columns.Count + 1).Value = filename

Next i

wb.Save

wb.Close

For i = 2 To ws.Rows.Count

filename = ws.Cells(i, ws.Columns.Count + 1).Value

Name wb.Sheets(i) As filename

Next i

wb.SaveAs Filename:="C:\Path\To\NewWorkbook.xlsx"

End Sub

二、如何根据xlsx文件中多个单元格的内容来命名文件

使用 Python

python

import openpyxl

import os

打开 Excel 文件

workbook = openpyxl.load_workbook('data.xlsx')

获取活动工作表

sheet = workbook.active

循环遍历要命名的单元格

for row in range(2, sheet.max_row + 1):

获取单元格内容

cell_values = [sheet.cell(row, col).value for col in range(1, sheet.max_column + 1)]

将单元格内容连接成文件名

filename = '_'.join(str(value) for value in cell_values)

重命名文件

os.rename('original_file.xlsx', filename + '.xlsx')

使用 VBA

vba

Sub RenameFilesBasedOnCells()

Dim wb As Workbook

Dim ws As Worksheet

Dim i As Long, j As Long

Dim filename As String

Set wb = ThisWorkbook

Set ws = wb.Sheets("Sheet1")

For i = 2 To ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

filename = ""

For j = 1 To ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

filename = filename & ws.Cells(i, j).Value & "_"

Next j

filename = Left(filename, Len(filename) 1) & ".xlsx"

wb.SaveAs filename

Next i

End Sub