Python read Excel Method Instance analysis, python instance analysis

Python read Excel Method Instance analysis, python instance analysis

This example describes how to read an Excel file from Python. Share it with you for your reference. The details are as follows:

This day, data is imported from an Excel file (.xls) to a database table, and a row is manually input. Later, I thought that I could not do this all the time. I wrote the following code using Python, which can easily deal with this scenario. For example, the methods encapsulated by me can easily generate SQL statements for data import. Of course, those familiar with Excel programming can also directly use VBA to write a script to generate an SQL statement for inserting data.

You can also change the. xlsfile to A. CSV file, and then import it through tools such as SQLyog or Navicat, but not fine-grained control (for example, some data that does not meet certain conditions does not need to be imported, the application can control the data more precisely. For example, duplicate data cannot be imported repeatedly. For example, the columns of the Excel table to be imported are inconsistent with those of the table in the database ).

My Python version is 3.0, You need to download xlrd 3: http://pypi.python.org/pypi/xlrd3/ and then install it through the setup. py install command

Import xlrd3 ''' author: jxqlove? This code mainly encapsulates several Excel Data Operations Methods ''''' to obtain the row view and obtain all rows contained in the Sheet according to the Sheet sequence number. The returned values are similar to ['A ', 'B', 'C'], ['1', '2', '3'] sheetIndex indicates the sheet index, 0 indicates the first sheet, similarly, 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. append (I, rowLists (I) return rows ''' get the row sheetIndex of a specified sequence number of a Sheet from 0. rowIndex starts from 0. ''' def getRow (sheetIndex, rowIndex, xlsFilePath): rows = getAllRowsBySheetIndex (sheetIndex, xlsFilePath) return rows [rowIndex] ''' get the column view get all the columns contained in the Sheet according to the Sheet sequence number, the returned values are similar to [['A', 'B', 'C'], ['1', '2', '3']. The sheetIndex indicates the sheet index, 0 indicates the first sheet, and the xlsFilePath 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 columns colList = table. col_values for I in range (colNum): cols. append (colList (I) return cols ''' get the sheetIndex column of the specified sequence number of a Sheet from 0. colIndex starts from 0. ''' def getCol (sheetIndex, colIndex, xlsFilePath): cols = getAllColsBySheetIndex (sheetIndex, xlsFilePath) return cols [colIndex] ''' gets the value '''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 ') # obtain the data print (getCol (0, 0 ,'. /product .xls ') # obtain the data print (getCellValue (0, 3, 2,') in the first column of the first sheet ,'. /product .xls ') # obtain the value of the cell in the second column of the fourth row of the first sheet.

I hope this article will help you with Python programming.