Shulou(Shulou.com)06/01 Report--

This article mainly explains "how Python merges excel files". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how Python merges excel files.

I. installation module

1. Find the corresponding module

2. Install with pip install

Pip3 install xlrd

Pip3 install XlsxWriter

Because you are using python3, you install it with pip3.

II. XlsxWriter example

Let's take a look at a simple demonstration:

Import xlsxwriter # create a workbook and add a worksheet workbook = xlsxwriter.Workbook ("c.xlsx") worksheet = workbook.add_worksheet () # set column width worksheet.set_column ("bold A", 20) # format bold = workbook.add_format ({"bold": True}) # set the value of the cell worksheet.write ("A1", "Hello") # formatted cell worksheet.write ("A2") "World") # write some numbers Use column identification worksheet.write (2,0123) worksheet.write (3,0,123.456, bold) # insert a picture worksheet.insert_image ("B5", "s.png") # close file stream workbook.close () 3. Merge Excel data

For merging excel, there are two kinds of case, one is that the header is the same, the other is the excel with different header, but you want to put it in the same table for easy viewing.

4. Excel with the same watch head

This process is very simple, and the specific code is as follows:

Import xlrdimport xlsxwriter source_xls = ["a.xlsx" "b.xlsx"] target_xls = "3.xlsx" # read data data = [] for i in c: wb = xlrd.open_workbook (I) for sheet in wb.sheets (): for rownum in range (sheet.nrows): data.append (sheet.row_values (rownum)) print (data) # write data workbook = xlsxwriter.Workbook (target_xls) worksheet = workbook.add_worksheet () font = workbook.add_format ({"font_size": 14}) for i in range (len (data)): for j in range (len (data [I])): worksheet.write (I J, data [I] [j], font) # close file stream workbook.close ()

Excel is made up of rows and columns, so here all the data from all the sheet in all files is read out to form a two-dimensional array, and then written to the new Excel.

5. Excel with different heads

For excel with different headers, you may need to manually select a portion of the table and merge it. The specific code is as follows:

Import xlrdimport xlsxwriter source_xls = ["a.xlsx", "b.xlsx"] target_xls = "merge .xlsx" # read data data = [] # duplicate data dupdata = [] # name list Deduplicates name by name = [] # get the number of excel sheetcount = len (source_xls) I = 0while I < len (source_xls): wb = xlrd.open_workbook (source_ excel [I]) # Storage different excel data data.append ([]) # there may be multiple tables for sheet in wb.sheets (): if I = = 0: # add the header to dupdata.append (sheet.row first _ values (0) for rownum in range (sheet.nrows): # determine whether the name is duplicated The head of the watch begins with the name. So remove the name from if (sheet.row_values (rownum) [0] in name): dupdata.append (sheet.row_values (rownum)) # add another table header if (sheet.row_values (rownum) [0] = = 'name'): data [I] .append (sheet.row_values (rownum)) else: name.append (sheet.row_values (rownum) [0]) data [I] .append (sheet.row_values (rownum)) iConsolidated 1 # merge data workbook = xlsxwriter.Workbook (target_xls) worksheet = workbook.add_worksheet () font = workbook.add_format ({"font_size": 14}) lineNum = 0for num in range (len (data)): # distinguish data from different excel if num== 0: for i in range (len (dataum)): # name worksheet.write (lineNum 0, data [num] [I] [0], font) # check number worksheet.write (lineNum, 1, data [num] [I] [1], font) # Age worksheet.write (lineNum, 2, data [num] [I] [23], font) # Blue label worksheet.write (lineNum, 3, data [num] [I] [14], font) # Yellow label worksheet.write (lineNum, 4, data [num] [I] [19]) Font) worksheet.write (lineNum, 5, data [num] [I] [20], font) worksheet.write (lineNum, 6, data [num] [I] [21], font) # size worksheet.write (lineNum, 7, data [num] [I] [24], font) worksheet.write (lineNum, 8, data [num] [I] [25], font) worksheet.write (lineNum, 9, data [num] [I] [26], font) lineNum + = 1 # only two excel So directly use else else: for i in range (len (data [num]): lineNum + = 1 # name worksheet.write (lineNum, 0, data [num] [I] [0], font) # check number worksheet.write (lineNum, 1, data [num] [I] [1], font) # Age worksheet.write (lineNum, 2, data [num] [I] [2], font) # Blue label worksheet.write (lineNum, 3) Data [num] [I] [30], font) # close file stream workbook.close () # for duplicate data Output with the header to facilitate subsequent review of workbook = xlsxwriter.Workbook ("repeat .xlsx") worksheet = workbook.add_worksheet () font = workbook.add_format ({"font_size": 14}) for i in range (len (dupdata)): for j in range (len (dupdata [I])): worksheet.write (I, j, dupdata [I] [j], font) # close the file stream workbook.close () here I believe you have a deeper understanding of "how Python merges excel files". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.