python - 使用xlsxwriter写入Excel, 只能写入65536 无法继续写入.

Question

1. 使用Python+xlsxwriter处理爬虫数据,写入到excel

2. 代码可以读取到全部数据,有9W行,但是写入文件,只能写入65536行

3. 打开文件使用的是mac的numbers,不是office.

4. 有朋友碰到过这个情况吗?帮忙看看.谢谢!

    workbook = Workbook('albumsound.xlsx')                                                                                                                                                                      
  1 
  2     try:
  3         f = workbook#创建工作簿
  4         sheet1 = f.add_worksheet(u'专辑')#创建sheet
  5         headings = [u'专辑名称', u'专辑类型', u'作者', u'播放量', u'上线时间', u'更新时间', u'单集数量']
  6         sheet1.write_row('A1', headings)
  7         #for x in range(0, len(row0)):
  8         #    sheet1.write(0, x, row0[x], set_style('Times New Roman', 220, True))
  9 
 10         #sheet2 = f.add_worksheet(u'专辑')
 11         #headings = [u'专辑名称', u'专辑类型', u'专辑作者', u'专辑上线时间', u'专辑更新时间', u'专辑播放量', u'单集数量', u'专辑链接']
 12         #sheet2.write_row('A1', headings, bold)
 13         # 生成第一行
 14         #for i in range(0, len(row0)):
 15         #    sheet2.write(0, i, row0[i], set_style('Times New Roman', 220, True))
 16 
 17     except Exception as e:
 18         pass
 19 
 20     con = mdb.connect('localhost', 'root', 'ubuntu', 'xmlyalbumdata', charset='utf8')
 21     cur = con.cursor()
 22     cur.execute("SET NAMES utf8")
 23 
 24     time1 = datetime.now().strftime('%Y%m%d')
 25     tabname = 'albumsound_' + time1
 26 
 27     sql = """select * from {}""".format(tabname)
 28     excel = cur.execute(sql)
 29     datas = cur.fetchall()
 30     i = 1
 31     for data in datas:
 32         sheet1.write(i, 0, data[1])
 33         sheet1.write(i, 1, data[2])
 34         sheet1.write(i, 2, data[3])
 35         sheet1.write(i, 3, data[4])
 36         sheet1.write(i, 4, data[5])
 37         sheet1.write(i, 5, data[6])
 38         sheet1.write(i, 6, data[7])
 39         i += 1  ### 这里的i最后输出9w行数据
 40 #    print i    
 41     
 42     f.close()

Answer 1

The 2003 version of Excel has 65536 rows, and the 2007 and 2010 versions have 1048576 rows

Answer 2

Would you like to first try changing a file every 65536 lines of output, or close the file and reopen it for additional writing.

Answer 3

The guy on the first floor has already given the correct answer. It looks like your suffix is ​​xlsx. In fact, the data has been written in. Did you use excel 2003 when opening it? You should switch to excel 2007 or above to open it.