python处理复杂excel_【已解决】python解析excel文件并读取其中的sheet和row和column的值...

折腾&＃xff1a;

期间&＃xff0c;需要去用python去读取和解析一个excel文件&＃xff0c;并读取其中的内容&＃xff0c;

包括包括多个sheet&＃xff0c;以及每个sheet中行row和列column的单元格cell的值&＃xff1a;

crifan python excel xlsx

python read excel xlsxxlwings

openpyxl

pandas

win32com

xlsxwriter

只能写入

DataNitro

xlutils

此处已经可以基本确定用&＃xff1a;openpyxl

但是为了想要顺带再去学学panda的话&＃xff0c;去搜搜&＃xff1a;xlrd&＃xff1a;用来读取很方便

openpyxl&＃xff1a;虽然功能很强大&＃xff0c;但是操作起来感觉没有xlwt方便openpyxl不错

xlrd也不赖

看下来&＃xff1a;

复杂点的&＃xff1a;panda&＃43;excel解析器&＃xff0c;比如panda&＃43;openpyxl

简单点的&＃xff1a;openpyxl或xlrd

很久之前用过&＃xff1a;xlrd

现在为了尝试新的&＃xff0c;且不太麻烦&＃xff0c;还是先直接用&＃xff1a;openpyxl吧。

参考官网文档&＃xff1a;

➜  英语资源 pip install openpyxl

Collecting openpyxl

Downloading openpyxl-2.5.1.tar.gz (169kB)

100% |████████████████████████████████| 174kB 1.1MB/s

Collecting jdcal (from openpyxl)

Downloading jdcal-1.3.tar.gz

Collecting et_xmlfile (from openpyxl)

Downloading et_xmlfile-1.0.1.tar.gz

Building wheels for collected packages: openpyxl, jdcal, et-xmlfile

Running setup.py bdist_wheel for openpyxl … done

Stored in directory: /Users/crifan/Library/Caches/pip/wheels/98/5e/20/70cde417026f1e168acdac7babf47b204a7b752b1a8e6bb795

Running setup.py bdist_wheel for jdcal … done

Stored in directory: /Users/crifan/Library/Caches/pip/wheels/0f/63/92/19ac65ed64189de4d662f269d39dd08a887258842ad2f29549

Running setup.py bdist_wheel for et-xmlfile … done

Stored in directory: /Users/crifan/Library/Caches/pip/wheels/99/f6/53/5e18f3ff4ce36c990fa90ebdf2b80cd9b44dc461f750a1a77c

Successfully built openpyxl jdcal et-xmlfile

Installing collected packages: jdcal, et-xmlfile, openpyxl

Successfully installed et-xmlfile-1.0.1 jdcal-1.3 openpyxl-2.5.1

想要去写代码&＃xff0c;但是发现对于excel中的列的表示不清楚&＃xff1a;

然后就可以去使用L13这种写法去读取cell的值了。

代码&＃xff1a;

# parse excel file

excelFullfilename &＃61; "/Users/crifan/dev/dev_root/xxx/xxx018.3.28_forDebug.xlsx"

wb &＃61; load_workbook(excelFullfilename)

logging.info("wb&＃61;%s", wb)

# sheetNameList &＃61; wb.get_sheet_names()

# logging.info("sheetNameList&＃61;%s", sheetNameList)

ws &＃61; wb[u"绘本"]

logging.info("ws&＃61;%s", ws)

rows &＃61; ws.rows

columns &＃61; ws.columns

是可以正常执行的&＃xff1a;

现在需要去搞清楚&＃xff0c;一共有多少行&＃xff0c;然后每行循环去处理。

去找找api文档&＃xff1a;

-》

搜&＃xff1a;

sheet

workbook

Openpyxl  get current sheet max row

row_count &＃61; sheet.max_row

column_count &＃61; sheet.max_column

-》

-》

“max_column

The maximum column index containing data (1-based)

Type:

int

max_row

The maximum row index containing data (1-based)

Type:

int “

找到了。

然后PyCharm中再想要去查看源码&＃xff0c;也是可以看到合适的匹配出的代码的&＃xff1a;

然后就可以去写代码读取值了&＃xff1a;

from openpyxl import Workbook, load_workbook

StorybookSheetTitle &＃61; u"绘本"

EnglishStorybookRootPath &＃61; "/Users/crifan/dev/dev_rootxxx"

ExcelFilename &＃61; "xxx资源2018.3.28_forDebug.xlsx"

ExcelFullFilename &＃61; os.path.join(EnglishStorybookRootPath, ExcelFilename)

AudioFilePathPrefix &＃61; EnglishStorybookRootPath

# parse excel file

wb &＃61; load_workbook(ExcelFullFilename)

logging.info("wb&＃61;%s", wb)

# sheetNameList &＃61; wb.get_sheet_names()

# logging.info("sheetNameList&＃61;%s", sheetNameList)

ws &＃61; wb[StorybookSheetTitle]

logging.info("ws&＃61;%s", ws)

# process each row in excel

for curRowNum in range(realContentRowStartNum, ws.max_row &＃43; 1):

logging.info("-"*30 &＃43; " row[%d] " &＃43; "-"*30, curRowNum)

hasAudioFileColNumCellValue &＃61; ws.cell(row&＃61;curRowNum, column&＃61;HasAudioFileColNum).value

logging.info("col[%d] hasAudioFileColNumCellValue&＃61;%s", HasAudioFileColNum, hasAudioFileColNumCellValue)

audioFilePathColNumCellValue &＃61; ws.cell(row&＃61;curRowNum, column&＃61;AudioFilePathColNum).value

logging.info("col[%d] audioFilePathColNumCellValue&＃61;%s", AudioFilePathColNum, audioFilePathColNumCellValue)

对于excel文件&＃xff1a;

输出&＃xff1a;

2018/03/30 02:55:02 LINE 104  INFO    wb&＃61;

2018/03/30 02:55:02 LINE 108  INFO    ws&＃61;

2018/03/30 02:55:02 LINE 113  INFO    mongoClient&＃61;MongoClient(host&＃61;[‘localhost:27017’], document_class&＃61;dict, tz_aware&＃61;False, connect&＃61;True)

2018/03/30 02:55:02 LINE 117  INFO    gridfsDb&＃61;Database(MongoClient(host&＃61;[‘localhost:27017′], document_class&＃61;dict, tz_aware&＃61;False, connect&＃61;True), u’gridfs’)

2018/03/30 02:55:02 LINE 125  INFO    fsCollection&＃61;

2018/03/30 02:55:02 LINE 129  INFO    —————————— row[3] ——————————

2018/03/30 02:55:02 LINE 132  INFO    col[12] hasAudioFileColNumCellValue&＃61;有

2018/03/30 02:55:02 LINE 134  INFO    col[13] audioFilePathColNumCellValue&＃61;None

调试的效果&＃xff1a;