This is the 10th day of my participation in the August More text Challenge. For details, see: August More Text Challenge

Hello everyone, I am Brother Chen ~

In Chen’s opinion, technology can reduce the boredom caused by tedious work, technology + practice = convenience. Recently, Chen brother also found it a little tedious to organize the Excel files manually when he was doing it. He thought technology could replace me to deal with this part of tedious work. Why not

Three scenarios:

  1. Multiple Excel files with the same field are merged into one Excel

  2. Multiple Excel files with different fields are combined into one Excel

  3. Combine multiple sheets of an Excel file into one sheet

So far, Chen brother has only thought of these three situations (if there are many other cases, please leave a comment below, because Chen brother is not often involved in a variety of Excel processing content, so can’t think of any other cases).

01 Merge multiple Excel files of the same field

Here we create three excel files: 11.xlsx; 12. XLSX; 13. XLSX; And fill it with data like this:

11.xlsx

12.xlsx

13.xlsx

Requirement: Merge these three Excel files into one Excel.

Import libraries

# Read module
import xlrd
# Write module
import xlwt
Copy the code

Two libraries are needed: XLRD reads Excel; XLWT writes to merged Excel;

# File list
xlxs_list = ["1/11.xlsx"."1/12.xlsx"."1/13.xlsx"]
# Create merged file
workbook = xlwt.Workbook(encoding='ascii')
worksheet = workbook.add_sheet('Sheet1')
Copy the code

Defines which Excel files to merge, and the merged Excel

# lines
count = 0
Only write the first XLSX header
bt = 0
for name in xlxs_list:
    wb = xlrd.open_workbook(name)
    # Locate worksheets by workbook
    sh = wb.sheet_by_name('Sheet1')
    # Iterate over Excel and print all data
    if count>1:
        bt=1
    for i in range(bt,sh.nrows):
        k = sh.row_values(i)
        # Traverse every column in every row
        for j in range(0.len(k)):
            worksheet.write(count,j, label=str(k[j]))
        count = count +1
workbook.save('1/ merge 1_ chengo.xlsx ')
Copy the code

** merge 1_ chengo. XLSX **

02 Concatenation of multiple Excel fields

Create three excel files: 21.xlsx; 22. XLSX; 23. XLSX; And fill it with data

21.xlsx

22.xlsx

23.xlsx

Combine these three Excel files into one Excel (from left to right)

# the number of columns
col = 0
for name in xlxs_list:
    wb = xlrd.open_workbook(name)
    # Locate worksheets by workbook
    sh = wb.sheet_by_name('Sheet1')
    # Iterate over Excel and print all data
    for i in range(0,sh.nrows):
        k = sh.row_values(i)
        # Traverse every column in every row
        for j in range(0.len(k)):
            worksheet.write(i,col+j, label=str(k[j]))
    col = col +len(k)
workbook.save('2/ merge 2_ chengo.xlsx ')
Copy the code

XLSX ** = XLSX ** = XLSX **

Merge multiple sheets of an Excel file

Create a new Excel file: 31.xlsx; And add sheet1, Sheet2, sheet3 to fill in the data

sheet1

sheet2

sheet3

Combine the three sheets in the same Excel file into one sheet.

sheet_list = ['Sheet1'.'Sheet2'.'Sheet3']
# lines
count = 0
Only write the first XLSX header
bt = 0
for st in sheet_list:
    # Locate worksheets by workbook
    sh = wb.sheet_by_name(st)
    # Iterate over Excel and print all data
    if count > 1:
        bt = 1
    for i in range(bt, sh.nrows):
        k = sh.row_values(i)
        # Traverse every column in every row
        for j in range(0.len(k)):
            worksheet.write(count, j, label=str(k[j]))
        count = count + 1
workbook.save('3/ merge 3_ chengo.xlsx ')
Copy the code

Merge 3_ chenger.xlsx into: merge 3_ chenger.xlsx

04 summary

So far, I can only think of these three cases that Chen Brother encountered (if there are many other cases, please leave a comment below, because Chen brother is not often involved in many kinds of Excel processing content, so I can’t think of any other cases).