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

This article focuses on "what are the Excel libraries that allow Python efficiency to take off". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what are the Python libraries for Excel efficiency to take off"?

Xlwings

Xlwings is a very powerful library for dealing with Excel, whether it's Windows or Mac,Excel or WPS.

It has very complete functions, can easily create, open, modify and save Excel, can seamlessly connect with matplotlib, numpy and pandas, support reading and writing numpy and pandas data types, and import matplotlib visual charts into excel. In addition, you can also call the program written by VBA in the Excel file, or you can have VBA call the program written by Python.

Import xlwings as xw # Import library app = xw.App (visible=True,add_book=False) wb = app.books.add () # Open Excel program wb = xw.Book ('example.xlsx') # Open existing workbook wb.save (' example.xlsx') # Save workbook wb.close () # exit workbook (may be omitted) app.quit () # exit Excel sht = wb.sheets [0] # reference worksheet In parentheses is the first sheet name rng = sht.range ('a1') # rng = sht [' a1'] # reference cell The first column of the first line is A1 rng = sht.range ('a1pura5') # reference region sht.range (' a1'). Value = 'Hello' # cell A1, write the string' Hello' sht.range ('a1'). Value = [1LJ 2JJ 3J 4] # insert by default by line: A1:D4 writes 1J, 2J, 3J, 4 sht.range (' a2') .options (transpose=True). Value = [5line 6]. 7J8] # insert sht.range ('a6'). Expand (' table'). Value = [['axiajiajie'], ['dFe8], [' gongzhongjiaoh'] 'i'] # Multi-line input print (sht.range ('a1A1:D4 rng d4'). Value) # read A1:D4 rng = sht.range (' a1'). Expand ('table') nrows = rng.rows.count a = sht.range (f'a1:a {nrows}'). Value # read Excel first column ncols = rng.columns.count fst_col = sht [0 : ncols] .value # reads the first line of Excel sht.range ('A1'). Column # gets the row label sht.range ('A1'). Row # gets the row label sht.range ('A1'). Column_width # gets the column width sht.range ('A1'). Row_height # gets the row height print (sht.range ('A1'). Column, sht.range ('A1'). Row, sht.range ('A1'). Column_width Sht.range ('A1'). Row_height) sht.range (' A1'). Rows.autofit () # row height adaptive sht.range ('A1'). Columns.autofit () # column width adaptive sht.range (' A1'). Color= (34Med 156) # give cell A1 a background color sht.range ('A1'). Color # returns the RGB value of the cell color print (sht.range (' A1'). Color) sht.range ('A1') .color = None # clear cell color print (sht.range ('A1') .color) sht.range (' A1') .formula='=SUM (B6:B7)'# enter the formula The corresponding cell execution result sht.range ('A1'). Formula_array # gets the cell formula sht.range (' A1'). Value= ['a1','A1','a2'], [1pc2] 3]] # write batch information to the specified cell location sht.range ('A1'). Expand (). Value # use the expand () method to read the batch data in the table print (sht.range ('A1'). Expand (). Value) import numpy as np np_data = np.array ((1d2)) sht.range ('F1'). Value = np_data # writes numpy array data type import pandas as pd df = pd.DataFrame ([[1J 2], [3J 4]) Columns= ['asides,' b']) sht.range ('A5'). Value = df # writes the pandas DataFrame data type to excel sht.range (' A5') .options (pd.DataFrame,expand='table') .value # to read the data The output type is DataFrame import matplotlib.pyplot as plt% matplotlib inline fig = plt.figure () plt.plot ([1, 2, 3, 4, 5]) sht.pictures.add (fig, name='MyPlot', update=True) # write the matplotlib chart to the excel table xlrd

Xlrd mainly reads Excel, supports excel tables in xlsx and xls format, and can read specified forms, specified rows and specified cells.

Import xlrd # Import library data = xlrd.open_workbook (filename) # filename and path If the path or file name is in Chinese, add an r worshipper native character # get a worksheet in book table = data.sheets () [0] # get table = data.sheet_by_index (sheet_indx)) # get table = data.sheet_by_name (sheet_name) # get names = data.sheet_names () # by name and return to book There is the name of the worksheet data.sheet_loaded (sheet_name or indx) # check whether a sheet has been imported nrows = table.nrows # get the valid rows in the sheet table.row (rowx) # return a list of all the cell objects in the row table.row_slice (rowx) # return a list of all the cell objects in the column table.row_types (rowx Start_colx=0, end_colx=None) # returns a list of data types of all cells in the row table.row_values (rowx, start_colx=0, end_colx=None) # returns a list of data of all cells in the row table.row_len (rowx) # returns the effective cell length of the column ncols = table.ncols # gets the number of valid columns of the list table.col (colx, start_rowx=0) End_rowx=None) # returns a list of all cell objects in the column table.col_slice (colx, start_rowx=0, end_rowx=None) # returns a list of all cell objects in the column table.col_types (colx, start_rowx=0, end_rowx=None) # returns a list of table.col_values (colx, start_rowx=0) consisting of the data types of all cells in the column End_rowx=None) # returns a list of data from all the cells in the column table.cell (rowx,colx) # returns the cell object table.cell_type (rowx,colx) # returns the data type table.cell_value (rowx,colx) # returns the data xlwt in the cell

Xlwt is mainly written to Excel, which can be used to write specified forms and cells, but the saved format only supports xls format.

