How to operate excel in python environment-Python Tutorial-php.cn

Home

Backend Development

Python Tutorial

How to operate excel in python environment

零到壹度

Apr 14, 2018 am 10:36 AM

excelpython

The content shared with you in this article is how to operate excel in the python environment. It has certain reference value. Friends in need can refer to it

1. Available Third-party libraries

handle excel tables in python. Commonly used libraries include xlrd (read excel) tables, xlwt (write excel) tables, openpyxl (read and write excel tables), etc. xlrd is more efficient than openpyxl when reading excel tables with large data, so I used the two libraries xlrd and xlwt when writing scripts. None of these library files provide the function of modifying the content of existing excel tables. Generally, the content in the original excel can only be read out, processed, and then written into a new excel file.

You can use pip search excel to check and you can see more development packages.

2. Frequently Asked Questions

When using python to process excel tables, I found two more difficult problems: unicode encoding and the time recorded in excel.

Because python's default character encoding is unicode, when printing Chinese read from excel or reading an excel table or sheet with Chinese names, the program prompts the error UnicodeEncodeError: 'ascii' codec can't encode characters in position 0-2: ordinal not in range(128). This is because in Windows, Chinese uses the gb2312 encoding method, and Python treats it as unicode and ascii to decode it, which is why it reports an error. Use VAR.encode(‘gb2312’) to solve the problem of printing Chinese. (It’s strange that sometimes the results can be printed out, but what is displayed is not Chinese, but a bunch of codes.) If you want to read data from an excel table with a Chinese file name, you can add 'u' before the file name to indicate The Chinese file name is encoded in unicode.

In excel, both time and date are represented by floating point numbers. It can be seen that when the cell of 'March 20, 2013' is expressed in the 'regular' format, the content becomes '41353'; when the cell format is changed to date, the content becomes 'March 2013' 20th'. After using xlrd to read the date and time in excel, a floating point number is obtained. So it doesn't matter if the date and time written to Excel are a floating point number. You only need to change the representation of the table to date and time to get the normal representation. In excel, use the floating point number 1 to represent December 31, 1899.

3. Commonly used functions

The following mainly introduces the date-related functions in xlrd, xlwt, and datetime.

import xlrdimport xlwtfrom datetimedef testXlrd(filename):
    book=xlrd.open_workbook(filename)
    sh=book.sheet_by_index(0)    print "Worksheet name(s): ",book.sheet_names()[0]    print &#39;book.nsheets&#39;,book.nsheets    print &#39;sh.name:&#39;,sh.name,&#39;sh.nrows:&#39;,sh.nrows,&#39;sh.ncols:&#39;,sh.ncols    print &#39;A1:&#39;,sh.cell_value(rowx=0,colx=1)    #如果A3的内容为中文
    print &#39;A2:&#39;,sh.cell_value(0,2).encode(&#39;gb2312&#39;)def testXlwt(filename):
    book=xlwt.Workbook()
    sheet1=book.add_sheet(&#39;hello&#39;)
    book.add_sheet(&#39;word&#39;)
    sheet1.write(0,0,&#39;hello&#39;)
    sheet1.write(0,1,&#39;world&#39;)
    row1 = sheet1.row(1)
    row1.write(0,&#39;A2&#39;)
    row1.write(1,&#39;B2&#39;)

    sheet1.col(0).width = 10000

    sheet2 = book.get_sheet(1)
    sheet2.row(0).write(0,&#39;Sheet 2 A1&#39;)
    sheet2.row(0).write(1,&#39;Sheet 2 B1&#39;)
    sheet2.flush_row_data()

    sheet2.write(1,0,&#39;Sheet 2 A3&#39;)
    sheet2.col(0).width = 5000
    sheet2.col(0).hidden = True

    book.save(filename)if __name__==&#39;__main__&#39;:
    testXlrd(u&#39;你好。xls&#39;)
    testXlwt(&#39;helloWord.xls&#39;)
    base=datetime.date(1899,12,31).toordinal()
    tmp=datetime.date(2013,07,16).toordinal()    print datetime.date.fromordinal(tmp+base-1).weekday()

Related recommendations:

Use Python to process Excel files

Python processing Excel modules

Comparison of various operations of excel modules in python

The above is the detailed content of How to operate excel in python environment. For more information, please follow other related articles on the PHP Chinese website!

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

What are the alternatives to concatenate two lists in Python?May 09, 2025 am 12:16 AM

There are many methods to connect two lists in Python: 1. Use operators, which are simple but inefficient in large lists; 2. Use extend method, which is efficient but will modify the original list; 3. Use the = operator, which is both efficient and readable; 4. Use itertools.chain function, which is memory efficient but requires additional import; 5. Use list parsing, which is elegant but may be too complex. The selection method should be based on the code context and requirements.

Python: Efficient Ways to Merge Two ListsMay 09, 2025 am 12:15 AM

There are many ways to merge Python lists: 1. Use operators, which are simple but not memory efficient for large lists; 2. Use extend method, which is efficient but will modify the original list; 3. Use itertools.chain, which is suitable for large data sets; 4. Use * operator, merge small to medium-sized lists in one line of code; 5. Use numpy.concatenate, which is suitable for large data sets and scenarios with high performance requirements; 6. Use append method, which is suitable for small lists but is inefficient. When selecting a method, you need to consider the list size and application scenarios.

Compiled vs Interpreted Languages: pros and consMay 09, 2025 am 12:06 AM

Compiledlanguagesofferspeedandsecurity,whileinterpretedlanguagesprovideeaseofuseandportability.1)CompiledlanguageslikeC arefasterandsecurebuthavelongerdevelopmentcyclesandplatformdependency.2)InterpretedlanguageslikePythonareeasiertouseandmoreportab

Python: For and While Loops, the most complete guideMay 09, 2025 am 12:05 AM

In Python, a for loop is used to traverse iterable objects, and a while loop is used to perform operations repeatedly when the condition is satisfied. 1) For loop example: traverse the list and print the elements. 2) While loop example: guess the number game until you guess it right. Mastering cycle principles and optimization techniques can improve code efficiency and reliability.

Python concatenate lists into a stringMay 09, 2025 am 12:02 AM

To concatenate a list into a string, using the join() method in Python is the best choice. 1) Use the join() method to concatenate the list elements into a string, such as ''.join(my_list). 2) For a list containing numbers, convert map(str, numbers) into a string before concatenating. 3) You can use generator expressions for complex formatting, such as ','.join(f'({fruit})'forfruitinfruits). 4) When processing mixed data types, use map(str, mixed_list) to ensure that all elements can be converted into strings. 5) For large lists, use ''.join(large_li

Python's Hybrid Approach: Compilation and Interpretation CombinedMay 08, 2025 am 12:16 AM

Pythonusesahybridapproach,combiningcompilationtobytecodeandinterpretation.1)Codeiscompiledtoplatform-independentbytecode.2)BytecodeisinterpretedbythePythonVirtualMachine,enhancingefficiencyandportability.

Learn the Differences Between Python's 'for' and 'while' LoopsMay 08, 2025 am 12:11 AM

ThekeydifferencesbetweenPython's"for"and"while"loopsare:1)"For"loopsareidealforiteratingoversequencesorknowniterations,while2)"while"loopsarebetterforcontinuinguntilaconditionismetwithoutpredefinediterations.Un

Python concatenate lists with duplicatesMay 08, 2025 am 12:09 AM

In Python, you can connect lists and manage duplicate elements through a variety of methods: 1) Use operators or extend() to retain all duplicate elements; 2) Convert to sets and then return to lists to remove all duplicate elements, but the original order will be lost; 3) Use loops or list comprehensions to combine sets to remove duplicate elements and maintain the original order.

See all articles