weixin_46798309
2020-12-24 14:02
采纳率: 100%
浏览 286

python excel 多行合并一行

求学习代码,谢谢大神!!!!

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

6条回答 默认 最新

  • 天际的海浪 2020-12-24 19:22
    已采纳
    #-*- coding:utf-8 -*-
    
    import openpyxl
    book = openpyxl.load_workbook(r'输入文件.xlsx')
    sh = book.active
    head = [x.value for x in sh[1]]
    body = {}
    index = None
    
    for r in sh.iter_rows(min_row=2):
        i = r[0].value
        arr = body.setdefault(i,[i])
        for v in r[1:]:
            arr.append(v.value)
    
    arr = list(body.values())
    n = (len(arr[0])-1)//(len(head)-1)
    head = head[0:1] + head[1:] * n
    
    newbook = openpyxl.Workbook()
    sh = newbook.active
    sh.append(head)
    for t in arr:
        sh.append(t)
    newbook.save(r'输出文件.xlsx')
    点赞 评论
  • SoftwareTeacher 2020-12-24 14:51

    1. 把文件转存为 .csv 文件

    2. 用 notepad 打开 .csv 文件,看里面的结构

    3. 用 Python 写读文件, 转化格式, 写文件的代码。

    点赞 评论
  • Captain_ZT 2020-12-24 15:50
    # -*- coding: utf-8 -*-
    import xlrd
     
    
    rbook = xlrd.open_workbook('wenda1.xlsx')
    rbook.sheets()
    rsheet = rbook.sheet_by_index(0) 
     
    faq_tmp_dict = {} 
    faq_formal_list = [] 
    first_category_tag = "" 
    index = 0
     
    for row in rsheet.get_rows():
      index +=1
     
      first_category_row = row[0] 
      first_category_value = first_category_row.value 
      if first_category_value != '1级分类': 
        if first_category_value: 
          first_category_tag = "%s#%s"%(first_category_value,index)
          faq_like_column = row[1] 
          faq_like_value = faq_like_column.value 
          faq_tmp_dict.setdefault(first_category_tag, []).append(faq_like_value)
        else:
          faq_like_column = row[1] 
          faq_like_value = faq_like_column.value
          faq_tmp_dict.setdefault(first_category_tag, []).append(faq_like_value)
     
     
    print(faq_tmp_dict)
    for i in faq_tmp_dict:
      tmp_dict = {}
      first_category = i.split('#')[0]
      # print("first_category",first_category)
      faq_like_all = "||".join(faq_tmp_dict[i])
      # print("faq_merge",faq_all)
      tmp_dict['first_category'] = first_category
      tmp_dict['faq_like_all'] = faq_like_all
      faq_formal_list.append(tmp_dict)
     
    print(faq_formal_list)
    点赞 评论
  • weixin_46798309 2020-12-24 17:22

    excel 表格转化,最后生成所需的表格,代码最后没生成表格

    点赞 评论
  • 天际的海浪 2020-12-24 19:30

    注意 openpyxl只支持 xlsx格式的文件,如果你的文件是xls格式的文件需要用 excel 另存为 xlsx格式
     

    点赞 评论
  • weixin_46798309 2020-12-25 14:21

    谢谢 老师

    点赞 评论

相关推荐 更多相似问题