来自 编程应用 2019-09-04 19:47 的文章
当前位置: 六合联盟网 > 编程应用 > 正文

用python读写excel(xlrd、xlwt)


一、xlrd的说明


xlrd是特地用来在python中读取excel文书档案的模块,使用前须求安装。


能够到那进展下载tar.gz文件,然后解压缩安装,在cmd命令窗口中切换来解压后的文件夹中,使用

获得贰个专门的职业表

python setup.py install
  table = data.sheets()[0]          
  table = data.sheet_by_index(0) 
  table = data.sheet_by_name(u)

开展安装。

获得整行和整列的值(重临数组) 获得行数和列数  6、获取单元格

方法二、

就自己要好行使的时候感到依旧赢得cell最有用,那就约等于是给了你四个二维数组,余下你就足以想怎么干就怎么干了。得益于那几个相当好用的库代码十分简单。但是还是有几多坑的留存导致话了一定期期探寻。现在列出来供后人衔考吧: 1、首先便是自家的总结是基于姓名总结种种表中的音信的,不过调节和测验开掘区别的表中各样名字貌似不可见包容,先河思疑过编码难点,然而新兴察觉是因为  空格。因为在excel中输入的时候很或然会顺手在一部分名字背后加上多少个空格或是tab键,那样看起来没什么分裂,不过程序管理的时候那便是七个完全  差别的串了。作者的消除办法是给每种获得的字符串都助长strip()管理一下。效果甚佳 2、照旧字符串的非常,在认清某些单元格中的字符串(粤语)是或不是等于笔者所付出的的时候开掘不可能合营,何况各类unicode也不太奏效,百度过一些缓慢解决  方案,可是都比较复杂或是没用。最终自身利用了一个相比变通的艺术:直接从excel中赢得自己想要的值再开展比较,效果是毫无疑问就是通用行不太好,个  呢不能难点还没解决。 二、写excel表   写excel表要用到xlwt模块,官方网站下载( 1、导入模块   import xlwt 2、创建workbook(其实就是excel,后来保留一下就行) workbook = xlwt.Workbook(encoding = 'ascii') 3、创建表   worksheet = workbook.add_sheet('My Worksheet') 4、往单元格内写入内容 worksheet.write(0, 0, label = 'Row 0, Column 0 Value') 5、保存 workbook.save('Excel_Workbook.xls') 是因为作者的供给比较轻巧,所以那方面没蒙受什么难点,独一的便是提议依然用ascii编码,不然大概会有一部分奇怪的景色。 本来xlwt功能远远不仅那些,他以致足以设置各样体制等等的。附上一点例证

动用pip进行设置

 Please note a useful alternative may be ezodf, which allows you to generate ODS (Open Document Spreadsheet) files  LibreOffice / OpenOffice. You can check them out at:http://packages.python.org/ezodf/ 
   workbook = xlwt.Workbook(encoding =  worksheet = workbook.add_sheet( worksheet.write(0, 0, label =  workbook.save( 
   workbook = xlwt.Workbook(encoding =  worksheet = workbook.add_sheet( font = xlwt.Font() 
 font.name = 
 font.bold = font.underline = font.italic = style = xlwt.XFStyle() 
 style.font = font 
 worksheet.write(0, 0, label =  worksheet.write(1, 0, label = , style) 
 workbook.save( 
  font.bold = True 
 font.italic = True 
 font.struck_out = True 
 font.underline = xlwt.Font.UNDERLINE_SINGLE 
 font.escapement = xlwt.Font.ESCAPEMENT_SUPERSCRIPT 
 font.family = xlwt.Font.FAMILY_ROMAN 
 font.charset = xlwt.Font.CHARSET_ANSI_LATIN 
 font.colour_index = font.get_biff_record = font.height = 0x00C8 
 font.name = font.outline = font.shadow = 
   workbook = worksheet = workbook.add_sheet( worksheet.write(0, 0,  worksheet.col(0).width = 3333 
 workbook.save( 
    workbook = worksheet = workbook.add_sheet( style = style.num_format_str =  
  workbook.save( 
   workbook = worksheet = workbook.add_sheet( worksheet.write(0, 0, 5) 
 worksheet.write(0, 1, 2) 
 worksheet.write(1, 0, xlwt.Formula()) 
 worksheet.write(1, 1, xlwt.Formula()) 
 workbook.save( 
   workbook = worksheet = workbook.add_sheet( worksheet.write(0, 0, xlwt.Formula()) 
 workbook.save( 
 Merging Columns   workbook = worksheet = workbook.add_sheet( worksheet.write_merge(0, 0, 0, 3, ) 
 font = xlwt.Font() 
 font.bold = True 
 style = xlwt.XFStyle() 
 style.font = font 
 worksheet.write_merge(1, 2, 0, 3, , style) 
 workbook.save( 
 Setting the Alignment   workbook = worksheet = workbook.add_sheet( alignment = xlwt.Alignment() 
 alignment.horz = xlwt.Alignment.HORZ_CENTER 
 alignment.vert = xlwt.Alignment.VERT_CENTER 
 style = xlwt.XFStyle() 
 style.alignment = alignment 
 worksheet.write(0, 0,  workbook.save( 

  workbook = worksheet = workbook.add_sheet( borders = xlwt.Borders() 
 borders.left = xlwt.Borders.DASHED 
 borders.right = borders.top = borders.bottom = borders.left_colour = 0x40
 borders.right_colour = 0x40
 borders.top_colour = 0x40
 borders.bottom_colour = 0x40
 style = xlwt.XFStyle() 
 style.borders = borders 
 worksheet.write(0, 0,  workbook.save( 
   workbook = worksheet = workbook.add_sheet( pattern = xlwt.Pattern() 
 pattern.pattern = xlwt.Pattern.SOLID_PATTERN 
 pattern.pattern_fore_colour = 5 
 style = xlwt.XFStyle() 
 style.pattern = pattern 
 worksheet.write(0, 0,  workbook.save( 
  - Panes -- separate views which are always  - Border Colors (documented above, but  - Border Widths (document above, but  - - - Zoom / - Source Code  reference available at: https://secure.simplistix.co.uk/svn/xlwt/trunk/xlwt/
pip install xlrd

table = data.sheets()[0] table = data.sheet_by_index(0) table = data.sheet_by_name(u ) 获取整行和整列的值(重临数组) 获取行数和列数 6、获...

二、使用介绍

1导入模块

  import xlrd

2 打开excel文件

  data = xlrd.open_workbook('excelFile.xls')

3 获取三个专业表方法

  table = data.sheets()[0]          #经过索引顺序获取

  table = data.sheet_by_index(0) #由此索引顺序获取 

  table = data.sheet_by_name(u'Sheet1')#经过名称获取

 

4 获取整行或整列的值,-->重回数组

  table.row_values(i)

  table.col_values(i)

 

5 获取行数和列数

  nrows = table.nrows

  ncols = table.ncols

 

6 循环表获取行数据,--->再次来到数据

   for i in range(nrows ):

        print table.row_values(i)

 

7 单元格

  cell_A1 = table.cell(0,0).value

  cell_C4 = table.cell(2,3).value

 

也得以动用行列号举行索引

  

cell_A1 = table.row(0)[0].value

  

cell_A2 = table.col(1)[0].value

 

 

8 sheet的部分性质

 

  name得到此时专门的学业表的名字,print table.name

 

  

 

 

 三、增多数量到excel文件

 

  要利用xlutils包里面包车型地铁copy函数,将xlrd张开的workbook转变来xlwt能够写的气象

  

def writeHeaderToXls(self):
        oldwb = xlrd.open_workbook(self.resultFileName,formatting_info=True)
        os.remove(self.resultFileName)
        newwb = copy(oldwb)

        for sheetindex in range(oldwb.nsheets):
            oldsheet = oldwb.sheet_by_index(sheetindex)
            colLength = oldsheet.ncols
            newsheet = newwb.get_sheet(sheetindex)
            for i in range(colLength):
                title = "level %d th"%(i+1)
                newsheet.row(0).write(i,title)

        newwb.save(self.resultFileName)

 

 

 

 

  

本文由六合联盟网发布于编程应用,转载请注明出处:用python读写excel(xlrd、xlwt)

关键词: