Python method Instance analysis for reading Excel

Source: Internet
Author: User
The example in this article describes how Python reads Excel. Share to everyone for your reference. Specific as follows:

Today you need to take data from an Excel document (. xls) to a table in the database, starting with a line of lines. Later, I can't keep doing this, I wrote the following code in Python, and it's easy to deal with this scenario. For example, using the methods I've encapsulated can make it easy to generate SQL for importing data. Of course, students familiar with Excel programming can also write a script directly with VBA to generate SQL to insert data.

You can also change the. xls file to a. csv file, and then import it through tools such as SQLyog or navicat, but not fine-grained control (such as some data that does not meet certain criteria that do not need to be imported, which can be more finely controlled by the program, or if duplicate data cannot be imported repeatedly; and Excel tables and numbers to import The columns of the tables in the library are not exactly the same).

My Python version is 3.0 and I need to download xlrd 3:http://pypi.python.org/pypi/xlrd3/and install it via the setup.py installation command.

Import Xlrd3 ' Author:jxqlove? This code mainly encapsulates a few methods of manipulating Excel data "" To get a row view of all the rows that the sheet contains, based on the sheet ordinal, the return value is similar to [[' A ', ' B ', ' C '], [' 1 ' , ' 2 ', ' 3 ']]sheetindex indicates the index of sheet, 0 represents the first sheet, and so on Xlsfilepath is the relative or absolute path of the Excel file "Def getallrowsbysheetindex (  Sheetindex, Xlsfilepath): WorkBook = Xlrd3.open_workbook (xlsfilepath) Table = Workbook.sheets () [sheetindex] rows = [] RowNum = table.nrows # total number of rows rowlist = Table.row_values for i in Range (RowNum): Rows.append (Rowlist (i)) # equivalent to Rows.appe nd (i, rowlists (i)) return rows "Gets the line of the specified ordinal of a sheet sheetindex starting from 0 RowIndex starting from 0 ' def getRow (Sheetindex, RowIndex, Xlsfilepath): rows = Getallrowsbysheetindex (Sheetindex, Xlsfilepath) return Rows[rowindex] " Gets the column view gets the sheet contains all the columns according to the sheet ordinal, the return value resembles [[' A ', ' B ', ' C '], [' 1 ', ' 2 ', ' 3 ']]sheetindex indicates the index of sheet, 0 represents the first sheet, Xlsfilepath, in turn, is the relative or absolute path of the Excel file "Def getallcolsbysheetindex (Sheetindex, xlsfilepath): WorkBook = Xlrd3.open_ Workbook (xlsfilepath) Table = Workbook.sheets () [sheetindex] cols = [] Colnum = table.ncols # Total number of columns Collist = Table.col_values for I in Range (Colnum): Cols.append (Collist (i)) return cols "' Gets the column of a specified ordinal of sheet Sheetindex starting from 0 Colin Dex starting from 0 "Def getcol (Sheetindex, Colindex, xlsfilepath): cols = Getallcolsbysheetindex (Sheetindex, Xlsfilepath) return Cols[colindex] "Gets the value in the cell that specifies the specified column of sheet ' def getcellvalue (Sheetindex, RowIndex, Colindex, xlsfilepath): WorkBook = Xlrd3.open_workbook (xlsfilepath) Table = Workbook.sheets () [Sheetindex] return Table.cell (RowIndex, Colindex). Value # or Table.row (0) [0].value or Table.col (0) [0].valueif __name__== ' __main__ ': rowsinfirstsheet = Getallrowsbysheetindex (0 , './product. xls ') print (rowsinfirstsheet) Colsinfirstsheet = Getallcolsbysheetindex (0, './product. xls ') print (Colsinfirstsheet ) Print (getRow (0, 0, './product. xls ') # Gets the first sheet of the first row of data print (Getcol (0, 0, './product. xls ')) # Gets the first column of data for the first sheet print (Getcell Value (0, 3, 2, './product. xls ') # Gets the values of the cells in the second column of row Fourth of the first sheet

Hopefully this article will help you with Python programming.

  • Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

    Start building with 50+ products and up to 12 months usage for Elastic Compute Service

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

      24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.