Import xlwt # Import module workbook = xlwt.Workbook (encoding='utf-8') # create workbook object worksheet = workbook.add_sheet ('sheet1') # create worksheet sheet worksheet.write (0,0,' hello') # write to the table, the first parameter row, the second parameter column The third parameter content workbook.save ('students.xls') # save table is students.xls # set style workbook = xlwt.Workbook (encoding='utf-8') worksheet = workbook.add_sheet (' sheet1') # set font style font = xlwt.Font () font.name = 'Time New Roman' # font font.bold = True # bold font.underline = True # underline font.italic = True # italic style = Xlwt.XFStyle () style.font = font # create style worksheet.write (0 1, 'world', style) workbook.save (' students.xls') # create workbook # merge cell workbook = xlwt.Workbook (encoding='utf-8') worksheet = workbook.add_sheet ('sheet1') # create merge cell through worksheet call merge () create merge cell # first and second parameter single table rows merge, third and fourth parameter columns merge # merge column 0 to column 2 cell worksheet.write_merge (0,0,0,2, 'first merge') # merge row 1, row 2 cell worksheet.write_merge (0,1,0,0) 'first merge') workbook.save (' students.xls') # sets the cell alignment workbook = xlwt.Workbook (encoding='utf-8') worksheet = workbook.add_sheet ('sheet1') alignment = xlwt.Alignment () alignment.horz = xlwt.Alignment.HORZ_CENTER # horizontal Center alignment.vert = xlwt.Alignment.VERT_CENTER # Vertical Center style = xlwt.XFStyle () style.alignment = alignment worksheet.col (0). Width = 6666 # Set the cell width worksheet.row (0). Height_mismatch = True worksheet.row (0). Height = 1000 # set the height of the cell worksheet.write (0) 0, 'hello world' Style) workbook.save ('center.xls') # set cell border workbook = xlwt.Workbook (encoding='utf-8') worksheet = workbook.add_sheet (' sheet1') border = xlwt.Borders () # DASHED dashed line # NO_LINE does not have # THIN solid line border.left = xlwt.Borders.THIN border.right = xlwt.Borders.THIN border.top = xlwt.Borders.THIN border.bottom = xlwt.Borders.THIN style = xlwt.XFStyle () style.borders = border worksheet.write (1,1 'love', style) workbook.save (' dashed.xls') # set cell background color workbook = xlwt.Workbook (encoding='utf-8') worksheet = workbook.add_sheet ('sheet1') pattern = xlwt.Pattern () pattern.pattern = xlwt.Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 3 style = xlwt.XFStyle () style.pattern = pattern worksheet.write (1,1,' shit' Style) workbook.save ('shit.xls') # set font color workbook = xlwt.Workbook (encoding='utf-8') worksheet = workbook.add_sheet (' sheet1') font = xlwt.Font () # set font to red font.colour_index=xlwt.Style.colour_map ['red'] style = xlwt.XFStyle () style.font = font worksheet.write (0,1,' world', style) workbook.save ('students.xls')

XlsxWriter

XlsxWriter can be used to write text, numbers, formulas and support unit formatting, pictures, charts, document configuration, automatic filtering and other features, but the drawback is also obvious, can not be used to read and modify Excel files.

Import xlsxwriter # Import library work_book = xlsxwriter.Workbook ('my first.xlsx') # create an excel file named "my first.xlsx" work_sheet1 = work_book.add_worksheet () # add shhet1 work_sheet2 = work_book.add_worksheet ('my excel.xlsx') # add sheet name my excel.xlsx work_sheet3 = work_book.add_worksheet () # No parameters Add sheet3 # write_number by default: write number # write_blank: write space # write_formula: write formula # write_datetime: write time format # write_boolean: write logical data # write_url: write link address work_sheet2.write_string (0,0, 'this is write stringing') Work_sheet2.write_number ('A _ 2, 123456) work_sheet2.write_blank ('A _ 3, None) work_sheet2.write_number ('B _ 1, 12) work_sheet2.write_number ('B _ 2, 24) work_sheet2.write_number ('B _ 3, 35) work_sheet2.write_formula ('B _ 7,'= sum (b1:b5)') work_sheet2.write_datetime (0,3, datetime.datetime.strptime ('2019-04-18,'% YMY% m ~ m% d')) Work_book.add_format ({'num_format':' yyyy-mm-dd'}) work_sheet1.write_boolean (0,0, True) work_sheet1.write_url ('A2, 'http://www.toutiao.com')openpyxl)

Openpyxl is a popular Python library for manipulating excel tables and only supports xlsx after version 03.

# create a workbook Workbook from openpyxl import Workbook workbook = Workbook () # create a workbook object workbook.save ('test.xlsx') # Save this workbook Name it test # Open the existing workbook from openpyxl import load_workbook workbook = load_workbook ('test.xlsx') # # Open the test table under the current path # create a table # method 1: insert to the last (default) ws1 = wb.create_sheet ("Mysheet") # method 2: insert to the beginning position ws2 = wb.create_sheet ("Mysheet") 0) # Select existing table from openpyxl import load_workbook workbook = load_workbook ('test.xlsx') # Open test form sheet = workbook [' first_sheet'] # Select a form page named first_sheet # Delete table from openpyxl import load_workbook workbook = load_workbook ('test.xlsx') # Open test form sheet = workbook [' first_sheet'] # Select under the current path Select the table page workbook.remove (sheet) # named first_sheet # Delete this table # access cells # method 1 cell1 = sheet ['A1'] # method 2 cell2 = sheet.cell (row=1 Column=2) cell1.value = '123456' # set the value of the cell sheet.merge_cells (' A1sheet.merge_cells A2') # merge A1 and A2 cells to this I believe you have a deeper understanding of "what are the Excel libraries that allow Python efficiency to take off", so 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.