Small knowledge, big challenge! This paper is participating in theEssentials for programmers”Creative activities.

One, foreword

Excel files are one of the most commonly used files in our work. There are a lot of powerful tools in Excel, so working with files in Excel is a lot easier. But sometimes when we have a file that’s not an Excel file, and we want to use something in Excel, we can figure out how to convert that file to Excel. Today we’re going to implement this, but note that we can only convert regular files to Excel, and this isn’t universal. There’s only one way to think about it.

2. Openpyxl module

The OpenPyXL module is a module for manipulating Excel files, but there are many other modules that can do the same thing that I won’t cover here.

1, install,

To install, simply execute the following statement:

pip install openpyxl
Copy the code

Then import the workbook in the code:

from openpyxl import Workbook
Copy the code

So we can get started.

2. Simple operation

Let’s look at some simple operations:

from openpyxl import Workbook
Create a workbook
wb = Workbook()
# activation
ws = wb.active
# Set refers to freeze-frame data
ws['A1'] = 41
Add data on the next line
ws.append([1.2.3])
# save
wb.save("1.xlsx")
Copy the code

The first two steps here are basic: first create the Workbook object, then invoke the active function to activate it. We then add data to the specified coordinates by subscript. Finally, the save method is called to save the file.

3. Transfer text files to Excel files

These are enough for today, so let’s look at how to convert text files into Excel files.

1. Look for patterns

As stated at the beginning of this article, we can only convert regular text files to Excel, otherwise there is not much point. So our first step is to look for patterns. For example, we have the following file:

Name sex age Zack male 21 Rudy male 22 Alice female 20 Atom male 23Copy the code

Let’s look at the data above, where the first row is the field of the data. The next few lines are the actual data. The attribute values of individual data are separated by commas. It’s a pretty clear pattern, and it’s a good one for today. As you can see on closer inspection, commas come in Both Chinese and English, and some contain Spaces. With that in mind, we need to do something about it when we convert.

2. Start the transformation

First we need to process the text, and then we need to write Excel. The code is as follows:

from openpyxl import Workbook
wb = Workbook()
ws = wb.active
# File processing
with open("test.txt"."r", encoding="utf-8") as f:
    # Replace commas with uniform \t
    content = f.read().replace(","."\t").replace("，"."\t")
    # Break content by newline
    lines = content.split("\n")
    # extract title
    titles = lines[0].split("\t")
    titles.insert(0."")
    Write the title to excel
    ws.append(titles)
    # write content
    for i, line in enumerate(lines[1:]):
        item = line.split("\t")
        item.insert(0, i)
        ws.append(item)
wb.save("1.xlsx")
Copy the code

After the above processing, we have successfully converted the text to Excel. Here is the result:

The result is exactly what we want, you can modify the code according to different needs and different files.